Making Data Useful: Queries, Modeling and Transformation, Reading Fundamentals of Data Engineering, Ch. 8

Data has been ingested and stored; the next question is: how do you turn it into something genuinely useful? This chapter gives three pillars — queries, modeling, transformation. Together they turn “a pile of raw data” into “a model the business can ask questions of, and that answers fast”. Of the three, modeling is the one engineers underrate most and the one that matters most, so that’s where this post puts its weight.

Queries: SQL is the lingua franca, and the optimizer does the work

The core of querying is SQL — you say declaratively “what I want”, and the query optimizer decides “how to get it” (whether to use an index, join order, how much to scan). So half the skill of writing queries is not getting in the optimizer’s way, and not forcing it into stupid moves:

Common landmine	What to do instead
Full table scan	Use partitions / indexes / columnar; read only what you need
SELECT *	Take only the columns you need (especially noticeable in columnar warehouses)
Joining a pile of big tables carelessly	Join is the most expensive step (see shuffle); filter first, then join
Wrapping a column in a function in the where clause	Let the predicate be pushed down to the lowest layer

In one line: SQL is declarative, but performance isn’t automatic — you have to give the optimizer room to work.

Modeling: translating “the business” into “tables”

Modeling is deciding what shape the data takes. Why does it matter? Because the structure decides whether queries are easy to write, whether they run fast, and whether non-engineers can query on their own. The first trade-off to face is how far to normalize:

OLTP loves normalization (little duplication, consistent writes); analytics often goes the opposite way — because columnar storage is cheap and fast, duplication doesn’t hurt, and one join fewer saves a lot. That’s the modeling-layer extension of Ch. 5‘s “don’t run analytics directly on OLTP”.

The classic of analytical modeling: the star schema

Kimball’s dimensional modeling is the most universal move in analytics: split data into two kinds of table — the fact table records additive measures, the dimension tables describe context:

The star schema works because it cleanly separates “what to compute (facts)” from “what angle to cut by (dimensions)”. Want “sales by region by month”? Join the store and date dimensions and sum the amount — even a non-engineer can slice it. The book also covers the other schools, each with its own position:

School	In one line
Kimball	Bottom-up, dimensional modeling, star schema (the most universal)
Inmon	Top-down, build a normalized enterprise warehouse first, then cut data marts
Data Vault	Hubs / links / satellites, built for traceability and evolution
One Big Table	Flatten everything into one giant table and let the columnar engine chew it

No need to memorise which is “right” — they’re different trade-offs: Kimball is understandable and self-serviceable, Inmon prioritises governance, Data Vault prioritises traceability, the wide table prioritises query speed.

Transformation: turning raw data into the model, and the T moved

With the model’s blueprint in hand, transformation does the actual shaping of raw data into it. The biggest change here is ETL → ELT:

• ETL: transform first, then load into the warehouse (the warehouse is expensive, so only clean data goes in).
• ELT: load as-is first, then transform inside the warehouse with SQL (storage is cheap and the warehouse is strong enough, so let it carry the work).

ELT wins because, at root, compute and storage separated and storage got cheap: storing first costs nothing, so hand transformation to the warehouse’s SQL. That’s also the underlying premise of Medallion layering and tools like dbt — layer after layer of SQL inside the warehouse, turning Bronze raw data step by step into Gold models. Transformation isn’t only batch either: streaming transformation (see Structured Streaming) computes and shapes the data as it flows past. As for views vs materialized, that’s another classic trade-off: a view computes fresh at every query (saves space, slow); materialization precomputes and stores (fast, takes space, needs refreshing).

Reflections

Modeling is translating the business into tables — the most underrated soft skill

What I felt most writing this chapter: the hard part of modeling isn’t technical at all, it’s understanding the business. The star schema is beautiful not because the technique is flashy but because it forces you to think through “what are this business’s measures, and which angles does it cut by”. I’ve seen too many data projects stall not because the engine was too slow but because nobody translated the business question into a clean model — the tables looked like a backend OLTP schema, and analysts had to join seven tables for every number and often got it wrong. Modeling data so that non-engineers can self-serve is, I think, the skill data teams most need to practise and least often do.

I prefer ELT + layering + dbt, and the reasons chain all the way back

Over these posts my position has become consistent: land raw data in cheap storage first (the L of ELT), then transform layer by layer in SQL (the T). It isn’t chasing fashion — it connects straight back to compute/storage separation and Medallion’s immutable, replayable Bronze. Transforming in SQL rather than a pile of scattered scripts gives you version control, testability and readability for everyone (dbt turned that into an engineering standard). ETL isn’t wrong, but in today’s “storage cheap, warehouse strong” world, ELT is my default.

Don’t carry the OLTP normalization instinct into analytics

This is a hole I fell into when I was younger: coming from backend, duplicated data made my skin crawl, so I normalized the analytics tables beautifully — and every report joined furiously and crawled. Only later did it click: on a columnar warehouse, duplication is cheap and one join fewer is a big win. The question for analytical modeling isn’t “is it normalized enough” but “are queries easy to write and fast to run”. It’s exactly Ch. 4‘s “every choice is a trade-off; pick sides by workload” — normalization is a virtue for writes, and analytics wants a different set.