EDP
platform-cdc-simulator
Visual CDC source guide

platform-cdc-simulator

This repo creates realistic e-commerce source activity. It writes customers, products, orders, payments, and shipments into PostgreSQL so AWS DMS can capture the changes and land raw events in Bronze S3.

PostgreSQL OLTPWALCDCAWS DMSBronze S3Faker dataSSM tunnelDocker local DB
Where this repo fits

The simulator creates the source events for the whole platform

Source traffic to Bronze S3 The simulator does not clean analytics data. It creates realistic source changes so DMS and downstream jobs have something real to process. This repository make schematables + triggers make seedhistorical orders make simulatelive changes PostgreSQL source OLTP tables6 related tables WALcommitted changes AWS DMS Full Loadinitial snapshot CDC StreamI / U / D records Data lake handoff Bronze S3raw Parquet events Glue nextreconcile to Silver schema first then live loop SQL writes every commit is logged DMS reads WAL LOAD files CDC parquet downstream Bronze Glue Silver dbt Gold Analytics Agent Streamlit · HTML · Slack
Repository boundaryThis repo creates tables and writes source transactions. It does not own DMS, Bronze S3, Glue, dbt, or the agent.
Why the WAL mattersPostgreSQL writes committed changes to the WAL. DMS reads that log instead of repeatedly querying source tables.
Bronze is rawBronze keeps every event DMS writes. Cleaning, deduplication, and latest-row selection happen later in Glue.
CDC concept

Why CDC is better than polling for this platform

Polling vs CDC Polling asks the table what changed later. CDC follows the database change log as the changes happen. Polling path: scheduled SELECT queries orders tableupdated_at column hourly jobSELECT updated rows Riskmiss deletes, stale data, skipped states poll every hour CDC path: read committed changes PostgreSQLbusiness writes WALordered log DMSreplication client Bronze S3I / U / D events commit first subscribe write parquet
Polling is an approximationIt depends on timestamps and can miss intermediate status changes or deletes between polling windows.
CDC follows the source of truthThe database log already knows the exact order of committed changes. DMS reads that stream.
Why this repo existsThe simulator creates enough realistic change activity for CDC behavior to be visible in Bronze S3.
Source data model

The six OLTP tables and their relationships

E-commerce OLTP schema The simulator writes relational data in a realistic dependency order so foreign keys make sense. customerscustomer_id, country, signup_datereference entity productsproduct_id, category, pricereference entity ordersorder_id, customer_idorder_status, order_datechanges often paymentspayment_id, order_idmethod, status, amount order_itemsorder_id + product_idquantity, line_total shipmentsshipment_id, order_idcarrier, delivery_status customer_id order_id product_id payment belongs to order shipment belongs to order Every table has updated_at triggers and REPLICA IDENTITY FULL so UPDATE/DELETE events contain enough row context for DMS.
Reference data firstCustomers and products are created before orders so transaction rows can reference valid keys.
Orders are the centerPayments, shipments, and order items all hang off the order. Status changes create useful CDC updates.
Realistic relationshipsThe model behaves like an application database, which makes downstream Bronze and Silver logic more meaningful.
What lands in Bronze

One business order becomes many raw CDC records

Raw event stream for order_id 123 Bronze preserves each event. Silver later chooses the latest valid current-state row. Bronze S3: append-only raw events op=I · pendingorder created op=U · confirmedsame order changed status op=U · shippedsame order changed again op=U · deliveredlatest event wins later Glue CDC logic Group by keyorder_id Sort newest first_dms_timestamp Keep latestunless op=D Silver S3 order_id 123status=delivered all four records are kept in Bronze clean current state
Bronze is the audit trailIt is normal to see multiple rows for the same business key. Each row is a source event.
Silver is the current viewGlue sorts records by DMS timestamp, keeps the latest valid row, and handles deletes explicitly.
Why status updates matterCancellations, refunds, shipping progress, and delivery events create the update records needed to test reconciliation.
Critical database setting

Why REPLICA IDENTITY FULL is applied to every table

UPDATE event row context DMS needs complete row context in Bronze so downstream jobs can rebuild the latest entity without querying PostgreSQL again. Without FULL WAL may contain only key + changed columnorder_id=123 · order_status=delivered Downstream context is incomplete With REPLICA IDENTITY FULL WAL includes full row contextorder_id, customer_id, status, dates, totals... Bronze record is complete missing fields hurt Silver
UPDATEs need contextA changed status alone is not enough for the lake. Bronze should contain enough columns to reconstruct the current row.
DELETEs need keysIf a row is deleted, Glue still needs to know which business entity was deleted.
The simulator applies itThe schema code sets REPLICA IDENTITY FULL on all six tables before DMS starts reading changes.
Concept map

The CDC ideas this repo teaches

Full Load + CDC

snapshot→ongoing changes→Bronze

DMS first copies existing rows, then keeps streaming new committed changes from the WAL.

OLTP traffic

orders→payments→shipments

The simulator behaves like a business application, producing many small relational writes instead of static files.

Bronze vs Silver

raw events→reconcile→latest state

Bronze stores history. Silver is where Glue creates a clean current-state view.

Local vs cloud

Docker PostgreSQLorRDS via SSM

Local development uses Docker. Cloud simulation connects privately to RDS through an SSM tunnel.

Reproducible data

Faker→seed→simulate

The data is realistic enough for demos but repeatable enough for testing.

Clear ownership

this repo writes→DMS copies→Glue cleans

The simulator owns source writes only. Other repos own replication infrastructure and downstream transformation.

Use this page to understand the CDC source visually; use the rendered README for exact setup commands, environment variables, and troubleshooting.