Late-Arriving Carrier Events Break the Nightly Load

The symptoms point to non-idempotent republishing rather than one bad carrier file. Truncating and reloading 30 days while late events continue to arrive creates overlapping snapshots; if the reload appends instead of replaces, or if the MERGE key is not unique, shipment revenue doubles. The retroactive changes suggest the fact table is being rebuilt from a mutable landing table, and the SCD2 join may fan out when more than one carrier version matches. I would keep an immutable raw landing table, deduplicate in staging with ROW_NUMBER over shipment_id plus event_type ordered by ingestion time, and MERGE into fct_shipments on the natural key with a hash comparison. Partition by event_date, replace only affected partitions, and make every run safe to rerun. Add a row-count and checksum gate before publish. For late events, update the existing shipment row and record a restatement timestamp rather than deleting history. This keeps the four-hour window predictable and makes finance reconciliation repeatable.

The dimension must preserve the version that was true when the shipment event occurred. I would join the fact to dim_carrier on carrier_id and event_timestamp between effective_start and effective_end, not on the current-flag alone. For a carrier not yet known, create an inferred member with a surrogate key and null attributes so the fact loads without dropping or fanning out. When the real record arrives, insert the proper version and, if necessary, update the inferred member's attributes while keeping the same surrogate key for already-loaded facts. If a carrier name changes later, create a new version with a new effective date; do not overwrite the old row. Late-arriving facts then resolve to the correct historical version. I would also add a data-quality check for facts with an inferred or missing carrier key and report that count daily, because silent null joins are how dimension errors become revenue errors.

I would add controls at three layers. Pipeline: freshness timestamp, duplicate rate on the shipment key, null foreign-key rate, row-count variance against a seven-day median, and late-event volume by carrier. Reconciliation: a daily comparison of fct_shipments to the finance carrier-cost feed and to the prior published snapshot, with a tolerance threshold and an owner for each break. Communication: a published data-version and restatement log so finance knows when a prior day changed and why, plus a monthly sign-off before close. Alerts should route to the data engineer with a runbook that says whether to pause publication, backfill a partition, or accept a known late batch. I would also cap cost by querying only affected partitions and by clustering on carrier_id and event_date. Monitoring without a restatement policy just produces louder surprises.