How we balance rows between Bronze, Silver and Gold

We built a small receipts pipeline, broke it on purpose, and watched its row counts still add up. One receipt had gone missing between two stages and another had been counted twice, so the totals balanced and the report looked right. The check that caught it asks a different question: did every record that arrived end up in exactly one place?

The pipeline is a synthetic example we built beside our plant-reporting demonstration, which takes sample Business Central receipts and scale-house records through a Databricks lakehouse. It keeps data in the three stages Databricks describes. Bronze holds the data as it arrived, Silver the cleaned and checked version, and Gold the daily summary a report reads. Rows drop out at every stage for good reasons, and a lost receipt drops out the same way. A count can't tell the two apart.

So we ran the example locally in DuckDB as built, then five more times, each broken a different way. Our checks caught all five breaks, but in the case above only one of our three checks noticed anything was wrong.

What one row means at each stage

Before any check could mean something, we had to write down what one row stands for at each stage and who answers for it. Without that, nobody can say whether a smaller number is a problem or the pipeline doing its job.

The distinction that mattered most is between a delivery and a receipt. A source system can send the same receipt twice, so every delivery gets its own arrival ID, raw_row_id, as well as the receipt number. With only the receipt number, a resent file either looks like new business or vanishes without a trace. With both, we can say it was a repeat and skip it on purpose. Silver's key is the business identity, (plant_id, receipt_id), and every Silver row also carries the raw_row_id it came from.

We also record which batch of data each check ran against, the version of every table, the cutoff time and the time zone the business day uses, so a result can be rerun and explained later. None of this catches a receipt that never reached Bronze at all. That takes a separate check against the source system.

Where each of the twelve deliveries went

