The Pipeline: DB to Warehouse

Streaming to the Destination

Once change events are in Kafka, the production database is fully decoupled from all downstream consumers. The database does not know or care how many systems are reading its changes. You can add a new consumer without touching the database at all.

To move data from Kafka into a warehouse or other destination, you use a Sink Connector — the mirror image of a Source Connector. Popular sink connectors include the Snowflake Kafka Connector, the BigQuery Sink Connector, and the S3 Sink Connector (for data lake patterns).

End-to-End Data Flow

Here is the complete journey of a single UPDATE orders SET status = 'shipped' WHERE id = 1001:

1. Commit: The application commits the transaction. Postgres writes the change to the WAL.
2. Capture: Debezium reads the WAL entry via the replication slot within milliseconds. It constructs a change event and publishes it to the Kafka topic postgres.public.orders.
3. Buffer: The event is durably stored in Kafka. It will be retained according to the topic's retention policy (e.g., 7 days), regardless of whether downstream consumers are running.
4. Sink: The Snowflake Sink Connector reads the Kafka topic, translates the Debezium event into a Snowflake MERGE or INSERT statement, and writes the row to the ORDERS table in Snowflake.
5. Available: The updated row is queryable in Snowflake.

End-to-End Latency: Typically 2–10 seconds under normal load. Compare this to 24-hour batch ETL.

Handling Deletes in the Warehouse

A DELETE event from Debezium has op: "d" and after: null. The Sink Connector must decide what to do with this. Common strategies:

• Hard delete: Issue a DELETE statement in the warehouse. Simple, but loses history.
• Soft delete: Set a deleted_at timestamp column and a is_deleted flag. The row remains queryable for historical analysis.
• Append-only / event log: Write every change event as a new row in the warehouse, including deletes. The warehouse becomes a full audit log. Query the latest state with a QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY ts_ms DESC) = 1 pattern.

The right strategy depends on your analytical needs. Most mature data platforms use the append-only approach for maximum flexibility.