The load audit and reconciliation report I run on every new source feed

This is the load audit and reconciliation report I run before I trust a single number out of a new source feed: 6 rows arrive, 1 is a corrected duplicate, 2 are invalid, 3 load clean, and every dollar has to tie out at the end.

A correct query returns the right answer once, against the rows in front of it. This report exists because that is not the same as a reliable process, one that returns the right answer every time it runs:

• when it reruns on a batch it already loaded
• when the source sends the same order twice
• when a bad row shows up
• when a technician changes region halfway through the year
• when the job dies partway through

None of that shows up in a single query. It lives in the parts a query does not say out loud: deduplication, explicit rejection, a transaction boundary, history, and a way to prove the result after the fact. This report packages all five checks and runs them against a synthetic field-service company I invented for this walkthrough, an HVAC-style shop I reuse across a few of these posts. It is not a real client and not a business I operate. Every figure below is synthetic, so the numbers prove a method, not a business.

• Tip: I write these patterns in SQL Server style (T-SQL). DuckDB runs the same select, window, and join logic with small changes; the load transaction and the technician dimension's identity column need platform-specific rewrites, which I call out inline.

The report, at a glance

• Every row is accounted for on both ends: 6 staged rows resolve to 3 clean rows loaded, and the $820.00 in clean revenue going in matches the $820.00 sitting in the fact table.
• The duplicate-key check on the loaded fact table returns 0 rows, which is the one number in this report that is only right when it is exactly zero.

What the numbers say

• 6 staged rows resolve to 1 duplicate version dropped, 2 rejected rows, and 3 clean rows loaded to the fact table.
• Resolving WO-1001's duplicate is a $40.00 swing on that one order: $480.00 on the stale first version versus $520.00 on the corrected one. Only the revenue field moved between the two versions; direct cost held at $210.00 either way.
• 2 rows are rejected with a named reason each, WO-1003 for a missing customer and WO-1004 for negative revenue, and 0 rows are silently coerced into a null.
• Clean revenue in ($820.00) ties exactly to fact table revenue out ($820.00), and the duplicate-key check on the loaded fact table returns 0 rows.
• One technician carries two effective-dated region versions in the dimension, North through March 2 and South from March 3, so a work order serviced in early March still resolves to the region that was true then.

Why this matters to the business

• A silently kept duplicate is real money, not a rounding error. WO-1001's two source versions billed $480.00 and $520.00 for the same job. A dedup rule with no deterministic tie-breaker can land on either one from one run to the next, a $40.00 swing on a single order that nobody notices unless two runs happen to get compared by hand.
• An uncaught reject does not disappear, it becomes someone else's phantom gap. Route WO-1003 (no customer) or WO-1004 (negative revenue) through as a silent null instead of an explicit, reason-coded reject, and an analyst three layers downstream is left chasing a hole in a report with nothing pointing at the cause.
• A non-idempotent load double-bills on the retry that is supposed to save the night. A load that is not wrapped in one transaction, or that does not check for an existing key before inserting, can post the same work order a second time the moment a failed batch reruns at 2am, and the fact table ends up counting revenue that was never re-earned.
• Overwriting history rewrites a report that already closed. WO-1001 was worked in March by a technician who was in the North region at the time. Updating that technician's region in place instead of expiring the old version would make a March report, pulled again in April, quietly say South, months after it already went out the door.

The staged rows, audited

Read this like the source system's own extract, with the disposition added at the end:

ingest_id	work_order_id	service_date	customer_id	technician_id	status	revenue	direct_cost	disposition
1	WO-1001	2026-03-02	C-01	T-07	completed	$480.00	$210.00	Superseded: older version of the same order
2	WO-1001	2026-03-02	C-01	T-07	Completed	$520.00	$210.00	Kept: newest source version
3	WO-1002	2026-03-04	C-02	T-07	completed	$300.00	$145.00	Clean
4	WO-1003	2026-03-05	(missing)	T-09	completed	$275.00	$130.00	Rejected: missing customer
5	WO-1004	2026-03-06	C-03	T-09	completed	-$90.00	$40.00	Rejected: negative revenue
6	WO-1005	2026-03-07	C-04	T-11	cancelled	$0.00	$0.00	Clean

Two checkable facts I hold myself to for the rest of this report: one duplicate version to resolve, two invalid rows to reject.

The load, pattern by pattern

Six checks close the gap between a query and a process. Each one below names what it protects against, how I run it, and what it produces.

Keeping one version of each order

