System design interview

Data Quality Framework for 500 Tables

HardLocked55 min read

DQ framework for 500 tables / 200 pipelines: YAML contracts, GX/dbt tests, Grafana, circuit breakers.

system-designdata-qualityobservabilitygovernance

Start here

The problem in plain words

A pipeline quietly publishes a revenue table with a null in the amount column for one day. Nothing fails. No red box appears in the scheduler. The next morning the dashboard shows revenue that is lower than it should be, and an executive makes a decision on it.

Silent wrong data is more damaging than a loud failure. A failed job makes someone investigate. A wrong number gets used. Data quality work is about making wrongness loud, and for the most important tables, impossible to publish.

With 500 tables and 200 pipelines you cannot hand-write checks for each one. You need a framework: agreements written as code, tests that run automatically, alerts that reach the right person, and a way to stop bad data before it reaches users.

Words you will meet

Data quality (DQ)
Whether data is correct, complete, unique, on time and in the expected shape.
Data contract
A written agreement about a table: its columns, types, allowed values, freshness and owner.
Contract as code
Keeping those agreements in files such as YAML in git, so they are reviewed and versioned like software.
Test / check
An automatic rule such as "amount is never null", "order_id is unique" or "data is under 2 hours old".
Freshness
How recent the latest data in a table is.
CI
Continuous integration: automatic checks that run when someone proposes a change.
Circuit breaker (fail-closed)
Stopping a bad table from being published when a critical check fails, like a fuse that trips.
Severity
How serious a failure is. A warning is noted, a halt stops the publish.
Tier-0 table
A critical table whose errors would hurt the business, such as revenue.
Alert fatigue
People ignoring alerts because too many are false or unimportant.
A wrong number that nobody sees fail
publishwrongPipelineruns greenRevenue tablenull amounts for a dayExecutivedecides from the chart
Press Play to watch it move

Each arrow is one thing happening. Play it step by step, or read the list below.

  1. 1.The job succeeds, so the scheduler shows green, and publishes the table. One column has nulls that should not be there.
  2. 2.The dashboard shows lower revenue. Nobody was alerted, and a decision gets made on bad data.

The numbers, as things you can picture

  • 500 tables, 200 pipelinesToo many to treat equally. Start with the few that matter most.

What the interviewer wants

Design a quality framework that scales: contracts as code, tests that run in every pipeline, monitoring over time, severity levels, and a way to block bad data from being published.

The interview room

Design a data quality framework for 500 tables and 200 daily pipelines with contracts-as-code, pipeline execution, monitoring, and circuit breakers that halt critical publishes.

Interviewers have watched optional DQ dashboards while finance ate bad data. They want trust tied to publish.

Basics

Quality dimensions: freshness, completeness, uniqueness, validity, consistency, accuracy. Schema-only is insufficient.

Wiki checklists do not scale to 200 pipelines at 2 AM. Expectations must be executable and owned.

Contracts

YAML/dbt/GX declarations with owner, SLA, tests, severity. Example halt contract on gold.fact_orders for PK/not-null/freshness.

Breakers and severity

Critical fail blocks consumer-visible publish; staging keeps evidence. Halt tier-0; warn lower tiers.

Prompt

Author, execute, monitor, gate, severity, gradual rollout, SLA-safe runtime, domain-authored tests.

Good looks like

Tier-0 first; contract→runner→results→gate→alerts; sampling strategy; flaky quarantine.

Clarify / scope

Tier-0 list, policy owners, engines, sampling. Out of scope: commercial DQ product fantasy.

Scoring lens: Data Quality Framework for 500 Tables

Interviewers reward incident-first teaching, a clear control loop, and named owners. Tool logos without that loop score poorly.

Weak vs strong answers on Data Quality Framework for 500 Tables

Weak: list products. Strong: tell the failure story, define the mechanism, draw the path, state rollout and monitors.

Scope control

Say what is out of scope for v1. Come back only if follow-ups demand it. This is a seniority signal for Data Quality Framework for 500 Tables.

Clarifying then committing

Ask a few high-use questions about SLAs, owners, and scale, then sketch. Endless questions without a diagram look evasive.

Results store and API

Store every test result with run_id, table, partition, test name, severity, status, metric, owner, and duration. Expose POST /runs and GET /runs/{id} so Airflow can gate publish on failed_critical. Without a results API, gates become brittle shell scripts.

Author experience

Ship tier templates. Domain engineers copy a Gold halt template, change column names, open a PR, and get CI validation. If authoring requires a platform ticket for every test, coverage dies.

Finance-grade example battery

For fact_orders: unique order_id on partition; not_null amount/currency; amount >= 0; freshness 4h; volume within weekday band; sample FK to dim_customer. That set prevents most silent revenue disasters without boiling the ocean.

Interview framing detail 1

For lb-sd-25, spend the first minutes making the problem concrete: who gets hurt when this fails, what SLA is implied, and what is explicitly out of scope. Then teach the prerequisite concept before proposing boxes. A candidate who names vendors first usually loses the plot. Write two clarifying questions on the board and answer them with assumptions if the interviewer shrugs. Keep the first diagram small enough to redraw when a follow-up changes a constraint.