Data Quality & Reconciliation

The seven data-quality dimensions with concrete checks, a stage-by-stage reconciliation framework, and diagnosing source-vs-target mismatches.

Same series as Parts 1–5. This topic tends to come up explicitly when the role touches financial, regulatory, or otherwise audited data — worth opening your answer by naming that context, since it signals you understand why the bar is higher here than on a generic analytics platform: a wrong number in a dashboard is an inconvenience, a wrong number in a financial report is a compliance and audit finding, potentially a restatement, and in some cases a legal exposure for the company.

THEORY PRIMER

Why financial/regulated data raises the bar

A generic analytics platform tolerates a certain amount of "close enough" — a dashboard that's 0.1% off for a day due to a late-arriving batch is rarely noticed and rarely matters. A financial reporting pipeline feeds numbers that get audited, filed, and legally attested to — the tolerance for silent drift is effectively zero, and more importantly, when something is off, you need to be able to prove exactly what happened, when, and why, not just "we fixed it." That shifts the engineering requirements: every stage needs to be reconcilable (you can prove input total = output total, or explain the delta), auditable (you can show what happened, not just that it happened), and idempotent/deterministic (rerunning must produce the same number, or reconciliation itself becomes unreliable).

The seven dimensions, at a glance

These aren't independent checkboxes — a pipeline can be 100% complete (every record arrived) and still fail accuracy (values are wrong) or integrity (an account references a customer that doesn't exist). A senior answer distinguishes between them explicitly rather than treating "data quality" as one blob.

Q&A — The Seven Dimensions, With Concrete Examples

Completeness

Q: How do you check completeness, concretely?

A: Compare expected record count against received/loaded count, ideally against an independent expectation (a source-provided control total, not just "however many rows landed") — a pipeline that silently drops rows and then measures completeness against its own output is checking nothing.

Expected records: 1,000,000
Received:            998,000

SELECT
  (SELECT COUNT(*) FROM source_control.expected_counts
   WHERE batch_date = CURRENT_DATE AND source_system = 'billing') AS expected,
  (SELECT COUNT(*) FROM bronze.billing_transactions
   WHERE batch_date = CURRENT_DATE) AS received,
  (SELECT COUNT(*) FROM bronze.billing_transactions WHERE batch_date = CURRENT_DATE)
    - (SELECT COUNT(*) FROM source_control.expected_counts
       WHERE batch_date = CURRENT_DATE AND source_system = 'billing') AS variance;

Q: You get 998,000 of an expected 1,000,000. Walk through how you'd diagnose the missing 2,000 before deciding whether it's safe to proceed.

A: First, is the "expected" number itself trustworthy — is it a hard control total the source system emits, or an estimate? Assuming it's real: check whether the shortfall is uniform (spread across all partitions/keys — points at a systemic upstream issue, like the source job itself failing partway) or concentrated (one specific partition/region/source-file missing entirely — points at a specific failed extract, a file that didn't land, a late-arriving file that just hasn't shown up yet). I'd check the source system's own logs/extract-completion markers before assuming it's my pipeline's fault, then decide: if it's confirmed late-arriving data with a known SLA, let it flow in on the next run rather than blocking (with reconciliation flagging the gap as "pending" not "failed"); if it's a genuine loss, halt downstream propagation past this point (fail the DQ gate from Part 1) rather than publish numbers I know are short by 0.2% into a financial report.

Accuracy

Q: Give a concrete accuracy check, distinct from completeness.

