RFC: Data Warehouse v2 — DMS CDC on Apache Iceberg

Status	Draft — pending implementation
Author	jeffg
Created	2026-06-01 (revised 2026-06-03 after technical review)
Issue	GH #8963

All infrastructure, codebase, and Iceberg/Athena/Redshift capability claims in this RFC were validated against live AWS (--profile readonly), the repo, and current AWS documentation in June 2026.

Summary

Our analytics warehouse (Postgres → DMS CDC → S3 parquet → Glue crawler → Athena) is silently broken in production and is entirely clickops-managed. This RFC replaces the Glue crawler — the root cause of schema drift — with an Apache Iceberg table layer written by an AWS Glue job, while keeping the Athena interface unchanged. The result is schema-evolution-proof tables, alarmed and self-healing replication, full Infrastructure-as-Code, built-in (best-effort) GDPR erasure, and lower storage and scan cost.

Scope is the append-only CDC changelog layer the Python pipeline chart depends on — no current-state ("silver") or curated ("gold") layers, because Superset usage is ad-hoc and imposes no contract.

Cutover requires no application deploy: the Python consumer already targets the database name we preserve, and the swap is an atomic catalog repoint. The one consumer change that must ship before cutover is a freshness guard — it is the safety net for the swap-gap window, not an optional add-on. The column-contract CI guard is genuinely additive and protects future migrations.

New to terms like bronze, CDC, or compaction? See Terminology below.

Terminology

A glossary for readers who are not data engineers. These terms recur throughout the RFC.

Term	Plain-language meaning
CDC (Change Data Capture)	A feed of every row-level change (insert / update / delete) made to a database, in order — as opposed to just the current contents.
DMS (Database Migration Service)	The AWS service that reads our Postgres change feed and writes it to S3 as files.
WAL / logical replication / replication slot / LSN	Postgres's write-ahead log (WAL) is the change journal DMS reads. A "replication slot" is a bookmark on the source telling Postgres how far DMS has consumed; an LSN is a position in that log. A slot that never advances forces Postgres to keep WAL forever — which can fill the source disk.
Parquet	A compressed, columnar file format. Efficient for analytics, but a folder of millions of tiny parquet files is slow and expensive to scan.
Data lake / "bronze"	Raw captured data stored cheaply in S3, untouched. "Bronze" is the raw tier in the common medallion naming (bronze = raw, silver = cleaned/current-state, gold = curated for dashboards). We build only bronze + a query layer; no silver/gold.
Apache Iceberg	An open table format: a metadata layer over parquet files that gives a database-like table (schema, evolution, transactional commits) without a database server. It is what fixes our schema-drift problem.
Schema evolution / field ID	Iceberg tracks each column by a stable internal ID, so adding/dropping/retyping a column is just a metadata change — queries keep working instead of breaking.
Glue (job / crawler / Data Catalog)	AWS's data-integration service. A crawler guesses table schemas by scanning files (our current problem). A job is code we control (our fix). The Data Catalog is the table registry Athena reads.
Athena	Serverless SQL over S3 data. Billed per amount of data scanned; nothing to run or pay for when idle. This is the interface our app already uses and that we preserve.
Partition / partition pruning	Splitting a table's files into folders by a column (e.g. by vendor) so a query for one value reads only that folder instead of the whole table.
Compaction / OPTIMIZE / VACUUM / snapshot	OPTIMIZE merges many small files into few large ones. Iceberg keeps point-in-time snapshots; VACUUM expires old ones so storage doesn't grow forever.
SQS / DLQ	A queue (SQS) that tells the Glue job which new files to process; a dead-letter queue (DLQ) holds anything that failed, so nothing is silently lost.
CTAS / backfill	CREATE TABLE AS SELECT — a one-shot query that builds the new Iceberg table from all existing raw history (the "backfill").
Zero-ETL	An AWS feature that auto-replicates a database into Redshift. Evaluated and rejected — see Why not Redshift.
IaC / Pulumi	Infrastructure as Code — defining cloud resources in code instead of clicking in the console. Pulumi is the tool we choose; see the sub-RFC.

Motivation

What exists today (v1)

flowchart LR
  PG["RDS Postgres (onrampdb_prod)"] -->|"DMS CDC: full-load-and-cdc"| DMS["DMS task cdc-duplousprodv1-task"]
  DMS -->|"parquet + op + TIMESTAMP, date-partitioned"| S3["S3 onramp-datawarehouse-cdc (~414 GB)"]
  S3 -->|"Glue crawler every 4h, CRAWL_EVERYTHING"| GC["Glue Catalog onramp_prod_us"]
  GC --> ATH["Athena workgroup onramp_webapp_prod"]
  ATH --> PY["Python: insights pipeline chart"]
  ATH --> SS["Superset (ad-hoc)"]

