YAML-Driven Databricks Reconciliation: A Data Engineer's Framework for Ingestion vs Lakehouse Validation

Reconciliation isn’t a query — it’s a framework. Every data engineer has written a one-off COUNT(*) comparison between two tables. That works once. It does not work across fifty tables, three layers, and five Systems of Record. For Brie AML (Ingestion to Lakehouse), a reusable SQL validation framework on Databricks runs automatically and proves every pipeline correct.

Why pipelines need built-in reconciliation

Data pipelines fail silently. An upstream schema change, a dropped partition, a timezone bug — each one can corrupt downstream data without raising a single error. In regulated industries like banking and healthcare, silent corruption is a compliance incident waiting to happen.

The rule on every data engineering project: every pipeline ships with its reconciliation. If you can’t prove it, you can’t promote it. Reconciliation is not a separate QA step. It is part of the pipeline itself.

The three-layer validation pattern

Validation runs at three layers, because each layer catches different failure modes:

1. Ingestion layer — schema conformance, file completeness, row counts vs source. Catches upstream breakage before it propagates.
2. Processing layer — business rule checks, key uniqueness, referential integrity. Catches transformation bugs.
3. Consumption layer — downstream report consistency, aggregate agreement with source systems. Catches everything the earlier layers missed.

-- Layer check: ingestion vs lakehouse row counts and key coverage
SELECT
  'ingestion' AS layer, COUNT(*) AS rows, COUNT(DISTINCT claim_id) AS keys
FROM ingestion.brie_aml_claims WHERE dt = '{{ ds }}'
UNION ALL
SELECT
  'lakehouse' AS layer, COUNT(*) AS rows, COUNT(DISTINCT claim_id) AS keys
FROM lakehouse.brie_aml_claims WHERE dt = '{{ ds }}';

Counts alone are not enough. The framework also compares key coverage, hash totals on amount fields, and null rates — because a pipeline can have the right row count and still be wrong.

YAML-driven thresholds

Hard-coding validation thresholds in notebooks breaks the moment a new table onboards. Instead, every check reads its thresholds from YAML:

table: brie_aml_claims
checks:
  - name: row_count_parity
    max_diff_pct: 0.0
  - name: amount_hash_parity
    max_diff_pct: 0.0
  - name: null_rate
    column: provider_id
    max_null_pct: 0.5

Zero tolerance on counts and hashes for financial data; small tolerances on null rates where upstream data is legitimately sparse. The same notebook runs every table — only the YAML changes.

Mismatch detection and alerting

When a check fails, the framework generates a mismatch report with the exact keys involved — not just “counts differ by 47” but “these 47 claim IDs are missing from the lakehouse layer.” That report goes to the data engineering team by email, and the pipeline run is marked failed so nothing downstream consumes bad data.

This exact pattern runs in production for Brie AML reconciliation and for banking SOR reconciliation with Airflow. The pattern is the same everywhere: validate every layer, configure in YAML, alert with context.

Steal this for your pipelines

• SQL validation notebooks comparing counts, keys, and hashes across layers.
• YAML job configs for dynamic thresholds per table and SOR.
• Mismatch detection + email reports for auditability.

Building data pipelines that need reconciliation? Let’s talk — reconciliation-first pipelines are the core of this data engineering work.