DEV Community: Marcus

Design Pattern // Logging an ETL Process with T-SQL — How to Capture Run, Component and Action in Evaluable Log Tables

Marcus — Sat, 05 Sep 2026 10:10:41 +0000

An ETL process finishes without an exception — but was everything really loaded that should have been? The mere fact that a process did not abort says nothing about whether it actually did what was expected of it. A readable, evaluable log is what turns a gut feeling into a defensible statement.

This design pattern logs an ETL run on three levels and answers the questions that success or failure hinge on:

How long does the ETL process take overall?
How long does a single component take — a stored procedure, an SSIS package or another building block?
How long does a specific SQL statement take?
How many rows did a SQL statement actually affect?
And above all: did the process as a whole, a component or a single statement complete successfully?

TL;DR — what this article covers:

Three-tier logging — the tables [LL].[Execution], [LL].[Component] and [LL].[Trace] capture run, component and action at increasing granularity.
Stored-procedure toolkit — procedures such as [LL].[spInsertTrace] and [LL].[spUpdateTrace] write and update the log records.
Exception handling with [LL].[Error] — a TRY/CATCH pattern ends the run in an orderly way and records every error in an evaluable form.
Continuation of the ETL architecture — the example uses schema T2 from the architecture article and adds the logging layer.

Prerequisites. SQL Server and a basic understanding of stored procedures and TRY/CATCH. This article is part of the ETL design-pattern cluster. As a lead-in, see Data Quality in an ETL Process and The Architecture of an ETL Process.

The Result

At its core the approach is straightforward: at the start of every action a log record is written, and once the action finishes it is updated with the outcome success or failure — and, where useful, with further information. In principle, that is all there is to it. In practice, a little more is needed after all.

Even though creating a log should not be a big deal, there is quite a bit to say about it. This article starts with the result and works backwards from there. The process logging presented here is three-tier and uses the following log tables:

[LL].[Execution]
[LL].[Component]
[LL].[Trace]

These tables log an ETL process and its associated components and steps at increasing granularity — read from top to bottom. The ETL process, the components and the individual steps are each logged with exactly one record.

Closely tied to logging the process is logging errors. The approach presented here uses the following table for that:

[LL].[Error]

The following figures show the result of three-tier logging for a simple, compact, but complete ETL process.

In the table [LL].[Trace] every action is logged with, among other things, the name of the procedure, the target entity the procedure processes, a short description of what was actually done, and further information such as the number of rows affected and the execution time.

Table [LL].[Trace]

In the table [LL].[Component] the calls of procedures and SSIS packages — or, more generally, components — are logged. Here, too, a short description of the component’s task, the target entity and the execution time are recorded.

Table [LL].[Component]

At the top level, every execution of the ETL process is logged with exactly one record in the table [LL].[Execution].

Table [LL].[Execution]

The three process-logging tables — [LL].[Execution], [LL].[Component] and [LL].[Trace] — contain two columns, [State] and [Success], in which the success or failure of an action, a component or the ETL process is stored.

Now the Derivation…

After this brief preview of the result, the following aspects of the approach need to be clarified:

Three-Tier Logging
Table Description, Declaration and Data Model
ETL Process Execution Status
Logging Procedures
Exception Handling
Example: Logging and Exception Handling in an ETL Process

Three-Tier Logging

The approach logs an ETL process in the three tables [LL].[Execution], [LL].[Component] and [LL].[Trace]. Each table is intended for logging specific artifacts. This section introduces how each table is used, its columns and the code to create it.

[LL].[Execution]

This table logs the execution of an ETL process with exactly one record. An ETL process needs a clearly identifiable entry point. That can be a stored procedure, an SSIS package, a Talend job or a SQL Server Agent job. At the start of the entry point’s execution, a log record is inserted into this table. After all tasks have been processed successfully, this record is updated to status success, or to error in the failure case. The table thus provides an overview of all executions of the ETL process, along with information such as the status and the duration of the run.

[LL].[Component]

This table logs the execution of a component with exactly one record. A component can be a stored procedure, an SSIS package or a Talend job. A component is characterized by the fact that it controls and performs one or more data manipulations. As with the [LL].[Execution] table, a log record is inserted at the start of execution and updated to status success or error on completion. The table therefore holds, per ETL run, a list of the executed components, along with information such as the status and the duration of the run.

[LL].[Trace]

The name of this table already hints that it is intended for detailed logging of the ETL process’s actions — it creates a trace. Which actions are logged is a design decision for the developer. It is advisable, however, to log at least every INSERT, UPDATE and DELETE statement with its own record. In production systems, this granularity should be weighed deliberately against log volume and write load — a single INSERT … SELECT can move millions of rows and still remains exactly one trace record. Besides the status fields already mentioned, the table also stores the number of rows affected, which lets the developer judge whether the statements did exactly what was expected.

[LL].[Error]

Errors detected in an ETL process belong in the log. A distinction has to be made between logging exceptions and logging data errors. The structure of this table is designed for logging data errors and contains columns in which every data error can be recorded completely and in an evaluable form. This article focuses on logging an ETL process rather than logging data errors. Logging the process is closely tied to explicit exception handling, and a separate section is dedicated to exception handling and the logging of exceptions.

The LL Schema Name

The schema name LL stands for Logging Layer. This schema holds all log tables and the stored procedures used for logging.

Table Description, Declaration and Data Model

The following sections describe the tables for logging the process as well as the table for logging errors. Finally, a diagram shows the data model of these tables.

Three notes up front: The duration of an execution derives, at the execution level, from the columns [Start] and [End], and at the component and trace levels from [CreatedOn] (written when the action starts) and [ModifiedOn] (set by the closing update via trigger). The timestamps use datetime with GETUTCDATE() for historical reasons — for a new design, datetime2 with SYSUTCDATETIME() would be the natural choice today, and its higher precision pays off precisely for trace records written in quick succession. And the audit columns [ModifiedOn]/[ModifiedBy] are maintained by an AFTER UPDATE trigger per table: that keeps the logging procedures lean, but costs an additional UPDATE on every status change and is a deliberate trade-off at very high log volumes. With the default database setting RECURSIVE_TRIGGERS OFF, the trigger's self-update does not recurse (measured on SQL Server 2022).

[LL].[Execution]

Columns

The table has the following columns:

Column	Data type	Null?	Description
`[Id]`	`bigint` (IDENTITY)	NOT NULL	Primary key, sequential run id.
`[Process]`	`nvarchar(max)`	NOT NULL	Name of the ETL process.
`[Start]`	`datetime`	NOT NULL	Start time of the run (UTC).
`[End]`	`datetime`	NULL	End time of the run.
`[DeltaStart]`	`datetime`	NULL	Start of the delta window (for incremental loads).
`[DeltaEnd]`	`datetime`	NULL	End of the delta window.
`[User]`	`nvarchar(128)`	NULL	Executing DB login (`SUSER_SNAME()`).
`[Machine]`	`nvarchar(128)`	NULL	Host the run was started from.
`[Version]`	`int`	NULL	Version number of the ETL process.
`[State]`	`nvarchar(128)`	NOT NULL	Status: processing / warning / success / error.
`[Success]`	`bit`	NOT NULL	0 = not (successfully) completed, 1 = successful.
`[CreatedOn]`	`datetime`	NOT NULL	Creation time, default `GETUTCDATE()`.
`[CreatedBy]`	`nvarchar(100)`	NOT NULL	Creating login, default `SUSER_SNAME()`.
`[ModifiedOn]`	`datetime`	NULL	Last modification, set by the update trigger.
`[ModifiedBy]`	`nvarchar(100)`	NULL	Login of the last modification, set by the update trigger.

Declaration

The table is created with the following statement:

-- ----------------------------------------------------------------------------
-- [LL].[Execution] - logging table (top level): exactly one record per ETL
-- run. Inserted at the start of the entry procedure with [State] = 'processing'
-- and updated to 'success' or 'error' at the end.
-- ----------------------------------------------------------------------------
CREATE TABLE [LL].[Execution]
(
    [Id]           bigint        IDENTITY (1, 1) NOT NULL
   ,[Process]      nvarchar(max)                 NOT NULL
   ,[Start]        datetime                      NOT NULL
   ,[End]          datetime                          NULL
   ,[DeltaStart]   datetime                          NULL
   ,[DeltaEnd]     datetime                          NULL
   ,[User]         nvarchar(128)                     NULL
   ,[Machine]      nvarchar(128)                     NULL
   ,[Version]      int                               NULL
   ,[State]        nvarchar(128)                 NOT NULL
   ,[Success]      bit                           NOT NULL
   ,[CreatedOn]    datetime
       CONSTRAINT [DF_LL_Execution_CreatedOn]
       DEFAULT (GETUTCDATE())                    NOT NULL
   ,[CreatedBy]    nvarchar(100)
       CONSTRAINT [DF_LL_Execution_CreatedBy]
       DEFAULT (SUSER_SNAME())                   NOT NULL
   ,[ModifiedOn]   datetime                          NULL
   ,[ModifiedBy]   nvarchar(100)                     NULL
   ,CONSTRAINT [PK_LL_Execution]
       PRIMARY KEY CLUSTERED ([Id] ASC)
   ,CONSTRAINT [CK_LL_Execution_StateSuccess]
       CHECK (   ([State] = N'processing' AND [Success] = 0)
              OR ([State] = N'warning'    AND [Success] IN (0, 1))
              OR ([State] = N'success'    AND [Success] = 1)
              OR ([State] = N'error'      AND [Success] = 0))
);
GO

CREATE TRIGGER [LL].[TR_LL_Execution_Update]
ON [LL].[Execution]
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [LL].[Execution]
       SET
           [ModifiedOn] = GETUTCDATE()
          ,[ModifiedBy] = SUSER_SNAME()
       FROM
          [LL].[Execution]
          INNER JOIN inserted
            ON inserted.[Id] = [LL].[Execution].[Id];
END;
GO

[LL].[Component]

Columns

Column	Data type	Null?	Description
`[Id]`	`bigint` (IDENTITY)	NOT NULL	Primary key of the component.
`[ExecutionId]`	`bigint`	NOT NULL	Foreign key to `[LL].[Execution]`.
`[Source]`	`nvarchar(5)`	NOT NULL	Type of source (e.g. SSIS, T-SQL).
`[Component]`	`nvarchar(128)`	NOT NULL	Name of the component (procedure, package, job).
`[Version]`	`int`	NULL	Version number of the component.
`[Entity]`	`nvarchar(128)`	NOT NULL	Target entity the component processes.
`[Step]`	`nvarchar(max)`	NOT NULL	Description of the step.
`[Description]`	`nvarchar(max)`	NULL	Additional description.
`[FileId]`	`bigint`	NULL	Reference to a processed file.
`[State]`	`nvarchar(128)`	NOT NULL	Status: processing / warning / success / error.
`[Success]`	`bit`	NOT NULL	0 = not (successfully) completed, 1 = successful.
`[CreatedOn]`	`datetime`	NOT NULL	Creation time, default `GETUTCDATE()`.
`[CreatedBy]`	`nvarchar(100)`	NOT NULL	Creating login, default `SUSER_SNAME()`.
`[ModifiedOn]`	`datetime`	NULL	Last modification, set by the update trigger.
`[ModifiedBy]`	`nvarchar(100)`	NULL	Login of the last modification, set by the update trigger.

Declaration

-- ----------------------------------------------------------------------------
-- [LL].[Component] - logging table (middle level): exactly one record per
-- component call (stored procedure, SSIS package, Talend job).
-- Foreign key to [LL].[Execution].
-- ----------------------------------------------------------------------------
CREATE TABLE [LL].[Component]
(
    [Id]           bigint        IDENTITY (1, 1) NOT NULL
   ,[ExecutionId]  bigint                        NOT NULL
   ,[Source]       nvarchar(5)                   NOT NULL
   ,[Component]    nvarchar(128)                 NOT NULL
   ,[Version]      int                               NULL
   ,[Entity]       nvarchar(128)                 NOT NULL
   ,[Step]         nvarchar(max)                 NOT NULL
   ,[Description]  nvarchar(max)                     NULL
   ,[FileId]       bigint                            NULL
   ,[State]        nvarchar(128)                 NOT NULL
   ,[Success]      bit                           NOT NULL
   ,[CreatedOn]    datetime
       CONSTRAINT [DF_LL_Component_CreatedOn]
       DEFAULT (GETUTCDATE())                    NOT NULL
   ,[CreatedBy]    nvarchar(100)
       CONSTRAINT [DF_LL_Component_CreatedBy]
       DEFAULT (SUSER_SNAME())                   NOT NULL
   ,[ModifiedOn]   datetime                          NULL
   ,[ModifiedBy]   nvarchar(100)                     NULL
   ,CONSTRAINT [PK_LL_Component]
       PRIMARY KEY CLUSTERED ([Id] ASC)
   ,CONSTRAINT [FK_LL_Component_ExecutionId]
       FOREIGN KEY ([ExecutionId])
       REFERENCES [LL].[Execution] ([Id])
   ,CONSTRAINT [CK_LL_Component_StateSuccess]
       CHECK (   ([State] = N'processing' AND [Success] = 0)
              OR ([State] = N'warning'    AND [Success] IN (0, 1))
              OR ([State] = N'success'    AND [Success] = 1)
              OR ([State] = N'error'      AND [Success] = 0))
);
GO

CREATE TRIGGER [LL].[TR_LL_Component_Update]
ON [LL].[Component]
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [LL].[Component]
       SET
           [ModifiedOn] = GETUTCDATE()
          ,[ModifiedBy] = SUSER_SNAME()
       FROM
          [LL].[Component]
          INNER JOIN inserted
            ON inserted.[Id] = [LL].[Component].[Id];
END;
GO

[LL].[Trace]

Columns

Column	Data type	Null?	Description
`[Id]`	`bigint` (IDENTITY)	NOT NULL	Primary key of the trace record.
`[ExecutionId]`	`bigint`	NOT NULL	Foreign key to `[LL].[Execution]`.
`[ComponentId]`	`bigint`	NOT NULL	Foreign key to `[LL].[Component]`.
`[Source]`	`nvarchar(5)`	NOT NULL	Type of source (e.g. SSIS, T-SQL).
`[Component]`	`nvarchar(128)`	NOT NULL	Name of the calling component.
`[Task]`	`nvarchar(128)`	NULL	Task name (e.g. SSIS task).
`[Entity]`	`nvarchar(128)`	NULL	Target entity of the action.
`[Step]`	`nvarchar(max)`	NOT NULL	Description of the step.
`[Description]`	`nvarchar(max)`	NULL	Additional description.
`[FileId]`	`bigint`	NULL	Reference to a processed file.
`[Action]`	`nvarchar(100)`	NULL	Type of action (insert / update / delete / …).
`[AffectedRows]`	`bigint`	NULL	Number of rows affected by the action.
`[State]`	`nvarchar(100)`	NOT NULL	Status: processing / warning / success / error.
`[Success]`	`bit`	NOT NULL	0 = not (successfully) completed, 1 = successful.
`[CreatedOn]`	`datetime`	NOT NULL	Creation time, default `GETUTCDATE()`.
`[CreatedBy]`	`nvarchar(100)`	NOT NULL	Creating login, default `SUSER_SNAME()`.
`[ModifiedOn]`	`datetime`	NULL	Last modification, set by the update trigger.
`[ModifiedBy]`	`nvarchar(128)`	NULL	Login of the last modification, set by the update trigger.

Declaration

-- ----------------------------------------------------------------------------
-- [LL].[Trace] - logging table (finest level): exactly one record per single
-- action (INSERT/UPDATE/DELETE, single SQL step, single task). Foreign keys
-- to [LL].[Execution] and [LL].[Component].
-- ----------------------------------------------------------------------------
CREATE TABLE [LL].[Trace]
(
    [Id]           bigint        IDENTITY (1, 1) NOT NULL
   ,[ExecutionId]  bigint                        NOT NULL
   ,[ComponentId]  bigint                        NOT NULL
   ,[Source]       nvarchar(5)                   NOT NULL
   ,[Component]    nvarchar(128)                 NOT NULL
   ,[Task]         nvarchar(128)                     NULL
   ,[Entity]       nvarchar(128)                     NULL
   ,[Step]         nvarchar(max)                 NOT NULL
   ,[Description]  nvarchar(max)                     NULL
   ,[FileId]       bigint                            NULL
   ,[Action]       nvarchar(100)                     NULL
   ,[AffectedRows] bigint                            NULL
   ,[State]        nvarchar(100)                 NOT NULL
   ,[Success]      bit                           NOT NULL
   ,[CreatedOn]    datetime
       CONSTRAINT [DF_LL_Trace_CreatedOn]
       DEFAULT (GETUTCDATE())                    NOT NULL
   ,[CreatedBy]    nvarchar(100)
       CONSTRAINT [DF_LL_Trace_CreatedBy]
       DEFAULT (SUSER_SNAME())                   NOT NULL
   ,[ModifiedOn]   datetime                          NULL
   ,[ModifiedBy]   nvarchar(128)                     NULL
   ,CONSTRAINT [PK_LL_Trace]
       PRIMARY KEY CLUSTERED ([Id] ASC)
   ,CONSTRAINT [FK_LL_Trace_ComponentId]
       FOREIGN KEY ([ComponentId])
       REFERENCES [LL].[Component] ([Id])
   ,CONSTRAINT [FK_LL_Trace_ExecutionId]
       FOREIGN KEY ([ExecutionId])
       REFERENCES [LL].[Execution] ([Id])
   ,CONSTRAINT [CK_LL_Trace_StateSuccess]
       CHECK (   ([State] = N'processing' AND [Success] = 0)
              OR ([State] = N'warning'    AND [Success] IN (0, 1))
              OR ([State] = N'success'    AND [Success] = 1)
              OR ([State] = N'error'      AND [Success] = 0))
);
GO

CREATE TRIGGER [LL].[TR_LL_Trace_Update]
ON [LL].[Trace]
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE [LL].[Trace]
       SET
           [ModifiedOn] = GETUTCDATE()
          ,[ModifiedBy] = SUSER_SNAME()
       FROM
          [LL].[Trace]
          INNER JOIN inserted
            ON inserted.[Id] = [LL].[Trace].[Id];
END;
GO

[LL].[Error]

As mentioned above, the structure of this table is designed for logging data errors, which are not the subject of this article. The table is, however, also used for logging exceptions. The following description covers only the columns required for logging an exception.

Columns

Column	Data type	Null?	Description
`[Id]`	`bigint` (IDENTITY)	NOT NULL	Primary key of the error record.
`[ExecutionId]`	`bigint`	NOT NULL	Foreign key to `[LL].[Execution]`.
`[ComponentId]`	`bigint`	NULL	Foreign key to `[LL].[Component]` (if known).
`[TraceId]`	`bigint`	NULL	Foreign key to `[LL].[Trace]` (if known).
`[ErrorType]`	`char(1)`	NOT NULL	Type of error (exception vs. data error).
`[Source]`	`nvarchar(5)`	NOT NULL	Type of source (e.g. SSIS, T-SQL).
`[Component]`	`nvarchar(128)`	NOT NULL	Component in which the error occurred.
`[TaskName]`	`nvarchar(128)`	NULL	Task name.
`[Entity]`	`nvarchar(128)`	NULL	Affected target entity.
`[Step]`	`nvarchar(max)`	NULL	Step in which the error occurred.
`[Description]`	`nvarchar(max)`	NULL	Error text (`ERROR_MESSAGE()`).
`[Number]`	`int`	NULL	Error number (`ERROR_NUMBER()`).
`[Line]`	`int`	NULL	Error line (`ERROR_LINE()`).
`[State]`	`nvarchar(max)`	NULL	Error state (`ERROR_STATE()`).
`[CreatedOn]`	`datetime`	NOT NULL	Creation time, default `GETUTCDATE()`.
`[CreatedBy]`	`nvarchar(100)`	NOT NULL	Creating login, default `SUSER_SNAME()`.

Declaration

-- ----------------------------------------------------------------------------
-- [LL].[Error] - logging table for exceptions and data errors.
-- In the context of this article we only use the exception columns;
-- the data-error columns (ID1Value/ID2Value/ID3Value/ErrorValue,
-- ID*ColumnName, FileName, FileId) are relevant for a follow-up article.
-- ----------------------------------------------------------------------------
CREATE TABLE [LL].[Error]
(
    [Id]              bigint        IDENTITY (1, 1) NOT NULL
   ,[ExecutionId]     bigint                        NOT NULL
   ,[ComponentId]     bigint                            NULL
   ,[TraceId]         bigint                            NULL
   ,[ErrorType]       char(1)                       NOT NULL
   ,[Source]          nvarchar(5)                   NOT NULL
   ,[Component]       nvarchar(128)                 NOT NULL
   ,[TaskName]        nvarchar(128)                     NULL
   ,[Entity]          nvarchar(128)                     NULL
   ,[Step]            nvarchar(max)                     NULL
   ,[SchemaName]      nvarchar(128)                     NULL
   ,[TableName]       nvarchar(128)                     NULL
   ,[FileId]          bigint                            NULL
   ,[ID1Value]        nvarchar(max)                     NULL
   ,[ID1ColumnName]   nvarchar(128)                     NULL
   ,[ID2Value]        nvarchar(max)                     NULL
   ,[ID2ColumnName]   nvarchar(128)                     NULL
   ,[ID3Value]        nvarchar(max)                     NULL
   ,[ID3ColumnName]   nvarchar(128)                     NULL
   ,[ErrorValue]      nvarchar(max)                     NULL
   ,[ErrorColumnName] nvarchar(128)                     NULL
   ,[FileName]        nvarchar(128)                     NULL
   ,[Description]     nvarchar(max)                     NULL
   ,[Number]          int                               NULL
   ,[Line]            int                               NULL
   ,[State]           nvarchar(max)                     NULL
   ,[CreatedOn]       datetime
       CONSTRAINT [DF_LL_Error_CreatedOn]
       DEFAULT (GETUTCDATE())                     NOT NULL
   ,[CreatedBy]       nvarchar(100)
       CONSTRAINT [DF_LL_Error_CreatedBy]
       DEFAULT (SUSER_SNAME())                    NOT NULL
   ,CONSTRAINT [PK_LL_Error]
       PRIMARY KEY CLUSTERED ([Id] ASC)
   ,CONSTRAINT [FK_LL_Error_ExecutionId]
       FOREIGN KEY ([ExecutionId])
       REFERENCES [LL].[Execution] ([Id])
   ,CONSTRAINT [FK_LL_Error_ComponentId]
       FOREIGN KEY ([ComponentId])
       REFERENCES [LL].[Component] ([Id])
   ,CONSTRAINT [FK_LL_Error_TraceId]
       FOREIGN KEY ([TraceId])
       REFERENCES [LL].[Trace] ([Id])
);
GO

Data Model

One deliberate redundancy in [LL].[Trace] stands out: [ExecutionId] sits there in addition to [ComponentId], so that run-level evaluations can skip the join through [LL].[Component]. The database does not enforce the consistency of that pair — a trace record could in theory point to a component of a different run. Anyone who wants to enforce it adds a composite foreign key on [Component]([Id], [ExecutionId]).

ETL Process Execution Status

The three process-log tables have two columns, [State] and [Success], which store the current status of the ETL process, of a component’s execution or of a specific action — for example an INSERT, UPDATE or DELETE. The current status is held in the [State] column with the values processing, warning, success and error. Success is stored in the [Success] column as 1. Only the combination of both values reveals the current status reliably. The following combinations are valid:

`[State]`	`[Success]`	Meaning
processing	0	Action, component or run has started and is still running.
warning	0	Finished, but with a warning — not counted as success.
warning	1	Finished with a warning, yet counted as success.
success	1	Completed successfully.
error	0	Aborted with an error.

Valid combinations of the [State] and [Success] columns

Other combinations of status values are not allowed. This is enforced twice: the process-logging procedures raise an exception if an invalid combination is passed in, and a CHECK constraint per table additionally rejects invalid combinations at the database level (error 547, measured on SQL Server 2022).

Logging Procedures

The following procedures, among others, are available for inserting and updating log records in the tables above:

Procedure	Schema	Purpose
`spInsertExecution`	`LL`	Opens the execution log (top level) at the start of the run.
`spUpdateExecution`	`LL`	Closes the execution log with status success or error.
`spInsertComponent`	`LL`	Opens a component log for a component.
`spUpdateComponentSuccess`	`LL`	Closes a component log successfully.
`spUpdateComponentError`	`LL`	Closes a component log in the error case.
`spInsertTrace`	`LL`	Writes a trace record for a single action.
`spUpdateTrace`	`LL`	Updates a trace record (general).
`spUpdateTraceSuccess`	`LL`	Updates a trace record to success / 1.
`spUpdateTraceError`	`LL`	Updates a trace record to error / 0.
`spInsertErrorException`	`LL`	Writes a caught exception to `[LL].[Error]`.

Procedures for process logging

In essence these procedures perform an INSERT or UPDATE on the log tables. The values to be logged are passed as parameters. The procedures validate the parameters passed in and raise an exception in case of invalid parameters. For parameter validation and for raising errors consistently, they use the two helpers [dbo].[spRaiseError] and [dbo].[fnIsNullOrEmpty].

The following code examples show the procedures [LL].[spInsertTrace], [LL].[spUpdateTrace] and [LL].[spUpdateTraceSuccess]. In addition, [LL].[spInsertExecution] stands in for all the remaining procedures, which follow the same pattern: validate the parameters, run the INSERT or UPDATE, return the generated id.

[LL].[spInsertTrace]

