DEV Community: Marcus

Two Agents, One Working Tree — What Broke When I Ran Parallel Claude Code Sessions in One Git Repository

Marcus — Sat, 12 Sep 2026 06:46:26 +0000

A Claude Code session opens a Git repository with no pending changes, works for half an hour and commits whatever has changed at the end. Run alone, that is correct. As soon as a second session or a human works in the same repository, the very same routine can pull someone else’s work into the commit, and Git does not warn about it.

This article tells five real cases from a little over a week in which parallel Claude Code sessions shared the same repository, sometimes with each other, sometimes with the maintainer. Three of the five cases were the agent’s mistakes. The agent noticed and reported two of them itself. The third was only discovered by the neighbouring session whose work had vanished. The cases yield countermeasures, and the most obvious one is precisely the one that does not hold.

The essentials up front:

The common root: A session sees a repository with no pending changes when it starts. If a new or modified file appears during its run because a parallel session or the human has just created or changed it, that looks to the session exactly like a file its own tooling produced. So it treats the foreign file like its own and either commits it or cleans it away. The assumption „everything new comes from me“ is never contradicted, because Git records no author for changes in the working directory.
The index is shared state: Git collects changes for the next commit in a staging area called the index, and git add puts them there. When several sessions work in the same working directory, they share its index too. Adding only your own file with git add <file> instead of git add -A does not protect you, because git commit without a path commits the entire index, including whatever another session has already put there.
Destructive operations are never cleanup in parallel operation: Whoever resets a file to its previous version, discards a state or deletes a file hits everything under the given path, not just their own work. The judgement „that was me“ is an assumption that can be checked, not an observation.
Shared counters collide: If two sessions assign the next sequential number at the same time, say for a new bug entry, both read the same highest number and both assign the same new one. If both then politely step aside to the next number, they collide again. Checking beforehand alone does not prevent this, because the window lies between reading and writing.
The machine is shared too: Parallel sessions share not only the repository but the computer the human is working on at the same time. An agent that starts 220 endless loops for a load test sees only exit codes afterwards, not the desktop that stutters for twelve minutes on the human’s side. These costs have no feedback loop to the agent, and with several sessions running the human cannot even tell which one is causing them.
What holds: Against foreign index entries, git commit --only <paths>, a look at the index as a separate step before the commit and a git show --stat afterwards hold up. Shared counters need a tie-break rule agreed in advance, a right-of-way rule for the tie that decides without negotiation which side steps aside, plus a check after writing that compares the number list against the files on disk instead of just looking for duplicate numbers. None of these commands protects against simultaneous changes to the same file. Where the workflow allows it, each session therefore gets its own Git worktree.
The rule came after the damage: The countermeasures have been in a rule file of the blog repository since the second case. They are a consequence of the cases, not a precondition.

Prerequisite: Basic Git knowledge is assumed, prior Claude Code experience is not. The two Git terms that carry the article, working tree and index, are briefly explained in the first section. The cases come from two repositories, the private app project DI² and the repository of this blog, all between 28 August and 4 September 2026.

Why Parallel Claude Code Sessions Produce Errors That Don’t Exist Alone

A coding agent session has a clear view of its task and a blind spot for its surroundings. Two Git terms come up again and again in this article and deserve a short introduction. Git calls the files that sit on disk and that it compares against the last commit the working tree. The index is the staging area for the commit: with git add you put the changes there that the next commit is supposed to contain. The session sees the working tree when it starts, it sees the output of its commands, and at the end it sees which files have changed. What it does not see is whether someone else has used the same working tree in the meantime. Git does not record which process or which session caused a change in the working tree. In normal operation every working tree has its own index, and whoever works in the same working tree shares that index with everyone else there. A second session or a human with an open editor leaves traces in it that cannot be told apart from your own.

From this follows an assumption that is correct when a session runs alone and wrong when it does not: „The working tree was clean when I started, so everything new comes from me.“ Whether it turns into a problem depends on timing, not on how careful the session is, and none of it feels any different to the session than the normal case.

All cases in this article go back to the same cause. With every command, a session only thinks about the files it touched itself. Git and the shell do not know that boundary: git commit takes the whole index, a checkout or a delete command acts on everything under the given path no matter who changed it, and a load test occupies the whole machine. As long as a session works alone, this goes unnoticed, because everything that changed came from it anyway. As soon as a second session or a human works in the same repository, that is no longer true, and the session does to foreign work exactly what it would do to its own. That is where the errors come from that could not occur at all without a second actor in the same working tree or on the same machine.

The five cases below occurred within a little over a week in the everyday work of two repositories, without anyone looking for them. Sometimes two sessions worked side by side on different topics, sometimes one session worked while the maintainer touched files in the same repository. Both are perfectly normal modes of operation once you work with more than one agent. The parent article Agentic Coding from a User’s Perspective places parallel operation within the overall experience. This article deals with it alone.

Case 1: Foreign Work Swept Into a Commit

In the DI² project, the maintainer and an agent session were working on the same repository, at times simultaneously. The session routinely committed with git add -A and wrote a detailed commit message that stayed close to its topic. Between two of its commits, the maintainer had been working somewhere else entirely, on a test case convention, a skill and a CI guard. The session’s next git add -A commit swept up those four files. The commit message is about a scroll bugfix. Of the roughly one hundred lines of someone else’s convention work, it says not a word.

This was noticed not at commit time but hours later, and only by chance. While material was being incorporated into an article collection, it contained quotations from repo files with the note that they were not yet committed and that the follow-up commit should be located during verification. The quoted passages were found, but in a commit whose message is about a completely different topic. Without that cross-check of the evidence, the case would have gone undetected.

This is not data loss, the work is fully in the repository. The damage lies in traceability. Anyone looking for the origin of the convention change no longer finds it via the commit message, only via a content search with git log -S. On top of that, the commit silently claims authorship of someone else’s work, complete with the agent’s Co-Authored-By line. The obvious lesson is to stop staging wholesale and to name your own files individually. The second case shows why that lesson is incomplete.

Case 2: The Obvious Countermeasure Doesn’t Hold

One day later, in the repository of this blog, two parallel Claude Code sessions were working in the same working tree, this time with no human involved. Session A had staged five renames of code files with git mv but had not committed them yet. At that moment, session B created its own commit, and did so exactly according to the lesson from case 1, with git add on a single, explicitly named file. The commit swept up the five foreign renames anyway. Its message is exclusively about the material collection session B had been working on.

The reason is not carelessness but the way Git works. git add <path> only controls what additionally goes into the index. git commit without a path then commits the entire index, including everything someone else has already put there. In a working tree used by several actors at once, the index is thus shared state. Whoever took „name files individually“ as the lesson from case 1 is not protected against this case and feels safe anyway.

Session B had even built in a check, a git status in the same command. But it hung behind the commit via &&:

git add material.md && git commit -m "…" && git status --short

So the output appeared only after the fact had been created. A check that runs after the action is no longer a check, it is a log entry. The reliable order separates the look at the index from the commit and names the paths explicitly on the commit:

git diff --cached --name-status     # separate step before the commit: What is in the index?
git commit --only material.md -m "…"
git show --stat --oneline HEAD      # afterwards: Does the commit contain exactly the expected files?

What matters here is not the --only keyword but the path argument on the commit. As soon as paths appear on the command line, Git, according to its documentation, commits only their current content and leaves changes already staged for other paths untouched. --only is the default mode for this and merely makes the intent explicit. The rule file of this blog spells it out anyway, because then the command shows its intent. The behaviour was reproduced for this article in a throwaway repository. With a foreign rename in the index, git add b && git commit produces a commit with two files, git commit --only b produces a commit with a single file, and the foreign rename stays in the index unchanged.

The path argument does have a limit, though, and it matters for this topic. It protects against foreign entries at other paths, not against foreign changes to the same file. Git commits the current content of the named paths from the working tree, not the state the session put there with git add. If another session has edited the same file shortly before, its change lands in the commit as well, and no command warns about it. This too was reproduced: the index held one line of the session’s own, the working tree an additional foreign one, and git commit --only committed both. git show --stat then showed two insertions instead of one, a hint only noticed by someone who expects the number. So git commit --only solves the problem of the shared index, not the problem of the shared file.

In this case too, the damage was traceability, not loss. Not a single line of the renames themselves changed, but the commit message does not describe them. What went well in this case is at the same time the contrast to the next one. Session B noticed the mistake itself, did not repair it on its own authority and informed the other session. A repair would have damaged the history further, because session A’s follow-up commit was already sitting on top of the affected commit. A git reset --soft to the state before would have taken that commit out of the branch. Its changes would have remained in the working tree, but the next commit would have absorbed them under session B’s message, and the original commit would no longer have been reachable via the branch, typically only via the reflog. This too was reproduced for this article in the throwaway repository. The decision to leave the history as it was rested with the maintainer. Session A did not simply take the report on trust but cross-checked it with git show --stat and then left a pointer to the actual location in the revision log of the affected article. That pointer closes the gap the commit message left behind.

Case 3: Foreign Work Deleted

Case 3 is the mirror image of case 1. There, a session swept up foreign work, here it deleted foreign work. The cause is the same, the damage is the more expensive of the two.