Live inventory (account 111776553558, us-west-2):

Component	Prod	Dev
DMS instance	usprod-dms-1 (dms.t3.large, multi-AZ)	dev-01 (dms.t3.small)
DMS task	cdc-duplousprodv1-task — running	cdc-duplodevdb-task — FAILED (WAL protocol error, ~70 days)
Source endpoint	duplousprodv1 → onrampdb_prod	duplodevdb → onramp_demo
S3 target	onramp-datawarehouse-cdc/duplousprodv1-read-1/	onramp-datawarehouse-cdc-dev/duplodevdb/
Glue crawler	duplousprodv1 (every 4h)	duplodevdb (hourly)
Glue DB	onramp_prod_us — 791 tables	onramp_dev — 528 tables
Athena workgroup	onramp_webapp_prod (engine v3)	onramp_webapp_dev

Prod DMS S3 target: DataFormat=parquet, IncludeOpForFullLoad=true, TimestampColumnName=TIMESTAMP, DatePartitionEnabled=true. Table mappings include public.% minus 10 hand-maintained excludes; no column pruning.

Scale — what we are actually dealing with

This bounds how much engineering each problem deserves, so it belongs up front. The entire warehouse is under 0.5 TB. We have roughly 100–200 vendors — a vendor is an OnRamp customer, the company that signs the contract and pays us, and it is the fundamental unit of data isolation. (Each vendor has its own end customers, called accounts inside the product; those are not vendors.) There is a real hotspot effect — some vendors are far busier than others — but at this total size most "scale" concerns are modest. We hope to grow toward thousands of vendors eventually, but going from under 200 to near 1,000 customers is a major business inflection, not an incremental step; we will revisit warehouse layout at that point rather than over-build for it now.

The four flaws

1. Schema changes break the pipeline — live in prod right now.
2. Outages go unalarmed — the dev task has been FATAL for ~70 days; nothing watches task state, latency, or data freshness. (The prod task's 2026-06-01 04:29 restart was investigated and found benign — a scheduled AWS minor-version patch in its Monday window, clean LSN resume, zero gap. It does prove that naive "task not running" alarms would false-positive every patch Monday.)
3. ClickOps — every resource was console-created; only prod CDC is real, dev is half-baked.
4. Inefficient — ~414 GB, no column pruning, a CRAWL_EVERYTHING crawl re-scans all of it every 4h, and or_projects alone is 71,930 small parquet files (5.25M rows) — a textbook small-file problem inflating Athena scan cost.

Root cause of the schema drift

Two mechanisms compound:

• DMS propagates DDL (HandleSourceTableAltered=true): a migration that adds/drops/retypes a column makes DMS write parquet with a new, incompatible schema under the same public/<table>/ path.
• The crawler cannot reconcile incompatible schemas under one path. It forks the data into a new table named after the deepest unambiguous folder — the date partition — producing 2025, 2026, and 2026_<schemahash> tables, then DEPRECATEs the clean one.

Result: 272 of 791 tables in onramp_prod_us are date-stamped drift artifacts.

Concrete production impact

onramp_prod_us.or_projects (the table the Python queries) is DEPRECATED_BY_CRAWLER and frozen at UpdateTime = 2026-03-06 (5,249,708 rows; 71,930 objects; 108 partitions). It is not empty — it still returns its stale, pre-March rows. The consumer's snapshot fallback fires only when Athena raises (helpers.py:350-354), so a deprecated-but-present table that returns rows triggers no fallback. The consumer runs its full CDC reconstruction over the March-6-frozen data, then merges live current-state via _supplement_from_snapshot (helpers.py:526,530). Net effect since early March: history-dependent pipeline stages (Opening / Pulled-in / Pushed-out) have been silently wrong, while disposition stages (Created / Completed / Archived) stayed plausible because the live snapshot supplement masked the staleness. That masking is why this went unnoticed for ~3 months.

The consumer contract (must not break)

The Athena surface is small and well-contained — one client wrapper and one query:

• app/api/utils/aws/athena.py — pyathena client; workgroup onramp_webapp_{env}; DB map prod → onramp_prod_us, dev/local_dev → onramp_dev, stage/demo/gdpr-prod → None (raises — no CDC there today; v2 changes this).

• VendorProjectPipelineHelper.get_project_history runs the single query — a windowed change-detection CTE, not a flat SELECT:

  WITH ranked_data AS (
    SELECT op, CAST(timestamp AS TIMESTAMP) AS timestamp, id,
           created_dts, planned_go_live_dts, project_completed_dts,
           project_archived_dts, value,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY CAST(timestamp AS TIMESTAMP) DESC) AS change_rank,
           LEAD(planned_go_live_dts)   OVER (PARTITION BY id ORDER BY CAST(timestamp AS TIMESTAMP) DESC) AS prev_planned_go_live_dts,
           LEAD(project_completed_dts) OVER (PARTITION BY id ORDER BY CAST(timestamp AS TIMESTAMP) DESC) AS prev_project_completed_dts,
           LEAD(project_archived_dts)  OVER (PARTITION BY id ORDER BY CAST(timestamp AS TIMESTAMP) DESC) AS prev_project_archived_dts,
           LEAD(value)                 OVER (PARTITION BY id ORDER BY CAST(timestamp AS TIMESTAMP) DESC) AS prev_value
    FROM {db}.or_projects
    WHERE vendor = %(vendor_id)s
  )
  SELECT op, timestamp, id, created_dts, planned_go_live_dts,
         project_completed_dts, project_archived_dts, value
  FROM ranked_data
  WHERE op = 'I' OR op = 'D'
     OR (op = 'U' AND (planned_go_live_dts   IS DISTINCT FROM prev_planned_go_live_dts
                    OR project_completed_dts IS DISTINCT FROM prev_project_completed_dts
                    OR project_archived_dts  IS DISTINCT FROM prev_project_archived_dts
                    OR value                 IS DISTINCT FROM prev_value))
  ORDER BY id, change_rank DESC

