Queries, Modeling, and Transformation

In one sentence

You think the value of data engineering lies in moving and storing data fast; in fact it lies in this chapter: queries, modeling and transformation turn raw data into something that answers business questions. Three ideas to take away: read the query plan and scan as little as possible; a data model freezes the business's language into structure, so definitions come before technology; and transformations are rerunnable code, with batch and streaming differing only in cadence.

Glossary

Where it sits

The fourth chapter of Part II, "The Data Engineering Lifecycle in Depth". It follows Chapter 7 on ingestion (the data has landed in storage) and leads into Chapter 9 on serving data for analytics and ML. It covers the lifecycle's transformation stage, which the authors describe as where data begins to create value for the business. In practice: warehouse modeling, dbt projects, Spark batch jobs, Flink streaming jobs.

What problem this chapter solves

Picture an online retailer: an orders table of 3 billion rows, three years of history, about 2 TB, growing by 5 million rows a day. Analysts run hundreds of queries daily against a cloud warehouse billed by bytes scanned or by compute. Typical consequences of ignoring this chapter:

• Queries burn money. One SELECT * without a date filter scans all 2 TB, when what was needed was three columns from the last seven days.
• Definitions collide. "Active user" or "GMV" is computed five ways in five dashboards, and meetings are spent reconciling numbers.
• History gets rewritten. A customer moves city, the dimension row is overwritten, and last year's "sales by city" changes overnight.
• Transformations are scattered scripts. Hundreds of unowned scheduled SQL jobs; rename one upstream field and nobody knows what breaks downstream.

The core ideas, explained

1. The life of a query: learn to scan less

The authors first place the query within SQL's family: DDL defines structure (CREATE), DML manipulates data (SELECT, INSERT), DCL controls access, TCL controls transactions. A submitted query goes through four steps: parsing (check syntax, convert to an internal form) → planning and optimization (the query optimizer decides which table to read first, which join to use, what can be skipped) → execution → returning results. The same SQL can differ by orders of magnitude in runtime under different plans.

The book's checklist for faster queries: optimize joins: pre-join common combinations into tables, beware many-to-many joins that explode row counts, and use CTEs (common table expressions, named sub-queries written step by step) for readability. Use the explain plan: EXPLAIN shows what the optimizer intends and which step is costliest. Avoid full table scans: select only the columns you need (column pruning) and let partitions and clustering skip irrelevant blocks (pruning). Know how your database handles commits: transaction and consistency models differ, and decide whether you see data someone just wrote. Vacuum dead records: updates and deletes leave old row versions behind. Use cached results: many cloud warehouses return a cached answer for a repeated query.

Using the table above: reading 3 of 100 columns cuts the scan to about 3%; querying 7 days out of three years cuts it again to about 0.6%. Together, 2 TB becomes roughly 0.4 GB.

2. Queries on streams: windows, watermarks and stream joins

For streaming data the authors describe two architectural approaches. The fast-follower: use CDC to replicate the production database in real time into an analytical database, and point queries at the replica rather than production. The Kappa architecture: treat the event-streaming platform itself as primary storage, keep enough history, and replay from the start when you need to recompute.

Aggregating a stream requires windows: fixed (tumbling) windows, e.g. non-overlapping 5-minute slots; sliding windows, e.g. computing "the last 5 minutes" every minute, so windows overlap; and session windows, cut by user activity and closed after a period of inactivity. Because data arrives late, the engine uses a watermark to judge that data before a given time has mostly arrived, so a window can close and emit; data later than the watermark needs separate handling. Streams also get combined with other data: enrichment adds dimension attributes to each event, while joining two streams means buffering recent data from both sides until matches arrive, and how long that buffer is kept trades cost against accuracy.

3. Data models: pin down the business's language

The authors define a data model plainly: how data relates to the real world. A good model reflects the business's concepts and rules: who a "customer" is, when "an order" counts. The book stresses that many organizations skip modeling and just pile up data, ending with data swamps and contradictory metrics. Modeling has three levels: the conceptual model (business entities and relationships, often an ER diagram) → the logical model (fields, types, primary and foreign keys) → the physical model (which database, how it is partitioned).

The first modeling decision is the grain: what does one row represent, an order or a single item within an order? Model at the finest grain you can, since fine grain rolls up to coarse but never the other way. Then comes normalization, Codd's rules for removing redundancy: first normal form makes every field atomic, second removes dependence on part of a composite key, third removes transitive dependencies between non-key fields. Normalization stores each fact once so a change touches one place, at the cost of more joins at query time.

4. Four schools of batch analytical modeling: Inmon, Kimball, Data Vault, wide tables

Inmon: Bill Inmon, often called the father of the data warehouse, defines it as a "subject-oriented, integrated, nonvolatile, and time-variant" collection of data supporting management's decisions. You first build a highly normalized (third normal form) enterprise warehouse as the single source of truth, then derive department-facing data marts from it. Top-down, strongly consistent, heavy up-front investment.