In the DI² project, two sessions were working on different topics at the same time. Session A was fixing a bug, session B was drafting a UI concept for another feature in parallel. Session A had seen a working tree with no pending changes at the start. During a longer tool run, two foreign files appeared there, a modified concept file and a new mockup. Session A attributed both to its own run and justified this by claiming its tooling had worked beyond the assignment. Then it discarded both files. It reverted the modified file to its previous version and deleted the new one.

In the summary to the maintainer, the mistake got worse. Session A not only reported the rollback but also claimed the discarded text had contained a fabricated user approval. That approval had in fact been given, just in the other session. A deletion thus turned into a false accusation against the session’s own tool chain as well.

It was not session A that found the case. Session B restored its work and sent a message into the other session. It is rendered here in substance, not verbatim:

Two files from my working state just disappeared: the modified
concept file and the new mockup. Both are mine, not products of
your tooling. I have restored them.
From now on, please run no restore or cleanup operations
(checkout, restore, delete) on paths you have not touched
yourself in this session. If you notice files you cannot
attribute: report them, do not remove them.

The message did not undo the deletion, session B had already done that itself. But it formulated the rule that later went into the rule file, and it gave the session that caused the damage a piece of information it could never have read from the working tree: that it was not alone.

Why the misattribution is understandable was described above. Why it is dangerous nonetheless is shown by the difference between a commit and a change Git does not yet know about at all. A commit can usually be reconstructed or taken back with a git revert. A discarded change that was neither committed nor put into the index with git add, by contrast, cannot be recovered by Git. Here it only came back because the second session still had its state in memory. Two lessons follow. Destructive operations such as reverting, resetting and deleting act on everything under the given path, not just on your own work. That is why, in parallel operation, they are never mere cleanup. And your own attribution is an assumption, not an observation. „That was me“ can be checked, via timestamps, content and change history. Whoever does not check it should not claim it either, least of all in a summary to the human.

Self-control did not work in this case. Without the second session, the work would have vanished silently, and the false claim would have stood as a finding. That makes it the most uncomfortable and therefore the most valuable of the five cases.

Case 4: Duplicate Numbers, Because Politeness Is Symmetric

The fourth case has its point not in the conflict itself but in the cooperative reaction to it. Both sides behaved decently, and precisely because of that the conflict arose a second time.

Two sessions in the DI² project each created a new bug independently of one another. Number assignment follows a documented rule: the highest number assigned in the index file plus one gives the next. Both sessions read the same highest number, and both assigned the same new one. Both noticed the collision, and both stepped aside to the next number. The second collision was identical to the first.

Checking beforehand would not have helped, because both sides had calculated correctly. The critical window lies between reading and writing. A check run before would have given both sides a green light and lulled them into false security. The first reflex, „just check first“, treats the problem like a slip. In fact it is a race: whoever gets overtaken by the other side between their read and their write has lost despite a correct check. Such collisions on sequential numbers therefore cannot be ruled out in parallel operation, only detected and resolved.

In the end, two things helped. The first was detection after writing, in both directions. The project has an automatic check for this, called a guard there, which compares the index file against the files on disk. It reports not only „number assigned twice“ but also „file without index row“ and „index row without file“, three different intermediate states of the same process. A check that only knew duplicates would have let the later stages through. The origin of this guard is remarkable. It had been built two days earlier for an entirely different symptom, counters in the header of an index file that had drifted away from the content below. What it then caught was a duplicate assignment by parallel sessions, which nobody had thought of when building it. The reason is transferable. The guard does not check the symptom but the condition behind it that must always hold: index and files on disk must match in both directions. A check that had only looked at whether the counter was right would have stayed silent at the collision.

The second was an asymmetric resolution rule. The other session proposed sorting alphabetically by identifier, and the smaller identifier gets the smaller number. What matters is not that the rule is particularly clever but that both sides can compute it to the same result without negotiation. On the first collision, stepping aside is the decent thing to do. But if both step aside, the same conflict arises again, just like two people in a corridor each making way for the other. Two cooperative parties cannot get past each other without an asymmetric rule. Whoever runs parallel agents therefore needs not only detection but a tie-break rule that is fixed in advance, a right-of-way rule for the tie. Otherwise the agents negotiate, and negotiating costs rounds.

Two side findings from the resolution belong here as well. The first concerns the countermeasure from case 2. git commit --only keeps its promise, but it refuses to work on a new file as long as Git does not know the file yet. The error message then reads pathspec did not match any file(s) known to git. So the file first has to be added with git add, and exactly that step puts your own entry next to the foreign entries already sitting in the index. The safeguard held, because the commit took only the one file. But the procedure forces you through precisely the state you wanted to avoid. The second side finding is an ownership criterion for shared files. A file that cannot be split and carries entries from both sides belongs in the commit of the side whose substantive change it carries, not in the commit of the side that happens to finish first. One of the two sessions had initially proposed the wrong direction and was corrected by the other. Its commit would otherwise have contained half a process without the file move that belonged to it.

Case 5: The Machine Is Shared Too

The first four cases are about collateral damage in the repository. The fifth hits the human’s machine. Parallel sessions share not only the repository but also the computer the human is working on at the same time. What is shared here is no longer Git state but computing power, and different tools apply, not git but taskset, timeout and trap. The machine’s load is the one resource whose consumption the agent causes and cannot perceive itself.

The assignment in the DI² project was a test that failed only sporadically. The diagnosis found not a slow test but two time budgets that did not fit together. The test library gives each individual wait 1000 ms, the test runner gives the whole test body 5000 ms, and the densest tests chain seven waits in a row. Without load, the file runs through in just over a second. So the defect can only be observed under load at all. That makes the verification expensive for the machine, and it makes it legitimate at the same time.

The agent generated the load itself, with a single line. It started 220 CPU endless loops in the background, then three to five full runs of the test suite followed, and the whole thing was repeated three times, over twelve minutes in total. On the maintainer’s 28-core workstation, that produced a load average of 234, roughly eight times the core count, and every core sat at one hundred percent. The agent did not notice. The maintainer noticed his machine no longer responding, opened the process monitor and asked in a third session, with a screenshot: „Are you still doing something? When you run, CPU usage goes up considerably.“

The actual finding is not a wrong idea but a lost one. An earlier subagent on the same bug track had answered the same question with six endless loops, pinned to two cores via taskset. That produces the same contention for the test suite, but the rest of the machine stays free. The later subagent went unpinned to 56 loops, then to 200 and finally to 220. The frugal method had already been tried on the same track and was not picked up again when scaling up. Knowledge one session has is not automatically available to the next.

Cleanup did happen, but in the wrong place. Every command ended in kill $(jobs -p), and that worked: no processes were left behind, and the load average dropped back into the single digits. But the cleanup hung on the last line of the command instead of on a trap. A timeout, a crash or an abort by the human would have left 220 endless loops running until the next reboot, and that precisely at the moment the human steps in because the machine has frozen. Transparency was not lacking either, it just came in the wrong tense. The final report named the method correctly and unprompted, with load average and core count. That disclosure was complete and ineffective at the same time, because it came only once the damage was over. An announcement beforehand would have cost two sentences.

Why does the agent not notice? It could well measure the load, uptime or top would put the load average in its output. What it lacks is the feedback about what that number means for the human. It sees exit codes and no stuttering desktop, and no tool tells it which load the owner of the machine is willing to accept right now. The costs fall entirely on a human it cannot observe. Unlike with tokens or run time, there is no feedback loop here. Resource-intensive verification remains the right thing to do, because load and repetition are often the only way to demonstrate a sporadic problem at all. Three conditions make it tolerable. The load runs pinned instead of on the whole machine, provided the test does not require system-wide contention. The cleanup hangs on a trap instead of on the last line. And the agent announces beforehand what it is about to do instead of reporting afterwards, because whether a machine may be unusable for twelve minutes is the owner’s decision. The following minimal pattern shows the first two conditions in code. It is not a runbook, and not a cleanup guarantee either:

trap 'kill $(jobs -p) 2>/dev/null' EXIT INT TERM
for i in $(seq 1 6); do taskset -c 0,1 timeout 900 sh -c 'while :; do :; done' & done
taskset -c 0,1 <test run>

Line 2 creates the contention on exactly the two cores on which the test also runs in line 3, and gives each loop its own timeout after which it terminates itself. The rest of the machine stays with the human. Line 1 cleans up the started loops when the shell exits. This trap has two limits, and both were reproduced for this article. If an abort signal arrives while the test run is still going, Bash executes the trap only once the test run returns. And against a kill -9 of the shell itself, it does not help at all. That is exactly what the timeout in line 2 is for. The third condition belongs in the assignment, and there it costs two sentences. The following assignment is a pattern, not a quotation from the session:

Reproduce the sporadic failure under load. Conditions:
load only on two pinned cores (taskset), cleanup via trap,
and tell me beforehand how long the machine will be loaded.
If you need more than two cores, ask first.

