Skip to content

ADR 024 — Bronze merge Strategy for Rolling-Window State-Snapshot Sources

Status

Accepted

Context

Some Gladly API reports (and similar sources in other platforms) deliver a rolling window of mutable state rather than an append-only event stream. The ContactExportReportV3 and ConversationExportReport reports each return the prior 30 days of data on every run. Rows in that window are not stable: a contact queued as WAITING yesterday may appear today with ENDED_AT populated; a conversation closed last week may reopen and reclose within the window.

The existing Bronze load strategies do not fit:

Strategy Behavior Why it fails for rolling-window sources
batch_replace DELETE WHERE BATCH_ID + COPY Creates 30 duplicate rows per natural key (one per day's batch). History balloon; dedup cost pushed to Silver.
truncate_insert TRUNCATE + COPY Keeps only yesterday's snapshot. History is lost.
append COPY only Same fan-out problem as batch_replace. Silver dedup is expensive and brittle.

The industry-standard pattern for this source shape is upsert by natural key: keep one row per entity (CONTACT_ID, CONVERSATION_ID), update it only when the content changes. This is how Fivetran ("history off"), Airbyte ("incremental + dedupe"), and Databricks medallion reference architectures handle mutable state from rolling-window APIs.

This decision adds a fourth Bronze load strategy — merge — to support this pattern, and implements it in xo-foundry's Snowflake task layer.

Decision

Add merge as a valid Bronze load_strategy value with the following semantics:

  1. COPY INTO a temporary landing table (CREATE OR REPLACE TEMPORARY TABLE {TABLE}_LANDING LIKE {table}) — isolates the incoming batch from the target during load.
  2. MERGE INTO the real target using landing as the source, matching on RECORD_KEY:
  3. WHEN NOT MATCHED → INSERT all columns (including UPDATE_DATE = CURRENT_TIMESTAMP()).
  4. WHEN MATCHED AND target.RECORD_HASH != source.RECORD_HASH → UPDATE all columns except those preserved from first ingestion.
  5. Preserved columns (never overwritten on UPDATE): DATE_TO_WAREHOUSE, BATCH_ID, PIPELINE_RUN_ID. These capture when/how a record was first seen. UPDATE_DATE is set to CURRENT_TIMESTAMP() on every UPDATE.
  6. RECORD_KEY is the match key (VARCHAR natural key, not hashed). Set use_hash: false in the pipeline config.
  7. The temporary landing table is dropped automatically at session end.
  8. The MERGE is wrapped in the existing BEGIN/COMMIT/ROLLBACK transaction block — same atomicity guarantees as other strategies.

The merge branch was added to packages/xo-foundry/src/xo_foundry/tasks/snowflake_tasks.py alongside a _build_merge_sql() helper. A per-source lookback_days override was added to the Gladly extractor (gladly.py) to override start_date = end_date - lookback_days for rolling-window APIs; this does not affect other sources.

When to use merge vs. other strategies:

Use merge when Use batch_replace when
Source delivers a rolling window of mutable state (rows may change within the window) Source publishes append-only events with stable, non-overlapping batch boundaries
One row per natural key is the desired Bronze shape Full history per batch is needed or the source is event-level
You don't need snapshot history (the data's own timestamps document state evolution) History across batches is required
Example: Gladly ContactExportReportV3, ConversationExportReport Example: Gladly ContactTimestampsReport, ConversationTimestampsReport

Consequences

What gets easier: - Bronze stays compact — one row per natural key, no fan-out. - Silver models remain simple incremental merges (no dedup CTE needed). - Idempotent: running the DAG twice for the same date is safe — the second run is a no-op for unchanged rows. - RECORD_HASH still detects corrections within the 30-day window and updates the Silver row.

What gets harder / what we give up: - Snapshot history is lost. If a contact's ENDED_AT changes, only the latest value is kept. This is acceptable because the Gladly export data carries its own timestamps — state evolution can be reconstructed from the timestamps, not from Bronze row history. - The _LANDING temporary table name is deterministic ({table}_LANDING). Two concurrent runs targeting the same table in the same Snowflake session would collide. Airflow's per-DAG-run isolation makes this safe in practice.

Options Considered

Option A — batch_replace + Silver dedup CTE Load all 30-day rows each day; Silver selects ROW_NUMBER() OVER (PARTITION BY CONTACT_ID ORDER BY BATCH_ID DESC) = 1. Rejected: costly full-scan dedup daily; historical balloon grows 30× per month; corrected rows (RECORD_HASH change on an older CONTACT_ID) are silently dropped by a "latest batch wins" dedup — not a correctness guarantee.

Option B — truncate_insert (legacy) TRUNCATE the Bronze table, COPY current window. Rejected: destroys the ability to detect RECORD_HASH changes across days. If an API field is retroactively corrected two days ago, a TRUNCATE/reload masks it permanently.

Option C — new merge strategy (chosen) COPY → landing table → MERGE INTO real target on RECORD_KEY, RECORD_HASH-gated. Correct, compact, idempotent.

Cross-References

  • ADR 011: Bronze batch_replace — the strategy this extends (does not replace).
  • ADR 013: Bronze all-VARCHAR — Silver owns type casting. Both Bronze tables introduced by this ADR follow the same rule.
  • ADR 020: Silver incremental merge with RECORD_HASH — the Silver layer pattern used downstream of Bronze merge.
  • Issue #1024: Implementation tracking.