Our example has 12 deliveries over two days. Eight are good receipts, two fail validation (one has no receipt number, the other a weight that isn't positive), and two are exact repeats of good receipts. So the right answer is eight receipts in Silver, two set aside for repair, which the pipeline calls quarantine, and two repeats skipped: 12 = 8 + 2 + 2.

The order of those steps is a decision, not a detail. We validate first, then skip exact repeats in a fixed arrival order, so the same input always keeps the same copy and a rerun gives the same answer. A receipt that comes back with the same number and different content is a change, and it needs an update or conflict rule of its own. Our example doesn't handle changes like that (it has no change data capture), and it doesn't look for repeats across separate batches.

Every outcome keeps the delivery's arrival ID. Anything set aside or skipped also keeps its reason, such as missing_receipt_id or exact_replay, so a person reading it later knows why it isn't in the report.

That sum is the obvious check, and the broken version passed it. Silver had lost receipt b07 and held a second copy of b01, so it still had eight rows and the sum still read 12 = 8 + 2 + 2. Gold summarised the wrong Silver faithfully, so Gold matched Silver too. What caught it was a count per arrival ID of the places each one ended up: b07 in none, b01 in two, so the run returned HOLD.

Here's that check in SQL. A healthy run returns no rows, and every row it does return is a delivery that went missing or was counted more than once:

WITH expected AS (
  SELECT raw_row_id, COUNT(*) AS n
  FROM bronze_receipts GROUP BY raw_row_id
), actual AS (
  SELECT raw_row_id FROM silver_receipts
  UNION ALL SELECT raw_row_id FROM quarantine_receipts
  UNION ALL SELECT raw_row_id FROM excluded_receipts
), counted AS (
  SELECT raw_row_id, COUNT(*) AS n FROM actual GROUP BY raw_row_id
)
SELECT COALESCE(b.raw_row_id, a.raw_row_id) AS raw_row_id,
       COALESCE(b.n, 0) AS bronze_occurrences,
       COALESCE(a.n, 0) AS output_occurrences
FROM expected b FULL OUTER JOIN counted a ON b.raw_row_id = a.raw_row_id
WHERE COALESCE(b.n, 0) <> 1 OR COALESCE(a.n, 0) <> 1;

It joins the three outcomes with UNION ALL rather than UNION, because UNION quietly removes duplicates, and a duplicate is exactly what we're looking for. Our test runner also confirms that each outcome follows the validation rules and that accepted values reached Silver unchanged. The check assumes one delivery makes one output. Where one record properly becomes several, such as an order split into line items, you'd check instead that each parent produced the children it should.

If you use Lakeflow expectations, the rules a Databricks pipeline applies to each record, don't add up their failure counts to get the number of rejected records. A record that breaks two rules is counted under both, so the sum overstates it, and the pipeline event log reports dropped records separately. Expectations keep, drop or fail a record, and setting one aside for repair needs its own routing.

Checking the daily totals

Gold is supposed to have fewer rows than Silver, because it holds one line per plant per day. Our eight receipts became two lines: four receipts and 61.25 tonnes on October 27, and four receipts and 65.25 tonnes on October 28. Comparing row counts between Silver and Gold would flag that correct report as broken.

So we compare SUM(receipt_count) and net weight at Gold's full grain, (plant_id, business_day). The grain is every column that defines one Gold row, and checking at anything coarser lets errors cancel out. The example stores weight in whole kilograms, 126,500 in all, so the totals match exactly. With decimal weights from a real source, the team has to agree how to round them before anyone decides how close is close enough.

The per-day comparison earned its place in another broken version, where we moved October 27's line to the wrong date. The total weight was still 126.50 tonnes and the receipt count was still eight, so a grand-total check passed it. Comparing day by day caught it.

Any one of the three checks failing returns HOLD, and only the unbroken version passed all three. We ran all six versions with DuckDB 2.0.0 on our own machines. We haven't run the SQL on Databricks yet, and in the example a failed check doesn't stop a report from publishing.

A balanced report can still be incomplete

Even when every check passes, the two receipts set aside aren't in Gold's total. So we call the number accepted receipt weight and keep the period open until the report's owner can close it. That means the source has sent everything, late deliveries have had their agreed window, and every set-aside receipt has been fixed or knowingly accepted.

We give each set-aside receipt an owner, a deadline for fixing it and a rule for how long it's kept. When one is fixed and run again, we record a new attempt linked to the original delivery, so the first outcome stays on record. If a failed check has to stop a report going out, it runs as its own step that publishing waits on, because a validation table inside one Lakeflow pipeline doesn't hold back the tables after it.

Where we'd start

We'd start with the one Gold number your plant or finance team reads first. Write down what it counts, which records belong in it, its cutoff and who owns it. Then run the per-delivery check against the data behind it and compare it plant by plant and day by day before the period closes. If the sum balances and that check still returns rows, the sum was hiding something.

Frequently asked questions

What does a matching row count miss?

A receipt that goes missing while another is counted twice. In our example Silver lost b07 and gained a second b01, so it still held eight rows and the balance read 12 = 8 + 2 + 2. Counting where each arrival ID ended up, with UNION ALL so duplicates stay visible, caught both.

Should Gold's row count match Silver's?

No. Gold has one row per plant and business day, so eight receipts became two rows in our example. Compare the summed receipt count and weight for each plant and day instead, because a grand total passed the version that moved a day's receipts to the wrong date.

Can expectation failure counts stand in for rejected records?

No. One record can break several expectations, so adding up the failures per rule overstates how many were rejected. The Lakeflow event log reports dropped records separately from failures per rule, and setting records aside for repair needs its own routing.

Does a PASS mean the period's totals are final?

No. A PASS means every delivery has exactly one outcome and Gold matches Silver. The two set-aside receipts are still unresolved, so Gold reports accepted receipt weight, and the period stays open until the report's owner closes it.

Does the check work when one record becomes several?

Not as written. It expects exactly one output per delivery, so a join that turns one record into several needs its own rule for how many rows each parent should produce, and a check that every parent produced them.