06 - Data Quality Framework

24 min read

Data Quality Framework

Quote

“Uncontrolled variation is the enemy of quality.”

— W. Edwards Deming, Out of the Crisis (1986)

“It is wrong to suppose that if you can’t measure it, you can’t manage it — a costly myth.”

— W. Edwards Deming, The New Economics (1993)

Data Quality Dimensions

Completeness — all expected data is present

Completeness means every row and every required field that should exist actually exists. A dataset is incomplete when rows are missing entirely (e.g., a constituent dropped from the feed) or when required columns contain nulls.

Silent row loss is the hardest quality failure to detect

If your pipeline silently drops rows during ingestion (e.g., a malformed CSV line), downstream aggregates look plausible but are wrong. A 50-constituent index calculated from 49 prices is published before anyone notices.

Assert Row Counts Against Expected Baseline

After every ingestion load, compare the incoming row count against the prior-day count (or a source manifest if available). Fail the pipeline if the count falls below 80% of the prior load. Also assert NOT NULL on all required columns before any rows leave bronze. Use a source manifest or API metadata endpoint to get the authoritative expected count when the source provides one.

How to detect: Compare incoming row counts against expected counts (prior day, reference dimension, source manifest). Assert non-null on required columns.

Uniqueness — no unwanted duplicates

Uniqueness means each entity appears exactly once per grain. Duplicate rows inflate aggregates, double-count weights, and corrupt joins.

Duplicate rows silently corrupt every downstream aggregate

A duplicated price row doubles a constituent’s weight contribution in a market-cap-weighted index. The index level shifts, and because the number looks reasonable, no human catches it until an investor reconciles against the exchange.

Assert Uniqueness on Natural Keys Before Every Transform

At the silver quality gate, assert that the natural key (instrument + trade_date, or equivalent) is unique before any aggregation or join runs. Use dbt_utils.unique_combination_of_columns in dbt and a Polars df.is_duplicated().any() assertion in Python. If duplicates exist, route the batch to quarantine and investigate the source before allowing any downstream run.

How to detect: Assert uniqueness on natural keys (instrument + trade_date). Hash-based dedup on composite keys.

Validity — data conforms to business rules

Validity means values fall within acceptable domains and pass business logic rules. Prices must be positive. Weights must sum to 1.0. ESG scores must be 0-100.

Business rules that live only in someone's head are never enforced

If the rule “weights must sum to 1.0” is documented in a wiki but not coded as an assertion, it will eventually be violated. Encode every business rule as a testable assertion.

Encode Every Business Rule as a Tested Assertion

For each business rule, write a corresponding dbt custom test (a SQL query that returns rows on failure) or a Python assertion. Examples: a tests/assert_weights_sum_to_100.sql that fails if any index’s weight deviates beyond 0.5%, and a dbt_utils.expression_is_true check that open_price <= high_price. Store these tests in version control alongside the models they protect.

How to detect: Range checks, regex patterns, enum membership, cross-field logic (e.g., open ⇐ high, low ⇐ close).

Timeliness — data arrives within SLA

Timeliness means data is available when downstream consumers need it. Late data delays index publication, triggers SLA breaches, and may force fallback to stale values.

A pipeline that succeeds with stale data is worse than one that fails

If your pipeline runs on schedule but processes yesterday’s file because today’s hasn’t arrived, you publish stale index values with no alert. Always assert data freshness, not just pipeline completion.

Assert Data Freshness Separately from Pipeline Completion

After every load, assert that MAX(trade_date) or MAX(loaded_at) in the target table is within the SLA window (e.g., today’s date for a daily pipeline). Use a dbt source freshness check in CI and a Python assert max_date >= today - timedelta(days=1) assertion in the production Airflow task. A dead man’s switch (an alert that fires if no successful load is recorded by the SLA deadline) catches the case where the pipeline never ran at all.

How to detect: Compare max timestamp in the dataset against expected freshness SLA. Implement dead man’s switch for expected-but-missing loads.

Three Types of Freshness

Source freshness: when the source system last updated the data. Pipeline freshness: when the pipeline last successfully processed the data. Serving freshness: when the consumer last received updated data. A pipeline can be “fresh” (ran on time) while serving stale data (the source was late). Monitor all three independently.

