Multi-tenant ingestion at scale: re-architecting CDC for ~2,000 tenant schemas

Challenge

The platform served about 2,000 tenants, each with its own PostgreSQL schema and 100+ tables. The existing ingestion stack tried to land every change from every tenant into Snowflake on near-real-time SLAs, landing each tenant’s data into its own set of tables.

The result:

• An object explosion. Roughly 2,000 schemas of around 85 tables each meant Snowflake objects in the hundreds of thousands. Every analytical query reached them through unions across the whole estate, so answering one question meant scanning the same data repeatedly. Queries could not get faster, because there was no way to touch less data.
• Runaway ingestion cost. Per-tenant connectors and per-table extraction created a multiplicative bill that scaled with tenant growth, not with business value.
• Latency creep. Getting a source change into a queryable table took between 30 minutes and an hour, and the number grew with every onboarded tenant.
• Schema-drift fragility. Source-system changes from product teams routinely broke downstream dashboards. Each break became a multi-day firefight.
• Inconsistent transformations. Twenty engineers wrote in twenty styles, with no shared pattern for a tested pipeline.
• Metric chaos. Executives and operators argued constantly about whose number was right, and the data team spent hours every week answering “what does X actually mean?”

The brief was unambiguous: cut cost dramatically, cut latency dramatically, and stop the platform from breaking, without halting the business.

Strategic approach

We treated ingestion as an engineering problem, not a tooling-procurement problem. The core decisions:

1. Move to event-driven, log-based CDC. Replace per-tenant pull-style connectors with a single AWS DMS deployment streaming change events, transformed in the DMS tasks themselves, landed to S3 and auto-ingested by Snowpipe. One pipeline, sized to change volume, not tenant count.
2. Consolidate the estate. Land every tenant’s copy of a given entity into one table carrying a schema identifier, rather than one table per tenant per entity. This is the decision the rest of the engagement rests on. It turns a union across about 2,000 schemas into a partitioned scan of a single table, and it makes incremental models possible where previously every run was a full rebuild.
3. Reconstruct tables from the change stream. CDC delivers changes, not state, so a landed change log is not yet something anyone can query. Stored procedures rebuild each entity from its changes, driven by a Python orchestrator that runs them concurrently rather than table by table.
4. Enforce contracts at the boundary. Every source-system topic published a versioned schema. Breaking changes failed CI before they reached production. Downstream consumers became durable.
5. Standardise the transformation layer. One dbt framework for the whole 20-person team, GitHub Actions CI/CD running in Docker containers, pre-merge tests on every model.
6. Ship a semantic layer. Snowflake-native, documentation-backed metric definitions, one source of truth for every number that hit a dashboard.

Reference architecture

There is one path, not several. Changes land in Snowflake via S3 and Snowpipe, are reconstructed into consolidated tables, and are transformed from there. Data contracts sit in front of the dbt layer; the semantic layer sits behind it.

Ingestion: from connectors to a single CDC stream

• AWS DMS as the single log-based CDC source, replacing dozens of per-tenant connectors, with transformation rules applied in the DMS tasks rather than in a downstream processing tier.
• Amazon S3 as the landing zone for change files.
• Snowpipe auto-ingesting from S3 into Snowflake as files arrive, roughly a minute behind the source.

This cut ingestion cost by 90% by sizing the pipeline to actual change volume, not tenant count.

Consolidation: one table per entity, not one per tenant

The estate’s shape was the root cause. Around 2,000 schemas of roughly 85 tables each meant every analytical query was assembled as a union across the whole estate, scanning the same data repeatedly to answer a single question. No amount of warehouse sizing fixes that, because the work is real. The query has to touch every object it names.

• Every tenant’s copy of a given entity now lands in one consolidated table, carrying a tenant schema identifier as a column.
• Queries filter and prune on that identifier instead of naming thousands of objects, so Snowflake’s partition elimination does the work that unions used to.
• Transformations become incremental. Where a full estate scan had been the only option, dbt models now process what changed, which is what took transformation cost and latency down by roughly 80%.

Reconstruction: from a change stream to queryable state

CDC delivers changes, not state. A landed change log is not something an analyst can query, so the pipeline needs a step that turns one into the other, and that step decides whether the pipeline meets its latency budget.

• Stored procedures rebuild each entity from its accumulated changes into the consolidated table.
• A Python orchestrator runs those procedures concurrently rather than sequentially, which is what keeps the reconstruction inside a couple of minutes across an estate this size.

End to end, a change in a source system became queryable in about 3 minutes, against 30 to 60 minutes before. Roughly a minute of that was DMS and Snowpipe landing the change, and one to two minutes was reconstruction.

Standardised dbt framework

• One project layout, one test pattern, one CI workflow for the whole 20-person team.
• GitHub Actions running dbt in Docker containers for reproducible builds and pre-merge testing.
• Test-driven development as the default, with every model shipping with unit tests and data tests.

One pattern replaced twenty, and redundant transformations were collapsed into it. Together with incremental models on the consolidated tables, that took transformation cost and latency down by roughly 80%.

Data contracts and the data-mesh boundary

• Each source-system team owned a published, versioned contract for the data they emitted.
• Breaking changes failed CI before they could reach production.
• Downstream dbt models, dashboards and internal apps became durable to upstream change.

Upstream source systems kept evolving at product-team speed without taking downstream dashboards with them, and schema drift stopped being a category of incident.

Semantic layer

• Snowflake-native semantic layer with documentation-backed metric definitions.
• Every metric had a single owner, a definition and a test.
• Self-serve BI wired to the semantic layer rather than to raw marts.

Ad-hoc “what does this metric mean?” requests to the data team fell away, and the arguments about whose number was right went with them.

Outcomes

The data team stopped being a bottleneck. The Snowflake bill came down. The ingestion and transformation reductions together took roughly $500,000 a year out of the compute spend. Product teams kept shipping changes upstream without breaking downstream. Executives stopped arguing about whose number was right.

Key takeaways

• Size ingestion to change volume, not tenant count. The cost of per-tenant or per-table connectors grows with tenant count, not with business value.
• Shape beats sizing. A union across thousands of objects cannot be tuned into a fast query. Consolidating the estate so the warehouse can prune is what makes the lower cost, the lower latency and the incremental models possible.
• CDC is not the finish line. A change stream is not queryable state. Budget for the reconstruction step, and parallelise it, because that step decides the end-to-end latency.
• Contracts are the part of a data mesh that matters. Without them, “mesh” is just “distributed chaos”.
• Standardisation beats heroics. One dbt framework with CI and TDD across a 20-person team buys more reliability than any monitoring tool.
• A semantic layer is leverage. Once metrics are governed, the data team stops being a help desk and starts being a platform.