Two HTTP surfaces reach it — /vendor/get_project_pipeline_analytics/<start>/<end> and POST /core-insights/pipeline-flow-analysis (via pipeline_flow_analysis_service.py). Both must be validated at cutover.

The consumer reconstructs each project's lifecycle from the raw append-only CDC change log, not current state. The per-id ordering it relies on is produced by the query itself (ORDER BY id, change_rank DESC, derived from timestamp) — not by physical file order — so an Iceberg append cannot reorder it.

The binding constraint on v2: expose an append-only changelog table named or_projects in onramp_prod_us, carrying op + timestamp + the source columns id, vendor, created_dts, planned_go_live_dts, project_completed_dts, project_archived_dts, value. A merge-to-current-state table would not satisfy it — which is also why Redshift zero-ETL is the wrong tool (see below).

Target architecture

Keep DMS → S3 raw parquet as-is (immutable history). Remove the crawler from the query path. A Glue (PySpark) job incrementally appends new raw CDC parquet into Apache Iceberg tables registered in the Glue Data Catalog. Athena and Superset query those tables by their existing names.

flowchart LR
  PG["RDS Postgres (per env)"] -->|"DMS CDC (DDL on, churn-pruned)"| DMS["DMS task"]
  DMS -->|"parquet + op + TIMESTAMP"| RAW["S3 raw bronze (immutable except erasure)"]
  RAW -->|"S3 event"| SQS["SQS queue + DLQ"]
  SQS --> GJ["Glue micro-batch: append to Iceberg + evolve schema"]
  GJ --> ICE["Iceberg table onramp_prod_us.or_projects (append-only changelog, partitioned by vendor)"]
  ICE --> ATH["Athena workgroup onramp_webapp_prod (engine v3)"]
  ATH --> PY["Python pipeline chart (freshness-guarded)"]
  ATH --> SS["Superset (ad-hoc, unchanged interface)"]
  ICE -.->|"scheduled OPTIMIZE + VACUUM"| MAINT["Glue managed compaction + snapshot expiry"]
  DMS -.->|"freshness / task-state / Glue-fail"| CW["CloudWatch → SNS → auto-resume Lambda"]
  PG -.->|"slot lag / disk"| CW

This is the canonical AWS DMS-on-Iceberg reference architecture, simplified: because the changelog stays append-only, the Glue job does a plain append, not a MERGE — no merge keys, no per-row ordering requirement.

Why Iceberg fixes the root cause

Iceberg stores the schema in the catalog with stable field IDs, independent of file layout:

• Schema evolution is a metadata operation — add/drop/reorder/type-widen a column and SELECT * keeps working. No forking, no 2026_<hash> tables. Resolves flaw #1 permanently.
• No crawler in the query path ⇒ no schema-guessing heuristics to misfire.
• Partition spec + compaction control file count and scan cost (flaw #4).
• Caveat — rename is not transparent. A column rename preserves the field ID but changes the name, so a consumer selecting the old name still breaks. v2 closes this with a tracked-column CI guard (see Migration safety).

Implementation

Components

1. DMS (per environment), IaC-managed. Same source/target shape. Prune obviously-churny, low-value columns (e.g. user.last_login) via remove-column rules generated from CdcExcluded ORM markers (single source of truth). Pruning never touches contract columns (op, timestamp, id, vendor, the four *_dts, value).
2. S3 raw "bronze" bucket. Unchanged layout; S3 Standard/IA, no Glacier tiering — raw is the rebuild source for Iceberg, the backfill source, and the target erasure must reach; all need fast access. Immutable except for erasure (see GDPR).
3. Glue Iceberg-writer (PySpark). --datalake-formats iceberg, Iceberg format version 2. Drains the SQS queue per micro-batch, plain-appends to the changelog table, evolves schema on column changes. At-least-once, single-writer (MaxConcurrentRuns=1). No Glue bookmarks (they trade tolerable duplicates for intolerable silent skips — the exact disease this RFC kills) and no DynamoDB manifest. On schema conflict it fails the file to the DLQ — never coerces.
4. Glue Data Catalog Iceberg tables. Database and table names preserved (onramp_prod_us, or_projects, …). Glue-catalog tables, not Lake-Formation-registered (Athena's Iceberg DDL requires the Glue catalog). Compatibility contract: or_projects exposes at least the nine contract columns.
5. Iceberg maintenance. Prefer AWS-managed compaction (Glue managed table optimization) over hand-tuned Spark — chosen deliberately because the team has no Spark expertise (see Ownership). A scheduled OPTIMIZE bin-packs small files; snapshot expiry / VACUUM bounds storage. At our scale this runs as a simple scheduled job in a low-traffic window.
6. Athena. Unchanged workgroups; engine v3 reads/writes Iceberg V2.

Ingestion semantics

• Micro-batch. One Glue run drains the queue on a window (every T minutes or N files, whichever first), amortizing Spark cold start across many files instead of paying it per file. At our data rate T can be generous (minutes, not seconds); it sets the lower bound on data lag, so it is chosen together with the freshness threshold (see Observability).
• Single-writer. MaxConcurrentRuns=1. Iceberg commits are optimistic compare-and-swap; one ingestion writer avoids commit-conflict retry storms.
• At-least-once, duplicate-tolerant. A duplicated U self-dedupes via the IS DISTINCT FROM prev_* filter; duplicated I/D overwrite per-id state with identical values (value is summed per distinct project, not per row). The only real failure — a raw file never appended — is caught by the freshness alarm plus the SQS DLQ + redrive.
• Maintenance overlap. With a single ingestion writer, modest data rate, and compaction scheduled in a quiet window, the rare commit overlap is absorbed by Iceberg's optimistic retry — we do not need partition-disjoint maintenance choreography at this scale.

Table layout: partitioning & compaction

A scale check first (see Scale): the warehouse is under 0.5 TB with ~100–200 vendors, so partition design is not a cost crisis. The real, measured problem is file count (or_projects is 71,930 tiny files) — a compaction problem, not a partitioning one. We keep the layout simple and let compaction do the heavy lifting.

• Partition by vendor (identity). The only query predicate is WHERE vendor = X, so an identity partition on vendor gives exact partition pruning, and ~100–200 partitions is trivial for the catalog. Hotspot vendors simply become larger partitions, which Iceberg handles fine. As a bonus, GDPR erasure becomes a partition-scoped operation — deleting a vendor rewrites only that vendor's files (see GDPR erasure).
• Sort by timestamp within each partition (via the compaction sort order) so a vendor's change history is physically clustered, matching the consumer's per-id/timestamp ordering.
• Compaction is the flaw-#4 fix. A scheduled OPTIMIZE bin-packs many small CDC files into 128–256 MB files; VACUUM/snapshot-expiry bounds storage. At <0.5 TB this is cheap.
• The backfill must be born compacted — run OPTIMIZE immediately after the one-time load (or set the target file size on write), or the new table is born with the same small-file problem it exists to cure.
• Future scale. Identity-partitioning by vendor scales cleanly to thousands of partitions; if/when we approach ~1,000 vendors we should revisit whether a bucketed or time-based layout serves better. Athena cannot change a partition transform in place, but because we keep raw forever and can rebuild the table from it cheaply at this size, that is a planned-rebuild decision, not a one-way door we must get perfect today.

Migration safety

Every schema change reaches the source DB through one vector: an Alembic migration in a PR. Two mechanisms turn that choke point into protection.

CDC exclusion is the only marker (default-include). CDC mirrors DMS: public.% captures everything by default; you opt out. So the only classification the warehouse needs is "is this CDC-excluded?" — orthogonal to the default-deny SQL-tool markers, never shared.

• Table-level: __cdc_excluded__ = True on a model (today's 10 hand-maintained excludes become markers).
• Column-level: a CdcExcluded marker on Mapped[...] prunes a churny/low-value column.
• Single source of truth: the IaC DMS table-mappings + remove-column rules are generated from these markers; a test asserts the generated config matches.

Consumers assert their own dependencies. Any code reading CDC data ships a unit test asserting every DB column it queries exists on the model and carries no CdcExcluded marker. The test keys on the DB column name (what flows through CDC), so an attribute-aliased rename is still caught. The pipeline chart's test pins its nine columns; a later migration that drops, renames, or excludes any of them fails in the same PR — no central manifest, no CODEOWNERS choke.

• Caught at PR time: column drop, column rename (by DB name), exclusion drift.
• Not caught at PR time: an unsafe retype (text→int) — name and exclusion are unchanged. That stays a loud runtime event: the Glue append rejects the conflict → file to DLQ → freshness alarm fires. Loud and safe, never silent.

Agentic review rule. Add .claude/rules/data-warehouse-migrations.md (the repo's conditional-rule mechanism), scoped to load when a PR diff contains migrations/*.py. It hands the reviewer the decision tree: add column / safe-promote → safe; drop or rename a depended-on column → the consumer's test fails, fix query + Glue schema-map in the same PR; unsafe-retype → will DLQ at runtime, decide cast vs. new column now; churny high-volume column → consider CdcExcluded; drop a captured table → its Iceberg table freezes with history retained, confirm intended.

Environment audit & standardization

The current setup grew by ClickOps over years, so before and alongside building v2 we run a one-time read-only audit of everything warehouse-related, then standardize and delete the cruft. The goal: it should be obvious which resource belongs to which environment (prod / stage / demo / dev / gdpr-prod) and what each is for.

Audit scope (aws --profile readonly):

• S3 buckets — the live CDC buckets plus any leftover POC/scratch buckets and stray parquet/CSV exports from earlier experiments.
• DMS — instances, tasks, and endpoints, flagging orphans. Known today: two POC endpoints (s3-onramp-dwh-dev-poc1, s3-poc1c) point at the already-deleted bucket onramp-dwh-dev-poc1.
• Glue — databases, crawlers, and jobs, including the 272 drift tables.
• Athena — workgroups and saved queries.
• IAM — roles and policies attached to any of the above; flag over-broad grants and unused roles.
• CloudWatch — existing alarms. We have some alarms today and they are inconsistent and ad-hoc; the audit catalogs them so v2's alarm set replaces them cleanly rather than layering on top.

Then: adopt one naming/tagging convention keyed on environment, delete confirmed-orphan resources (POC leftovers first), and fold the survivors into Pulumi so the inventory stays true. Deletion is gated on per-resource confirmation — read-only audit first, destructive cleanup second.

Migration & cutover

Pre-build spike (gate before design lock)

One pass over real prod raw, run before building, gates two decisions at once:

1. Per-column type-history report — did any of the nine contract columns ever retype across history? (They are expected to be stable.) This determines whether "no captured history lost" holds.
2. Deduped-raw oracle baseline — the dataset the validation step compares against (below).

Sequence

1. Dev first — and dev is disposable. We do not care about the existing dev pipeline or its data; it is broken and not worth saving. Delete it wholesale and rebuild clean from IaC. The only data that matters anywhere in this project is prod. (The dev WAL FATAL therefore blocks nothing — see Risks.)
2. Full-history backfill. A Glue/Athena CREATE TABLE … AS SELECT reads the existing raw parquet — including the orphaned 2026_<hash> fragments — into the Iceberg table, born-compacted.
   • Retype reconciliation rule: cast every historical fragment to the current model type; quarantine unparseable values to a side table (never drop silently, never fail the whole backfill); report quarantine counts. Acceptance: the contract columns lose zero rows (or a tiny, documented count). Non-contract columns are best-effort.
   • Low stakes by design: no current query reads the date-namespaced drift tables directly, and raw is retained, so the Iceberg table can be rebuilt from raw at any time if we improve the reconciliation later.
3. Validate against a trustworthy oracle — the raw bronze parquet (only the catalog broke; the raw files are intact truth). Dedup raw by the full changelog key (id, timestamp, op, changed-column tuple) and assert set-equality of the deduped changelog on the contract columns between raw and Iceberg, across all history including post-March, per vendor. Then feed both through the actual consumer query and assert identical chart output for windows spanning the broken period. Validate both HTTP surfaces.
4. Atomic cutover — repoint the catalog entry in place. Build and validate the Iceberg table at a staging metadata_location, then flip the canonical or_projects entry's metadata_location via a single Glue UpdateTable (one compare-and-swap; no data move, no window where readers see a missing or duplicated table). Athena also supports ALTER TABLE … RENAME TO on Iceberg tables (metadata-only) as a documented two-step fallback. Rehearse the swap in the dev cutover, and export the Glue DDL first for rollback. The Python DB map already targets onramp_prod_us, so no app deploy is required; the consumer's freshness guard absorbs the brief swap gap.
5. Cleanup. Delete the crawlers and the 272 drift tables — safe, no current query reads them. Superset keeps running against the preserved Iceberg tables (interface unchanged); v2 does not delete Superset. Before deleting the drift tables, inventory Superset's metadata DB and Athena/CloudTrail history (≥90 days) for any dependency on those specific tables.
6. Remove the orphaned POC endpoints (part of the audit cleanup).

Operations / Day-2

Observability

CloudWatch alarms (replacing today's ad-hoc set — see audit):

• Data freshness — max(timestamp) age in or_projects. The single most valuable alarm: it catches every staleness cause (DMS, Glue, or catalog) and would have caught the March freeze immediately. No business SLA exists, so the threshold is engineering-chosen: above observed steady-state lag (DMS CDCLatency* + SQS dwell + the micro-batch cadence) or it false-positives, and below the staleness at which history-dependent stages go materially wrong or it is useless. Compute the number from the parallel-run baseline before cutover. Starting default pending that measurement: warn at age > 1 h, page if sustained > 4 h (tunable).
• Glue writer failure — the new critical path.
• DMS FATAL stop-reason — not mere "task not running"; suppress the Monday patch window so benign maintenance restarts don't false-positive.
• Source-side (new — the only failure mode that is a prod OLTP outage, not a stale chart). A logical replication slot behind a stopped task pins WAL on the source → source disk fills → production database down. Alarm on slot lag (WAL bytes retained) and RDS FreeStorageSpace / TransactionLogsDiskUsage, before disk pressure.

Self-healing & source protection

An auto-resume Lambda resumes a stopped task and always pages a human, with a loop-guard (cap retries, then escalate — never loop on a FATAL). The freshness alarm is the backstop for "resumed but no data flows." When the loop-guard escalates and the task stays down, the runbook permits dropping/advancing the replication slot to protect the OLTP database, accepting a CDC gap that backfill-from-raw later reconciles rather than risking the source.

Consumer freshness guard (pre-cutover prerequisite)

If max(timestamp) is older than the threshold, the consumer falls back to the live-DB snapshot — as a labeled degraded mode, never presenting approximate history as authoritative. The snapshot is current-state only (no history), so it is exactly the March failure mode (Opening / Pulled-in / Pushed-out go wrong) — acceptable only when flagged and on a clock: freshness breach → warn + degraded mode engaged; sustained breach beyond the page threshold → incident. This guard is the swap-gap safety net, so it ships before cutover — not as an additive change.

GDPR erasure

Built in v1, but deliberately simple: best-effort, operator-run, whole-vendor. No legal SLA applies. A customer asking to be deleted means delete 100% of that vendor's data from the CDC pipeline. Finer-grained erasure (an individual user, a single column) is a future extension, not v1.

We explicitly reject building a suppression-list / append-path-filter machine to make erasure race-proof against at-least-once ingestion — that is over-engineering for this requirement. The whole procedure is an idempotent, re-runnable operator CLI:

1. Delete the vendor from Postgres (the source of truth). New CDC for that vendor stops.
2. Delete the vendor from the warehouse. DELETE FROM or_projects WHERE vendor = X — a clean, partition-scoped operation because the table is partitioned by vendor — followed by OPTIMIZE/VACUUM so the bytes are actually removed, not just hidden; and purge the vendor's raw bronze parquet.
3. Wait, then delete from the warehouse again. A second pass after a delay sweeps up any rows that were already in flight (in the queue, in a DLQ redrive) when step 2 ran.

That re-run is the safety model — no locks, no ordering machinery, no blocking. If a future full-history backfill ever re-reads raw, simply run the erasure CLI again afterward. Because raw is also purged, the routine case won't resurrect the vendor anyway. The only pillar this softens is "immutable raw forever," which becomes "immutable except for erasure."

Ownership

Shared-infra owners: Sean, Ross, and jeffg. We have no Spark/Iceberg production experience today, and as a small startup, single-author bus-factor is accepted rather than engineered around. The design deliberately minimizes the new operational surface to compensate: a plain-append Glue job with no MERGE logic, AWS-managed compaction instead of hand-tuned Spark, and a small, loop-guarded auto-resume Lambda. Runbooks are a v1 deliverable: resume task, drop/advance a replication slot, replay the DLQ, run the backfill, handle DLQ-quarantined retypes, and run the GDPR erasure CLI.

Infrastructure as Code (Pulumi) — separate sub-RFC

Adopting Pulumi is a genuinely new competency (we don't use it today), so it gets its own sub-RFC rather than being hand-waved here. This RFC fixes only the standard and the first validation step.

Standard (decided): Pulumi with a self-managed backend, not Pulumi Cloud. State lives in a dedicated, versioned, locked S3 bucket in our own account; secrets are encrypted with an AWS KMS key (awskms://). No external SaaS dependency and no per-resource pricing — the state sits beside the resources it manages.

Smallest thing that validates Pulumi. Before importing anything load-bearing, stand up one new, low-risk v2 resource end-to-end — the SQS queue and its DLQ are the ideal first target (brand new, isolated, nothing depends on them yet). Write the definition, run pulumi up manually as an operator, confirm both the resource and the S3 state/locking behave, then make a no-op change and a destroy/recreate to prove the full loop at zero blast radius.

How we'd run it — manual first, CI later. To start, Pulumi is operator-triggered: an engineer runs pulumi preview / pulumi up locally against a named stack per environment. Once we trust it, the sub-RFC covers wiring pulumi preview into a GitHub Actions check on PRs (so infra diffs are reviewable) and gated pulumi up on merge for the lower environments — keeping prod changes operator-run until we are confident.

What the sub-RFC must still answer. The prod adoption mechanics: importing the live prod S3 bucket (414 GB) and the running DMS task/endpoints into Pulumi state without disruption — read the live config, write definitions that match it, and confirm the first pulumi up is a no-op diff — versus recreating, which is acceptable only for dev (which we are rebuilding anyway). Pulumi is parameterized for all five environments, but CDC is turned on per environment deliberately (see Design decisions).

Design decisions

Decision	Rationale
Changelog-only, not medallion	The Python contract needs the append-only log; Superset is ad-hoc with no contract. Silver/gold layers add ETL for no current consumer.
Keep Athena + Iceberg; reject Redshift	Redshift zero-ETL gives a current-state mirror, not our changelog, and would break the Athena interface. See the deep dive.
Iceberg format version 2, not 3	Athena (Trino) cannot read V3 as of June 2026, and Athena is our primary read path. V3 does not block GDPR — DELETE works on V2 Glue-catalog tables.
Plain append, at-least-once	Justified by the duplicate-tolerant consumer; no bookmarks (silent-skip risk), no DynamoDB manifest.
Partition by vendor (identity)	Matches the only query predicate, trivial at ~100–200 vendors, and makes per-vendor GDPR erasure a partition operation. Revisit near ~1,000 vendors.
GDPR: whole-vendor, re-runnable CLI	Best-effort, no SLA. A second delete pass replaces a suppression-list machine.
CDC enabled in all five environments	prod, dev, and stage / demo / gdpr-prod — all confirmed needed.
Pulumi, self-managed state, own sub-RFC	S3 backend + KMS secrets, no Pulumi Cloud; adoption mechanics deferred to the sub-RFC.
Dev is disposable	Delete and rebuild from IaC; we care only about prod data.
No crawler in the query path	The crawler is the drift source; the Glue job owns the schema instead.

Alternatives considered

Alternative	Why not
Fix the crawler (TableLevelConfiguration 4→3, stop DEPRECATE)	Short-term only; does not solve column evolution (rename/retype still forks).
Athena external tables + partition projection (no Iceberg)	Hive tables match parquet by name/ordinal and break on rename/retype; reintroduces manual schema sync. Iceberg's field-ID mapping is the durable fix.
MERGE into a current-state "silver" table	Breaks the Python contract (no op, no history). Out of scope.
Amazon Redshift (zero-ETL)	Gives a current-state mirror, not the append-only changelog; breaks the Athena interface; more cost/ops at our scale. Full analysis below.
DMS writing Iceberg directly	Not supported; DMS S3 target emits CSV/parquet only.

Deep dive: why not Amazon Redshift?

We re-examined Redshift — the obvious "managed warehouse" alternative — against current AWS docs. It is the right tool for a different problem than ours.

What Redshift / zero-ETL actually gives you. The modern, low-ETL path is RDS/Aurora zero-ETL integration to Redshift, which replicates source tables into Redshift and keeps them in sync within seconds of each write. What lands in Redshift is a current-state mirror of each table — the latest row per primary key, plus transaction-sequence metadata — not an append-only insert/update/delete change log. (A SELECT * returns today's rows; an update overwrites the prior row.)

Why that fails our one hard requirement. Our consumer does not want current state; it reconstructs each project's lifecycle from the append-only CDC change log — every I/U/D with a timestamp, full history per id. Zero-ETL discards that history at the moment of overwrite. To recreate our changelog on Redshift we would have to build our own CDC capture / slowly-changing- dimension history on top — i.e. re-introduce exactly the ETL that "zero-ETL" exists to remove. So it does not simplify our problem; it cannot even serve our query.

It breaks the interface we are committed to preserving. The entire low-risk cutover rests on keeping the Athena interface unchanged (one pyathena client, one SQL query, two HTTP surfaces, no app deploy). Redshift is a different client and SQL endpoint — adopting it means rewriting and redeploying the consumer, the opposite of this RFC's central promise.

Cost and operations point the same way at our scale. We have under 0.5 TB and a handful of queries (one chart + ad-hoc Superset). Athena is serverless and billed per data scanned — with Iceberg compaction and vendor partition-pruning each query scans a sliver, so cost is effectively pennies and there is nothing running when idle. Redshift Serverless bills per RPU-time while queries run on a 4-RPU / $1.50-per-hour-rate floor, plus managed storage, plus a new workgroup to size and monitor; provisioned Redshift is worse (an always-on cluster from ~$0.54/hour). For a low-volume, history-oriented workload that is more cost and more operational surface, not less — and Redshift's strengths (high concurrency, large scale, materialized views, many frequent dashboards) target a workload we do not have.

It would even add a new drift-class failure. Zero-ETL supports only a fixed set of source data types; an unsupported type takes the table out of sync — uncomfortably close to the crawler drift we are eliminating.

When we would revisit. If the product ever pivots to large-scale, high-concurrency, current-state BI where the append-only history is no longer the contract, Redshift (with zero-ETL for freshness) becomes attractive — a different requirement than #8963, to be decided fresh. For today's requirement — preserve the append-only changelog and the Athena interface at small scale — keeping Athena + Iceberg is clearly correct.

Risks

The dev WAL FATAL is no longer a blocker, but the dev source needs a quick check. We are deleting and rebuilding the dev pipeline regardless of why it failed, so the "internal WAL conversational protocol error" is off the critical path. The one thing that still matters is hygiene on the dev source database: a logical-replication slot left behind by the 70-day-dead task could be pinning WAL and slowly filling the dev source disk, which would take down the database developers use. We check for and drop that orphaned slot up front. If the same error reappears on the clean rebuild, we investigate the source's logical-replication settings then. (Prod is already cleared — its 2026-06-01 restart was a benign maintenance patch with a clean LSN resume.)

Source-side replication-slot retention is a production risk we previously weren't watching. A replication slot behind a stopped or failed DMS task pins WAL on the source database; if it is never cleared, the source disk fills and the production database can go down. This is the one failure mode in the whole design whose blast radius is the OLTP database rather than a stale chart, so v2 adds source-side alarms (slot lag and free storage) and the auto-resume runbook explicitly permits dropping a stuck slot to protect the source, accepting a CDC gap that the backfill-from-raw later reconciles.

Backfill correctness across historical schema changes. "No captured history lost" depends on unioning years of raw parquet whose schema changed over time. A column that was retyped mid-history cannot be unioned without a cast, and old text values may not parse to the new type. The pre-build spike resolves this: it produces a per-column type-history report, and the backfill rule is to cast to the current type and quarantine the few unparseable values rather than fail the whole load. The contract columns are stable, so we expect zero loss there — and because we keep raw forever, the table can always be rebuilt if we improve the reconciliation later.

No in-house Spark/Iceberg experience. The team has not run Spark or Iceberg in production before. We accept this rather than engineer around it — we are a small startup and bus-factor mitigation isn't realistic — but we deliberately shrink the surface to compensate: a plain-append Glue job with no MERGE logic, AWS-managed compaction instead of hand-tuned Spark, and a small loop-guarded Lambda. Runbooks for every routine operation are a v1 deliverable.

Glue cost versus the old crawler. The old crawler re-scanned 414 GB every four hours. The new Glue job is incremental and micro-batched, so we expect it to be cheaper — but this is a post-build confirmation rather than a gate, since the micro-batch design (drain the queue per windowed run) is specifically what keeps it cheap.

GDPR raw-purge is the heaviest erasure step. Deleting a vendor from the Iceberg table is a clean, partition-scoped operation, but the raw bronze parquet is date-partitioned with vendors interleaved, so purging a vendor's raw bytes means rewriting the affected files. With no legal SLA and a re-runnable delete, this runs as a best-effort background job, and the second delete pass catches anything that arrived in flight.