CK WhereFour customer re-key runbook

What happened

CK’s WhereFour tenant moved (my.wherefour.com → clevelandkitchen.wherefour.com) and every customer was re-keyed. The warehouse never resynced, so it still holds the old ids. Verified against the live tenant on 2026-08-05 using CK’s own connection credentials:

GET customers/206671  -> 404          (warehouse id)
GET customers/206644  -> 404          (the id the customers directory renders)
GET customers/415509  -> 200 'Walmart DC 6097 - London'
GET customers/1739868 -> 200 'Walmart DC 6065 - Harrisonville'

Id overlap between the warehouse roster and the live roster is 0. Name overlap is near-total (720 of 732 map 1:1).

What this breaks

• The customers directory shows ERP ids that do not exist in the ERP.
• customer_aliases keeps recording matches against dead ids (189 rows / 39 ids, last_used was the day this was found).
• The EDI badge cannot work. wherefour_customer_edi_job resolves scraped names against the live roster and writes live ids, so stg_customers’ COALESCE(edi.is_edi, FALSE) join misses every row — 172 correct EDI rows sit in bronze contributing nothing.

Doing nothing is not neutral. Every day adds more aliases and more submitted orders keyed to ids that 404.

Why customers_sync alone makes it worse

Bronze is merge-only (shared multi-tenant table — never replace). A resync adds the 735 live ids and leaves the 732 dead ones in place. raw_customers hardcoded delete_flag = FALSE on the WhereFour leg, so gold and Typesense would carry both — a directory of ~1,467 customers, half of them dead. The tombstone step exists to close exactly that window.

Order of operations

Deploy order is not load-bearing: raw_customers reads active behind iceberg_column_exists, so before the first tombstone write the model compiles to today’s delete_flag = FALSE behavior and only starts honoring tombstones once the column exists. Deploy the dbt PR whenever; the steps below still read best in order.

Run steps 2–6 in one window, off-hours.

The duplicate state is not user-visible for most of that window. Step 2 leaves both id namespaces in bronze (merge-only), but nothing reads bronze directly — the directory still serves the previous gold build. Users can only see doubled customers between step 4 (gold rebuilds from a bronze that now holds both namespaces) and step 5 (Typesense reconcile_documents drops the tombstoned docs). Keep 4→5 tight; 2→3 can breathe.

Rollback expectations follow the same split: before step 4 nothing user-facing has changed, so backing out is just re-running the transform.

1. Pre-flight (read-only, run any time)

python scripts/ck-wherefour-customer-rekey.py crosswalk \
  --connection-id 9cb6136c-b5b7-48a1-b18d-1e65a2e84521 --out /tmp/xwalk.json

Expect roughly 1:1 = 720, ambiguous = 1, orphans = 11. The orphans are mostly DELETED_*_ZZ Test Customer rows. Stop and get eyes on it if the 1:1 count drops sharply or the ambiguous list grows — a name collision mapped wrong re-points an alias at the wrong account, which is worse than a dead id.

2. Resync the roster

Launch customers_sync for the connection (Dagster UI, or the local-GraphQL launch path in the Dagster staging/prod runbook). Wait for SUCCESS.

3. Tombstone the dead ids

python scripts/ck-wherefour-customer-rekey.py tombstone \
  --connection-id 9cb6136c-b5b7-48a1-b18d-1e65a2e84521

Dry run by default. It prints bronze=… live=… api_total=… stale=… and refuses on:

refusal	meaning
empty_feed	the roster read returned nothing
feed_total_unknown	the API gave no Total header — no yardstick, so no retiring
feed_incomplete	the feed is shorter than the tenant’s own customer count

Completeness is judged against WhereFour’s own Total, not against the size of bronze — by this point bronze holds both id namespaces (~1467 rows for a ~735 customer tenant), so any ratio against it measures the wrong thing. A short feed is the failure that matters: every customer the API failed to return would look absent, get tombstoned, and disappear from gold and Typesense.

Re-run with --apply once stale looks right (~732) and live matches api_total.

The FIRST tombstone against a table needs two --apply runs. dlt adds active / retire_reason by schema evolution and drops their values on that same write — the upsert aligns to the pre-existing schema, writes the rows, then evolves. Run 1 lands the right rows with NULLs and still reports LOADED … no failed jobs. The script now re-reads and raises rather than letting that pass; if you see tombstone did not persist, just run it again.

4. Deploy the dbt PR, then transform

Merge the dbt change (raw_customers delete_flag + the raw_order_udfields guard), let it deploy, then run dbt_transform for the connection.

The raw_order_udfields guard is a prerequisite, not a nicety: without it dbt build dies on an E4-only Iceberg table for every non-E4 tenant and gold never rebuilds.

5. Typesense

Run typesense_sync for the connection. reconcile_documents deletes the stale docs once gold stops emitting them — no manual index surgery.

6. Remap the aliases

python scripts/ck-wherefour-customer-rekey.py alias-sql \
  --connection-id 9cb6136c-b5b7-48a1-b18d-1e65a2e84521

Prints a single transaction: snapshot table customer_aliases_bak_<date>, the UPDATE, and a verification SELECT that must return 0 rows — it looks for aliases still pointing at a RETIRED id, so live customers that never had an old-id counterpart do not show up as false positives. Review it, then run it yourself — the script never writes to Postgres.

If the crosswalk resolved nothing, the script prints a no-op comment instead of SQL rather than emitting a statement Postgres would reject.

Do this close to step 2. Aliases accrue continuously, so anything written between the resync and the remap lands on a dead id.

Explicitly not done

• extracted_orders history is not rewritten. Those ids record what was actually submitted at the time; rewriting them falsifies the audit trail.
• Ambiguous and orphan ids are not auto-mapped. They are reported for a human.

Verification

• Search walmart in the customers directory: one row per DC, not two.
• Spot-check a rendered ERP id with GET customers/<id> — expect 200, not 404.
• The EDI badge lights up with no extra step — the EDI rows are already keyed to live ids. Expect 168 of the 172 bronze EDI rows to surface: the other 4 are scraped names that never resolved to a roster id (the sync logs them). Verify DISTINCT customer ids rather than a row count, since a duplicated display name fans out to one EDI row per matching id.
• SELECT count(*) FROM customer_aliases WHERE connection_id = '<id>' is unchanged (189) — the remap re-points rows, it does not create or drop any.

Rollback

Nothing in this runbook destroys data. Bronze is merge-only, so the pre-re-key rows are still there; gold rebuilds from bronze and Typesense rebuilds from gold. To back out: restore customer_aliases from the _bak_ table, revert the dbt PR, and re-run transform + Typesense sync.

Durable follow-up

The tombstone logic lives in a one-off script. Inventory already solved this properly with _reconcile_retired_inventory in the WhereFour sync orchestrator — porting it to customers means the next tenant migration self-heals instead of needing this runbook.