Learn data engineering, with practice after every lesson
8 lessons, about 135 minutes of reading, and 24 multiple-choice questions. Each lesson names the mistake that most often costs people the interview, because that is where the hours actually go. Part of the free coding course.
What a data pipeline actually is
BasicsA pipeline moves data from where it is created to where it gets used, changing its shape on the way. Somebody's app writes a row when a customer orders something; twelve hours later an analyst needs that order counted, joined to the customer, converted into rupees and sitting in a table that a dashboard reads in under a second. Everything between those two states is the pipeline, and building it is the job.
The three letters you will see everywhere are ETL and ELT — extract, transform, load, in either order. The order matters more than it sounds. ETL transforms before loading, which was necessary when storage was expensive and compute was scarce. ELT loads the raw data first and transforms it inside the warehouse, which is what almost everyone does now because storage is cheap and warehouses are fast. If a job description says ELT and dbt, it is telling you which of these two worlds you are entering.
The part that surprises people moving from analysis into engineering is how much of the work is about failure rather than transformation. The transformation is usually twenty lines of SQL. The pipeline around it — what happens when the source is late, when a row arrives twice, when yesterday's run half-finished — is the other ninety percent.
Syntax
# A pipeline is usually four steps and a lot of error handling.
# 1. EXTRACT — pull from the source
orders = read_postgres("SELECT * FROM orders WHERE updated_at >= :since", since=last_run)
# 2. LOAD — land it raw first (ELT), so you can always reprocess
write_parquet(orders, f"s3://raw/orders/dt={run_date}/")
# 3. TRANSFORM — in the warehouse, where the compute is
"""
CREATE OR REPLACE TABLE analytics.daily_orders AS
SELECT order_date, country, COUNT(*) AS orders, SUM(total_inr) AS revenue
FROM raw.orders
WHERE status = 'confirmed'
GROUP BY 1, 2;
"""
# 4. VERIFY — the step people skip, and the one that catches the outage
assert row_count("analytics.daily_orders") > 0, "empty table — upstream probably failed"Key points
- ELT (load raw, transform in the warehouse) is the modern default. ETL still exists in older estates and in places where data cannot legally land unprocessed.
- Always keep the raw landing copy. When a transform turns out to be wrong six weeks later — and it will — reprocessing from raw is the difference between a morning and a quarter.
- A pipeline without a verification step is a pipeline that fails silently. An empty table looks exactly like a quiet Sunday until somebody asks why revenue was zero.
Practice challenge
Put these four steps in the order an ELT pipeline runs them, and name the one that is most often skipped: transform in the warehouse, verify the output, extract from source, land the raw copy.
1. extract from source
2. land the raw copy
3. transform in the warehouse
4. verify the output
Most often skipped: verify1. ______
2. ______
3. ______
4. ______
Most often skipped: ______Show a hint
- The L comes before the T — that is what ELT means
- The skipped one is why an empty table looks like a quiet Sunday
Check yourself
1. What does the L coming before the T in ELT mean in practice?
Show answer
A. Data is loaded raw, then transformed inside the warehouse
2. Why keep the raw landing copy after transforming?
Show answer
B. So history can be rebuilt when a definition changes
3. What does an empty output table most often mean?
Show answer
B. Something upstream failed and nobody was told
Batch, streaming and where Kafka fits
Working levelBatch processing runs on a schedule: every night at two, every hour on the hour. Streaming processes each event as it arrives. Almost every company runs mostly batch and describes itself as real-time, because batch is simpler, cheaper and correct more often. The honest question in an interview is not 'can you do streaming' but 'do you know when it is actually needed', and the answer is when a decision has to be made before the next batch would have run — fraud checks, live inventory, alerting.
Kafka is the piece that appears in nearly every data engineering job description, and it is worth being precise about what it is. It is a durable, ordered log that producers write to and consumers read from at their own pace. That last part is the whole point: the consumer holds an offset, so a slow or crashed consumer does not lose data or slow the producer down. It is a buffer between systems that would otherwise have to run at the same speed as each other.
The concept that trips people in interviews is delivery semantics. At-most-once can drop messages, at-least-once can deliver duplicates, and exactly-once is expensive and narrower than the name suggests. Most production systems choose at-least-once and make the consumer idempotent — writing the same message twice produces the same result — which is a far more reliable answer than claiming exactly-once.
Syntax
# Producer — writes an event, does not care who reads it
producer.send("orders", key=order_id, value={"id": order_id, "total": 1299})
# Consumer — reads at its own pace, tracks its own offset
for msg in consumer:
order = msg.value
# Idempotent write: running this twice must not double-count.
# The unique key is what makes at-least-once delivery safe.
warehouse.upsert("orders", key=order["id"], row=order)
consumer.commit() # commit AFTER the write, never before
# Batch equivalent, for comparison — same result, once a night:
# INSERT INTO orders SELECT * FROM staging WHERE dt = CURRENT_DATE - 1;Key points
- Kafka decouples producer speed from consumer speed. That buffering is the reason it is used, more than the throughput numbers people quote.
- Commit the offset AFTER the write succeeds. Committing first means a crash loses the message permanently — the one ordering mistake that causes real data loss.
- At-least-once plus an idempotent consumer is the standard production answer, and a stronger one in an interview than claiming exactly-once.
Practice challenge
A Kafka consumer commits its offset immediately on receiving a message, then writes to the warehouse. Say what is lost when the process crashes between those two steps, and give the corrected order.
The message is lost permanently — the offset says it was handled, so it is never redelivered
Process and write first, then commit the offsetWhat is lost: ______
Correct order: ______Show a hint
- The offset is a promise that the message was dealt with
- Committing late risks a duplicate; committing early risks a loss — one of those is recoverable
Check yourself
1. What problem does Kafka's consumer offset solve?
Show answer
B. It lets consumers read at their own pace without losing data
2. When should the consumer commit its offset?
Show answer
B. After the write succeeds
3. What is the usual production choice for delivery semantics?
Show answer
B. At-least-once with an idempotent consumer
Orchestration, idempotency and backfills
AdvancedOnce you have more than about three jobs, something has to decide what runs, in what order, and what happens when a step fails. That is orchestration, and Airflow is the tool named most often, with Dagster and Prefect close behind. The model is a DAG — a graph of tasks with dependencies and no cycles — so the orchestrator knows that the transform cannot start until the extract finished, and that a failure halfway through should not silently leave the warehouse half-updated.
Idempotency is the property that makes all of this survivable. A task is idempotent when running it twice produces the same result as running it once. In practice this means writing with a deterministic partition — overwrite the data for 14 August rather than appending it — so a retry replaces yesterday's output instead of doubling it. Without idempotency, every retry is a decision about whether the data is now wrong, and every backfill is a risk.
A backfill is rerunning history: the logic changed, or a bug ran for three weeks, and now sixty partitions need rebuilding. This is where idempotency stops being theoretical. With it, a backfill is a loop over dates that you can run twice by accident and still trust. Without it, it is a manual reconciliation that engineers dread and often get wrong. Interviewers ask about backfills precisely because the answer reveals whether you have run a pipeline in production or only built one.
Syntax
# Airflow-style DAG: dependencies, retries, and a date the task can be re-run for
with DAG("daily_orders", schedule="0 2 * * *", catchup=True,
default_args={"retries": 2, "retry_delay": timedelta(minutes=5)}) as dag:
extract = PythonOperator(task_id="extract", python_callable=pull_orders)
transform = SQLOperator(task_id="transform", sql="sql/daily_orders.sql")
verify = PythonOperator(task_id="verify", python_callable=assert_not_empty)
extract >> transform >> verify # the dependency graph
# The transform is idempotent because it REPLACES one partition:
# DELETE FROM analytics.daily_orders WHERE order_date = '{{ ds }}';
# INSERT INTO analytics.daily_orders SELECT ... WHERE order_date = '{{ ds }}';
#
# Backfill three weeks — safe to run twice, because each date overwrites itself:
# airflow dags backfill daily_orders -s 2026-07-24 -e 2026-08-14Key points
- A DAG encodes dependencies, not just order. The orchestrator can then retry one failed task rather than rerunning the whole chain.
- Idempotency usually means writing by partition and replacing it, not appending. That single choice is what makes retries and backfills safe.
- `catchup` and the run date ({{ ds }}) are what let a task be rerun for a past day. A task that quietly uses today's date cannot be backfilled at all.
Practice challenge
A nightly job runs INSERT INTO daily_sales SELECT ... WHERE date = '{{ ds }}'. A retry after a partial failure double-counts the day. Rewrite it so a retry is safe, and say what this property is called.
DELETE FROM daily_sales WHERE date = '{{ ds }}';
INSERT INTO daily_sales SELECT ... WHERE date = '{{ ds }}';
Property: idempotencySQL: ______
Property: ______Show a hint
- Appending is what makes the retry unsafe — the fix replaces the partition instead
- The same property is what makes a backfill safe to run twice
Check yourself
1. What makes a pipeline task idempotent?
Show answer
B. Running it twice gives the same result as running it once
2. Why do appending tasks break backfills?
Show answer
B. A rerun duplicates rows rather than replacing them
3. What does a DAG give the orchestrator?
Show answer
B. The dependency graph, so one failed task can be retried alone
Design a pipeline end to end
Job-readyThe data engineering interview question is almost always some version of "we need yesterday's orders in the warehouse every morning — design it". What is being assessed is not whether you can name Airflow. It is whether you think about what happens when it fails, because a pipeline that only works on the happy path is a pipeline that will page you.
Answer in the order the data moves and say the failure mode at each step. Extract: what if the source is down, do you retry or skip? Land: raw copy first, untouched, so a transform bug does not cost you the data. Transform: idempotent, so a re-run replaces rather than appends. Verify: row counts and a freshness check, because the failure that costs most is the one where the job succeeds and the data is silently wrong.
Then say how you would know it broke. "The task fails and Airflow emails me" only catches crashes. The expensive failure is an empty upstream file producing a table with zero rows and a green tick. A check that yesterday's row count is within a sane band of the last fortnight catches that, and it is the difference between a junior and a senior answer.
Syntax
Daily orders -> warehouse, stated as failure modes
EXTRACT source API, page through yesterday's orders
fails? retry 3x with backoff, then FAIL LOUDLY (never skip)
LAND write raw JSON to s3://raw/orders/dt=2026-08-23/
immutable; a transform bug never costs us the source data
TRANSFORM DELETE FROM orders WHERE dt = '{{ ds }}';
INSERT INTO orders SELECT ... WHERE dt = '{{ ds }}';
-> idempotent: safe to re-run, safe to backfill
VERIFY row count within 40-250% of the trailing 14-day median
no NULLs in order_id, total; max(dt) = yesterday
fail the DAG on breach — a silent empty table is the
failure that costs real money
ALERT on failure AND on the freshness check, to a channel
a human actually readsKey points
- Land the raw copy before transforming. It is cheap storage and it means a bug in your SQL is an inconvenience rather than a data loss.
- Make the transform idempotent — delete the partition, then insert it. Retries and backfills then cost nothing to reason about.
- Alert on staleness, not only on errors. The job that succeeds while writing zero rows is the one nobody notices for a week.
Practice challenge
Your nightly load ran green for six days. On day seven the business says the numbers have been wrong all week - the source had been returning an empty file since Monday and the job appended nothing each night, successfully. Say which check would have caught it, why the task-failure alert did not, and how you would make the job safe to re-run for those six days.
Check: a freshness/volume assertion - row count for the day within a sane band of the trailing median, and max(date) equals yesterday. Fail the run on breach.
Why missed: nothing errored. An empty input processed correctly is a success by every technical measure; only a business expectation was violated.
Safe re-run: make the load idempotent - DELETE the partition for the date, then INSERT it - so you can simply backfill the six dates without double-counting.Check that catches it: ______
Why the failure alert missed it: ______
Safe re-run: ______Show a hint
- The alert you have answers 'did the code crash', not 'is the data right'
- Backfilling is only painless if re-running a date replaces rather than appends
Check yourself
1. Why land a raw, untransformed copy?
Show answer
B. A transform bug then costs you nothing permanent
2. What makes a daily load idempotent?
Show answer
B. Replacing the day's partition instead of appending
3. Which failure is most expensive in practice?
Show answer
B. The job succeeds and writes nothing
Where data comes from, and what dirty means
BasicsData reaches you from a handful of places and each has a characteristic failure. An application database is authoritative but changes shape when developers ship, and nobody tells you. A third-party API is someone else's uptime, someone else's rate limit and someone else's definition of a field. A file drop is a directory that is either reliable or a disaster depending on whether the sender uses a consistent schema, and event streams give you volume and order but no guarantee that a row appears only once.
'Dirty data' sounds vague until you have named the specific ways it arrives, and there are about six. Missing values, where blank, NULL and the string 'NULL' are three different things. Duplicates, from a retried delivery or a rerun. Type drift, where a numeric column arrives as text because one row contained 'N/A'. Encoding damage, the mojibake that comes of reading UTF-8 as Latin-1. Timezone confusion, where naive timestamps from three systems are compared as though they meant the same instant. And referential gaps, where an order references a customer that is not in the customer table.
The habit that separates working pipelines from fragile ones is profiling before building. Before writing a transformation, count the rows, count the distinct values in the key, count the nulls per column, and look at the minimum and maximum of every date. It takes ten minutes and it tells you what you are actually dealing with rather than what the schema claims. The alternative is discovering the duplicate key three weeks later, in a number a finance team has already presented.
Syntax
-- PROFILE FIRST. Ten minutes here saves a week later.
SELECT COUNT(*) AS rows,
COUNT(DISTINCT order_id) AS distinct_ids,
COUNT(*) - COUNT(customer_id) AS null_customers,
MIN(created_at), MAX(created_at)
FROM raw_orders;
-- rows 1,204,338 | distinct_ids 1,198,004 <- 6,334 duplicates
-- MAX(created_at) = 2031-04-01 <- a future date
-- the three faces of missing
SELECT status, COUNT(*) FROM raw_orders
GROUP BY status ORDER BY 2 DESC;
-- 'shipped' 900k | NULL 12k | '' 400 | 'NULL' 33 <- all different
-- referential gaps
SELECT COUNT(*) FROM raw_orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;
-- duplicates: which ones, and are they identical?
SELECT order_id, COUNT(*) FROM raw_orders
GROUP BY order_id HAVING COUNT(*) > 1
ORDER BY 2 DESC LIMIT 10;
-- Timezones: store UTC, convert at the edge, never compare naive stamps.Key points
- Profile before you transform: row count, distinct key count, nulls per column, min and max of every date. Ten minutes, and it replaces assumptions with facts.
- Blank, NULL and the literal string 'NULL' are three different values and will be counted differently by every query you write. Normalise them deliberately.
- Store timestamps in UTC and convert only for display. Comparing naive timestamps from systems in different zones produces answers that are wrong by hours and never look wrong.
Practice challenge
You are given raw_orders before writing any transformation. Write the single query that answers the four profiling questions, then say what each of these results would mean: 1,204,338 rows with 1,198,004 distinct order_ids, and MAX(created_at) of 2031-04-01.
Query:
SELECT COUNT(*), COUNT(DISTINCT order_id), COUNT(*) - COUNT(customer_id), MIN(created_at), MAX(created_at) FROM raw_orders;
The gap means: 6,334 duplicate order_ids - joining on order_id will multiply rows and inflate every downstream sum
The future date means: bad or default data in the timestamp column; any date filter or partition on it is already wrongQuery: ______
1,204,338 vs 1,198,004 means: ______
MAX date 2031-04-01 means: ______Show a hint
- Four questions, one pass over the table
- A key called id is not automatically unique
Check yourself
1. You have 1,204,338 rows and 1,198,004 distinct order_ids. What does that mean?
Show answer
B. There are 6,334 duplicate rows to handle before joining
2. Why do blank, NULL and 'NULL' matter?
Show answer
B. They are three distinct values that every filter and count treats differently
3. What should you do with timestamps from three different systems?
Show answer
B. Store as UTC and convert only for display
Modelling for analytics: grain, facts and dimensions
Working levelAnalytics modelling has one question at its centre and everything else follows from it: what does one row of this table represent? That is the grain, and it must be stated in a sentence before any column is chosen — one row per order line, per day per store, per session. Tables where nobody wrote the grain down end up mixing levels, and then every sum is silently wrong because joining a per-order table to a per-line table duplicates the order value once per line.
The shape that has survived forty years is the star schema: a fact table in the middle holding the measurements at a stated grain, surrounded by dimension tables holding the descriptive attributes you filter and group by. Facts are numbers you add up — quantity, amount, duration — plus the keys pointing at dimensions. Dimensions are wide, textual and comparatively small: customer, product, store, date. Analysts find this shape obvious to query, and query engines optimise for it, which is why it beats a cleverer design in practice.
The complication worth knowing by name is the slowly changing dimension, because attributes change and history matters. A customer moves from Pune to Berlin: if you overwrite the row, every historical sale moves to Germany and last year's regional report changes retrospectively, which is Type 1. If instead you close the old row with an end date and insert a new one, each sale keeps the attributes that were true when it happened, which is Type 2. Neither is wrong — overwriting is right for a corrected typo — but choosing accidentally is how a report that reconciled last month stops reconciling.
Syntax
-- STATE THE GRAIN FIRST, in a comment, in a sentence.
-- fct_order_line: one row per line item on an order.
CREATE TABLE fct_order_line (
order_line_id BIGINT PRIMARY KEY,
order_id BIGINT, -- degenerate key
date_key INT, -> dim_date
customer_key BIGINT, -> dim_customer
product_key BIGINT, -> dim_product
quantity INT, -- additive
line_amount NUMERIC(12,2) -- additive
);
-- Type 2 dimension: history is preserved
CREATE TABLE dim_customer (
customer_key BIGINT PRIMARY KEY, -- surrogate, changes per version
customer_id BIGINT, -- natural, stable
city TEXT,
valid_from DATE,
valid_to DATE, -- 9999-12-31 while current
is_current BOOLEAN
);
-- 5001 | 88 | Pune | 2023-01-01 | 2026-03-14 | false
-- 5002 | 88 | Berlin | 2026-03-15 | 9999-12-31 | true
-- THE FAN TRAP: joining across grains double-counts
-- orders(1 row, total 500) JOIN lines(3 rows) -> SUM(total) = 1500
SELECT SUM(line_amount) FROM fct_order_line; -- correct: sum at the grain
-- SUM(order_total) after that join is wrong, and looks plausible.Key points
- Write the grain as a sentence before creating the table. 'One row per order line' is the fact that makes every later join checkable.
- Facts hold additive numbers and keys; dimensions hold the descriptive attributes you filter and group by. Analysts and query planners both expect this shape.
- Type 1 overwrites and rewrites history; Type 2 closes the old row and inserts a new one, so a past sale keeps the attributes it had at the time. Choose deliberately.
Practice challenge
An analyst joins orders (one row per order, with order_total) to order_lines (one row per line) and reports SUM(order_total) as revenue. Orders average 3 lines. Say what the reported number is, write the correct query, and state the grain of the result table in one sentence.
Reported number is roughly 3x actual revenue - the order header repeats once per line, so its total is counted once per line
Correct query: SELECT SUM(line_amount) FROM fct_order_line; (sum at the grain of the table you are querying)
Grain: one row per line item on an orderReported number is: ______
Correct query: ______
Grain of fct_order_line: ______Show a hint
- Ask what one row of the joined result represents
- The number will look entirely plausible, which is why it reaches a deck
Check yourself
1. What is the grain of a table?
Show answer
B. What a single row represents
2. A customer moves city. You want last year's report to stay unchanged. Which approach?
Show answer
B. Type 2: close the old row, insert a new one
3. You join orders to order_lines and sum order_total. What happens?
Show answer
B. The header repeats per line, so the total is multiplied
Testing data, and contracts with the people upstream
AdvancedCode tests check that your logic is right. Data tests check that reality is what you assumed, and you need both, because a pipeline whose code is flawless still produces nonsense when the source silently changes. The four tests that catch most incidents are cheap: freshness, that the newest row is recent enough to be useful; volume, that today's row count is within a sane range of the recent norm; uniqueness, that the key you join on really is unique; and not-null on the columns downstream logic depends on.
Volume deserves its own note because it catches the failure mode nothing else does. A pipeline that loads zero rows usually succeeds — there was no error, there was simply nothing — and every dashboard downstream renders happily with yesterday's totals or an empty chart. A test asserting that today's count is within, say, 50% to 150% of the trailing average turns a silent nothing into a loud failure, and it is the single highest-value data test most teams are missing.
Above the tests sits the contract, which is the social half of the problem. Most breakages come from upstream shipping a change without knowing you existed: a column renamed, a type widened, an enum gaining a value. A data contract makes that explicit — these fields, these types, these guarantees, and notice before they change — and it works only when the owner has agreed to it. Where you cannot get agreement, the defensive equivalent is a schema check at ingest that fails loudly on drift, so you learn from your own alert rather than from a business user asking why a number looks odd.
Syntax
-- 1. FRESHNESS -- is the newest row recent enough?
SELECT MAX(created_at) FROM fct_orders;
-- fail if older than 6 hours
-- 2. VOLUME -- the test that catches the silent zero
WITH d AS (
SELECT COUNT(*) AS today FROM fct_orders
WHERE created_at::date = CURRENT_DATE
), b AS (
SELECT AVG(c) AS baseline FROM (
SELECT COUNT(*) c FROM fct_orders
WHERE created_at::date BETWEEN CURRENT_DATE - 14 AND CURRENT_DATE - 1
GROUP BY created_at::date) x
)
SELECT today, baseline FROM d, b
WHERE today < baseline * 0.5 OR today > baseline * 1.5;
-- any row returned = alert
-- 3. UNIQUENESS -- the key you join on
SELECT order_id FROM fct_orders GROUP BY 1 HAVING COUNT(*) > 1;
-- 4. NOT NULL -- columns downstream logic depends on
SELECT COUNT(*) FROM fct_orders WHERE customer_id IS NULL;
-- dbt expresses exactly these declaratively:
-- models:
-- - name: fct_orders
-- columns:
-- - name: order_id
-- tests: [unique, not_null]
-- Fail the RUN on a failed test. A test that only warns
-- is a test everybody stops reading within a month.Key points
- Volume tests catch the silent zero-row load, which succeeds without error and leaves every dashboard downstream looking plausible and stale.
- Test uniqueness on any key you join on. Source systems retry and backfill, so a key that was unique last quarter may not be today.
- A failing data test must fail the run, not warn. A warning that appears daily becomes wallpaper, and then it is not a test at all.
Practice challenge
A pipeline loaded zero rows this morning. It exited successfully, no alert fired, and the dashboard still renders. Name the four data tests every table should carry, write the SQL for the one that would have caught this, and say whether a failing test should warn or fail the run.
Four tests: freshness, volume, uniqueness on the join key, not-null on columns downstream logic depends on
SQL: compare today's row count to the trailing 14-day average and return a row (i.e. alert) when it is below 50% or above 150% of it
Warn or fail: fail the run. A warning that appears regularly becomes wallpaper and stops being read within weeks.Four tests: ______
SQL for the one that catches this: ______
Warn or fail: ______Show a hint
- Zero rows is not an error - there was simply nothing, so nothing failed
- Compare against a recent baseline rather than a fixed threshold
Check yourself
1. A load inserts zero rows. Why is that dangerous without a volume test?
Show answer
B. It succeeds silently and dashboards render stale or empty numbers
2. What is a data contract?
Show answer
B. An agreement with the upstream owner on fields, types and notice before change
3. Should a failed data test warn or fail the run?
Show answer
B. Fail — a daily warning is ignored within weeks
The query that scanned four terabytes
Job-readyOn a warehouse billed by data scanned, the cost of a query is decided by how much it must read, and that is set by physical layout far more than by clever SQL. Partitioning splits a table into chunks by a column — nearly always a date — so a query filtered to one day reads one partition instead of the whole history. It only works if the filter is on the partition column and is written so the engine can use it: WHERE event_date = '2026-08-01' prunes, while WHERE CAST(event_ts AS DATE) = '2026-08-01' wraps the column in a function and reads everything.
Clustering, or sort order, is the second lever, ordering rows within each partition by a column you filter on often, so the engine can skip blocks that cannot contain a match. And the habit that costs the most for the least benefit is SELECT *. Columnar storage reads only the columns you name, so selecting ten columns from a table of two hundred reads roughly five per cent of the bytes. SELECT * on a wide table in a notebook, rerun all afternoon, is one of the most common large line items on a warehouse bill.
The discipline is to read the plan before you run the thing. Every warehouse offers a dry run or explain that reports bytes scanned without executing, and checking it takes seconds. Beyond that, materialise what is recomputed: a dashboard where forty users each trigger the same expensive aggregation should read a table refreshed once an hour. The general rule is to do expensive work once on a schedule rather than many times on demand, and it is the difference between a warehouse that costs hundreds a month and one that costs tens of thousands.
Syntax
-- PRUNES: filter directly on the partition column
SELECT customer_id, SUM(amount)
FROM events
WHERE event_date BETWEEN '2026-08-01' AND '2026-08-07'
GROUP BY 1;
-- scanned: 12 GB
-- DOES NOT PRUNE: the column is wrapped in a function
WHERE CAST(event_ts AS DATE) = '2026-08-01'
-- scanned: 4.1 TB (the entire table)
-- DOES NOT PRUNE: filtering a different column
WHERE event_ts > '2026-08-01' -- not the partition key
-- SELECT * on a columnar table reads every column
SELECT * FROM events WHERE event_date = '2026-08-01'; -- 210 cols
SELECT event_id, customer_id, amount -- 3 cols
FROM events WHERE event_date = '2026-08-01'; -- ~1.5% of bytes
-- CHECK BEFORE YOU RUN -- costs nothing
-- BigQuery: bq query --dry_run
-- Snowflake: EXPLAIN
-- Spark: df.explain()
-- Compute once, read many
CREATE TABLE daily_revenue AS -- refreshed hourly
SELECT event_date, SUM(amount) revenue
FROM events GROUP BY 1;
-- 40 dashboard users now read a small table
-- instead of each re-aggregating the sourceKey points
- Partition pruning only happens when the filter is on the partition column and unwrapped. CAST or any function around it forces a full scan.
- Columnar storage reads only the columns you name, so SELECT * on a wide table can cost twenty times a narrow select for identical results.
- Dry-run or explain before executing an unfamiliar query. It reports bytes scanned without running, which turns a four-figure mistake into a five-second check.
Practice challenge
This query scans 4.1 TB every run: SELECT * FROM events WHERE CAST(event_ts AS DATE) = '2026-08-01'. The table is partitioned on event_date and has 210 columns. Rewrite it to read three columns for that one day, explain both reasons it was expensive, and name the command that checks cost without running it.
Rewritten:
SELECT event_id, customer_id, amount FROM events WHERE event_date = '2026-08-01';
Reason 1: CAST wrapped the partition column, so the engine could not prune and read every partition
Reason 2: SELECT * on a columnar table reads all 210 columns; naming 3 reads about 1.5% of the bytes
Check first: a dry run / EXPLAIN, which reports bytes scanned without executingRewritten: ______
Reason 1: ______
Reason 2: ______
Check cost first with: ______Show a hint
- A function around the partition column defeats pruning
- Columnar storage only reads the columns you name
Check yourself
1. Why does WHERE CAST(event_ts AS DATE) = '2026-08-01' scan the whole table?
Show answer
B. Wrapping the partition column in a function prevents pruning
2. Why avoid SELECT * on a wide columnar table?
Show answer
B. Columnar storage reads only named columns, so * reads far more bytes
3. Forty users trigger the same expensive aggregation hourly. Best fix?
Show answer
B. Materialise it once on a schedule and have them read the result
Common questions
Do I need any background to start Data Engineering?
No. This track begins at its own beginning and assumes nothing, and the first lesson explains what the thing is before showing you any syntax.
How long does the Data Engineering track take?
About 135 minutes of reading across 8 lessons, plus the practice challenges and 24 multiple-choice questions, which is where the time actually goes.
Is it free?
Yes, and there is no account. Everything runs in your browser.
More: all 15 tracks · what employers actually ask for · the full syllabus