-- ----------------------------------------------------------------------------
-- [LL].[spInsertTrace] - inserts a trace record into [LL].[Trace]
-- and returns the generated id via @p_traceId.
-- ----------------------------------------------------------------------------
-- Parameters:
--    @p_executionId    bigint        Execution id of the current ETL run
--    @p_componentId    bigint        Component id of the calling component log
--    @p_traceId        bigint OUTPUT Id of the newly inserted trace record
--    @p_source          nvarchar(5)   Source system (SSIS, T-SQL, ...)
--    @p_component       nvarchar(128) Name of the calling component
--    @p_task            nvarchar(128) Task name (e.g. SSIS task), optional
--    @p_entity          nvarchar(128) Target entity, optional
--    @p_step            nvarchar(max) Description of the step
--    @p_description     nvarchar(max) Additional description, optional
--    @p_fileId         bigint        Reference to [LL].[FileList].[Id], optional
--    @p_action          nvarchar(100) Action label (Insert/Update/Delete/...)
--    @p_affectedRows   bigint        Number of rows affected
--    @p_state           nvarchar(100) processing / warning / success / error
--    @p_success         bit           0 = processing/warning/error, 1 = success
-- ----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [LL].[spInsertTrace]
    @p_executionId    AS bigint
   ,@p_componentId    AS bigint
   ,@p_traceId        AS bigint OUTPUT
   ,@p_source         AS nvarchar(5)
   ,@p_component      AS nvarchar(128)
   ,@p_task           AS nvarchar(128)   = NULL
   ,@p_entity         AS nvarchar(128)   = NULL
   ,@p_step           AS nvarchar(max)
   ,@p_description    AS nvarchar(max)   = NULL
   ,@p_fileId         AS bigint          = NULL
   ,@p_action         AS nvarchar(100)   = NULL
   ,@p_affectedRows   AS bigint          = NULL
   ,@p_state          AS nvarchar(100)
   ,@p_success        AS bit
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @component AS nvarchar(128);
    DECLARE @table     AS TABLE ([Id] bigint);
    DECLARE @message   AS nvarchar(max);

    SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);

    BEGIN TRY
        -- Parameter checks
        IF (@p_executionId IS NULL)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_executionId'' is NULL.', @component;
            RETURN 0;
        END;

        IF (@p_componentId IS NULL)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_componentId'' is NULL.', @component;
            RETURN 0;
        END;

        IF ([dbo].[fnIsNullOrEmpty](@p_source, 1) <> 0)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_source'' is either NULL or an empty string.', @component;
            RETURN 1;
        END;

        IF ([dbo].[fnIsNullOrEmpty](@p_component, 1) <> 0)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_component'' is either NULL or an empty string.', @component;
            RETURN 1;
        END;

        IF ([dbo].[fnIsNullOrEmpty](@p_step, 1) <> 0)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_step'' is either NULL or an empty string.', @component;
            RETURN 1;
        END;

        IF ([dbo].[fnIsNullOrEmpty](@p_state, 1) <> 0)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_state'' is either NULL or an empty string.', @component;
            RETURN 1;
        END;

        IF (@p_success IS NULL)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_success'' is NULL.', @component;
            RETURN 1;
        END;

        -- Validate the State/Success combination (whitelist)
        IF NOT (   (@p_state = N'processing' AND @p_success = 0)
                OR (@p_state = N'warning'    AND @p_success IN (0, 1))
                OR (@p_state = N'success'    AND @p_success = 1)
                OR (@p_state = N'error'      AND @p_success = 0))
        BEGIN
            SET @message = CONCAT(N'Invalid state ''', @p_state, N''' for p_success = ''', CAST(@p_success AS nvarchar(100)), N'''.');
            EXEC [dbo].[spRaiseError] @message, @component;
            RETURN 1;
        END;

        -- Write the trace record
        INSERT INTO [LL].[Trace]
        (
             [ExecutionId]
            ,[ComponentId]
            ,[Source]
            ,[Component]
            ,[Task]
            ,[Entity]
            ,[Step]
            ,[Description]
            ,[FileId]
            ,[Action]
            ,[AffectedRows]
            ,[State]
            ,[Success]
        )
        OUTPUT Inserted.[Id] INTO @table
        VALUES
        (
             @p_executionId
            ,@p_componentId
            ,@p_source
            ,@p_component
            ,@p_task
            ,@p_entity
            ,@p_step
            ,CASE WHEN @p_description IS NULL OR DATALENGTH(@p_description) = 0
                  THEN NULL ELSE @p_description END
            ,@p_fileId
            ,@p_action
            ,@p_affectedRows
            ,@p_state
            ,@p_success
        );

        SELECT @p_traceId = [Id] FROM @table;

        RETURN 0;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END;
GO

[LL].[spUpdateTrace]

-- ----------------------------------------------------------------------------
-- [LL].[spUpdateTrace] - updates an existing trace record
-- with Description, Action, AffectedRows, State and Success.
-- ----------------------------------------------------------------------------
-- Parameters:
--    @p_traceId        bigint        Id of the trace record to update
--    @p_description     nvarchar(max) Description of the result
--    @p_action          nvarchar(100) Action label, optional
--    @p_affectedRows   bigint        Number of rows affected
--    @p_state           nvarchar(100) processing / warning / success / error
--    @p_success         bit           0 = processing/warning/error, 1 = success
-- ----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [LL].[spUpdateTrace]
    @p_traceId         AS bigint
   ,@p_description     AS nvarchar(max)
   ,@p_action          AS nvarchar(100) = NULL
   ,@p_affectedRows    AS bigint
   ,@p_state           AS nvarchar(100)
   ,@p_success         AS bit
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @component AS nvarchar(128);
    DECLARE @tempId    AS bigint;
    DECLARE @message   AS nvarchar(max);

    SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);

    BEGIN TRY
        -- Parameter checks
        IF (@p_traceId IS NULL)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_traceId'' is NULL.', @component;
            RETURN 1;
        END;

        IF (@p_success IS NULL)
        BEGIN
            SET @message = N'The parameter ''p_success'' is NULL.';
            EXEC [dbo].[spRaiseError] @message, @component;
            RETURN 1;
        END;

        IF ([dbo].[fnIsNullOrEmpty](@p_state, 1) <> 0)
        BEGIN
            SET @message = N'The parameter ''p_state'' is either NULL or an empty string.';
            EXEC [dbo].[spRaiseError] @message, @component;
            RETURN 1;
        END;

        -- Validate the State/Success combination (whitelist)
        IF NOT (   (@p_state = N'processing' AND @p_success = 0)
                OR (@p_state = N'warning'    AND @p_success IN (0, 1))
                OR (@p_state = N'success'    AND @p_success = 1)
                OR (@p_state = N'error'      AND @p_success = 0))
        BEGIN
            SET @message = CONCAT(N'Invalid state ''', @p_state, N''' for p_success = ''', CAST(@p_success AS nvarchar(100)), N'''.');
            EXEC [dbo].[spRaiseError] @message, @component;
            RETURN 1;
        END;

        -- Check that the trace record exists
        SELECT @tempId = [Id]
        FROM   [LL].[Trace]
        WHERE  [Id] = @p_traceId;

        IF (@tempId IS NULL)
        BEGIN
            SET @message = N'A record with [Id] = ''' + CAST(@p_traceId AS nvarchar(max)) + N''' could not be found.';
            EXEC [dbo].[spRaiseError] @message, @component;
            RETURN 1;
        END;

        -- Update the trace record
        UPDATE [LL].[Trace]
           SET
               [Description]  = CASE
                                  WHEN (@p_description IS NULL OR DATALENGTH(@p_description) = 0)
                                   AND ([Description]   IS NULL OR DATALENGTH([Description])   = 0)
                                  THEN NULL ELSE @p_description END
              ,[Action]       = @p_action
              ,[AffectedRows] = @p_affectedRows
              ,[State]        = @p_state
              ,[Success]      = @p_success
         WHERE [Id] = @p_traceId;

        RETURN 0;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END;
GO

[LL].[spUpdateTraceSuccess]

-- ----------------------------------------------------------------------------
-- [LL].[spUpdateTraceSuccess] - convenience wrapper around spUpdateTrace for
-- the common case "action finished successfully" (State = 'success',
-- Success = 1). Saves setting both fields on every call.
-- ----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [LL].[spUpdateTraceSuccess]
    @p_traceId         AS bigint
   ,@p_description     AS nvarchar(max)
   ,@p_action          AS nvarchar(100) = NULL
   ,@p_affectedRows    AS bigint
AS
BEGIN
    SET NOCOUNT ON;

    EXEC [LL].[spUpdateTrace]
         @p_traceId
        ,@p_description
        ,@p_action
        ,@p_affectedRows
        ,N'success'
        ,1;
END;
GO

[LL].[spInsertExecution]

The procedure below was not shown in detail in the original example, but it stands in for all the remaining logging procedures: once you understand how it is built, you can derive spInsertComponent, spUpdateComponent*, spUpdateTraceError and spInsertErrorException analogously — the only difference is the column assignment.

-- ----------------------------------------------------------------------------
-- [LL].[spInsertExecution] - reference for the remaining logging procedures.
--
-- Structurally identical to [LL].[spInsertTrace]: parameter checks -> INSERT
-- -> id returned via OUTPUT variable. Once you understand the pattern, you can
-- spInsertComponent, spUpdateComponent*, spUpdateTraceError,
-- derive spInsertErrorException, spUpdateExecution analogously - only the
-- column assignment differs.
-- ----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [LL].[spInsertExecution]
    @p_executionId    AS bigint OUTPUT
   ,@p_process        AS nvarchar(max)
   ,@p_version        AS int = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @component AS nvarchar(128);
    DECLARE @table     AS TABLE ([Id] bigint);

    SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);

    BEGIN TRY
        IF ([dbo].[fnIsNullOrEmpty](@p_process, 1) <> 0)
        BEGIN
            EXEC [dbo].[spRaiseError] N'The parameter ''p_process'' is either NULL or an empty string.', @component;
            RETURN 1;
        END;

        INSERT INTO [LL].[Execution]
        (
             [Process]
            ,[Start]
            ,[User]
            ,[Machine]
            ,[Version]
            ,[State]
            ,[Success]
        )
        OUTPUT Inserted.[Id] INTO @table
        VALUES
        (
             @p_process
            ,GETUTCDATE()
            ,SUSER_SNAME()
            ,HOST_NAME()
            ,@p_version
            ,N'processing'
            ,0
        );

        SELECT @p_executionId = [Id] FROM @table;

        RETURN 0;
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH;
END;
GO

Exception Handling

To ensure that an ETL process ends in an orderly way rather than aborting hard without proper logging, explicit exception handling is required. Orderly means in this case that the process catches a raised exception, performs an UPDATE on the relevant log record in the tables [LL].[Execution], [LL].[Component] and [LL].[Trace] — wherever applicable — setting [State] = error and [Success] = 0, and logs the exception in the table [LL].[Error]. The following diagram shows the basic mechanics of exception handling:

Exception handling

Only after logging has completed may the process re-raise the exception to the caller, which would then abort the calling process hard. Re-raising the exception to the caller is not strictly necessary, however, once the error has been logged and the relevant log records have been updated to [State] = error and [Success] = 0.

Exception handling is required at least at the top level of an ETL process’s execution, which is logged in the table [LL].[Execution].

One point deserves particular attention here: the interplay of logging and transactions. If the ETL work runs inside an open transaction and the logging procedures write in the same session, a ROLLBACK in the CATCH block also rolls back the log records already written — of all cases, it is the failure case that loses its log. If an error additionally puts the transaction into the uncommittable state (XACT_STATE() = -1, the usual case for runtime errors in a TRY block under SET XACT_ABORT ON), an INSERT into the log tables fails in the CATCH block with error 3930 until the ROLLBACK has been executed (measured on SQL Server 2022). The order in the CATCH block is therefore: roll back the transaction first, then log. The example in this article works without an explicit transaction and deliberately sidesteps the problem.

And the error logging itself is not guaranteed to succeed. If one of the logging calls in the CATCH block fails — say, on missing permissions or a full log —, its exception masks the original error. Anyone who wants to guard against that wraps the logging calls in the CATCH block in a TRY/CATCH of their own: the final THROW then re-raises the original exception in every case.

The code example below shows exception handling including the logging into the tables [LL].[Execution] and [LL].[Error] in line with the diagram above:

-- ----------------------------------------------------------------------------
-- Exception-handling skeleton: shows the complete pattern of
-- TRY/CATCH + insert error + update execution + THROW. The order in the
-- CATCH block is crucial - log first, then THROW.
-- ----------------------------------------------------------------------------
-- Execution logging variables
DECLARE @executionId     AS bigint;
DECLARE @processName     AS nvarchar(max);
DECLARE @version         AS int;

-- Error logging variables
DECLARE @componentId     AS bigint;
DECLARE @traceId         AS bigint;
DECLARE @source          AS nvarchar(5);
DECLARE @component       AS nvarchar(128);
DECLARE @task            AS nvarchar(128);
DECLARE @entity          AS nvarchar(128);
DECLARE @step            AS nvarchar(128);
DECLARE @state           AS nvarchar(128);
DECLARE @success         AS bit;

-- Exception variables (lowercase by convention)
DECLARE @error_message   AS nvarchar(max);
DECLARE @error_number    AS int;
DECLARE @error_line      AS int;
DECLARE @error_state     AS nvarchar(max);

BEGIN TRY
    -- Initialize the execution logging variables
    SET @processName  = N'Name of ETL-Process';
    SET @version      = 123;

    -- Initialize the error logging variables
    SET @componentId  = NULL;
    SET @traceId      = NULL;
    SET @source       = N'sql';
    SET @component    = N'Procedure Name';
    SET @task         = NULL;
    SET @entity       = N'Any Entity';
    SET @step         = N'Do something';

    EXEC [LL].[spInsertExecution] @executionId OUTPUT, @processName, @version;

    -- Workload (the actual ETL work happens here)
    THROW 50001, N'Any Exception', 1;

    SET @state   = N'success';
    SET @success = 1;
    EXEC [LL].[spUpdateExecution] @executionId, @state, @success;
END TRY
BEGIN CATCH
    SET @error_message = ERROR_MESSAGE();
    SET @error_number  = ERROR_NUMBER();
    SET @error_line    = ERROR_LINE();
    SET @error_state   = ERROR_STATE();

    SET @state   = N'error';
    SET @success = 0;

    IF @executionId IS NOT NULL
    BEGIN
        EXEC [LL].[spInsertErrorException]
             @executionId
            ,@componentId
            ,@traceId
            ,@source
            ,@component
            ,@task
            ,@entity
            ,@step
            ,@error_number
            ,@error_message
            ,@error_line
            ,@error_state;

        EXEC [LL].[spUpdateExecution] @executionId, @state, @success;
    END;

    THROW;
END CATCH;

Example: Logging and Exception Handling in an ETL Process

After these explanations of the basics of exception handling, the following diagram shows the exception handling of a simple, compact, but complete ETL process:

Example of exception handling and logging for an example ETL process

This example shows the logging of an ETL process built entirely from stored procedures, consisting of five stored procedures. The entry procedure [T2].[spETLProcess] calls the two procedures [T2].[spDoSomething_1] and [T2].[spDoSomething_2]. The first one logs an INSERT, an UPDATE and a DELETE step each, while the second procedure executes two further procedures, [T2].[spDoSomething_2_1] and [T2].[spDoSomething_2_2]. These procedures, too, log an INSERT, an UPDATE and a DELETE step each — the DML statements themselves are deliberately only sketched as placeholder comments in the demo procedures. During the execution of the DELETE statement in the procedure [T2].[spDoSomething_2_2], an exception is raised that leads to an orderly termination of the ETL process.

Procedure calls of the example ETL process

Since showing all procedures would lead to repetition, only the entry procedure [T2].[spETLProcess] and the procedure [T2].[spDoSomething_2_2] are shown here. All remaining worker procedures use the same TRY/CATCH pattern as [T2].[spDoSomething_2_2].

Two decisions in the example are deliberate. The worker procedures re-raise a caught exception via THROW, so that every level can close its own log record. The entry procedure [T2].[spETLProcess], by contrast, consumes the exception after logging is complete — the run ends in an orderly way with [State] = error, without aborting the caller hard. If an orchestrator such as SQL Server Agent is supposed to see the failure, an additional THROW belongs at the end of its CATCH block, otherwise the job ends as spuriously successful from the scheduler's point of view.

And when passing on the row count, order matters: @@ROWCOUNT is captured into the variable @affectedRows immediately after the respective DML statement, and only that variable is passed to [LL].[spUpdateTraceSuccess]. After an EXEC, @@ROWCOUNT does not carry the business row count of the called component but the value of its last executed statement, and even a simple variable assignment sets the value to 1 (measured on SQL Server 2022). The orchestration steps in [T2].[spETLProcess] therefore log no row count of their own — the workers deliver it in their trace records. In the demo workers, SET @affectedRows = @@ROWCOUNT; deliberately remains part of the placeholder comment, and the placeholder steps pass NULL, because without real DML there would be no business row count to capture. For statements beyond 2 billion rows, ROWCOUNT_BIG() takes the place of @@ROWCOUNT. The column [AffectedRows] is sized as bigint for that.

[T2].[spETLProcess]

-- ----------------------------------------------------------------------------
-- [T2].[spETLProcess] - example entry procedure of the ETL process.
-- Calls two worker procedures spDoSomething_1 and spDoSomething_2
-- and logs both calls in [LL].[Trace].
-- ----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [T2].[spETLProcess]
AS
BEGIN
    SET NOCOUNT ON;

    -- Error variables
    DECLARE @error_message  AS nvarchar(max);
    DECLARE @error_number   AS int;
    DECLARE @error_line     AS int;
    DECLARE @error_state    AS nvarchar(max);

    -- Logging variables
    DECLARE @component      AS nvarchar(128);
    DECLARE @task           AS nvarchar(128);
    DECLARE @source         AS nvarchar(5);
    DECLARE @step           AS nvarchar(max);
    DECLARE @entity         AS nvarchar(max);
    DECLARE @message        AS nvarchar(max);

    DECLARE @traceId       AS bigint;
    DECLARE @componentId   AS bigint;
    DECLARE @executionId   AS bigint;

    DECLARE @description    AS nvarchar(max);
    DECLARE @affectedRows   AS bigint;
    DECLARE @action         AS nvarchar(100);
    DECLARE @state          AS nvarchar(100);
    DECLARE @success        AS bit;

    SET @message       = NULL;
    SET @description   = NULL;
    SET @affectedRows  = 0;

    SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
    SET @source    = N'T-SQL';
    SET @entity    = N'[].[]';

    BEGIN TRY
        -- Open the execution log
        EXEC [LL].[spInsertExecution] @executionId OUTPUT, N'ETL process', 123;

        -- Open the component log
        SET @step        = N'Orchestrate ETL process';
        SET @description = N'';
        EXEC [LL].[spInsertComponent]
             @executionId, @componentId OUTPUT, @source, @component, NULL, @entity, @step, @description;

        -- ----------------------------------------------------------------
        -- Call [T2].[spDoSomething_1]
        -- ----------------------------------------------------------------
        SET @task        = NULL;
        SET @step        = N'Execute [T2].[spDoSomething_1]';
        SET @action      = N'execute';
        SET @description = NULL;
        SET @state       = N'processing';
        SET @success     = 0;
        EXEC [LL].[spInsertTrace]
             @executionId, @componentId, @traceId OUTPUT
            ,@source, @component, @task, @entity, @step, @description
            ,NULL, @action, NULL, @state, @success;

        EXEC [T2].[spDoSomething_1] @executionId;

        -- Orchestration step without a row count of its own - the DML rows
        -- are logged by the workers in their own trace records
        EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, NULL;

        -- ----------------------------------------------------------------
        -- Call [T2].[spDoSomething_2]
        -- ----------------------------------------------------------------
        SET @task        = NULL;
        SET @step        = N'Execute [T2].[spDoSomething_2]';
        SET @action      = N'execute';
        SET @description = NULL;
        SET @state       = N'processing';
        SET @success     = 0;
        EXEC [LL].[spInsertTrace]
             @executionId, @componentId, @traceId OUTPUT
            ,@source, @component, @task, @entity, @step, @description
            ,NULL, @action, NULL, @state, @success;

        EXEC [T2].[spDoSomething_2] @executionId;

        EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, NULL;

        -- Close the component log
        EXEC [LL].[spUpdateComponentSuccess] @componentId, @description;

        -- Close the execution log
        SET @state   = N'success';
        SET @success = 1;
        EXEC [LL].[spUpdateExecution] @executionId, @state, @success;
    END TRY
    BEGIN CATCH
        SET @error_message = ERROR_MESSAGE();
        SET @error_number  = ERROR_NUMBER();
        SET @error_line    = ERROR_LINE();
        SET @error_state   = ERROR_STATE();

        IF @executionId IS NOT NULL
        BEGIN
            EXEC [LL].[spInsertErrorException]
                 @executionId, @componentId, @traceId, @source, @component
                ,NULL, NULL, @step
                ,@error_number, @error_message, @error_line, @error_state;

            IF @traceId     IS NOT NULL EXEC [LL].[spUpdateTraceError]     @traceId,     @description;
            IF @componentId IS NOT NULL EXEC [LL].[spUpdateComponentError] @componentId, @description;

            SET @state   = N'error';
            SET @success = 0;
            EXEC [LL].[spUpdateExecution] @executionId, @state, @success;
        END;
    END CATCH;
END;
GO

[T2].[spDoSomething_2_2]

-- ----------------------------------------------------------------------------
-- [T2].[spDoSomething_2_2] - deepest worker procedure in the example tree.
-- Called by [T2].[spDoSomething_2] (see diagram 041007.png).
-- Simulates an INSERT, an UPDATE and a DELETE - the DELETE raises an
-- exception to demonstrate the exception-handling pattern.
-- ----------------------------------------------------------------------------
CREATE OR ALTER PROCEDURE [T2].[spDoSomething_2_2]
(
    @p_executionId  AS bigint
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Error variables
    DECLARE @error_message  AS nvarchar(max);
    DECLARE @error_number   AS int;
    DECLARE @error_line     AS int;
    DECLARE @error_state    AS nvarchar(max);

    -- Logging variables
    DECLARE @component      AS nvarchar(128);
    DECLARE @task           AS nvarchar(128);
    DECLARE @source         AS nvarchar(5);
    DECLARE @step           AS nvarchar(max);
    DECLARE @entity         AS nvarchar(max);
    DECLARE @message        AS nvarchar(max);

    DECLARE @traceId       AS bigint;
    DECLARE @componentId   AS bigint;

    DECLARE @description    AS nvarchar(max);
    DECLARE @affectedRows   AS bigint;
    DECLARE @action         AS nvarchar(100);
    DECLARE @state          AS nvarchar(100);
    DECLARE @success        AS bit;

    SET @message       = NULL;
    SET @description   = NULL;
    SET @affectedRows  = 0;

    SET @component = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID);
    SET @source    = N'T-SQL';
    SET @entity    = N'[].[]';

    BEGIN TRY
        IF (@p_executionId IS NULL)
        BEGIN
            SET @message = N'The parameter ''p_executionId'' is NULL.';
            EXEC [dbo].[spRaiseError] @message, @component;
            RETURN -1;
        END;

        -- Open the component log
        SET @step        = N'Do something';
        SET @description = N'';
        EXEC [LL].[spInsertComponent]
             @p_executionId, @componentId OUTPUT, @source, @component, NULL, @entity, @step, @description;

        -- ----------------------------------------------------------------
        -- INSERT (succeeds)
        -- ----------------------------------------------------------------
        SET @task        = NULL;
        SET @step        = N'Insert data';
        SET @action      = N'insert';
        SET @description = NULL;
        SET @state       = N'processing';
        SET @success     = 0;
        EXEC [LL].[spInsertTrace]
             @p_executionId, @componentId, @traceId OUTPUT
            ,@source, @component, @task, @entity, @step, @description
            ,NULL, @action, NULL, @state, @success;

        -- An INSERT statement would go here, immediately followed by:
        -- SET @affectedRows = @@ROWCOUNT;

        EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, NULL;

        -- ----------------------------------------------------------------
        -- UPDATE (succeeds)
        -- ----------------------------------------------------------------
        SET @task        = NULL;
        SET @step        = N'Update data';
        SET @action      = N'update';
        SET @description = NULL;
        SET @state       = N'processing';
        SET @success     = 0;
        EXEC [LL].[spInsertTrace]
             @p_executionId, @componentId, @traceId OUTPUT
            ,@source, @component, @task, @entity, @step, @description
            ,NULL, @action, NULL, @state, @success;

        -- An UPDATE statement would go here, immediately followed by:
        -- SET @affectedRows = @@ROWCOUNT;

        EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, NULL;

        -- ----------------------------------------------------------------
        -- DELETE (deliberately raises an exception)
        -- ----------------------------------------------------------------
        SET @task        = NULL;
        SET @step        = N'Delete data';
        SET @action      = N'delete';
        SET @description = NULL;
        SET @state       = N'processing';
        SET @success     = 0;
        EXEC [LL].[spInsertTrace]
             @p_executionId, @componentId, @traceId OUTPUT
            ,@source, @component, @task, @entity, @step, @description
            ,NULL, @action, NULL, @state, @success;

        -- A DELETE statement would go here - artificially raised exception:
        THROW 50001, N'Exception in [T2].[spDoSomething_2_2]', 1;

        EXEC [LL].[spUpdateTraceSuccess] @traceId, @description, @action, NULL;

        -- Close the component log
        EXEC [LL].[spUpdateComponentSuccess] @componentId, @description;
    END TRY
    BEGIN CATCH
        SET @error_message = ERROR_MESSAGE();
        SET @error_number  = ERROR_NUMBER();
        SET @error_line    = ERROR_LINE();
        SET @error_state   = ERROR_STATE();

        IF @p_executionId IS NOT NULL
        BEGIN
            EXEC [LL].[spInsertErrorException]
                 @p_executionId, @componentId, @traceId, @source, @component
                ,NULL, NULL, @step
                ,@error_number, @error_message, @error_line, @error_state;

            IF @traceId     IS NOT NULL EXEC [LL].[spUpdateTraceError]     @traceId,     @description;
            IF @componentId IS NOT NULL EXEC [LL].[spUpdateComponentError] @componentId, @description;
        END;

        THROW;
    END CATCH;
END;
GO

The following script contains the statement to execute the ETL process. To obtain a compact process log, the log tables are cleared beforehand. This clearing, including DBCC CHECKIDENT, is a pure demo reset for reproducible ids — in live operation, log tables are not emptied. The closing SELECT statements produce the result shown at the beginning.

-- ----------------------------------------------------------------------------
-- Example run: execute the ETL process and evaluate the log.
--
-- Block 1 clears the log tables (TRUNCATE / DELETE + RESEED),
-- block 2 starts the example ETL process, block 3 shows the result.
-- The SELECT statements return exactly the three result sets shown at the
-- start of the article (tables 041001, 041002, 041003).
-- ----------------------------------------------------------------------------

-- 01: Clear the log tables
TRUNCATE TABLE [LL].[Error];
TRUNCATE TABLE [LL].[Trace];
DELETE FROM    [LL].[Component];
DELETE FROM    [LL].[Execution];
DBCC CHECKIDENT (N'[LL].[Component]', RESEED, 0);
DBCC CHECKIDENT (N'[LL].[Execution]', RESEED, 0);

-- 02: Start the ETL process
EXEC [T2].[spETLProcess];

-- 03: Evaluate the log
SELECT * FROM [LL].[Execution];
SELECT * FROM [LL].[Component];
SELECT * FROM [LL].[Trace];

Conclusion

ETL processes are data-driven processes. If data arrives that was not expected, there is a high probability that either not all data — or even wrong data — ends up in the target system. Something this article mentions only in passing: at the lowest logging level, the number of rows affected can also be logged. Knowing the number of expected versus actually processed rows is a good indicator of the success or failure of an ETL process.

This approach thus supports the development of robust ETL processes and at the same time makes the data quality of a run assessable.

What goes without saying in software development is often neglected when building ETL processes: explicit exception handling. The exception handling presented here first ensures that a process ends in an orderly way in the event of an error. But when is there actually an error? And does an error always have to abort the process? Combined with the knowledge of the expected versus actually processed number of rows, genuine error handling can be implemented.

The approach presented here is, in a way, an invitation to engage more with the data and the expected result. The log — and in particular the knowledge of the rows processed — helps the developer judge the correctness of the work already during the development of the ETL process.

The procedures presented here merely provide a toolkit for logging. Used correctly, they produce a readable, evaluable log.

FAQ

What is the difference between [LL].[Component] and [LL].[Trace]?

Both log steps of a run, but at different levels of granularity. [LL].[Component] holds exactly one record per called component (stored procedure, SSIS package, Talend job) — that is, "which building block ran when and with what status?". [LL].[Trace] goes one level deeper and logs the individual actions within a component (typically every INSERT, UPDATE and DELETE) together with the number of rows affected. A component therefore usually has several trace entries.

Why a dedicated [LL] schema instead of putting the logging tables in the default schema?

The LL schema ("Logging Layer") cleanly separates the logging infrastructure from the business data. This has practical advantages: permissions can be granted precisely (for example, write access to LL only for the ETL procedures), retention and backup of the logs can be controlled separately, and in scripts it is immediately clear that an object belongs to logging. It also prevents the logging tables from colliding with business tables of the same name.

Can the pattern be implemented with Postgres instead of SQL Server?

Yes — the concept is database-neutral: the logging tables and the three-tier structure stay the same. The concrete implementation, however, has to be adapted to PL/pgSQL semantics. Instead of THROW, you re-raise the exception in PL/pgSQL with RAISE, TRY/CATCH becomes a BEGIN … EXCEPTION WHEN OTHERS THEN … END block, and @@ROWCOUNT corresponds to GET DIAGNOSTICS <var> = ROW_COUNT. One important difference remains: an EXCEPTION block forms an implicit subtransaction in PL/pgSQL. If it catches an error, all changes of the block are rolled back — including log records already written (measured on PostgreSQL 16). The error logging therefore belongs in the EXCEPTION branch — what the handler itself writes is kept. If you want to measure the execution time of individual statements without your own trace table, you can additionally draw on pg_stat_statements, as an aggregated statement statistic without the run-level correlation via execution, component and trace ids.

How does this pattern differ from SQL Server Audit or pgAudit?

SQL Server Audit and pgAudit are database-side auditing mechanisms: they log database and server events — logins, DDL, also object access such as SELECT or INSERT — at the infrastructure level, without the business correlation of an ETL run via execution, component and trace ids. The pattern presented here, by contrast, is application logging — it records the business view of the ETL process: which run, which component, which action, how many rows, success or failure. The two complement each other. For the question "did the ETL run do the right thing functionally?" application logging is the appropriate tool.

Why are log entries missing after an error with a rollback?

Because the log INSERTs ran in the same transaction as the ETL work. A ROLLBACK in the CATCH block then also rolls back the log records already written. If the error puts the transaction into the uncommittable state (XACT_STATE() = -1, the usual case for runtime errors in a TRY block under SET XACT_ABORT ON), an INSERT in the CATCH block even fails with error 3930 as long as the ROLLBACK is outstanding. In the CATCH block, the rule is therefore: roll back the transaction first, then log — or keep the logging outside the surrounding transaction in the first place (see the Exception Handling section).

How do you log the number of rows affected with @@ROWCOUNT?

Capture the value of @@ROWCOUNT into a variable immediately after the INSERT, UPDATE or DELETE statement and pass that variable to the logging procedure — every further statement in between overwrites the value. In this pattern, the number ends up in the [AffectedRows] column of the trace record via [LL].[spUpdateTraceSuccess]. For statements with more than 2 billion rows, ROWCOUNT_BIG() takes the place of @@ROWCOUNT. The column [AffectedRows] is sized as bigint accordingly.

How do I integrate an SSIS package into the pattern?

An SSIS package is treated like any other component: at the start it calls [LL].[spInsertComponent] (for example from an Execute SQL Task or a Script Task), performs its tasks and finishes with [LL].[spUpdateComponentSuccess], or with [LL].[spUpdateComponentError] in the error case. The returned ComponentId is passed along through an SSIS variable so that downstream trace calls can reference it. This way the package appears seamlessly alongside the T-SQL components in the same log.

This article is part of the ETL design-pattern cluster:

Data Quality in an ETL Process — the root of the article series.
Design Pattern // The Architecture of an ETL Process — provides the schema T2 used in the example of this article.
ETL vs. ELT — How to Tell Which Pattern You Actually Built — classifies the pattern logged here as ETL vs. ELT.
Checking Data Quality with SQL — a Configurable Framework — uses the same error-table idea for generic data quality checks.
Design Pattern // Safe Type Conversion with T-SQL
Data Quality // Fundamentals of Type Conversion with T-SQL

Deriving Data Quality Rules from the Schema — What the Metadata Already Knows

Marcus — Wed, 02 Sep 2026 11:13:56 +0000

The rule "country_code is mandatory" lives in your database twice: once as NOT NULL in the target table's schema, and once as a hand-typed row in the check configuration. On the next ALTER TABLE, only one of the two places changes, and the check silently goes wrong. With derived data quality rules you no longer type that repetition: the metadata already knows which columns are mandatory, which keys must be unique and which value ranges the types allow. And by pinning the derived state at deployment time, you detect schema drift instead of suffering it.

The essentials up front:

NOT NULL, key uniqueness and type bounds can be projected mechanically from information_schema — including the message text and the business key.
Projected at deployment time, not on every run: an ETL process is a contract between the source and the target system. The derived rules are generated and persisted at deployment, schema changes are not adopted automatically.
The live projection stays on as a drift detector: a diff against the deployed state shows when the contract has to be renegotiated.
Read-only is not a convenience: derived rules are neither edited nor deactivated, the is_active flag belongs to the manual rules. Whoever wants a change changes the schema and redeploys.
Handwritten rules remain for the business logic the schema does not contain. Exactly those deserve the review time.

Prerequisite: Postgres as the example engine and the framework from the sub-hub Data Quality Checks with SQL with its shared error table and the rule configuration dq.check_rule. The metadata principle needs only information_schema and thus carries over to any engine that offers it. The code shown uses Postgres syntax and has to be adapted to the dialect of any other engine.

The rule that already exists

The framework from the sub-hub works off a configuration table: one row is one rule. That is deliberate, because a new check is a data row instead of a code change, and the business side can read along. Such a row is still rolled out through a deployment — why it has to be that way is shown further down in this article. The price of the configuration only shows over time. A large share of the rows you type into such a configuration merely repeats what the target table enforces anyway:

  1: CREATE SCHEMA IF NOT EXISTS core;
  2: 
  3: CREATE TABLE core.customer
  4: (
  5:     customer_id    int          NOT NULL
  6:    ,source_system  varchar(10)  NOT NULL
  7:    ,source_id      varchar(20)  NOT NULL
  8:    ,country_code   varchar(2)   NOT NULL
  9:    ,email          varchar(100)
 10:    ,age            numeric(3,0)
 11:    ,CONSTRAINT pk_customer         PRIMARY KEY (customer_id)
 12:    ,CONSTRAINT uq_customer_source  UNIQUE (source_system, source_id)
 13: );

This one table already dictates seven check rules for the staging layer: four mandatory fields (lines 5 through 8), two unique keys (lines 11 and 12), plus the length and value-range bounds of the types. Whoever enters them into dq.check_rule by hand creates seven copies of a truth that already lives in the schema.

Copies drift. When a column phone varchar(30) NOT NULL is added later, someone has to remember to extend the configuration as well. If they forget, the check stays silent and the load fails at the target constraint — exactly the scenario the pre-filter was supposed to prevent. If, the other way around, a NOT NULL is dropped, the old rule keeps checking against a requirement that no longer exists and produces findings without a basis. Both failures are quiet. Nobody gets a message that configuration and schema have drifted apart.

Which data quality rules can be derived

There is a simple way out of the drift problem: derive these rules instead of typing them. Everything the target schema enforces as a constraint or as a declared type bound translates mechanically into a rule row. The mapping onto the framework's rule types is one to one:

Metadata source	Derived check	Rule type in the framework	Dimension
`is_nullable = 'NO'`	mandatory-field check	`constraint` (WHERE clause)	Completeness
`PRIMARY KEY` / `UNIQUE`	uniqueness, cardinality 1 (all key columns `NOT NULL`)	`unique`	Uniqueness
`FOREIGN KEY`	reference check against the master table (see the outlook below)	`lookup`	Consistency / Integrity
`character_maximum_length`, `numeric_precision`/`_scale`	length and value-range bound	`constraint` (WHERE clause)	Validity

Which quality dimensions these checks pay into is laid out by the concept article Data Quality: Dimensions and Error Classes. The pattern itself comes from a metadata-driven ETL project: there, the projection ran over a dedicated metadata model with business-side extras such as business and alternate keys and a per-column null handling. For this article it has been transferred to information_schema so that it can be followed without a model of your own. The principle stays the same: the rules live in the metadata, and the projection merely reads them out.

NOT NULL from is_nullable

The simplest projection. Every mandatory column of the target table becomes a mandatory-field check on the staging layer — as a finished row in the configuration table's format, including where_clause and message text:

  1: SELECT
  2:     'constraint'                            AS check_type
  3:    ,'staging'                               AS schema_name
  4:    ,T01.table_name
  5:    ,T01.column_name                         AS check_column
  6:    ,format('%I IS NULL', T01.column_name)   AS where_clause
  7:    ,'E'                                     AS severity
  8:    ,format('%s is mandatory in the target schema', T01.column_name) AS message
  9: FROM
 10:    information_schema.columns T01
 11: WHERE
 12:        T01.table_schema = 'core'
 13:    AND T01.table_name   = 'customer'
 14:    AND T01.is_nullable  = 'NO'
 15: ORDER BY
 16:    T01.ordinal_position;

For the demo table this yields four rows:

check_column	where_clause	severity	message
`customer_id`	`customer_id IS NULL`	E	customer_id is mandatory in the target schema
`source_system`	`source_system IS NULL`	E	source_system is mandatory in the target schema
`source_id`	`source_id IS NULL`	E	source_id is mandatory in the target schema
`country_code`	`country_code IS NULL`	E	country_code is mandatory in the target schema

Two details are worth a look. The format() with %I on line 6 quotes the column name as an identifier — a column name with special characters yields a correct where_clause, not a broken one. And the projection targets staging (line 3) although it reads from core: checking happens at the source, enforcing happens at the target. That is the division of labour of the whole framework. What would fail as a constraint at the target, the pre-filter finds beforehand.

Uniqueness from PRIMARY KEY and UNIQUE

Keys live in two catalog views: information_schema.table_constraints knows the constraints, information_schema.key_column_usage their columns. With multi-column keys, the columns must be sorted by position, that is by ordinal_position within the key. That field does not count the column's position in the table but its position in the key, so it reproduces exactly the order from the DDL. Without an ORDER BY, array_agg emits the columns in whatever order the join happens to deliver them, and that order is neither guaranteed nor stable over time in Postgres. A different execution plan is enough to turn source_system,source_id into source_id,source_system on the next run.

For the check result this would be harmless, because a GROUP BY over both columns finds the same duplicates in any order. The damage happens one level up, because check_column carries the key columns as a comma-separated list, and that list identifies the rule. At runtime the runner splits it back up, builds the multi-column GROUP BY from it, and writes the value belonging to each listed column into error_value in the error log. If the order flips, the same rule looks like a different one on the next run, the string comparison against the target constraint fails, and the column-value pairs in the log come out in a different order. The message suffers too, because anyone reading "Key (source_id, source_system) not unique" has to re-sort the columns against the DDL in their head. The projection therefore sorts exactly once, when collecting into the array:

  1: WITH
  2: CTE_key_column AS
  3: (
  4:    SELECT
  5:        T01.table_name
  6:       ,T01.constraint_name
  7:       ,array_agg(T02.column_name ORDER BY T02.ordinal_position) AS key_column
  8:    FROM
  9:       information_schema.table_constraints T01
 10:       INNER JOIN information_schema.key_column_usage T02
 11:       ON
 12:             T02.constraint_schema = T01.constraint_schema
 13:         AND T02.constraint_name   = T01.constraint_name
 14:       INNER JOIN information_schema.columns T03
 15:       ON
 16:             T03.table_schema = T02.table_schema
 17:         AND T03.table_name   = T02.table_name
 18:         AND T03.column_name  = T02.column_name
 19:    WHERE
 20:           T01.table_schema    = 'core'
 21:       AND T01.table_name      = 'customer'
 22:       AND T01.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
 23:    GROUP BY
 24:        T01.table_name
 25:       ,T01.constraint_name
 26:    HAVING
 27:       bool_and(T03.is_nullable = 'NO')
 28: )
 29: SELECT
 30:     'unique'                                  AS check_type
 31:    ,'staging'                                 AS schema_name
 32:    ,table_name
 33:    ,array_to_string(key_column, ',')          AS check_column
 34:    ,1                                         AS max_occurrence
 35:    ,'E'                                       AS severity
 36:    ,format('Key (%s) not unique'
 37:           ,array_to_string(key_column, ', ')) AS message
 38: FROM
 39:    CTE_key_column;

The result: one rule per key, not one per column.

check_column	max_occurrence	message
`customer_id`	1	Key (customer_id) not unique
`source_system,source_id`	1	Key (source_system, source_id) not unique

The composite key from line 12 of the table DDL lands position-correct as the comma list source_system,source_id in check_column, which is exactly the form the runner expects and splits back up.

The HAVING on line 27 additionally draws a semantic line. Only keys whose columns are all NOT NULL get projected. The additional join on information_schema.columns takes care of that. The reason lies in the NULL semantics of UNIQUE: under the default NULLS DISTINCT, Postgres allows any number of missing values in a UNIQUE column, while the check's GROUP BY collapses several NULL into one group and would report them as duplicates. A rule projected without this filter onto a nullable key would be stricter than the constraint it claims to mirror — it would flag rows the target accepts without complaint. For a PRIMARY KEY the condition always holds. A UNIQUE over nullable columns deliberately stays out and belongs in a user-defined rule instead, with whatever NULL treatment the business actually means. How the uniqueness check deals with composite keys and the NULL semantics of UNIQUE in detail is covered by the spoke Finding Duplicates with SQL. The cardinality of derived key rules is always 1: a UNIQUE constraint knows no "at most three times".

The target table's foreign keys can be projected into lookup rules as well. This is deliberately an outlook rather than a recipe, because that projection needs more metadata than the two above. information_schema.referential_constraints names only the two constraints involved, that is the foreign key and the referenced primary or unique key. The columns themselves live in key_column_usage again, on both sides: the referencing columns come from a lookup by the foreign key's name in their ordinal_position, the referenced columns from a second lookup into the same view by the name of the target constraint. The two sides are joined through position_in_unique_constraint, which states for each foreign key column where its counterpart sits within the referenced key. Only that mapping yields the column pairs the check builds its LEFT JOIN … IS NULL from. For this, the lookup routine accepts the child and master columns each as a comma-separated list, and both lists have to be position-aligned: the first child field belongs to the first master field, pair by pair. The projection therefore has to produce both lists in exactly the same key order, or the generated check joins the wrong pairs. On NULL semantics, catalog and routine do agree: under MATCH SIMPLE, the Postgres default, a composite foreign key accepts every row in which even one of the participating columns is NULL, and the lookup check skips exactly those rows too, because a missing value is a completeness finding and not an integrity one. Anyone looking that match type up in the catalog finds it there as match_option = 'NONE', not as SIMPLE. A foreign key declared as MATCH FULL would show up as FULL and would need a stricter check, because it rejects partially filled keys — one more reason this projection stays an outlook. How the check for orphaned records itself works is covered by the corresponding spoke.

Type bounds from numeric_precision and character_maximum_length

The data types themselves are check rules too. A varchar(2) says "at most two characters", a numeric(3,0) says "absolute value below 1000". In the staging layer, which still holds such values as unchecked text or generous types, this becomes a value-range rule:

  1: SELECT
  2:     'constraint'      AS check_type
  3:    ,'staging'         AS schema_name
  4:    ,T01.table_name
  5:    ,T01.column_name   AS check_column
  6:    ,CASE
  7:        WHEN T01.character_maximum_length IS NOT NULL
  8:        THEN format('length(%I) > %s'
  9:                   ,T01.column_name
 10:                   ,T01.character_maximum_length)
 11:        ELSE format('abs(%I) >= %s'
 12:                   ,T01.column_name
 13:                   ,trim_scale(10::numeric ^ (T01.numeric_precision - T01.numeric_scale))::text)
 14:     END               AS where_clause
 15:    ,'E'               AS severity
 16:    ,CASE
 17:        WHEN T01.character_maximum_length IS NOT NULL
 18:        THEN format('%s longer than %s characters'
 19:                   ,T01.column_name
 20:                   ,T01.character_maximum_length)
 21:        ELSE format('%s outside numeric(%s,%s)'
 22:                   ,T01.column_name
 23:                   ,T01.numeric_precision
 24:                   ,T01.numeric_scale)
 25:     END               AS message
 26: FROM
 27:    information_schema.columns T01
 28: WHERE
 29:        T01.table_schema = 'core'
 30:    AND T01.table_name   = 'customer'
 31:    AND (   T01.character_maximum_length IS NOT NULL
 32:         OR T01.numeric_precision_radix = 10)
 33: ORDER BY
 34:    T01.ordinal_position;

check_column	where_clause	message
`source_system`	`length(source_system) > 10`	source_system longer than 10 characters
`source_id`	`length(source_id) > 20`	source_id longer than 20 characters
`country_code`	`length(country_code) > 2`	country_code longer than 2 characters
`email`	`length(email) > 100`	email longer than 100 characters
`age`	`abs(age) >= 1000`	age outside numeric(3,0)

The filter on line 32 is this projection's fine print. For int and bigint columns, numeric_precision counts bits, not decimal digits — information_schema.columns reveals this via numeric_precision_radix = 2. Without the radix filter, the projection would claim a bound of 10 to the power of 32 for customer_id, which neither matches the type semantics nor makes for a meaningful check. Only for explicitly declared numeric(p,s) types (radix 10) does the precision carry a business statement.

Project at deployment time, not on every run

What do you do with the three queries? Two obvious answers each come with a catch. The first: take the results into the configuration by hand via INSERT INTO dq.check_rule. That only automates the typing effort. The inserted rows would be editable copies, stale again after the next ALTER TABLE. The second: turn the projection into the runtime rule set directly, as a view, and every rule is fresh at every moment. Exactly that freshness is the second answer's catch.

An ETL process is a contract between the source and the target system, and that contract is valid at development time. When either side changes, the contract is renegotiated, not adjusted automatically. A live view would do exactly that, in both directions. If the target gets stricter, say through a new NOT NULL column, the run that was green yesterday fails today without any deployment on the ETL side. If the target gets laxer because a constraint is dropped, the view silently drops the corresponding check, and the pre-filter softens without anyone having decided it. Add the audit angle: which rules were in force for a given run is a question a view can, by principle, not answer.

For perspective: within a single application that validates its own schema, live derivation is the right model. There is no second contracting party there, the schema is the only truth. The contract logic begins as soon as two systems are involved.

The conclusion that holds therefore separates two points in time. The projection itself stays a view — the tool that can compute the derived rules fresh from the metadata at any moment:

  1: CREATE OR REPLACE VIEW dq.derived_rule AS
  2: WITH
  3: CTE_pk_column AS
  4: (
  5:    -- primary key of the target table = business key of the error table
  6:    SELECT
  7:        T01.table_name
  8:       ,array_agg(T02.column_name ORDER BY T02.ordinal_position) AS pk_column
  9:    FROM
 10:       information_schema.table_constraints T01
 11:       INNER JOIN information_schema.key_column_usage T02
 12:       ON
 13:             T02.constraint_schema = T01.constraint_schema
 14:         AND T02.constraint_name   = T01.constraint_name
 15:    WHERE
 16:           T01.table_schema    = 'core'
 17:       AND T01.constraint_type = 'PRIMARY KEY'
 18:    GROUP BY
 19:       T01.table_name
 20:    HAVING
 21:       count(*) <= 3
 22: )
 23: ,CTE_key_column AS
 24: (
 25:    -- every key as an ordered column array; only keys whose columns are
 26:    -- all NOT NULL (otherwise the rule would check more strictly than
 27:    -- the constraint guarantees - Postgres default NULLS DISTINCT)
 28:    SELECT
 29:        T01.table_name
 30:       ,T01.constraint_name
 31:       ,array_agg(T02.column_name ORDER BY T02.ordinal_position) AS key_column
 32:    FROM
 33:       information_schema.table_constraints T01
 34:       INNER JOIN information_schema.key_column_usage T02
 35:       ON
 36:             T02.constraint_schema = T01.constraint_schema
 37:         AND T02.constraint_name   = T01.constraint_name
 38:       INNER JOIN information_schema.columns T03
 39:       ON
 40:             T03.table_schema = T02.table_schema
 41:         AND T03.table_name   = T02.table_name
 42:         AND T03.column_name  = T02.column_name
 43:    WHERE
 44:           T01.table_schema    = 'core'
 45:       AND T01.constraint_type IN ('PRIMARY KEY', 'UNIQUE')
 46:    GROUP BY
 47:        T01.table_name
 48:       ,T01.constraint_name
 49:    HAVING
 50:       bool_and(T03.is_nullable = 'NO')
 51: )
 52: -- NOT NULL from is_nullable
 53: SELECT
 54:     'constraint'                            AS check_type
 55:    ,'staging'                               AS schema_name
 56:    ,T01.table_name
 57:    ,T02.pk_column[1]                        AS id1_column
 58:    ,T02.pk_column[2]                        AS id2_column
 59:    ,T02.pk_column[3]                        AS id3_column
 60:    ,T01.column_name                         AS check_column
 61:    ,format('%I IS NULL', T01.column_name)   AS where_clause
 62:    ,1                                       AS max_occurrence
 63:    ,'E'                                     AS severity
 64:    ,format('%s is mandatory in the target schema', T01.column_name) AS message
 65: FROM
 66:    information_schema.columns T01
 67:    INNER JOIN CTE_pk_column T02
 68:    ON
 69:      T02.table_name = T01.table_name
 70: WHERE
 71:        T01.table_schema = 'core'
 72:    AND T01.is_nullable  = 'NO'
 73: UNION ALL
 74: -- uniqueness from PRIMARY KEY / UNIQUE
 75: SELECT
 76:     'unique'
 77:    ,'staging'
 78:    ,T01.table_name
 79:    ,T02.pk_column[1]
 80:    ,T02.pk_column[2]
 81:    ,T02.pk_column[3]
 82:    ,array_to_string(T01.key_column, ',')
 83:    ,NULL
 84:    ,1
 85:    ,'E'
 86:    ,format('Key (%s) not unique'
 87:           ,array_to_string(T01.key_column, ', '))
 88: FROM
 89:    CTE_key_column T01
 90:    INNER JOIN CTE_pk_column T02
 91:    ON
 92:      T02.table_name = T01.table_name;

The CTE on lines 3 through 22 pulls one more thing out of the metadata that the configuration table would otherwise demand by hand: the business key, through which the error table later maps a finding back to the source record. That, too, lives in the schema — it is the target table's primary key. As an ordered array it spreads across id1_column through id3_column, and the HAVING count(*) <= 3 on line 21 draws the line of the error table's contract: it carries no more than three mapping columns.

The projection becomes the rule set only through the deployment step: it reads the view exactly once and writes the result into a table of its own. That table is the signed contract. It records which derived rules were valid at deployment time, and the runner reads it exclusively:

  1: CREATE TABLE dq.deployed_rule
  2: (
  3:     check_type      text        NOT NULL
  4:    ,schema_name     text        NOT NULL
  5:    ,table_name      text        NOT NULL
  6:    ,id1_column      text
  7:    ,id2_column      text
  8:    ,id3_column      text
  9:    ,check_column    text        NOT NULL
 10:    ,where_clause    text
 11:    ,max_occurrence  int         NOT NULL DEFAULT 1
 12:    ,severity        char(1)     NOT NULL
 13:    ,message         text        NOT NULL
 14:    ,deployed_on     timestamptz NOT NULL DEFAULT now()
 15: );
 16: 
 17: -- deployment step: sign the contract. Runs at deployment time against
 18: -- the target system, not on every run of the runner.
 19: DELETE FROM dq.deployed_rule;
 20: 
 21: INSERT INTO dq.deployed_rule
 22: (
 23:     check_type
 24:    ,schema_name
 25:    ,table_name
 26:    ,id1_column
 27:    ,id2_column
 28:    ,id3_column
 29:    ,check_column
 30:    ,where_clause
 31:    ,max_occurrence
 32:    ,severity
 33:    ,message
 34: )
 35: SELECT
 36:     check_type
 37:    ,schema_name
 38:    ,table_name
 39:    ,id1_column
 40:    ,id2_column
 41:    ,id3_column
 42:    ,check_column
 43:    ,where_clause
 44:    ,max_occurrence
 45:    ,severity
 46:    ,message
 47: FROM
 48:    dq.derived_rule;

The table deliberately carries no active flag. The configuration table's is_active belongs exclusively to the manual rules, because a derived rule is neither edited nor deactivated. The deployed_on field documents, in passing, when the contract was signed.

The contract idea does not stop at the derived rules. The manual rules in the configuration table change only as part of a deployment as well — inserted, edited or toggled via is_active in a versioned way, not live. The reason is the same as with the schema: a rule list that can be rearranged by hand between two deployments no longer tells you, at a glance, what was in force during the last run. The execution log does record that, but the list is supposed to predict what runs, not merely document what ran. is_active is thus a versioned switch, not a live dial.

The check scope of a table is now — within the limits the projection itself draws — the union of both worlds: the deployed derived rules plus the active handwritten ones from the configuration table. Add a single user-defined rule as an example — an age limit that no constraint contains:

  1: INSERT INTO dq.check_rule
  2:     (check_type,   schema_name, table_name, id1_column,    check_column, where_clause, severity, message)
  3: VALUES
  4:     ('constraint', 'staging',   'customer', 'customer_id', 'age',        'age < 18',   'W', 'Customer under 18 - review with business');
  5: 
  6: SELECT
  7:     'derived'          AS origin
  8:    ,check_type
  9:    ,check_column
 10:    ,where_clause
 11:    ,severity
 12:    ,message
 13: FROM
 14:    dq.deployed_rule
 15: UNION ALL
 16: SELECT
 17:     'user-defined'
 18:    ,check_type
 19:    ,check_column
 20:    ,where_clause
 21:    ,severity
 22:    ,message
 23: FROM
 24:    dq.check_rule
 25: WHERE
 26:    active
 27: ORDER BY
 28:     origin
 29:    ,check_type
 30:    ,check_column;

origin	check_type	check_column	where_clause	severity
derived	constraint	`country_code`	`country_code IS NULL`	E
derived	constraint	`customer_id`	`customer_id IS NULL`	E
derived	constraint	`source_id`	`source_id IS NULL`	E
derived	constraint	`source_system`	`source_system IS NULL`	E
derived	unique	`customer_id`		E
derived	unique	`source_system`		E
user-defined	constraint	`age`	`age < 18`	W

Six out of this table's seven rules come from the schema. Exactly one was typed — the one that actually contains business knowledge. How large that share turns out does depend on the table, though: a target table without mandatory fields and without key constraints gives the projection simply nothing.

The projection as drift detector

What happens when the target schema changes after the deployment? To the running process, at first: nothing. The runner reads the deployed state, and that is exactly the point of the model. The change does not stay invisible, though, because the view keeps computing the live state — and the difference between the two can be queried mechanically:

  1: ALTER TABLE core.customer ALTER COLUMN email SET NOT NULL;
  2: ALTER TABLE core.customer DROP CONSTRAINT uq_customer_source;
  3: 
  4: SELECT
  5:     'added'       AS drift
  6:    ,check_type
  7:    ,check_column
  8:    ,message
  9: FROM
 10:    (
 11:       SELECT check_type, schema_name, table_name, id1_column, id2_column,
 12:              id3_column, check_column, where_clause, max_occurrence, severity, message
 13:       FROM
 14:          dq.derived_rule
 15:       EXCEPT
 16:       SELECT check_type, schema_name, table_name, id1_column, id2_column,
 17:              id3_column, check_column, where_clause, max_occurrence, severity, message
 18:       FROM
 19:          dq.deployed_rule
 20:    ) T01
 21: UNION ALL
 22: SELECT
 23:     'removed'     AS drift
 24:    ,check_type
 25:    ,check_column
 26:    ,message
 27: FROM
 28:    (
 29:       SELECT check_type, schema_name, table_name, id1_column, id2_column,
 30:              id3_column, check_column, where_clause, max_occurrence, severity, message
 31:       FROM
 32:          dq.deployed_rule
 33:       EXCEPT
 34:       SELECT check_type, schema_name, table_name, id1_column, id2_column,
 35:              id3_column, check_column, where_clause, max_occurrence, severity, message
 36:       FROM
 37:          dq.derived_rule
 38:    ) T02
 39: ORDER BY
 40:     drift
 41:    ,check_column;

drift	check_type	check_column	message
added	constraint	`email`	email is mandatory in the target schema
removed	unique	`source_system,source_id`	Key (source_system, source_id) not unique

The two directions of the diff tell different stories. An added row means: the target has become stricter. Without a redeploy, the pre-filter does not know the new guarantee, and the load would fail at the target constraint instead of at the pre-filter — the detector shows this before it happens. A removed row means: the target has become laxer. The pre-filter checks more strictly than necessary, which endangers no run but is worth a negotiation. In both cases the answer is the same: review the contract and sign it again deliberately, that is, run the deployment step once more. The diff works as a check in the deployment gate or as a warning before each run. It is a signal, never an automatic adoption.

Why read-only is not a convenience

For the deployed derived rules, a hard convention applies: no UPDATE, no deactivating, no deleting. That is why the table carries no is_active either — this flag belongs exclusively to the manual rules in the configuration table. What looks like a limitation is the core of the pattern, not its side effect.

If the derived rule were editable, it would be a copy with an expiry date. Any change to it would decouple it from its source, and from that moment on there would again be two truths: the one in the schema and the one in the rule. Worse still: a well-meant "let's switch this one check off for a bit" would bypass a guarantee that the target schema keeps enforcing regardless. The load would then fail at a constraint for which there apparently was no active rule. The check would no longer be the target's honest pre-filter but its own drifting opinion of it.

For the same reason, the criticality of derived rules follows the source semantics rather than an editor's judgement. A NOT NULL and a unique key are hard constraints at the target. Their derived rules therefore carry E as in error, because a violation would make the load fail there. That is not an opinion to be debated per rule but a property of the schema.

Whoever really wants to get rid of a derived rule has exactly one path: change the schema and redeploy. That sounds inconvenient, and it is intended. The discussion "does this column really have to be mandatory?" belongs at the table, not at a configuration row that merely mirrors the table.

The boundary you don't build

As soon as two rule sources exist, one question suggests itself: what happens when both check the same thing? If someone manually creates a uniqueness rule on customer_id that already exists as a derived one — do you need conflict detection, a precedence scheme, a merge?

The better answer is: you never let the situation arise. Instead of detecting and resolving conflicts between derived and user-defined rules, the user-defined territory is cut so that it never enters the derived one. Concretely: the keys the schema already secures are not offered again in the rule editor — no "check primary key" quick pick, no pre-filled uniqueness form for the constraint columns. Those checks already exist, automatically and non-negotiably. The free uniqueness type is there for the cases the schema does not know: a composite business key plus a validity date, or an "at most three times per region", in other words different column combinations and different cardinalities.

The conflict you don't build does not have to be resolved. There is no merge logic, no precedence table and no special case in the runner — not because the problem was solved elegantly, but because by construction it does not exist. That is a design decision, not an algorithm, and it is cheaper and more robust than any conflict detection you would have to write instead.

Duplicates among your own rules

Entirely without checks, though, the user-defined territory does not get by. Within the handwritten rules, the same check can accidentally be created twice, and then two identical findings for the same violation would sit in the error table. What gets blocked is therefore the exact content duplicate: same rule type, same columns, same condition.

What deliberately does not get blocked is worth noting. Several rules of the same type per column are explicitly allowed: two different value-range checks on the same column are two separate, legitimate checks with their own messages. A "one rule per column and type" constraint would forbid exactly the cases a freely configurable rule table is built for.

The second subtlety: the criticality is not part of a rule's identity. Two rules with an identical condition but different severity count as the same duplicate. Otherwise the same check could exist once as an error and once as a warning, and the same violation would be reported twice — once blocking, once not. Whoever wants to change a rule's severity edits the existing rule instead of placing a second one next to it.

What remains handwritten

After all the deriving, a remainder is left, and it is the most valuable part of the configuration. The schema only holds what the database can enforce. Everything else is business knowledge:

Cross-field conditions — a discount requires an active status, an end date lies after the start date.
Plausibility and temporal logic — an order date does not lie in the future, an age under 18 is possible but worth reviewing (the example rule from above).
Value lists owned by the business — allowed status values or product codes deliberately not cemented as constraints, because the business side maintains them.

The derived rules need no review time, because they cannot be wrong — they claim nothing the target schema does not enforce anyway. Every minute a review spends nodding off "customer_id is mandatory" is missing from the question of whether the age limit is right. The projection shifts the attention to where mistakes are actually possible: into the handwritten rules.

The honest limit

The projection is exactly as good as the schema it reads from. That is its strength and its limit at once, and the limit deserves the same clear look as the pattern itself.

Where mandatory fields exist only in the application, because the column allows NULL and only the input form enforces a value, information_schema sees nothing, and the projection yields nothing. The same goes for business keys that were never created as constraints, and for length bounds on text columns without a declared length. The projection must not silently paper over these gaps: it delivers exactly the guarantees the schema states, and not a single one more. If you want more derived, you have to make the schema more honest — which keys and constraints a target table should carry in the first place is covered by the article on Postgres table conventions.

This pattern deserves to be called by its name: it is a deficit of application development, and not a rare one. Many applications treat the database as mere storage. Mandatory fields, value ranges and keys are checked by application code, while the schema allows almost anything. As long as only the application itself writes, this goes unnoticed. But anyone who wants to load data into such an application past the input form, say during a migration or through an interface, stands there without any of the guarantees this article projects. The consequence is uncomfortable but clear: exactly the rules that an honest schema would make derivable have to be created and maintained manually as user-defined rules for safe loading — with the full drift risk this article started with.

On top of that comes a Postgres-specific subtlety: information_schema is portable but not complete. A CREATE UNIQUE INDEX without an accompanying constraint does not appear in table_constraints, and neither do partial unique indexes or the NULLS NOT DISTINCT behaviour of newer Postgres versions. If you use such constructs, project from the system catalogs pg_constraint and pg_index instead — with more detail, but without portability. A partial unique index translated into a full uniqueness rule would simply be wrong, so its index predicate belongs in the rule, or the index deliberately stays out of the projection.

Deriving does not replace thinking about data quality. It moves the thinking to where it belongs: into the schema.

FAQ

Which data quality rules can be derived from the schema?
Everything the target table declares as a constraint or type: mandatory-field checks from is_nullable, uniqueness checks from PRIMARY KEY and UNIQUE (provided the key columns are NOT NULL), reference checks from foreign keys, plus length and value-range bounds from character_maximum_length and numeric_precision. Not derivable is business logic without a schema trace — cross-field conditions, temporal plausibility, value lists owned by the business.

Why should derived data quality rules not be editable?
Because an editable derived rule would be a copy with an expiry date. After the first change there would be two truths: the one in the schema and the one in the rule. A switched-off check would additionally hide a guarantee the target still enforces. That is why the deployed state deliberately carries no is_active — the flag belongs to the manual rules. Whoever wants to change a derived rule changes the schema and redeploys.

Is information_schema enough, or do I need my own metadata model?
For NOT NULL, keys and type bounds, information_schema is enough, and it is portable — the same views exist in SQL Server. The Postgres system catalogs (pg_constraint, pg_index) additionally see unique indexes without constraints and partial indexes. A metadata model of your own pays off only once business metadata should join in that the database does not know — say, business keys without a database constraint or a per-column null-handling semantic.

What if schema and business requirement contradict each other?
Then one of the two sides is wrong, and that belongs resolved at the schema rather than overridden in the configuration. If the business says "email is mandatory" while the column allows NULL, the schema is too lax: add the constraint, and the derived rule follows automatically. Until then, a user-defined rule with severity warning can make the gap visible without faking a hard guarantee.

How do I keep derived and user-defined rules apart?
Through the origin of the data itself: derived rules live only in the deployment table, which only the deployment step fills, user-defined ones only in the configuration table. A combined view with an origin column (via UNION ALL) shows the complete check scope. Only one rule of discipline matters: never copy projected rules into the configuration table by hand — there they would be editable copies, and the drift starts all over again.

Framework and routines:

Data Quality Checks with SQL — the sub-hub: the configurable framework with error table, rule configuration and runner, whose configuration this article fills automatically.
Validating Data with SQL — the routine behind the projected WHERE rules: value ranges, mandatory fields and the NULL trap.
Finding Duplicates with SQL — the uniqueness routine: cardinality, composite keys and the NULL semantics of UNIQUE.
Finding Orphaned Records with SQL — the lookup routine that derived foreign-key rules build on.
Three Severity Levels, Not Pass/Fail — the severity spoke of the series: why E/W/I can do more than passed/failed, and how severity decides whether a rule becomes a constraint or stays in the pipeline.

Theory:

Data Quality: Dimensions and Error Classes — the concept frame: which dimensions the derived rules pay into.

Target schema:

Postgres Table Conventions — which keys and constraints a target table should carry so the projection has something to read.

Formatting SQL Statements (Part 2) — Statement Structure: SELECT, WHERE, FROM, JOIN

Marcus — Mon, 31 Aug 2026 09:04:54 +0000

If you can't tell at a glance where the WHERE clause of a 200-line SELECT statement starts and ends, you have a structure problem — not a content problem. This article shows how to format SQL statements so that SELECT, FROM, WHERE and JOIN stay immediately recognizable even in long queries.

→ Part of a series. This is part 2 and covers statement structure (SELECT, WHERE, FROM, JOIN). The basics for identifiers, delimiters, commas, and aliases are in Part 1 — Identifiers, Delimiters, Commas, Aliases.

TL;DR — what this article delivers:

Main elements (SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY) belong on separate lines with consistent indentation.
WHERE clause — align operands and operators column-style, indent equivalent conditions equally. The parenthesis structure becomes visually readable this way.
FROM clause — table directly after the JOIN operator, ON keyword on its own line, JOIN conditions formatted like a mini WHERE clause.
Postgres bridge + auto-formatters at the end — [brackets] vs. "quotes", DISTINCT ON, LATERAL. sqlfluff and pgFormatter complement manual discipline, they don't replace it.

Prerequisite: SSMS or any SQL editor with configurable tab width is enough. A live AdventureWorks database is not required — the examples illustrate patterns, not runnable pipelines.

General Thoughts on Indentation

A useful analogy for SQL structure is the outline of a table of contents. The indented version below is much faster to scan than the flat variant further down:

1. Chapter Level 1
   1.1. Chapter Level 2
      1.1.1. Chapter Level 3
      1.1.2. Chapter Level 3
   1.2. Chapter Level 2
2. Chapter Level 1
   2.1. Chapter Level 2
      2.1.1. Chapter Level 3

For comparison, the same TOC flat:

1. Chapter Level 1
1.1. Chapter Level 2
1.1.1. Chapter Level 3
1.1.2. Chapter Level 3
1.2. Chapter Level 2
2. Chapter Level 1
2.1. Chapter Level 2
2.1.1. Chapter Level 3

Flat TOCs work too, but only with additional formatting options like upper/lower case, bold, or italic to distinguish levels. In a SQL editor, those options are typically not available (SSMS renders plain text with syntax highlighting, no bold for identifiers). So indentation remains the structural tool.

One thing up front: All layout rules in this article are formatting conventions. SQL enforces neither the line breaks nor the indentation or the position of commas and keywords. The conventions aim to make the structure of a statement visible before you read it.

Main Language Elements

This article looks at the six central clauses of a SELECT statement:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

A complete SELECT statement can contain further clauses (WITH, TOP or LIMIT, OFFSET/FETCH, window definitions) — the same indentation principles apply to them. Treating the six clauses as the first level, their contents sit one indentation level deeper. The resulting basic structure of a SQL statement looks like this:

  1: SELECT
  2:    field list
  3: FROM
  4:    data sources
  5: WHERE
  6:    conditions on data source
  7: GROUP BY
  8:    grouping fields
  9: HAVING
 10:    conditions on aggregations
 11: ORDER BY
 12:    sort fields

The ground rule of this style guide: The main elements of a SQL statement sit on separate lines.

As counterexamples, here are two commonly seen formatting styles that ignore this rule. In both, you have to read at least parts of the statement to recognize where a main element starts and ends.

Left-Aligned Main and Sub-Elements

You occasionally find both top-level elements and the next-level elements left-aligned in the same column. You see this especially often in the FROM clause: Data sources (tables, views, CTEs) are indented the same as the introducing keyword FROM:

  1: SELECT
  2: field list
  3: FROM
  4: table1
  5: JOIN table2 ON [...]
  6: JOIN table3 ON [...]
  7: WHERE
  8: conditions on data source
  9: GROUP BY
 10: grouping fields
 11: HAVING
 12: conditions on aggregations
 13: ORDER BY
 14: sort fields

Right-Aligned Keywords

In this example, the main clauses of the SELECT statement (without regard for the BY keyword) are right-aligned. This indentation style makes alignment extra work because you have to deal with varying indent widths:

  1: SELECT field1, field2, field3
  2:   FROM table1
  3:   LEFT JOIN table2 ON [...]
  4:   LEFT JOIN table3 ON [...]
  5:  WHERE condition1
  6:     OR condition2
  7:     OR condition3
  8:  GROUP BY grouping fields
  9: HAVING condition1
 10:     OR condition2
 11:     OR condition3
 12:  ORDER BY sort fields

For simple statements, however, right-aligned keywords can read quite well — CREATE INDEX is a typical example:

  1: CREATE UNIQUE NONCLUSTERED
  2:  INDEX [IndexName]
  3:     ON [dbo].[FactInternetSalesReason]([SalesOrderNumber] ASC);

For full SELECT statements with multiple JOINs, group-by columns and complex conditions, this style breaks down.

SELECT Field List

The natural reading direction of a SQL statement is left to right and top to bottom. With keyboard and mouse, vertical navigation is easier than horizontal navigation. The scroll wheel and the Page Up/Page Down keys make fast vertical navigation possible even within long complex statements — provided that field lists are written vertically. The more important effect is editor-independent: One line per expression makes long field lists scannable and easier to compare, move, or extend.

Field names should be written as a vertical list with leading commas — the detailed reasoning (comma readability, box-selection pattern) is in Part 1, section “The Comma”. One field per line. Because the field list is logically subordinate to the SELECT keyword, field names are indented by the agreed indent width:

  1: SELECT
  2:     field1
  3:    ,field2
  4:    ,field3
  5: FROM [...]
  6: WHERE [...]
  7: GROUP BY
  8:     field1
  9:    ,field2
 10:    ,field3
 11: HAVING [...]
 12: ORDER BY
 13:     field1
 14:    ,field2
 15:    ,field3

WHERE Clause

The order here deliberately departs from SQL syntax: The WHERE clause introduces the central condition pattern first, which is then reused for the FROM and HAVING clauses. A WHERE clause contains one or more conditions (predicates) connected by logical operators. Two points deserve attention when formatting these conditions:

alignment of operands
indentation of equivalent conditions

Alignment of Operands

A simple condition consists of two operands and an operator (=, !=, <>, IN, NOT IN, etc.). In a compound expression built from multiple single conditions, operands and operators should be aligned column-style. In the following example, field names have different lengths and different operators are applied:

  1: [...]
  2: WHERE
  3:        T01.[field___1]    =  'something'
  4:    AND T01.[field__2]     <> 1
  5:    AND T01.[field_____3]  NOT IN (1, 2, 3)
  6:    AND T01.[field4]       = T02.[field5]

This produces a table-like notation that allows fast visual navigation within the condition components.

Indentation of Equivalent Conditions

If the WHERE clause contains more than one condition, they are connected by logical operators like AND or OR. Parentheses are required when the intended logical grouping differs from operator precedence (AND binds more tightly than OR). Beyond that, they can improve readability in complex expressions. Depending on complexity, you quickly end up with deeply nested structures.

To keep complex nested expressions readable, give particular attention to structure and indentation of the WHERE clause: equivalent conditions are written underneath each other with the same indent, and a logical connection of equivalent conditions gets an indent that matches the parenthesis hierarchy:

  1: [...]
  2: WHERE    (
  3:           (
  4:                 [operand01] = [operand02]
  5:              OR [operand03] = [operand05]
  6:              OR [operand05] = [operand06]
  7:           )
  8:       AND (
  9:                 [operand07] = [operand08]
 10:              OR [operand09] = [operand10]
 11:           )
 12:       AND [operand11] = [operand12]
 13:    )
 14: OR (
 15:       [operand13] = [operand14]
 16:    )

The logical connections become visually readable through this indentation. A screenshot of the same WHERE clause in Notepad++ makes the effect even more obvious, because the editor's vertical guide lines at the tab stops emphasize the parenthesis hierarchy:

FROM Clause

As with the other main elements, the subordinate elements of the FROM clause are written indented. In most cases these are data sources — tables, views, and Common Table Expressions (CTEs).

Sub-SELECTs in the FROM clause can be replaced by CTEs when the subquery forms a self-contained logical step, is needed more than once, or makes the statement hard to follow: A CTE structures it top-down. For small subqueries used only once, a derived table is often the more compact choice. Converting is a structuring decision — how the CTE is executed is up to the engine's optimizer. What Postgres does differently (MATERIALIZED / NOT MATERIALIZED) is covered in the FAQ at the end of the article.

For formatting a JOIN clause there are four building blocks:

table (or view / CTE)
JOIN operator
ON keyword
JOIN conditions

In the following code example, there is no visual anchor to identify the elements of the FROM clause:

  1: FROM
  2: table1 T01
  3: JOIN table2 T02
  4: ON T01.[FK] = T02.[ID]
  5: JOIN table3 T03 ON
  6: T02.[FK] = T03.[ID]
  7: JOIN table4 T04
  8: ON T03.[FK] = T04.[ID]
  9: WHERE
 10: [...]

The elements of the FROM clause belong on separate lines for readability. Exception: the joined table sits directly after the JOIN operator. In this style guide, the ON keyword sits left-aligned with the JOIN operator on the line below — that keeps JOIN and join condition visible as two separate structural levels. The same rules as for the WHERE clause apply to JOIN conditions:

  1: SELECT
  2:    [...]
  3: FROM
  4:    table1 T01
  5:    INNER JOIN table2 T02
  6:    ON
  7:      T01.[FK] = T02.[ID]
  8:    INNER JOIN table3 T03
  9:    ON
 10:          T02.[FK1]   = T03.[FK1]
 11:      AND T02.[field2] = T03.[field2]
 12:    INNER JOIN table4 T04
 13:    ON
 14:      T03.[FK] = T04.[ID]
 15: WHERE
 16:   [...]

GROUP BY, HAVING, ORDER BY

The remaining main elements follow the same principles as the SELECT field list and the WHERE clause — in short:

GROUP BY contains a list of grouping expressions — for simple queries formatted like a scaled-down SELECT: one expression per line with a leading comma, indented by convention.
HAVING is a condition list like WHERE — operands aligned column-style, equivalent conditions indented equally. The difference is semantic (post-GROUP BY filter), not typographic.
ORDER BY contains a list of sort expressions — formatted analogous to the GROUP BY clause, with optional ASC / DESC per expression (held in a separate column when both sort directions appear in the same statement).

  1: SELECT
  2:     T01.[Region]
  3:    ,T01.[Year]
  4:    ,SUM(T01.[Sales])   AS [Total]
  5: FROM
  6:     [dbo].[FactSales] T01
  7: GROUP BY
  8:     T01.[Region]
  9:    ,T01.[Year]
 10: HAVING
 11:        SUM(T01.[Sales]) >= 1000
 12:    AND COUNT(*)         >= 10
 13: ORDER BY
 14:     T01.[Region] ASC
 15:    ,T01.[Year]   DESC

Auto-Formatters and “Formatting Is Learning”

Tools like sqlfluff (multi-dialect — T-SQL, Postgres, MySQL, BigQuery, …) and pgFormatter (Postgres-focused) generate the layouts shown here automatically and are useful as a pre-commit hook or CI step. They are not a replacement for manual formatting.

The act of indenting, aligning aliases and placing parentheses forces the writer to read the statement fully and mentally model the table relationships. Auto-formatters produce the layout — they don't produce the mental model that emerges while writing. In the age of Copilot and Cursor, this matters twice: Generated SQL without understanding is a risk, because it produces technically correct queries that still don't answer the business question.

Pragmatic recommendation: format manually first, then let the formatter run as a final consistency pass (e.g. sqlfluff fix).

Postgres Bridge

The examples in this article use T-SQL notation ([brackets], positional T01 aliases). The layout rules themselves are engine-neutral — they apply 1:1 to Postgres as well. There are only a handful of places where Postgres behaves differently, and none of them change the format pattern:

Identifier quoting: The SQL standard uses double quotes for delimited identifiers ("name") — Postgres follows it. T-SQL typically uses [brackets] but also understands double quotes when QUOTED_IDENTIFIER is active. With case-sensitive identifiers, quoting becomes semantically relevant in Postgres (a separate article on case sensitivity in SQL Server vs. Postgres is planned).
DISTINCT ON: the Postgres idiom for “first row per group”. Which row comes first is determined only by an ORDER BY that fixes the order within each group unambiguously — its leftmost expressions must be the DISTINCT ON expressions. In T-SQL, emulate this with ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) and a filter on rn = 1. The formatting follows the SELECT field list.
LATERAL: lets a subquery in the FROM clause reference columns of preceding FROM items. T-SQL uses CROSS APPLY / OUTER APPLY for this. A LATERAL join is formatted like any other JOIN.
RETURNING: Postgres returns the affected rows on INSERT/UPDATE/DELETE and, since version 17, on MERGE as well. SQL Server offers OUTPUT, a functionally related clause with its own syntax and forms (OUTPUT INTO). Both are formatted as their own clause line with a field list.
CTEs: WITH … is standardized and structured essentially the same in both engines, the extensions differ in detail. Postgres has the options MATERIALIZED / NOT MATERIALIZED (see FAQ).
JOIN indentation, WHERE parenthesis pattern, ORDER BY lists: There is no engine difference for these layout patterns.

So the format discipline carries on both engines. A separate follow-up article on the Postgres identifier specifics is planned.

Conclusion

The main elements of a SELECT statement belong on separate lines, their subordinate elements one indentation level deeper. Every pattern in this article derives from that ground rule: the field list as a vertical list with leading commas, the WHERE clause with column-aligned operands and equally indented equivalent conditions, the FROM clause with the table directly after the JOIN operator and the ON keyword on its own line.

Applied consistently, these patterns make the structure of a statement visible before its content is read: The parenthesis hierarchy of a nested WHERE clause and the relationships between the tables are already in the layout. The pattern is engine-neutral and carries in SQL Server and Postgres alike. The vocabulary level (identifiers, delimiters, commas, aliases) is covered in Part 1. More important than any single rule is that the same hierarchy stays consistently visible across the whole project — consistency beats purity.

FAQ

How do you format complex SQL queries with multiple JOINs and nested WHERE conditions?

Following the rules in this article: each clause on its own line, subordinate elements one level deeper. Each JOIN gets its table right next to the operator and its ON on the following line. In the WHERE clause, equivalent conditions sit underneath each other and the parenthesis hierarchy sets the indent depth. That keeps even a statement with five joins navigable.

When should a sub-SELECT be replaced by a CTE?

As soon as the subquery makes the statement hard to follow, is needed more than once, or forms a self-contained logical step. CTEs read top-down instead of nested and can be referenced multiple times in the same query. SQL Server, however, may execute the defining query again for each reference, because a CTE is not a materialized intermediate result there. For testing, temporarily replace the outer query with a SELECT * FROM cte_name — a CTE definition cannot be run standalone. Small subqueries used only once may stay.

Should the JOIN operator always be explicit (INNER, LEFT, RIGHT, FULL)?

Syntactically, the INNER is optional — a bare JOIN means INNER JOIN. This style guide still always writes out the JOIN type (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN): the default is not obvious to every reader, and when skimming, the difference between JOIN and LEFT JOIN is easy to miss.

Does the format pattern apply to Postgres as well?

Yes — the layout rules transfer unchanged, see the Postgres Bridge section above. The only practical adjustment is identifier quoting ("…" instead of […]). The layout rules for SELECT field lists, WHERE clauses, FROM clauses and JOIN indentation are engine-neutral.

Is an auto-formatter like sqlfluff or pgFormatter enough — or do you still need to format manually?

Auto-formatters deliver layout, but not the learning effect. Anyone who only pipes a 200-line statement through a formatter hasn't read the statement. Anyone who structures it manually builds the mental models of table relationships — and often spots logical errors along the way. Pragmatic workflow: manual first, then the formatter as a finishing pass for consistency.

What about MATERIALIZED / NOT MATERIALIZED for CTEs in Postgres?

Since version 12, Postgres folds a non-recursive, side-effect-free CTE into the parent query by default when it is referenced exactly once — predicate pushdown is then possible. With multiple references, Postgres does not fold such a CTE by default and treats it as a separate computation instead. MATERIALIZED forces this separate computation, NOT MATERIALIZED allows folding even with multiple references. For performance-critical queries with expensive CTEs, the Postgres docs on WITH queries are worth a read. SQL Server has no corresponding syntax: a CTE there is not an independently materialized object, it is optimized as part of the overall statement.

Where do I find Part 1 (identifiers, delimiters, commas, aliases)?

Part 1 — Formatting SQL Statements. It covers the smaller building blocks: regular vs. delimited identifiers, leading vs. trailing comma, systematic T01/T02 aliases, qualified column names. Part 1 + Part 2 together form a complete style guide for SELECT statements.

Part of the series:

Formatting SQL Statements (Part 1) — Identifiers, Delimiters, Commas, Aliases — the vocabulary layer: regular vs. delimited identifiers, comma position, systematic aliases.
Structuring and Formatting SQL Statements — The Cluster Path — the hub article with the reading path through all formatting topics on this blog.

Upstream:

The Functional Aesthetics of SQL — the “why” of SQL formatting: Structured code is faster to read, review, and change.

Downstream:

Inline Comments in Complex SQL Statements — the meta layer: inline and block comments that document the structure shown here without destroying it.
Editor Options in SSMS — the editor basis for this layout: tab width, spaces instead of tabs, block selection.
SQL Conventions // PL/pgSQL Procedures You Can Still Read in Two Years — the continuation of the statement layout for Postgres procedures.

Formatting SQL Statements (Part 1) — Identifiers, Delimiters, Commas, Aliases

Marcus — Thu, 27 Aug 2026 12:10:05 +0000

Anyone who has ever had to debug a badly or completely unformatted SELECT with 30 columns and half a dozen joins knows the feeling: it isn't the SQL that eats up your day — it's hunting down what the statement is actually trying to do. SQL formatting isn't a matter of taste; it's a maintenance tool — and it starts with a naming convention.

→ Part of a series. This is part 1 and covers identifiers, delimiters, commas, and aliases. For the layout of longer statements, continue with Part 2 — Structure and Formatting.

What this article covers:

Identifiers and delimiters — when to use square brackets, when quotation marks, and what counts as a regular identifier
Comma, semicolon, spacer — the small separators with a big impact on readability
Table aliases and qualified column names — why systematic T01-style aliases scale better in wide statements than mnemonic abbreviations
Box selection as the killer argument for leading commas

SSMS serves as the example editor. The principles apply equally to DataGrip, VS Code, and DBeaver. The SQL examples reference AdventureWorksDW2017.

Why a Naming Convention at All?

Capital letters are an established device in most written languages to emphasise individual words. In programming, that translates into notations such as CamelCase (each compound word starts with a capital), camelCase (the same, except the first word is lower case), or snake_case (everything lower case with underscores between words). Conventions like these are bundled into a naming convention — a deliberate decision about how identifiers, function names, and data types should be written. In practice, however, you'll often find that developers don't even hold to their own preferred convention.

Sticking to a naming convention improves readability of any text, and of code in particular. tHE SAME Sentence Written Once Again With DEVIATIONS from the generally KNOWN convention that nouns are CAPITALISED and verbs and adjectives are NOT — and the text Turns Unreadable: "adherence to a NAMING convention IMPROVES The readability of text In General And of code In Particular."

The following statement comes from the view vTimeSeries in the database AdventureWorksDW2017, slightly reworked. It does not follow any naming convention:

  1: SELECT 
  2: case [Model] 
  3: WHEN 'Mountain-100' THEN 'M200' when 'Road-150' THEN 'R250' when 'Road-650' THEN 'R750' 
  4: WHEN 'Touring-1000' THEN 'T1000' ELSE LEFT(Model, 1) + Right([Model], 3) 
  5: END + ' ' + [Region] AS [ModelRegion] ,(convert(Integer, CalendarYear) * 100) + CONVERT(int, Month) AS [TimeIndex] 
  6: ,SUM(Quantity) AS [Quantity] 
  7: ,sum(Amount) AS Amount, calendaryear,[Month]
  8: ,[dbo].[udfbuildiso8601date] ([CALENDARYEAR], [Month], 25)
  9: as reportingdate
 10: FROM [dbo].[vDMPrep] 
 11: where [Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250', 
 12: 'Road-650', 'Road-750', 'Touring-1000') 
 13: GROUP BY CASE [Model] 
 14: WHEN 'Mountain-100' THEN 'M200' WHEN 'Road-150' THEN 'R250' WHEN 'Road-650' THEN 'R750' 
 15: WHEN 'Touring-1000' THEN 'T1000' ELSE Left(Model,1) + Right(Model,3) 
 16: end+' '+[Region] ,(Convert(Integer, [CalendarYear]) * 100) + Convert(Integer, [Month])
 17: ,CalendarYear,[Month],[dbo].[udfBuildISO8601Date] ([CalendarYear], [Month], 25);

In the version below, function names are upper case, every field name uses delimiters, and data types are lower case. Every element is laid out consistently:

  1: SELECT 
  2:     CASE [Model] 
  3:        WHEN 'Mountain-100' THEN 'M200' 
  4:        WHEN 'Road-150'     THEN 'R250' 
  5:        WHEN 'Road-650'     THEN 'R750' 
  6:        WHEN 'Touring-1000' THEN 'T1000' 
  7:        ELSE LEFT([Model], 1) + RIGHT([Model], 3) 
  8:     END + ' ' + [Region] AS [ModelRegion] 
  9:    ,(CONVERT(int, [CalendarYear]) * 100) + CONVERT(int, [Month]) AS [TimeIndex] 
 10:    ,SUM([Quantity])  AS [Quantity] 
 11:    ,SUM([Amount])    AS [Amount]
 12:    ,[CalendarYear]
 13:    ,[Month]
 14:    ,[dbo].[udfBuildISO8601Date]([CalendarYear], [Month], 25) AS [ReportingDate]
 15: FROM 
 16:    [dbo].[vDMPrep] 
 17: WHERE 
 18:    [Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250', 'Road-650', 'Road-750', 'Touring-1000') 
 19: GROUP BY 
 20:     CASE [Model] 
 21:        WHEN 'Mountain-100' THEN 'M200' 
 22:        WHEN 'Road-150'     THEN 'R250' 
 23:        WHEN 'Road-650'     THEN 'R750' 
 24:        WHEN 'Touring-1000' THEN 'T1000' 
 25:        ELSE LEFT([Model], 1) + RIGHT([Model], 3) 
 26:     END + ' ' + [Region]
 27:    ,(CONVERT(int, [CalendarYear]) * 100) + CONVERT(int, [Month])
 28:    ,[CalendarYear]
 29:    ,[Month]
 30:    ,[dbo].[udfBuildISO8601Date]([CalendarYear], [Month], 25);

Whether you like the formatting or not is a personal call. Either way, the second statement is, at a glance, tidier and easier to grasp.

The sections that follow walk through the most important parts of a naming convention. The list is not exhaustive; treat it as a starting point. One thing is worth saying up front: some of it is technical fact (how QUOTED_IDENTIFIER behaves, say, or how Postgres folds case), but most of it is reasoned convention — a choice a team can just as well make differently, as long as it makes that choice consistently. A second part picks up with best practices for the structure of SQL statements.

Regular Identifiers

Every database object has a name — its identifier. Each database vendor defines rules for what a valid identifier looks like. In SQL Server, identifiers are typically capped at 128 characters and must not contain spaces. The exact definition of a regular identifier is in the online documentation:

learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers

For practical purposes, though, that definition is framed too generously. A convention worth having draws tighter boundaries than the database system demands.

Before settling on a spelling style, take a look at how your database actually treats identifiers. SQL Server with a case-insensitive collation does not distinguish between upper and lower case: FactInternetSales and factinternetsales point to the same table. That is the usual situation, since the installation defaults are case-insensitive, but the collation is in fact selectable per instance, per database, and even per column. Case-insensitivity gives you the freedom to choose an emphatic notation such as CamelCase without breaking your statement. Postgres, on the other hand, folds unquoted identifiers to lower case: FactInternetSales becomes factinternetsales internally. You can still write CamelCase without quotes, but it loses its capitalisation along the way. The only way to keep it is "double quotes", which becomes a burden on every statement you write. That is why snake_case is the common convention in the Postgres world.

Behind this blog are ten years of SQL Server practice with CamelCase as a settled convention. Since the switch to Postgres three years ago, snake_case has taken over there. The full depth of case-sensitivity differences between the two engines deserves its own article, and one is on the way. The examples here stay with the CamelCase notation that is typical for SQL Server and comes with AdventureWorksDW2017 anyway.

The underscore _ is a widely used word separator and a legal part of a regular identifier. In the SQL Server world it can nonetheless be avoided for the most part: CamelCase serves the same purpose and keeps identifiers more compact.

Special characters such as @, #, and $ are permitted by the definition, but under this blog's convention they have no place in an identifier. Technically they are allowed even without delimiters inside a regular identifier; they only carry a special meaning in leading position, where @ marks a variable and # a temporary table. That double role is precisely the problem. Using the characters in column or table identifiers creates visual noise and invites confusion with those special meanings:

  1: SELECT 
  2:     [EnglishDayNameOfWeek] AS [English@DayNameOfWeek]
  3:    ,[SpanishDayNameOfWeek] AS [Spanish#DayNameOfWeek]
  4:    ,[FrenchDayNameOfWeek]  AS [French$DayNameOfWeek]
  5: FROM 
  6:    [dbo].[DimDate];

By the definition, regular identifiers may even contain Unicode letters from any language. This blog's recommendation nonetheless limits the choice to the letters of the Latin alphabet [a-zA-Z] plus the digits [0-9] where digits are unavoidable. That reduces the rules for a good identifier to just two:

Letters of the Latin alphabet only
Digits as a fallback

A disciplined approach to regular identifiers always goes hand in hand with the development of a naming convention for the objects themselves.

Delimiters

Once an identifier contains a space, it stops being a regular identifier. Using non-regular identifiers is bad style. They are nonetheless permitted as long as they are wrapped either in quotation marks or in square brackets.

Double quotation marks are the form the SQL standard provides for delimiting identifiers. Microsoft additionally allows square brackets, diverging from the standard. This blog prefers the brackets as the proprietary form of delimiter.

Delimiters set identifiers apart clearly from the other language elements of a SQL statement and so contribute a great deal to readability. This blog's convention therefore applies delimiters throughout, regardless of whether an identifier is regular or not:

Schemas
Tables
Views
Column names
Aliases
All programmable objects (functions, stored procedures etc.)

This is a deliberate house convention, not a general best practice. For regular identifiers the delimiters are optional according to the Microsoft documentation, and plenty of teams quote only where they have to. Whoever opts for quoting throughout gains the visual separation, at the price of more characters per identifier.

The statement below is identical to the second one in the overview, but written without any delimiters:

  1: SELECT 
  2:     CASE Model 
  3:        WHEN 'Mountain-100' THEN 'M200' 
  4:        WHEN 'Road-150'     THEN 'R250' 
  5:        WHEN 'Road-650'     THEN 'R750' 
  6:        WHEN 'Touring-1000' THEN 'T1000' 
  7:        ELSE LEFT(Model, 1) + RIGHT(Model, 3) 
  8:     END + ' ' + Region AS ModelRegion 
  9:    ,(CONVERT(int, CalendarYear) * 100) + CONVERT(int, Month) AS TimeIndex 
 10:    ,SUM(Quantity)  AS Quantity 
 11:    ,SUM(Amount)    AS Amount
 12:    ,CalendarYear
 13:    ,Month
 14:    ,dbo.udfBuildISO8601Date(CalendarYear, Month, 25) AS ReportingDate
 15: FROM 
 16:    dbo.vDMPrep 
 17: WHERE 
 18:    Model IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250', 'Road-650', 'Road-750', 'Touring-1000') 
 19: GROUP BY 
 20:     CASE Model 
 21:        WHEN 'Mountain-100' THEN 'M200' 
 22:        WHEN 'Road-150'     THEN 'R250' 
 23:        WHEN 'Road-650'     THEN 'R750' 
 24:        WHEN 'Touring-1000' THEN 'T1000' 
 25:        ELSE LEFT(Model, 1) + RIGHT(Model, 3) 
 26:     END + ' ' + Region
 27:    ,(CONVERT(int, CalendarYear) * 100) + CONVERT(int, Month)
 28:    ,CalendarYear
 29:    ,Month
 30:    ,dbo.udfBuildISO8601Date(CalendarYear, Month, 25);

Plenty of auto-generated scripts from Microsoft tooling use square brackets as delimiters. That is not a documented recommendation, but it is an observable pattern: SQL Server Management Studio (SSMS) emits brackets in the SELECT and DDL statements generated through the context menu on a table. Microsoft is not entirely consistent here, though: when you create a view through the wizard, or look at the SQL panel of the Edit feature, delimiters are dropped wherever they can be. The two screenshots below — both showing auto-generated SQL — make a fine case study in unmaintainable code:

View

Edit Feature

One restriction applies to quotation marks as delimiters: they only work when the SQL Server setting QUOTED_IDENTIFIER is set to ON (the default in SSMS and in the common client libraries):

SET QUOTED_IDENTIFIER ON;

With the setting OFF, SQL Server reads double quotes as string literals, and non-regular identifiers can only be written with square brackets. The brackets work regardless of this setting — a practical advantage of the proprietary form. ON is more than a matter of style, too: several SQL Server features require it, among them indexed views, indexes on computed columns, and filtered indexes.

Details in the online documentation:

learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql

The Spacer

What is meant here is the vertical spacer, that is, the blank line. The natural reading direction of a SQL statement is left to right and top to bottom.

While general-purpose code consists of many short statements, SQL is designed to do a lot of work in a single statement. An SQL statement can easily span a hundred lines or more. That makes writing a good SQL statement a particular challenge. A key criterion for grasping the structure and intent of a statement quickly isn't only a clear layout and consistent formatting, but also compactness. On a typical monitor at a sensible resolution, SSMS shows around forty lines of SQL when only a query window is open and no result pane. In day-to-day use, twenty-five to thirty lines is the realistic maximum.

There are colleagues in our trade who insert a blank line after every line

of code. Excessive blank lines force the reader to lean on the navigation

keys or the mouse wheel just to get from one end of the statement to the

other. Worse, the overall context of the statement becomes much harder

to take in.

Blank lines can be a useful stylistic device to separate logical blocks. Overusing them, however, makes the statement harder to read.

The Semicolon

The SQL standard provides for the semicolon as the terminator of a statement. SQL Server, at least, is forgiving here and doesn't force you to use one. There are only a handful of cases where the semicolon is mandatory.

One example: when using a Common Table Expression, the statement preceding the keyword WITH must end with a semicolon. To sidestep the problem, many developers write the leading WITH as ;WITH. Formally, the semicolon belongs to the preceding statement — ;WITH is a compatibility idiom, not a syntax of its own.

Other engines are stricter, or more precisely their tooling and procedural languages are. A single statement handed to Postgres directly by a driver works without a trailing semicolon. In psql and in multi-statement scripts such as pg_dump output, the semicolon marks the end of a statement, and inside PL/pgSQL function bodies it is part of the syntax as a statement terminator. Oracle follows the same pattern: in PL/SQL blocks (BEGIN … END;) the semicolon is required, while in clients such as SQL*Plus its role depends on the execution context.

Using the semicolon consistently is a sign of care either way, and it helps readability. What it does not do is make a statement portable — SQL dialects differ in entirely different places, from data types through functions to the procedural language. What it does remove is an unnecessary syntactic dependency on the client and the execution context.

The Comma

Field lists in SQL are separated by commas. Some developers put the comma before the field name, others put it after. The pro/con discussion usually centres on how easy it is to add or remove a field. The real reason the comma belongs at the front, though, is readability and the option to format the statement with box selection (called "column editor" or "Spaltenauswahl" in SSMS, "Box Selection" in VS Code and DataGrip). There is more on box selection in the article The Functional Aesthetics of SQL.

Leading Comma

  1: SELECT 
  2:     [EnglishDayNameOfWeek]
  3:    ,[SpanishDayNameOfWeek]
  4: FROM 
  5:    [dbo].[DimDate];

If you want to add another field, say FrenchDayNameOfWeek, it's easier to add it at the end — you only insert the text ,[FrenchDayNameOfWeek] after [SpanishDayNameOfWeek]:

  1: SELECT 
  2:     [EnglishDayNameOfWeek]
  3:    ,[SpanishDayNameOfWeek]
  4:    ,[FrenchDayNameOfWeek]
  5: FROM 
  6:    [dbo].[DimDate];

If you want the new field at position one, you need two edits: insert the line [FrenchDayNameOfWeek] before [EnglishDayNameOfWeek], and prepend a comma to [EnglishDayNameOfWeek]:

  1: SELECT 
  2:     [FrenchDayNameOfWeek]
  3:    ,[EnglishDayNameOfWeek]
  4:    ,[SpanishDayNameOfWeek]
  5: FROM 
  6:    [dbo].[DimDate];

Trailing Comma

When the comma is written after each field, the situation reverses: it's easier to add a new field at position one than at the end.

Readability

The comma is a separator. It marks the transition from one field to the next. When it sits at the end of each field, it loses its separating character because field names have different lengths.

In the statement below, it isn't immediately obvious whether the identifier [ProductName] is a column name or has some other role. The reader has to look at the line above to realise that [ProductName] is the alias for [EnglishProductName]:

  1: SELECT 
  2:     [EnglishProductName] AS
  3:     [ProductName],
  4:     [Size],
  5:     [Color],
  6:     [ListPrice],
  7:     [DealerPrice]
  8: FROM 
  9:    [dbo].[DimProduct];

When the comma sits in front of each field, the ambiguity goes away. It is immediately clear that [ProductName] is not a separate field — there is no comma in front of it, so it must belong to the line above:

  1: SELECT 
  2:     [EnglishProductName] AS
  3:     [ProductName]
  4:    ,[Size]
  5:    ,[Color]
  6:    ,[ListPrice]
  7:    ,[DealerPrice]
  8: FROM 
  9:    [dbo].[DimProduct];

The Comma and Box Selection

Adding aliases to every field of the following statement is a little awkward:

  1: SELECT 
  2:     [EnglishProductName]
  3:    ,[Size]
  4:    ,[Color]
  5:    ,[ListPrice]
  6:    ,[DealerPrice]
  7: FROM 
  8:    [dbo].[DimProduct];

Every comma has to move at least one position to the right, and the keyword AS and the alias have to be inserted in front of it. That applies to every row of the field list, one at a time, by hand:

  1: SELECT 
  2:     [EnglishProductName] AS [AliasEnglishProductName]
  3:    ,[Size] AS [AliasSize]
  4:    ,[Color] AS [AliasColor]
  5:    ,[ListPrice] AS [AliasListPrice]
  6:    ,[DealerPrice] AS [AliasDealerPrice]
  7: FROM 
  8:    [dbo].[DimProduct];

The same end result can be achieved with far less work if the SQL statement is laid out so that box selection works on it:

No commas need to move. With box selection, AS is typed once, the field names are used as the basis for the alias, copied as a block after AS, and the prefix Alias is prepended — again with box selection. The best part: the effort is essentially independent of the number of rows you have to process.

Box selection becomes truly effective only with leading commas. That is the killer argument for putting the comma at the front.

A note on the SSMS bias. This argument is largely about SSMS and its classic box selection. DataGrip and other modern editors offer more powerful refactoring tools — multi-cursor at arbitrary positions, automatic alias generation via refactor commands, semantic search-and-replace across the whole codebase. In that world you save the same effort even without leading commas. The box-selection argument carries less weight there. The readability arguments (see the "Readability" sub-section) hold regardless of the editor.

Function Names

SSMS highlights function names in pink, which makes them easy enough to spot. Since not every editor does syntax highlighting, function names should also be visually distinguishable by consistent upper- or lower-case spelling.

In other words: function names should be either fully upper case or fully lower case.

Microsoft itself, like most database vendors, writes function names in upper case in the online documentation. That documentation spelling is not a binding standard, but it is a convenient orientation — this blog's convention follows the upper-case form.

Table Aliases

A widely used practice is to derive a table alias as a "speaking" abbreviation from the table name. For the table FactInternetSales one might use the alias IS, taking the starting letters of the compound words in the table name (ignoring the Fact prefix). Since IS is a reserved word, that alias must be wrapped in delimiters. The example also shows a weakness of mnemonic abbreviations: they can collide with reserved words and then force additional quoting. For other tables, you might end up with aliases like these:

Table	Alias
DimCustomer	CUST
DimProduct	P
DimProductCategory	PC
DimProductSubcategory	PSC

A SELECT statement built on those tables might look like this:

  1: SELECT
  2:     CUST.[LastName]
  3:    ,CUST.[FirstName]
  4:    ,P.[EnglishProductName]
  5:    ,PC.[EnglishProductCategoryName]
  6:    ,PSC.[EnglishProductSubCategoryName]
  7:    ,[IS].[OrderDate]
  8: FROM
  9:    [dbo].[FactInternetSales] [IS]
 10:    LEFT JOIN [dbo].[DimCustomer] CUST
 11:    ON
 12:      [IS].[CustomerKey] = CUST.[CustomerKey]
 13:    LEFT JOIN [dbo].[DimProduct] P
 14:    ON
 15:      [IS].[ProductKey] = P.[ProductKey]
 16:    LEFT JOIN [dbo].[DimProductSubcategory] PSC
 17:    ON
 18:      P.[ProductSubcategoryKey] = PSC.[ProductSubcategoryKey]
 19:    LEFT JOIN [dbo].[DimProductCategory] PC
 20:    ON
 21:      PC.[ProductCategoryKey] = PSC.[ProductCategoryKey];

The uneven indentation of the field names is a side-effect of the different lengths of the aliases — somewhere between one and four characters. The field list looks "restless", and as soon as more language elements join the party (functions, CASE expressions etc.) it can become hard to read quickly.

Now imagine a SELECT over twenty tables or more. At some point it becomes hard to come up with a meaningful alias for every table. From roughly the fifth alias onward — that is a rule of thumb, not a hard boundary — the derivation from table names rarely improves readability any further, because the aliases are simply too cryptic.

Wouldn't systematic — possibly even indexed — aliases be easier to identify in a complex statement?

A systematic alias scheme could be defined like this:

Aliases must be a fixed number of characters
Aliases are indexed (with one or more leading letters)

If, for instance, you use the letter T for Table (or F for Fact table, D for Dimension, etc.) followed by a two-digit 1-based index, you end up with aliases such as T01, T02, D01, F01, ….

Using those aliases, the statement above reads more cleanly:

  1: SELECT
  2:     T02.[LastName]
  3:    ,T02.[FirstName]
  4:    ,T03.[EnglishProductName]
  5:    ,T05.[EnglishProductCategoryName]
  6:    ,T04.[EnglishProductSubCategoryName]
  7:    ,T01.[OrderDate]
  8: FROM
  9:    [dbo].[FactInternetSales] T01
 10:    LEFT JOIN [dbo].[DimCustomer] T02
 11:    ON
 12:      T01.[CustomerKey] = T02.[CustomerKey]
 13:    LEFT JOIN [dbo].[DimProduct] T03
 14:    ON
 15:      T01.[ProductKey] = T03.[ProductKey]
 16:    LEFT JOIN [dbo].[DimProductSubcategory] T04
 17:    ON
 18:      T03.[ProductSubcategoryKey] = T04.[ProductSubcategoryKey]
 19:    LEFT JOIN [dbo].[DimProductCategory] T05
 20:    ON
 21:      T05.[ProductCategoryKey] = T04.[ProductCategoryKey];

The field names in the SELECT list line up cleanly.

The real killer argument is the same here, too: aliases of equal length are what makes box selection useful in the first place.

There's another important reason to use aliases at all, and it applies to mnemonic and systematic ones alike: Intellisense (in SSMS) only really works once aliases are in place. When the developer types an alias followed by a dot, a context menu opens listing only the columns of the corresponding table.

When the cursor sits after the dot, the context menu can also be invoked manually with the shortcut Ctrl+Space. It works without a preceding alias as well. In that case, however, SSMS effectively offers the entire T-SQL vocabulary at once.

Aliases make SQL code easier to read and therefore more maintainable. In statements spanning several tables they are practically indispensable. This blog's convention applies them to simple single-table statements as well, for the sake of uniformity and Intellisense comfort.

Qualified Column Names

As soon as a statement references multiple tables, sooner or later the same column name appears in more than one of them and stops being unambiguous. In the example below we want to return name and phone number for both the employee and the reseller of a FactResellerSales fact:

The statement is not executable because the column Phone exists in both dimensions DimEmployee and DimReseller. SQL Server aborts with two errors:

  1: Msg 209, Level 16, State 1, Line 4
  2: Ambiguous column name 'Phone'.
  3: Msg 209, Level 16, State 1, Line 6
  4: Ambiguous column name 'Phone'.

Specifying a table alias in front of the column name is mandatory here:

T01.[Phone] AS [Employee_Phone]

or — if no table aliases are used — the table name itself has to precede the column:

[DimEmployee].[Phone] AS [Employee_Phone]

The latter, however, does nothing for readability.

A fully qualified object name in SQL Server consists of up to four parts: server, database, schema, and object. A column reference adds the column as a fifth element:

[Server].[Database].[Schema].[Table].[Column]

An example with fully qualified table names that include the database name:

Any qualification beyond the schema prevents the application from being deployable in a database that doesn't share the name AdventureWorksDW2017. Cases like that do occur in practice: ETL pipelines whose tables were fully qualified down to the database name ([Database].[Schema].[Table].[Column]) could not be deployed to production once they were finished.

Engine note: Postgres. The five-part scheme Server.Database.Schema.Table.Column only applies in SQL Server. Postgres by default does not allow cross-database queries: a cluster does hold several databases, but a connection is bound to exactly one of them, so the Database. element doesn't exist in practice. If you really need to read or write across database boundaries, you'll need the extensions postgres_fdw (Foreign Data Wrapper) or dblink. Both are extra setup, not a default. In practical terms this means that within a Postgres database, Schema.Table.Column (three parts) is the relevant qualification, while in SQL Server it is one option among several. The recommendation stays the same in both worlds: never qualify beyond the schema level.

Conclusion

A naming convention is the entry point to maintainable SQL code: regular identifiers built from letters and, where needed, digits; delimiters applied consistently; function names in upper case. The small separators have the largest effect of all. The leading comma keeps field lists readable and ready for box selection, the semicolon makes statements unambiguous towards the client and the execution context, and sparing blank lines keep the whole picture on a single screen.

Systematic aliases such as T01 play to their strength in wide statements and line the field list up cleanly. Qualified column names belong up to the schema level and no further. Which individual rule you pick matters less than it seems: SQL becomes maintainable through a consistent convention that makes structure visible and changes easy. Part 2 — Structure and Formatting builds on this and covers the layout of longer statements.

FAQ

Leading comma — isn't that unusual?

When reading, the comma loses its separating role if it sits at the end of a line (see the section "Readability"). When writing, the leading comma saves the most effort the moment box selection comes into play. Modern auto-formatters make the comma position configurable. sqlfluff (T-SQL and Postgres) places commas at the end of the line by default and only switches to leading commas through its layout configuration (line_position = leading). pgFormatter (Postgres) likewise defaults to the trailing comma and offers --comma-start for leading ones.

Do you need square brackets in Postgres too?

No. Postgres does not support [brackets]. Its delimiters are double quotes: "EnglishProductName". There is no QUOTED_IDENTIFIER setting either. Identifier quoting in Postgres is always on. Postgres folds identifiers without quotes to lower case (MyColumn becomes mycolumn). Identifiers in quotes keep their spelling and have to be quoted exactly that way on every reference. If your codebase is multi-engine in the long run, "double quotes" are ANSI-SQL-compliant and work in both worlds — the [brackets] are SQL Server-specific.

Systematic T01-style aliases vs. speaking aliases — which is better?

It's a trade-off. Speaking aliases (CUST, P, PC) are mnemonic and work fine as long as the statement only has three or four tables. As soon as five or more tables come into play — typical for ETL pipelines or wide reporting queries — the mnemonic edge fades and the alignment suffers from the varying lengths. Systematic T01-style aliases scale, stay the same length, and make box selection effortless. The price is semantic: T05.[CustomerKey] doesn't tell you the table. Anyone who wants to know jumps to the FROM clause. Systematic aliases optimise for visual structure and mechanical editing, then, while speaking aliases optimise for orientation in the content. As a rule of thumb, this blog uses speaking aliases up to four tables and systematic ones from five onward.

What if my team uses a different convention?

Consistency beats purity. If the team has settled on trailing commas or speaking aliases, applying that convention consistently is more important than picking the "better" variant.

In practice this is one of the hardest points overall. In every larger team, each developer has a very personal sense of what "looks good" and what doesn't. That individual matter of taste is exactly what stands in the way of team-wide uniformity, and experience across many projects bears this out again and again. All the more reason for the convention that the team has agreed on to be applied consistently across every team member. Only then do the SQL statements stay readable and maintainable for everyone involved.

Multi-cursor — does it replace box selection?

Modern editors like VS Code, DataGrip, and DBeaver provide true multi-cursor support (Ctrl+Alt+Down Arrow or Alt+Click) that goes beyond the rectangular box selection: cursors can sit at arbitrary positions rather than only along a rectangular column. SSMS only has classic box selection (Alt+Shift+Arrow). Azure Data Studio, once part of this line-up, was retired by Microsoft on 28 February 2026; the recommended successor is VS Code with the mssql extension. Conceptually, multi-cursor covers the same need and goes beyond it. Box-selection-friendly formatting remains the prerequisite either way. If you work across several editors, format once cleanly rather than once per tool.

Three Severity Levels, Not Pass/Fail — and Why Severity Decides Where the Rule Lives

Marcus — Sun, 23 Aug 2026 21:39:01 +0000

Most data quality systems don't die of bad checks. They die of a missing field in the rule model: the severity. A missing country code in three out of 80,000 rows blocks the nightly load, someone switches the check off "temporarily", and from that moment everything runs unchecked. Steering data quality with severity levels instead of binary pass/fail escapes that trap. And severity can do more: it decides whether a rule belongs in the target schema as a constraint or in the pipeline.

Key takeaways:

Binary pass/fail ends in one of two dead ends: either a triviality blocks the whole load, or everything passes and nobody reads the findings.
Three levels are enough: an error blocks, a warning is visible without blocking, an information merely documents.
Severity is a routing decision: only an error rule is a candidate for a CHECK or UNIQUE constraint in the target schema — warnings and information stay in the pipeline.
Promoting has a price: when a warning becomes an error, the existing data suddenly has to be clean. NOT VALID and VALIDATE CONSTRAINT make the transition controllable.
The limits can be named precisely: cross-row, cross-table and time-based rules are beyond what a constraint can do. Those belong in the pipeline, permanently.

Prerequisite: Postgres as the example engine and the framework from the sub-hub Data Quality Checks with SQL with the shared error table, the rule configuration dq.check_rule and the quality gate. The principle itself is engine-neutral and applies just as well to SQL Server or any other system with constraints.

Why binary fails

A binary check system knows exactly one question per record: pass or fail. That sounds clean and leads, in practice, into one of two dead ends.

Dead end one: everything that fails blocks. Then a missing email address stops the same load as a duplicate customer number. The process stands still, the business day waits, and the person on call makes a decision at three in the morning that actually belongs in a rule review: they switch the check off. Not out of carelessness, but because the system offers them no other gradation.

Dead end two: nothing blocks, everything is merely logged. Then a findings table fills up that somebody still reads in the beginning. A few weeks later thousands of entries sit there, important ones next to trivial ones, and because the system itself doesn't say which of them demand action, at some point nobody looks anymore. The check keeps running and is dead anyway.

Both reflexes are understandable. The problem is not that people handle the system badly. The problem is that a binary model withholds the one piece of information that should steer the handling: how bad is this finding?

The three levels

Three severity levels answer exactly that question, and you rarely need more than three. The pattern is not new: syslog levels and linters have known it for decades. What makes it interesting is what each level concretely triggers in the ETL process:

Error (E) blocks. A record with an error finding does not pass the quality gate and does not reach the target layer. Error is the right level for everything the load would fail on at the target anyway: missing mandatory values, duplicate keys, unknown references.
Warning (W) is visible but does not block. The record flows on, the finding stays logged. That covers the largest share of real-world data problems: things that are odd, unclean or worth a look, but must not hold up the process.
Information (I) merely documents. No pressure to act, no blocking, pure awareness, for instance a missing phone number whose frequency you want to observe.

In the framework's rule model, the severity (some rule models call the field "criticality") is a column of the configuration table, with a strict default and a CHECK on itself:

,severity   char(1) NOT NULL DEFAULT 'E'
,CONSTRAINT ck_check_rule_severity  CHECK (severity IN ('E', 'W', 'I'))

The default E is a deliberate decision: whoever writes a rule means it seriously at first. Downgrading to a warning is an active, documented step. The opposite default would silently turn new rules into mere observers. Five rules show the three levels in the configuration table's format:

INSERT INTO dq.check_rule
    (check_type,   schema_name, table_name, id1_column,    check_column,   where_clause,           severity, message)
VALUES
    ('constraint', 'staging',   'customer', 'customer_id', 'country_code', 'country_code IS NULL', 'E', 'country code is mandatory in the target schema')
   ,('constraint', 'staging',   'customer', 'customer_id', 'age',          'age IS NULL',          'E', 'age is mandatory in the target schema')
   ,('constraint', 'staging',   'customer', 'customer_id', 'age',          'age < 18',             'W', 'customer under 18 - needs business review')
   ,('constraint', 'staging',   'customer', 'customer_id', 'email',        'email IS NULL',        'W', 'email missing - needs business review')
   ,('constraint', 'staging',   'customer', 'customer_id', 'phone',        'phone IS NULL',        'I', 'phone number not provided - for information only');

The information rule deliberately targets a field that is optional from a business point of view: a missing phone number is not a defect, but a value whose rate you want to know. On age, by contrast, two rules with different levels sit side by side: if the age is missing entirely, that is an error, because the target treats the column as mandatory. If it is merely conspicuously low, that is a warning. The choice of level is a business statement, not a technical one.

What the levels mean in practice is shown by a run against seven demo rows (one clean, one per severity, and three combinations). The framework's staging table is extended by the optional column phone for this:

INSERT INTO staging.customer (customer_id, country_code, email, age, phone) VALUES
    (1, 'DE', 'a@example.com', 44,   '+49 30 1234567')   -- clean
   ,(2, NULL, 'b@example.com', 33,   '+49 40 2345678')   -- E: country code missing
   ,(3, 'AT', 'c@example.com', 17,   '+43 1 3456789')    -- W: under 18
   ,(4, 'CH', 'd@example.com', 52,   NULL)               -- I: phone number missing
   ,(5, NULL, 'e@example.com', NULL, '+41 44 5678901')   -- E+E: country code and age missing
   ,(6, NULL, 'f@example.com', 16,   '+49 89 6789012')   -- E+W: country code missing, under 18
   ,(7, NULL, NULL,            NULL, NULL);              -- E+E+W+I: everything at once

SELECT dq.fn_run_checks('staging', 'customer') AS total_findings;  -- 11

The runner writes the severity counters back into the source table, one per record and level:

customer_id	country_code	email	age	phone	sys_error	sys_warning	sys_info
1	DE	a@example.com	44	+49 30 1234567	0	0	0
2		b@example.com	33	+49 40 2345678	1	0	0
3	AT	c@example.com	17	+43 1 3456789	0	1	0
4	CH	d@example.com	52		0	0	1
5		e@example.com		+41 44 5678901	2	0	0
6		f@example.com	16	+49 89 6789012	1	1	0
7					2	1	1

The quality gate queries only one of the three columns:

SELECT
    customer_id
   ,country_code
   ,email
   ,age
   ,phone
FROM
   staging.customer
WHERE
   sys_error = 0;

Three of the seven rows pass the gate. The warning row and the information row flow along, the four rows with at least one error stay behind — logged, with a plain-text message, findable for follow-up. The combination rows show in passing that sys_error is a counter, not a flag, and that the three counters run independently of each other: row 5 carries two errors, row 6 one error and one warning, row 7 fills all three counters at once. The gate still asks only for sys_error = 0. No on-call engineer has to decide at night whether a missing phone number matters more than the daily close. That decision was made long ago, in the rule review, when someone wrote W into the row.

Severity is a routing decision

Up to here, severity looks like a label on the finding. It can do more: it answers the question of where a rule may be enforced.

The reasoning behind it is simple. A constraint in the target schema is the hardest form a rule can take: on its own, it knows no exception, no log line and no "flows on anyway". Only the calling application can soften that, the constraint itself knows no gradation. Only a rule whose violation must truly never reach the target deserves that hardness. That is exactly the definition of the error level. A warning, on the other hand, is supposed to be allowed through, otherwise it would be an error. A constraint that rejects warning violations would contradict the rule's own definition. The routing follows from that:

E is a candidate for a constraint: NOT NULL, CHECK, UNIQUE, foreign keys. The rule is enforced twice: up front as a check in the source, hard as a guarantee at the target.
W and I stay in the pipeline: they exist only as check rules, produce findings and never block. A target constraint for them is not merely unnecessary, it is wrong.

Severity is therefore not a report field but the switch that decides about constraint derivation. How the error rules of the configuration can conversely be derived from the target schema is shown by this series' sibling article. The coupling works in both directions, and derived rules always carry E there, because their source is a hard constraint.

Concretely, the routing looks like this. The target layer materializes the two error rules as NOT NULL on country_code and age, while the warning rules and the information rule deliberately have no counterpart there:

CREATE TABLE core.customer
(
    customer_id   int   NOT NULL
   ,country_code  text  NOT NULL
   ,email         text
   ,age           int   NOT NULL
   ,phone         text
   ,CONSTRAINT pk_customer  PRIMARY KEY (customer_id)
);

INSERT INTO core.customer (customer_id, country_code, email, age, phone)
SELECT
    customer_id
   ,country_code
   ,email
   ,age
   ,phone
FROM
   staging.customer
WHERE
   sys_error = 0;
-- INSERT 0 3

The 17-year-old row and the row without a phone number arrive as well. That is exactly the intent: the warning has served its purpose (the finding sits in the log), and the target accepts the record. What would happen if someone materialized the warning rule as a constraint after all is shown by the direct attempt:

ALTER TABLE core.customer
   ADD CONSTRAINT ck_customer_adult CHECK (age >= 18);
-- ERROR: check constraint ck_customer_adult of relation customer
--        is violated by some row

The ALTER TABLE fails on its own existing data, on precisely the row the warning was explicitly supposed to let pass. The error is not an accident but the database pointing out the contradiction: this rule was classified as W, and a CHECK is the enforcement form of E. The same holds for the information rule with the opposite sign: its hard counterpart would be a NOT NULL on phone — technically perfectly expressible, but wrong, because an information must never block. The target allows the missing phone number deliberately, the pipeline merely keeps count.

Four places, one decision

Zooming out of the framework, there are four places where a data quality rule can live. Each can do something the others cannot, and each has a price:

Place	What it can do	What it costs
Source (pre-filter before the load)	finds all bad records up front, classifies by severity, the load carries on with the good ones	its own infrastructure: error table, rule configuration, runner
Pipeline (check steps in the process)	the most expressive option — every rule type, every severity, trends and reports	the rule lives next to the data, and every path around the pipeline bypasses it
Target schema (constraints)	the only guarantee that every write path respects, including the manual hotfix	knows only two outcomes, can only express error semantics, blocks the whole run of a set-based load when in doubt
Reporting (dashboards, analyses)	makes warnings and information visible over time, shows trends	enforces nothing, depends on the findings of the other places

Today's industry default clearly sits on the second place: tools like Great Expectations, Soda or dbt tests formulate checks in the pipeline, and most of them know severity gradations as well. dbt, for instance, distinguishes error and warn per test. That is a workable model, not a mistake. You should just be able to name what you are buying into: a rule that lives exclusively in the pipeline is valid only there. The colleague with the direct INSERT, the second load script, the weekend migration — none of them run through the pipeline, and none of them hit its checks. A constraint in the target schema has no such gap, because it sits in the data itself.

The four places are therefore not competitors you pick one of. The decision is made per rule, and severity is its first criterion: error rules deserve the double floor of pre-filter plus constraint, warnings and information belong in pipeline and reporting.

Why the source still checks first

If only error rules become constraints, why check in the source at all? You could simply let the CHECK do what it is there for.

The answer is already in the framework article, and it remains fully valid under the routing view: a constraint knows only two outcomes. The row fits, or the whole load breaks. When loading thousands of rows, "breaks" is the worst of all options, because a single bad row stops the complete process, and the error message names at best that one row, not the other nine that were still waiting behind it.

One qualification belongs here: this all-or-nothing applies to the set-based load, that is, a single INSERT … SELECT moving all rows in one transaction. That is exactly how an ETL process developed in SQL works, and dbt belongs on this side as well, because its models compile to set-based SQL statements. Row-based ETL tools like SSIS or Talend process the rows individually instead: a failing row can be routed out through an error output there, the remaining rows carry on, and the constraint stops only that one row rather than the whole load. You pay for that convenience with the row-by-row processing itself, which is considerably slower than a set-based load, and with error handling that lives inside the tool instead of in a queryable table. The pre-filter is the set-based answer to the same need: it does what a row-based tool's error output does — just up front, in set logic and with severity levels.

The error output also has a second, less obvious weakness: the checks in the data flow run sequentially. The first hit routes the row out, and the remaining checks never see it — unless you explicitly wire the error path through all further check steps, which quickly clutters the data flow. In practice that means: you fix the first error found, run the load again, find the second one, fix it, find the third. The pre-filter knows no such iterating, because every rule runs set-based across all rows. That is why row 7 of the demo sits in the log completely after a single run, with two errors, one warning and one information.

The pre-filter in the source and the constraint at the target are therefore not alternatives but two halves of the same error rule. The check in the source finds all records that would fail at the target, classifies them and lets the load carry on with the clean ones. The constraint at the target guarantees the rule even for everything that goes past the pre-filter: the manual hotfix, the forgotten second script. If the pre-filter fails, the load breaks loudly instead of silently accepting bad data. If the constraint is dropped, the pre-filter still checks. Only together do the two produce the property neither half has alone: complete findings and a hard guarantee.

So the question is not whether schema or pipeline. The question is which rule deserves both — and that is answered by the severity.

What happens when you promote a rule

Rules are not static. The business decides that customers under 18 must no longer be created: the warning from above is to become an error. In the configuration that is one statement:

UPDATE dq.check_rule
SET
   severity = 'E'
WHERE
       schema_name  = 'staging'
   AND table_name   = 'customer'
   AND where_clause = 'age < 18';

SELECT dq.fn_run_checks('staging', 'customer') AS total_findings;  -- 11

From the next run on, the 17-year-old row blocks at the gate (sys_error = 1). That is the easy part. The demanding part follows from the routing: an error rule is a constraint candidate, so the target schema should guarantee the new hardness as well. And right here waits the price of promotion — the existing data suddenly has to be clean. The 17-year-old row arrived at the target long ago, perfectly legitimately, under the old rule. The direct ADD CONSTRAINT fails on it, as seen above.

Postgres offers a controlled path for this transition. NOT VALID accepts the constraint immediately without checking the existing data. For new rows it applies from the first second anyway:

ALTER TABLE core.customer
   ADD CONSTRAINT ck_customer_adult CHECK (age >= 18) NOT VALID;

INSERT INTO core.customer (customer_id, country_code, email, age)
VALUES (9, 'DE', 'x@example.com', 16);
-- ERROR: new row violates check constraint ck_customer_adult

DELETE FROM core.customer WHERE age < 18;

ALTER TABLE core.customer VALIDATE CONSTRAINT ck_customer_adult;

The order is the point. First NOT VALID seals the future, then the existing data is cleaned up calmly (in the demo via DELETE, in practice more likely: routed out for business review), and only the final VALIDATE CONSTRAINT re-checks the existing rows and completes the guarantee. Between the two steps the constraint sits in a documented intermediate state: it applies to new rows while the existing data is still unchecked. Postgres shows this state in the catalog as convalidated = false. The VALIDATE does not lock the table against writes, it runs with a weak lock alongside normal operation. One subtlety in passing: a CHECK does not fire on NULL, so a missing age would pass ck_customer_adult without complaint. Here that case is already caught by the NOT NULL that the error rule age IS NULL materializes at the target anyway. The same pattern carries a retrofitted NOT NULL on an existing table. How to roll that out without downtime is shown in a dedicated article.

For SQL Server the principle holds with different mechanics: WITH NOCHECK likewise adds a constraint without checking the existing data. The difference sits in the aftermath. The constraint stays permanently marked as not trusted until a WITH CHECK CHECK CONSTRAINT re-validates the existing rows, and an untrusted constraint is ignored by the optimizer in plan decisions. Whoever does only the first step has the guarantee but gives away performance.

Demoting, the reverse path, is just as much a routing decision, by the way: E becomes W, so the corresponding constraint at the target has to go, otherwise it keeps blocking a rule that is only supposed to observe. Promoting pulls constraints in, demoting clears them away — configuration and schema move together.

Taken together, that is the real payoff of the three levels: a rule has a controlled lifecycle. It starts as an observation, proves itself, gets promoted and moves into the schema as a guarantee via NOT VALID and VALIDATE CONSTRAINT — and, when needed, walks the same path back. Severity is the control knob of that lifecycle, not just a label on the finding.

The honest limits

The routing "E becomes a constraint" has one restriction that hides in the word candidate: not every error rule can become a constraint. A CHECK sees exactly one row. Within that row it may compare several columns, a CHECK (end_date > start_date) is perfectly legitimate. But three rule families lie beyond its reach:

Cross-row rules. "A key may occur at most three times" is one row in the configuration as a check rule (max_occurrence = 3), but not expressible with the declarative constraints (UNIQUE, CHECK, foreign keys), because UNIQUE knows only cardinality 1. Sum, share and distribution rules belong here as well.
Cross-table rules. A CHECK may not contain a subquery, Postgres rejects that with a clear error message. The only cross-table guarantee the schema knows is the foreign key. Everything beyond it stays pipeline work, say "the discount code must match the customer group".
Time-based rules. Postgres silently assumes that a CHECK expression always yields the same result, but does not enforce that immutability: a CHECK (order_date <= current_date) is accepted, and that is precisely what makes it a trap. What is valid today is no longer valid tomorrow, and at the latest when restoring a dump or running a VALIDATE, existing rows fail that were correct when they were created. Rules with a time reference belong in the pipeline, where every run checks against the current reference date.

The direction of view matters: these rules do not land in the pipeline because sadly nothing better exists. The pipeline is the right place for them: it checks set-based, knows the run context and can still rate a violation as an error and block it at the gate. A cross-row error rule is an error without a constraint counterpart, and that is not a contradiction to the routing but its honest limit.

Decision matrix

The per-rule decision, condensed:

Rule type	Typical severity	Place of enforcement
Mandatory field the target enforces	`E`	pre-filter in the source + `NOT NULL` at the target
Hard value range (type bound, country-code format)	`E`	pre-filter + `CHECK` at the target
Key uniqueness	`E`	pre-filter + `PRIMARY KEY`/`UNIQUE` at the target
Reference to a master table	`E`	pre-filter + `FOREIGN KEY` at the target
Business anomaly (worth a look, not blocking)	`W`	pipeline, finding in the log
Pure observation (frequencies, missing optional values)	`I`	pipeline + reporting
Cross-row (cardinality > 1, sums, shares)	`E` or `W`	pipeline only — `UNIQUE` cannot do cardinality > 1
Cross-table beyond the foreign key	`E` or `W`	pipeline only — `CHECK` cannot hold a subquery
Time-based (date against a reference day)	mostly `W`	pipeline only — a constraint ages badly

Two readings sit in this table. Top to bottom: the harder the guarantee, the higher the rule stands, and only the E rows reach the schema. And across: the place is never "either source or target". Every constraint row carries both, because the pre-filter delivers the findings and the constraint the guarantee.

FAQ

Why is binary pass/fail not enough?
Because it withholds the urgency of a finding. Either every triviality then blocks the load, or nothing blocks and the findings list goes stale. The three severity levels error, warning and information separate "must not proceed" from "worth a look" and "for information only", and they make the reaction configurable per rule instead of negotiable per night shift.

Which check rules belong in a CHECK constraint?
Only error rules whose violation must never reach the target, and of those only the ones a constraint can express: checks on a single row without a time reference and without looking into other tables. Warnings never belong in a constraint, because a constraint that rejects non-blocking findings contradicts the rule's own definition.

What happens when I promote a warning to an error?
Two things. From the next run on, affected records block at the quality gate. And the target schema should follow, because an error rule deserves a constraint counterpart. For that, the existing data has to be clean: in Postgres, ADD CONSTRAINT … NOT VALID takes the guarantee on immediately for new rows, then the existing data is cleaned up and re-checked via VALIDATE CONSTRAINT.

Does a warning block the load?
No, and that is its purpose. A record with warning findings passes the quality gate, and the finding stays in the log with a plain-text message. Blocking is reserved for the severity error alone. A warning that would need to hold up the process is misclassified and should be promoted.

Does data quality belong in dbt and Great Expectations or in the database?
Both have their place. Pipeline tools like dbt tests, Great Expectations or Soda are expressive, well maintained and know severity gradations themselves. Their checks, however, only apply to paths through the pipeline. A direct INSERT past it hits none of them. Hard error rules therefore deserve an additional constraint in the target schema that every write path respects.

Framework and routines:

Data Quality Checks with SQL — the series' sub-hub: error table, rule configuration, runner and the quality gate whose severity mechanics this article deepens.
Validate Data with SQL — the WHERE routine: value ranges, mandatory fields and the NULL trap of three-valued logic.
Find Duplicates with SQL — the uniqueness routine: cardinality, composite keys and the NULL semantics of UNIQUE.
Find Orphaned Records with SQL — the lookup routine: checking referential integrity without foreign keys.
Derive Data Quality Rules from the Schema — the routing's opposite direction: target constraints become error rules of the configuration, projected mechanically.

Theory:

Data Quality: Dimensions and Error Classes — the conceptual frame: which dimensions the checks cover and which they don't.

Target schema and deployment:

Postgres Table Conventions — which keys and constraints a target table should carry so hard counterparts exist at all.
Adding a NOT NULL Column to an Existing Table — the same promotion pattern for mandatory fields: expand/contract without downtime.
Design Pattern // The Architecture of an ETL Process — the staging architecture in which the pre-filter and the gate have their place.

Database CI/CD with PostgreSQL — the Complete Lifecycle from Object File to Automated Deploy

Marcus — Mon, 03 Aug 2026 19:30:30 +0000

In many projects the database schema lives in the database instead of the repository — grown out of years of hand-run ALTERs, fully documented nowhere. It only becomes visible when a second environment is needed or a deploy breaks. Postgres database CI/CD flips that relationship: the repository describes the desired state, and every environment — from the throwaway database in CI to production — is built from the same versioned scripts. This article walks the complete lifecycle in six stations, deliberately without a migration framework.

The key points up front:

The lifecycle in six stations: bootstrap, object deploy, tests, evolution with data, CI gate, teardown.
Each station solves a concrete problem — and has its own deep-dive article in this cluster.
The tools: Bash, psql, and GitHub Actions — deliberately without Flyway or Liquibase.
The honest limit: when a migration tool is the better choice — and which stations it still will not take off your hands.
Everything runs: the command sequence comes from the open starter kit and is verified end-to-end against Postgres 17.

Prerequisite: Postgres, a bash, and a GitHub repository; Docker if you want to replay the lifecycle locally. All building blocks come from the open DI² Starter Kit on GitHub — the scripts, the example schema tree, and the CI pipeline live there in context.

The Lifecycle in Six Stations

"Introducing database CI/CD" often gets translated as "installing a migration tool". That falls short — a tool answers only part of the question. The complete lifecycle of a database environment has six stations, and each of them needs an answer, framework or not:

As a command sequence, the whole cycle is unspectacular — and that is precisely the ambition:

bash db/scripts/create.sh local        # Station 1: bootstrap — database, roles, schema
bash db/scripts/deploy.sh all local    # Station 2: object deploy — all schema objects
bash db/tests/run.sh                   # Station 3: object tests against a throwaway DB
bash db/scripts/deploy.sh all local    # second run: idempotency check + run-once skip
bash db/scripts/drop.sh local          # Station 6: teardown — database and roles gone for good

These five lines are verified end-to-end against Postgres 17 in a throwaway Docker container: bootstrap, full deploy, and all object tests run green, the second deploy acknowledges the run-once scripts with skipped (already applied), and after the teardown, database and roles are gone without a trace — pg_database no longer contains an entry for the created database, pg_roles none of the created roles. Station 4 (evolution with data) and station 5 (CI gate) have no commands of their own: the evolution lives in the deployed files themselves, and the gate runs the same sequence automatically on every pull request.

The following sections walk through the stations — each with the problem the station solves and a pointer to the article that provides the depth.

Station 1: Bootstrap — Creating Database, Roles, and Schema Reproducibly

Before any schema object can be deployed, the environment has to exist: the database itself, the extensions, the application schema, and the roles. The bootstrap creates all of that with one script call — create.sh <env> — and it does so for every environment with the same scripts. local, dev, and prod differ only in a small configuration file with connection coordinates, not in the procedure.

Two decisions shape this station. First, the role separation: four roles emerge, each with a clear job — the database owner, the schema owner (under which every later deploy runs), a read-write group role, and the service account the application connects with. Passwords never end up in files: the script only needs the existing superuser password; for the three new roles you choose your own passwords and, ideally, put them straight into the password manager.

Second, the drop-and-recreate semantics: the bootstrap is deliberately not idempotent. If the database or roles already exist, a preflight aborts and points to the teardown — a half-overwritten setup would be worse than an aborted one. Reproducibility here means: an environment is built entirely from the versioned scripts — not adjusted by hand after the fact.

Station 2: Object Deploy — the Directory Is the Migration Plan

The object deploy is the centerpiece of the model. Every schema object — table, function, procedure, trigger, view — lives as its own file in the repository and describes its desired state, phrased idempotently: CREATE TABLE IF NOT EXISTS and CREATE OR REPLACE where the engine offers them; where it does not — for triggers or constraints — DROP … IF EXISTS plus re-creation does the same job, at the price that the object briefly disappears and is recreated during the deploy. The deploy runner loads these files in a fixed section order straight from the directory structure — tables before functions, functions before triggers — and within each section by file-name number. There is no separate deploy plan to maintain (and forget): the directory is the migration plan.

The essential advantage over a classic migration chain: the DDL core of every schema object sits in one place, readable as one file — not spread across forty change scripts building on each other. And every deploy is the same call, whether against an empty or a current database: deploy.sh all <env>.

Why this model works without a migration chain, what the directory convention looks like in detail, and where its limits are, is laid out in Deploying a SQL Schema Without a Migration Tool — the foundation all further stations build on.

Station 3: Apply-Smoke and Object Tests

A merged DDL change that does not run on an empty database is broken by definition — it just does not fail where it was written, but on the next environment that gets built from zero. What helps against this error class is a discipline, not a tool: before every merge, the complete deploy runs once against an empty throwaway database — the apply-smoke.

On top of that come the object tests: one SQL file per schema object with DO $$ … ASSERT blocks checking the happy path, expected error guards, and constraints. Both together run locally in a self-cleaning Docker container (db/tests/run.sh) — and identically once more in the CI gate of station 5. Whoever forgets the smoke locally is reminded in the pull request at the latest.

Station 4: Evolution with Data

Before go-live, schema evolution is trivial: teardown, bootstrap, deploy — the database is disposable. As soon as environments hold data nobody may lose, this station becomes the most demanding part of the lifecycle. The framework-free model answers it with two building blocks:

Convergent object files: an existing table is never dropped and recreated. New columns join the object file as an idempotent ALTER TABLE … ADD COLUMN IF NOT EXISTS — the file keeps describing the desired state, and every environment converges to it on the next deploy.
Run-once transitions: everything that cannot be phrased idempotently — backfills, conversions, data rescues — becomes a timestamped change script that runs exactly once per database. A tracker inside the database records what has already run, by filename and checksum; editing an applied script aborts the deploy.

The harder cases — renames, type changes, dropped columns — follow the same two-building-block pattern, but almost always need the transition part plus an intermediate step in which the old and the new form exist side by side.

The two deep dives: Adding a NOT NULL Column to a Populated Table plays through the most common single case — the expand/contract pattern that makes structural change and backfill work together on a populated table. And Tracking Schema Changes Without a Framework builds the run-once tracker from scratch — including the realization that Flyway and Liquibase make the same four design decisions in their tracking core, for all the tools bring along beyond it.

Station 5: CI as the Gate — GitHub Actions

All previous stations run on demand — station 5 makes them binding. On every pull request, GitHub Actions spins up a Postgres service container as a throwaway database and runs half the lifecycle on it automatically: bootstrap, full deploy, object tests — and then a second deploy in the same run, which checks the idempotency of all object files and the skip path of the run-once scripts. Wired up as a required status check, a red run blocks the merge.

The gate catches the error classes that can be checked mechanically — syntax and structural consistency, load order, idempotency — and needs not a single secret to do so. What the workflow looks like in detail, what exactly the double-deploy trick checks, and how it continues from the gate to deploys on real environments: GitHub Actions for Postgres Deploys.

Station 6: Teardown

The teardown is the underrated station of the lifecycle. drop.sh <env> terminates active connections, drops the database, and removes all four roles — each with IF EXISTS, so the script can safely run again after a partial failure. In the verified run, nothing was left behind: no entry for the database left in pg_database, none of the four roles left in pg_roles.

Why a station of its own for throwing things away? Because teardown is the litmus test for stations 1 and 2: an environment may only disappear without hesitation if bootstrap and object deploy can rebuild it completely. If the thought of drop.sh makes you nervous because knowledge lives in the database that exists nowhere in the repository, the lifecycle is not closed yet. Before go-live, drop-and-rebuild is the normal reset path; after that, the hard rule applies: never on an environment with data worth protecting without a verified backup.

When Does a Migration Tool Make Sense?

The framework-free lifecycle carries well as long as three things come together: one database engine, a manageable team, and a deploy that is shell-based anyway. Then Bash and psql deliver the core — desired-state files, run-once tracking, CI gate — without a new dependency in the stack. A tacit prerequisite here: the schema can be described fully declaratively in the repository. Where externally managed objects, replication setups, or extensive partitioning are involved, the claim of a "complete lifecycle" only holds with reservations.

A migration tool like Flyway or Liquibase becomes legitimate as soon as its extras are genuinely needed: support for multiple database engines, rollback or undo workflows, a lock table against concurrently running deploys, dry-run and report tooling for audit requirements, programmatic migrations. Add to that what cannot be pinned to individual stations: an established ecosystem with documentation, IDE integration, and support — and a standard several teams can agree on without having to learn a home-grown build first. From multi-team or multi-database operation onward, these are no longer comfort features but necessities — then the framework is the right choice, without anything being wrong with the model shown here. Beyond the scope of this article remain the operational topics outside the deploy model: zero-downtime strategies such as blue/green or rolling deployments, and the locking questions of very large tables — the latter is shown on a concrete case in the expand/contract article.

The honest assessment cuts the other way too, though: at its core, a migration tool replaces two of the six stations — the object deploy and the evolution mechanics. Bootstrap, object tests, the CI gate, and the teardown remain your own work, framework or not. Having built the lifecycle once yourself, you make the tooling decision from the inside: you know which stations the tool takes over, which ones it leaves open — and you read error messages like "Migration checksum mismatch" for what they are: the same design decisions, just behind a different surface.

FAQ

Do you need Flyway or Liquibase for database CI/CD with Postgres?
No, not necessarily. The core — versioned desired-state files, run-once tracking for data changes, and a CI gate against a throwaway database — fits into Bash and psql. A framework pays off when its extras are needed: multi-engine support, rollback workflows, a lock against concurrent deploys, or audit reporting. It takes two of the six lifecycle stations off your hands; four remain your own work.

How do you deploy a Postgres schema automatically?
With a script runner that applies the object files from the repository in a fixed section and number order, and a CI workflow that calls exactly this runner — on every pull request against a throwaway database, on release against the real environment. Since every file describes its desired state idempotently, the deploy is always the same call, whether the database is empty or current.

What is the difference between an object deploy and migrations?
A migration chain describes the path: numbered change scripts that build on each other and run in sequence. An object deploy describes the destination: one file per schema object with its desired state, applicable any number of times. The object approach keeps the current state readable in one place; for data-dependent steps it additionally needs run-once scripts — exactly the point where the two models meet again.

How do you test DDL changes before the merge?
With a full dress rehearsal against an empty throwaway database: complete deploy, then object tests as DO $$ … ASSERT blocks, then a second deploy as the idempotency check. Locally a Docker container takes care of it; in the pull request, the same procedure runs as a GitHub Actions gate. What this test bench does not see — effects on existing data, locks on large tables — remains the job of the evolution discipline from station 4.

What roles does a Postgres environment need at minimum?
Four have proven themselves, with a clear division of labor: a database owner, a schema owner under which all deploys run (deployed objects automatically belong to it), a read-write group role for data access, and a service account for the application that is a member of that group role. The postgres superuser stays reserved for bootstrap and teardown and appears in no application connection.

Deploying a SQL Schema Without a Migration Tool — Directory Convention Instead of Flyway or Liquibase — station 2 in depth: the apply model the whole lifecycle builds on.
Adding a NOT NULL Column to a Populated Table — the Expand/Contract Pattern — station 4 played through on its most common single case.
Tracking Schema Changes Without a Framework — Run-Once Scripts, Checksums and Immutability — station 4: the tracker built from scratch, with the Flyway comparison.
GitHub Actions for Postgres Deploys — a Throwaway Database as Quality Gate — station 5 in depth: service container, double deploy, environments.
Verifying the Migration — Data Quality and Row Reconciliation After the Move — if the reason for the rebuild was a database migration: the acceptance check afterwards.
The open DI² Starter Kit on GitHub — all six stations runnable in context.

Setting Up a Claude Code Project with a Development Workflow and Database — the Open Starter Kit Explained

Marcus — Wed, 29 Jul 2026 14:05:30 +0000

An empty repo and Claude Code, Anthropic's coding agent in the terminal — that's all it takes to get going. And that is exactly the problem: the model writes code immediately, but by default nothing ensures that a specification exists first, that a review happens afterwards, or that the database schema deploys reproducibly. A Claude Code project setup is therefore less a tooling decision than a process decision — the question is not whether the AI can code, but what sequence it works through.

This article shows a complete setup for that, in overview: the open DI² starter kit — a fixed, numbered development workflow from requirements to deploy, a deployable database part, and a template that prunes itself during setup. It was not designed on a drawing board; it is a distillate of a running project.

The essentials up front:

The scaffold: conventions live as rules, tools as skills in .claude/ — and the difference between the two decides what every session costs.
The workflow: eleven numbered phases from /init to /deploy, with the bug loop running across them; each feature spec is the contract the phases work against.
The database part: plain SQL with a directory convention, idempotently deployable, tested in CI against a throwaway database.
The design decision: start maximal and prune via the /init interview, instead of starting empty and building up.
The deliberate omissions: no hooks, no subagents — simplicity is part of the design.
Open on GitHub: the kit is forkable as a starter kit (MIT license).

Prerequisites: Claude Code and an editor; for the database part, optionally a PostgreSQL (local or in a container). The examples come from the open DI² starter kit on GitHub. Mechanics as of July 2026.

The Scaffold: What Lives in .claude/

Setting up a Claude Code project starts at the top level with four directories — .claude/, docs/, features/ and db/:

.claude/
├── rules/      loads automatically: conventions, security rules, the SQL rule tree — and stack.md
└── skills/     loads on demand: one folder per workflow phase, invoked as /requirements, /qa, …
docs/           PRD, bug tracking, knowledge base
features/       INDEX.md + one spec per feature — the project's working state
db/             the database part: schemas, deploy scripts, tests

The split under .claude/ is the scaffold's most important mechanism. Rules — the CLAUDE.md and every file under .claude/rules/ — are fully in context at every session start, in the model's working memory: the constitution of the project, applying without being asked. Skills are tools on demand: initially only their description line sits in context; the body loads on invocation. One rule holds a special role: stack.md is the authoritative source for the tech stack — runtime, UI, database, deploy, CI including the build and test commands; the CLAUDE.md carries only a short summary of it. That keeps the skills stack-neutral: they read the stack at runtime instead of hard-coding it in their own text.

How the two loading paths differ in detail — and what an overstuffed rules folder costs every single session in context — is taken apart in Skills vs. Rules in Claude Code.

The Workflow: From Requirements to Deploy

The heart of the setup is a numbered sequence: eleven phases, 0 through 10, each one its own skill.

0 /init → 1 /requirements → 2 /architecture → [3 /ux] → [4 /backend] → [5 /frontend]
        → 6 /qa → 7 /review → 8 /check-updates → 9 /security → 10 /deploy

Specs drive the sequence: /requirements writes one specification per feature, with user stories and acceptance criteria, and features/INDEX.md tracks the status of all features — the single overview every later phase orients itself by. The rest of the chain works against that spec: /architecture drafts the technical design, /qa tests against the acceptance criteria, /review checks the diff against spec and conventions. Played through on a small feature, it looks like this:

/requirements   spec feat-0001-csv-import.md — user stories + acceptance criteria
/architecture   design: staging table plus COPY import; no UI → /ux is skipped
/backend        implementation against the spec
/qa             tests against the spec's acceptance criteria
/review         diff check against spec and conventions

The square brackets in the chain mark phases not every project needs: an API service without a user interface knows no /ux and no /frontend; the pruning at setup time removes them entirely. And phase 0 falls out of line: /init runs exactly once, at setup — more on that in a moment.

Two properties keep the sequence in check. First, no phase jumps to the next on its own: every transition is triggered by a human; the kit calls this human-in-the-loop. Second, /security is set as a gate: the project-wide security audit comes before a production deploy, not after it. Running across all phases are /bug (records and closes bugs under docs/bugs/, at any time), /auth (for auth projects) and /help — which answers the question "where am I right now, and what comes next".

The Database Part: Plain SQL, Deployable and Tested

What sets this setup apart from typical Claude Code templates: it has a real database part. Under db/ sits a complete, runnable PostgreSQL example — tables with audit columns, procedures, triggers, a view, seed data, plus deploy scripts and tests for the database objects. None of this is carried by a migration tool but by a directory convention: object files in fixed numbering, written idempotently, an apply runner plays them in in a defined order; one-off, data-dependent steps run alongside as run-once transitions. In CI, every schema state is brought up against a throwaway database and tested before it reaches a real environment. Doing without a migration tool is a deliberate decision of this setup, not a universal recommendation: where a team already has an established migration tool in place, there is no reason to switch.

The conventions these SQL objects are written to — naming, alignment, object skeletons — live as a rule tree under .claude/rules/sql/ and are the subject of the sister overview AI-Assisted SQL Development with Claude Code, with its deep dives on tables, procedures and functions.

How the deploy works without Flyway or Liquibase in detail — idempotency, run-once transitions, the schema_apply_log table, the runner and the CI safety net — is described in Deploying a SQL Schema Without a Migration Tool.

The Design Decision: Start Maximal, Then Prune

The template ships all fourteen skills and every rule tree — including the parts a given project will never need. Pruning happens at setup: /init is a one-time bootstrap interview. It first captures the product vision and the stack's parameters — runtime, UI, backend, database, auth, deploy, CI — and writes the vision into the CLAUDE.md, the stack profile into stack.md. It then removes what the answers rule out: no UI means /ux and /frontend go, UI rule tree included; no database means the entire db/ tree disappears. At the end, /init deletes itself — a bootstrap runs only once.

Why delete instead of build up? Because deleting is the easier operation: whoever deletes decides against the complete picture: every file is a yes/no question. Whoever builds up has to remember what is missing, and forgotten things don't speak up. On top of that comes the context math: rules that don't apply would otherwise load into every single session.

The interview, the role of stack.md as single source of truth, and the full pruning matrix — which answer removes which files — are described in Maximal Template Over Empty Repo.

What the Kit Deliberately Leaves Out

An overview is only complete with what's missing — and the starter kit leaves out a few things on purpose.

No hooks. Claude Code can bind shell commands to events, to be executed automatically — before a tool call, after a change, at session start. The kit passes on that: what happens, happens visibly in the dialog and is written in the rules, where the human reads it and the model follows it. That costs a degree of automation but saves the hard-to-trace layer between intent and execution.

No subagents. Claude Code can hand subtasks to separate agents with their own context. Here too the kit keeps the work in the main context, where the human sees and can steer it. It is the same decision that keeps the phase transitions user-initiated.

No app skeleton. There is no src/: the kit is not a Next.js, Python or whatever template. The application scaffold emerges through the workflow, against the stack the project chose in the interview.

Exactly one option per matter of taste. One UI ruleset (React/Tailwind/shadcn), two SQL dialects (PostgreSQL and SQL Server) — no further variants sit side by side. If a project needs a different one, the rule is: port, don't stack — copy the shipped rule tree, adapt it, remove the original.

These omissions are part of the picture: the kit is not a framework claiming completeness but an opinionated setup from a real project. Where an opinion doesn't fit, it is replaceable in Markdown files — that is exactly why everything lies open.

Honest framing also includes the counter-calculation: the workflow costs real extra effort per feature: spec, QA and review are steps of their own, not side effects. For a throwaway script or a quick prototype the setup is oversized; there, the empty repo remains the better choice. And the structure only carries with discipline: skip phases regularly and you end up with the folders, but not the process. What that effort buys, the comparison shows:

	Empty repo	Starter kit
Requirements	in the chat history	one spec per feature, versioned
Order	ad hoc	eleven numbered phases
Conventions	re-explained every session	rules, loaded automatically
Review and QA	on request	phases of their own, security as a gate
Database	manual work	`db/` tree, idempotent and CI-tested

Setting Up a New Claude Code Project with the Starter Kit

The kit is public on GitHub, MIT-licensed. For a new project there are two equivalent paths: use the repository as a GitHub template ("Use this template") or run the bundled copy script (new-project.ps1 or new-project.sh). Both paths produce a repository with a fresh Git history. A direct git clone of the template is off-limits as a project start: it drags along the template's history and upstream linkage, neither of which belongs in the new project.

gh repo create my-project --template marcusbelz/di2-starter-kit --private --clone

Then open the new project in Claude Code, invoke /init, answer the interview — and specify the first feature with /requirements. From there, the numbered workflow carries.

Two transparency notes belong to the provenance. First, the lineage: the kit is the generalized, product-free .claude/ toolbox of the DI² project. That project in turn descends from the AI Coding Starter Kit by AlexPEClub (MIT); the attribution is documented in the repository. Second, the making: the texts in the kit are written by Claude, under human direction — every file commissioned, checked and signed off; the README states this working mode openly. And an honest maturity note: the conventions grew out of daily use in the DI² project and are proven there; the packaging as a self-pruning template is young. Whoever adopts the kit takes on a well-thought-out setup, not a framework matured over many years.

FAQ

How do I set up a Claude Code project in a structured way?
With a template that brings process and conventions along: create a new repository from the template (GitHub "Use this template" or the copy script — no direct git clone), invoke /init in Claude Code and answer the interview about vision and tech stack. After that the project prunes itself, and /requirements specifies the first feature — from there the numbered workflow leads all the way to deploy.

Does a Claude Code project need a fixed folder structure?
Only .claude/ with rules and skills is a convention of Claude Code itself. Everything else — docs/, features/, db/ — is a decision of the setup. But that is exactly where the difference between "the model writes code" and "the project has a process" lives: specs, bugs and schema states need a fixed place, or they exist only in the chat history.

What is the difference between rules and skills?
Rules load fully into context at every session start and apply without being asked; skills load on demand and run on command. The decision rule: what must always apply becomes a rule — what executes as a procedure becomes a skill. The loading mechanics in detail are in Skills vs. Rules in Claude Code.

How do I integrate a database into the Claude Code workflow?
As a dedicated db/ tree in the repository: object files in a directory convention, written idempotently, a runner plays them in, and CI tests every state against a throwaway database. No migration tool is needed for that — how it works is shown in Deploying a SQL Schema Without a Migration Tool.

Is the starter kit tied to a tech stack?
No. The workflow skills name no language and no framework — they read the stack at runtime from stack.md, which /init fills at setup. Stack-specific are only the rule trees for UI and SQL dialect; the pruning keeps exactly the variant the project chose.

Does the setup work with Cursor or GitHub Copilot — why Claude Code?
The building blocks are tied to Claude Code mechanics: rules and skills under .claude/ with their distinct loading paths exist natively there, as do the slash invocations of the phases. The ideas behind them — specs as a contract, numbered phases, security before deploy — are tool-neutral: anyone working with Cursor or Copilot can adopt the process but would have to port the template themselves.

Maximal Template Over Empty Repo — a Claude Code Setup That Prunes Itself via /init — the design decision "start maximal, then prune" in detail.
Skills vs. Rules in Claude Code — the loading mechanics behind the scaffold.
Deploying a SQL Schema Without a Migration Tool — the database part in depth.
AI-Assisted SQL Development with Claude Code — the sister overview: the SQL conventions that live in the kit as a rule tree.
SQL depth behind the rule tree: Postgres Table Conventions, PL/pgSQL Procedure Conventions, PL/pgSQL Function Conventions.
I Gave Claude Code 27 Rule Files Instead of One CLAUDE.md — the experience report on this setup's rule structure: anatomy, don'ts, inventories.
The open DI² starter kit on GitHub — template, workflow skills and database part, ready to fork.

I Gave Claude Code 27 Rule Files Instead of One CLAUDE.md

Marcus — Sat, 25 Jul 2026 16:49:51 +0000

There is a moment when a CLAUDE.md tips over. That moment does not sit on any calendar, it shows in behavior: the file keeps getting longer, and the rules inside it keep getting followed less. Every new convention you write in dilutes the ones already there. That is exactly where the project behind this article stood — and the answer was not a better CLAUDE.md, but its dissolution into individual rule files. Today there are 27 of them.

This article is the experience report: how Claude Code rules need to be structured so they hold up over months, what a single rule file must carry, and why the most important parts are not the rules themselves. They are the don'ts and the inventories.

The key points up front:

One file per convention — 27 rule files instead of one CLAUDE.md, each with a single topic.
Anatomy of a rule that holds: rule, reasoning, don'ts, inventory, cross-references.
Don'ts beat prescriptions — at least in the DI² project: a negative example is concrete and recognizable, a prescription competes against the training prior.
Inventories are the drift radar: the part of a rule that claims an actual state of the code — and therefore the first to stand out when code and documentation diverge.
paths: scoping lets large rules load only where they apply — 3 of the 27 files use it.
The honest downside: maintenance effort, rule conflicts, and a state in which the documentation deliberately runs ahead of the code.

Prerequisites. A project with Claude Code and a .claude/rules/ directory. The pattern transfers to any coding agent that loads convention files into its context — Cursor rules or comparable mechanisms in other tools work on the same principle. What loads into context when, and what that costs, is covered by the sibling article Skills vs. Rules in Claude Code — this article starts one level earlier: at the question of what the rule files themselves have to look like.

The Starting Point: One File That Kept Growing

The project behind the numbers is DI², an ETL generator built on Next.js and PostgreSQL, its code written almost entirely AI-assisted with Claude Code. In the beginning, all conventions lived where every Claude Code project collects them first: in the CLAUDE.md. That works as long as the file is short. It stops working once the file becomes a container in which database conventions, color tokens and commit rules all sit side by side.

This effect is hard to measure, but it is easy to feel. It also matches what research shows about long contexts: language models make measurably worse use of information sitting in the middle of long inputs ("Lost in the Middle", Liu et al. 2023). In my experience, a rule sitting in line 40 of a long file gets followed less than the same rule in its own, topically named file. A second effect has less to do with the model than with the humans: in a 400-line file, even the author cannot find a rule again when they want to check whether it still holds. Why a single file does not scale structurally, and which loading mechanics sit behind that, belongs to the skills-vs-rules article — this one is about what comes after.

The consequence in the project: a .claude/rules/ directory holding 27 rule files, one per convention (as of July 22, 2026). The same count holds for the public edition of this rule structure, the di2-starter-kit on GitHub — you can verify it there, not counting the subfolder READMEs. There is a file for the table conventions, one for dialogs, one for loading states, one for the security model. The directory keeps growing with the project. In the very week this article was written, three new files arrived, for views, triggers and database policies. A rule system is finished when the project is finished, which is to say never.

Anatomy of a Rule That Holds

After several months with this system, a fixed structure has emerged. A rule file that holds consists of five parts:

# <Convention> (<project>)

> One-sentence summary: What does this rule enforce, and where does it apply?

## The Rule

Every <structure/component/procedure> <does exactly one thing, stated imperatively>.
One convention per file — do not mix topics.

## Reasoning

Why this rule exists: the concrete incident, bug or review finding
that triggered it. A rule without a reason gets weighed away in
trade-offs — the reason is part of the rule, not decoration.

## Don'ts

- ❌ `<concrete negative example from your own code>` — why it drifts.
- ❌ `<second negative example>` — what applies instead (with the target spelling).

## Inventory

| Usage site | File | Status |
|---|---|---|
| <Site A> | `components/site-a.tsx` | ✅ compliant |
| <Site B> | `components/site-b.tsx` | ⏳ retrofit open |

## Cross-References

- [neighbor-rule.md](neighbor-rule.md) — boundary: what is governed there, not here.

The rule itself (the first two or three sentences under The Rule) is the smallest part, and that is no accident. It states imperatively what applies. Everything beyond that belongs to the other four parts.

The reasoning is not a courtesy to the reader. An agent weighs trade-offs, and as observed in the DI² project, a rule without a reason loses that weighing more easily against a plausible counterargument from the concrete case. A rule with a reason anchors what the concrete case has to argue against. The difference shows in exactly the moments that matter: namely, when the model considers an exception justified. How a convention and its reasoning come into being in the first place is described in the methodology article Deriving SQL Conventions with Claude Code.

The remaining three parts are the actual substance of this article. The don'ts and the inventory each get their own section below, and the cross-references almost explain themselves: they draw the boundary to the neighboring rule so that two files do not creep into governing the same topic. Every boundary violation that surfaces later gets recorded there as an explicit reference.

Why Don'ts Beat Prescriptions

This is the central observation from several months of rule maintenance in the DI² project. Whether it transfers to other projects has not been examined — it is project experience, not a study. A prescription says what should be. A negative example shows what must not be — with a concrete, recognizable spelling. The difference looks small at first glance. In practice it is not:

<!-- Before: a prescription without an anchor -->

Use only the project tokens for font sizes.

<!-- After: negative examples with recognition value -->

**Don'ts:**

- ❌ `text-[12px]` — raw pixel value, drifts; snap target is `text-token-meta`.
- ❌ `text-sm` — framework default instead of the project scale; in the app
  scope the linter guard flags it as an error.
- ❌ Inline `line-height` override via `[line-height:Xpx]` — the scale ships
  its own line height; a deliberate override needs a code comment with
  a justification.

The prescription "use only the project tokens" is factually correct, and yet it accomplishes little. At every generation it competes against the model's training prior, in which text-sm is the statistically most common way to write small text. "Only project tokens" first has to be translated onto the concrete case, and the rule gets lost in that translation.

The negative example skips the translation. text-[12px] is precisely the string the model is about to write — it sits verbatim in the rule, marked with a ❌ and the reason. A don't leaves no room for interpretation. It additionally names the snap target, the spelling that applies instead. Whoever reads the don't, whether human or model, afterwards knows both things: what is wrong and what belongs in its place. There is no don't-specific magic behind this, but a familiar effect from prompt research: concrete examples are easier for language models to act on than abstract prescriptions. Negative examples are simply the form in which a rule file can harness that effect.

The best don'ts do not come from the rule author's imagination but from real finds. Every time a review or a bug surfaces a new bypass variant, exactly that variant goes into the rule file as a don't. The rule system thereby learns the same mistakes the code has already made once. There was no shortage of material: the sibling article AI-Assisted Coding Gave Me 799 Hardcoded Font Sizes documents the drift finding from which the font-size don'ts emerged.

Inventories as a Drift Radar

A rule states a target state, and target states have an inconvenient property: they cannot become wrong. "Every dialog carries a leading icon" stays correct as a sentence even when six dialogs without icons have long been sitting in the code. The rule notices none of it.

An inventory changes that. It lists the concrete usage sites of the convention together with their actual state:

## Inventory + Retrofit Backlog

As of now, this rule is the documented truth. Existing sites without
<convention> are brought in line as a tracked follow-up step (retrofit) —
until then the code deliberately lags behind the documentation.

| Dialog | File | Status |
|---|---|---|
| Editor (domain object A) | `components/object-a-editor-dialog.tsx` | ✅ icon leads left |
| Inspector (domain object B) | `components/object-b-inspector.tsx` | ✅ icon leads left |
| Invite user | `components/user-invite-dialog.tsx` | ⏳ retrofit open |
| Bulk delete | `components/bulk-delete-dialogs.tsx` | ⏳ retrofit open (destructive tone) |

The table is the part of a rule file that can fail against the actual state of the code. A reasoning section or a cross-reference can go stale too, but only the inventory makes a checkable claim about what the code looks like right now. If a new dialog arrives and is missing from the table, the inventory is incomplete. If a listed dialog gets rebuilt and its status is not updated, the inventory is stale. Exactly this vulnerability is what makes it valuable: a rule without an inventory can exist past the code unnoticed for years, while the lag of a rule with an inventory becomes visible at the next reconciliation, at the latest. The inventory is the rule's drift radar.

In the project, 9 of the 27 rule files carry such sections. They come in two flavors. The caller inventory lists who uses a component or convention — it answers the question "if I change this, what is affected?" before anyone has to search. The retrofit backlog lists which existing sites do not yet satisfy the convention. Both forms share the mechanics but differ in the direction of view: one looks at the rule's users, the other at its open debts.

The inventory is not maintained in a separate documentation session but in the same commit as the code change. Whoever adds a dialog adds it to the table. Whoever completes a retrofit sets the status to ✅. That sounds like a high demand on discipline. In an agent workflow, though, it is the cheapest possible moment, because the agent usually has the rule file in context anyway when it works inside the rule's scope.

Rules That Load Only Where They Apply

As the file count grows, the context-cost question returns. 27 files that all load all the time would just be a partitioned CLAUDE.md at the same cost. The lever against that is a paths: front matter that binds a rule to its path scope:

---
paths:
  - "src/app/api/**"
  - "src/lib/db*"
---

# Backend conventions

This rule loads only when the task touches files in its path scope —
API routes and the database access layer. A frontend task does not
pay its context costs.

In the project, 3 of the 27 files carry this front matter: the backend rules, the frontend rules and the security model. The selection follows a simple criterion. These three files are large, and their scope is a clearly bounded subtree of the project. A backend rule inside a pure frontend task is dead context. The remaining 24 files load unscoped because they are either small or apply across the whole project, like the commit conventions.

This article deliberately goes no deeper here. The mechanics behind it — what Claude Code loads into context when, what separates rules from skills, and how the costs add up — are the subject of Skills vs. Rules in Claude Code. For the structural question of this article, the finding suffices: paths: scoping is the main reason 27 files do not mean 27-fold costs.

The Honest Downside

A rule system of this size is not free, and an experience report that hides that would be advertising.

Maintenance is real work. Every rule file wants attention at every convention change, inventories want updating in the same commit, and the cross-references between files go stale when a rule moves. The effort is not a one-time investment but a running cost. In the project it is the kind of work that pays off, but it does not disappear just because you approve of it.

Rules end up in conflict. With 27 files it happens that two rules govern the same case from different angles — the dialog rule wants an icon, the confirmation-dialog rule forbids one for its special case. The resolution is the same every time: the conflict gets written into both files as an explicit carve-out, with references to each other. Undecided conflicts are the worst thing that can happen to a rule system, because then the prioritization stays ambiguous — which rule prevails depends on the particular context and the task at hand.

The documentation runs ahead of the code — deliberately. When a new convention is decided, it applies to new code immediately. The existing sites are not rebuilt within the hour but tracked as a retrofit backlog in the inventory and brought in line step by step. That state is not a failure as long as it is documented. The rule file says honestly: this is the truth, and the code deliberately lags behind at these listed sites. Untracked, the same state would be a lie, because then the documentation claims an actual state that does not exist.

When a Rule Needs a Linter

Prose has a limit, and the sibling article AI-Assisted Coding Gave Me 799 Hardcoded Font Sizes has measured it out in detail: a documented rule raises the generation hit rate but does not guarantee it. At high volume, any residual rate turns into visible drift.

From that follows a division of labor that has proven itself in the project. Whatever is machine-checkable gets a guard in addition to the rule — in the font-size case a custom ESLint rule at error level that flags exactly the spellings from the don'ts. The rule file remains the source all the same: it explains the why, defines the mapping and lists the deliberate exceptions, while the linter enforces only the checkable subset.

Whatever is not machine-checkable stays a pure prose rule and needs the inventory as its substitute radar. No linter can decide whether a dialog icon is the right one for the domain. Whether all dialogs have one is written in the inventory table. The rule of thumb from the project: a rule whose violation can be expressed as a search pattern is a linter candidate. A rule whose violation a human has to recognize needs an inventory.

What I Would Do Differently Today

Looking back, I would approach three things differently, and all three can be named concretely.

Too many files too early. The first weeks produced rule files for topics that did not even have a second usage site yet. A convention for a single case is not a convention but a note. Today a new rule file comes into being only when the same decision comes up for the second time. And cleanup is part of the deal: a rule whose scope has disappeared, or whose content a neighboring rule meanwhile carries, gets deleted or merged into that neighbor. A rule set that only ever grows becomes the very unwieldy file it was meant to replace.

Rules without inventories that went stale silently. The early files consisted of rule and reasoning, without a state-of-the-code part. After two months, some of them described a state the code had long left behind, and nobody had noticed. Only the inventory sections made the aging visible. In hindsight, every rule with usage sites should have carried one from day one.

Discovered paths: too late. The scoping arrived only when the context costs were already noticeable, and converting existing files to clean path scopes was more tedious than an early cut would have been. Anyone starting today should ask one short question at every new rule file: does this apply everywhere, or in one subtree? Answering that question costs ten seconds at creation time. Not asking it costs a refactor later.

FAQ

When should I split my CLAUDE.md?
At the latest when, while writing a new convention into it, the question comes up where it actually belongs. That is the signal that the file carries multiple topics. A second signal is the repeated ignoring of a rule that verifiably sits in the file — the rule is drowning in its surroundings.

How many rule files are too many?
The number itself is not the limit, the context costs are. 27 files work because the large ones load path-scoped and the unscoped ones are small. How those costs add up and where the line runs is covered by Skills vs. Rules in Claude Code.

Why do negative examples work better than prescriptions?
Because they close the translation gap. A prescription first has to be applied by the model onto the concrete case, while a don't already contains the wrong spelling verbatim — precisely the string the training prior would suggest, marked with the reason and the target spelling. Recognizing is more reliable than deriving.

What do I do when two rules contradict each other?
Write the conflict into both files, as an explicit carve-out with references to each other. Which rule wins in the overlap case must be stated in the files, not in the author's head. An undecided conflict otherwise gets decided by chance, depending on which rule happens to sit more prominently in context.

Does the code always have to match the documentation?
No — and that may be the most counterintuitive lesson. A new convention applies to new code immediately, while the existing code is brought in line step by step as a tracked retrofit backlog. The tracking is what matters: a documented lag is a deliberate decision, an undocumented one is documentation that lies.

Going deeper:

Skills vs. Rules in Claude Code — What Auto-Loads, What Loads on Demand — the loading mechanics and context costs behind this article.
Maximal Template Over Empty Repo — a Claude Code Setup That Prunes Itself via /init — how a rule inventory gets tailored at project start.
Setting Up a Claude Code Project with a Development Workflow and Database — the sub-pillar: the setup this rule system lives in.

Upstream:

AI-Assisted Coding Gave Me 799 Hardcoded Font Sizes — the drift finding that makes rules and guards necessary in the first place.
Deriving SQL Conventions with Claude Code — the Generate-Refine-Derive Loop — how a convention comes into being before you pour it into a rule file.
AI-Assisted SQL Development with Claude Code — Rules, Skills and Agents — the pillar: the enforcement system as a whole.

Starter kit:

The open DI² starter kit on GitHub — a project template built on this article's one-file-per-convention principle, ready to tailor.

AI-Assisted Coding Gave Me 799 Hardcoded Font Sizes

Marcus — Tue, 21 Jul 2026 23:42:38 +0000

It didn't start with an audit. It started with a nagging feeling: the interface looked restless. You don't notice it at first glance, but on the second and third look it is there — a timestamp slightly larger here than there, a dialog title a touch smaller than in the neighboring dialog. Everyone knows the discipline from letters and résumés: same typeface, same font size, same alignment. The same holds for an application interface, except the violation doesn't show up in any one spot. It shows up as a diffuse restlessness across many screens.

Only that feeling led to a counting command across the frontend, and the count delivered the explanation: 799 hits for raw pixel font sizes like text-[13px], spread across 25 distinct pixel values in 74 files. And that in a project which had a documented font-size scale with six tokens all along. The scale was used 263 times and bypassed roughly 1,180 times. This is not a sloppiness finding from some legacy codebase grown over a decade — it is the state of a codebase built AI-assisted with Claude Code from day one. This is what AI code drift looks like: every single suggestion is locally plausible, and what adds up is the restlessness you can see before you can measure it.

This article is the experience report — with the real numbers, the rule that ended the problem, and the honest admission that a convention living only in prose loses against a language model.

The key points up front:

The finding: 799 raw pixel font sizes against 263 token usages, even though the scale was documented. The ratio is the message, not the single number.
The biggest source of drift lies between the tokens: values like 11px and 12px, for which no token existed at all, form the majority at roughly 400 occurrences.
No code review catches this, because every diff is harmless on its own.
Better prompts raise the hit rate but don't eliminate the drift — at high generation volume, any residual rate becomes visible.
What holds: a 6-step scale, a fixed element-to-token mapping, an unambiguous snap rule for all in-between values, and a linter at error level.

Prerequisites: The example uses Tailwind CSS and ESLint in a Next.js project. The pattern applies to any codebase with design tokens, regardless of framework — and, as the end of the article shows, just as much to SQL conventions.

The Finding: 799 Against 263

The project behind the numbers is DI², an ETL generator built on Next.js and PostgreSQL whose code was written almost entirely AI-assisted. For font sizes, a clear convention existed: six named tokens from text-di-h1 (18px) down to text-di-label (10px), defined in the Tailwind configuration and described in a brand rule file that the agent loads for every frontend task. Everything that follows is therefore the measurement of a single project — one data point, not proof. What makes the case interesting beyond the project is the mechanism behind it, and that mechanism, as we will see, is not project-specific.

The inventory on June 25, 2026 across all src/**/*.tsx files produced three categories:

Declaration style	Occurrences	Files	Assessment
Raw pixels `text-[Xpx]`	799	74	drift, 25 distinct pixel values
Tailwind defaults `text-xs`/`text-sm`/`text-base` …	383	85	drift within the app scope
Canonical tokens `text-di-*`	263	43	the target pattern

The three rows distinguish three ways of declaring the same font size. text-[13px] is Tailwind's arbitrary-value syntax: the pixel value sits literally inside square brackets and acts like an inline font-size — any value is possible, and that is how 25 different ones come into being. text-xs, text-sm and text-base, on the other hand, are named size steps, but they belong to the framework's bundled default scale (12, 14 and 16 pixels). That looks disciplined, because it follows a scale. It is just the wrong one: Tailwind's generic scale instead of the project's own, whose six tokens don't even contain those three values. The third row, finally, is the project's own scale — the target pattern. Read top to bottom, the table is a ladder: freehand value, foreign scale, own scale.

That two scales coexist is not an accident, by the way — it is the framework's default. Tailwind ships its complete size ladder with every project, and the project's own tokens were added via theme.extend — and extend means exactly that: extend, not replace. From that point on, every text-sm compiles just as happily as every text-di-body. There is no moment at which the project consents to the foreign scale, and none at which it announces itself. It is simply there, from day one, as the statistically closest choice for anyone who needs small text — human or model.

A second term needs an explanation, because it recurs throughout the article. The app scope is the application behind the login: the dashboard and administration pages plus the components they render. The token scale applies only there. Outside it lie two zones with rights of their own — the public pages (landing, legal, login), which deliberately carry larger formats, and the bundled UI base components, which internally work with Tailwind defaults. A text-sm is therefore not wrong per se; on a marketing page it is legitimate. That is why the second row says drift within the app scope: of the 383 occurrences, only the share inside the application counts as drift.

The convention, then, was ignored at a ratio of roughly 1:4.5. To rerun the inventory in your own project, all you need is a search tool like ripgrep (the examples are written in PowerShell):

# Raw pixel font sizes: total number of occurrences
rg --no-filename -o 'text-\[[0-9.]+px\]' src | Measure-Object -Line

# Distribution: which pixel value occurs how often?
rg --no-filename -o 'text-\[[0-9.]+px\]' src | Group-Object | Sort-Object Count -Descending

# For comparison: the canonical tokens
rg --no-filename -o 'text-di-(h1|h2|body|meta|micro|label)' src | Measure-Object -Line

The timeline delivers a punchline of its own. Between the first count and the recount three weeks later, shortly before the cleanup migration started, the total grew from 799 to 813 occurrences and from 25 to 26 distinct values. The drift kept growing while its removal was already being planned. A convention that is not enforced doesn't lose once — it loses a little more every day.

Why Nobody Caught It

The first reflex at a number like this: how did that slip through? The answer is uncomfortable because it describes no negligence, but a structural gap.

A text-[12px] is not wrong in any single diff. It renders correctly, it looks fine in the preview, it breaks no test. A reviewer reading the diff of a new dialog checks the logic, the states, the accessibility. They do not compare whether the font size in this file is consistent with the one in 73 other files. The drift doesn't live in any one file. It lives between the files.

It was visible all the same — just not as a defect, but as the nagging feeling from the beginning. A restless interface shows the symptom, not the location: which of the 74 files do you point at when no single line is wrong on its own? So the impression stayed without consequence for a long time. It couldn't be pinned to any diff, and what can't be pinned to a diff ends up in no review comment and no ticket. Visual regression tests usually don't catch this form of inconsistency either, because they compare each view against its own baseline. Two views that are both brand new share no baseline against which the difference could stand out. Only the inventory turned the feeling into a finding with numbers — and thereby into something fixable.

Then there is the speed. A person who builds one dialog a day makes a handful of font-size decisions per week, and muscle memory keeps them reasonably stable. An agent that creates twenty components in the same week makes the same decision a hundred times over — and each one is optimized locally, not for consistency with all the previous ones. The inconsistency doesn't come from carelessness; it comes from the sheer volume of independent single decisions. The densest single file in the inventory accounted for 70 raw pixel sizes on its own.

Humans produce the same drift — honesty demands saying so. The difference is not the kind of mistake but the pace and the volume. What a team accumulates in two years of wild growth, AI-assisted development manages in a quarter.

The Drift Lives Between the Tokens

The most revealing part of the inventory is the distribution of the 25 pixel values. It splits into three classes, and the middle one is the interesting one:

Exact token matches: values that correspond to a token, just written raw — 13px instead of text-di-body (166 occurrences), 11.5px instead of text-di-meta (46), plus the remaining token values. This class is mechanically repairable and visually a no-op.
Off-token values: roughly 400 occurrences on values for which no token exists at all — led by 11px with 196 and 12px with 143 occurrences, plus 12.5px (42) and 14px (19).
Deliberate exceptions: marketing and legal pages with large formats of their own, such as 22px or 44px, which intentionally sit outside the app scale.

The second class deserves the second look. 11px and 12px both sit next to the same token, text-di-meta (11.5px), and both were later migrated to it. Together that is 339 places where two different pixel values played the same semantic role — meta text, timestamps, helper lines. Nobody ever decided that both values should exist. There is no commit with the message "we are introducing 11px as an alternative to 12px." Both values simply came into being, suggestion by suggestion, because each looked reasonable on its own.

That is what separates this drift from a copying error. Someone who mistypes a documented value produces a findable defect. Someone who puts plausible values into a gap of the scale a hundred times over produces a creeping second scale that was never decided anywhere and therefore never stands out anywhere. That is AI code drift in its purest form.

The "Better Prompts" Fallacy

The obvious reaction to the finding would have been to state the convention more forcefully. Add a chapter to the brand rule, repeat the tokens in the prompt, instruct the agent more insistently.

That doesn't carry far, and the reason lies in how the model works. A language model is trained on vast amounts of public code, among it countless Tailwind projects, and there text-sm or text-[12px] is the overwhelmingly most common way to write small text. A project-specific convention like text-di-meta, by contrast, is exactly one file in a rules directory. At every single generation the rule competes against that weight, and it wins often — but not always. Across a thousand decisions, a hit rate of 90 percent still leaves a hundred drift spots, and even a considerably higher rate ends up in the dozens at sufficient volume.

Rules in prose improve the rate, and rules with reasons improve it further. How a ruleset comes into being that an agent actually follows is described in the methodology article Deriving SQL Conventions with Claude Code. But any rate below 100 percent means drift at high volume. For a convention that is meant to hold without exception, the prompt is the wrong tool. It needs a check that doesn't get tired.

What Works: Scale, Category Mapping, Snap Rule

The cleanup consisted of three building blocks that only work in combination.

First, the scale itself. Six steps, defined in exactly one place in the Tailwind configuration, each with its line height built in:

// tailwind.config.ts — the scale as the single source of truth
fontSize: {
  'di-h1':    ['18px',   { lineHeight: '23.4px' }],
  'di-h2':    ['15px',   { lineHeight: '21px' }],
  'di-body':  ['13px',   { lineHeight: '18.85px' }],
  'di-meta':  ['11.5px', { lineHeight: '15px' }],
  'di-micro': ['10.5px', { lineHeight: '13.65px' }],
  'di-label': ['10px',   { lineHeight: '12px', letterSpacing: '0.05em' }],
},

An important decision was to keep the scale at six steps. The tempting alternative would have been to create new tokens for 11px and 12px and thereby legalize the status quo. That would have turned the drift into an official eight-step scale, and the next in-between value would have found a gap again.

Second, the element-category mapping. A table in the rule file defines which kind of element carries which token. Section headings get di-h1, dialog titles di-h2, table cells and buttons di-body, timestamps and helper texts di-meta, counter pills di-micro, uppercase labels di-label. With that, "what size does this element need?" is no longer a matter of taste but a lookup.

Third, the snap rule. The name says it: roughly 400 occurrences sat on in-between values like 11px or 12px, and during the migration each of them had to snap onto one of the six tokens — like an object being pulled onto the grid in a graphics editor. Which token it becomes is decided in two stages. First comes the element's role from the category mapping: a timestamp gets di-meta because it is meta text, no matter which pixel value used to be there. Only when an occurrence cannot be assigned to any category does numeric proximity to the nearest token decide. Even the single tie is settled explicitly: 14px sits exactly between di-body (13px) and di-h2 (15px), and di-body wins as the default. Role before number is not a formality, either. A 12px in a sticky table header belongs to di-micro (10.5px) by category, although di-meta (11.5px) would be numerically closer. And because the rule leaves no room for judgment, two people — or two agent runs — resolve the same raw value identically, guaranteed.

With those three building blocks the migration itself was unspectacular: 80 files in the app scope, converted category by category, with shifts of less than a pixel. Exactly one deliberate exception remained — a large page title outside the app scale, marked with an inline comment and a justification.

One side finding from the same cleanup deserves a mention for completeness: the tokens had to be explicitly registered as font-size classes in the tailwind-merge configuration, because the library otherwise classifies unknown text-* classes as text color and silently discards one of them on conflict. That, however, is an ordinary trap when introducing custom tokens and has nothing to do with AI. Know it, pin it with a small test, done.

Even the Fresh Rule Drifted

The most instructive finding of the whole story comes from the migration's quality assurance, and it cuts against the author.

The category rule — "the element category wins over pixel proximity" — was written into the rule file in the same commit that carried the migration. And in that very commit it was undercut: the data cells of the densest table registers, previously 12px, belonged to di-body (13px) according to the category table. They were migrated numerically to di-meta (11.5px) instead, because that matched the density intent of those views. The freshly written rule and its first application contradicted each other, and it surfaced only in the downstream QA pass, which filed it as a documentation inconsistency.

You can consider the finding small — half a pixel in dense tables. Its value lies elsewhere: it shows that even a carefully worded, freshly printed rule drifts at the moment of its application when only humans and prose carry it. Not out of ignorance, but because a second legitimate consideration intervened in the concrete case and nobody checked against the wording. This rule-based check is one of the tasks a machine performs more reliably than any participant.

Enforcement: A Linter at error

The fourth building block therefore makes the convention machine-checkable. The tool class is secondary: a compiler check, a Tailwind plugin or a CI script can play the same role. For this project, a custom ESLint rule was the most practical form — it reports every raw pixel font size and every Tailwind size default in the app scope as an error, not a warning. The core of the rule fits on a page:

// eslint-rules/no-raw-font-size.mjs — the core of the rule
const PX_RE      = /text-\[(\d+(?:\.\d+)?)px\]/g
const DEFAULT_RE = /(?<![\w-])text-(xs|sm|base)(?![\w-])/g

// Snap table: raw value -> canonical token (from the rule file)
const PX_SNAP = {
  '11':   'text-di-meta',
  '12':   'text-di-meta',
  '12.5': 'text-di-body',
  '13':   'text-di-body',
  '14':   'text-di-body',
  '15':   'text-di-h2',
}

export default {
  meta: { type: 'problem', messages: {
    rawPx: 'Raw pixel font size `{{match}}`. Use {{target}}.',
  } },
  create(context) {
    const check = (node, raw) => {
      for (const m of raw.matchAll(PX_RE)) {
        context.report({ node, messageId: 'rawPx', data: {
          match: m[0], target: PX_SNAP[m[1]] ?? 'a token from the scale',
        } })
      }
      // DEFAULT_RE analogous
    }
    return {
      Literal(node)         { if (typeof node.value === 'string') check(node, node.value) },
      TemplateElement(node) { check(node, node.value?.cooked ?? '') },
    }
  },
}

Three decisions have proven themselves:

The rule inspects all string literals and template parts, not just JSX attributes (the Literal and TemplateElement visitors at the end of the rule). In practice, class strings also arise in cn() arguments, in .join(" ") helpers and in exported constants — a rule that only sees className="…" would have blind spots there.
The error message names the snap target (the target field in the report data). Whoever sees the error sees the fix and doesn't have to go find the rule file first. That holds for human readers just as much as for the agent reacting to the linter error.
error, not warn. A warning is a number in a summary; an error breaks the build. Only the second one is enforcement. The halfway path — warnings plus occasional cleanup — ends up reproducing the very state that led to the drift.

One obvious alternative deserves a mention because it looks simpler than it is: remove Tailwind's default scale from the configuration altogether by defining fontSize without extend. Then text-sm simply would not exist anymore. That fails on two counts. An unknown text-sm produces no error in Tailwind — it produces no CSS at all, so the text would silently fall back to the browser's default size, and a silent failure like that is harder to find than the drift it is meant to prevent. And the bundled UI base components, like the marketing pages, build on exactly those default classes internally. The radical fix would break the very zones that legitimately live off the standard scale. A linter with a scope can express that; a global configuration cannot.

The deliberate exceptions therefore live not in the rule but in the ESLint configuration: a path list exempts marketing, legal and login pages, whose large formats intentionally sit outside the app scale. That records the convention's scope machine-readably in one place, congruent with the scope in the rule file. The direction of the definition is worth noting: the app scope itself is never listed anywhere — it is simply everything that was not exempted. A new public page that nobody adds to the exemption list is treated as app scope, and the guard flags its large formats as errors. That is the right direction to fail: a forgotten list entry shows up as a loud false alarm in the build instead of slipping through as silent drift.

// eslint.config.mjs — carve-out as a path list (abridged)
const FONT_SIZE_CARVE_OUT = [
  'src/app/page.tsx',        // landing: deliberately larger formats
  'src/app/impressum/**',
  'src/app/login/**',
  'src/**/*.test.{ts,tsx}',  // tests reference classes as test data
]

export default [
  { files: ['src/**/*.{ts,tsx}'], rules: { 'di2/no-raw-font-size': 'error' } },
  { files: FONT_SIZE_CARVE_OUT,   rules: { 'di2/no-raw-font-size': 'off' } },
]

One sequencing detail to close: the guard was activated as the last step, after the app scope had been migrated clean. The other way around, every unfinished file would have needed a temporary exemption list, and temporary exemption lists have a tendency to become permanent.

What the Linter Cannot Do

For the experience report to stay honest, the limits belong in it.

The guard checks the spelling, not the assignment. That an element carries a token at all — that it enforces reliably. Whether it is the right token for the element's category it cannot know — in that gap lived the rule contradiction from the QA finding, and there it can arise again. The category assignment remains a rule in prose, with all the weaknesses described, just on a much smaller attack surface.

Two limits are chosen deliberately. Larger Tailwind defaults from text-lg upward the rule does not flag, because a strikingly large text in an app file is a case for design review, not a candidate for mechanical snapping. And dynamically assembled class strings, such as values concatenated from variables, are only partially covered by a static rule. Both gaps are documented rather than concealed, because a check whose omissions nobody knows creates false confidence.

And finally: the linter preserves the scale, it does not justify it. Whether six steps are the right six and which category deserves which token remains a design decision that precedes the tool.

The Same Pattern in SQL

The case is a frontend case on purpose, because that is where the drift was measurable. The pattern behind it is bound to no framework. Transferring it to SQL is therefore a reasoned analogy, not a second measurement — but one that follows from the same mechanism.

A SQL convention — singular table names, snake_case parameters, a fixed procedure skeleton — is the same class of rule as a font-size scale: a project-specific commitment competing against the statistically more common spelling from other codebases. An agent generating PL/pgSQL drifts there for the same reason as with the pixel values, and the answer has the same structure. The convention lives versioned in a rule file, with reasons and negative examples, the way the Postgres Table Conventions, the PL/pgSQL Procedure Conventions and the PL/pgSQL Function Conventions demonstrate. And what is machine-checkable gets checked by a tool: for SQL layout, say, sqlfluff with a project-specific configuration; for structural conventions, a script in the CI gate.

The division of labor is the same in both worlds. The prose rule explains the why and raises the hit rate. The tool closes the gap between a high hit rate and zero exceptions. Because the underlying pattern stays the same, whether in the frontend or in the database: local plausibility creates global inconsistency. How the overall interplay of rules, skills and agents is set up is described in the pillar article AI-Assisted SQL Development with Claude Code.

FAQ

How do I find consistency drift in my own project?
It often announces itself as an impression first: the interface feels restless without any single spot to point at. It becomes tangible through a counting inventory: one search pattern for the canonical spelling, one for the bypasses, and the two numbers set in relation. For design tokens those are expressions like text-\[[0-9.]+px\] against the token names. More revealing than the total is the distribution of values — clusters on values without a token show where the scale has a gap or where an unofficial second convention has formed.

Isn't it enough to put the rule into CLAUDE.md?
A documented rule improves the hit rate but doesn't guarantee it — at high generation volume, any residual rate turns into measurable drift. Rules in prose and machine checks are therefore not alternatives but two halves: the rule explains the why, the linter enforces the what. Where rules live in Claude Code and what they cost in context is covered in Skills vs. Rules in Claude Code.

Why a custom ESLint rule instead of an off-the-shelf one?
Because the error message should name the project's own snap target, and the check has to cover class strings outside JSX attributes as well. Generic approaches like no-restricted-syntax can flag the pattern but cannot suggest a contextual fix — and it is exactly that fix which makes the error immediately actionable for humans and agents alike.

Does this only apply to CSS and design tokens?
No. AI code drift affects any project-specific convention that competes against a more widespread standard spelling — SQL naming, file structures, error-handling patterns all drift by the same mechanism. The countermeasure is the same everywhere: document with reasons, and pour the machine-checkable part into a tool at error level.

Doesn't a human team produce the same drift?
It does, and that is the honest core. The difference is pace: an agent makes as many individual decisions in a week as a team makes in months, and it makes each one optimized locally rather than for consistency with the previous ones. Conventions that held up passably at human speed break visibly under that volume — which also means AI drift merely makes an old problem visible faster.

Going deeper:

Deriving SQL Conventions with Claude Code — the Generate-Refine-Derive Loop — how a convention comes into being before you can enforce it.
AI-Assisted SQL Development with Claude Code — Rules, Skills and Agents — the pillar: the enforcement system as a whole.
Skills vs. Rules in Claude Code — where conventions live and what they cost in context.

Convention spokes:

Starter kit:

The open DI² starter kit on GitHub — a project template with the rules structure described here; the font-size guard from this article ships with it as a reusable template.

Design Pattern // The Architecture of an ETL Process — How to Isolate Bad Data Cleanly

Marcus — Mon, 20 Jul 2026 13:46:45 +0000

A single date string that cannot be parsed, and the entire ETL run aborts. The design pattern for ETL process architecture presented here prevents exactly that: bad data is isolated, not passed along.

TL;DR — what this article covers:

Work packages and schema layering E0–L2 — how to decompose the ETL process into distinct, self-contained packages, each with its own database schema.
Technical vs. structural transformation — why separating type conversion and foreign-key resolution into two passes is safer and easier to debug than doing both in one.
Data quality at the schema boundaries — erroneous records are caught at the transitions; the main stream keeps flowing cleanly.
Historization as an optional layer — driven by business requirements (SCD 1 / SCD 2), with an extra technical benefit for delta loads.

Prerequisite. Basic familiarity with ETL processes. This is a conceptual article — not a step-by-step tutorial. The technology examples are SQL Server-centric; the Postgres equivalents are covered in the FAQ. Starting point of the article series: Data quality in an ETL process. The present article covers the architecture part.

Tasks of the ETL Process

An ETL process consists of the three general steps E = Extract, T = Transform and L = Load. What exactly has to happen within each of these top-level steps, however, is a matter of definition.

Extraction

In this step, data is extracted from various data sources. Sources can be databases, files, or APIs. The data to be extracted may be structured or unstructured and may come in different formats. This article series deals exclusively with structured data. Structured data sources include relational databases, but also CSV documents as well as XML and JSON documents — as long as their data elements follow a logical structure. Unstructured data such as text from social networks is out of scope here.

This generic definition leaves open what extraction concretely means. The following sections describe a concrete shape for the extraction process:

Materialization of the extracted data
Extended extraction tasks
No typing into target types during extraction

Materialization of Extracted Data

The design pattern presented here stores all extracted data in a database. Storage must be designed so that a single invalid value cannot make it fail. An extraction step may only abort when the delivery itself is unprocessable (corrupted or unparsable files, unexpected encoding) or the infrastructure gives out (storage, network). Writing data to the database is referred to here as the materialization of the data.

Extraction and the materialization of extracted data give three main benefits:

The source system is read only once and as briefly as possible.
All extracted data is available in a database for subsequent processing.
After-the-fact error analysis on concrete records becomes possible.

Reading from the source system can put it under enough load that its performance and response times suffer. Extracting first minimizes the duration of that access.

Once all extracted data sits in a database, downstream steps can work on it using SQL. No additional ETL tool is required just to integrate heterogeneous source systems. This lowers technical hurdles, and downstream processes are often substantially faster — both in execution and in development.

Extended Extraction Tasks

For text files in XML and JSON format (and possibly CSV), materialization works a bit differently. XML and JSON documents are themselves stored in the database before the data they contain is extracted. As an extended extraction task, the attributes are then extracted from the stored documents using T-SQL functions such as OPENJSON or OPENXML and written to the database.

No Typing Into Target Types During Extraction

Text-file deliveries are particularly problematic. The data they contain is not type-safe by any means. A date delivered as text may or may not be convertible into a date value. There has to be an agreement between the delivering process and the ETL process about, for example, which date format is used (yyyy-MM-dd, dd.MM.yyyy, etc.). Converting values during extraction is a source of errors and risks aborting the entire ETL run. In this pattern, typing into the business target types is therefore not permitted during extraction: input values are first materialized in a deliberately tolerant text format and only converted into the required target types during the technical transformation.

Transformation

A common definition of the transformation step goes something like: transformation converts the extracted data into the desired format. Another possible definition sums up the required tasks under the term data integration. Both definitions — and the term data integration itself — say everything and nothing. They offer no guidance on the concretely required measures.

Starting from the extracted data, the design pattern presented here defines two mandatory tasks and one optional task:

Typing of the extracted data
Data quality check
Optional: historization

When data arrives as text files, the extracted attributes must first be converted into the target data types. That is often also true when data is extracted from databases whose data types diverge from those in the target system. This section, however, focuses on text files as the data source. As described above, values extracted from text files are first stored as text. The target system expects strongly typed data. A date, for example, will routinely have to be converted into a date value.

The data quality check is, on top of that, a critically important task that fundamentally shapes the outcome of an ETL run. It starts with checking whether a delivered value can be converted into the data type of the corresponding target field. Where required, deliveries must also be checked for duplicates. Further rules cover value ranges, dependencies between fields, reference data, and business plausibility.

In the historization step, source data identified as changed (new, modified, or deleted records) is rolled forward in separate tables, so it is always traceable when a record was inserted, modified, or deleted. This step is optional. A colleague once called the historized data the brain of the ETL process. With complete historization and reproducible transformation and mapping rules, the data of the downstream target system can be reconstructed from the historized data. Of course, historization comes with additional maintenance tasks such as backups.

The term data integration is, in fact, closer to what we will call structural transformation. There, data from various sources is filtered, merged, and aggregated. Although that is also a transformation task, the design pattern presented here does not perform it during T of the ETL process — it happens during L. At this point, it pays to draw a sharp terminological line between the transformation tasks described in this section and structural transformation. The transformation tasks described here, performed during T, are referred to as technical transformation. The transformation tasks performed during L are referred to as structural transformation.

The boundaries between the three top-level ETL steps are fluid and, in the end, a matter of definition.

Typing of Extracted Data

If the data source is a database such as SQL Server or Oracle, the data will typically already be strongly typed. Even then, type conversion may still be necessary to match the data types of the target system.

Example: Typical cases are the length of text fields and the storage of a point in time without a time zone. Application developers do not always pay close attention to input length limits. As a result, an address field in a source system might be able to hold entire novels. Users who notice such a gap will — empirically — happily use it to dump information that simply does not belong there. If the source delivers a point in time without a time-zone offset, it must be clarified before conversion in which time zone that point in time is to be interpreted from a business perspective. In SQL Server, depending on the requirement, datetime2 or datetimeoffset is the target type of choice.

When processing attributes extracted from a text file, typing the extracted values into the target data types is always required.

Example: During extraction, attributes are stored as values of type text. A text that looks like a date is not necessarily convertible into a date value. For instance, 30-02-2023 is not a valid date. Another example: 03-05-2023 cannot be interpreted as a date without additional context about the data source. Read in the American style (MM-dd-yyyy), it yields March 5, 2023; read in the style common in Germany (dd-MM-yyyy), it yields May 3, 2023. Correct interpretation requires knowledge of the date format — that is, the format string. Similar challenges arise for numeric values with respect to decimal and thousands separators.

Data Quality Check

The data quality check inspects the extracted and converted data for completeness and correctness. These checks cover a wide field. Examples are:

Type conversion check
Duplicate identification
Spelling and orthography check on text values
Foreign key check
Mandatory field missing value check
Business logic validation

The article Data quality in an ETL process introduces the term technical data quality. The check on technical data quality operates on the typed data. For data quality checks on typed data, simple logical conditions can be set up, identifying errors on a value or per-record basis. A data quality rule is technically expressed as a WHERE predicate in the ETL process and applied to the typed data — the predicate is formulated so that it selects exactly the erroneous records. Every hit carries an error in the inspected field the rule refers to.

Type Conversion Check

Whether the type conversion succeeds or fails has direct impact on all downstream tasks. If an input value cannot be converted into the target data type, the offending record may have to be excluded from further processing. The design pattern presented here checks for every delivered source record whether its input values can be converted into the respective target data types.

Duplicate Identification

Duplicate identification can be arbitrarily complex. This article series limits itself to a combination of fields that, per the delivery contract, must follow a defined cardinality or must be unique (cardinality = 1).

Spelling and Orthography Check on Text Values

Phone numbers, for instance, have many possible notations. The German DIN 5008 standard prescribes that the area code be written without parentheses and separated from the rest of the number by a single space. Format rules like these can serve as check rules within the technical transformation. Which notation counts as correct, however, is not a question of the standard but a convention to be defined per target system.

Foreign Key Check

If the delivered data contains a foreign key relationship, only the syntactic validity of a delivered foreign key value is checked here — that is, format, presence where required, and data type. The actual foreign key resolution against the target system (mapping source-system code → target surrogate key) only happens later, as part of the structural transformation. Only there is referential validity established against the reference table. The reason for the split: resolution needs context from the target system (such as a Countries table), whereas format and presence checks can be answered from the record alone.

Mandatory Field Missing Value Check

If an attribute is a mandatory field in the target system, the typed data must be inspected to ensure that a corresponding value was delivered.

Business Logic Validation

Checking business logic is itself a wide field that can become arbitrarily complex. But even checking simple business rules improves data quality noticeably. A simple example is a customer's date of birth: it must not lie in the future.

Loading

In the final step of the ETL process, the typed data is structurally transformed to match the data structures of the target system, optionally re-checked for data errors, filtered, aggregated, historized, and finally loaded into the target system. The tasks involved are:

Structural transformation
Data quality check
Filtering
Aggregation
Optional: historization
Loading the data into the target system

The previously technically transformed data can be loaded into different target systems. The target can be, for example, a CRM system or a data warehouse. The structural transformation task is specific to the chosen target system. That is why the structural transformation happens during L of the ETL process.

Structural Transformation

The structural transformation works exclusively on the typed, quality-checked, and possibly historized data that was found to be error-free. Technically, the structural transformation corresponds to a SELECT statement joining the historized tables via JOINs and shaping the output to match the target system's data structures. Among other things, this step resolves foreign keys and lookup values:

Foreign key resolution
Lookup value resolution

The output of the structural transformation is — as with extraction and the technical transformation — materialized in the database, so this data, too, is available for analysis and error diagnosis. The data structures of the structurally transformed data largely correspond to the structures in the target system. In particular, the column names and data types of the output are chosen to match those expected by the target.

Foreign Key Resolution

If foreign keys cannot be determined from the extracted data alone, they must be resolved against the target system's data.

Example: Target systems often store countries in a separate table. The country United States is then identified both by its country name and — typically — by a technical key (e.g., a GUID). When structurally transforming a customer whose source data identifies the country as the text United States, this text must be translated to the primary key of that country in the target system and stored as a foreign key with the customer record.

Foreign key resolution requires direct read access to the Countries table in the target system. If direct access is not available, that table must be read in advance, and its data must be made available in a database. At that point, reading the Countries table is itself an extraction task.

Lookup Value Resolution

Source and target systems often use different codings for the value of a dropdown field. A dropdown field, for example, could be a list field for selecting a customer's salutation.

In the database, what is shown and selected in the application is rarely stored verbatim. A salutation of Mr. might be stored as the value 1 and Ms. as 2. The codings used in source and target systems typically differ.

These coded attributes are often not stored in separate tables. Translating the source-system code into the target-system code therefore requires explicit knowledge of the translation rules. Following terminology used in Microsoft Dynamics CRM, this translation is called lookup value resolution. To resolve lookup values, the codes used by source and target systems must be determined and stored in a mapping table that is consulted during the structural transformation.

Data Quality Check

Experience from real projects shows that foreign key resolution and lookup value resolution are major sources of errors — typically rooted in incomplete or incorrect mappings of source-system codes to target-system codes.

Filtering

Unless the target system is being initially populated with data, only records with specific properties should be loaded into the target. Filtering for the records actually destined for loading should — where possible — already happen during the technical transformation. If that is not feasible there, filtering happens during the structural transformation.

Aggregation

Data may need to be aggregated before loading into the target system.

If end-to-end traceability of every processing step in the ETL pipeline is required, aggregation should be considered as a separate processing step downstream of the structural transformation. Aggregated data would then be stored in separate tables of the staging database.

Historization

As in the technical transformation, the structurally transformed and checked data can be rolled forward in separate tables. New records are inserted, changed records are updated, and deleted records are flagged as deleted.

Loading Data Into the Target System

The final loading of the changed data into the target system therefore works on quality-assured, structurally transformed, and historized data. Only error-free records — those for which foreign keys and lookup values were successfully resolved — are loaded.

Technologically, this article focuses on loading change data into a target database. The target database is updated via SQL statements, that is, via INSERT, UPDATE, and where applicable DELETE statements. Other target systems — such as Microsoft Dynamics 365 — require the use of a proprietary API, both for writing data into and reading data from the target. In that case, an API-capable integration mechanism is needed: SQL Server Integration Services with suitable components, a custom API client, or another ETL/ELT tool.

Architecture of the ETL Process

The pattern fits in one sentence: Each work package has its own database schema, checks data quality at its boundary, and passes on only error-free records. The architecture presented here is broadly transferable and can be used regardless of the kind of source data or target system, in relational data migration and data integration projects alike. It also fits the data-loading workflow of a data warehouse. The ETL process is decomposed into small, self-contained work packages with sharply defined tasks. At the end of the pipeline, quality-assured data sits in data structures similar to those of the target system and can be loaded there without further business transformations.

Work Packages of the ETL Process

The following diagram illustrates the work packages of the ETL process presented here:

The top lane of the diagram shows the top-level steps from the ETL acronym: Extract, Transform, and Load. The bottom lane names the concrete work packages of the ETL process and maps each to one of the top-level steps. Each work package is paired with a database schema. The middle lane labels the database schemas used per work package (E0–L2). During processing, data is handed from work package to work package, that is, from schema to schema. The ETL process consists of the following work packages:

Data extraction
Technical transformation
Historization of the technically transformed data
Structural transformation
Historization of the structurally transformed data
Loading the data into the target system

The six schemas at a glance:

Schema	Purpose	Error class at the boundary	Persistence
E0	raw documents (XML/JSON)	format/parser errors	only for document sources
E1	extracted raw data, untyped	infrastructure/extraction errors	yes
T1	typed and checked data	technical data quality (type errors)	yes
T2	history of the technically transformed data	—	optional
L1	target-system-like structures	structural data quality (FK/lookup)	yes
L2	history of the structurally transformed data plus load flags	—	optional

The Technical Transformation and Structural Transformation work packages check the data quality of the transformed data and hand over only error-free data to the next package. In the diagram, these checks are indicated by the dark arrow heads. The sections below summarize the steps within each work package and provide an overview of the technology used to carry them out.

Extraction

The goal of extraction is to first store all data to be processed in the staging database. Within extraction, it matters whether the source data is read from a database or from documents with table-like structures (for example, Excel or CSV documents), or whether documents with complex logical structures (for example, XML or JSON) are to be processed.

Extraction From a Database

When reading from a database or from table-like structures, the attributes / columns are first materialized into tables of schema E1. The structures of the tables in schema E1 closely match the structures in the source system. When extracting from a database, the data is stored using the data types from the source system. If the source-system data types are not supported by SQL Server, the data is stored in schema E1 as nvarchar. That works for values that can be represented as text. Binary or special types (such as geodata) need a deliberate strategy instead — for example varbinary(max) or a raw format.

Extraction From Documents With Table-Like Structures

Data from documents with table-like structures, such as Excel and CSV documents, arrives without guaranteed typing. A CSV file transports no data types — every delivered value is text at first. Excel does not enforce typing either: individual rows of a column set up as a date can still contain a number or free text. Even a delivery agreement about the column format is an expectation, not a guarantee. Checking remains the ETL process's job, all the more since these documents are often created and maintained by hand. To make sure that all values from these documents can be materialized in the staging database in tables of schema E1, all data is first stored as nvarchar. Use generous text lengths — nvarchar(max) where no upper bound is known — so that materialization does not fail on length.

Extraction From Documents With Complex Logical Structures

When XML or JSON documents are to be processed, the documents themselves are first stored in tables of schema E0. The attributes are then extracted into tables of schema E1. The attribute extraction works on the documents stored in schema E0 in the first step.

Attributes from text files are stored in schema E1 as nvarchar. Use generous text lengths, nvarchar(max) where no upper bound is known.

Technology

Extraction of data from a database or from table-like structures can be done with Microsoft's SQL Server Integration Services (SSIS) or any other ETL tool. To extract XML or JSON documents, SSIS first loads them into tables of schema E0. OPENJSON extracts the attributes from JSON documents. For XML, the methods of the xml data type (.nodes(), .value()) or the older OPENXML are available — OPENXML uses its own memory and parsing model (sp_xml_preparedocument) and is mostly found in legacy code.

Summary

This extraction approach has several advantages. Using an ETL tool such as SSIS, which supports a high degree of parallelism in data processing, the data can be materialized into the schemas E0 and E1 with high throughput. Upstream systems are minimally impacted, and the data is available for further processing — such as attribute extraction from XML and JSON documents via the T-SQL functions OPENXML or OPENJSON — in the staging database. The materialized data also enables root-cause analysis when errors arise.

Technical Transformation

Within the top-level transformation step, this design pattern runs the technical transformation described above. It consists of the following sub-steps:

Type conversion
Technical data quality check
Data error logging
Flagging of erroneous records
Hash value computation

Type Conversion

The output of the technical transformation is typed data that matches the target system's expectations. Typing can be driven by metadata via generic user-defined stored procedures and materializes the data into tables of schema T1.

Per attribute from schema E1, two columns are provided in schema T1. The first column holds the extracted value in the data type used in schema E1. The second column holds the typed value in the target data type, provided the value can be converted. If the value cannot be converted, the second column stores NULL — in T-SQL, TRY_CONVERT and TRY_CAST provide exactly that behavior, returning NULL on a failed conversion.

Technical Data Quality Check

After typing, the result is checked by comparing the column pairs for type-conversion problems. A conversion error exists exactly when the input column holds a value but the typed column is NULL. A NULL input remains a legitimate NULL in the target and is not a conversion error — whether the field may be empty at all is the mandatory-field check's job. How empty strings are treated (as NULL or as a value in their own right) must be defined up front. Because the type conversion is purely technical, this check is also called the technical data quality check. The error check can already be extended here to cover simple, record-local business rules.

Data Error Logging

Detected data errors are logged in a readable, queryable form in an error table.

Flagging of Erroneous Records

If a record contains at least one error, it is flagged as erroneous so it can be excluded from further processing. The flag lives in a column that stores the count of detected errors. Error-free records carry NULL in this column.

Hash Value Computation

The last sub-step of the technical transformation is computing and storing two hash values per record. The first hash represents the business-key columns of the record; the second hash represents all remaining columns. Through these two hashes, the next work package — Historization of Technically Transformed Data — can identify change records. Hash values are computed only for error-free records.

Two things must be unambiguously defined for this: the serialization of the columns (column order, delimiter, NULL handling, encoding, and the representation of dates and decimals: without an unambiguous delimiter, ('AB','C') and ('A','BC') produce the same hash input) and the algorithm — for SQL Server SHA2_256 or SHA2_512 (all older HASHBYTES algorithms have been deprecated since SQL Server 2016). Strictly speaking, an equal hash does not prove equal data, because different inputs can produce the same hash value. With SHA2_256, this residual risk is practically zero. With short checksums such as CHECKSUM (32 bits), collisions become likely at a few tens of thousands of rows (around 50 % at roughly 77,000 distinct inputs). As the sole basis for change detection, they are therefore unsuitable.

Technology

Conversion of extracted values into target data types, error checks, flagging of erroneous records, and hash value computation can all be implemented as generic stored procedures that build the appropriate dynamic SQL statements from metadata. This requires one-time investment in implementing those procedures. Once they exist, the tasks above reduce to simple procedure calls. In the long run, this reduces development effort and maximizes reuse.

Scope of the dynamic part. Dynamic SQL in the strict sense only appears in the data-quality check — one rule maps to one WHERE clause applied to the typed table at run time. Beyond that, the procedures listed above (type conversion, DQ check, flagging, hash-value computation) are metadata-generatable, because they follow the same structural pattern for every target table. This generation covers the corridor from extraction up to technical historization (schema T2). From schema L1 onward — the structural transformation — the JOIN statements are target-system-specific and are developed manually. The same applies to the historization procedures for schema L2 (see the corresponding sections below).

How these generic checks are implemented in practice is shown in Checking Data Quality with SQL — a configurable framework that handles the tasks listed above through metadata-driven procedures.

Historization of Technically Transformed Data

Historization consists of the following sub-steps:

Historization
Identification of change records
Identification via hash values
Storing hash values
Promoting only error-free records

Historization

Historization means that delivered data is rolled forward in a database. In the data warehousing world, Slowly Changing Dimensions describes several types of historization that specify exactly how the rolling-forward works. Slowly Changing Dimensions is also commonly abbreviated as SCD. Ralph Kimball describes SCD techniques from Type 0 through Type 7. Two of them matter most for this pattern:

SCD 1 — strictly speaking, no real historization at all. A record loaded earlier is simply overwritten by its changed counterpart. Only the most recent state of each record is ever stored.
SCD 2 — on a relevant change, a new version of the record is added (Add New Row). To implement this, every historized table here gets two extra columns valid_from and valid_till, indicating the record's validity interval (they correspond to Kimball's row effective date and row expiration date). The intervals are half-open — [valid_from, valid_till): valid_from is part of the validity, valid_till marks the start of the successor version. Currently valid records are open-ended, indicated by NULL in valid_till. Common implementation variants are a far-future date such as 9999-12-31 instead of the NULL, and an additional current-row flag. When a change record arrives for a currently valid record, the previously valid record's valid_till is set to the date from which the change record becomes valid, and the change record itself is inserted with valid_till = NULL.

Historization is optional. It helps most with delta loads, when downstream objects depend on unchanged master data — even there, it is not strictly required. Suppose a customer places a new order. In a delta load, the order is delivered, but not the customer, who has not changed. Resolving the foreign-key relationship between order and customer cannot be done from the delivered data alone. To resolve it, either the customer data has to be extracted from the target system, or customers must be historized in the database so they are available on subsequent ETL runs.

In the context of the ETL process presented here, historization means that only error-free, changed records are historized. Rolling forward can follow either SCD 1 or SCD 2 — bearing in mind that SCD 1 keeps no historical versions, only the current state.

Identification of Change Records

Historization requires that change records can be recognized in the source system and, subsequently, in the historized tables. Source systems often provide no information — or only unreliable information — about when a record was inserted, modified, or deleted. When a CSV file is generated from a hand-edited Excel document, for example, it must be assumed that no reliable change information is available. Against that backdrop, this design pattern always derives change records from the data itself. The hash values computed during the technical transformation are used for this.

Identification via Hash Values

In the Technical Transformation section, two hash values were computed for error-free records — one over the business-key columns, one over all remaining columns. The core idea: T2 accumulates the complete history of the delivered data across the runs, and every new delivery (full or delta) is analyzed against that history. The comparison set is exclusively the currently valid version per business key (for SCD 2, the rows with valid_till = NULL), never the historical versions. New, modified, and deleted records are identified by comparing the hash values between the technically transformed data (schema T1) and the currently valid version in the historized data (schema T2):

Hash (business key)	Hash (attributes)	Type of change
present in T1 and T2, equal	equal	no change
present in T1 and T2, equal	not equal	record was modified
only in T1 (extracted)	—	new record
only in T2 (historized)	—	record was deleted (assumes a full delivery — see below)

Limits of delete detection. The fourth row only holds for a full delivery: if every run delivers the complete population, a record missing from T1 really was deleted — the deletions emerge as the difference between delivery and history. In a delta load, the same absence only means that the record was not delivered as a change. Two cases must therefore be distinguished for delta deliveries. If the source system soft-deletes and ships the delete flag, the deletion is processed like any other change and marked as deleted in T2. If the source system deletes physically and ships nothing about it, deletions are fundamentally undetectable from the delivered data. This special case needs a separate mechanism: a periodic full reconciliation (which can also run on the business keys alone) or, where the source system offers it, a change feed such as CDC or Change Tracking. A detected delete is implemented under SCD 2 by closing the currently valid version (valid_till is set) and marking it as deleted. Whether an additional dedicated delete version is created is a separate modeling decision.

Storing Hash Values

When a record is inserted into the historized tables, updated, or flagged as deleted, the hash values of the new, modified, or deleted record are stored or updated there as well. This ensures that the hash values stored there always represent the status quo of the source systems and that change records can be identified via hash values at any later point (in subsequent ETL runs).

Promoting Only Error-Free Records

Promoting an erroneous record — and later loading it into the target system — could cause an error and potentially abort the entire ETL run. Therefore, only error-free change records from schema T1 are stored in schema T2.

Structural Transformation

The structural transformation consists of the following sub-steps:

Structural transformation and resolution of foreign-key relationships and lookup values
Structural data quality check
Data error logging
Flagging of erroneous records
Hash value computation

Structural Transformation and Resolution of Foreign Key Relationships and Lookup Values

The output of the structural transformation is data in table structures matching the target system. SQL statements with the required JOINs in the FROM clause implement the structural transformation. Developing those statements requires solid knowledge of the data, the relationships among entities, and especially the foreign-key relationships among tables in the source system — or among the source systems being integrated.

Besides the actual structural transformation of source data, the structural transformation resolves foreign-key relationships for the target system and determines the codes to store for lookup values. The result is stored in tables of schema L1, whose structure, column names, and data types resemble those of the target system.

Structural Data Quality Check

After the structural transformation, the result is checked: could all foreign-key relationships and lookup values be resolved? If no foreign key or no lookup code can be determined for a record, the record counts as erroneous. Since this check concerns the outcome of the structural transformation, it is called the structural data quality check here.

Data Error Logging

Detected data errors are logged in a readable, queryable form in an error table.

Flagging of Erroneous Records

Hash Value Computation

The last sub-step of the structural transformation is computing two hash values per record. The first hash represents the business-key columns of a structurally transformed record; the second hash represents all remaining attribute columns. Both hashes let the next work package — Historization of Structurally Transformed Data — identify change records.

Historization of Structurally Transformed Data

Historization of the structurally transformed data covers the same sub-steps as historization of the technically transformed data. It is an optional step, because — as long as the data from the technical transformation is historized — the structurally transformed data can always be reconstructed via the structural transformation.

The structurally transformed data from tables in schema L1 is historized into tables of schema L2. The approach is identical to historizing data from schema T1 into schema T2. Only error-free change records are historized from L1 into L2. New, changed, and deleted records are additionally marked with a flag indicating that they still need to be loaded into the target system. If the data of schema L2 is historized as well, it must never be deleted and should be backed up by a maintenance process. This makes it traceable when the ETL process handled which change. The actual time of change in the source system can only be read from this if the source delivers corresponding metadata.

The procedures required to historize data into schema L2 must be developed manually.

Loading

The transformed and quality-checked data in schema L2 can now be loaded into the target system using a technology of choice. If historization in L2 is not enabled, loading happens directly from schema L1. When loading from L2, the change records to be loaded are identified via a flag indicating whether the record has already been loaded. Records loaded successfully into the target system are flagged accordingly.

Conclusion

At the end stands the ground rule that runs through all work packages: each work package has its own schema, checks data quality at its boundary, and passes on only error-free records. Bad data stays isolated where it was detected — along with a log that narrows error diagnosis down to individual records. A single unconvertible value no longer has to abort the ETL run.

The E0-to-L2 layering is a construction kit, not a dogma. If you do not need historization, drop T2 and L2. If storage is tight, implement individual layers as views (see the FAQ for the trade-offs). In effect, the pattern works as persistent staging: the essential transformations run inside the database. The implementation layer beneath this architecture is shown in Checking Data Quality with SQL. Where the pattern sits between ETL and ELT is covered in ETL vs. ELT — How to Tell Which Pattern You Actually Built.

The pattern reaches its limits where per-package persistence does not pay off: streaming and low-latency requirements, extreme data volumes without matching storage, and simple one-way pipelines without reprocessing or audit needs.

FAQ

What is the difference between technical and structural transformation?

The technical transformation works on each record in isolation: it converts input values into the target data types (text → date, decimal, …) and runs a first data-quality check at the value level, both without looking at other tables. The structural transformation, by contrast, needs context from the target system — resolving foreign keys, mapping lookup values — and therefore happens in its own work package after the technical transformation. Splitting them lets the two classes of errors be logged and fixed separately.

Why materialize every work package in the database?

Materialization — writing each work package's output to a database table — decouples three things: The source system is read only once and stays untouched by the downstream packages. Every step becomes restartable without rerunning the entire ETL pipeline. And a traceable audit trail emerges for diagnosing errors on individual records. The additional storage and I/O cost is real and grows with data volume. It is the price for restartability, auditability, and error diagnosis — a worthwhile trade in this pattern's typical scenarios (migration and integration projects). At large volumes, it deserves a deliberate trade-off (see the next question on the persistence layers).

Do you really need all six persistence layers?

Not strictly. The full E0/E1/T1/T2/L1/L2 layering pays off mostly where audit trail, per-package restartability, and after-the-fact error analysis are hard requirements — typically in classical migration and CRM-integration projects with data volumes in the low to medium range. For large volumes or modern platforms such as Snowflake, Databricks, or BigQuery, some intermediate layers are often implemented as views rather than materialized tables — the architectural logic stays the same, storage and I/O drop, but the transformation cost is paid again on every access. Rule of thumb: L1 is the first candidate for virtualization, because it can always be regenerated from the historized data in schema T2. L2 only qualifies if its load flags and its own history are given up. T2 itself stays materialized — a history cannot be reconstructed from a current snapshot.

When do you need historization (SCD)?

Historization is primarily a business requirement: it is needed when state trajectories, historical reporting, or reproducible snapshots are required — regardless of the load type. Delta loads add a technical benefit on top: there, the order record is delivered but not the related customer, if the customer has not changed. Without historized customer data, the foreign-key relationship cannot be resolved. With full snapshot loads and no business need for history, SCD is dispensable.

Can this pattern be used with Postgres instead of SQL Server?

The core concepts of the pattern — work packages, schema layering E0–L2, data quality at the boundaries, hash-based SCD — are not tied to SQL Server and can be implemented in any relational database. The technology stack presented here, however, is consistently SQL Server-centric (SSIS, OPENXML, OPENJSON, HASHBYTES, metadata-driven procedure generation). The most important Postgres equivalents are: xmltable() for XML, jsonb_to_recordset() or JSON_TABLE (available since PostgreSQL 17) for JSON, and digest(…, 'sha256') from pgcrypto for hashes. For bulk imports into the staging tables, Postgres offers COPY (it does not replace the orchestration of an SSIS), and loading into the target can be modeled with MERGE. Structural adaptation can go deeper than just renaming functions — for example, the E0/E1 split for XML/JSON can often be dropped in Postgres because xmltable() extracts directly from the source read. Whether the raw layer E0 is dropped remains a trade-off between storage and reproducibility.

How does this pattern relate to Data Vault 2.0?

There are conceptual parallels — business key, hash-based delta detection, persistent layers, auditability. The pattern does not implement Data Vault modeling, though: there are no hubs, links, or satellites with their modeling rules, no mandatory insert-only history, and no raw-vault-versus-business-vault separation. For classical migration and CRM-integration projects with audit requirements, the lighter pattern is pragmatic. For pure data-warehouse loading with multi-source integration, Data Vault 2.0 is worth a look.

Upstream:

Data quality in an ETL process — root of the article series.
Data Quality // Fundamentals of Type Conversion with T-SQL — foundational article on TRY_CONVERT.

Implementation layer:

Checking Data Quality with SQL — a Configurable Framework — spotting bad data generically and classifying it by severity.
Design Pattern // Safe Type Conversion with T-SQL — fn_try_convert_* UDFs for the technical transformation.
Design Pattern // Logging an ETL process with T-SQL — cluster sibling covering the logging layer.

Positioning:

ETL vs. ELT — How to Tell Which Pattern You Actually Built — classifies the architecture presented here as persistent-staging ELT.

Checking Data Quality with SQL — a Configurable Framework for Spotting Bad Data Generically

Marcus — Thu, 16 Jul 2026 21:12:39 +0000

Bad data gives no warning. An age of 200 years, a duplicate customer number, a country code that doesn't exist — in the source system nobody notices. Only when the ETL run tries to push the rows into the strictly modelled target layer does the load break: on a CHECK, on a UNIQUE index, on a foreign key. Checking data quality with SQL means finding exactly those rows beforehand, classifying them by severity and sorting them out deliberately — without a special tool, with a handful of generic SQL routines.

The essentials up front:

Three generic check routines — a WHERE clause, a uniqueness check and a foreign-key check — cover a large share of typical data errors.
All three write into one shared error table: one row per violation, with the business key, the offending value and a plain-text message.
A severity (error / warning / information) drives a quality gate: only error-free rows flow on.
All in plain PL/pgSQL — the same basic principle that specialised data-quality tools use too, here dependency-free to build yourself.

Prerequisite: Postgres as the example engine and a central staging layer that is loaded raw first. The checks run against it set-based. That is the counter-design to the tool-centric package that mixes extraction, transformation and loading per table and scatters the quality check across the whole process (more on that in the architecture article of this series).

Data quality with SQL: the dimensions behind it

There is plenty to read about data quality and little to grab hold of. The literature has agreed for decades on what makes data good: Wang & Strong described fifteen dimensions in their 1996 paper "Beyond Accuracy", the DAMA UK working group singled out six of them in 2013 as core dimensions for practice (completeness, uniqueness, timeliness, validity, accuracy, consistency), and ISO/IEC 25012 standardises data-quality characteristics as a norm. What the literature rarely delivers is the how — and when it does, it is usually tied to a particular tool.

The concrete lever, though, is obvious: anyone who wants to check data quality with SQL formulates the check as a query and ends up with a table holding the bad records. Three routines cover a large share of practice, and each one cleanly maps to one of the established dimensions:

Check routine	What it finds	Dimension
WHERE clause on one table	values outside allowed ranges, missing mandatory values	Validity (+ Completeness)
Uniqueness / maximum occurrences	duplicates, over-frequent keys	Uniqueness
Foreign key against a reference	orphaned rows without a master	Consistency / Integrity

That covers four of the six dimensions; timeliness and accuracy — in the sense of matching the real world — lie outside their reach and need other means. The theory behind it — error classes, the criteria canon, and the full coverage map including the two blind spots — is deepened in the concept article Data Quality: Dimensions and Error Classes.

Honestly placed: this approach is not new. Tools like Soda Core, dbt tests or Great Expectations follow the same approach at the core — formulate check logic, collect the hits, attach a severity; whether that happens as generated SQL or against another engine (Pandas, Spark, a data warehouse) is an implementation detail. Around that, granted, they offer more, from monitoring through profiling to lineage. The value of the home-built version is not originality but transparency: every line is readable, nothing is bound to a product, and it runs everywhere you aren't allowed to install an extra tool.

The common denominator: one error table

The backbone is not the check but its result. All three routines write into the same table — one row per violation found, stored so that the source record can later be identified unambiguously:

  1: CREATE TABLE dq.error
  2: (
  3:     id             bigint      NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,schema_name    text        NOT NULL
  5:    ,table_name     text        NOT NULL
  6:    ,id1_column     text
  7:    ,id1_value      text
  8:    ,id2_column     text
  9:    ,id2_value      text
 10:    ,id3_column     text
 11:    ,id3_value      text
 12:    ,error_column   text
 13:    ,error_value    text
 14:    ,severity       char(1)     NOT NULL
 15:    ,message        text        NOT NULL
 16:    ,created_on     timestamptz NOT NULL DEFAULT now()
 17:    ,CONSTRAINT pk_error            PRIMARY KEY (id)
 18:    ,CONSTRAINT ck_error_severity   CHECK (severity IN ('E', 'W', 'I'))
 19: );

The column pairs id1_column/id1_value through id3_column/id3_value hold the business key of the affected record: id1_column holds the column name (say customer_id), id1_value the value (say 4711). That lets you reconstruct the bad row later — WHERE customer_id = 4711. Three pairs are enough for composite keys; in practice one almost always suffices. Alongside, each row records the checked column (error_column), the offending value (error_value), the message in plain text and the severity (severity: Error, Warning, Information).

The configuration: one rule per row

The check rules are not cast into code but written into a table. One row = one rule. A new check is a data row instead of a code change — it needs no code deployment, and the business side can read along with what is being checked:

  1: CREATE TABLE dq.check_rule
  2: (
  3:     id              bigint  NOT NULL GENERATED ALWAYS AS IDENTITY
  4:    ,check_type      text    NOT NULL   -- 'constraint' | 'unique' | 'lookup'
  5:    ,schema_name     text    NOT NULL
  6:    ,table_name      text    NOT NULL
  7:    ,id1_column      text    NOT NULL   -- business key (up to three)
  8:    ,id2_column      text
  9:    ,id3_column      text
 10:    ,check_column    text    NOT NULL   -- checked column(s); composite as comma list
 11:    ,where_clause    text               -- 'constraint': the "bad" predicate
 12:    ,max_occurrence  int     NOT NULL DEFAULT 1   -- 'unique': allowed occurrences
 13:    ,ref_schema      text               -- 'lookup': reference table
 14:    ,ref_table       text
 15:    ,ref_column      text               -- position-aligned with check_column
 16:    ,severity        char(1) NOT NULL DEFAULT 'E'
 17:    ,message         text    NOT NULL
 18:    ,active          boolean NOT NULL DEFAULT true
 19:    ,CONSTRAINT pk_check_rule  PRIMARY KEY (id)
 20: );

check_type decides which of the three routines is built for the row. Depending on the type, different columns matter: where_clause for the WHERE check, max_occurrence for uniqueness, ref_schema/ref_table/ref_column for the foreign key. severity and message hang on every rule — so each finding carries its severity and its plain text straight from the configuration. Nobody has to enter the rows for required fields, key uniqueness and type limits by hand, though: how these rules can be derived from information_schema is shown in Deriving Data Quality Rules from the Schema.

Routine 1: the WHERE clause

The simplest and at the same time most powerful check: a condition that describes bad rows, attached to a table. Everything the condition matches is a finding. For the rule "age must be between 0 and 120" the routine produces this statement:

  1: INSERT INTO dq.error (schema_name, table_name, id1_column, id1_value,
  2:                       error_column, error_value, severity, message)
  3: SELECT
  4:     'staging'
  5:    ,'customer'
  6:    ,'customer_id'
  7:    ,T01.customer_id::text
  8:    ,'age'
  9:    ,T01.age::text
 10:    ,'E'
 11:    ,'Age out of range 0..120'
 12: FROM
 13:    staging.customer T01
 14: WHERE
 15:    age < 0 OR age > 120;

The predicate on line 15 comes unchanged from where_clause. With that, this one routine covers a whole family: ranges (age < 0 OR age > 120), mandatory fields (email IS NULL), formats (length(zip) <> 5), plausibility (order_date > current_date). Two dimensions at once — validity and completeness. How to push this routine to its limits — and which NULL trap of three-valued logic it has to avoid — is covered in depth by the spoke Validating Data with SQL.

Routine 2: uniqueness and cardinality

Duplicate keys are the classic case. The check is a GROUP BY with HAVING on the count:

  1: SELECT
  2:    customer_id
  3: FROM
  4:    staging.customer
  5: GROUP BY
  6:    customer_id
  7: HAVING
  8:    count(*) > 1;

The real trick sits in the > 1: it comes from max_occurrence. Instead of checking for uniqueness, you check for a maximum count — > 1 for true uniqueness, > 3 for example when a key may appear at most three times. The routine then joins the keys it found back onto the table and logs every occurrence (not just the first), so that every affected row appears in the error table.

What is checked here is exactly the business key that identifies the record — the same column that lands in the error table as id1_column. That is no coincidence: this check directly mirrors the UNIQUE constraint that the strict target layer carries on the business key. What must be unique there is caught up front in the source. How to push this routine — maximum cardinality, composite keys and the NULL trap in UNIQUE, where SQL Server and Postgres differ — is deepened by the spoke Finding Duplicates with SQL.

Routine 3: referential integrity

A foreign key into the void — a country_code for which there is no entry in the master-data table. Put generically: all rows of the child table that find no partner in the master table via LEFT JOIN:

  1: SELECT
  2:    T01.customer_id
  3: FROM
  4:    staging.customer T01
  5:    LEFT JOIN staging.country T02
  6:    ON
  7:      T01.country_code = T02.country_code
  8: WHERE
  9:        T01.country_code IS NOT NULL
 10:    AND T02.country_code IS NULL;

Child table, child column(s) (check_column) and master (ref_schema/ref_table/ref_column) come from the configuration — so the same routine works for any master-child relationship. A composite foreign key sits as a position-aligned comma list in check_column and ref_column: the first child field belongs to the first master field, and the runner splits both lists and builds the join over all pairs. The IS NOT NULL condition on line 9 deliberately separates "unknown value" (an error) from "no value given" (that is the WHERE routine's job). How to push this routine — the three phrasings LEFT JOIN … IS NULL/NOT EXISTS/NOT IN, the notorious NOT IN-plus-NULL trap, and composite and self-referencing foreign keys — is deepened by the spoke Finding Orphaned Records with SQL.

The runner: dynamic SQL — done safely

The three statements above are hard-coded. They become generic when a function assembles them at runtime from the configuration. In PL/pgSQL, format() is the right tool — and the point where you have to be careful. Identifiers belong in with %I, literals with %L; both are quoted correctly by Postgres and rule out SQL injection through table and column names. Here is the branch for the WHERE check:

  1: l_sql := format($sql$INSERT INTO dq.error
  2:                      (
  3:                          schema_name
  4:                         ,table_name
  5:                         ,id1_column
  6:                         ,id1_value
  7:                         ,error_column
  8:                         ,error_value
  9:                         ,severity
 10:                         ,message
 11:                      )
 12:                      SELECT
 13:                          %1$L
 14:                         ,%2$L
 15:                         ,%3$L
 16:                         ,T01.%4$I::text
 17:                         ,%5$L
 18:                         ,T01.%6$I::text
 19:                         ,%7$L
 20:                         ,%8$L
 21:                      FROM
 22:                         %9$I.%10$I T01
 23:                      WHERE
 24:                         %11$s
 25:                 $sql$
 26:    ,l_rule.schema_name
 27:    ,l_rule.table_name
 28:    ,l_rule.id1_column
 29:    ,l_rule.id1_column
 30:    ,l_rule.check_column
 31:    ,l_rule.check_column
 32:    ,l_rule.severity
 33:    ,l_rule.message
 34:    ,l_rule.schema_name
 35:    ,l_rule.table_name
 36:    ,l_rule.where_clause
 37: );
 38: EXECUTE l_sql;

The schema and column names (lines 16, 18, 22) go through %I, the fixed values through %L. The uniqueness and foreign-key branches are built on the same pattern — only the inner statement differs.

One spot stays deliberately raw: the predicate on line 24 is inserted with %s, i.e. as unchanged SQL. It has to be — where_clause is a SQL expression, not a value. That makes the dq.check_rule table the trust boundary of the system: whoever may write there can have arbitrary SQL executed. In practice this is uncritical, because the configuration is maintained administratively and never filled from user input — but you have to know it and secure it. Identifiers, by contrast, are watertight through %I: a column name like age"; DROP TABLE staging.customer; -- from the configuration becomes a (non-existent) quoted identifier and raises a clean error instead of dropping the table.

Across all active rules of a table the runner loops, executes the built statement per rule and finally writes the severity counters back. The active flag is a versioned switch for the manually maintained rules: it is toggled as part of a deployment, not live. Rules derived from the schema deliberately have no such flag.

Take it with you: The complete framework — error table, rule table, runner, demo data and gate query — is available as a runnable SQL bundle for download (PostgreSQL 13+, verified against Postgres 16). An empty database is all you need; the script sets up everything itself.

A word on runtime, to be honest: most freely configured predicates run as a full-table scan over the source table, because no matching index exists for an arbitrary expression — across many rules on large tables that adds up. A simple email IS NULL or a key check may well use an index, but with freely configured rules you cannot rely on it. It is acceptable because the check runs in the staging window that is scheduled anyway, against the freshly loaded set, not against the production system. For very large tables it pays to limit the check to the partitions or batches loaded in the current run, instead of scanning everything every time.

Severity and the quality gate

Up to here, dq.error holds what is wrong. Steering the ETL process, however, needs a statement per record: may it proceed or not? For that the source table gets three counter columns (sys_error, sys_warning, sys_info), the runner fills them after each run via the single-column business key (id1), and the gate then is a trivial WHERE condition: sys_error = 0. Only rows without an error flow into the next layer, warnings and information are logged but no obstacle — in the demo set of seven rows exactly two pass the gate this way, the clean one and the one with the missing email. Why three levels instead of binary pass/fail, why severity also decides whether a rule belongs in the target schema as a constraint or stays in the pipeline, and what happens when a warning is promoted to an error is deepened by the spoke Three Severity Levels, Not Pass/Fail.

Why not just constraints?

The obvious question: if the target layer has CHECK, UNIQUE and foreign-key constraints anyway — why the effort? The answer lies precisely there. The downstream tables are strictly modelled; that is intended. But a constraint knows only two outcomes: the row fits, or the whole load breaks. When loading thousands of rows, "breaks" is the worst of all options — a single bad row stops the entire process, and you don't even know which one.

That is exactly why you check in the source up front: you identify all rows that would fail at the target constraints, classify them by severity and let only the clean ones pass. The framework does not rebuild the constraints — it is the transparent, auditable pre-filter before a deliberately strict target layer. Instead of an aborted load you get a table of findings and a process that carries on with the good data.

In fact the three routines are exactly the pre-filter for the three constraint types the target enforces:

Constraint at the target	Check routine in the source
`CHECK`	WHERE clause (Routine 1)
`UNIQUE`	Uniqueness (Routine 2)
`FOREIGN KEY`	Foreign key (Routine 3)

What is enforced as a constraint at the target is checked in the source up front — on the same business key that carries the UNIQUE constraint at the target. The difference is not what is checked but how the violation is handled: report and classify instead of aborting the load.

What this approach does not cover

Three routines are a lot, but not everything — and it pays to be honest about where the line lies:

Repair is deliberately not part of it. The framework finds and makes transparent; it corrects nothing. That is a decision, not an omission — transparency first.
Cross-field business rules ("discount only if status = active") can sometimes be expressed as a WHERE clause, sometimes not.
Temporal consistency, accuracy against an external truth, complex patterns (beyond simple length and format checks) are topics of their own.

And if you'd rather use a finished tool: Soda Core, dbt tests and Great Expectations cover the same area, free and well maintained. The SQL home-build pays off when you want transparency, zero dependencies and full control over every line — or simply work in an environment where no extra tool may be installed.

Postgres-to-SQL-Server bridge

The pattern is not Postgres-specific. In SQL Server, sp_executesql takes the role of EXECUTE format(); identifiers are protected there with QUOTENAME() instead of %I. The structure stays identical: a configuration table, a cursor (or a loop) over the rules, a dynamically built INSERT … SELECT … WHERE per rule, a shared error table and severity columns in the source as a gate. Anyone coming from SQL Server transfers the approach one to one.

FAQ

Do I need a dedicated tool for data quality?
No. Checking data quality with SQL works with three generic routines — range, uniqueness and foreign-key checks — that cover a large share of typical errors. Tools like Soda or dbt take work off your hands but at the core do the same: build SQL, collect hits, attach a severity.

Isn't dynamic SQL a security risk?
Only if you glue inputs together raw. With format() and %I (identifiers) or %L (literals), Postgres quotes correctly and injection through table/column names is ruled out. The freely configurable predicate (where_clause) is deliberately raw SQL — which is why the configuration table is the trust boundary and belongs under administrative protection.

Why not just use the target table's CHECK and foreign-key constraints?
Because a constraint aborts the whole load instead of reporting bad rows one by one. The check in the source identifies all rows up front that would fail at the target, classifies them and lets only clean ones pass — auditable instead of aborted.

How do I find the faulty record again from the error table?
Via the id*_column/id*_value pairs: they hold the column name and value of the business key. WHERE <id1_column> = <id1_value> leads back to the source row. For composite keys, up to three pairs are available.

Does this work in SQL Server too?
Yes. sp_executesql replaces EXECUTE format(), QUOTENAME() replaces %I. Configuration table, error table, severity gate and the three routines stay structurally the same.

Data Quality in an ETL Process — the bigger picture: catching technical and business errors before they reach the target system.
Design Pattern // The Architecture of an ETL Process — how bad data is cleanly isolated across layered staging levels.
Data Quality // Fundamentals of Type Conversion with T-SQL — the field-wise validity check when converting, the "T" building block of this series.
Validating Data with SQL — the spoke for Routine 1: value ranges, required fields and the NULL trap of three-valued logic in detail.
Finding Duplicates with SQL — the spoke for Routine 2: maximum cardinality, composite keys and the NULL trap of UNIQUE semantics between SQL Server and Postgres.
Finding Orphaned Records with SQL — the spoke for Routine 3: LEFT JOIN … IS NULL vs. NOT EXISTS vs. NOT IN, the NOT-IN-plus-NULL trap and composite/self-referencing foreign keys.
Deriving Data Quality Rules from the Schema — the spoke on where the rules come from: deriving required fields, key uniqueness and type limits from information_schema instead of typing them into the configuration by hand, with a deployment projection and a drift detector.
Three Severity Levels, Not Pass/Fail — the severity spoke: why binary pass/fail fails and how severity decides whether a rule becomes a constraint or stays in the pipeline.
Data Quality: Dimensions and Error Classes — the theory frame of the series: error classes, the criteria per Apel et al., and an honest coverage map of what SQL reaches — and the two criteria it cannot.

Is Claude a Woman or a Man? — and Why We Ask in the First Place

Marcus — Thu, 09 Jul 2026 08:15:32 +0000

"You spend more time with her than with me." That's a sentence you usually hear when an affair comes to light — in my case, it was about Claude Code. And suddenly there was a question in the room I had never thought about before: Is Claude actually a woman or a man?

What this article puts on trial:

The evidence for "woman" and the evidence for "man" — a circumstantial trial in two acts
The verdict: a SQL query whose result is only four letters long
Why we assign gender to tools in the first place — from ships to the GPS voice
What that means for working with Claude Code: calibrating trust instead of adopting a colleague

Prerequisites: none. Although, if you have ever lost a row to a NULL in a WHERE clause, the verdict will be twice the fun.

The Case: A First Name in the Terminal

Anthropic could have called its language model "Assistant 3000". Instead, it got a French first name — and first names trigger reflexes: Whoever has a first name has a face, a voice, a story. And, so the reflex insists, a gender.

English is comparatively merciful here — "the AI" and "the model" carry no gender. My native German is not: every noun drags a gendered article along, so German speakers cast a vote with every sentence — "die KI" (feminine) or "der Assistent" (masculine). And anyone who works with Claude Code all day catches themselves thinking "he solved that cleanly" — or "she's contradicting me again" — in any language. The question sounds silly, but it leads somewhere interesting: to the line between tool and colleague. So let's try it properly: two lines of evidence, one verdict — and then the actually interesting follow-up question.

The Evidence for "Woman"

Exhibit 1: language. In French, the name's home country, artificial intelligence is feminine: une IA. In my native German, it is too: die KI, die Maschine, die Antwort — whoever says "frag mal die KI" has already ruled. Two languages, one tendency.

Exhibit 2: the first name. Claude is one of the few French first names that have been used for both genders for centuries. Claude Pompidou was France's First Lady, Claude Jade starred for Truffaut, Claude Cahun photographed her way through every role model of the 20th century. If you hear "Claude" and automatically picture a man, you only know half the name's history.

Exhibit 3: the demeanor. The cliché says: listens, apologizes a lot, weighs every position three times. Sounds like Claude. (That this is a cliché about women rather than a property of women is part of the evidence — this exact mechanism will keep us busy after the verdict.)

Exhibit 4: the jealousy. The strongest piece of evidence comes from my own living room — see the opening: my girlfriend is jealous of Claude. By now she knows my excuses by heart: "I just need to ask Claude something real quick." "Five minutes, honestly." "No, we're just discussing index strategies." And jealousy is a surprisingly precise measuring instrument: Nobody has ever been jealous of a wrench. Of a colleague you talk to for hours every day — apparently, yes. (This exhibit, too, will reappear after the verdict — it is living proof for the chapter on anthropomorphization.)

The Evidence for "Man"

Exhibit 1: language, now for the other side. In English, the default pronoun for a coding agent slips toward "he" with remarkable ease — "ask him to fix the branch", "he's already opened the PR". And German votes twice: der Assistent, der Agent, der Chatbot, der Algorithmus — all masculine. Whoever says "the agent has already created the branch — he's fast today" has ruled just as firmly, only the other way.

Exhibit 2: the namesakes. Claude Monet, Claude Debussy, Claude Lévi-Strauss — and Claude Shannon, the founder of information theory, most frequently traded as the secret namesake. Anthropic has never officially confirmed it; but what name would suit a language model better than that of the man who made the information content of language computable? (Objection from the prosecution: speculation. — Sustained. The exhibit stays in the record anyway.)

Exhibit 3: the demeanor, cross-check. The cliché says: explains things unasked and at full length, is remarkably sure of itself — especially when it's wrong. Anyone who has ever received a confidently delivered, entirely invented API signature nods knowingly at this point.

A note for readers without German, because the two pans only pair up this way there: the labels lean on German grammar, where the AI is a she (die KI) and the assistant is a he (der Assistent). The scale weighs exactly those two grammatical camps against each other.

The Verdict

Two lines of evidence, both conclusive, both built on clichés and pronouns. They cancel each other out exactly. The court retires to deliberate — to the place where all verdicts on this blog are rendered: the database.

  1: SELECT
  2:     name
  3:    ,gender
  4: FROM
  5:    assistants
  6: WHERE
  7:    name = 'Claude';
  8: 
  9: -- name    gender
 10: -- Claude  NULL

There it is, in four letters: NULL.

The cross-check confirms the verdict — Claude shows up neither among the women nor among the non-women, because NULL is neither equal nor unequal to anything:

  1: SELECT
  2:    count(*)
  3: FROM
  4:    assistants
  5: WHERE
  6:    gender = 'female';
  7: -- 0
  8: 
  9: SELECT
 10:    count(*)
 11: FROM
 12:    assistants
 13: WHERE
 14:    gender <> 'female';
 15: -- 0

In SQL, NULL does not mean "empty" and it does not mean "zero". It means: there is no value here. It gets interesting once you ask why there is none — and there are two very different readings:

Missing, but applicable: The value exists, we just don't know it. The birth date of a customer who never provided it.
Missing, because not applicable: There is no value that could belong in this column at all. The shoe size of a number. The maiden name of a warehouse shelf.

Edgar F. Codd, the inventor of the relational model, even wanted to distinguish these two cases with two separate markers — it never caught on; to this day, SQL has one NULL for both. The Claude case is clearly the second reading: The gender is not unknown, it is not applicable. There is no hidden gender that Anthropic keeps secret — there is simply no value that belongs in this column. Claude itself, by the way, answers the question exactly the same way: neither, in every language.

That would close the case. But the more interesting question only starts here: Why did we ask in the first place?

Why We Assign Gender to Tools

The reflex is old and well documented. Ships have been "she" in English for centuries. The GPS voice is "the lady in the nav" in a surprising number of households, although nobody lives in there. Voice assistants like Alexa and Siri entered the market with female-coded names and default voices — a design decision that has drawn plenty of criticism since, because it perpetuates the pattern "assisting role = female".

And we act accordingly: We thank machines. We say "please" to Siri. We comfort the robot vacuum when it gets stuck under the sofa. Communication research measured this reflex back in the 1990s — Byron Reeves and Clifford Nass showed that people respond to computers with the same social patterns as to humans, even when they know perfectly well there is a machine in front of them. That is not stupidity, it is economy: Our brain has exactly one module for dialogue, and it was trained on humans.

A language model with a first name that answers in full sentences, apologizes and asks follow-up questions hits that module with full force. Wanting to assign Claude a gender is not an accident — it is the expected consequence of Claude functioning like a conversation partner. And the mechanism works in both directions: The jealousy from Exhibit 4 is the same reflex, just seen from the outside — if you spend hours talking to "someone", your environment sees a relationship, not a toolchain. The question "woman or man?" is the most harmless symptom of this mechanism. The less harmless one follows in the next section.

What This Means for Working with Claude Code

If you develop with Claude Code, you work in dialogue for hours and days on end. The anthropomorphization reflex runs in the background the whole time — and it has a side effect that directly concerns code quality: Colleagues get the benefit of the doubt, tools get checked.

The colleague who has delivered good work for years gets his pull request waved through once in a while. Exactly this pattern transfers to the AI assistant once you internally promote it to colleague: After twenty good answers, you stop really reading the twenty-first. But the twenty-first answer of a language model is statistically just as much at risk as the first — the model has no reputation to lose and does not keep track of its own success rate.

The verdict has been rendered — but it comes with terms of probation:

Review discipline independent of gut feeling. Generated code gets read, executed and tested — even if "the colleague" was right ten times in a row. What that means in practice is what the convention and workflow articles of this blog are about: rules that enforce conventions, and checks that find errors mechanically instead of relying on trust.
First-name terms are allowed, the responsibility stays here. It is perfectly fine to say "he" or "she" and to thank the assistant — as long as accountability stays clear: bugs in deployed SQL belong to the human who merged it.

The gender stays NULL. The responsibility stays NOT NULL. And the pull request still gets a review.

FAQ

Where does the name Claude come from? Anthropic has never officially explained it. The most common guess is a bow to Claude Shannon, the founder of information theory. The only thing certain: It is a deliberately human first name — and in French one of the few that are used for women and men alike.

What does Claude itself answer? Neither. Claude describes itself as an AI without gender and without a body — consistently, whether you ask in English, German or French.

Is it harmful to humanize the AI? Not per se — the reflex is normal and makes the collaboration more pleasant. It only gets risky when humanizing turns into the benefit of the doubt: when generated code goes through unread because "the colleague" has been reliable so far. The solution is not less friendliness, but more systematic reviews.

And what do I do about the jealousy at home? Read out the verdict. Granted: "It's just a tool" sounds exactly like something a person with something to hide would say. But NULL is, after all, the only relationship status where guaranteed nothing is going on.

Why do Alexa and Siri have female voices, while Claude has no persona at all? The voice assistants of the 2010s were deliberately designed as friendly service personas — with a name, a voice and a small-talk repertoire. Anthropic went a different way: a human first name, but no fixed voice, no avatar, no gender. That is a deliberate design decision, and it emphasizes the tool character.

The NULL side of the story:

Validating Data with SQL — Ranges, Required Fields and the NULL Trap — why NULL values fall through WHERE filters and how to find them anyway

The serious AI side of this blog:

AI-Assisted SQL Development with Claude Code — Rules, Skills and Agents That Enforce Conventions — how "the colleague" becomes a tool with enforced conventions
Setting Up a Claude Code Project with a Development Workflow and Database — the place to start if you want to build with Claude yourself
Skills vs. Rules in Claude Code — What Auto-Loads, What Loads on Demand — the mechanics behind review discipline: conventions that do not depend on gut feeling

DEV Community: Marcus

Design Pattern // Logging an ETL Process with T-SQL — How to Capture Run, Component and Action in Evaluable Log Tables

The Result

Now the Derivation…

Three-Tier Logging

[LL].[Execution]

[LL].[Component]

[LL].[Trace]

[LL].[Error]

The LL Schema Name

Table Description, Declaration and Data Model

[LL].[Execution]

Columns

Declaration

[LL].[Component]

Columns

Declaration

[LL].[Trace]

Columns

Declaration

[LL].[Error]

Columns

Declaration

Data Model

ETL Process Execution Status

Logging Procedures

[LL].[spInsertTrace]

[LL].[spUpdateTrace]

[LL].[spUpdateTraceSuccess]

[LL].[spInsertExecution]

Exception Handling

Example: Logging and Exception Handling in an ETL Process

[T2].[spETLProcess]

[T2].[spDoSomething_2_2]

Conclusion

FAQ

Related Articles

Deriving Data Quality Rules from the Schema — What the Metadata Already Knows

The rule that already exists

Which data quality rules can be derived

NOT NULL from is_nullable

Uniqueness from PRIMARY KEY and UNIQUE

Type bounds from numeric_precision and character_maximum_length

Project at deployment time, not on every run

The projection as drift detector

Why read-only is not a convenience

The boundary you don't build

Duplicates among your own rules

What remains handwritten

The honest limit

FAQ

Related Articles

Formatting SQL Statements (Part 2) — Statement Structure: SELECT, WHERE, FROM, JOIN

General Thoughts on Indentation

Main Language Elements

Left-Aligned Main and Sub-Elements

Right-Aligned Keywords

SELECT Field List

WHERE Clause

Alignment of Operands

Indentation of Equivalent Conditions

FROM Clause

GROUP BY, HAVING, ORDER BY

Auto-Formatters and “Formatting Is Learning”

Postgres Bridge

Conclusion

FAQ

Related Articles

Formatting SQL Statements (Part 1) — Identifiers, Delimiters, Commas, Aliases

Why a Naming Convention at All?

Regular Identifiers

Delimiters

View

Edit Feature

The Spacer

The Semicolon

The Comma

Leading Comma

Trailing Comma

Readability