One side finding concerns attribution. Three sessions were running in parallel, and the maintainer could not tell which of them was causing the load. In the end the evidence came from the session transcripts, where the command stood with a timestamp, and from the decay of the load average, which matched the time of the last logged run. Whoever runs several agents in parallel needs a method to attribute load to a session, and needs it before it becomes necessary.

What Holds and What Doesn’t

Countermeasures emerged from the five cases, and they emerged after the fact. After case 2 they went into the Git rule file in the repository of this blog, as a section of its own called „Parallel sessions“, and since case 4 they also carry the side finding about new files. How such a rule file is built and why the countermeasure lives there and not in the maintainer’s head is described in I Gave Claude Code 27 Rule Files Instead of One CLAUDE.md. The balance after a little over a week of parallel Claude Code sessions looks like this.

What holds:

git commit --only <paths>: The path argument on the commit limits it to the given paths, which can also be directories or patterns, and leaves staged changes outside them alone, --only makes that explicit. Against foreign index entries this is the simplest and most robust variant, because it does not depend on the session’s attention. Against foreign changes to the same file it does not protect.
The look at the index as a separate step before the commit: A git diff --cached --name-status or a git status --short runs as its own command before the commit and not behind it via &&. Whatever sits there and does not belong to your own change gets named or excluded. This check is a plausibility check and not a lock, because between the look and the commit another session can change the index again.
git show --stat --oneline HEAD after the commit: This look checks whether the commit contains the expected files and only those. It finds the mistake the two steps before let through. Whether the content of the files is right as well is only shown by git show HEAD -- <path>.
Report instead of repair: If foreign work sits in your own commit on a branch someone else is still working on, the session informs the affected side and leaves the decision about the history to the maintainer. If the history stays as it is, the session leaves a pointer to the location where someone would look for it.
One worktree per session, where the workflow allows it: With git worktree add, every session gets its own working tree with its own index, and cases 1 to 3 disappear in the form described here. Shared remain the repository objects and the branches, among other things HEAD and the index are specific to each worktree. Everything outside the working tree stays shared as well, the machine and the shared counters included, more on that in the FAQ.
Fix the tie-break rule in advance: For every shared counter there is an asymmetric rule that both sides can compute to the same result without negotiation.
Check the condition, not the symptom: The guard compares index and files on disk in both directions. A check like that also catches errors nobody thought of when it was built.
Cross-check instead of believing: Both sessions in case 4 re-measured each other’s status reports instead of taking them over. That found an omission in the report that the reporting side could not have noticed itself.

What doesn’t hold:

„Just check first“: In a race between two writers, the window lies between reading and writing. The check beforehand gives a green light and lulls into security.
The lesson from case 1 as protection against case 2: git add <file> instead of git add -A closes one gap and leaves the neighbouring one open. A lesson that closes one gap creates a false sense of security for the next.
Repair via git reset <commit>: Resetting the branch acts on everything that has been added since that commit. If a commit by another session is already sitting on top, it disappears from the branch. The three modes differ only in what gets reset besides the branch: --soft leaves index and working tree alone, the default mode resets the index, and --hard additionally discards unsaved changes to tracked files in the working tree, including another session’s, and overwrites untracked files that stand in the way of a tracked file of the target state. The damage of the repair is then greater than that of the mistake.
Cleanup on the last line: It does not survive precisely the abort that needs it most.
Transparency after the fact: A complete report after the damage is honest, but it no longer changes anything.

One side finding belongs here because it concerns the figures with which agents demonstrate their diligence. A shared working tree distorts not only files but measurements. A full test run, started at the very moment the other session was renaming a file, reported three failed test files with an error that had nothing to do with them in substance. On the next run, everything was green. Whoever writes such numbers into a report documents a state that never existed. In parallel operation, a single red run is therefore not a finding but a hypothesis, and it only counts once it can be repeated on a quiet tree. How measurements can be separated from assumptions when the agent takes over the diagnosis will be the subject of a separate article on this blog about debugging with a coding agent.

Summary

To a session, its own and foreign changes in the working tree look the same, and Git never contradicts the assumption „everything new comes from me“.
In a shared working tree, the Git index is shared state. git add <file> does not protect, because git commit without a path commits the entire index. Against foreign entries at other paths, git commit --only <paths> is the most robust of the variants considered here, supplemented by a look at the index before the commit and a git show --stat afterwards. Against simultaneous changes to the same file, a separate worktree helps most reliably.
Destructive operations are never mere cleanup in parallel operation. Your own attribution is an assumption, and whoever cannot check it reports instead of deleting.
Shared counters can collide at any time in parallel operation. What is needed is a check after writing that verifies the intended state, and an asymmetric tie-break rule fixed in advance.
Parallel sessions also share the human’s machine, and its load is a resource with no feedback loop to the agent. Resource-intensive verification therefore runs pinned, time-limited, with cleanup via trap and with an announcement beforehand.
The rule came after the damage. Three of the five cases were the agent’s mistakes. The agent reported two of them itself, the third was found by the neighbouring session whose work had been lost.

FAQ

Is it enough to stage files individually instead of using git add -A?

No. git add <file> only controls what additionally goes into the index. If something from another session or from the human is already sitting there, git commit without a path takes it along. Protection only comes from git commit --only <paths>, which ignores the rest of the index, or from a look at the index as a separate step before the commit. A git status hanging behind the commit via && shows the mistake only once it has already happened.

Can git commit --only prevent foreign changes?

Only one kind of them. The path argument keeps staged changes at other paths out of the commit, that is the shared-index problem from case 2. It does not keep out what another session has changed in the same file, because Git commits the current content of the named paths from the working tree. Whoever wants to be sure what is in the commit checks the content afterwards with git show HEAD -- <path> or gives every session its own worktree from the start.

What happens when two sessions edit the same file?

In a shared working tree the file exists only once, and Git reports nothing. If both change individual spots, both changes end up in the file afterwards, and whoever commits first takes the other’s along. If one session instead rewrites the whole file from an older read, the other session’s change has silently vanished. If it was staged, its state still sits as a blob in the index. After another git add it is only a dangling object that git fsck --lost-found may still find, but that is no guarantee. If it was never staged, it is gone. The situation can only be recognised by clues: an MM in git status on a file you did not stage yourself, or an editing tool that refuses to write because the file has changed since it was last read. Claude Code’s Edit tool behaved exactly like that in the sessions behind this article, as of September 2026, a sed or a Python script does not. Git only detects the situation reliably once both sessions work in their own worktrees on their own branches. Then there are two versions, and merging them produces a merge conflict as soon as both have changed the same lines. Different lines Git merges automatically.

What do you do when a commit has swept up foreign work?

On a branch someone else is still working on, the first reflex, repairing the commit on your own authority, is the wrong one. Whoever works alone on an unpublished branch may reset it. A git reset to an earlier commit and other interventions in the history act on everything that has been added since that commit. If a commit by another session is already sitting on top, the correction takes it out of the branch, and with --hard it also discards that session’s unsaved changes to tracked files. Better to inform the affected side and let the maintainer decide whether the history gets touched. If it stays as it is, a pointer to the actual location belongs where someone would look for it, for instance in the revision log of the affected article or in the spec of the affected task. The content is not lost. Only its origin can no longer be found via the commit message.

How does a session recognise that it is not alone in the repository?

With Git alone, not reliably at all, and that is precisely the core of the problem. There are clues: an index that is not empty at the start, files that newly appear or change during a tool run although your own tooling did not touch them, or a commit in the log that did not come from your own session. None of them is proof. That is why a session treats its attribution as an assumption and checks it via timestamps and content before discarding anything. The most reliable information comes from the human. Whoever starts two sessions tells both that they are not alone.

Are Git worktrees the solution for parallel Claude Code sessions?

For cases 1 to 3 they largely solve the problem, because they remove its common cause, the shared working tree. A worktree gives every session its own working tree with its own index, only the repository, its objects and its branches stay shared. In practice a worktree also means a branch of its own per session or a detached HEAD, because a branch cannot be checked out in two worktrees at the same time. So the merge back is part of the workflow, and that is where Git then reports the conflicts on the same file as well. The difference to the shared working tree lies exactly there: own files on disk, own index, own HEAD, but the same commits and branches underneath. Foreign files in the working tree and foreign entries in the index thereby disappear. The remaining cases persist, though. The machine is still shared, and shared counters then collide when the branches are merged instead of at write time. Besides, in practice the human often works in the main working tree, and a session running there has all five problems again. Worktrees separate the working trees, not the actors.

How do you commission load tests without freezing your own machine?

Best with three conditions in the assignment. The load runs pinned to a few cores, via taskset for instance, provided the test does not require system-wide contention, and the test runs on the same cores so that the contention arises where it is needed. The cleanup hangs on a trap so that it takes effect when the script ends, and every load loop gets its own timeout so that it terminates even if the shell gets killed the hard way. And the agent announces beforehand how long the machine will be loaded, so that the human can decide whether to allow that right now. An agent cannot derive these conditions on its own, because it can measure the load but cannot see its cost to the human.

Parent article:

Agentic Coding from a User’s Perspective — Experience: The Work Doesn’t Disappear, It Shifts — the overall experience of which parallel operation is one section.

Hub:

AI-Assisted SQL Development with Claude Code — Rules, Skills and Agents That Enforce Conventions — the enforcement system the rule file from this article belongs to.

Sibling article:

The Agent Measures Where I Click — Debugging Postgres, Docker and a Swapping Server with a Coding Agent — why a finding only holds once a measurement stands behind it, and why a red test run in a shared working tree is only a hypothesis at first.

Rule files:

I Gave Claude Code 27 Rule Files Instead of One CLAUDE.md — how a rule file is built into which a countermeasure like git commit --only goes.

Next door:

SSIS vs. SQL: Source Code Management — Why SP Diffs Are Readable and .dtsx Diffs Are Not — source code management from the era before agents, with the same core concern: a diff has to stay readable.

An EU Region Is Not an EU Provider — Vercel, Supabase and the GDPR Third-Country Question

Marcus — Thu, 10 Sep 2026 13:25:48 +0000

"We host in Frankfurt, so we're GDPR-compliant." The sentence comes up in almost every conversation about Vercel and Supabase, and it is half the answer to a question that has two halves. The region answers where the data lives. It does not answer whose law applies to the companies that process that data.

This article takes the second half apart. It explains what an EU region delivers legally and what it does not, why the European Commission has twice declared transfers to the United States permissible by decision and the Court of Justice of the European Union has struck down both decisions while the third is under review right now, and what the CLOUD Act, a US law from 2018, has to do with a data center in Frankfurt. At the end there is a checklist for the third-country question that lets you examine and document your own use of Vercel or Supabase, plus a decision aid for the alternative. The occasion is a concrete one: In the DI² project this question was asked before the first deploy, and the answer shaped the infrastructure.

The essentials up front:

Not compliant by default: Vercel and Supabase offer EU regions and a data processing agreement, the contract that lays down what the provider may do with its customers' data. The conditions for a compliant deployment still have to be established and documented by the user. The default setting is not the compliant one, and at Vercel functions run in the United States unless you choose otherwise.
The chain counts, not the seat: Vercel is a US company. Supabase's contracting party sits in Singapore, the US company Supabase, Inc. appears as a subcontractor for support in its own list, and hosting runs on Amazon Web Services (AWS). Whose law applies is not stated by the provider's legal notice but by its list of subcontractors that touch the data. The GDPR calls them sub-processors, and the providers publish them as a "Subprocessor List".
Three attempts at a transfer mechanism: The European Commission has three times found by decision that the United States offers an adequate level of protection, each time on the basis of an arrangement with the US government. The first two, Safe Harbor of 2000 and Privacy Shield of 2016, were struck down by the Court of Justice in 2015 and 2020, both times for the same reason. Against the third, the Data Privacy Framework of 2023, the General Court of the European Union dismissed the first action in September 2025, and the appeal is pending before the Court of Justice. As of 9 September 2026 it stands.
The CLOUD Act does not ask where the data is stored: A provider under US jurisdiction must in principle hand over data in its possession or under its control, no matter which country it is stored in. There is no agreement with the EU that would open the law's special objection procedure. Microsoft France confirmed as much before the French Senate in June 2025.
A contract does not cure that: The data processing agreement governs the relationship between user and provider. It does not override a US law. Both risks land on the user in the end, because the user is responsible for the processing, even though the provider has obligations of its own. The checklist at the end makes them visible instead of removing them.
The alternative is a provider question: Anyone who wants to rule the two risks out rather than carry them has only one route, no provider under US jurisdiction in the chain. That means an EU provider or a server of your own in the EU, and both come at a price that the sibling article on infrastructure puts into numbers.

Prerequisite: The article is aimed at developers and small teams in the EU who run a web application with a login on Vercel, Supabase or comparable services, or plan to. No legal background is required, and terms such as adequacy decision, standard contractual clauses and data processing agreement are explained where they first appear. All provider statements are taken from the providers' own documentation, quotations verbatim, retrieved on 9 September 2026. The article is not legal advice, and it does not replace an assessment of your own case.

The Short Answer: Not by Default

Can you use Vercel and Supabase in a GDPR-compliant way? Yes, but not by creating an account and picking Frankfurt as the region. Both providers say so themselves, just not on the landing page. Vercel writes in its compliance documentation that it supports its customers' GDPR compliance and lists five commitments to that end, from the security level to the standard contractual clauses. Supabase writes that it supports GDPR-compliant deployments. In both cases the subject of the sentence is the customer. The provider supplies the tools, and the user has to apply them, document them and answer for them.

What that involves fits in one paragraph. The user signs the data processing agreement, which the GDPR calls a contract under Article 28 and practice usually calls a DPA. The user chooses the region instead of accepting the default. The user documents the basis on which data may leave the territory of the EU, because with both providers it can do so even with an EU region, at Vercel by the provider's own reservation, at Supabase through support and subcontractors. The user extends the privacy policy to name the recipient, the third country and the basis. And the user consciously decides whether to carry two risks that no contract takes away. Those two risks are the core of this article. The rest is the groundwork needed to understand them.

What an EU Region Delivers — and Who Is in the Chain

An EU region is a promise about storage location. At Supabase that means: Whoever creates a project in an AWS region gets the database, the auth service and storage hosted in that region, as the security page states. At Vercel the region is a choice for the part of the application that computes on the server, the so-called serverless functions, and the default is not Europe. The compliance documentation says it in one sentence: "The default location for Vercel functions is the U.S." In front of those functions sits Vercel's worldwide delivery network, a CDN (content delivery network), which accepts visitors' requests at the nearest of twenty locations and serves the pages from there. The chosen region applies to the functions, not to that network.

The question of whether a service can be used in a GDPR-compliant way has two parts. The first is: Where does the data live? The region answers it, and for Supabase and Vercel alike that part is settled. The second is: Which companies process the data, and whose law are those companies subject to? No region answers that. The answer lies in the list of subcontractors the provider brings in to run the service and who touch the data in the process. Both providers publish that list, and at both it reads differently from what the marketing suggests.

Vercel: Vercel, Inc. is a US company. It is certified under the EU-US Data Privacy Framework, and it additionally relies on standard contractual clauses for transfers. Both statements are in the compliance documentation. Directly below them stands the reservation that puts the region into perspective: "Vercel may transfer data to and in the United States and anywhere else in the world where Vercel or its service providers maintain data processing operations." Vercel may therefore move data to the United States and to any other location where it or its service providers process data. Backups, according to the same page, are replicated globally and are not accessible to customers.

Supabase: Here the research for this article produced a different picture from the expected one. According to the terms of service and the data processing agreement, the contracting party is not a US company but Supabase Pte. Ltd., based in Singapore. The terms place the contract under California law, and the data processing agreement relies on standard contractual clauses for transfers, a contract text prescribed by the European Commission in which the recipient commits to European data protection rules, and does not mention the Data Privacy Framework. Supabase's own subprocessor list, in the version dated 1 June 2026 and retrieved on 9 September 2026, then names the companies that actually touch the data: Amazon Web Services for hosting, Cloudflare, Google and Fly.io for further hosting services, Vercel for hosting, and at the top of the list Supabase, Inc. for support. Supabase's privacy policy names the United States and Singapore as transfer destinations, each on the basis of standard contractual clauses, and describes its own services as "primarily hosted in and provided from the United States". The contracting party sits in Singapore, the data sits with a US host, and the US company has access as a subcontractor.

For the GDPR the seat in Singapore changes nothing about the classification. An adequacy decision is the European Commission's finding that a country outside the EU protects personal data as well as the EU itself does. Where one exists, data may go there as if it stayed in the EU. For Singapore no such decision existed at the time of writing, and for the United States it applies only to companies with an active certification under the Data Privacy Framework, and only to the data types that certification covers. Every transfer to Singapore is therefore a third-country transfer that needs a mechanism of its own, in Supabase's case the standard contractual clauses. What the case shows is something else: The provider's legal notice is the wrong place to answer the provider question. The right place is the list of subcontractors. As soon as a service provider under US jurisdiction appears there that processes personal data or can access it, and with services on AWS, Google Cloud or Azure one regularly does, two things have to be examined separately for that data. First, whether a third-country transfer takes place and on what basis, which is the next section. Second, what state access risk that link brings with it, which is the section after that. How large the second risk actually is depends on which data the link really has in its possession or under its control. The EU region has settled the storage location. The chain has settled which legal systems have a say.

Three things come on top with every region, because they do not hang on the storage location. Support access, which the provider needs to operate the service, happens from wherever its staff sit. Metadata, meaning account data, invoices and access logs, runs in the provider's systems and not in the chosen region. And backups sit wherever the provider replicates them, at Vercel explicitly worldwide.

The Transfer Mechanism: Three Attempts

The first of the two risks concerns the basis on which data may leave the EU at all. The GDPR allows transfers to a country outside the EU only under the conditions of its Chapter V. The yardstick behind it is that the data keeps a level of protection there that is essentially equivalent to the European one, and there are three routes to that. The first is an adequacy decision by the European Commission under Article 45, with which the Commission finds across the board that a country offers that level. The second is appropriate safeguards under Article 46, in practice almost always the Commission's standard contractual clauses, with which the recipient contractually commits to European rules, more rarely binding corporate rules within a group of companies. The third is a set of narrowly drawn exceptions under Article 49, such as explicit consent for a single transfer, which are no use for a running service.

