yudopr.dev
← Back to all posts

Building a Data Warehouse with PostgreSQL, Kafka, and Debezium

2026-08-12•8 min read

Data Warehouse Architecture

This article is the text version of my YouTube video. You can watch the full video here:
Watch on YouTube: Building a Data Warehouse with PostgreSQL

In this article, I'll discuss the data warehouse architecture I built using PostgreSQL as the DWH, Debezium and Kafka for CDC (Change Data Capture), pg_cron as the scheduler, and stored procedures for ETL.

By shifting away from an OLTP architecture where ELK queried data directly, we moved the workload to a highly efficient Data Warehouse.

The Problem to Solve

When I first joined the project, there was no data warehouse layer between the application and the analytics system. Data was pulled directly from MySQL to ELK. ELK was then used for user dashboards and served as the source for several reverse ETL needs.

The problem? The application database is an OLTP system whose primary job is serving user transactions. If analytical queries, data synchronization, and dashboard needs constantly rely directly on MySQL, the additional load can severely degrade application performance. Moreover, business logic was scattered everywhere, and there was no dedicated layer for cleansing, unifying, and storing historical data.

However, I couldn't just drop in a massive data stack either. Costs had to be kept low, our team was still small, and the business requests were actually quite simple: separate analytics from the OLTP, maintain low latency, and don't introduce too many services that we'd have to operate.

So the proposal was to create a PostgreSQL data warehouse as a new middle layer. The design targets were fourfold:

  1. Protect the MySQL OLTP from analytical loads.
  2. Only move changed data (incremental updates).
  3. Maintain data velocity for reverse ETL.
  4. Keep the architecture cheap and simple.

The Architecture Flow

The flow starts at the MySQL application database. Every insert, update, and delete is recorded by MySQL in its binary log (binlog).

Debezium reads the changes from the MySQL binlog and converts them into CDC events. With this pattern, I don't have to perform repetitive full loads or compare the entire contents of a table. Only data changes are sent to Kafka.

Kafka is used as a buffer and a log that can be replayed. This ensures that MySQL doesn't have to wait for the data warehouse to process anything. If a consumer stops or a load process fails, the events remain safely stored and can be reprocessed without having to re-read the entire application database.

The consumer reads events from Kafka and performs upserts into the raw schema in the PostgreSQL data warehouse. From this point on, dashboards and reverse ETL no longer need to pull data directly from MySQL.

graph LR
  MySQL[MySQL OLTP] -->|Binlog| Debezium[Debezium]
  Debezium --> Kafka[Kafka Buffer]
  Kafka -->|Consumer| Raw[(PostgreSQL Raw)]
  Raw --> Staging[(Staging)]
  Staging --> Core[(Core Models)]
  Core --> Datamart[(Datamarts)]
  Datamart --> Dashboard[BI Dashboards]
  Datamart --> RevETL[Reverse ETL]

Inside the DWH, data moves through several stages:

  • Raw: Stores the CDC results as close to their original source format as possible.
  • Staging: Handles data type casting, deduplication, and normalization.
  • Core: Unifies business rules in the form of facts and dimensions.
  • Datamart: Prepares aggregated tables that are directly consumed by dashboards or reverse ETL processes.

Why PostgreSQL as a DWH?

PostgreSQL was chosen not because the previous team were PostgreSQL experts, but because they were already comfortable working with SQL through MySQL. The transition wasn't as drastic as introducing an entirely different system. The majority of the transformations could still be written purely in SQL.

In the context of this workload, PostgreSQL is far more suited for analytical needs compared to sticking with MySQL. Features like complex queries, window functions, aggregations, materialized views, indexing, and partitioning are much more manageable when building a warehouse. This doesn't necessarily mean PostgreSQL is always faster than MySQL across the board, but its feature set perfectly aligns with the analytical query patterns we needed.

PostgreSQL also helped keep costs down. I didn't have to immediately spin up a managed warehouse (like Snowflake or BigQuery), Spark clusters, or numerous other services. However, PostgreSQL still had to be treated like a true warehouse: large tables were partitioned, indexes were created based on query patterns, dashboards were directed to read aggregated tables, and VACUUM / ANALYZE processes were heavily monitored.