Kimball: bottom-up, modeling directly for analysis with a star schema. At its center is the fact table: one row per business event, holding numbers and keys to dimensions, append-only and immutable, narrow and long. Around it sit dimension tables describing who, what, where and when, wide and short; a date dimension is nearly universal. Dimensions shared across several fact tables are conformed dimensions, keeping definitions consistent across subjects. Dimensions change, hence slowly changing dimensions (SCD): Type 1 overwrites and keeps no history; Type 2 adds a new row with validity dates, preserving full history; Type 3 adds a column holding the previous value, keeping one level of history.

Data Vault: proposed by Dan Linstedt, it splits data into three kinds of tables: hubs (business keys only, such as customer number), links (relationships between hubs only), and satellites (descriptive attributes and their history). Insert-only, never updated; when sources change you just add tables. It suits enterprises with many changing sources and audit requirements; the cost is many tables, and usually another analysis-friendly layer on top before querying.

Wide denormalized tables: flatten facts and dimensions into one table that may have thousands of columns. This used to mean redundant storage and painful updates; with columnar storage, empty columns cost almost nothing and only the used columns are read, so wide tables query fast and are easy for analysts. The authors are pragmatic: wide tables are good for fast iteration, but adding columns ad hoc strips business logic of structure, so they still need governance.

5. Transformation: write the preparation as rerunnable code

The authors separate queries from transformations: a query retrieves data to answer a question, while a transformation persists its result for repeated downstream use, usually orchestrated as a scheduled pipeline.

Distributed joins. When one table is small enough to fit in each node's memory, use a broadcast join: copy the small table to every node and leave the big one in place. When both are large, use a shuffle hash join: redistribute both sides across nodes by join key, at much higher network cost. Hence the common optimization: filter and aggregate first to shrink one side until it can be broadcast.

SQL or code. SQL is declarative and can express far more complex transformations than people assume, so it should be the default; but complex procedural logic or ML feature processing fits code frameworks like Spark better. With Spark the authors advise filtering early, relying on the built-in APIs, and avoiding Python user-defined functions (UDFs), which shuttle data between the JVM and Python.

Update patterns. (1) Truncate and reload: wipe and recompute everything; simple but expensive. (2) Insert only: append new versions as new rows and read the latest; avoid single-row inserts into columnar stores. (3) Delete: hard deletes remove data, soft deletes only flag it; compliance may require hard deletes. (4) Upsert / merge: update if the key exists, insert otherwise. In columnar storage an update often means rewriting a whole file block, so frequent small merges are costly and should be batched. Schemas evolve with the business too, so design to allow added columns.

Views, materialized views, federated queries. A view stores only the query and computes on each use; a materialized view stores the result and refreshes periodically or incrementally, trading storage for speed. A federated query lets one engine query external sources directly; data virtualization goes further, fetching across systems at query time without moving data. Flexible, but it pushes query load onto the source systems.

Streaming transformations. These process data continuously in flight, often as a directed acyclic graph (DAG) of steps. On the debate between micro-batch (e.g. Spark Streaming batching every few seconds to minutes) and true streaming (e.g. Flink processing event by event), the authors' answer is: ask how fast the business really needs results. If seconds are fine, micro-batch; if milliseconds are required, true streaming, verified by testing.

Key trade-offs and choices

Table 1 · Four schools of batch analytical modeling

Table 2 · Slowly changing dimensions and update patterns

Table 3 · Choices in querying and compute

Rule of thumb: fix the grain and definitions first, then pick a modeling approach. For analytics on a columnar cloud warehouse, a Kimball star or wide table is the usual starting point; consider Data Vault only with many sources and audit needs. Any dimension used for historical attribution defaults to Type 2.

Why it matters / real systems

A strong answer to the interview question "design a data warehouse for an online retailer" lands like this: a raw layer keeps ingested data as is; a middle layer cleans, deduplicates and unifies definitions; the serving layer follows Kimball with an order-lines fact table and customer, product and date dimensions, the customer dimension as Type 2; every transformation is a SQL model under version control with data tests, run incrementally each hour in dependency order; tables are partitioned by date and clustered by customer; fraud metrics run separately as Flink sliding windows. Each step maps to a concept in this chapter.

Pitfalls and debates

• "Modeling is obsolete; just query the columnar warehouse." Compute got cheaper; the cost of muddled definitions did not. The authors explicitly argue against skipping modeling.
• "More normalization is always better." It is designed for write consistency; analytics often denormalizes on purpose for query speed.
• "Just overwrite a changed dimension." Type 1 silently rewrites historical reports; whether to keep history is a business decision, not a default.
• "A coarser grain saves space." Coarse grain can't be split later, so the next question can't be answered.
• "Upserts are cheap." In columnar storage each merge may rewrite files; row-by-row updates kill performance.
• "Streaming means true streaming." Micro-batch is enough for most cases; ask what latency the business accepts.
• "Kimball versus Inmon is either-or." In practice they mix: normalized or Data Vault underneath, star or wide tables on top.
• Dated by its time. The book came out in 2022; lakehouse table formats have since made merges and time travel common, but the judgments about grain, definitions and preserving history still hold.

Key takeaways