Accuracy — data values are correct

Accuracy means recorded values match the real-world truth. A price of 150.00 is complete, unique, valid, and timely, but if the actual close was 151.00, it is inaccurate.

Accuracy is the hardest dimension to automate

You cannot validate accuracy without an independent reference source. For financial data, corroborate against a second vendor, an exchange API, or a manual check for high-impact values.

Use Statistical Anomaly Detection Plus Cross-Vendor Corroboration

Apply a rolling z-score (30-day window, 3-sigma threshold) to flag price and score values that deviate unusually from recent history. For high-impact values (index levels, published weights), cross-check against a second vendor or the exchange API as part of the gold quality gate. Flag anomalies for manual review rather than auto-failing — a genuine market event can produce a valid 5-sigma move.

How to detect: Cross-reference against independent sources. Statistical anomaly detection (z-score) to flag outliers for manual review. Reconciliation queries between systems.

Consistency — data agrees across systems

Consistency means the same logical entity has the same value in every system that stores it. SQL Server gold must match BigQuery published values. Dimension attributes must agree across fact tables.

Cross-system inconsistency erodes trust faster than any other quality failure

When a client sees one index level on a website and a different level in a downloaded file, they lose confidence in all your data, even the parts that are correct.

Run Cross-System Reconciliation Queries After Every Publication

After each export from SQL Server gold to BigQuery, run a reconciliation query comparing COUNT(*), SUM(index_level), and MIN/MAX(trade_date) between both systems. Hash the entire gold table using hashlib.sha256 on a sorted export and store the hash as a pipeline artifact. Any mismatch halts publication until the source of the discrepancy is identified and resolved.

How to detect: Reconciliation queries comparing row counts, checksums, and key aggregates across systems. Hash-based comparison of entire datasets.

Quality Gates by Medallion Layer

Each medallion-architecture layer has different quality priorities. Bronze gates protect ingestion integrity. Silver gates enforce business rules. Gold gates guard publication correctness.

Bronze Quality Gate

Bronze (medallion-architecture > Bronze (Raw)) validates that raw data landed correctly before any transformation.

Tier 1 — Critical (halt pipeline)

  • Schema conformance: column names and types match expected contract
  • File hash verification: SHA-256 matches source manifest
  • Zero-byte / empty file detection

Fix: Halt and Quarantine on Tier 1 Failure

If schema conformance fails, do not load any rows — raise a SchemaDriftError and quarantine the entire file. Verify the SHA-256 hash against the source manifest before opening the file. Detect zero-byte files with a pre-check (os.path.getsize) and alert immediately. Resume only after the upstream issue is confirmed resolved.

Tier 2 — Standard (quarantine bad rows)

  • Null rate exceeds threshold on required fields (>5%)
  • Duplicate detection on natural keys
  • Malformed rows (parse failures, encoding errors)

Fix: Separate Good and Bad Rows, Continue with Good

Split the batch into passing and failing rows. Write failing rows to the quarantine table with failure_reason, gate_name, and rejected_at metadata. Load the passing rows downstream so the pipeline continues without the bad rows. Alert the data team and set a replayed_at target of < 24 hours.

Tier 3 — Advisory (log for review)

  • Row count outside 80-120% of prior load
  • New enum values not in reference table
  • Column order changed (schema evolution signal)

Silver Quality Gate

Silver (medallion-architecture > Silver (Cleaned)) enforces business rules and referential integrity on cleaned data.

Tier 1 — Critical (halt pipeline)

  • Business rule violation on mandatory fields (price ⇐ 0, negative volume)
  • Referential integrity failure (instrument not in dimension table)
  • Deduplication check fails after cleaning

Fix: Halt Silver Pipeline on Critical Business Rule Failures

If any price ≤ 0 or volume < 0 rows survive bronze, halt the silver pipeline and quarantine the affected instruments. Validate referential integrity with a LEFT JOIN against the dimension table before any fact transform — reject rows with no matching dimension entry. Re-run deduplication and assert uniqueness before promoting to silver; if duplicates persist, investigate the bronze load strategy.

Tier 2 — Standard (quarantine bad rows)

  • Values outside expected statistical range (z-score > 3)
  • Staleness: data older than freshness SLA
  • Cross-field logic violations (open > high, low > close)

