Incremental identity / transaction pipeline

Problem

Identity and transactions do not arrive in the order the business thinks they happened. A record that should attach to Monday shows up Wednesday. A naive daily incremental job either misses it or reprocesses so much history that the warehouse bill becomes the incident.

The system had to match entities across time, apply new facts without rebuilding the whole table, and still produce a key downstream processes could join.

Constraints

• Client unpublished. Architecture only.
• Arrival time ≠ business time. Ingest timestamp is not the grain.
• Matching is temporal. “Same id today” is not the same as “same entity as of the event date.”
• Cost control is a requirement, not a later optimization. Full scans dressed up as increments fail this constraint.

Architecture

Source extracts keep the raw payload. A match step assigns the durable key using validity windows or as-of logic. Incremental models only read what the predicate says is new or late. Serving consumes the matched grain — not the staging dump.

Decisions and trade-offs

• Do not incrementalize a fuzzy key. If two people would draw different primary keys, you do not have an incremental pipeline. You have a frequent full load with extra steps. (This is the same discipline as Understand the grain.)
• Late data is a first-class path. A lookback window, a merge on business time, or a separate catch-up model beats “we’ll rerun last 90 days every night.”
• Cadence follows the decision. Matching that feeds a morning operations list does not need the same schedule as a debug event table. (How I choose cadence.)
• Cost shows up in the predicate. If you cannot name updated_at, a partition, or a CDC cursor, you cannot claim incrementality.

Implementation

Modeling, incrementality, and reliability on the match → incremental → serve path. Tooling stays at the level I can publish: incremental processing, temporal matching, cost control. Cloud and client systems stay unnamed here.

Result

A path where late facts can land without turning every run into a historical rebuild, and where the served key is stable enough to join. No client SLAs or dollar savings are published.

What I learned

The expensive part is rarely the matcher’s cleverness. It is rebuilding because the grain and the incremental predicate were never the same sentence. Write the claim, then the window, then the schedule.