A Failing Nightly Load at a 210-Store Retailer

Grain: one row per POS transaction line, the finest level the sources produce consistently. A daily store-product summary cannot answer basket, void, or return questions, and it cannot be rebuilt later without the detail.

Each row carries additive measures (units, gross sales, discount, net sales, cost) plus degenerate keys for receipt and line number.

Do not key the fact on the natural receipt number. It fails twice:

• Receipts restart annually at each store, so receipt 00123 exists 210 times a year. Without store and date in the key the identifier is ambiguous and line items attach to the wrong sale.
• Store IDs are reused after remodels, so two physically distinct stores collapse into one history. Store-level and chain-level totals silently merge, and nobody notices until a district manager disputes a number.

Instead, assign surrogate keys at load time and keep the source business key only in staging, where collisions can be detected and quarantined. The store dimension is slowly changing type 2, so a remodeled store gets a new version with its own surrogate key while historical rows stay attributed to the old one.

Partition by transaction date, since nearly every query filters on it, and size partitions to the load window rather than to convenience.

Use type 2: surrogate key, effective date, expiry date, current-row flag, and change reason.

Why not the alternatives: type 1 overwrites the attribute, so a reclassification rewrites history and last quarter's figures change under last quarter's categories - precisely what buyers need to avoid. Type 3 keeps one prior value in an extra column, which suits a single planned reorganization, not 1,400 changes a day across a multi-level hierarchy.

Operational consequences:

• The dimension grows by roughly 1,400 rows daily, so change detection must be set-based: hash the tracked attributes in staging and compare to the current version, inserting only genuine changes.
• A product reclassified twice in one day produces multiple versions with the same effective date; the load needs a deterministic tie-break, such as timestamp plus sequence, or the fact-to-dimension join becomes ambiguous.
• Someone must own the as-was definition: fact rows keep the version in force at event time, while a separate current-hierarchy view serves as-is reporting.
• If merchandising reclassifies several hierarchy levels independently, a bridge table beats flattening everything into one dimension.

Most type 2 failures in production are change-detection failures rather than modeling failures. Budget for the maintenance, not just the first load.

Truncate-and-reload of 2.1 billion rows cannot fit a 4-hour window and never could. It also explains the failure mode: the job hits the window and aborts, then reruns from zero.

Fix the pattern in this order:

1. Load incrementally by partition. Take only rows whose source timestamp or date falls in the affected range and switch partitions in. Reprocess a rolling window, say seven days, to catch late returns, voids, and corrections.
2. Obtain change data without a CDC product. Oracle materialized view logs, SQL Server change tracking, an indexed updated_at column with a watermark, or a nightly key comparison in staging. Choose per source; any of these beats a full extract.
3. Stagger the sources. POS first because of volume, then e-commerce and loyalty in parallel streams.
4. Make loads idempotent and restartable with a merge on the business key, so a failed run resumes rather than duplicating.
5. Add an aggregate layer at store-day-product and store-week-category for the 20-minute month-end queries. The detail table is not the right place for those.
6. Reconcile every run: row counts and control totals per partition against source, so a partial load fails loudly instead of quietly under-reporting sales.

Do steps 1 and 6 first. They buy the most stability per hour of effort, and reconciliation is what turns a silent data problem into a ticket someone can fix.