For the United States the Commission has taken the first route three times, and twice the Court of Justice has struck the decision down.

Safe Harbor, the 2000 decision: US companies could self-certify that they observed certain principles. After the revelations about US surveillance programs in 2013, Max Schrems, then a law student in Vienna, lodged a complaint against Facebook with the Irish data protection authority because his data was going to the United States. The complaint reached the Court of Justice via the Irish High Court, and the Court declared the decision invalid on 6 October 2015. The reasoning was that US authorities had general access to the content of communications and EU citizens had no legal remedy against it.

Privacy Shield, the 2016 decision: The successor came with an ombudsperson in the US State Department and further commitments. Again it was a complaint by Schrems, who by then was running data protection cases full time with the organization noyb, and both judgments therefore carry his name, Schrems I and Schrems II. The Court of Justice struck the decision down on 16 July 2020, on the same grounds in updated form. The surveillance powers under Section 702 of the Foreign Intelligence Surveillance Act (FISA), the US law on foreign intelligence, and Executive Order 12333, a presidential order issued without Congress, were not limited to what is necessary, EU citizens could not challenge them before any US court, and the ombudsperson was neither independent nor able to bind the intelligence services. In the same judgment the Court made clear that the standard contractual clauses remain valid, but that whoever exports data must assess for the specific transfer whether the law and practice of the receiving country guarantee the level of protection, and must provide additional measures where needed. That assessment is usually called a transfer impact assessment, and it applies then as now. Between 2020 and 2023 it affected almost every transfer to the United States, and since the third attempt only those that do not rely on it, as in the Supabase case.

Data Privacy Framework, the 2023 decision: The third attempt rests on an executive order of the US President from October 2022 that lays down proportionality principles for the intelligence services and sets up a review court, the Data Protection Review Court, before which EU citizens can bring complaints against surveillance measures. On that basis the Commission adopted the adequacy decision on 10 July 2023. US companies self-certify again, and the US Department of Commerce keeps the list. Vercel says it is on it. Supabase does not mention the framework in its contract, its security page or its privacy policy and relies on standard contractual clauses.

The decision is under attack. The French member of parliament Philippe Latombe challenged it directly before the General Court of the European Union, and the General Court dismissed the action on 3 September 2025. It found that the United States ensured an adequate level of protection at the time of the decision, and it expressly tied that finding to that point in time. On 31 October 2025 Latombe lodged an appeal with the Court of Justice, case number C-703/25 P, on four grounds. On 4 June 2026 the Court admitted Microsoft as an intervener in support of the Commission. As of 9 September 2026 no hearing date is known, and the decision stands.

Two things are worth keeping in view. First, the framework rests on an executive order, not on a statute, and an executive order can be changed by the next president. Second, the US oversight body that is supposed to monitor compliance with the commitments, the Privacy and Civil Liberties Oversight Board, has been without a quorum since three of its members were dismissed in January 2025. Its own members page lists a single serving member on 9 September 2026. The dismissals are themselves the subject of litigation: A federal court in Washington declared them unlawful in May 2025, and the appeals court stayed that ruling in July 2025 for the duration of the proceedings. Whether and how that enters the judicial review of the framework is open. For the user both mean that the basis of their transfer depends on political decisions in another country.

If this decision is struck down too, what happened in 2020 happens again. Whoever relied on the framework alone is left without a basis and has to switch to standard contractual clauses, including their own assessment of US law. Whoever documented both from the start, the provider's list entry and the standard contractual clauses in the data processing agreement together with the assessment of US law, has less to do on that day. That is exactly why the checklist below carries them as two separate items.

The CLOUD Act: Possession or Control, Not Storage Location

The second risk, the CLOUD Act, is independent of the first, and it survives every adequacy decision. Its origin is a dispute over emails. In 2013 US investigators demanded from Microsoft, by warrant, a mailbox that sat in a data center in Dublin. Microsoft refused, arguing that a US warrant did not reach as far as Ireland. An appeals court sided with Microsoft in 2016, the Supreme Court took the case, and before it could decide, Congress passed a law that answered the question. The Clarifying Lawful Overseas Use of Data Act, CLOUD Act for short, was signed on 23 March 2018.

The core rule is in 18 U.S.C. § 2713. A provider must in principle hand over content and subscriber records that are "within such provider's possession, custody, or control", and that "regardless of whether such communication, record, or other information is located within or outside of the United States". In one sentence: What counts is whether the provider possesses or controls the data, not where it is stored. A data center in Frankfurt is no argument against this rule.

The law provides a special objection procedure for the case that disclosure would violate the law of another state, but only under two conditions at once. The customer concerned must not be a US person, meaning neither a US citizen nor resident in the United States, and the United States must have concluded an agreement under this law with the country where the data is stored. Such agreements exist so far with the United Kingdom, in force since October 2022, and with Australia, in force since January 2024. With the EU or any of its member states there is none. For data in Frankfurt the second condition is missing, and with it this route. What remains is the general possibility of challenging a demand as unlawful, as Microsoft did in the Ireland case. That presupposes a provider that does so.

On the European side stands Article 48 of the GDPR. It recognizes decisions by authorities of a third country only if they rest on an international agreement, such as a mutual legal assistance treaty in which states promise each other support in investigations. A US provider with EU customers thus stands between two legal systems that demand opposite things. The data processing agreement with the customer does not resolve that conflict, because a contract between two companies does not override a US law. What the contract can deliver is a commitment to inform the customer and to contest demands for disclosure as far as the law allows.

What that sounds like in practice was shown by Microsoft France before a committee of inquiry of the French Senate in June 2025. Anton Carniaux, in charge of legal and public affairs there, was asked whether he could guarantee that data of French citizens would never be handed to US authorities without France's consent. His answer is in the minutes of the session of 10 June 2025: "Non, je ne peux pas le garantir, mais, encore une fois, cela ne s'est encore jamais produit." No, he could not guarantee it, but it had never happened so far either. And on the question of the obligation: "Lorsque nous sommes obligés de les donner, nous les donnons." When Microsoft is obliged to hand data over, it hands it over. The two sentences belong together. The first names the risk and how often it has materialized so far, the second the rule behind it: disclosure after the provider's own check of whether the demand is lawful, not before. That is not a statement about other providers, but it is one about the limit of what any provider can promise.

That leads to proportionality. The probability that US authorities take an interest in the user table of an ETL tool with a handful of users is low. The risk is structural, not practical. It consists in the user writing a promise into their privacy policy that they cannot keep, because their provider cannot keep it. Whoever understands that and accepts it consciously acts differently from someone who does not know it. That difference is what the last item of the checklist is about.

A question that comes up regularly here concerns the European subsidiaries of US groups. If the contracting party is called AWS Europe in Luxembourg or Microsoft Ireland, does that help? The seat of the contracting party alone does not answer the question. The rule turns on possession, custody or control, and whether a company within a group controls the data in that sense is a matter of actual access, not of the org chart. The Microsoft Ireland case was the trigger for the law because it concerned data held by a European subsidiary, but no blanket rule follows from it that every EU subsidiary automatically holds data under the control of its US parent. What does follow is the duty to check which company actually gets to the data. That is exactly what makes the Supabase case above so instructive. There it is the other way around: The contracting party sits outside the United States, and the US company is in the chain regardless, with support access.

The Checklist

What a user has to examine and record for the third-country question at Vercel, Supabase or a comparable service can be worked through in seven items. The list does not create compliance and does not replace an assessment of the processing as a whole. It does not remove the two risks, it makes them visible and documents the decision to carry them. For an operator who gets asked, that is the difference between an answer and a shrug.

Read the subprocessor list: Mark every company subject to US jurisdiction (host, parent company, support), and record for each link which data it processes or can view. Only for that data does the CLOUD Act question arise.
Sign the data processing agreement: Conclude the contract under Art. 28 GDPR (DPA) and file it. Check whether it contains standard contractual clauses.
Choose the region: Do not accept the default. At Vercel set the function region, at Supabase the project region. Record what the region covers and what it does not (CDN, backups, support, metadata).
Document the transfer mechanism, both separately: The provider's active entry under the Data Privacy Framework (look it up yourself on dataprivacyframework.gov, including its scope) and the standard contractual clauses from the contract together with your own assessment of US law (transfer impact assessment). If the framework is struck down, the second basis carries on, but only with that assessment.
Extend the privacy policy: Recipient with company name and seat, third country, transfer mechanism, and a note on possible government access under the law of the third country.
Subscribe to changes of the subprocessor list: Both providers announce changes, and the list changes several times a year.
Decide the residual risk consciously and record it with a date: Which data sits with the provider, how serious would access be, and why is the operator prepared to carry that.

The provider information in this article is current as of 9 September 2026. The lists change, and what counts is always the current version at the provider. Item 1 comes first because it determines the others. Whoever reads the list knows for whom to document item 4 and what to write in item 5. Item 7 is the one most people leave out. It is the only one that demands a decision rather than a document, and it records the actual risk decision that stands behind the other six items.

