The problem
Analytics for 100+ tenants ran on a stack of legacy Python ETL scripts writing into large row-store Postgres (RDS) instances that doubled as the OLAP layer. It worked, but every part of it was straining: the nightly batch took around five hours, dashboard queries routinely took 15+ seconds, and the only lever for performance was a bigger instance — the fleet had already reached 8xlarge class.
The goal: move analytics onto a columnar engine, turn the ETL into declarative, testable models, keep data fresh without a nightly-only window, and do all of it without breaking the 100+ BI dashboards clients already relied on.
Architecture
The dashed mint arrow is the scaling loop: replication-slot lag is published as a custom CloudWatch metric and drives step-scaling on the CDC workers — not CPU.
ELT on ClickHouse + dbt
The old pipeline was imperative Python: extract, transform in memory, load row by row. I flipped it to ELT — land raw data in ClickHouse, then express every transformation as a dbt model that runs inside the engine. Transformations became versioned SQL with tests and lineage, and the heavy lifting moved to a columnar engine built for exactly this kind of scan-and-aggregate work.
- Tenant-aware models so one codebase serves 100+ tenants instead of per-tenant scripts
- Incremental models where the grain allows it, full rebuilds only where it doesn't
- dbt tests as the contract between raw and mart layers
Retiring the OLAP fleet
With analytics moved off Postgres, the db.m7g.8xlarge RDS instances that had been serving as the OLAP layer could be decommissioned. ClickHouse runs on AWS ECS, provisioned with AWS CDK and backed by EC2 capacity providers — so the cluster is code, reproducible, and sized for the workload rather than for the worst query.
- Database costs down 85% — roughly $25,000 / year
- Storage down 75%, from ~1 TB to ~200 GB, from columnar compression alone
Zero-downtime CDC
Nightly batches alone meant analytics were always a day stale. I built a Postgres → ClickHouse change-data-capture pipeline with PeerDB running on ECS Fargate, mirroring 4 microservices' databases across multi-tenant mirrors — with no downtime on the source systems.
Scale on the signal that matters
CPU is the wrong autoscaling signal for CDC. A worker can sit at low CPU while its replication slot quietly falls behind — and on Postgres, a lagging slot holds WAL on the primary, so the risk lands on the production database, not the analytics side. I replaced CPU autoscaling with step-scaling driven by a custom CloudWatch metric for replication-slot lag, keeping lag under 1 GB across all mirrors.
Dashboard cutover
100+ client BI dashboards were written against the old Postgres schema and dialect. Rewriting them by hand would have been slow and error-prone, and a hard cutover would have put every client at risk at once.
- A custom Python query-translation engine programmatically rewrites legacy Postgres SQL into ClickHouse SQL
- An idempotent upsert migration framework applies dashboard and dataset changes so any step can be re-run safely
- Dashboards cut over incrementally — seamless for clients, reversible for us
MCP servers
I built custom Model Context Protocol (MCP) servers for the BI layer and for ClickHouse, so AI agents can inspect dashboards, run live queries, and translate SQL directly. They turned the migration's long tail — odd queries, dialect edge cases, “why is this number different” debugging — into agentic workflows instead of manual digging.
Results
- Nightly ELT: 5 hours → 15 minutes (20×)
- Analytical queries: 15+ s → 1–2 s
- Database cost: −85% (~$25K / year)
- Storage: 1 TB → ~200 GB (−75%)
- CDC replication-slot lag held under 1 GB across multi-tenant mirrors
- 100+ client dashboards migrated with zero-downtime cutovers
What I'd add next
- End-to-end freshness SLOs per tenant (source commit → mart availability)
- Per-tenant query cost attribution on ClickHouse
- Automated diffing of dashboard results between old and new stacks as a CI gate
Stack
- ClickHouse on AWS ECS (EC2 capacity providers), provisioned with AWS CDK
- dbt for ELT models and tests
- PeerDB on ECS Fargate for Postgres → ClickHouse CDC
- PostgreSQL as the operational source of truth
- CloudWatch custom metrics + step-scaling
- Python for the query-translation engine and migration framework
- Model Context Protocol (MCP) servers for BI + ClickHouse