Fix: Quarantine Outliers and Cross-Field Violations

Compute a 30-day rolling z-score per instrument; quarantine rows where |z| > 3 for manual review. Assert open_price <= high_price AND low_price <= close_price with a dbt expression_is_true test. For stale rows, check MAX(trade_date) against the SLA before running transforms — fall back to T-1 values only when explicitly configured, and log every fallback occurrence.

Tier 3 — Advisory (log for review)

  • Rows requiring fallback to T-1 values
  • Minor schema drift (new nullable columns)
  • Data distribution shift beyond 1 standard deviation

What Is Schema Drift?

Schema drift occurs when a data source changes its schema without notice — a column is renamed, a type changes, a new field appears, or a field disappears. The pipeline’s contract expects the OLD schema. The source delivers the NEW schema. Without detection, the pipeline silently loads NULLs (renamed column), fails mid-transform (type change), or ignores new data (unknown column). Detection methods: compare incoming columns against the contract, hash the schema, alert on mismatch.

Gold Quality Gate

Gold (medallion-architecture > Gold (Analytics)) is the last line of defense before data reaches clients, regulatory filings, and downstream systems.

Gold is publication — treat every Gold check as a circuit breaker

If a quality gate at the Gold layer fails and the pipeline continues anyway (e.g., because the check was set to severity: warn instead of error), incorrect index values reach clients and regulatory filings. Gold-layer checks that affect publication integrity must ALWAYS halt the pipeline. See esg circuit breaker fired for a real incident where this saved us.

Set All Gold-Layer dbt Tests to severity: error

In every gold model’s schema.yml, set config: severity: error on every test — not warn. Use a ShortCircuitOperator in the Airflow DAG after dbt test so that any single gold test failure halts publication immediately. Never route gold-layer quality failures to a log-and-continue path.

Tier 1 — Critical (halt publication)

  • Weights sum to 1.0: ABS(SUM(weight) - 1.0) < 1e-9
  • No missing constituents: count matches target (e.g., 50)
  • Index level sanity: daily change within +/-15%
  • Cross-dataset consistency: SQL Server gold = BigQuery published

Fix: Implement Weight and Constituent Circuit Breakers

Assert ABS(SUM(weight_pct) - 1.0) < 1e-9 per index_code + trade_date in a custom dbt test. Assert COUNT(DISTINCT instrument_isin) = target_count for each index. Compare the calculated index level against the prior day’s level and reject if the daily change exceeds ±15%. Cross-check SQL Server gold vs BigQuery row count and checksum before publishing to any downstream consumer.

Tier 2 — Standard (quarantine and alert)

  • ESG score outside normalized 0-100 range
  • Sector allocation drift beyond threshold
  • Turnover exceeds rebalance limits

Fix: Alert and Hold on Tier 2 Gold Failures

For ESG scores outside 0–100, quarantine the affected instruments and send an immediate alert to the data team — do not publish affected scores until reviewed. For sector drift and turnover threshold breaches, compare against the prior rebalance snapshot and hold publication pending a manual sign-off from the index operations team.

Tier 3 — Advisory (log for review)

  • Minor rounding differences across systems (<1e-6)
  • Constituent weight below minimum threshold
  • Publication timestamp later than typical

Data Quality Tooling

dbt Tests — SQL assertions in YAML

Best for: Schema validation, business rule checks, referential integrity in warehouse-centric pipelines.

Strengths: Version-controlled with models, runs in CI/CD, built-in severity levels, test failure storage for audit.

Limitations: SQL-only (no Python logic), limited statistical capability without dbt-expectations, test runs add warehouse cost.

Anti-pattern: writing dbt tests that duplicate warehouse constraints

If your warehouse enforces NOT NULL and UNIQUE via DDL constraints, dbt tests on the same columns are redundant cost. Use dbt tests for business rules the warehouse cannot enforce.

Reserve dbt Tests for Business Logic the Warehouse Cannot Enforce

Audit your schema.yml for not_null and unique tests on columns already covered by DDL NOT NULL and UNIQUE constraints. Remove the duplicates. Instead, invest dbt test slots in cross-field logic (expression_is_true), range checks (accepted_range), referential integrity (relationships), and aggregate rules (weight sums, constituent counts) that no DDL constraint can express.

