Lesson 10 · Transform, warehouse & the whole pipeline

The data warehouse

Where the refined stream lands — a star schema for analytics — and the unusual trick of turning the warehouse itself back into a CDC source.

Your win: describe the warehouse's star-schema shape and the medallion arc that produced it, and explain chained CDC and how a single warehouse serves multiple partners without leaking tenants.

The destination: a star schema

The LMS data warehouse (lmsdwh) is the analytics/reporting destination. Its tables are a classic star schema: dimension tables (dim_user, dim_student, dim_course, dim_location…) describing things, and fact tables (fact_student_enrollment, fact_assessment_result…) recording events, referencing the dimensions.¹ It's deliberately denormalized — dim_user carries the user and their group columns together (from the ksqlDB join, Lesson 9) — so a BI query is a simple join, not a ten-table maze.

The medallion arc (why this course is gold) Data engineering names the refinement stages like precious metals: raw change (the CDC topics) → reshaped/joined (ksqlDB streams & tables) → gold (the query-ready dim_*/fact_* warehouse tables). Each stage is more query-friendly and further from the operational schema. The whole pipeline you've learned is that arc: capture the raw, refine in flight, land the gold.

Chained CDC: the warehouse is also a source

Here's the genuinely unusual part. lmsdwh isn't just a sink — it's also a source. Its migration sets up its own publication, signal table, and heartbeat, exactly like a captured service:

migrations/lmsdwh/1001_migrate.up.sql:6-16

CREATE TABLE public.lmsdwh_dbz_signals ( id TEXT PRIMARY KEY, type TEXT, data TEXT );  -- its OWN signal table
CREATE PUBLICATION lmsdwh_publication;                                          -- its OWN publication

So a second Debezium source captures lmsdwh's dim_*/fact_* tables and re-emits them — and per-partner sink connectors land them in each partner's own data warehouse. That's chained CDC: services → lmsdwh → partner DWHs, two Debezium hops in series (two publications, two slots, two registries).

services' Postgres ──CDC──▶ ksqlDB ──▶ lmsdwh (dim_*/fact_*) ──CDC again──▶ partner DWHs └─ the "gold" warehouse ─┘ (Tokyo JPREP, …)

One warehouse, many partners — without leaking tenants

The platform is multi-tenant (recall resource_path from the Auth course). A partner's warehouse must receive only that partner's rows. That's enforced right in the sink connector with a third SMT — a filter:

…/generated_connectors/sink/tokyo/prod/lmsdwh_to_tokyo_jprep_dwh_dim_student.json:12-15

topics    = "prod.tokyo.lmsdwh.public.dim_student"          // lmsdwh's OWN CDC topic (chained)
transforms = "unwrap,route,filterResourcePath"            // ← a THIRD SMT
transforms.filterResourcePath.type = "io.confluent.connect.transforms.Filter$Value"
transforms.filterResourcePath.filter.condition = "$[?(@.resource_path =~ /-2147483647/)]"  // only this tenant
transforms.filterResourcePath.filter.type      = "include"

Tenant isolation, in the connector The Filter$Value SMT applies a JSONPath predicate on resource_path — include only rows matching this partner's tenant id. So the Tokyo JPREP warehouse receives exactly Tokyo JPREP's students, and no one else's, even though every partner reads from the same lmsdwh tables. Multi-tenancy enforced at the pipeline layer, not just the database — the same isolation idea as RLS (Auth course), applied to streaming.

Trust but verify: accuracy reconciliation A multi-hop, at-least-once pipeline could silently drift. So there are accuracy verifiers (internal/lmsdwh/internal/verify/) and a hephaestus_dwh_accuracy job that reconcile row counts between the source and the warehouse per table. If the warehouse falls behind or drops rows, the counts diverge and it's caught — the pipeline audits itself.

Dimensional modeling & CDC to a warehouse

The star-schema (dimension/fact) model the warehouse uses, and how CDC feeds analytics stores.

→ Kimball — dimensional modeling (star schema)
→ in-repo internal/lmsdwh/entities/ · migrations/lmsdwh/1001_migrate.up.sql · …/sink/tokyo/prod/lmsdwh_to_tokyo_jprep_dwh_dim_student.json

Check yourself (from memory)

Q1. What shape are the warehouse tables?

dim_* (things) + fact_* (events), denormalized for simple analytics joins — the "gold" layer.

Q2. What is "chained CDC" here?

lmsdwh is both sink and source: it has its own publication/slot/ signals, so a second CDC hop fans it out to per-partner DWHs.

Q3. How does a partner warehouse receive only its own tenant's rows?

A filter SMT with a JSONPath on resource_path — tenant isolation enforced in the connector, like RLS applied to the stream.

Recall: the warehouse — star schema, medallion, chained CDC, tenant filter.

shape + arc + chain + isolation, then reveal

lmsdwh = the LMS analytics warehouse: a star schema — dim_* (things: user/student/course) + fact_* (events: enrollments/ results), denormalized for BI. Medallion arc: raw CDC → ksqlDB reshape/join → gold dim/fact tables. Chained CDC: lmsdwh is BOTH sink AND source — its migration creates its own lmsdwh_publication + lmsdwh_dbz_signals + heartbeat, so a second Debezium source re-emits its tables to per-partner DWHs (services → lmsdwh → partners; two hops). Tenant isolation: partner sinks add a third SMT Filter$Value with JSONPath $[?(@.resource_path =~ /tenant/)], include-only → each partner gets only its rows. Accuracy verifiers reconcile row counts (hephaestus_dwh_accuracy).

🎯 Interview one-liner "Where does the CDC data end up?" → "A star-schema analytics warehouse — denormalized dimension and fact tables built by ksqlDB joins. Unusually, the warehouse is itself a CDC source: a second Debezium hop re-emits it to per-partner warehouses, and each partner sink filters by tenant with a JSONPath SMT, so one warehouse serves many partners without leaking data. Accuracy jobs reconcile row counts to catch drift."

Next: the payoff — the full end-to-end trace of one row change through every hop, and the delivery guarantees that make it correct. Ask me about star schemas or chained CDC if you want more depth.

1. Kimball — dimensional modeling. In-repo: migrations/lmsdwh/1001_migrate.up.sql:1-52, …/sink/tokyo/prod/lmsdwh_to_tokyo_jprep_dwh_dim_student.json:12-15.