DWH data sync

Outbound (Postgres → BigQuery) is the pubsub export pipeline: export_to_warehouse.py cronjobs read changed rows via per-model export classes and *WarehouseStatus reconciliation tables in duunitori5, publish them to ~19 pubsub topics, where 19 cloud functions write them into staging_duunitori.* BigQuery tables, which Airflow DAGs (scheduled workflows in Cloud Composer) then merge into the warehouse dataset. It works, but:

• High maintenance. Every synced table carries some combination of its own export class, a *WarehouseStatus bookkeeping table with Django model, a cronjob manifest, a pubsub topic, and a cloud function. Adding or fixing a table means touching several of these.
• Batch lag. Data moves on cron schedules (every ~20 minutes per table group), so the warehouse runs up to ~20+ minutes behind — more after a missed run.
• Painful backfills. Re-syncing a single table after a breakage means writing a custom backfill script each time. There is no standard "re-sync this table" button.

Inbound (BigQuery → Postgres) is a pile of ad-hoc fetch_*_dwh_data.py management commands, each with its own cronjob YAML in duunitori-kubernetes. Every new inbound sync is a new Python command plus a new manifest.

Doing nothing means this maintenance tax and lag continue to grow with every table we add.

Outbound: a Datastream stream reads changes from prod RDS via a Postgres logical-replication slot and writes them into a new BigQuery dataset (app_cdc), with minutes-level freshness and a built-in per-table backfill trigger. No Django code runs in the pipeline at all — the export classes, status tables, topics, and cloud functions all become deletable. Inbound (later): one Django management command driven by per-sync config entries, with the helm chart generating cronjobs from the same config — adding a sync becomes a one-config-entry PR.

Each with its why and what was rejected:

• Managed Datastream for the outbound side. The recorded rationale: it is native Postgres → BigQuery, priced at ~$0.20/GB of CDC data processed — expected steady-state cost $20–100/month, plus a one-time $50–150 backfill of the in-scope tables — and it eliminates the per-model export classes and *WarehouseStatus reconciliation tables outright. The design doc records no compared alternative; the obvious one — modernising the existing pubsub export — would keep the whole per-model layer we are trying to eliminate and leave backfills and delivery on us, but that comparison is this page's editorial framing, not a recorded decision.
• Hybrid destination shape: merge mode for most tables, append-only changelog for the few where history matters. Merge mode keeps a 1:1 live mirror of the source table — simple and what most consumers want. But for 3–5 tables (e.g. jobentry, where publish-level changes and openness state over time are analytically valuable), we keep an append-only changelog and derive daily snapshot tables from it via dbt (the data team's SQL transformation tool) or a scheduled query. The single-mode alternatives fail in predictable ways — changelog-for-everything buys cost and complexity for tables nobody needs history on; merge-only silently loses history we rely on today — though that comparison is this page's editorial reasoning: the design doc selects the hybrid without recording weighed alternatives. The exact changelog table list is an open item — see below.
• Prod-only stream. A second stream from staging Postgres was rejected as not worth the operational surface; data-team and dbt development happens in separate dev_* BigQuery datasets off the same prod source data.
• Parallel run, not a hard cutover. The existing pubsub export keeps running untouched while Datastream runs alongside it for ~2 weeks with automated reconciliation. We only decommission the old pipeline after the numbers match. Rollback at any point never interrupts data flow — the old pipeline never stopped — but it is not zero-touch: the stream must be deleted and its replication slot and publication dropped (merely pausing it leaves a lagging slot retaining WAL — the disk-fill risk below), and the RDS logical-replication parameter change persists until reverted in another reboot window.
• Inbound framework in-app over a reverse-ETL SaaS (Census/Hightouch-style). A config-driven management command is cheap enough to build and keeps the logic in our repo. It is deliberately sequenced after outbound is stable — smaller scope, lower risk, and it doesn't block the outbound win.

Supporting behaviors: deletes are applied in merge tables (and preserved as DELETE events in the changelog dataset); JSON columns ship whole, with any redaction done downstream in BigQuery views — the design doc records that choice without a rationale, so the following is this page's editorial reading, not a recorded decision: filtering JSON at sync time would put per-table transform logic back into the pipeline (the very layer this migration deletes), while a downstream view can be changed without touching the stream. This choice has PII implications — see the business risk below. Schema drift auto-propagates added columns (type changes pause the stream and alert).

Scope is the same ~14 entities the pipeline exports today — jobentry, company, the jobwatches, and the lookup/reference tables — with the Jobbland jobwatch M2M through-tables picked up automatically as plain Postgres tables, killing the per-M2M export code for free. Full design detail lives in prd.md, in this project's folder of the docs repo (docs/dwh-data-sync/).

The execution plan (todo.md, in the same docs folder as prd.md) covers the outbound parallel run end-to-end; it deliberately stops at "Datastream running in parallel, validated" — decommissioning and the inbound framework are sequenced after.

Effort: S ≈ hours–1 day · M ≈ several days · L ≈ 1–2+ weeks. The effort ratings are new estimates made for this page — the execution plan itself doesn't carry any.

After the ~2-week parallel run validates: migrate downstream consumers (dbt, dashboards) table-by-table, using BigQuery views to alias old table names where convenient; only then decommission the old pipeline (export code, *WarehouseStatus tables, pubsub topics, cloud functions, cronjobs); and only after that, build the inbound framework.

Technical

• The RDS reboot. Enabling logical replication requires rebooting the prod database — the very first step of the plan needs a maintenance window. What that reboot costs users (a brief failover blip vs minutes of downtime) depends on the instance's Multi-AZ setup, which the plan doesn't record — expected downtime is an unknown to resolve with question 2 below.
• WAL disk / replication slot lag — the one way this can hurt prod. A logical replication slot forces Postgres to retain write-ahead-log segments until the consumer reads them; a lagging or abandoned slot can retain WAL indefinitely and fill the database disk. This is why slot lag is a page-worthy alert and why WAL headroom must be sized before the stream exists. Threshold and headroom are a DBA call, still open.
• Network path GCP → RDS is undecided. Options: public endpoint + Datastream IP allowlist (simplest for the parallel run), VPC peering, or Private Service Connect.
• Table sizing is estimated, not measured. The enablement tiers are based on what each table represents, not actual row counts — Tier assignments may shuffle once we look.
• Cost figures are estimates. $20–100/month assumes moderate CDC volume; jobwatch write volume may be heavy and will show up in the bill during the parallel run — which is partly what the smallest-first tiering is for.

Business / compliance

• PII ships to BigQuery inside JSON columns. The jobwatch tables carry raw email addresses and IP addresses inside their JSON metadata, and this plan ships JSON columns whole — redaction, if any, happens downstream in BigQuery views. This is not a new exposure: the user-data-deletion docs project documents that today's pipeline already exports exactly this PII with no erasure propagation at all — its warehouse merges are upsert-only, so rows deleted in Postgres live on in BigQuery indefinitely. Merge mode actually improves the erasure story: a row deleted in Postgres now disappears from its BigQuery mirror (to the extent source rows really get deleted — that project tracks gaps there too). The flip side: any PII-carrying table put in changelog mode would preserve every row, including its DELETE events, forever. See question 8.

Consciously judged rather than proven: prod-only stream, merge-mode-by-default, and shipping JSON columns whole are design judgments from prd.md — the parallel run validates correctness, not these choices.

The concrete questions we want the team to answer — this is the discussion agenda.

Technical

1. Is a public RDS endpoint + Datastream IP allowlist acceptable for the parallel run, or do we require private connectivity (VPC peering / Private Service Connect) from day one?
2. When can we take the RDS reboot window to enable logical replication — does it fold into an existing maintenance slot, and what user-facing downtime should we expect (is the instance Multi-AZ)?
3. Who makes the DBA call on WAL disk headroom and the slot-lag alert threshold, and how much disk headroom does prod RDS have today?
4. Which existing channel should page-worthy alerts route to (PagerDuty vs New Relic), and which Slack channel takes the informational tier?

Business / data

1. Besides jobentry, which tables' history do analytics actually rely on? We need the definitive 3–5 table list for changelog mode — everything else becomes a live mirror where past states vanish.
2. Is the ~2-week parallel run plus ≥7-day per-table validation acceptable, or does something downstream need the new tables (or their freshness) sooner?
3. Are there downstream consumers of the current warehouse tables we haven't listed — dashboards, dbt models, exports — that must be on the table-by-table migration list before decommissioning?
4. From a GDPR standpoint: can any PII-carrying table (the jobwatches above all) go into changelog mode at all, and is redacting PII inside JSON columns in downstream BigQuery views acceptable — or do those tables need redaction or erasure guarantees at sync level?

• Adding tables not currently exported. Scope revisit (add/drop tables) is explicitly deferred until after the first cutover — the migration is judged on parity, not new features.
• Row-level filtering and filtering inside JSON columns at sync time. All rows of in-scope tables sync as-is; JSON ships whole. Redaction, if needed, happens downstream in BigQuery views.
• Reverse-ETL SaaS (Census/Hightouch) for the inbound side — a config-driven framework in our own repo is cheap enough.
• A staging-environment stream — prod-only, per the decision above.
• Real-time BigQuery reads from the request path — the inbound framework covers "real-time-ish" (every 5–10 minutes) at most.

Deferred but planned (not dropped): decommissioning the old pubsub pipeline and building the inbound framework — both sequenced after outbound validation, per prd.md's cutover plan.