Great Expectations — Python assertion suites with profiling

Best for: Statistical profiling, data documentation, complex multi-column expectations that are awkward in SQL.

Strengths: Rich expectation library, auto-profiling to bootstrap suites, data docs for stakeholder visibility.

Limitations: Heavy dependency footprint, slower than native SQL checks, checkpoint configuration complexity.

Anti-pattern: auto-profiling in production without review

Great Expectations can auto-generate expectations from data. If you deploy auto-profiled suites without human review, you encode current data quirks as rules — including bugs. Always review and curate generated expectations.

Treat Auto-Profiled Suites as a Draft, Not a Final Rule Set

Run great_expectations suite new --profile against a representative data sample to generate the initial expectation suite, then manually review every generated expectation before committing it to the repository. Remove expectations that reflect current bugs or data anomalies. For each expectation kept, add a comment explaining the business reason so future reviewers understand its intent.

Soda Core — YAML-defined checks with SodaCL

Best for: Quick setup across multiple data sources, team-friendly YAML syntax, Soda Cloud dashboards for non-technical stakeholders.

Strengths: Multi-source (SQL, Spark, Pandas), SodaCL is readable by analysts, anomaly detection built in.

Limitations: Advanced checks require Soda Cloud (paid), fewer community extensions than dbt or GE.

Custom SQL / Python — stored procedures and scripts

Best for: Legacy systems, highly specific edge cases, environments where adding a framework is impractical.

Strengths: Full flexibility, no dependency overhead, can run in any environment.

Limitations: No standardization, no built-in reporting, maintenance burden grows with pipeline count.

Dataplex Quality (GCP) — native BigQuery quality scans

Best for: BigQuery-centric pipelines on GCP, teams already using Dataplex for data governance.

Strengths: Zero infrastructure to manage, native BigQuery integration, results in Cloud Monitoring.

Limitations: GCP-only, limited custom logic, no cross-cloud support.

When to Combine Tools

No single tool covers all six quality dimensions. A practical stack for financial pipelines:

LayerToolCovers
Bronze ingestionCustom Python (Pydantic + Polars)Completeness, Validity, Uniqueness
Silver / Gold warehousedbt tests + dbt-expectationsAll six dimensions in SQL
Cross-system reconciliationCustom SQL / PythonConsistency, Accuracy
Monitoring and alertingGCP Dataplex + Cloud MonitoringTimeliness, Completeness

Start with dbt tests and custom Python gates, then add Great Expectations or Soda only when you need statistical profiling or multi-source checks that justify the extra dependency.

The Quarantine Pattern

What Is a Quarantine?

A quarantine isolates rows that fail quality checks so they can be investigated and replayed without blocking the pipeline. Good rows continue downstream; bad rows are persisted with failure metadata.

Never silently drop bad rows

Dropping rows that fail validation means you lose evidence of upstream data issues. Quarantined rows are your forensic trail: they tell you what went wrong, when, and how often. Without quarantine, you discover data loss only when a client reports it.

Always Route Rejected Rows to the Quarantine Table

In every quality gate, split the batch into passing and failing rows using a Polars filter or a SQL CASE expression. Insert failing rows into bronze.quarantine with source_table, source_row_json, failure_reason, gate_name, and rejected_at. Never use DROP, DELETE, or silent filtering — every rejected row must be traceable and replayable.

Quarantine Table Design

Every quarantine table needs the original row, the failure reason, and enough metadata to replay.

-- Quarantine table DDL — one per source or shared across Bronze
CREATE TABLE bronze.quarantine (
    quarantine_id   INT IDENTITY(1,1) PRIMARY KEY,
    source_table    NVARCHAR(128)   NOT NULL,
    source_row_json NVARCHAR(MAX)   NOT NULL,
    failure_reason  NVARCHAR(512)   NOT NULL,
    gate_name       NVARCHAR(128)   NOT NULL,
    rejected_at     DATETIME2       NOT NULL DEFAULT SYSUTCDATETIME(),
    replayed_at     DATETIME2       NULL
);

