🧭 Part 8 — No Duplicates, No Drama: Idempotent MERGE, Latest-Wins & SCD2

Deterministic keys + total ordering = replay-safe facts and auditable dimensions.

⬅️ Previous: 📦 Part 7 — Manifests, Compaction & Load Jobs | 🔗 All Parts (Series Hub) | ➡️ Next: ⏪ Part 9 — Backfills & Replays: Fast Recovery Without Duplicates

🏷️ Topics: Data Engineering · BigQuery · Idempotency · De-duplication · SCD2

If you’re new here, Follow and read the series in order — each part builds on the last.

Why Part 8 matters

Your micro-batches are reliable and your loads are cheap — but analytics are only trustworthy if your tables converge to the same answer every time, even when:

• producers resend data,
• events arrive out of order,
• you replay an hour/day,
• or you run backfills.

Today we’ll make your fact tables idempotent with latest-wins de-dup and your dimensions auditable with SCD Type 2. Everything here is re-runnable — no hand-waving “exactly-once” needed.

1) Idempotency pillars (recap & apply)

1. Deterministic identity: a business key or event_id that uniquely identifies a fact row.
2. Total ordering: a deterministic tie-breaker (e.g., event_time, then ingest_time, then run_id).
3. Monotonic convergence: repeated runs pick the same winner for each key (latest-wins).
4. Ledgering: a processed_parts table ensures you don’t re-LOAD the same file; but even if you do, the MERGE makes results stable.

2) De-dup in staging (window functions)

Always deduplicate in staging first so each key has one “champion” row before you MERGE into the silver table.

-- Champion per business key using a deterministic order:
-- 1) event_time (latest wins)
-- 2) ingest_time (latest wins as a tiebreak)
-- 3) run_id (highest/lexicographically last wins as final tiebreak)
WITH ranked AS (
  SELECT
    s.*,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY event_time DESC, ingest_time DESC, run_id DESC
    ) AS rn
  FROM `proj.stage.orders_hour` s
)
SELECT * EXCEPT(rn)
FROM ranked
WHERE rn = 1;

Why this works: Whatever order files arrive, the same row wins for each order_id. Your MERGE then becomes deterministic and idempotent.

Tip: If producers don’t have a stable business key, mint one upstream (e.g., a deterministic hash over natural keys) or require an event_id from producers.

3) Latest-wins MERGE (facts)

Now take the champions from staging and MERGE into your silver fact table.

-- Silver table schema sketch:
-- order_id STRING PRIMARY KEY
-- amount NUMERIC, status STRING, event_time TIMESTAMP, ingest_time TIMESTAMP, src_run_id STRING
MERGE `proj.silver.orders` T
USING (
  WITH ranked AS (
    SELECT
      s.*,
      ROW_NUMBER() OVER (
        PARTITION BY order_id
        ORDER BY event_time DESC, ingest_time DESC, run_id DESC
      ) AS rn
    FROM `proj.stage.orders_hour` s
  )
  SELECT * EXCEPT(rn) FROM ranked WHERE rn = 1
) S
ON T.order_id = S.order_id
WHEN MATCHED AND (
  -- Only replace if S is strictly "newer" under our total order
  S.event_time >  T.event_time OR
 (S.event_time = T.event_time AND S.ingest_time >  T.ingest_time) OR
 (S.event_time = T.event_time AND S.ingest_time = T.ingest_time AND S.run_id > T.src_run_id)
) THEN
  UPDATE SET
    amount      = S.amount,
    status      = S.status,
    event_time  = S.event_time,
    ingest_time = S.ingest_time,
    src_run_id  = S.run_id
WHEN NOT MATCHED THEN
  INSERT (order_id, amount, status, event_time, ingest_time, src_run_id)
  VALUES (S.order_id, S.amount, S.status, S.event_time, S.ingest_time, S.run_id);

Properties

• Replays: inserting the same (or older) record does nothing.
• Late arrivals (out-of-order): a truly newer record wins and updates the row.
• Idempotent: running this again yields the same table.

4) Out-of-order arrivals without tears

Use two timestamps consistently (see Part 3):

• event_time = when the thing actually happened (producer-declared).
• ingest_time = when your pipeline saw the event.

Your ordering should be event_time first, then ingest_time to break ties. Why?

• It reflects business truth (not network luck).
• It naturally handles late arrivals that are truly newer in time.

