04 Modelling and transformation¶
How raw data becomes trustworthy tables: the engines that run SQL on the lake, the shapes analytical data takes, turning change events into current state and history, combining sources, and building, testing, publishing and rebuilding transformations safely, and the conventions and alternative models around them.
Before starting
01 Foundations, 02 Storage and 03 Ingestion, in particular Medallion layers, Change data capture and Delivery guarantees and idempotency.
Pages, in reading order¶
- SQL query engines: A SQL query engine runs SQL over data it does not own: it reads tables from object storage and other systems through connectors, spreads the work across many machines, and returns the result.
- Dimensional modelling: A way to shape data for analysis as facts, the measurable events of the business, surrounded by dimensions, the context used to filter and group them.
- Deduplication and merge: Turning raw records, with their duplicates and change events, into one correct row per entity: choosing the winning record for each key and applying it to the clean table with MERGE.
- History and slowly changing dimensions: Ways to keep the past values of data that changes over time, such as a customer's address, so that results can reflect the world as it was when an event happened.
- Multi-source conformance: Combining records of the same kind of thing from several sources into one consistent view: mapping their fields and codes, matching records that describe the same real-world entity, and deciding which values win.
- dbt: dbt is a tool for building transformations as SQL SELECT statements in version-controlled files; it works out their order, runs them on a query engine, and tests and documents the results.
- Testing data pipelines: Checking that transformation code is correct and that the data it produces meets expectations, using several kinds of test at different points between a code change and a published table.
- Write-audit-publish: A pattern in which new data is written where consumers cannot see it, checked, and only then made visible in one atomic step.
- Backfill and replay: Re-running a pipeline for past periods, to fill a new table with history, to correct data after a bug, or to apply changed logic, without disturbing the current runs or the people reading the results.
- Naming conventions (optional): Agreed rules for naming schemas, tables, columns and keys, so that a name alone says what a dataset is, which layer it belongs to and how it can be used.
- Other modelling approaches: Data Vault, one big table and activity schema (optional): Alternatives and complements to star schemas: Data Vault for integrating and historising many sources, one big table for simple consumption, and activity schema for modelling behaviour as a single stream of events.