Reject-Persist-Investigate Workflow

  1. Reject — Quality gate identifies failing rows and separates them from the good batch
  2. Persist — Failing rows are written to the quarantine table with failure metadata (reason, gate name, timestamp)
  3. Investigate — Data engineers query the quarantine table to diagnose root cause (bad source, schema drift, business rule change)
  4. Fix — Correct the upstream issue or update the validation rule
  5. Replay — Re-ingest corrected rows through the pipeline; mark replayed_at in quarantine

Quarantine Metrics

Track these metrics to measure data quality over time:

  • Quarantine rate — percentage of rows rejected per load (target: <1% for Bronze, <0.1% for Silver)
  • Mean time to replay — how long quarantined rows sit before investigation (target: <24 hours)
  • Repeat offenders — source_table + failure_reason combinations that recur (signals upstream fix needed)
  • Quarantine growth — if the table grows faster than replays, investigation is falling behind

Replay Pattern

Replaying quarantined rows must be idempotent. The replay process re-ingests rows through the same pipeline (not a direct insert into Silver/Gold) so all quality gates run again.

Mark replayed rows, don't delete them

Set replayed_at on successfully replayed rows instead of deleting them. The quarantine table is an audit log; deleting rows destroys the quality history.

Anomaly Detection for Financial Time Series

Statistical anomaly detection catches data issues that pass business rule validation but are still wrong (e.g., a price that is positive and within range but 10x the prior day’s close).

Z-score formula:

# Rolling z-score for anomaly detection
z_score = (value - rolling_mean) / rolling_std
is_anomaly = abs(z_score) > threshold

Window size guidance:

Data typeWindowThresholdRationale
Prices (daily close)30 trading days3 sigmaPrices are relatively stable; 3 sigma catches true outliers
Volumes (daily)90 trading days2 sigmaVolumes are noisier; 2 sigma with longer window smooths seasonal patterns
Row counts (per load)30 loads2 sigmaCatches ingestion anomalies without over-alerting

Seasonal adjustment is critical for volume data

Trading volumes spike predictably around index rebalance dates, options expiry, and quarter-end. A naive z-score flags every predictable spike as anomalous. Either exclude known event dates from the rolling window or use a seasonal decomposition model.

Exclude Known Event Dates from the Rolling Window

Maintain a reference table of known high-volume event dates (quarterly rebalance dates, options expiry dates, index reconstitution dates). When computing the rolling z-score for volume anomaly detection, filter out these dates from the lookback window using a LEFT JOIN anti-pattern: WHERE trade_date NOT IN (SELECT event_date FROM ref.known_volume_events). This eliminates false positives on predictable spikes while retaining sensitivity to genuine anomalies.

Quality Gate Orchestration

Airflow Integration — ShortCircuitOperator as quality gate

Use Airflow’s ShortCircuitOperator to implement quality gates as pipeline tasks. If critical checks fail, the operator returns False and skips all downstream tasks, preventing bad data from reaching publication.

# Airflow ShortCircuitOperator quality gate
quality_gate = ShortCircuitOperator(
    task_id="bronze_quality_gate",
    python_callable=run_quality_checks,
)

ShortCircuitOperator vs BranchPythonOperator

Use ShortCircuitOperator when failure means “stop everything.” Use BranchPythonOperator when failure means “take an alternate path” (e.g., quarantine and continue with good rows).

GitHub Actions Integration — dbt test in CI/CD

Run dbt test --select state:modified+ on every pull request to catch quality regressions before they reach production. Only modified models and their downstream dependents are tested, keeping CI fast.

# GitHub Actions step for dbt quality checks
- run: dbt test --select state:modified+ --defer --state prod-manifest/

SLA Definitions by Dataset

DatasetFreshness SLAQuality ThresholdFallback
Market data (OHLCV)T+0 by 18:30 UTC100% completenessExchange backup feed
ESG scoresT+0 by Monday 08:00 UTC95% coverageUse T-1 scores
Corporate actionsT-1 by 06:00 UTC100% mandatory actionsManual sourcing
Index levels (published)T+0 by 19:00 UTC100% accuracyHold publication
Reference data (dimensions)T-1 by 04:00 UTC100% completenessUse prior version

SLAs are per-dataset, not per-pipeline

A single pipeline may load multiple datasets with different SLAs. Define freshness and quality thresholds at the dataset level, then map pipeline tasks to the strictest SLA they serve.

Graph View

Backlinks