If producers sometimes lie or drift, consider:

• a skew budget (e.g., “accept events up to 24h late”), and
• enforcing a monotonic floor per key (reject far-future timestamps to DLQ; see Part 6).

5) SCD Type 2 for dimensions (auditable history)

Facts use latest-wins. Dimensions often require history (who was the account owner last month?). That’s SCD2.

Table design (SCD2 dim)

dim_customer_scd2
- customer_sk       INT64      -- surrogate key (unique row id)
- customer_id       STRING     -- business key
- attributes_hash   STRING     -- hash of attrs for change detection
- name, email, plan, ...      -- current attributes
- valid_from        TIMESTAMP
- valid_to          TIMESTAMP  -- NULL means "current"
- is_current        BOOL

Step A — Detect changes (staging vs current)

-- 1) Prepare champion rows per customer in staging (same window de-dup idea)
WITH champions AS (
  SELECT * EXCEPT(rn) FROM (
    SELECT s.*,
           ROW_NUMBER() OVER (PARTITION BY customer_id
                              ORDER BY event_time DESC, ingest_time DESC, run_id DESC) rn
    FROM `proj.stage.customer_changes` s
  ) WHERE rn = 1
),
-- 2) Current snapshot
current AS (
  SELECT * FROM `proj.dim.dim_customer_scd2` WHERE is_current = TRUE
),
-- 3) Changes: new customers or attribute changes (hash compare)
diff AS (
  SELECT
    c.customer_id,
    TO_HEX(SHA256(TO_JSON_STRING(STRUCT(c.name, c.email, c.plan)))) AS new_hash,
    cur.attributes_hash AS old_hash,
    c.* EXCEPT(run_id)  -- remove volatile fields if any
  FROM champions c
  LEFT JOIN current cur USING (customer_id)
  WHERE cur.attributes_hash IS NULL       -- new
     OR cur.attributes_hash != TO_HEX(SHA256(TO_JSON_STRING(STRUCT(c.name, c.email, c.plan))))
)
SELECT * FROM diff;

Step B — Apply SCD2 (close old row, open new row)

-- Close any current row that changed
UPDATE `proj.dim.dim_customer_scd2` cur
SET valid_to   = CURRENT_TIMESTAMP(),
    is_current = FALSE
WHERE is_current = TRUE
  AND customer_id IN (SELECT customer_id FROM diff);

-- Insert new current rows
INSERT INTO `proj.dim.dim_customer_scd2`
  (customer_sk, customer_id, attributes_hash, name, email, plan,
   valid_from, valid_to, is_current)
SELECT
  GENERATE_UUID(),                  -- or a sequence if you prefer ints
  d.customer_id,
  d.new_hash,
  d.name, d.email, d.plan,
  CURRENT_TIMESTAMP(),              -- valid_from
  NULL,                             -- valid_to
  TRUE                              -- is_current
FROM diff d;

Why hash? Hashing a deterministic shape of attributes avoids per-column “did it change?” complexity and makes updates easy to reason about.

Idempotency: Running the detection logic again yields no additional changes because current/old rows already reflect the latest attributes.

6) What about “same event_time” collisions?

When two rows share the same event_time for a key (it happens), you still need a stable tie-breaker:

1. ingest_time (later wins)
2. run_id (higher wins)
3. source_rank (optional: assign priority to sources)

This triple makes ordering total and deterministic. Encode the same order in both staging de-dup and MERGE WHEN MATCHED conditions.

7) Hardening tips (production)

• Normalize timestamps to UTC at ingest; store with microsecond precision.
• Do not cast away precision in staging or MERGE (e.g., avoid DATE(event_time) in ordering).
• Prefer numeric/ENUMs for status flags; avoid free-form strings that drift.
• Guard rails: DLQ anything with future timestamps beyond your skew budget, null keys, or malformed payloads.
• Test late data: simulate an event arriving hours late and confirm your latest-wins logic replaces the row.
• Monitor: “duplicate key attempts” and “rows updated vs inserted” per run.

8) Validation queries (trust but verify)

8.1 One row per key in silver

SELECT COUNT(*) AS total_keys, COUNT(DISTINCT order_id) AS distinct_keys
FROM `proj.silver.orders`;
-- Expect equal counts

8.2 Only one current row per key in SCD2

