Data Engineer · Step 2 of 8 · 9 min
SQL and data modelling
Write the SQL a data engineer uses daily and sketch a simple star schema.
SQL is the common language
Almost every data tool speaks SQL: databases, warehouses, lakehouses and many streaming systems. If you learn one thing well in this track, make it SQL.
Start with SELECT, WHERE, GROUP BY and ORDER BY. Then learn JOINs properly, because most bugs in data work come from a join that quietly duplicates or drops rows. After that, learn window functions such as ROW_NUMBER and SUM OVER, and common table expressions with WITH.
Tables have a grain
The grain of a table is what one row means: one order, one order line, one customer per day. Write it down for every table you build. Most confusing numbers come from mixing grains, such as joining order lines to orders and then summing the order total.
Each table should also have a key that identifies a row. Check it: count the rows, then count the distinct keys. If they differ, you have duplicates.
Facts and dimensions
A common way to model data for analysis is the star schema. A fact table holds events you measure, such as sales, with numbers like quantity and price. Dimension tables hold the context: customers, products, dates, stores.
Facts are long and narrow. Dimensions are shorter and wide. Analysts join facts to dimensions to slice the numbers. This design is old, from Ralph Kimball's work in the 1990s, and still widely used because it is easy to query.
Normalised versus analytical
The app's own database is usually normalised: data is split into many tables so each fact is stored once and updates are safe. That is right for running the app and awkward for analysis.
Part of your job is reshaping data from the first form into the second. Knowing why both exist will save you from arguing with the app developers.
Try it
Model a small shop in DuckDB
- 1.Open shell.duckdb.org in your browser. It is free and runs locally in the page.
- 2.Ask a chatbot for the prompt below and paste its SQL into the shell.
- 3.Run one query that joins the fact table to two dimensions and groups by month.
- 4.Check the grain: compare COUNT(*) with COUNT(DISTINCT your key).
Write DuckDB SQL that creates a tiny star schema for a coffee shop: one fact table of sales (one row per item sold) and dimension tables for products, stores and dates. Insert about 30 rows of realistic sample data. State the grain of each table in a comment.