Multi-tenant SaaS products have a standard architecture where we have a dedicated database for each customer/tenant. This database stores product data, user accounts, orders, subscriptions, and payments in tables, and there are hundreds of rows updated in these tables daily. This solution is simple and efficient while the database only needs to serve end-users.
But when the product’s business requirements evolve and it’s necessary to reason about data across customers, these OLTP databases are no longer sufficient for the task. To analyze such a dataset it’s necessary to rewrite it into a data lakehouse: a fundamentally different set of tools, built for a fundamentally different set of operations.
This article describes how we built a data lakehouse for a system that processes 200 million changes to the database’s rows every day. It shares the implementation details behind a few critical decisions, what headwinds we introduced at the time, and what Databricks features ended up being most heavily relied upon.
The solution described in this article follows a Medallion Architecture — organizing the data lakehouse into three broad bands (Bronze, Silver, and Gold layers) that progressively refine raw changes into clean, report-ready datasets.
Figure 1. End-to-end flow: tenant OLTP databases → Debezium CDC → Kafka → Databricks Bronze and Silver (AUTO CDC SCD Type 1) → reporting, cross-tenant analytics, and exports. Unity Catalog governs access over environments.
Also Read: Declarative Lakehouse Pipelines: Oracle & MongoDB Integration
Capturing changes at multi-tenant scale
The first challenge, before any data transformation had happened, was getting this data out of the databases in the first place. This step is referred to as Change Data Capture (CDC), and it’s a critical part of what differentiates a data lakehouse from a standard data warehouse.
While it’s possible to query the databases directly and attempt to capture changes that way, doing so at the necessary scale would put an unacceptable strain on the database servers. Instead, we’ve instrumented our databases with Debezium, an open-source CDC platform.
Debezium streams directly from the database’s transaction log, which captures all changes, including inserts, updates, and deletes, without any additional overhead. It’s an extremely lightweight and scalable change capture solution that allowed us to avoid placing any additional strain on the database servers.
Debezium forwards all of these changes directly into Kafka, which then serves as a transport mechanism into Databricks. It’s Kafka’s job to smooth out bursts of data and to reliably deliver all messages into downstream systems such as Databricks. It allowed us to decouple the rate at which data changes occurred from the rate at which downstream systems consumed them. It also allowed us to process individual messages in near-realtime instead of having to schedule and manage batch jobs.
Once the data changes are in Kafka, it’s Databricks’ turn to process them. This concludes the data ingestion pipeline and marks the beginning of the actual data transformation process.
Why databricks, and what it brought to the table
Once the data has been ingested and placed into persistent storage, it’s necessary to perform a few cleaning and validation operations before it can become report-ready data. This transformation needs to happen on a per-table, per-customer basis while ensuring zero downtime for any given customer’s tables.
There are several reasons why Databricks was the obvious choice for this particular use case:
- Lakehouse capabilities such as Delta Lake allowed us to keep all tables transaction-consistent, even with numerous concurrent transformations running.
- Lakeflow Declarative Pipelines (DLT) allowed us to declaratively define the shape of our target tables, avoiding any hand-written orchestration logic as Databricks managed all dependencies and transformations.
- Flexible run modes allowed the most critical tables to be processed in near-realtime while others were batch-processed, with no additional software or infrastructure requirements.
- Built-in monitoring and alerting capabilities provided out-of-the-box insight into the performance and data quality of all transformations without any additional dashboards or tooling.
- Unity Catalog allowed fine-grained access controls to be applied consistently across all environments without any modifications to the transformation logic itself.
These features, used together, were responsible for most of the business value that Databricks brought to the table. While none of them individually would have been sufficient for this use case, their combination allowed us to achieve significantly better results than we would have with any individual tool or platform.
Bronze: The one decision that made everything else possible
After data has been ingested into Databricks, it’s stored as records in a set of tables that comprise the Bronze layer of the Medallion Architecture. It’s in this layer that we’ve made a decision that affected the rest of the architecture and the transformation logic in downstream stages.
At this point in the pipeline, it would have been possible to store each customer’s data in a separate table or even pipeline. However, we elected to keep all customers’ data in shared tables. Each record in a Bronze table contains an additional column (tenant_id) to identify which customer’s data it belongs to:
Tenant A ─┐
Tenant B ─┤
Tenant C ─┼──▶ Bronze orders table + tenant_id
Tenant D ─┤
Tenant E ─┘
- This approach paid dividends in downstream processing. We only had to write generic transformation logic that could handle any customer, and it immediately worked for all of our customers without additional modifications.
- Similarly, adding a new customer only required us to write their data into tables that already had the required format, without any additional transformation logic.
- And if ever necessary to isolate a particular customer’s data for processing, it was possible to do so downstream with minimal additional effort.
At this point in the pipeline, we deliberately avoided any transformation or validation logic. Every column in a Bronze table is stored as StringType to avoid any additional processing overhead.
We also added a failsafe mechanism to capture any records that might have unexpectedly additional or missing fields, to avoid disrupting the downstream processing for the majority of records just because of a few malformed entries from a particular customer.
Bronze to silver: From raw events to trusted data
This is where the real work happens. Silver is not only about data cleaning, but also about making a continuous stream of CDC events that can be reliably queried and used for current-state tables and views. It is all about validation, casting, deduplication and materializing the latest version of records.
It becomes more interesting when we are working within a multi-tenancy setup. Same logical entities live inside the same tables, but belong to different tenants and need to be treated as such when applying updates, deletes and handling out-of-order events. Updates and deletes to Tenant A should not affect Tenant B’s records, even though they live in the same physical table.
Databricks’ AUTO CDC capability handles this elegantly. Rather than writing custom MERGE logic for every table, dlt.create_auto_cdc_flow() materializes the latest state of each record as SCD Type 1 (Slowly Changing Dimension Type 1).
dlt.create_auto_cdc_flow(
target=target_table,
source=view_name,
keys=[“tenant_id”] + business_keys,
sequence_by=sequence_columns,
apply_as_deletes=col(“operation_type”) == “d”,
stored_as_scd_type=1
)
create_auto_cdc_flow() is a declarative DLT function that automates the entire CDC-to-table materialization. Under the hood, it:
- Orders incoming events using the sequence_by columns to handle out-of-order arrivals.
- Deduplicates so that replayed or duplicate events do not corrupt the target table.
- Applies deletes when the CDC operation type signals a delete.
- Merges inserts and updates into the target Delta table using the specified keys all without the developer writing a single MERGE INTO statement.
Note that the tenant_id column is part of the composite key. By specifying this column as part of the primary key, Databricks will ensure that all deletes, inserts and updates are processed correctly and only for a specific tenant. We do not have to worry about accidentally deleting or updating records of other tenants as well. The same applies to out-of-order events. Databricks will make sure that they are processed in the correct order so that we always have the latest values for each record.
Pipelines can be tiered by business criticality. High-priority tables process in near real-time, while lower-priority tables run on triggered schedules. The same pattern applies across all tiers: stream from Bronze, transform, and materialize via AUTO CDC.
What this architecture enables
Building a lakehouse for our multi-tenancy SaaS system (with hundreds of tenants, dozens of source tables per tenant, and CDC) is an engineering challenge that goes beyond selecting the right tools; it involves building isolation for tenants inside the shared infrastructure, building resilience to unexpected data patterns of any particular tenant, and delivering overall simplicity of operation at scale.
Our choice of Databricks as the primary data processing engine gives us all three:
- Seamless tenant onboarding: new tenants flow into existing pipelines with zero code changes.
- Scalable CDC processing: tiered pipelines with appropriate latency guarantees for each set of tables.
- Unified processing logic: a single pipeline definition handles all tenants for a given table.
- Resilient data quality: issues are isolated and observable, never pipeline-breaking.
- Governed environments: the same codebase serves development and production through Unity Catalog.
It has given us a reliable and maintainable data platform to power our analytics stack, which is crucial when processing hundreds of millions of events a day for hundreds of tenants.
Conclusion
There were a number of factors that contributed to this architecture’s overall success. But looking at it in hindsight, the most valuable decisions were the ones that helped keep tenant data isolated while still allowing it to be consistently processed.
The choice to capture all change data without additional overhead made it possible to scale this approach to hundreds of tenants. The same choice made it possible to utilize existing infrastructure to stream this data to downstream consumers. Finally, implementing transformations in a way that keeps tenant data consistently separated allowed this architecture to avoid additional complexity or maintenance overhead in the long run.
The most valuable lesson from this experience for a similar project is that multi-tenancy should be considered at all levels of such a system. The tenant ID should be consistently tracked and utilized throughout the pipeline, from the initial change data capture to the final table schemas. This allows the same system design and implementation details to be consistently utilized across all tenants, and helps avoid the additional maintenance overhead that would have been incurred otherwise.
Databricks provided an environment where all of these design choices were easy to implement and immediately beneficial. This allowed us to separate all of our tenants’ data at every stage of the pipeline while also avoiding unnecessary maintenance and additional infrastructure costs.
About author
Dipen Mistry is an Application Architect and alumnus of NIT Goa with 12+ years of experience building software products. He specializes in Data applications, Databricks backed solutions, Microservices, AWS, Node.js, NestJS, and React.js. Dipen excels at translating complex product visions into highly scalable, cloud-native software solutions.
Also Read
Transforming Channel Marketing Analytics with Databricks Lakehouse
