Late-Arriving Dimensions and Three Tempting Shortcuts
A nightly batch moves roughly 6 million change rows from three source systems (two Oracle, one SQL Server) into an on-premise star schema. The SLA defines a four-hour load window; the last ten runs averaged 5 hours 40 minutes and three of them overran the 07:00 reporting cut-off. The job aborts on average at 92 percent because about 1,200 fact rows reference dimension members that will not arrive until the following night.
The team proposes three changes: drop the foreign key constraints on the fact table, generate surrogate keys with MAX(key) + 1 inside the load procedure, and replace all dimension loads with TRUNCATE-and-reload each night. There is also an unused CDC stream available from one source, delivering sub-minute latency. The schema contains three type-2 dimensions averaging 4.2 versions per member, and about 40 downstream reports are built on it.
1. Evaluate the three proposals. For each, state what it actually fixes and what it costs.
Read the worked answer
Dropping foreign keys removes the only automated guard against orphan facts. The 1,200 rejects do not disappear; they become silent orphans that make downstream counts wrong in ways nobody detects until a reconciliation, and with 40 reports consuming the schema the blast radius is large. If constraints are relaxed at all, do it only during the load and validate afterwards (SQL Server WITH CHECK, Oracle ENABLE VALIDATE). That still requires a scan, so it moves the cost rather than removing it, and it is not a performance strategy.
MAX(key) + 1 is a correctness bug waiting to happen. It serialises key allocation, and any overlapping run or restart after a mid-load abort can hand out the same value twice, or leave gaps that confuse auditing. It also collides with the platform's own sequence or identity mechanism. Use sequences with a suitable cache, or identity columns, and let the database own allocation under concurrency.
TRUNCATE-and-reload destroys exactly what the type-2 design exists to preserve. Every reload changes surrogate keys, so every historical fact row either breaks its join or must be restaged, and version history is lost, which is unacceptable with an average of 4.2 versions per member and audit-visible reports. It is defensible only for small, fully derived, type-1 lookup tables, and even then only with a stable natural key.
None of the three addresses the real problems: the load is full-volume where it could be incremental, and the late arrivals are being handled by abort rather than by design.
2. Design the handling of late-arriving dimension members. Compare the options and recommend one.
Read the worked answer
Option one is an inferred member: when a fact arrives for an unknown natural key, insert a placeholder dimension row (flagged as inferred, carrying the natural key) so the fact loads and the foreign key holds. The member is back-filled when the real attributes arrive. Strengths: the load never blocks and no measure is lost. Weaknesses: reports show an unknown member until back-fill, and you must decide the versioning rule, because a type-2 dimension with an inferred row that later gains attributes should normally close the inferred row and insert a real version rather than update it in place, which is only acceptable if you do not need history for that member. Back-fill must be a reliable scheduled process, not a manual afterthought.
Option two is deferral: park the orphan facts in a suspense table and retry on the next run. Strengths: no dummy data ever reaches the mart. Weaknesses: reporting is incomplete, and without alerting and an ageing rule the suspense table quietly becomes a graveyard.
Option three is a staging pre-pass: stage the whole source batch first, then load dimensions from the staged set before facts. Because most of the 1,200 are almost certainly members that arrive in the same batch but are processed out of order, this alone will eliminate the majority at near-zero cost.
Recommendation: staging pre-pass first, inferred members for genuinely late arrivals, suspense for anything that still fails validation, plus a monitored count of inferred and suspended rows so the exception rate is visible. Also decide the SCD2 rule for inferred members explicitly and document it, since it silently determines whether history is correct.
3. The job aborts at 92 percent and is restarted by hand. What does correct restartability require here?
Read the worked answer
The current behaviour means every abort leaves partial state, and a manual restart either duplicates data or fails on a conflicting key. That is a design defect independent of performance, and fixing it usually recovers a large share of the lost time on its own.
Requirements: make each unit of work idempotent. Scope by a batch key, typically load date plus source, and make the load a delete-and-insert per partition or a MERGE on the natural key and effective date, so running twice produces the same result as running once. Wrap each batch in a transaction, and only after commit advance the high-water mark recorded in a control table along with batch id, source, rows read, rows written, status and timestamps. Never advance the watermark before commit, or a crash loses data silently.
For the fact table, load by day partition so a failed partition can be replaced by partition exchange rather than a full reprocess of 6 million rows. Route rejects to an error table with a reason and the source row identifier, so a restart reprocesses them rather than dropping them quietly.
Prove it rather than assert it: kill the job deliberately mid-load, rerun it twice, and check that the target equals the result of a single clean run, row for row and to the cent, with no duplicate keys and no orphans. That test belongs in the release checklist. Also add an alert on the control table, so an abort is noticed by the team before the business notices the missing report. The manual restart habit is itself a finding.