Ultimately, this decision was a careful compromise between analytical capability, the team's SQL proficiency, cost, and operational complexity.

Stored Procedures and pg_cron

I stored the ETL logic in stored procedures because the transformations were primarily SQL-based, and all the data was already resting in PostgreSQL.

The procedure accepts parameters like business date, start timestamp, and end timestamp. Inside, it handles deduplication, merges, aggregations, row count logging, and updating the job status.

One critical detail: the procedure must be idempotent. If a job for the same date or time window is re-run, the final result must remain exactly the same without duplicating any data.

For scheduling, pg_cron was chosen because the business requests and job dependencies were still simple. Given the strict cost constraints, I didn't want to introduce Apache Airflow or a separate orchestrator just to schedule a few procedures in PostgreSQL.

The division of labor is incredibly clear:

  • Debezium and Kafka handle capturing changes from the source.
  • PostgreSQL stores and processes the data.
  • pg_cron determines when micro-batches run.
  • Stored procedures execute the transformation logic.
  • Execution details can be easily monitored via the cron.job_run_details table.

Near Real-Time and Reverse ETL

CDC was chosen because the legacy system was used to getting data almost as fast as the application database received it. Even after the DWH was added, the business still wanted reverse ETL latency to be as minimal as possible. Because of this, data from MySQL flows into PostgreSQL via CDC, and specific tables required for reverse ETL are processed using micro-batch procedures every one to five minutes.

The job reads records based on the updated_at column. However, I don't just use a simple condition where updated_at is greater than the last timestamp. That pattern is prone to data loss when dealing with identical timestamps, late-arriving data, or if a job fails mid-process.

As a solution, I fetch the last successful watermark from the cron.job_run_details table. I look for the last run of the job with a succeeded status, then use its end_time value as the last_success_watermark.

When the job starts, the procedure creates an upper bound. Data is read from the previous watermark (minus a small overlap window to catch late-arriving data), up to that upper bound.

The data is then sent to the target using an upsert. Since the process is idempotent, re-reading records within that overlap window isn't an issue at all.

The watermark is only updated after the entire process succeeds. If it fails, the watermark stays exactly where it was, and that exact time window will be reprocessed in the next run.

So, near real-time here doesn't mean the entire pipeline has to be streaming end-to-end. CDC keeps ingestion fast, while the micro-batch procedure keeps reverse ETL logic simple, safely repeatable, and close enough to the speed of the previous system.

Batch and Monitoring

Naturally, not every process needs to run every single minute.

  • Daily batches are used for KPIs, snapshots, and reconciling the previous day's transactions.
  • Weekly batches are used for trend analysis and operational reports.
  • Monthly batches are used for closing out the books and generating management summaries.

Every procedure receives its period explicitly (like a business date or the start and end of a period). This design pattern makes jobs incredibly easy to re-run and backfill without ever needing to change the underlying code.

Every single run is neatly logged in a control table that records: job name, start time, end time, status, parameters used, row count, watermark progression, and error messages if it fails.

For Data Quality, minimal checks include data freshness, checking for duplicate keys, validating nulls on mandatory columns, and reconciling row counts or aggregate values with the source systems.

Conclusion

This architecture was a perfect fit for the exact conditions I faced at the time: there was no existing DWH, the main source was still MySQL, the team was comfortable with SQL, reverse ETL needs had to remain fast, but the budget (cost) and service complexity had to be kept to an absolute minimum.

To me, the most important decision wasn't picking the newest or most advanced tools. What mattered most was:

  1. Eliminating the risk of analytical queries hitting the OLTP database directly.
  2. Meeting the business's latency requirements.
  3. Aligning with the team's capabilities and skillset.
  4. Ensuring operational costs didn't unexpectedly skyrocket.

The bottom line: Separate analytics from OLTP. Maintain latency. Control costs.

(Watch the deep dive on this architecture in this YouTube video)