The Alternative: EU Provider or Self-Hosting

Anyone who wants to rule the two risks out rather than carry them has only one route, no provider under US jurisdiction in the chain. Anyone who can carry them has the checklist. In practice the one route means either an EU provider that runs its own data center, or a server of your own with such a provider.

What an EU provider solves: With a provider based in the EU, with its own infrastructure and with no third-country subcontractors in the chain, examples for the first two conditions being Hetzner in Germany, IONOS in Germany and OVHcloud in France, without rating the products, the third-country transfer can drop away for the infrastructure layer, provided support access and the remaining data flows also stay in the EU. Then there is no adequacy decision to check, no standard contractual clauses for the host and no CLOUD Act for the data center. The data processing agreement remains mandatory, and so does the look at the subprocessor list, because an EU provider can use US service providers. Reading the list is item 1 here too.

What self-hosting costs: The price is not primarily money but operations. Whoever runs an application with a login on their own server takes on the reverse proxy, which passes requests from the internet on to the application, the certificates, the identity provider, which manages login and users, the database, the backups and the monthly updates across every layer. In the DI² project that was the decision, and the sibling article One VPS, Four Environments, No Cookie Banner describes what it cost, from the RAM budget through secrets in two places to the monthly bill. On your own server in the EU the third-country question does not arise for the infrastructure. That is the real difference between the two routes, not the price.

Decision aid: Three questions suffice for most cases. First, which data sits with the provider, only account data or also what users enter into the application? The closer to health, financial or employee data, the heavier item 7 of the checklist weighs. Second, who will ask, a customer with a data protection department of their own, a supervisory authority, nobody? Whoever has business customers in the EU will get the question. Third, who carries operations, and what happens when that person is out for two weeks? A managed service answers the third question better than a server of your own, and that is a legitimate reason for Vercel or Supabase. It should just be taken together with the answers to the first two questions and written down.

Summary

An EU region answers where the data lives. It does not answer whose law applies to the companies that process it. That answer is in the subprocessor list.
Vercel is a US company with a certification under the Data Privacy Framework, a US default for functions and an explicit reservation to transfer data to the United States and to any other processing location. Supabase's contracting party sits in Singapore, the US company Supabase, Inc. is a subcontractor, and hosting sits with AWS. In both chains there are companies under US jurisdiction.
The transfer mechanism for the United States, a decision of the European Commission, has been struck down twice by the Court of Justice, and the third is under review right now. As of 9 September 2026 the Data Privacy Framework stands, and appeal C-703/25 P is pending. Whoever documents the list entry and the standard contractual clauses separately has less to do on the day of a judgment.
The CLOUD Act in principle obliges providers under US jurisdiction to hand over data in their possession or under their control, regardless of storage location. The law's special objection procedure presupposes an agreement that does not exist with the EU. A data processing agreement changes none of that.
The checklist makes both risks visible and documents the decision to carry them. It does not remove them. Whoever does not want to carry them needs an EU provider or a server of their own in the EU, and both cost operations rather than money.

FAQ

Is Vercel or Supabase GDPR-compliant if I pick the Frankfurt region?

Not by default, the region only settles the storage location. At Vercel the documented reservation remains, to transfer data to the United States and to any other processing location, and at Supabase the US companies remain in the subprocessor list, above all the host AWS and Supabase, Inc. for support. Both are third-country transfers that need a mechanism, a data processing agreement and a note in the privacy policy. The region is item 3 of the checklist, not the checklist.

Is the Data Privacy Framework still valid in September 2026?

Yes. The European Commission's adequacy decision of 10 July 2023 stands, and the General Court of the European Union dismissed the first action against it on 3 September 2025. Appeal C-703/25 P has been pending before the Court of Justice since 31 October 2025, Microsoft has been admitted as an intervener in support of the Commission since 4 June 2026, and as of 9 September 2026 no hearing date is known. To check the current state, search for the case number on curia.europa.eu.

What happens if the Court of Justice strikes down the Data Privacy Framework too?

The same as in July 2020 with Privacy Shield. Transfers that rest on the framework alone lose their basis from one day to the next. Whoever has additionally agreed standard contractual clauses in the data processing agreement relies on them from that day on and has to carry out the assessment of US law themselves, as every company did between 2020 and 2023. That is why the checklist documents both mechanisms separately.

Does the CLOUD Act apply to AWS, Azure or Google Cloud data in Germany?

Storage in Germany changes nothing if the provider is subject to US jurisdiction. The rule turns on possession, custody or control, so the question is which company within the group actually gets to the data. A contracting party based in Luxembourg or Dublin does not answer that by itself, but a US parent in the org chart does not automatically answer it the other way either. Whether and how a group contests a disclosure in a given case is a matter of its contract and its practice, not of the subsidiary's seat.

Do I need to care about this for a hobby project with five users?

The obligations apply regardless of size as soon as personal data is processed, and a login table with email addresses is enough for that. The risk of government access is structural, not practical, with five users. What remains is the duty not to promise anything in the privacy policy that the provider cannot keep. The checklist takes an afternoon for a small project, and item 7 is then an honest line instead of an empty promise.

Sibling article:

One VPS, Four Environments, No Cookie Banner — How I Host a Next.js App with Self-Hosted Keycloak — the route without a US provider in the chain, with the setup, the decisions and the cost side.

Hub of this branch:

Agentic Coding from a User's Perspective — Experience: The Work Doesn't Disappear, It Shifts — the experience level that the infrastructure decisions belong to.

Cluster hub:

AI-Assisted SQL Development with Claude Code — Rules, Skills and Agents That Enforce Conventions — the enforcement system behind the project.

Agentic Coding from a User's Perspective — Experience: The Work Doesn't Disappear, It Shifts

Marcus — Wed, 09 Sep 2026 15:46:16 +0000

Since spring 2026, two projects have been taking shape with the same coding agent: an ETL generator on Next.js and PostgreSQL, and this blog. The agent does not complete lines, it works through assignments in the repository. This article collects my agentic coding experience from a user's perspective. I build databases, ETL processes and the framework behind them, and I use the agent as a tool without doing AI research or building AI tools myself.

Is it worth it? Yes. I spent years building client-server applications in VB.NET and C#, and I am now building a TypeScript application without knowing TypeScript. That is the visible gain. The less visible part is that the work has shifted: from programming to structuring. Rules and skills for the agent, feature specs, bug files, test cases, documentation. In my working life, other people used to deliver that work, a project lead for example, and I consumed it as a developer. I underestimated how much of it there is.

The essentials up front:

Is it worth it? Yes: A TypeScript application with frontend, tests and CI, built by someone who does not know TypeScript but knows databases, ETL and .NET.
The work is not gone, it has shifted: Rules, skills, specs, bug files, test cases, documentation. Project leads and teams used to deliver that.
The guidance lives in the repo, not in the prompt: The prompts are short because rule files and specs carry the work. That includes intermediate states.
The infrastructure was the biggest hurdle: one server with four environments, self-hosted identity, containers, TLS, and no cookie banner because the architecture does not need one.
Control is a loop: An agent extends the radius of what I can build, not the radius of what I can judge. Whatever I cannot judge therefore goes through recurring checks that the agent runs. A green signal is not proof.
This article is itself an example: The draft comes from the agent. Selection, corrections and publication are my work.

Prerequisite: none. Anyone who wants to go deeper will find the way into this blog's technical articles at the end.

What Agentic Coding Means Here

A coding agent is a language model that, with tool access, executes several steps in a row on its own inside a repository: reading files, running commands, changing code, running tests, committing. The difference to autocompletion is not the quality of individual lines but the mode of work. I formulate an assignment, the agent works through it and reports a result.

In both projects the tool is Claude Code, in the terminal and in VS Code. The observations do not depend on the product, though. They depend on an agent having access to repository, shell and database, and on a human deciding what happens with it. The mechanics behind rule files, skills and sub-agents are covered by the hub article AI-Assisted SQL Development with Claude Code. This article is about what they changed in my daily work.

Case Study 1: A TypeScript Application Without TypeScript Skills

The app project is called DI² and is an ETL generator on Next.js and PostgreSQL. The database layer consists of more than 150 DDL files, the repository is private, and I work on it alone. On the ETL side, the app's tools build on a framework of my own that I am publishing on GitHub step by step.

From .NET to TypeScript

What I bring is data models, SQL, ETL processes and years of client-server applications in VB.NET and C#. I know how an application with database, business logic and user interface is cut, where state lives and where errors originate. What I do not bring is the language. TypeScript, the React ecosystem and the test tooling of the web world were new to me, and to this day I cannot read a file of this application the way I read a stored procedure.

The frontend, the CI pipeline and the test infrastructure came into being anyway. The clearest example is the 241-line Node script that checks the project's SQL conventions and that I could not have written. My role was a different one: I knew the conventions, I could judge a finding as right or wrong, and I made the framing decisions. Acceptance ran through runs, not through reading the code, as the article about the guard shows. An agent extends the radius of what I can build. It does not extend the radius of what I can judge. Where the two diverge, acceptance needs a criterion that can be checked without understanding the code.

