SaaS & data, an interactive architecture walkthrough
Data warehouse ingestion pipeline: how the architecture works
How a data pipeline captures database changes, API records and events, lands them in lake tables, and builds tested warehouse models with SQL.
This is a learning explainer based on public engineering writing and general design practice. It is simplified, it is not the actual architecture of any company, and it may be inaccurate.
Interactive diagram
Overview
A data warehouse ingestion pipeline copies data from production databases, third-party tools and product event tracking into one place where analysts can query it. In this design every source lands in raw topics first: database changes come from change data capture on the database's log, SaaS data from incremental API pulls, and product events through a collector that validates schemas.
A stream loader writes the raw data every few minutes into append-only lake tables using an open table format, where each load becomes visible as one atomic snapshot. An orchestrator then runs SQL transformation models in dependency order, from cleaned staging tables to business-level marts, and data quality tests gate what gets published to the warehouse and dashboards. This is a simplified learning design, not any company's data platform.
Trade-offs and what changes at scale
Keeping raw data unchanged and transforming afterwards (ELT) lets models be rebuilt from history when logic changes, at the cost of storing everything twice. Change data capture is near real time and light on the source but must handle schema changes and deletes, which batch exports avoid. Streaming loads keep data fresh but create many small files, so table maintenance has to compact them. At larger scale the hard parts are ownership and contracts, meaning who may change a source schema, rather than throughput, plus the cost of rebuilding large models.
How it works, step by step
The walkthrough above, as text. Each step lights up the components named under it.
The CDC Connector follows the Application DB's change log and publishes every committed insert, update and delete to Raw Topics, one topic per table and in commit order. Reading the log avoids running heavy full-table queries against the production database.
API Extractors pull from SaaS APIs on a schedule, asking only for records changed since the saved cursor, and publish them to the same topics. Storing the cursor only after a successful publish means a failed run repeats rather than skips data.
Product Events from web and mobile apps arrive at the Event Collector, which checks each one against its schema in the Schema Registry. Catching a renamed field at the door is cheaper than finding it in a broken dashboard.
Valid events go to Raw Topics, and invalid ones go to the Dead-letter Topic with the reason. Bad data is kept for fixing and replay instead of being dropped or blocking the stream.
The Stream Loader consumes all raw topics and writes them every few minutes as columnar files. Loading continuously keeps data fresh within minutes and avoids one huge nightly batch.
Files land in Raw Lake Tables, and each micro-batch becomes visible only when the loader commits a new snapshot to the Table Catalog. Readers always see whole batches, and several engines can read the same tables safely.
The Orchestrator checks the catalog for fresh inputs and starts the models that depend on them, in dependency order. Running on data arrival rather than a fixed clock avoids transforming half-loaded data.
SQL Transform Jobs build staging models that keep only the latest version of each CDC row, then core and mart models such as daily revenue per region. Each model is a SELECT statement in version control, so changes are reviewed like code.
Finished models are written to the Warehouse as tables analysts can query directly. Large fact tables are built incrementally, processing only new partitions instead of rebuilding history every run.
Data Quality Tests check uniqueness, missing values and row counts after each build, and a failure stops dependent models from publishing. Stale but correct numbers are better than fresh wrong ones.
BI Dashboards read only the mart tables, and Lineage & Docs shows which source columns each metric comes from. When a number looks wrong, lineage tells an analyst where to start looking.
Observability tracks loader lag, model run status and how old each mart's newest data is. Freshness per table is what consumers of the data actually notice.
Sequence diagram
The same walkthrough as a UML sequence diagram, one lane per layer or group (19 components in all), left to right in the order a request reaches them, numbered by step. A loop on a lane is a step that stays inside that group. Solid arrows are synchronous calls, dashed are asynchronous.
Key components
Ingestdomain
- API Extractors: incremental pulls by cursor
- CDC Connector: streams committed row changes
- Dead-letter Topic: records that failed validation (kafka)
- Event Collector: validates and batches events
- Raw Topics: one topic per table or event type (kafka)
- Schema Registry: event and table schemas
Raw Lakedomain
- Raw Lake Tables: append-only Parquet in a table format (s3)
- Stream Loader: micro-batches into table commits (flink)
- Table Catalog: current snapshot of each table
Transformdomain
- Data Quality Tests: uniqueness, nulls, row counts
- Orchestrator: runs the model graph when inputs are fresh
- SQL Transform Jobs: staging -> core -> marts models
Servelayer
- BI Dashboards: reports and ad-hoc SQL
- Lineage & Docs: where each column comes from
- Warehouse: modelled tables for analysts (clickhouse)
Observability
- Observability: freshness, lag, failed tests
Sourceslayer
- Application DB: orders, customers, payments (postgresql)
- Product Events: web and mobile tracking events
- SaaS APIs: CRM, billing, support tools
Sources
Public writing this explainer draws on. Read these for what the real systems actually do.
- debezium/debezium: change data capture into Kafka topics
- apache/iceberg: open table format for huge analytic tables
- dbt-labs/dbt-core: transforming warehouse data with SQL models
- Apache Airflow docs: Scheduler (triggering tasks when dependencies complete)
- Apache Kafka docs: Design (durable, replayable log)
Make it yours
Copy this diagram and its walkthrough into your own workspace, then change anything: swap Postgres for DynamoDB, add a cache, rewrite the narration. Free, and it uses no AI credits.
Related walkthroughs
- Multi-tenant SaaS analytics pipelineHow a SaaS product collects events from many customers, keeps each tenant's data isolated, and serves fast dashboards from a columnar store.
- Metrics and monitoring systemHow a monitoring system scrapes metrics from every service, alerts through grouped and routed notifications, and keeps years of data in object storage.
- Log aggregation pipelineHow a log pipeline collects lines from every host, buffers them in a topic, indexes only labels, and keeps compressed text in object storage.
This is a learning explainer based on public engineering writing and general design practice. It is simplified, it is not the actual architecture of any company, and it may be inaccurate. Real systems differ in the details. Browse the library.