• Partition the staged rows by the business key, work_order_id, not by anything about who touched the row last.
• Rank each partition newest first, using the source's own last-updated timestamp.
• Break every tie with a strictly increasing load id, so two versions carrying the exact same timestamp still pick the same winner every time. Without that second key, the ranking is free to choose a different winner on the next run, a load that passes today and quietly drifts tomorrow.
• Fold obvious formatting drift, status arriving as "Completed" one day and "completed" the next, into one consistent value while resolving the winner, so casing differences never masquerade as a real change downstream.
• Keep exactly the top-ranked row per business key. Every other version drops out here, not later.

Output: the fixture table above already shows it. WO-1001's first version is superseded, the corrected version is kept, and the staged set drops from 6 rows to 5.

Tools I'd use: SQL Server for the window function; DuckDB runs the identical ranking logic with a small syntax change for the output table.

Rejecting bad rows out loud

• Every row that fails a business rule, a missing key, a negative dollar amount, gets a named reason code instead of quietly becoming a null.
• The reject set and the clean set are exact complements of the deduplicated rows: nothing is lost between the two, and nothing sits in both.
• The reject reasons become a first-class quality signal an analyst can query, instead of a mystery someone has to reverse-engineer three layers downstream.

Output, reject reasons and counts:

reject_reason	rows
MISSING_CUSTOMER	1
NEGATIVE_REVENUE	1

WO-1003 is missing a customer. WO-1004 has negative revenue. Both are named, both are counted, and both are still sitting in the reject table if anyone needs to go look.

A load that cannot half-apply

• The fact table is keyed on the same business key, so loading it means updating the rows that already exist and inserting the ones that do not, in that order.
• Both statements run inside one transaction. If either one fails, the whole batch rolls back, so the fact table never ends up holding half a batch.
• Running the identical batch twice changes nothing the second time: the update rewrites the same values, and the insert matches nothing new because every key already exists. That property, safe to rerun, is what lets me retry a failed batch without fear of double-counting.

Output, the same batch run twice:

Run	Rows updated	Rows inserted	Fact table rows after
First run	0	3	3
Rerun, same input	3	0	3

A single MERGE statement can express this same update-then-insert more compactly, and it reads well. I still write it as two explicit statements, because the locking is easier to reason about and because MERGE has a documented history of edge cases under concurrency that lead some teams to avoid it. Neither choice is wrong. The reliability comes from the transaction boundary, not from which statement draws it.

Tools I'd use: SQL Server's TRY/CATCH transaction; DuckDB wraps the same two statements in BEGIN TRANSACTION and COMMIT, with rollback handled from the client.

Tracking a technician's region without rewriting history

• When a technician changes region, expire the current dimension row instead of updating it in place, and insert a new row with its own effective date range.
• Every fact row resolves its historical dimension key by matching the work order's service date to the effective range that was true then, using a half-open interval so a boundary date lands in exactly one version.
• Overwriting the region in place would silently rewrite closed history: a March report pulled again later would show a region that only became true after March ended, which is the failure this pattern exists to prevent.

Output, the technician's own history:

tech_key	technician_id	region	effective_from	effective_to	is_current
1	T-07	North	2026-01-01	2026-03-03	0
2	T-07	South	2026-03-03	9999-12-31	1

Output, how the two orders resolve:

work_order_id	service_date	resolved region
WO-1001	2026-03-02	North
WO-1002	2026-03-04	South

WO-1001, dated 2026-03-02, resolves to North. WO-1002, dated 2026-03-04, resolves to South, four days after the technician moved. The fact points at what was true when the work happened, not what is true now.

Tools I'd use: an IDENTITY column in SQL Server; a sequence, or GENERATED ALWAYS AS IDENTITY, in DuckDB. The expire-then-insert logic itself is standard SQL either way.

Proving the load is right

• A control total ties the row count and the dollar amount of the clean rows going in to the fact rows coming out, so a load that silently drops or duplicates rows cannot hide.
• A duplicate-key check on the fact table has exactly one correct answer, zero rows, and it is written so a scheduled job can gate on that pass condition instead of a person eyeballing a dashboard.
• A reconciliation query nobody looks at is decoration. One with a pass condition a job can fail on is a control.

Output, the control totals:

Metric	Clean rows in	Fact rows out
Row count	3	3
Revenue	$820.00	$820.00

Output, the duplicate-key check: 0 rows returned. Pass.

Reading the plan without pretending to be a DBA