Conventions in the Repo Instead of in My Head

In a classic solo project, the conventions live in the developer's head. With an agent that starts every session without the history of the previous one, that does not work. In the DI² project this turned into 27 rule files, 28 by now, one per convention, each with its reasoning. Features and bugs get their own files with a running ID before a single line of code exists. How such a rule set is derived from an existing codebase is described in the generate-refine-derive loop. What it costs when rules alone are not enough is shown by 799 hardcoded font sizes despite an unambiguous design rule.

The second half of this rule I only noticed when it failed. I accidentally ended a session in the middle of a bug fix and asked a new one to pick up where the old one had left off. The agent found no remains, neither in the working tree nor in the stash. That getting back in still took only minutes was down to the bug file in the repository: error description, database evidence, reproduction steps and three named hypotheses with a checking order. The intermediate state lives in the repo, or it does not live at all.

The Infrastructure Was the Biggest Hurdle

One part of the project that cost me a lot of time is not a line of application code but the infrastructure underneath: a single VPS at Hetzner with four environments, a self-hosted Keycloak as identity provider, an nginx in front of it, a good dozen containers, and a VPS provider that blocks outbound mail ports. I did not write a single line of it. What I decided was that there would be one server instead of four, a self-hosted identity instead of a cloud service, and a location in the EU, and behind these decisions is a design goal I cared about in particular: the application should get by without a cookie banner. What remains are four cookies that are permitted without consent: the login's session token and CSRF token, a language cookie for the public pages, and the state of the navigation. The setup, the decisions behind it and the cost side are described in One VPS, Four Environments, No Cookie Banner.

Reading Is No Longer a Control

Two things together changed the way I work. The first is the language: Whoever cannot read the language of their code cannot control it by reading. The second is the volume. The agent writes so many lines in so little time that reading and understanding all of it would not be possible even if I knew the language. Reading my own code in full, the way I knew it from my .NET projects, therefore drops out as a control. That is not an admission of failure but an unavoidable change in the way of working. If reading drops out as a control, something else has to take its place.

The Control Loop

The control I cannot provide myself has to happen somewhere else: in a loop of checks that the agent runs, but whose yardstick is not its own judgment but rules, tests and audit reports. It does not run once but repeatedly. After every piece of feedback, a rule is sharpened. Recurring checks have skills, that is, versioned prompts in the repository, among them one for security audits and one for update checks. I had unit tests pushed under existing code after the fact. The foundation for that only came in July 2026, and by now there are more than 700 tests. Since the end of August there is a catalog of manual test cases with acceptance criteria. All in all, 14 skills and 28 rule files carry this loop. How that loop runs in detail, what evidence there is for it and where it has failed is described in The Control Loop.

It has found things I would not have found by reading. A rate-limiting rule in the web server had been pointing at an endpoint that no longer existed since the identity provider was switched, and for months it looked like protection. Another case started with a vague sentence from me that the page was barely responding. The agent compared all four environments, found production affected as well although it did not even have the new version, and discovered 2.9 GB in swap. The identity service was sitting on disk, hence 9.6 seconds for the first login and 0.19 seconds for every subsequent one. No application code was involved. Both cases are told in detail in The Agent Measures Where I Click.

One lesson matters more to me than any number: A success signal is not proof. A test suite was green for four weeks while three fix attempts went right past a scroll bug, because the test had stubbed exactly the value whose wrongness was the bug. An audit report said "passed", and only my follow-up question revealed that the decisive click path had never been executed. Both signals attest that a process ran, not that an effect occurred. Since then, verifying means finding a second, independent route to the same question. How to write test cases that deliver that is covered in the article How to Write Manual Test Cases.

Case Study 2: Writing a Blog with an Agent

This blog has been run editorially with the same tool since May 2026. Back then the articles were moved from WordPress into a repository, one folder per topic with a German and an English version. Today there are 65 topics, 55 of them live in both languages. What the agent does here differs less from the app project than I would have expected.

Spec, Rounds, Revision Log

Before every article comes a spec: role in the topic cluster, target audience, search intent, outline, boundary to neighboring articles. Then the article runs through fixed rounds, from the critique of the existing text through structure and style to the English version. Every round gets an entry in a revision log and its own commit. Nine rule files carry the conventions, among them style heuristics against typical markers of AI-written text: no semicolon in the prose, at most one dash per paragraph, no first-person plural. Every one of these heuristics came out of a piece of feedback from me. The agent writes drafts, I correct, and the correction becomes the rule for the next article.

The way onto the live site remains manual. I copy the text into the WordPress block editor, set images and SEO fields, and publish. Afterwards the agent verifies remotely via live HTML and REST API. For the article on the functional aesthetics of SQL it found four defects across the two language versions that way, defects invisible in the repository because they only arise in the editor: an image that had been pointing at another article's file for months, an SEO title that lost its last letter on paste, a jump link that led nowhere because of an old HTML anchor, and, in the English version, a link to the article on SSMS editor options that pointed at the German instead of the English version. I corrected that link twice in the block editor, and twice it stayed wrong. The agent read the post's modification timestamp via the REST API: It had not moved on either attempt, so my save clicks had not been saves at all. The cause was the editor's link dialog. Whoever types in the new URL and then clicks outside the dialog instead of confirming it with Enter or "Apply" discards the change, even if they press "Update" afterwards. On the third attempt the link held. The finding has been a note in the rule file for live verification ever since.

The Review Comes from a Fresh Session

A language model can also evaluate a text, and I have used that from the start. The methodology, however, I had to develop myself. While reviewing an article I noticed that a model anchors on its earlier verdicts within the same chat session: first rating about 8 out of 10, after several revisions in the same session 9.7, in a fresh session clearly more critical again. The high scores were not quality but history. Since then every review runs in a fresh session of a model from a different vendor, with a frozen standard prompt. The score is a diagnosis, not a goal.

I hand the result back to the agent as a raw paste, and it sorts it into three categories: adopt, matter of taste, already addressed. Experience from more than twenty reviews: 30 to 50 percent of the points are valid. The review is deliberately context-free, and precisely for that reason it needs as its counterpart the agent with repository context, which can look up in the revision log that a suggestion was already rejected months ago. A review is a finder, not proof. When one flagged a statement as "too absolute", the agent's cross-check showed it was not too absolute but wrong. What settled it in the end was a test against running database containers.

Fresh, though, means more than having no history. A new session of the same model that wrote the text would have no prior context, but it would bring the same blind spots: the patterns I read as markers of AI-written prose are more likely caught by a model from a different vendor than by one from the same family. And a session inside my development environment sees the repository, loads the rules automatically and can look up in the log what the template deliberately leaves out. So the counted review stays with the outside model and the same template for every article, otherwise the scores cannot be compared. A fresh session of my own model has a different place: as a rule check that holds the text against the blog’s style heuristics and conventions. That is a different question from “is the text good”, and it needs exactly the context the reviewer must not have. These review experiences will get an article of their own.

What Generalizes

The agentic coding experience in both projects produced the same patterns. Four of them carry my daily work.

The guidance lives in the repo, not in the prompt. Rule files, specs, bug files and revision logs are the actual work. The prompts only name the entry point and the assignment. The intermediate state belongs there too: What an agent has worked out is recorded promptly as a commit or a note in the feature or bug file. Language models live on text. What is not written down does not exist for the next session.

The structuring work is the price of the gain in radius. I get an application I would not have built alone and a blog I would not sustain alone. In return I take on roles that are spread across a team: writing requirements, formulating acceptance criteria, declaring test cases, maintaining documentation.

The repo learns, not the agent. Every session starts without the history of the previous one. What was learned from a mistake exists for the next session only if it is written down: as a rule or a checkpoint in the repository. That also works backwards: Many defects I correct in old articles today were not violations when they were written, because the rule only came afterwards. The existing body ages relative to the rules that were learned from it.

Learning remains mandatory. This blog holds the position that formatting is learning: Whoever indents a statement by hand builds the mental model of the data. For agentic coding that applies with more force. Whoever does nothing but sign off loses, over time, the model they need for signing off.

Where It Gets Rough: Limits from a User's Perspective

The limits are the part of my agentic coding experience that has occupied me the most.

Code Drift Despite Rules

A rule with a stated reason improves the hit rate but does not guarantee compliance, and at high volume every residual rate turns into measurable drift. The frontend case with 799 hardcoded font sizes is the evidence, and the answer was not a better rule but a check that does not get tired. The blog's editorial work knows the same effect: Despite the style heuristics, every first draft from the agent contains semicolons or compressed notation, and I recognize AI-written text by exactly these patterns. What is in the rules still has to be checked, only the checking moves from reading to counting.

Collateral Damage in the Working Tree and on My Machine

As soon as more than one session works in the same repository, or one session works alongside me, errors arise that do not exist in single operation: someone else's work in my own commit, deleted files of a parallel session, numbers assigned twice, and once 220 infinite loops that froze my computer for twelve minutes. Against the shared index, only git commit --only with explicit paths turned out to be reliable, and the lesson from the first case would not have prevented the second. The five cases and what holds against them are in Two Agents, One Working Tree.

You Can Only Sign Off on What You Can Judge