SELECT customer_id, COUNTIF(is_current) AS current_rows
FROM `proj.dim.dim_customer_scd2`
GROUP BY customer_id
HAVING current_rows != 1;
-- Expect 0 rows

8.3 Latest-wins truly wins

WITH winners AS (
  SELECT order_id, MAX(event_time) AS max_et
  FROM `proj.stage.orders_hour`
  GROUP BY order_id
)
SELECT s.order_id, s.event_time, w.max_et
FROM `proj.silver.orders` s
JOIN winners w USING (order_id)
WHERE s.event_time < w.max_et
LIMIT 10;
-- Expect 0 rows

8.4 Out-of-order sanity check

SELECT
  COUNTIF(event_time < ingest_time) AS arrived_late,
  COUNT(*) AS total
FROM `proj.stage.orders_hour`;
-- Expect some late arrivals in real systems; track % over time

9) Putting it on rails (ops flow)

1. Load job writes to staging (Part 7).
2. A de-dup view (windowed ROW_NUMBER) exposes champions.
3. A MERGE job runs: latest-wins into silver.orders.
4. SCD2 job updates dim_customer_scd2.
5. Dashboards show rows inserted/updated, late arrival rate, duplicates suppressed, SCD2 changes.

10) Common pitfalls (and fixes)

• Pitfall: Using only ingest_time for ordering → network luck decides truth. Fix: Order by event_time first, then ingest_time, then run_id.
• Pitfall: Non-deterministic tiebreakers (e.g., random UUIDs in ORDER BY). Fix: Deterministic run_id or fixed source_priority.
• Pitfall: Pre-aggregating before de-dup. Fix: First de-dup champions, then aggregate.
• Pitfall: Changing the ordering later. Fix: Treat it as a breaking change; re-MERGE from the lake with the new order.

Production checklist

• Business key or event_id is present for every fact row.
• Staging de-dup view (ROW_NUMBER … ORDER BY event_time, ingest_time, run_id).
• Idempotent MERGE logic mirrors the same ordering.
• SCD2 dimension with valid_from, valid_to, is_current, and attributes_hash.
• Validation queries are saved and monitored.
• Late data tests included in CI (simulate hours-late events).
• DLQ rules catch null keys and far-future timestamps (Part 6).

Final thought

Idempotency isn’t magic — it’s just deterministic identity + deterministic ordering. Add a small de-dup window in staging, a latest-wins MERGE in silver, and (for dimensions) a simple SCD2 pattern. Your tables will converge to the same answer after every replay, retry, or backfill — exactly what trustworthy analytics demand.

⬅️ Previous: 📦 Part 7 — Manifests, Compaction & Load Jobs | 🔗 All Parts (Series Hub) | ➡️ Next: ⏪ Part 9 — Backfills & Replays: Fast Recovery Without Duplicates

🙌 Enjoyed this?

If this helped, tap the Clap (up to 50×!) and **follow me** for more hands-on serverless guides. Your claps + follows tell Medium to show this to more builders — thank you!

Read next (hand-picked)

• 🚀 Run Jupyter Notebooks in VS Code — No Browser Required
• 🧵Kinesis Partial Batch Failure, Done Right (Deep-Dive)
• 🔑 GitLab and GitHub on One Machine: The Ultimate Guide to Handling Two Repos Seamlessly
• ⚡DynamoDB Lambda Done Right: NAT-Free, Idempotent, Scalable
• 🛡️ Injecting Chaos into AWS Architectures with Diagram-as-Code
• 🔐 Least-Privilege by Construction: Policy-as-Code for OpenSearch
• 🔵🟢 Blue/Green OpenSearch Ingestion — Snapshots as Your Safety Net
• 🔎 Forensic Snapshots Opensearch: Root-Cause in Minutes
• 🚀 Snapshot-Driven Staging Environments for Every Pull Request
• 🌐High Availability in AWS: Designing for Resilience and Uptime

🧭 More deep-dive content is on the way!

☁️I’ve just started a new technical series on Cloud Architecture. Stay tuned

• 🚀Zero‑Loss Micro‑Batching on GCP — Kafka → Cloud Run → Parquet → BigQuery
• Chaos in the Stack: A Hub for Reliability, Chaos Engineering, and the Future of Infrastructure
• ⚡ From Frustration to Innovation: Why I’m Building an AI Cloud Troubleshooting Copilot