A: Completeness confirms the row arrived; accuracy confirms its values are right. Example: comparing a sampled set of transaction amounts against an independent source (the payment processor's own settlement report, or a manually verified subset) rather than just checking the pipeline's internal consistency, which would happily pass even if every row was individually wrong in the same way (a systematic unit conversion bug, e.g. cents vs dollars, is completeness-clean and consistency-clean but is an accuracy failure).

SELECT
  t.transaction_id, t.amount AS pipeline_amount, p.amount AS processor_amount,
  ABS(t.amount - p.amount) AS discrepancy
FROM curated.transactions t
JOIN external_processor_report p ON t.transaction_id = p.transaction_id
WHERE ABS(t.amount - p.amount) > 0.01
ORDER BY discrepancy DESC;

Consistency

Q: Give a concrete consistency check.

A: The same fact should agree wherever it's stated. Classic example in a financial pipeline: a customer's total account balance computed from the transaction ledger (SUM(amount) per account) should match the balance stored in the account-summary table that a different upstream system maintains independently.

SELECT
  l.account_id,
  l.ledger_balance,
  s.summary_balance,
  l.ledger_balance - s.summary_balance AS discrepancy
FROM (
  SELECT account_id, SUM(amount) AS ledger_balance
  FROM curated.transactions GROUP BY account_id
) l
JOIN curated.account_summary s ON l.account_id = s.account_id
WHERE ABS(l.ledger_balance - s.summary_balance) > 0.01;

Consistency failures often point at timing (one side updated, the other hasn't caught up yet — a legitimate lag, not a bug) vs a genuine calculation divergence (one side applies a fee/adjustment the other doesn't) — I'd check timestamps on both sides before treating every discrepancy as an error.

Validity

Q: Give a concrete validity check.

A: Values conform to defined business rules/domain constraints — not "is this the right number" (accuracy) but "is this even a legal value at all."

transaction_amount >= 0

SELECT *
FROM curated.transactions
WHERE transaction_amount < 0
   OR currency_code NOT IN (SELECT code FROM ref.valid_currencies)
   OR transaction_date > CURRENT_DATE   -- future-dated transaction, invalid
   OR account_type NOT IN ('CHECKING', 'SAVINGS', 'CREDIT');

Q: transaction_amount >= 0 — is this always the right rule? What's the trap in assuming it blindly?

A: Depends entirely on the domain semantics — if the table represents raw transaction events where a refund or reversal is legitimately negative, >= 0 is wrong and would incorrectly flag valid data. The correct rule might be "amount != 0" (no zero-value transactions) with a separate transaction_type column distinguishing debits from credits, or a rule scoped per transaction_type. I'd never accept a validity rule from a requirements doc without confirming it against actual legitimate edge cases in the domain — this is exactly the kind of rule that looks obviously right and is subtly wrong.

Uniqueness

Q: Give a concrete uniqueness check.

A: Each real-world entity/event should appear exactly once at the grain the table claims to represent.

SELECT business_key, COUNT(*) AS occurrences
FROM curated.transactions
GROUP BY business_key
HAVING COUNT(*) > 1;

Q: A uniqueness check on transaction_id passes, but you still suspect duplicate transactions. What's going on?

A: The uniqueness check is only as good as the key it's checked against — if transaction_id is generated per ingestion event rather than being a true stable business identifier, the same real-world transaction re-sent by the source (a retry, a re-export) gets a new transaction_id each time and sails through a naive uniqueness check while still being a duplicate at the business level. The right check needs the actual business key — e.g. (account_id, amount, transaction_timestamp, external_reference_id) or whatever the source system's true idempotency key is — not whatever surrogate ID the pipeline itself assigned on ingest.

Timeliness

Q: Give a concrete timeliness check.

A: Freshness/latency SLA — how old is the newest data relative to now, and did each stage complete within its expected window.

SELECT
  MAX(ingestion_timestamp) AS latest_record_loaded,
  CURRENT_TIMESTAMP() - MAX(ingestion_timestamp) AS data_lag
FROM bronze.transactions;
-- alert if data_lag exceeds the agreed SLA, e.g. 2 hours for near-real-time feeds

For a financial close process specifically, timeliness also means: did every required upstream source deliver before the reporting cutoff, not just "eventually, that day" — a source landing 10 minutes after the finance team pulled the report is a timeliness failure even though the data itself is otherwise perfect.

Integrity

Q: Give a concrete referential integrity check.

A:

Every account must reference an existing customer.

SELECT a.account_id, a.customer_id
FROM curated.accounts a
LEFT JOIN curated.customers c ON a.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Any row returned is an orphaned account — a foreign key with no valid parent. In a Delta/lakehouse context there's no database-enforced FK constraint doing this for you automatically (unlike a traditional RDBMS with declared foreign keys), so this has to be an explicit, scheduled DQ check, not something you can assume the platform guarantees structurally. (Unity Catalog does support declaring primary/foreign key constraints for documentation and some query optimization purposes, but — unlike an OLTP database — they're informational, not enforced on write, so they don't substitute for an actual DQ check.)

Q: What do you do when you find orphaned records like this in a financial pipeline — just drop them?

A: Never silently drop in a financial context — quarantine (route to a curated.accounts_orphaned holding table, exclude from the published Gold/reporting layer) and alert, because an orphaned account with real transaction activity is a data problem that needs a human decision (was the customer record deleted incorrectly? is this a timing issue where the customer hasn't loaded yet this run?) — not something the pipeline should unilaterally decide to discard or guess-fix.

Reconciliation Framework

This is the section to have fully rehearsed — expect a direct ask for it if the role is finance-adjacent.

Source
 ↓
Raw
 ↓
Transformation
 ↓
Curated
 ↓
Reporting

Q: Design a complete reconciliation framework across this pipeline.

A: At every stage boundary, capture the same fixed set of control metrics, and compare them stage-to-stage (not just start-to-end) so a discrepancy is localized to exactly the stage that introduced it:

• Record count
• Sum(amount) (or whatever the core financial measure is — sum, not just count, because count can match while amounts don't, e.g. a join fan-out corrupting values while leaving row count coincidentally intact for count-only checks)
• Distinct business keys (catches duplication that inflates count without changing distinct-key count, or the reverse — collapsed keys)
• Null counts (on critical columns — a transformation bug that nulls out a join column can silently break everything downstream)
• Duplicate counts
• Control totals — an independent, source-provided total (not computed from the pipeline's own data) checked against the pipeline's own count/sum at every stage

-- generic reconciliation snapshot, run identically at each stage
CREATE OR REPLACE TABLE audit.reconciliation_log AS
SELECT
  'curated' AS stage,
  CURRENT_TIMESTAMP() AS check_time,
  COUNT(*) AS record_count,
  SUM(amount) AS total_amount,
  COUNT(DISTINCT business_key) AS distinct_keys,
  SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS null_amount_count,
  COUNT(*) - COUNT(DISTINCT business_key) AS duplicate_count
FROM curated.transactions
WHERE batch_date = CURRENT_DATE;

Run the identical query shape at Raw, Transformation-output, Curated, and Reporting, land each result as a row in one audit.reconciliation_log table (partitioned by stage + batch_date), and diff consecutive stages automatically — a discrepancy between Raw and Transformation output localizes the problem to the transformation step specifically, rather than a vague "somewhere in the pipeline" finding that requires re-deriving stage-by-stage numbers by hand every time.

Q: How do you decide what tolerance is acceptable at each stage — does everything need to match exactly?

A: For financial control totals: exact match, zero tolerance — a $0.01 unexplained variance in a financial reconciliation isn't noise, it's a signal something is wrong (a rounding bug, a currency conversion issue, a dropped record), and it needs to be explained, not tolerance-banded away. For operational/non-financial metrics elsewhere in the same platform, some tolerance may be reasonable (a 0.5% variance in a marketing-analytics row count due to expected late-arriving events might be fine) — but I'm explicit that the financial numbers specifically get the zero-tolerance treatment, and I don't let a general "reasonable tolerance" policy from the rest of the platform bleed into the financial reporting path.

Q: How do you surface reconciliation failures — what does the actual operational response look like?

A: The reconciliation log feeds an automated check as part of the pipeline itself (a DQ gate task, per Part 1's failure-handling design) — any stage-to-stage variance beyond the zero-tolerance threshold blocks promotion to the next stage and pages/alerts a human, with the reconciliation log's row-level detail (which stage, which metric, what the expected vs actual was) attached so the on-call person isn't starting the investigation from scratch. I'd also keep a standing reconciliation dashboard (not just alert-on-failure) so finance/audit stakeholders can self-serve "prove this month's numbers reconcile end to end" without needing an engineer to run ad hoc queries every time an auditor asks.

"Source total ≠ target total" — Diagnosis

Q: Your reconciliation shows source total ≠ target total. Walk through how you'd diagnose it.

Source total = 10,542,320
Target total = 10,542,320

(when they don't match — walk the causes systematically rather than guessing):

Filtering: is there a WHERE clause anywhere in the pipeline — intentional or accidental — excluding rows that should be included? Check every transformation step's filter conditions against the business requirement; a filter added for a legitimate reason in one context (excluding test accounts) can silently also exclude legitimate production data if the condition is too broad.

Duplicates: inflated target total from rows counted more than once — a join that fans out (one-to-many where one-to-one was assumed) is the single most common cause of this in practice. Check COUNT(*) vs COUNT(DISTINCT business_key) at the suspect stage — a gap between them is the smoking gun.

Missing records: rows that simply didn't make it through — a failed partial write, a task that errored on a subset and got silently retried in a way that skipped rather than reprocessed the failed rows, or an upstream extract that itself was incomplete (loops back to the completeness dimension).

Late-arriving data: not actually an error — data legitimately hadn't arrived yet when target was computed, and will reconcile once it lands. Distinguish this from real loss by checking whether the "missing" records show up in a later run's source extract, or by checking source-side timestamps against the target's processing cutoff.

Transformation errors: a bug in the transformation logic itself — wrong aggregation grain, a currency conversion applied twice or not at all, a sign flip on debits/credits, a rounding function applied inconsistently. This is where the EXPLAIN/plan-level inspection from Part 5 and stepping through the transformation stage-by-stage with the reconciliation log's stage-to-stage diffs earns its keep — it tells you which transformation step introduced the discrepancy, not just that one exists somewhere.

Joins: an unintended cartesian product or fan-out (covered under duplicates), or the opposite — an inner join silently dropping rows that should have been kept via a left join, because a dimension lookup didn't have a matching row for every fact record.

Type conversions: a string-to-numeric cast that silently produces NULL for unparseable values instead of failing (Spark's default CAST behavior — CAST('abc' AS DOUBLE) returns NULL, not an error), a precision/scale mismatch that truncates decimal amounts, or a date/timestamp parse assuming the wrong format for a subset of source rows.

CDC problems: from Part 2 — duplicate change events not deduplicated correctly before merge, an out-of-order event processed after a newer one, a captured DELETE not correctly reflected, or a snapshot/full-load reconciliation baseline that was taken at a different point in time than the CDC stream's starting offset (double-counting or gap at the seam between an initial snapshot load and the CDC stream that's supposed to continue from it).

Closing structure for this answer: "I don't guess which of these it is — the reconciliation framework's stage-by-stage control totals tell me exactly which stage introduced the discrepancy, and then within that stage, count vs distinct-key vs null-count narrows it further: a distinct-key gap with matching count points at duplication or key-collapse, a null-count spike points at a type-conversion or join issue, and a clean count/key match with a wrong sum points at a transformation logic bug in the amount calculation specifically, not a row-level completeness problem at all."

Quick-fire recap