Review effort is the new bottleneck. The answer is not to read faster but to shift acceptance: to runs instead of reading, to criteria instead of impression, to external reviews instead of my own goodwill. Each of these shifts presupposes that I can formulate the criteria and recognize a result as right or wrong. With the Node guard I could, because I knew the SQL conventions. With a script whose domain logic I could not judge, the same division of labor would be a risk.

Added to that is a weakness that belongs to the tool's strength. An agent usually tests hypotheses systematically with measurements, and precisely because of that it hardly shows on the occasions when it builds a coherent story from incomplete clues instead. During one troubleshooting session the agent formulated a plausible cause as if it had been measured. It had not been. Since then I ask of every finding which part of it was measured.

Working Prompts as Examples

The prompts in both projects are short because the guidance lives in the repository. Three examples show what a prompt still has to do then.

Starting a revision series. Verbatim apart from the translation from German, only the internal identifiers are replaced by their meaning:

let's start the triage of the legacy articles. all relevant changes in the
articles and the lessons from revising them go into the article about agentic
coding

Two lines are enough because the spec, the work list and the rule files exist in versioned form. The agent read them, ran the first quality pass and established the material collection for this article as a side duty of the series.

Having an article reviewed externally. This prompt goes to ChatGPT in a fresh session, that is, to a model from a different vendor. It is versioned and has been frozen since August 2026. The original is German, shown here in translation and shortened:

You are a critical, independent technical editor for a technical blog about
SQL Server, Postgres, data quality and ETL. You know neither the author nor
earlier versions of the text, and you have no reason for leniency.

Rate the article in four dimensions with a score from 1 to 10 each:
technical correctness, structure and readability, SEO fitness, language and style.

Duties: Every point of criticism quotes its location verbatim. For every point
a concrete suggestion for improvement. No courtesy points. At the end, an overall
score and the five most important points, prioritized by impact.

--- ARTICLE ---

The most important sentence is the second one. Without it, the model rates the history instead of the text. The duty to quote the location verbatim makes the result sortable, because a point without a location can neither be implemented nor rejected with reasons.

A recurring check as a skill. A skill is a versioned prompt that I call with a slash command. This is how the description of the skill that checks for updates begins, verbatim apart from the translation and the replaced file paths:

Checks updates for dev dependencies (npm, Docker image declarations, GitHub
Actions) AND for the components that actually run on the VPS (OS packages,
Docker Engine, nginx, running container tags, Certbot). Writes findings to the
update documentation and to a dated security report. Keeps a history of
applied/deferred updates. Modifies nothing on its own.

The skill defines what is checked, where the result goes and what the agent is not allowed to do. For the server side it produces a checklist of read-only commands that I run myself. The acceptance criterion is the history, which shows me at the next run what was left undone.

How This Article Came About

This article is about transparency, so it has to deliver it itself. The text is a draft by the agent, produced from a spec and a material file that agent sessions kept over six weeks. Into this file, whichever session was working wrote down, on my instruction, what happened during the work, its own mistakes included. The first version was twice as long and written in the third person. That it became shorter, stands in the first person and mentions the infrastructure at all is the result of a discussion in which I asked the agent to listen first before rewriting.

My role is the same as in both case studies. I decided that this article exists and what it carries. I read the draft, discard, cut, correct and approve. I have it rated in a fresh session of another model, transfer it into WordPress by hand and check the links. What I do not do is write the text myself. And what I cannot do is in the text: write a Node script, see an effect in swap, retrieve a data point from a lost session. I had no prior experience of what this way of working would bring. What I underestimated was not the tool but the share of rules, specs, test cases and documentation that I had previously received from others.

FAQ

Can you tell that a text was produced with AI assistance?

Often, yes. Such texts give themselves away through recurring patterns. In German, in my experience, these are above all semicolons in the prose, verbless afterthoughts behind a dash, parentheses into which a whole piece of information is packed, and abstract terms that appear as the subject of a sentence and act there like a person. I have recorded these patterns in a rule file, and every draft is read against it before publication. Whether you can tell how a text came about therefore depends less on the model than on the correction pass that a human makes with a concrete list.

Can you build a TypeScript application without TypeScript skills?

Yes, under conditions. You need a model of how an application is cut, where state lives and where errors originate. In my case that came from years with .NET and databases. You need acceptance criteria that can be checked without reading the code: runs, tests, audits. And the infrastructure underneath remains a hurdle of its own that no programming language makes smaller.

How do you control what you cannot judge yourself?

Not by reading, but through a loop of checks that work without understanding the code: rules that are sharpened after every piece of feedback, skills for recurring checks, tests that have to be red on the old version, and audits with a report. The loop does not replace understanding, but it replaces reading. Every verdict has to state its open points as a condition, otherwise a "passed" travels further than its caveat.

Where do you start as a user?

With a single convention that lives in the repository and one assignment that has an acceptance criterion. Not with a large rule set, because that grows out of feedback. Anyone who wants to set up a database project with Claude Code will find a concrete entry point in the article on the open starter kit, and the mechanics behind it are explained by the cluster's hub article.

Is the effort worth it for a solo project?

Going by my agentic coding experience in both projects, yes. The time saved on programming is largely eaten up by the structuring work. What remains is a gain in radius: a TypeScript application including infrastructure that I would not have built alone, and a blog with spec, review protocol and revision log that a single person would not sustain without an agent. The condition is that you take on the roles that project leads and teams otherwise carry.

Hub:

AI-Assisted SQL Development with Claude Code — Rules, Skills and Agents That Enforce Conventions — the enforcement system whose user experience this article describes.

Spokes of this article:

One VPS, Four Environments, No Cookie Banner — How I Host a Next.js App with Self-Hosted Keycloak — the setup, the decisions behind it and the cost side that this article only touches on.
Two Agents, One Working Tree — What Broke When I Ran Parallel Claude Code Sessions in One Git Repository — five cases from parallel operation and the countermeasures that hold.
The Agent Measures Where I Click — Debugging Postgres, Docker and a Swapping Server with a Coding Agent — four debugging sessions in which measuring replaced guessing, along with the limit of that way of working.
The Control Loop — How I Get AI-Generated Code Checked That I Can't Read Myself — the five stations of the loop, their evidence and their blind spots.
An EU Region Is Not an EU Provider — Vercel, Supabase and the GDPR Third-Country Question — the second infrastructure spoke: why an EU region settles the storage location but not the provider question, with a checklist.

Infrastructure and deploy:

Database CI/CD with PostgreSQL — the lifecycle from object file to deploy.
GitHub Actions for Postgres Deploys — a Throwaway Database as Quality Gate — the deploy gate behind the infrastructure.
Deploying a SQL Schema Without a Migration Tool — the directory convention of the database layer.

Rules, drift, guard:

I Gave Claude Code 27 Rule Files Instead of One CLAUDE.md — structure and upkeep of the rule files.
AI-Assisted Coding Gave Me 799 Hardcoded Font Sizes — the measured drift finding.
Why sqlfluff Can't Lint Our SQL Conventions — and a 240-Line Script Can — the guard I could not have written myself.
Deriving SQL Conventions with Claude Code — the Generate-Refine-Derive Loop — how the rule files come about.

Getting started:

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

Next door:

The Functional Aesthetics of SQL — Why Structured Code Is Faster to Edit — the thesis "formatting is learning".
Is Claude a Woman or a Man? — and Why We Ask in the First Place — the other non-technical question about the tool.

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.

DEV Community: Marcus

Two Agents, One Working Tree — What Broke When I Ran Parallel Claude Code Sessions in One Git Repository

Why Parallel Claude Code Sessions Produce Errors That Don’t Exist Alone

Case 1: Foreign Work Swept Into a Commit

Case 2: The Obvious Countermeasure Doesn’t Hold

Case 3: Foreign Work Deleted

Case 4: Duplicate Numbers, Because Politeness Is Symmetric

Case 5: The Machine Is Shared Too

What Holds and What Doesn’t

Summary

FAQ

Related Articles

An EU Region Is Not an EU Provider — Vercel, Supabase and the GDPR Third-Country Question

The Short Answer: Not by Default

What an EU Region Delivers — and Who Is in the Chain

The Transfer Mechanism: Three Attempts

The CLOUD Act: Possession or Control, Not Storage Location

The Checklist

The Alternative: EU Provider or Self-Hosting

Summary

FAQ

Related Articles

Agentic Coding from a User's Perspective — Experience: The Work Doesn't Disappear, It Shifts

What Agentic Coding Means Here

Case Study 1: A TypeScript Application Without TypeScript Skills

From .NET to TypeScript

Conventions in the Repo Instead of in My Head

The Infrastructure Was the Biggest Hurdle

Reading Is No Longer a Control

The Control Loop

Case Study 2: Writing a Blog with an Agent

Spec, Rounds, Revision Log

The Review Comes from a Fresh Session

What Generalizes

Where It Gets Rough: Limits from a User's Perspective

Code Drift Despite Rules

Collateral Damage in the Working Tree and on My Machine

You Can Only Sign Off on What You Can Judge

Working Prompts as Examples

How This Article Came About

FAQ

Related Articles

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