WhereFour / P21 / E4 Iceberg `replace` multi-tenant data-loss fix

May contain stale references

This document was audited on 2026-06-22 and contains references that need updating: PR #1806 referenced does not exist; unfreeze PR is #1796. Cross-check critical details against the current codebase before relying on them.

Status: PR1 (always-merge guardrail) implemented in this change. Owner: data platform Related: PR #1785 (P21 A/R facts), partner-data-api AR endpoints (Simpl/Jacob).

TL;DR

The bronze ERP sync orchestrators wrote shared, multi-tenant Iceberg tables with write_disposition = "replace" if full_sync else "merge". Those Iceberg tables are shared across tenants — partitioned by a connection_id column, not separate physical tables — and DLT’s filesystem+Iceberg replace maps to pyiceberg table.overwrite(overwrite_filter=ALWAYS_TRUE), a whole-table overwrite. So a full sync for one connection deletes every other tenant’s rows in that table.

This has already fired in staging: the invoices table’s current snapshot holds 40,335 rows for connection 49fc4591 (a Genfit P21 connection) and 0 for e8847315 (the Genfit playground connection) — the last write was a full-sync replace for 49fc4591 that orphaned everyone else’s data.

PR1 makes the write always merge at every shared-Iceberg write site and adds hard guardrails that reject a bare replace.

Evidence (staging, 2026-06-19)

Queried via DuckDB iceberg_scan over s3://erp-data-catalog-staging/erp_data_native_v2/invoices:

metric	value
rows in current snapshot, all connections	40,335
distinct connection_id in snapshot	1 (49fc4591)
rows for e8847315	0
balance_due populated	0 / 40,335 (always NULL — P21 has no balance_due)
amount_paid positive	38,178
computed open A/R for 49fc4591 (total_amount - amount_paid)	~$344,189.85
parquet data files under invoices/data/	11

Root cause

write_disp = "replace" if full_sync else "merge" appeared at every bronze write site:

• wherefour_sync_orchestrator.py (_run_table, routes through run_iceberg_merge)
• eclipse_e4_sync_orchestrator.py (_run_entity, routes through run_iceberg_merge)
• p21_sync_orchestrator.py (9 sites, routes through _run_pipeline_with_cleanup)
• p21_sync_orchestrator_v2.py (9 sites, calls pipeline.run() directly)

full_sync is set whenever the connection has no rows yet or the cursor has no valid watermark (utils/sync_state.py: get_sync_params). So an empty/first-sync connection auto-fulls → replace → wipe. A brand-new connection’s first scheduled sync is also a full sync. The connection_id column is only a partition hint, not a write boundary, so replace ignores it.

Why it has not (visibly) fired on child tables

merge auto-enables DLT root_key, so nested child tables get a REQUIRED _dlt_root_id; replace omits it, so pyiceberg fails the load with “Mismatch in fields” before the overwrite — masking the bug on child tables. Parent tables (e.g. invoices, wherefour_customers/_inventory/_orders) have no such blocker, so a full sync there DOES wipe other tenants. Critical ordering consequence: fixing the schema mismatch (e.g. optionalizing _dlt_root_id) WITHOUT first making the write non-destructive would convert a blocked load into silent data loss.

Fix — PR1 (this change): always merge

erp_pipeline/utils/dlt_iceberg.py:

• resolve_iceberg_write_disposition(full_sync) -> "merge" — single source of truth. full_sync legitimately changes only the API pull scope (full backfill vs. incremental-since-watermark); the write stays a merge.
• run_iceberg_merge(...) now rejects any write_disposition outside {"merge", "append"} (covers the WhereFour + E4 path).

Every shared-Iceberg write site now calls resolve_iceberg_write_disposition(...). p21_sync_orchestrator.py’s _run_pipeline_with_cleanup choke point also hard-guards against replace for defense-in-depth (V2 calls pipeline.run() directly, so the per-site disposition fix is the real safety there).

Unit tests: apps/dagster/tests/utils/test_dlt_iceberg.py.

Decisions / rejected alternatives

• NOT root_key=True — it is a DltSource-level setting (not resource-level), risks a NOT-NULL violation on already-populated tables, and is moot under always-merge (merge already enables root_key).
• NOT optionalize _dlt_root_id — wrong layer; would unblock a destructive load (see ordering note above).
• Demo orchestrator is intentionally untouched (synthetic single-tenant source). Reversed in Phase 4 — the demo orchestrator writes the same shared multi-tenant tables (products/customers/cross_references/shipping_addresses in dataset erp_data_native_v2), so its data is synthetic but its writes are not single-tenant. A demo requestedFullSync replace would wipe real tenants. Now resolves via resolve_iceberg_write_disposition + an assert_not_shared_replace guard in _run_pipeline_with_cleanup.

Tradeoff

Always-merge is upsert-only, so source-side deletions linger after a full sync (a data-quality issue, not data loss). Phase 4 (below) adds a tenant-scoped overwrite for true full refresh.

Scope & follow-ups

• PR1 (here): WhereFour + P21 (both orchestrators) + E4. These are the active prod connections (prophet21 active=2, wherefour active=1 sharing shipping_addresses with P21).
• Fast-follow (DONE — PR #1790): NetSuite (netsuite_sync_orchestrator.py, 3 sites, direct pipeline.run) and Acumatica (acumatica_sync_orchestrator.py + sources/acumatica/entities.py + sources/acumatica/dlt_source.py). 0 active prod connections today, but same latent bug.
• Phase 4 (THIS PR — demo gap): the demo orchestrator’s 4 replace if full_sync sites, made reachable by the full-sync unfreeze (PR #1806) via a manual requestedFullSync=true run. Demo writes the same shared tables, so this closed the last bare-replace write path. (The tenant-scoped overwrite below remains future work.)
• Phase 4 (future): tenant-scoped full refresh — overwrite_filter=EqualTo(connection_id, cid) (or delete-WHERE-connection_id + append) so a full sync can drop a single tenant’s stale rows without touching others.

Operational recovery (separate from PR1)

e8847315 invoices must be repopulated by a safe merge backfill, never a requestedFullSync replace (which would now wipe 49fc4591’s 40,335 rows / ~$344k A/R). After PR1 deploys, a full pull + merge write is safe:

1. Freeze full syncs / provision no new connections during the window.
2. Deploy PR1 (no replace reachable).
3. Backfill e8847315 invoices via merge (full pull, merge write).
4. Drain any stuck normalized load package (.dlt/pipelines/<pipeline>/load/normalized/<load_id>) only after the fix is live.

Note: A/R facts also require PR #1785’s computed balance_due model (total_amount - amount_paid) — the deployed model reads balance_due directly, which is NULL for all P21 rows, so A/R facts are empty for every connection until #1785 is merged and deployed.