Data Pipeline Architecture Diagram: CDC, Streaming and a Data Warehouse

Data pipelines fail quietly. A dashboard shows yesterday's numbers, a column changes type upstream and a job drops rows without an error, or a replication slot nobody remembers keeps write-ahead log on the production database until the disk is full. Each of these is a hop or a contract that was never drawn.

A data pipeline architecture diagram shows where data comes from, how it moves, where it is stored at each stage, who transforms it and when, and how anyone knows it is fresh and correct. This post builds one for a common setup, change data capture from PostgreSQL through Kafka into a lake and a warehouse, and covers what to label on every hop.

Batch extracts, CDC or events

There are three common ways to get data out of an operational database:

Log-based change data capture (CDC) is popular for analytics because it needs no application changes, sees deletes, and adds little query load. Its costs are operational, and they belong on the diagram.

How log-based CDC works with PostgreSQL

PostgreSQL exposes changes through logical decoding, which turns the write-ahead log (WAL) into a stream of row changes. It needs wal_level = logical, an output plugin and a replication slot. The Debezium PostgreSQL connector uses the standard pgoutput plugin that ships with PostgreSQL 10 and later. On first start it takes a consistent snapshot of the tables, then streams every committed insert, update and delete, by default to one Kafka topic per table, named topicPrefix.schemaName.tableName.

The replication slot is the part that bites. The PostgreSQL documentation explains that a slot prevents the removal of the WAL its consumer still needs, even when nothing is connected. If the connector stops for a weekend, the WAL keeps growing on the primary. The setting max_slot_wal_keep_size caps how much WAL slots may retain; its default, -1, means no limit. A cap protects the database at the price of invalidating the slot, which means a new snapshot. Either way, monitor slot lag and draw the slot on the diagram.

Lake layers and the warehouse

Once changes are in Kafka, a sink writes them to storage, and transformations make them usable. Databricks popularized the medallion architecture for naming the stages:

• Bronze holds raw data as it arrived, with load metadata, so you can reprocess without reading the source again.
• Silver holds cleaned, deduplicated and conformed records: one current row per order, consistent types and keys.
• Gold holds business-level tables and aggregates that dashboards and models read.

Where each layer lives is a design choice: object storage with an open table format such as Apache Iceberg, or the warehouse itself. Draw where it actually lives, because that decides cost, access control and who can query it.

Orchestration and contracts

Transformations from bronze to gold run on a schedule or when new data lands, managed by an orchestrator such as Airflow or Dagster, often running SQL models in dbt. Two things make the difference between a pipeline and a liability:

• Contracts. Change event schemas are registered in a schema registry with compatibility rules, so a breaking change in the source table fails loudly at the producer instead of silently in a report.
• Checks. Freshness, row counts, uniqueness and null checks run after each step, and a failure pages the owner before the dashboard is wrong.

The example: an online shop

The shop's PostgreSQL database holds orders, payments and customers. Debezium runs in Kafka Connect, reads the WAL through a replication slot and publishes shop.public.orders, shop.public.payments and shop.public.customers. A sink connector appends the change events to bronze tables. Every 15 minutes the orchestrator runs dbt models that build silver and gold, then runs the data checks. Dashboards read gold.

How to read it:

• Top row, capture. Changes leave PostgreSQL through the replication slot, become change events in Debezium, which registers their schemas, and travel through one Kafka topic per table to the sink connector.
• Bottom row, storage, right to left. The sink appends raw changes to bronze. Transformations merge them into silver and aggregate silver into gold, which the dashboards query.
• Below, control. The orchestrator runs the transformations and the checks; a failed check raises an alert.
• The Markdown block lists the operational rules for the slot and for schema changes.

What to label

• The slot and its limit on the first edge, and who is alerted when it lags.
• Topic names or a naming rule, so a consumer knows what to subscribe to.
• Schedules and freshness targets on transformation edges, such as "every 15 min, fresh within 30 min".
• Retention per layer. Bronze often keeps history longest; it is your replay buffer.
• Owners of each layer and of each check.
• Personal data. Mark where it is stored and where it is masked or dropped; see the GDPR data mapping diagram.

Common mistakes

• A single arrow from the database to the warehouse. It hides the slot, the schemas and every place data can be lost or delayed.
• Analytics queries on the primary. Batch extracts against the production primary compete with customer traffic; use CDC or a replica, and draw which one.
• No replay path. If bronze is not kept, a bug in a transformation means reading the source again.
• Raw tables as the public interface. Consumers that read change events of internal tables break when the schema changes. For events meant for other teams, publish designed events instead; the event-driven architecture diagram covers that side.
• Checks that only log. A test that fails without alerting anyone is a comment.

For the database side of the same system, see the database replication diagram and the ER diagram example.

Build it in Archboard

1. Sign in with Google or GitHub and create a diagram.
2. From the Data pack, add a Postgres node for the source database and a Kafka node for the topics.
3. Add C4 Model Container nodes for Debezium, the schema registry, the sink connector and the dashboards.
4. Add a Core Frame named Lake and warehouse with two Data pack Object Store nodes for bronze and silver and a Data Warehouse for gold.
5. From the DevOps pack, add a Pipeline for the orchestrator and an Alert for the data checks. Draw control edges dashed.
6. Add a Core Markdown block with the slot and schema rules.

The example uses 13 diagram nodes, within the Free plan. If you keep the source schema in SQL, the PostgreSQL to ER diagram tool draws the tables behind the topics.

FAQ

Is CDC better than batch extracts? For freshness, deletes and load on the source, usually yes. Batch is simpler to run and fine when hours of delay are acceptable and rows are never hard-deleted.

Do I need Kafka for CDC? Debezium is most often run on Kafka Connect, and Kafka gives you replay and many consumers. Other setups exist; draw the transport you actually run and its retention.

How do I handle schema changes? Register schemas with compatibility rules, add columns instead of changing them, and make the downstream models tolerate new columns. Draw the registry, since it is where the contract lives.

Where should data quality checks run? After each layer, as part of the orchestrated run, with alerts to the owner. Freshness checks on gold catch most silent failures.

How do I keep the pipeline diagram current? Change it with the connector and orchestrator configuration in the same pull request; see architecture diagrams that don't rot.

Start free — one seat, no credit card.