A convention aware integrity auditor for Slowly Changing Dimension Type 2 tables that runs 16 structural checks over 1,280,000 dimension version rows in 3.95 seconds, ranks every finding by the fact rows and revenue that join to the broken business key, and writes idempotent repair SQL in DuckDB and Snowflake dialects.
- A merge that ran twice leaves two current rows and nobody notices for a quarter. Sixteen SQL rules cover overlaps, gaps, current flag drift, sentinel drift, key collisions, point in time referential breaks and type 1 / type 2 misclassification. Measured on a warehouse with 327 deliberately injected defects: precision 1.000, recall 1.000, 0 findings that ground truth cannot explain (docs/effectiveness.md).
- The tool that cries wolf gets muted. Interval convention and sentinel style are declared per table instead of assumed. On an identical clean warehouse of 40,000 dimension rows the auditor reports 0 findings with the declared conventions and 24,947 with every interval and sentinel flipped (benchmark/results/README.md).
- 4,000 findings is not a work queue. Every finding carries the fact rows and measure value that join to the broken key, so the list sorts by damage rather than by table name. On the demo warehouse the top finding covers 1,628 fact rows and 145,893 of measure, and the generated repair SQL closes 295 of 391 findings (75.4%) with a second run changing 0 rows.
A Type 2 dimension is a set of claims about time: this customer was in this segment between these two dates. Every point in time join downstream inherits those claims. When a merge runs twice, a late arriving record backdates a change, or a timezone boundary shifts an effective date by six hours, the claims stop being consistent and every historical report quietly starts returning a different number. The failure has no error message. It surfaces when an auditor asks why last quarter's revenue moved, and by then the answer costs weeks of reconciliation. For a mid sized analytics team, a restatement of one quarter's regional revenue split is a plausible two to three week diversion for two people plus the credibility cost with finance. That figure is a representative scenario, not a measurement from this repository.
This project audits the dimension for the structural defects that cause that, ranks them by blast radius, and generates the SQL to fix them. It seeds a DuckDB warehouse with four SCD2 dimensions built by a realistic daily merge, deliberately injects each defect class with recorded ground truth, then runs sixteen rule modules over it. Each rule is ordinary window function SQL parameterised by the table's declared convention, which is what lets one body of interval arithmetic serve both closed-open and closed-closed tables and both NULL and high date sentinels. The point in time referential check is the one almost nobody implements: it asks not whether the surrogate key on a fact exists, but whether the version it points at was in force at the fact's event timestamp. Findings are ranked by severity and then by the measure at risk, repair SQL is generated with sqlglot in both DuckDB and Snowflake dialects, and nothing is ever applied without an explicit environment interlock.
Measured on this container: the audit reports 0 findings on a correctly built warehouse of 4,638 dimension rows and 41,727 fact rows, and on the same warehouse after 327 injected defects it reports 391 findings at precision 1.000 and recall 1.000 with zero unexplained findings. Runtime scales from 0.68 s at 80,000 dimension rows to 3.95 s at 1,280,000 (p50 of five runs, 2 vCPU, DuckDB 1.5.5, 300,000 fact rows held constant). The container was shared with other builds during the benchmark and variance was not controlled for, so treat the percentiles as shape rather than as a clean lab measurement.
flowchart TB
subgraph decl["Declared contract"]
CONV["config/conventions.yml<br/>interval, sentinel, grain,<br/>type1 / type2 columns, fact links"]
end
subgraph build["Local warehouse (DuckDB, no cloud)"]
SEED["seed: 60 daily merge batches<br/>closes and opens versions<br/>per declared convention"]
CORRUPT["corrupt: injects each defect class<br/>and records expected checks"]
GT[("audit_ground_truth")]
WH[("dim_customer, dim_product,<br/>dim_employee, dim_cost_center<br/>fact_sales, fact_ledger")]
end
subgraph engine["Audit engine"]
RULES["16 rule modules<br/>adjacency, currency, intervals,<br/>keys, referential, type classification"]
BLAST["blast radius join<br/>fact rows and measure per key"]
RANK["rank: severity then measure at risk"]
end
subgraph out["Outputs"]
CLI["CLI: audit, explain, report"]
HTML["self contained HTML report"]
SQLGEN["repair SQL<br/>DuckDB + Snowflake via sqlglot"]
EVAL["precision / recall vs ground truth"]
end
CONV --> SEED --> WH
WH --> CORRUPT --> WH
CORRUPT --> GT
CONV --> RULES
WH --> RULES --> BLAST --> RANK
RANK --> CLI & HTML & SQLGEN
RANK --> EVAL
GT --> EVAL
B1{{"Boundary 1: unknown convention<br/>rejected at load, not guessed"}} -.-> CONV
B2{{"Boundary 2: missing tables<br/>fail with 'run seed first', not a traceback"}} -.-> RULES
B3{{"Boundary 3: writes blocked<br/>unless SCD2_ALLOW_APPLY=true"}} -.-> SQLGEN
B4{{"Boundary 4: second corruption pass<br/>refused, it would invalidate ground truth"}} -.-> CORRUPT
| Technology | Role here | Why chosen for this problem |
|---|---|---|
| DuckDB 1.5.5 | The warehouse under audit and the execution engine for every check | The checks are window functions partitioned by business key over millions of rows. DuckDB runs that shape in process with no server, so the whole project clones and runs with zero credentials, and 1.28M version rows audit in 3.95 s on 2 vCPU. |
Advanced SQL (window functions, CTEs, QUALIFY, IS DISTINCT FROM) |
Every rule and every repair statement | LEAD over PARTITION BY business_key ORDER BY valid_from is the natural expression of "does this version meet the next one". Writing it as SQL means the rules can be lifted into the real warehouse later, unchanged in shape. |
| sqlglot 30.14 | Transpiles generated repair SQL to Snowflake | The repair script has to run where the dimension lives. Parsing the DuckDB statement into an AST and re-rendering it for Snowflake catches dialect differences (INTERVAL '1' DAY becomes INTERVAL '1 DAY', TIMESTAMP literals become CAST(... AS TIMESTAMPNTZ)) that string formatting would silently get wrong. The test suite re-parses every generated Snowflake statement. |
| pandas / numpy | Batch construction in the seeder and grouping of check output into findings | The merge replay builds a staging frame per batch, and the injector needs reproducible sampling from a seeded generator so the ground truth is identical on every run. |
| PyYAML | Loads the per table convention contract | The convention is a declaration about the warehouse, not code. Keeping it in YAML means an analytics engineer can correct a mis-declared table without touching Python, which matters because a wrong declaration is the single largest source of false positives. |
| jinja2 | Renders the self contained HTML audit report | One file, inline CSS, no network assets, so it can be attached to a Jira ticket. Autoescaping is on, which the test suite asserts against a finding title containing markup. |
| matplotlib | Validity window timeline and the scaling chart | The timeline is the artefact that makes a non technical stakeholder understand the defect in three seconds: two bars overlapping, and a hole where no bar exists. |
| rich | CLI tables for findings, dry run counts and the effectiveness scorecard | The audit output is tabular and gets read in a terminal during an incident. Column alignment and severity colour are the difference between scanning it and squinting at it. |
| playwright (chromium) | Headless render of the HTML report for the README screenshot | Build time only. Nothing at runtime depends on a browser. |
| pytest / pytest-cov / ruff | 78 tests at 97% statement coverage, lint in CI | The load bearing tests are the clean baseline (a correct dimension must produce zero findings) and the convention awareness pair (the same two rows must be clean under one convention and a defect under the other, in both directions). |
Prerequisites: Python 3.11 or newer, git. No database server, no cloud account, no credentials.
git clone https://github.com/Sandeep0430/scd2-integrity-auditor.git
cd scd2-integrity-auditor
python3 -m venv .venv && source .venv/bin/activate
pip install -e ".[dev]"
cp .env.example .env # optional, every value already has a default
# 1. build a clean SCD2 warehouse by replaying 60 daily merge batches
scd2-audit seed
# 2. audit it. A correctly built dimension reports nothing.
scd2-audit audit
# 3. inject the defect catalogue, with ground truth
scd2-audit corrupt
# 4. audit again, ranked by the measure at risk
scd2-audit audit --top 15
# 5. see one finding in full: offending rows, blast radius, repair SQL
scd2-audit explain <finding-id-from-step-4> --dialect snowflake
# 6. how many rows would the repair touch? Nothing is written.
scd2-audit repair --dry-run --out artifacts/repair.snowflake.sql --dialect snowflake
# 7. precision and recall against the injected ground truth
scd2-audit evaluate
# 8. self contained HTML report
scd2-audit report # writes artifacts/audit_report.htmlApplying the repair is deliberately awkward:
SCD2_ALLOW_APPLY=true scd2-audit repair --applyReproduce everything in this README:
make test # 78 tests, coverage report
make bench # writes benchmark/results/results.json and README.md
python docs/measure_effectiveness.py # writes docs/effectiveness.json and .md
make screenshots # regenerates the three images in docs/screenshots
docker compose run --rm demo # the whole flow inside a container
The self contained HTML report rendered headless with playwright chromium at 1440x900. Findings are
grouped by severity and, inside a severity, ordered by the measure that joins to the broken key.
The expanded finding is the highest ranked one that carries a generated UPDATE: a point in time
referential break where 227 rows of fact_ledger for EMPL-00000 point at a dim_employee
version that was not in force at the event time, worth 21,675.47 of misattributed measure. Its
offending rows and the repair statement in both DuckDB and Snowflake dialects are shown inline.
Three business keys from the same dimension, drawn on a real effective date axis. The top key is correct: each version ends exactly where the next begins. The middle key is covered by two versions for five days, so an as-of join returns two dimension rows and every measure in that window doubles. The bottom key has a two day hole where no version exists at all, so an as-of join returns nothing and the period looks smaller than it was.
A real session: seed, inject 327 defects, audit (391 findings, ranked), evaluate against ground truth (precision 1.000, recall 1.000), dry run the repair, and the full test suite at 78 passed and 97% coverage. Raw text in docs/terminal_capture.txt.
Method: benchmark/run_benchmark.py builds a clean synthetic warehouse with set based SQL (the
daily merge replay is realistic but would dominate the measurement at these sizes), then runs the
full audit five times per level and reports percentiles of wall clock time plus per check
medians. Two axes are measured separately because the window function checks partition by business
key: axis one grows versions per key with keys fixed, axis two grows keys with versions fixed.
Both fact tables are held at 150,000 rows throughout. Container: 2 visible vCPU,
Linux 6.18.5, Python 3.11.15, DuckDB 1.5.5. The container was shared with other builds during
this run and variance was not controlled for.
Axis 1, dimension rows growing (business keys fixed at 5,000 per dimension):
| Versions per key | Dimension rows | p50 s | p95 s | p99 s | Rows/s at p50 |
|---|---|---|---|---|---|
| 4 | 80,000 | 0.679 | 0.925 | 0.932 | 117,786 |
| 16 | 320,000 | 1.838 | 2.389 | 2.460 | 174,074 |
| 64 | 1,280,000 | 3.954 | 5.656 | 5.944 | 323,690 |
Axis 2, business keys growing (versions per key fixed at 4):
| Keys per dimension | Dimension rows | p50 s | p95 s | p99 s | Rows/s at p50 |
|---|---|---|---|---|---|
| 2,500 | 40,000 | 0.746 | 0.914 | 0.941 | 53,648 |
| 10,000 | 160,000 | 1.431 | 1.489 | 1.497 | 111,802 |
| 40,000 | 640,000 | 1.850 | 3.201 | 3.465 | 346,021 |
The honest sentence: throughput improves with size because the fixed cost of sixteen separate
queries dominates at small scale, and the check that dominates at every scale is
duplicate_surrogate_key (0.673 s of a 3.95 s audit at the largest level) because it is the only
rule that groups the whole dimension by a high cardinality column with no partition to prune,
followed by type2_column_never_changes at 0.552 s for the same reason. The per check times sum
to about 3.4 s of the 3.95 s total; the remainder is the blast radius join that attributes fact
rows and measure to every broken key. Where it degrades: the audit reads every dimension row
every time, so a dimension an order of magnitude beyond these sizes would need the checks scoped
to keys touched since the last load rather than the whole table.
- ADR 0001: Convention aware SQL checks over the warehouse, not a dbt test package and not row by row Python. Declaring the interval convention and sentinel per table is what keeps the false positive count at zero; the same clean data audited with flipped conventions produces 24,947 findings.
- ADR 0002: Generate repair SQL for a human to review rather than auto-healing the dimension. The boring choice. A wrong automated fix on history silently re-attributes every fact in the window with no error and no alert, and the largest finding in the demo warehouse already covers 145,893 of measure.
- Incremental auditing. Every run reads the whole dimension. Add partition pruning by load date when a single audit exceeds the load window it has to fit inside, which on these numbers means somewhere past ten million version rows.
- Running against a live Snowflake or Azure SQL warehouse. Snowflake is a dialect target only. Add a connection layer when there is a real account, a service principal and a change ticket to hang it off, not before.
- Bridging and mini dimensions. The rules assume one surrogate key per version per business key. Add support when a dimension in scope actually uses a bridge table.
- Automatic remediation on a schedule. See ADR 0002. The trigger to revisit would be a check whose fix is provably deterministic under every convention plus a rollback path that restores the previous version rows, not merely a wish for fewer tickets.
- Alert routing and ticket creation.
auditalready exits non zero on any CRITICAL finding, which is enough to fail a pipeline step. Wire it to Jira when someone owns the queue.
- No credentials anywhere. DuckDB is a local file. There is no cloud SDK in the dependency list or in the Docker image, so there is nothing to leak. Snowflake exists only as a sqlglot output dialect.
- Configuration comes from environment variables only (
scd2_audit/config.py), with every variable documented in.env.example..envis gitignored; no secret is read, so none is logged. - What is never logged. The structured JSON logger emits check ids, timings, table names, counts and the run id. It does not emit dimension attribute values or fact measures. Business key values do appear in findings and in the HTML report, because a finding without its key is not actionable, so the report should be treated at the same sensitivity as the dimension it describes.
- Least privilege by default. The audit path opens the warehouse read only. Writes happen in
exactly one place,
repair --apply, which refuses to run unlessSCD2_ALLOW_APPLY=trueand which wraps the whole plan in one transaction that rolls back on any error. - Reproducibility. The seed and the injector are driven by
SCD2_RANDOM_SEED, so an audit result can be reproduced exactly from the committed configuration.
| Failure | Detection | Behaviour | Recovery |
|---|---|---|---|
| Convention declared wrong for a table (closed-closed read as closed-open) | Findings explode: 24,947 on clean data in the measured case, concentrated in overlapping_windows, history_gap and sentinel_inconsistent |
Rules run and report; the tool cannot tell it was lied to | Compare a known good key against the report, correct config/conventions.yml, re-run. The benchmark's convention sensitivity section documents the signature |
| Unknown interval or sentinel value in the conventions file | TableConvention.__post_init__ validation |
ValueError naming the table and the accepted values, at load time, before any SQL runs |
Fix the YAML |
| Audit run against a warehouse that was never seeded | require_tables before any rule executes |
Exit code 2 with "warehouse is missing tables ... Run scd2-audit seed first", no traceback |
Run scd2-audit seed |
repair --apply invoked without the interlock |
Config.allow_apply is false by default |
Exit code 2, nothing written, message pointing at the dry run | Read the dry run counts, then set SCD2_ALLOW_APPLY=true deliberately |
| A repair statement fails partway through the plan | Exception inside the single transaction in apply_plan |
Whole plan rolls back, the dimension is left exactly as it was, exception propagates | Fix the statement or exclude the finding, re-run the dry run |
| Repair script run twice by mistake | Every UPDATE carries a predicate excluding rows already in the target state | Second pass changes 0 rows, measured in docs/effectiveness.md |
Nothing to do |
| Corruption injected on top of existing corruption | corrupt reads audit_meta.corrupted |
Exit code 2 explaining that the ground truth would be invalidated, --force available for deliberate stacking |
Re-seed, then corrupt once. This is the war story below |
| A fact points at a dimension version that never existed | fact_fk_missing (CRITICAL) |
Reported with the fact row count and measure at risk; repair re-points by natural key and event time | Apply the generated repair after the structural repairs |
The bug that cost the most time was not in a check. It was in the thing that measures the checks.
Late in the build, after regenerating the README screenshots, scd2-audit evaluate reported
overall precision 0.508 with 386 findings that ground truth could not explain. Minutes
earlier the same command on the same code had reported 1.000 with zero unexplained findings, and
nothing in the rule modules had changed between the two runs. The obvious suspects, a
non-deterministic seed or an ordering dependency between checks, were both wrong: reseeding from
scratch and re-running gave 1.000 again. The failure only appeared after the screenshot script
had run.
The root cause was that the screenshot script built the warehouse (seed, then corrupt) and then,
inside the terminal capture block, ran scd2-audit corrupt a second time against the same file.
The second pass injected a fresh round of damage on top of the first and replaced
audit_ground_truth with only its own defects. Every finding from round one instantly became a
false positive. The measurement was lying, not the auditor. It was uncomfortable to find precisely
because the number it produced, roughly half of the findings unexplained, is exactly what a
genuinely broken check suite would look like, and I spent twenty minutes reading window function
SQL that was fine.
Two things came out of it. corrupt now reads audit_meta.corrupted and refuses to run a second
pass unless --force is passed, with a message that says why, because stacking damage while
replacing the ground truth is never what anyone means to do. And the screenshot capture now
replays seed before corrupt so it always starts from a known state. Both are covered by
tests. Fix commit
a4c53e8: fix(corrupt):
refuse a second corruption pass unless forced. The earlier
fca5398 is a smaller
one from the same instinct: DuckDB's CAST(DOUBLE AS BIGINT) rounds rather than truncates, so a
randomly sampled index could exceed the key table and drop rows, which left one dimension member
genuinely unreferenced and broke the zero false positive baseline by exactly one finding.
- Scope the checks to keys touched since the last load. The audit currently reads every row every time. The first metric to watch after deploying is audit wall clock as a fraction of the load window; when it passes about 20% of that window, incremental scoping stops being optional.
- Track findings across runs and report the delta. Which findings are new since yesterday matters far more than the absolute count, and it is the difference between a report someone reads and a report someone archives.
- Learn the convention rather than only declaring it. A confidence score derived from observed boundary behaviour would not replace the declaration but would flag a table whose data disagrees with what the config claims, which is the case the tool is currently blind to.
- Widen the injected defect catalogue. The precision and recall numbers here are measured against defects this repository knows how to cause. Adding adversarial cases contributed by people who have broken dimensions in other ways is the only honest path to claiming coverage of the wild.
- Emit the repair plan as a dbt snapshot compatible fix. Where a warehouse already uses dbt snapshots, the correct long term repair is often a snapshot configuration change rather than an UPDATE, and the auditor has enough information to suggest it.