• Read the execution plan as an analyst, not as someone tuning a server, so the questions stay small: is a selective filter reading the whole table, and would an index change that.
• A scan reads every row; a seek jumps straight to the ones that match. On a table with no supporting index, even a selective filter shows a scan, which is fine on six rows and a real cost on six million.
• From a scan on a selective filter, the hypothesis is an index on the columns the filter actually uses, with the remaining columns included so the engine never has to go back to the table.
• I am not going to quote a before-and-after speedup number on a six-row table. Measuring is the discipline here, not the number: capture the plan, form the hypothesis, add the index, rerun, recapture the plan, and record the real scan-versus-seek result along with the logical-reads count. On this fixture there will not be a meaningful difference, and that absence is itself the honest result: the method only pays off at a size where a scan actually hurts. The index is not free either. It costs storage and it slows every insert and update that has to maintain it, so on a write-heavy table the read win has to justify the write cost.

Tools I'd use: SSMS or Azure Data Studio for the real execution plan and STATISTICS IO; DuckDB's EXPLAIN ANALYZE for the zero-install version.

What a local lab does not reproduce

These six patterns are the correct shape of a reliable load, and a small fixture is enough to prove the shape. Three things the shape alone does not exercise:

• Concurrency. One session running clean statements never surfaces lock contention, deadlocks, or the MERGE-under-load behavior that shaped the transaction choice above. Concurrent writers only get validated on the real platform.
• Production storage and volume. The index decision only pays off at a data size where a scan is actually expensive, and at that size partitioning, statistics, and physical layout enter the picture too. A six-row table cannot show which index earns its keep.
• High availability and disaster recovery. Replicas, failover, backup and restore, and point-in-time recovery are platform properties, not query properties. Nothing in a local lab tests them.

The lab's value is that every pattern is runnable and reproducible from one fixture, so the logic is checkable line by line. The three items above are exactly what I would carry into a real warehouse to validate next.

How I run this when I stand up a new feed

• Land the raw feed untouched first. Whatever the source sends, duplicate versions and bad rows included, gets captured before anything is cleaned.
• Deduplicate deterministically. One row per business key, newest source version wins, with an explicit tie-breaker (a monotonic load id) so a tie can never pick a different winner on a rerun.
• Reject bad rows out loud, with a reason code. Missing keys, negative amounts, anything that fails a business rule goes to a reject table with a named reason, never a silent null.
• Load inside one transaction that either fully applies or fully rolls back. Update the rows that already exist, insert the ones that do not, and confirm that running the same batch twice changes nothing the second time.
• Track anything that changes over time with an effective-dated history, not an in-place overwrite, for any dimension a fact table's history depends on: technician, price, region, ownership.
• Reconcile before trusting the load, with two checks: a control total that ties input rows and dollars to output rows and dollars, and a duplicate-key check whose pass condition is zero rows, wired so a scheduled job can gate on it.
• Read the plan before tuning anything. Confirm whether a selective filter is scanning or seeking, and only add an index once a real query, not a guess, says one would help. Then measure the actual before-and-after, not an assumed one.
• Write down what a small lab still cannot prove. Concurrency behavior under real load, whether an index earns its keep at production volume, and platform-level HA and disaster recovery all get validated on the real system, not the fixture.

Key takeaways

• A correct query returns the right answer once. A reliable process returns it every time it reruns, even on a batch it already loaded or a source that sends the same order twice.
• Deduplicate with a partition, a newest-first rank, and a deterministic tie-breaker, so the same input always picks the same winner.
• Reject bad rows out loud with a named reason, instead of letting them become silent nulls an analyst three layers away has to chase.
• Wrap the update-then-insert load in one transaction, so a failed batch rolls back whole and a rerun changes nothing extra. That is what makes it safe to retry.
• A reconciliation check is only a control when a scheduled job can gate on its pass condition. The duplicate-key check that must return zero rows is the one to gate on.

Before the next feed goes live

This is the audit I run before I trust a number out of any new source integration: dedupe deterministically, reject out loud, load inside a transaction that cannot half-apply, track anything that changes over time, and reconcile with a check that has to return zero rows. If you are standing up a new source feed and want the load audited before it lands in the warehouse, get in touch.

Related posts

Other walkthroughs built on the same invented field-service company:

• The same ROW_NUMBER dedup shape runs on Databricks in the monthly gross-margin bridge report.
• The reconciliation habit continues end to end in tracing gross margin from raw record to dashboard.
• Machine learning on that lakehouse: a legible completion model and a customer segmentation.
• The Power BI star schema and DAX measure dictionary that the loaded fact table feeds.
• Putting the Power BI project under version control, so a measure edit gets a diff, a review, and an audit trail.