Data engineering flashcards: ingestion to serving
Recall cards for the data engineering track: formats, ingestion, storage, modelling, SQL and dbt, orchestration, Spark, Kafka, streaming, quality and cost.
483 cards. Read the question, answer it in your head, then open the card to check.
-
What does a data engineer build and run, and what is left to the neighbouring roles?
Pipelines that carry data from the systems that create it to the people and programs that read it, so it arrives correct and on time. The dashboard itself is an analyst's work and the model an ML engineer's.
From the lesson What data engineering is, and what it is not
-
Into which seven areas does this track divide the work of data engineering?
Ingestion, storage, transformation, orchestration, quality, governance and serving.
From the lesson What data engineering is, and what it is not
-
Apart from tables, what can a pipeline hand over to its readers?
Files, streams, data contracts and documentation. Each of them has readers who notice when it arrives wrong or late.
From the lesson What data engineering is, and what it is not
-
Which roles work next to a data engineer?
Analysts, analytics engineers, ML engineers and platform teams. Job titles vary between companies, yet the work splits along the same lines.
From the lesson What data engineering is, and what it is not
-
When is a pipeline safe to rerun and replay?
When a second run of a day leaves exactly what the first run left, and any past day can be rebuilt from source rows the pipeline kept.
From the lesson What data engineering is, and what it is not
-
Which three habits separate a rerunnable pipeline from a one-off script?
It checks the data before writing anything, it replaces a whole day rather than appending to it, and it keeps the source rows so that any day can be rebuilt.
From the lesson What data engineering is, and what it is not
-
Which five stages does every record pass on its way from source to reader?
Generation, ingestion, storage, transformation and serving. Any of them can delay, lose or change a record, so find the stage of a problem before you look at a tool.
From the lesson The data lifecycle from source to serving
-
Should a scheduler be drawn as a stage between transformation and serving?
No. A scheduler moves no data: it decides when ingestion, transformation and serving run and retries them after a failure, so it sits beside the stages with a line to every job it starts.
From the lesson The data lifecycle from source to serving
-
Which concerns run through every stage of the lifecycle instead of belonging to one?
Security and privacy, data management, DataOps, orchestration and software engineering. A pipeline is not finished until each of its stages meets all five.
From the lesson The data lifecycle from source to serving
-
Where does the transform run in ETL, and where in ELT?
ETL transforms on the way in and stores only the result; ELT lands a raw copy first and transforms it inside the warehouse. When a rule forbids keeping a raw copy of a source, that source has to be ETL.
From the lesson The data lifecycle from source to serving
-
How do you pick batch, micro-batch or streaming for a dataset?
Start from the freshness its reader needs. Of the modes whose worst case still meets that target, take the slowest one, because it has the fewest moving parts to run.
From the lesson The data lifecycle from source to serving
-
For one hour of orders, how old was the median order with 1, 12 and 200 deliveries?
About 5.5 hours with a single delivery, 2.6 minutes with 12, and 2 seconds with 200. Each step up in freshness costs more deliveries to run and watch.
From the lesson The data lifecycle from source to serving
-
What do you ask of each box when you read a data architecture diagram?
Which lifecycle stage it is, what it receives, and what happens to the data when that box fails.
From the lesson The data lifecycle from source to serving
-
A pipeline prints a different number today although its code is unchanged. Where is the cause usually?
In the lab around the code: a library that upgraded itself, an engine now running on another Java, or a tool that was never pinned. Fixing one version of every tool removes that drift.
From the lesson A local data lab you can trust
-
What must be fixed for a result to be reproducible months later?
Both the code and the toolchain. Pinned versions fix the toolchain, so the same run gives the same output later.
From the lesson A local data lab you can trust
-
Where does the track's lab record the versions it pins?
The base runtimes in a mise.toml file, and each engine's exact version where that engine is installed. A report of what is really installed lets a check compare it with the pins.
From the lesson A local data lab you can trust
-
Why does every engine in the lab get a virtual environment of its own?
Two engines can need different versions of the same library, as PySpark 4.2 and the warehouse work do with pandas. Separate environments keep one from breaking the other.
From the lesson A local data lab you can trust
-
How do you keep a local data engine unreachable from the network?
Bind it to the loopback address 127.0.0.1, then read the list of listening sockets to prove it. These engines are not secure by default, and several listen on every interface out of the box.
From the lesson A local data lab you can trust
-
What should a printed result carry so that someone else can reproduce it?
The exact command and the tool versions that produced it. Anyone with the same pinned lab can then run it again rather than take it on trust.
From the lesson A local data lab you can trust
-
Over 30 nights the payout job never failed. On how many was its file both on time and complete?
On 24. Nobody had written down what early and complete meant, so the 6 bad nights went unnoticed.
From the lesson Pipeline requirements: SLAs, freshness, sizing
-
What does a requirement need before it becomes an objective you can check?
It starts from the people who read the data and names a measurement, a target, how often the target must hold, and an owner who is told when it is missed.
From the lesson Pipeline requirements: SLAs, freshness, sizing
-
Where and how should freshness and completeness be measured?
Where readers meet the data, against a count the pipeline did not produce, such as a provider's own report, and day by day. A successful job and a good monthly average can both hide a bad day.
From the lesson Pipeline requirements: SLAs, freshness, sizing
-
What do the RPO and the RTO of a pipeline limit?
The RPO caps the data a failure is allowed to cost; the RTO caps how long readers go without it. Kept input plus an idempotent rerun meets the RPO, while noticing, restoring and rerunning all count toward the RTO.
From the lesson Pipeline requirements: SLAs, freshness, sizing
-
How do you estimate the storage a dataset needs?
Multiply events per day by bytes per event, divide by a compression ratio measured on one real day of your own rows, and multiply by the days you keep it.
From the lesson Pipeline requirements: SLAs, freshness, sizing
-
Why is a tenfold margin over the average a poor throughput target?
The busiest minute can still exceed it: ten times this year's 2.8 events a second is 28, below the 31.1 measured in the busiest minute. Size for a measured peak and write down the peak factor and its source.
From the lesson Pipeline requirements: SLAs, freshness, sizing
-
Which other duties tend to hide inside a request for data?
Quality, security and legal duties. Each one gets an owner and a check, on the same one-page document as the targets and the escalation path.
From the lesson Pipeline requirements: SLAs, freshness, sizing
-
Why should a pipeline never store money as DOUBLE or FLOAT?
Binary floating point rounds most decimal fractions, and the errors add up in a sum: five amounts that should total 59.99 came back as 59.989999999999995. Use DECIMAL or whole minor units such as paise.
-
Why does int(19.99 * 100) give 1998 instead of 1999 paise?
As a float, 19.99 * 100 is 1998.9999999999998, and int() cuts off the fraction, leaving the amount one paisa short.
-
A 5% fee comes to 0.125 rupee. What do half-to-even and half-up rounding give?
Half to even, Python's default, gives 0.12, and half up gives 0.13. Neither is wrong, but every division, fee and split in a pipeline needs one written rounding rule.
-
Where should a pipeline convert between UTC and local clock time?
At its edges: every timestamp becomes a UTC instant when it enters, for example in a TIMESTAMPTZ column, and a local clock appears only when a person reads it.
-
What happens when DuckDB casts a timestamp written without a zone to TIMESTAMPTZ?
It borrows the zone of the session doing the cast, so the instant it stores depends on the machine and session that ran it. Attach the source's zone before the cast.
-
Why store the source's zone, for example Asia/Kolkata, beside each UTC instant?
The instant records when something happened and the zone records what the wall clock said, which a report needs to count the shop's own business day.
-
Why can a local time with no offset be ambiguous for New York but not for India?
IST keeps one offset all year. New York's clocks repeat an hour in autumn and skip one in spring, so such rows need an offset or a UTC time from the source, or a place in the rejects table.
-
What does a byte-order mark do to a CSV file read without stripping it?
Its three invisible bytes join the first column's name, so a column called name no longer matches. Strip the mark, and read a file in another encoding with that encoding declared.
-
Why does schema inference fail in a production pipeline?
It guesses column types from a sample of rows. When later rows do not fit the guess, the load fails late or coerces values into something wrong, so a production loader declares its schema.
-
What does the quarantine load pattern do with good and bad rows?
Good rows go on to the landing zone and bad rows to a rejects table, so the pipeline keeps running while a separate process checks the rejects.
-
Which fields make a rejects table useful to the source team?
The row's line number in the source file, the column that failed and the error message, kept with the raw row, so the data can be fixed and sent again or a correction rule written.
-
How does RFC 4180 let a CSV field contain a comma, a quote or a line break?
The field is wrapped in double quotes, a quote inside it is written twice, and a quoted field may run over a line break. A reader that cuts each line at every comma gets the fields wrong.
-
Why do pipelines prefer newline-delimited JSON to one JSON array?
Each line holds one whole record, so the file can be appended to and split between workers; a single JSON array allows neither.
-
Why keep the raw record beside the rows flattened out of nested JSON?
When the flatten logic changes, the rows can be rebuilt from the raw column without loading the file again.
-
When should a batch fail instead of loading with a rejects table?
When rejects pass a threshold agreed in advance, such as 1% of rows, when a column the downstream model needs is missing, or when the file's set of columns changes. The threshold is a business decision made with the consumers.
-
Why should raw input files never be cleaned in place?
Kept unchanged in the landing zone, they let you fix a parser or a schema and replay them. Editing them in place destroys the audit trail.
-
What is inside a Parquet file, and where does a reader start?
Row groups of column chunks made of pages, then a footer with the schema, the position of every chunk and each chunk's minimum and maximum. A reader starts at the end, with the footer.
-
How does a Parquet row group differ from a partition folder?
Day folders split a dataset into files, while row groups split one file. A reader skips a folder by its name, but it skips a row group only by that group's statistics.
-
Why sort rows by the filtered column before writing a Parquet file?
Statistics skip a row group only when its range of that column rules the filter out. One day of orders sat in 1 of 13 row groups of the time-sorted file and in all 13 of the shuffled one.
-
What shrinks a Parquet column before the compression codec runs?
Encodings such as dictionary, run-length and delta. Sorted data gives longer runs and smaller deltas, and a newer encoding helps only when every reader implements it.
-
How do you choose a Parquet codec and row-group size?
The codec from sizes and times measured on your own data. The group size from the queries and the threads: small groups for narrow slices, large ones for whole scans, never fewer groups than threads.
-
What does Apache Arrow do that a Parquet file does not?
It holds columns in memory as contiguous buffers that another engine can read in place, so engines share data without a CSV round trip and types such as DECIMAL stay intact.
-
Why is a Parquet file not a table?
A file cannot delete rows or commit a change, so removing rows means writing a new file. A table format adds the commit on top of such files.
-
Why did one order take 9 bytes as Avro but 49 bytes as JSON?
Avro keeps the schema beside the data, so field names are stored once instead of travelling inside every record as they do in JSON.
-
When can Avro read old data with a new reader schema?
When every reader field has a match in the writer schema or a default of its own, and every type change is a promotion the specification allows. Anything else fails with a resolution error rather than wrong data.
-
What does a backward-compatible schema change promise, and which side upgrades first?
The new reader schema can read data written with the old one, as when long widens to double. Consumers upgrade first, then producers.
-
What does a forward-compatible schema change promise, and which side upgrades first?
The old reader schema can read data written with the new one, as when a removed field has a default in the old reader. Producers upgrade first.
-
How do a fully compatible schema change and a breaking one differ in rollout?
A full change, such as adding a field with a default, works in either upgrade order. A breaking change, such as string to long, needs a new topic or a dual-write period, and a schema registry refuses it.
-
Why is renaming a Protobuf field safe but reusing a removed field number dangerous?
Fields travel by number, never by name. An old consumer still decodes a reused number with the old type and records wrong values without any error, so list retired numbers and names in a reserved block.
-
Why does a topic with many consumers need a transitive compatibility rule?
A non-transitive rule compares a new version only with the latest one, so a chain of small changes can strand a consumer that has not upgraded for weeks. A transitive rule compares it with every earlier version.
-
What does a schema registry add to schema changes?
It stores every version of a subject and runs the compatibility check when a producer registers a new one, so the rollout order is enforced instead of hoped for.
-
Which four ingestion patterns cover almost every source, and when does each fit?
Full refresh for small tables, incremental extraction when reads must stay cheap, change data capture when hard deletes and freshness both matter, and event streams when the application already publishes what you need.
-
Which source change does an incremental read never see?
A hard delete. The row is gone, so no changed row reports it; a full refresh drops it on the next reread, and change data capture sees the delete itself.
-
What does a pull source need, and what does a push source need?
A pull source needs a stored cursor and a retry that is safe to repeat. A push source, such as a webhook, needs an inbox table whose key is the event id the sender assigns, plus a merge that is idempotent.
-
In what order should a webhook receiver store, answer and merge?
Store the raw body in the inbox table, answer with a 2xx straight away, then merge. Slow or failed answers are retried by the sender, and the burst after a restart can be many times the normal rate.
-
For an idempotent load, which matters more: the key or the SQL?
The key. With the right key a loader deduplicates on any database, and with the wrong one it duplicates on the best database. Use a stable key the record itself carries.
-
What lets a team rebuild a curated table after fixing a bug a month later?
A raw landing zone that every load writes once and never edits. A fixed merge rebuilds the curated table from those files without going back to the source.
-
CDC and application events are both fresh and light on the source. What separates them?
CDC catches whatever the database does, while events carry only what the application chose to publish.
-
How can an API copy be wrong when every request succeeded?
Offset and page-number pagination count positions, so rows that change during the run shift the pages. In the lesson's run, 11 clean requests read 17 settlements twice and never read 17 others.
-
Why page on the pair (updated_at, id) rather than on updated_at alone?
A page can end part-way through several rows that share one updated_at, and a cursor on the time alone then skips the rest of them. Comparing the pair, sorted by both, reads every row.
-
With a cursor on a unique, growing key, what happens to a row that changes mid-run?
It is read twice instead of never, and a load that merges on id turns the two copies into one row.
-
Which failures should an API loader retry, and which should stop it?
Retry 408, 429 and 5xx answers and dropped connections. Stop on any other 4xx, such as 401, and alert someone who can fix the request, because waiting will not.
-
How long should a loader wait before retrying a refused request?
As long as Retry-After says when the provider sends it; otherwise a random time below a ceiling that doubles up to a cap. Give up after enough attempts for the waits to outlast the outage you expect.
-
Should a loader write a landed page or its manifest entry first?
The page. Write it under a temporary name, rename it into place, then record its rows, checksum and next cursor. An entry written first can point at a missing or half-written file after a crash.
-
How should an ingestion job carry its API key?
In the Authorization header, never in a URL, masked in everything the job writes and fetched when the run starts, with a test that fails if a key turns up in a log or a landed file.
-
Why should a long extract from an OLTP database read under one snapshot?
Under the default isolation a long extract sees several snapshots of the source. REPEATABLE READ, or a replica at a known log position, freezes one, so every row of the run comes from the same moment.
-
Which two kinds of change does a plain high-watermark load miss?
Hard deletes, and late-committed rows whose stamps are older than the stored watermark. A safety window on the next read, with an idempotency key at the destination, closes the second gap.
-
Does stamping updated_at in the database close the late-commit gap?
No. It removes clock drift between application servers, but the stamp is taken before the transaction commits, so a slow transaction can still commit a value below rows already read. Only a safety window, or reading changes in commit order, catches that row.
-
Which three steps give a watermark load one row per key at the destination?
Store the watermark, subtract a safety window on the next read, and deduplicate on the record's own key. The reads are at least once, and no late row is lost while the window covers the source's commit lag.
-
Why does keyset pagination beat OFFSET when extracting a big table?
With OFFSET the server computes every row it skips, and changes in front of the position repeat or skip rows. Keyset pagination resumes from the last sort key, so each page costs the same and a changed row moves no later page.
-
How should an incremental query receive its cursor, watermark and range?
As bound parameters, with an allowlist for any identifier that has to vary, so no input can ever change the SQL that runs.
-
Which daily signals catch silent drift between a source table and its copy?
Per day, a row count, a sum and a hash of the keys. When one of them drifts, replay that day and let the key absorb the duplicates.
-
An order is placed, paid and deleted between two 15-minute updated_at queries. What does the warehouse learn?
Nothing: the next query finds no row, so the order never existed for the warehouse. Change data capture reads the log where PostgreSQL records every committed change, deletes included.
-
What does a logical replication slot hand to its consumer?
Every committed transaction, whole and in commit order, from the moment the slot was created. Work that rolled back never appears in it.
-
In which order should a consumer apply the changes from a slot?
Whole transactions, in the order the slot returns them, which is commit order. Sorting by each row's updated_at or by statement time rebuilds a state no reader of the database ever saw.
-
Which old values does an update or delete carry in PostgreSQL logical decoding?
What the table's replica identity sets: the primary key by default, every column with FULL, and an unchanged out-of-line value not at all. A consumer holding a copy of every row it has seen gets by with the key.
-
How does a CDC consumer make sure a crash never loses a batch?
It peeks at the changes, stores the rows and the last position in one transaction, and only then advances the slot. A crash then costs a repeat instead of a lost batch.
-
What does a replication slot that nobody reads do to the server?
It keeps WAL for a consumer that may never return, until the disk is full. Alert on the WAL it holds and how long it has been idle, and set max_slot_wal_keep_size and idle_replication_slot_timeout as limits.
-
Why can an upsert not make a repeated CDC batch harmless on its own?
The current-state table survives the repeat, but the history table gains a version for each repeated change: 9 rows instead of 7 in the run. Keep the last applied position with the rows and skip anything at or below it.
-
What does a declarative ingestion library such as dlt write on your behalf?
Given the records, the destination, the primary key and the cursor column, it writes the schema, the upsert SQL and the state kept between runs.
-
Which three objects make up a dlt pipeline?
A resource that yields the records and names their key, a destination for the database, and a pipeline whose run call loads one into the other.
-
Which tables does dlt create next to the table named after the resource?
Three for its own bookkeeping: _dlt_loads, _dlt_pipeline_state and _dlt_version.
-
How do an incremental cursor and merge stop a dlt rerun from loading twice?
The cursor on updated_at keeps its last value in pipeline state, so a rerun reads only newer rows, and merge on a primary key replaces rows by key, so a replay leaves one row per order.
-
What does dlt do with a merge write disposition that names no key?
It appends and only warns. In the run, two loads grew the table to 4 rows for 2 order ids; with order_id as the primary key the same two loads left 2 rows.
-
What does dlt do with a nested list, and with a field it has not seen before?
A nested list becomes a child table linked to its parent by _dlt_parent_id and _dlt_list_idx. A new field becomes a new column unless a schema contract freezes or discards it.
-
What decides between a hand-written loader, dlt and a managed connector service?
How many sources the team needs, who owns ingestion, and how much schema and backfill control must stay in the team's own repository. dlt is the default for small-to-mid teams with their own engineers.
-
Why does a three-column report read far less from Parquet than from CSV?
A query reads the columns it names, and a columnar file stores each column on its own: 302,195 bytes for the report, against all 8,353,780 bytes of the same 100,000 orders as CSV.
-
How do OLTP and OLAP workloads differ in what they read and the layout they want?
OLTP fetches and writes single records by key and wants rows with an index. OLAP scans many rows for a few columns and wants compressed columns, so the two run on different systems.
-
How do a warehouse, a data lake and a lakehouse differ?
A warehouse keeps engine, catalogue, rules and storage in one system. A lake keeps open files that any engine reads, with no rules on top. A lakehouse adds an open table format's commits and snapshots to those files.
-
Which facts about a team decide between a warehouse, a lake and a lakehouse?
The number of engines that must read the data, the people available to run its upkeep, and whether the data changes or must be read as it was at an earlier point.
-
What turns raw, cleaned and business-ready layers into more than folder names?
Each is a contract the team enforces: raw stays exactly as it arrived, and every later layer is rebuilt from it by code.
-
A staging job read 3 part files while a 4th was still on its way. Why did no check fail?
The job took a listing of the bucket as a finished batch, as if the object store were a shared file system. The late file arrived after the load, and only a reconciliation found the 523 missing orders.
-
What does a raw path with source=, ingest_date= and run=<id> make possible?
Telling the source, the day and the retry of every batch apart without opening any file.
-
What does S3 guarantee that a pipeline can rely on, and what does it not offer?
Strong read-after-write consistency for a PUT or DELETE of a single key, in every Region. It has no atomic rename of many objects, which makes a commit by rename slow and unreliable there.
-
Why does renaming an _in_progress/ prefix not work as a commit on S3?
S3 cannot rename a prefix: every key is copied to its new name and then deleted. A 10,000-file batch takes at least 10,000 copy requests, and a crash halfway leaves it split across both prefixes.
-
When is a batch in an object store complete, and in what order is it written?
When its manifest is present and every part verifies against it. Writers put the parts first and the manifest last; readers go the other way round and start from the manifest.
-
How do two writers racing for the same manifest key avoid overwriting each other?
Each PUT carries If-None-Match set to *, so the store keeps the first manifest and answers the second writer with PreconditionFailed.
-
How can landing-zone code be tested fast and offline?
With moto, which answers every AWS call made inside mock_aws() from an in-process S3, plus a thin contract test against real S3 for the few behaviours production depends on.
-
Which six settings should a landing bucket have before the first write?
Block Public Access, encryption at rest, versioning, a bucket policy that refuses requests without TLS, access logging and lifecycle rules.
-
Which column should a dataset be partitioned by?
The one every query filters on, with a cardinality in the hundreds rather than the hundreds of thousands. The folder tree prunes only on the column it is named by, so a key nobody filters on makes every query open every folder.
-
How many files did a one-day query open over 500,000 orders in daily folders?
1 of the 90 files, while over the same orders kept in one file it read every row.
-
How can a finer partition key make every scan slower?
Each column added to the key multiplies the files. With a few hundred rows per cell, as in the 3,600-cell layout, a scan pays for opening files and reading footers instead of reading rows.
-
What does sorting rows inside a partition do for a filter on another column?
It narrows each row group's statistics: one month sorted by outlet let an outlet filter read 2 of 7 row groups instead of all 7. The count comes from parquet_metadata, not from a timer.
-
What do BigQuery, Snowflake, Iceberg and Delta Lake call the layout that sorts rows so filters prune?
Clustered tables in BigQuery, clustering keys in Snowflake, sort orders in Iceberg and z-ordering in Delta Lake: one mechanism, four names.
-
Why can a query engine miss a partition folder that was just written?
An engine that reads a catalogue sees only the partitions listed there until the catalogue is refreshed. A table format keeps the file list in its own metadata and commits it atomically.
-
Why is there no PARTITION BY clause in Snowflake's CREATE TABLE?
Snowflake divides every table into micro-partitions of 50 to 500 MB of uncompressed data by itself, so pruning works much as it does locally under another name and another clause.
-
How should you approach a cloud data platform you have not used before?
As a translation task: find the name it gives each capability you already understand, such as pruning, a catalogue or rules on rows, instead of learning it from scratch.
-
Which four layers does each of the five cloud data platforms sit on?
Storage, compute, catalogue and access, with storage kept apart from compute. Most differences between BigQuery, Snowflake, Redshift, Databricks and Fabric are different products in those four boxes.
-
A BigQuery table is partitioned by order_date. What serves the same purpose on Redshift?
Making order_date the first SORTKEY column. The intent is to skip dates outside a range, and Redshift has no partition clause to paste.
-
What does a SQL translator such as sqlglot handle, and what does it leave to you?
It maps data types mechanically, for example DECIMAL(10, 2) to NUMERIC on BigQuery, but it copies, refuses or drops layout clauses, so the layout is yours to translate.
-
How do you prepare for a cloud data platform without spending or exposing anything?
Rehearse on DuckDB or local Spark, use the public certification guides for breadth, and if you do open an account, set a budget alert, stop what you start and keep every endpoint off the public internet.
-
Which four modelling shapes keep coming up, and whom does each serve?
3NF serves writers in operational systems, a star serves readers in a warehouse, a Data Vault serves auditable integration from many sources, and one-big-table serves a single dashboard or one model's features.
-
How many joins does a per-hostel lunch spend question need in each modelling shape?
Four in the operations database, two small ones in a star, eight or more in a Data Vault, and none in a flat table built for the dashboard.
-
What is cheap and what is costly in 3NF, and in one-big-table?
3NF makes a single-row update cheap and a grouped sum costly. One-big-table makes the sum cheap and renaming an attribute copied into every row costly.
-
What trade does a star schema make?
It places the dimensions beside the facts so a reader joins once: a longer load, paid once, buys a shorter query every time it runs.
-
How can one dataset be stored in more than one shape within a pipeline?
It passes through raw, staging, intermediate and marts layers, and each stop uses the shape that its own reader needs.
-
What should be settled before you draw a table or choose an engine?
The business process and the grain: what one row of the fact table means and which question it is for. A table with a stated grain and a clear reader picks its own shape.
-
What is the grain of a fact table?
The meaning of one row, stated in a single sentence and declared before any key, measure or dimension is chosen.
-
Six invoice lines totalled 1,123.50, but a dashboard reported 1,543.50 for them. What happened?
A join to payments repeated every line of one invoice settled in two payments: the two tables had different grains. A uniqueness test on the declared grain catches this at once.
-
Which fact-table shape fits events, regular state, multi-step processes and bare occurrences?
Transaction facts for events, periodic snapshots for regular state, accumulating snapshots for multi-step processes and factless facts for occurrences without a measure.
-
How do you sum additive, semi-additive and non-additive measures correctly?
Add additive measures freely. Reduce a semi-additive one to a single point in time before adding. For a non-additive ratio, store its numerator and denominator rather than the ratio itself.
-
Why does summing a wallet balance over several days give a meaningless number?
It counts the same money once for every day. Sum across wallets on a single day, or take the latest value of each wallet.
-
Why must a fact table's grain be written in its description and tested?
A grain that lives only in its author's head changes silently when a teammate joins another table to add a column, and a total that fed reports starts counting rows twice.
-
A customer moves hostel and all her past orders move with her on the dashboard. Where is the fault?
In the customer dimension, which overwrote the attribute: the old hostel's line fell by 320 and the new one's rose by 320 while the fact table stayed unchanged.
-
Which two keys does a dimension row carry, and who owns each?
A natural key that the source owns and a surrogate key that the warehouse owns and the fact table stores, so one entity can have many versions over time.
-
How do SCD Types 1, 2 and 3 treat a changed attribute?
Type 1 overwrites it, Type 2 adds a row per version with a valid interval, and Type 3 keeps the current value and one earlier value. The choice is made per column, so one dimension can use all three.
-
Which two SQL statements implement SCD Type 2?
A MERGE that closes the current row when its attributes changed, followed by an INSERT that adds the new version and any entities seen for the first time.
-
How do you join a fact to the dimension version that was valid when it happened?
Match the natural key, then keep the version whose valid_from is on or before the fact date and whose valid_to is after it or NULL, with NULL read as no end.
-
What goes wrong when facts are joined to a versioned (Type 2) customer table by the customer's source id only?
The join matches every version, so one order comes back once per version and every total that includes it doubles. A fact row must reach exactly one version.
-
What should a pipeline do with a fact whose dimension row has not arrived yet?
Never drop it: give it an inferred dimension row, and correct that row when the real attributes arrive.
-
Three orders worth 1,370 rupees showed as 3,070 on a promotions dashboard. Why?
Joining orders to promotions repeated each order once per promotion it carried, so the sum counted the same revenue several times.
-
When is a dimension conformed, and what is a bus matrix for?
When every process that reads it uses the same keys and attributes. A bus matrix is the one-page plan that makes that sharing a decision taken before any table is built.
-
Which small cases do role-playing, degenerate, junk and mini-dimensions cover?
Reused attributes, bare identifiers, low-cardinality flags and volatile slices, in that order, each without the weight of a full dimension table.
-
What does a many-to-many link between a fact and a dimension need?
A bridge table plus an allocation rule whose factors add up to one for each fact row. Without the allocation, every total per dimension member double-counts.
-
Which check keeps a bridge table from over-counting quietly?
A test in the pipeline that finds every order whose allocation factors do not add up to 1.0; any such row stops the publish.
-
How does a Data Vault store data, and what do analysts query?
Business keys in hubs, relationships in links and changing attributes in satellites, each row stamped with a load time and a record source. Analysts query a star view on top that picks the latest row per hub.
-
What three things do you state in a modelling interview before drawing anything?
The grain in one sentence, the conformed dimensions by name and the history policy.
-
A file sent twice gave 10 rows for 7 orders. What two errors can one query straight to a sales table make?
It can count the re-sent orders twice, or treat an order that changed from paid to cancelled as a sale on one run and drop it on the next.
-
What is each layer of an ELT pipeline responsible for?
Raw keeps what arrived, staging cleans each source table, intermediate joins tables and holds the shared rules, and marts are the tables people read.
-
Which rule keeps the logic of ELT layers easy to follow?
Every piece of logic lives in exactly one layer, and each layer reads only the one beneath it.
-
What does a staging model do, and what does it leave out?
It casts, renames and cleans one source table and keeps one row per key with a stated tie-breaker. It holds no business rule, because every model above it would inherit that rule.
-
Where does a rule such as 'a sale is a paid order' belong?
In intermediate, under a name such as is_sale that every mart reads. Filtered in staging, it fixes the sales mart and quietly empties a cancellations report built on the same view.
-
How does a mart show that it holds what it claims?
It states its grain in one sentence and proves it with a grain test and a reconciliation.
-
What makes a SQL pipeline safe to rerun?
Every model is rebuilt with CREATE OR REPLACE, reads a run date passed in rather than the clock, and ranks each latest row by an order on which no two rows can tie.
-
Why does rebuilding a fact table every night stop scaling?
Each night has one more day of history to rewrite: after a year, adding one day of orders rewrites about 365 days of them.
-
What is the real danger of an incremental load, more than its cost?
A batch that arrives twice. Retries, reruns after a fix and deliberate safety windows all resend rows the target holds, and a plain append counts them again without an error.
-
In which four ways can an incremental load write its batch?
Append it, MERGE it on a business key, delete and insert the days it covers in one transaction, or overwrite whole partitions such as one day's folder.
-
Which write strategies survive a rerun, and how does each recognise the rows?
MERGE recognises rows by key, so it suits rows that change in place. Delete-and-insert and partition overwrite recognise whole days, need no key and also drop rows the source removed. Append does not survive a rerun.
-
Run twice on one day of 3 orders, what did a plain INSERT and a MERGE on order_id leave?
The INSERT left 6 rows and doubled the day from 485.50 to 971.00 rupees. The MERGE left 3 rows and 485.50 both times, and later took a corrected order as an update.
-
What does a MERGE leave alone, and what does it assume about its batch?
It deletes no target row unless a WHEN NOT MATCHED BY SOURCE clause asks it to, and it assumes every key appears once in the batch.
-
Ten raw rows for five payments summed to 1,830 instead of 1,290. Why?
Two rows were second copies of events already stored. Delivery is at least once: a sender that hears nothing back sends the same event again.
-
Which column finds duplicate payment events, and why not the payment id?
The event id, which every copy of an event shares. One payment can have several real events, such as a capture and a refund, so the payment id would merge different events.
-
What do DISTINCT, GROUP BY and QUALIFY each do with duplicate rows?
DISTINCT drops rows that are equal in every selected column, GROUP BY builds one new row per key from aggregates, and QUALIFY with row_number() = 1 keeps one whole, real row per key.
-
How can GROUP BY payment_id with max(status) produce a row that never existed?
max(status) picks the alphabetically last status rather than the newest, so a payment can show settled beside the time of its refund. The other columns come from different events.
-
Why is ORDER BY event_at DESC not enough to choose the latest event?
Copies of an event share event_at and providers stamp whole seconds, so rows tie, and SQL may return tied rows in a different order on another run. End the ordering with a column unique within the key that follows arrival, such as an ingestion sequence.
-
What do event time, ingestion time and processing time each record?
When the payment happened, as the provider stamped it; when the loader received the row; and the time on the machine that runs the job.
-
Why should a daily total use event time, and what does that choice cost?
Only event time puts money on the day it moved; by processing time every payment of the run landed on the same later day. The cost is late rows, which arrive after their day was already totalled.
-
What does EXPLAIN ANALYZE add to EXPLAIN?
EXPLAIN prints the physical plan the engine chose; EXPLAIN ANALYZE also runs it and reports the rows every operator produced.
-
In which direction should a DuckDB plan be read?
From the bottom up. The root is printed first, but rows travel upwards from the scans, so set each operator's rows_out against the rows its inputs sent it.
-
What do Projections and Filters on a Parquet scan in a DuckDB plan show?
Projection and predicate pushdown: the column list and the condition moved into the scan, which then reads only the columns and rows the query needs and needs no FILTER above it.
-
Which input of a DuckDB HASH_JOIN is loaded into the hash table?
The second input, the build side; the first, the probe side, streams past it. Joined to the 10-row outlets table, the 50,000 orders came out as 50,000 rows, one per order.
-
When can a filter let a scan skip partition folders?
When it reads only the partition columns, even through a function such as day(order_date). Once it also needs a value kept inside the data files, no folder name can answer it and every file is opened.
-
Where does most of a transformation's speed come from?
From three savings: opening fewer files, reading fewer columns and moving fewer rows than before.
-
What does dbt make of a folder of SELECT files?
The ref() calls between the files form a DAG, and dbt build runs seeds, models and tests in dependency order, so nothing runs before the thing it reads from.
-
Why does a script that creates tables in a typed-out order break as a project grows?
A model added in the middle can run before the table it reads is rebuilt, and then it fails or quietly reads yesterday's rows. Declaring inputs with ref() lets dbt work out the order.
-
What are the two generations of dbt?
v1 is the Python dbt-core plus an adapter; v2 is a ground-up rewrite in Rust. Both read the same project files, but they reach databases differently and are licensed differently.
-
How are dbt-core, dbt-oss and the recommended dbt package licensed?
dbt-core is Apache-2.0. The Rust engine ships as dbt-oss with Apache-2.0 code only, and as the recommended dbt package, which adds proprietary features under a product licence.
-
How should each dbt generation be installed?
Each in its own virtual environment with pinned versions. v1 needs its adapter named, such as dbt-duckdb; v2 installs as one package and fetches its database driver the first time it connects.
-
Why should a job check dbt's exit status instead of reading its summary line?
The two engines log the same build differently, even when both produce the same eleven nodes and the same mart.
-
What decides which dbt version a team standardises on?
Its warehouse, since v2 lists the platforms it supports; anything that ties it to v1; the features and licence it needs; and how settled a tool it wants. The connection profile stays the same and reads secrets from the environment.
-
What is a dbt model, and how does it name its inputs?
One SELECT in a .sql file. ref() names what the project builds and source() names raw tables it does not; both compile to a fully qualified relation name.
-
What goes wrong when a mart reads from main.stg_orders instead of ref('stg_orders')?
The DAG loses that edge, so dbt may schedule the mart before the staging view exists, and the error then names a missing table instead of the real cause.
-
How do the view, table and ephemeral materializations differ?
A view recomputes on every query and is always fresh; a table stores the result once and is stale between builds; an ephemeral model is inlined as a __dbt__cte__ CTE and leaves no object behind.
-
When should a dbt model use source(), and when ref()?
source() for a table dbt does not build, such as raw data an ingestion tool loads; ref() for the models, seeds and snapshots the project builds. A ref() edge makes dbt build the upstream node first.
-
How does dbt source freshness decide that a feed is stale?
It compares the newest value of the source's loaded_at_field with now, against the warn_after and error_after set in YAML. Past error_after it exits non-zero, the signal a scheduler reads to hold back downstream jobs.
-
Does dbt source freshness check every table of a source?
No. Only tables with a freshness block are checked and the rest are skipped, so the check is switched on one table at a time.
-
One order names a canteen that does not exist. What does dbt build do with the mart?
The relationships test fails, and the build skips every model that depends on all the models the test checked, so the daily revenue mart is not written from rows a test has just rejected.
-
What two shapes do dbt data tests take?
Generic tests configured in YAML on a column, such as unique, not_null, accepted_values and relationships, and singular tests, each a query selecting the rows that break a rule.
-
Why can dbt run followed by dbt test publish a mart that dbt build would skip?
dbt run writes every model before any test looks at the rows, while dbt build runs each test as soon as its model exists and skips what depends on a failure.
-
What does a dbt unit test catch that a data test cannot?
A broken rule that today's data never exercises: it feeds the model rows you wrote and compares the output with expected rows. It reads no stored rows, but the model's parents must exist.
-
What does an enforced dbt model contract check, and when?
The shape of the result, its column names, data types and column count, while the model is built, so a renamed or dropped column fails the run that changed it. It says nothing about meaning, quality or freshness.
-
A model returns DOUBLE where its contract says decimal(10,2). What did each dbt engine do?
dbt-core 1.12.6 stopped the build with an enforced contract failure naming the column. dbt-oss 2.0.8 created the table with the contract's types and DuckDB cast the value, so a passing build is no proof of the type.
-
Which of data tests, unit tests and contracts should a project use?
Each answers its own question, today's rows, the logic or the shape, so pick by the failure you fear and expect to need more than one.
-
How does a dbt incremental model add only new rows?
is_incremental() is false on the first run and true afterwards. Its true branch keeps only source rows newer than a watermark read from the model's own stored table, and a unique_key replaces a changed row instead of adding a copy.
-
What does dbt's --full-refresh do to an incremental model?
It drops the table and rebuilds every row from the whole source, because is_incremental() is false again for that run.
-
Which settings turn a dbt model into microbatch, and what does each batch do?
incremental_strategy set to microbatch, an event_time column, a batch_size such as day and a begin date. Each batch re-inserts its own period, and lookback re-runs earlier days to catch late rows.
-
Why can a microbatch model backfill or retry one day without rebuilding the rest?
Each batch touches only its own window of time, so a past range between --event-time-start and --event-time-end, or a single failed day, runs on its own.
-
What does a dbt snapshot record, and how?
Every version of a row that changes in place, as SCD Type 2: dbt_valid_from and dbt_valid_to around each version, with dbt_valid_to null for the one in force. The timestamp strategy watches updated_at to spot a new version.
-
Did dbt-core 1.12.6 and dbt-oss 2.0.8 write different rows for incremental, microbatch and snapshot runs?
No. Both wrote identical rows in every run; only the way they reported a run differed, such as one line per batch against one model line.
-
Which files does a production dbt build leave in target/ for the next CI run?
manifest.json, with every node, its code, its refs and a checksum of its SQL; run_results.json, with the status of each node that ran; and semantic_manifest.json. A deploy job publishes them to a known location.
-
Which three flags make a slim dbt CI run?
--select state:modified+ for the changed nodes and everything downstream, --state pointing at the production manifest, and --defer to read unchanged parents from where that manifest published them.
-
Why should a CI job select state:modified+ and not state:modified?
Without the plus only the changed nodes build, so CI passes while a mart that reads the edited model is never built against it.
-
How big was the slim CI build when one staging model changed?
6 nodes instead of the 11 of the production build: the changed model, the mart downstream and the four tests on the changed model, the same six on both engines.
-
What do a dbt model's group and access keys control?
group pins the model to an owner; access is private (only models of the same group may ref it), protected (any model of the project) or public (no limit).
-
How do dbt-core 1.12.6 and dbt-oss 2.0.8 treat a private model referenced from another group?
dbt-core refuses it when it parses the project. dbt-oss ignores the group and access keys with an UnusedConfigKey message and builds anyway, so a feature you rely on may not take effect on your deploy engine.
-
What does it mean for a pipeline to be a DAG of tasks?
A task starts only when every task it waits for has succeeded, so a missing input file stops every task below it, and a cycle is refused before anything runs.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
A validation step must compare new city totals with the old ones. How do you add it without a cycle?
Give the comparison its own task after the aggregation and make the email wait for it. Making validate also wait for the aggregation that waits for validate is a cycle, and the run refuses to begin.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
Which three things can start a pipeline run?
The clock, as a five-field cron expression or a timetable in a time zone; an update to the run's input; or an outside message. The way the input arrives decides which fits.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
Why must each run process its own data interval instead of reading the clock?
Reruns and backfills replay old runs. In the lesson's replay, runs that read the clock wrote 1 day and missed 2 of the 3 they were meant to cover.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
What is wrong with deleting order_date = CURRENT_DATE - 1 before a task writes its day?
Replayed later, it clears the day before the replay rather than the run's own day. Use the start of the run's data interval as the day to replace.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
With 3 retries 30 seconds apart and a 5-minute timeout, how long can one task hold up the next?
21 minutes 30 seconds at most: four attempts of up to 5 minutes each, plus three waits of 30 seconds.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
When is it safe to let an orchestrator retry a task?
Only when the task is idempotent. A task that inserts its rows and then fails doubles them on the retry; replace the day's partition, or upsert by order id, instead.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
When is cron with a script enough, and when does an orchestrator pay off?
Cron is enough for one job on one machine with a log and an alert. An orchestrator pays once jobs depend on each other, past days must run again, or several people share the on-call.
From the lesson Orchestration concepts: DAGs, schedules, backfills
-
What does airflow dags test run?
One DAG run, with every task in one Python process, against a migrated metadata database and with no scheduler or web server.
From the lesson Airflow 3: TaskFlow, assets and schedules
-
How does Airflow's TaskFlow API work out the order of the tasks?
@task turns a function into a task whose return value goes to the tasks that use it, and the calls in the @dag body, such as load(transform(extract())), draw the arrows. No >>, set_upstream or operator class is needed.
From the lesson Airflow 3: TaskFlow, assets and schedules
-
What is an XCom in Airflow?
A small value handed from one task to another, which Airflow keeps with the run. The pipeline's files belong elsewhere, often in object storage.
From the lesson Airflow 3: TaskFlow, assets and schedules
-
Where do the dag and task decorators come from in Airflow 3?
From airflow.sdk, in place of Airflow 2's airflow.decorators. Task code now reaches Airflow through the Task SDK rather than the metadata database.
From the lesson Airflow 3: TaskFlow, assets and schedules
-
Which two settings of a DAG file surprise new Airflow writers?
catchup=False, which stops the scheduler creating a run for every interval missed since start_date, and the fact that code outside the task functions runs every time Airflow parses the file, so expensive work belongs inside a @task.
From the lesson Airflow 3: TaskFlow, assets and schedules
-
What is the date passed to airflow dags test?
The run's logical date, which identifies the run. The slice of time the run covers is its data interval, and the schedule decides how long that is.
From the lesson Airflow 3: TaskFlow, assets and schedules
-
Why does a daily Airflow 3 task that filters rows by its data interval select nothing?
Airflow 3 turns the @daily schedule, like any bare cron string, into a trigger timetable, so the run's interval is 0 minutes long and the query finds no rows, yet the run succeeds. CronDataIntervalTimetable gives each run a whole day.
From the lesson Airflow 3: TaskFlow, assets and schedules
-
An appending task ran on schedule and again in a backfill for the same day. What happened?
The day's file went from 4 rows to 8 without a single error, and every total built on that day doubled. A dashboard finds such a partition, not the orchestrator.
From the lesson Idempotent tasks, retries and backfills
-
Which three properties make a pipeline task idempotent?
Deterministic inputs, read from the run's data interval and never from now(); deterministic outputs that replace the day's whole output; and atomic publishing, so the new output appears all at once.
From the lesson Idempotent tasks, retries and backfills
-
Why publish a partition by writing a temporary file in its own folder and calling os.replace?
A successful os.replace renames atomically and replaces an existing file, so readers see either the old day or the new one. A rename can fail across file systems, which is why the temporary file sits in the same folder.
From the lesson Idempotent tasks, retries and backfills
-
What should a task do to its temporary file before the rename that publishes it?
Call flush() to hand Python's buffer to the operating system, then os.fsync() to ask for the file on disk. A crash at any step then leaves the published day whole.
From the lesson Idempotent tasks, retries and backfills
-
Why is reopening the day's file in mode w not enough?
It stops the doubled rows, but the file is empty from the open until the last write, so a reader or a crash in between meets part of the day.
From the lesson Idempotent tasks, retries and backfills
-
Why does an idempotent task make a backfill safe?
A backfill is many runs of the same task for past days, each with its own interval, so a task that may run twice for one day may run for thirty.
From the lesson Idempotent tasks, retries and backfills
-
How do task-centric and asset-centric orchestrators differ?
Task-centric tools, Airflow, Prefect and Kestra, model a pipeline as a graph of steps. Asset-centric tools, Dagster and Airflow's assets, model the data products and derive the steps from them.
From the lesson Dagster, Prefect, Kestra and cron: how they compare
-
Where does cron sit beside the orchestrators?
At the bare end of the task-centric side: it only starts each command on time, and job order, retries and reruns of past days are left to the scripts.
From the lesson Dagster, Prefect, Kestra and cron: how they compare
-
How does Kestra differ from Airflow and Prefect?
It is task-centric as well, but its flows are written in YAML rather than Python and run on its own scheduler, UI and server written in Java.
From the lesson Dagster, Prefect, Kestra and cron: how they compare
-
What do Airflow, Dagster, Prefect and Kestra all do that cron does not?
Each runs a graph of work on a schedule, in order, with retries you can set and a record of every run; cron only starts each command on time.
From the lesson Dagster, Prefect, Kestra and cron: how they compare
-
Can a licence or a download count pick the orchestrator for you?
No. All four carry the Apache License 2.0, and the three Python packages were within about a million PyPI downloads of each other in the saved month. Caches, mirrors and scripts also skew such counts.
From the lesson Dagster, Prefect, Kestra and cron: how they compare
-
What does decide between cron and the four orchestrators?
How many pipelines you run, the unit you think in and who runs the servers. Cron with a tested script fits one or two jobs, a team that cannot run servers needs a hosted service, and a team that reasons about data leans to Dagster or Airflow's assets.
From the lesson Dagster, Prefect, Kestra and cron: how they compare
-
Why are iterative algorithms slow on MapReduce?
Each pass is its own job, and it writes its whole result to a replicated file system before the next pass can read it back. Spark keeps that data in memory between steps.
From the lesson From MapReduce and Hadoop to Spark
-
How does Spark recover a lost partition of an RDD?
It recomputes the partition from its parents, following the RDD's lineage, the recorded chain of transformations, instead of keeping a copy in advance.
From the lesson From MapReduce and Hadoop to Spark
-
How does Spark turn one action into work for the executors?
The driver makes the action a job, cuts the job into stages at every shuffle and runs one task per partition: the lesson's word count was 1 job, 2 stages and 4 tasks.
From the lesson From MapReduce and Hadoop to Spark
-
Is Spark's map the map phase and reduceByKey the reduce phase?
No. A shuffle splits the job into stages, and reduceByKey works on both sides of it: it combines pairs in the first stage, beside flatMap and map, and adds them up in the second.
From the lesson From MapReduce and Hadoop to Spark
-
What separates a Spark transformation from an action?
A transformation only adds to the plan and an action runs it. One shuffle splits the work into 2 stages, each partition is one task, and each action runs its chain again unless it can reuse shuffle output.
From the lesson From MapReduce and Hadoop to Spark
-
How does Spark Connect differ from local mode?
Local mode puts the driver and the executor in one JVM on your machine. Spark Connect moves the driver to a server and leaves the client a session that has no RDDs.
From the lesson From MapReduce and Hadoop to Spark
-
What do HDFS, YARN and the Hive metastore do?
HDFS stores files as replicated blocks, YARN hands out a cluster's resources, and the Hive metastore keeps table definitions that several engines read.
From the lesson From MapReduce and Hadoop to Spark
-
Is a PySpark DataFrame query faster than the same query in Spark SQL?
No. They are two front doors to one engine, and Catalyst turns both into the same optimised plan and physical operators. In the run, both versions printed the same six rows and the same plan outline.
From the lesson PySpark 4.2 DataFrames and Spark SQL
-
What does Spark's Catalyst optimiser do with a query?
It resolves the names in a logical plan, rewrites the plan with rules, picks physical operators and generates code, whichever API or language wrote the query.
From the lesson PySpark 4.2 DataFrames and Spark SQL
-
What does spark.read take, and what decides df.write's behaviour at a path that holds data?
spark.read takes a format, options such as header for CSV, and a schema or inference, then a path to load. The save mode of df.write decides what happens to data already at the path.
From the lesson PySpark 4.2 DataFrames and Spark SQL
-
How does Spark SQL read and write files without the DataFrame API?
It reads through a temporary view, or a format and a path in the FROM clause, and writes with INSERT OVERWRITE DIRECTORY ... USING parquet ahead of a query, which replaces what the folder held.
From the lesson PySpark 4.2 DataFrames and Spark SQL
-
What happens to a bad cast, an overflow or a division by zero in Spark 4?
ANSI mode is on by default since Spark 4.0, so the query stops with an error naming a condition and a five-character SQLSTATE, where Spark 3 returned NULL or a wrapped number. The try_ functions return NULL instead.
From the lesson PySpark 4.2 DataFrames and Spark SQL
-
What do QUALIFY and the pipe syntax add to Spark SQL?
QUALIFY, added in Spark 4.2, filters on a window function's result inside the same SELECT. The pipe syntax writes a query as FROM followed by steps joined with |>, each working on the result of the one before.
From the lesson PySpark 4.2 DataFrames and Spark SQL
-
Where does a Python user-defined function run in PySpark?
In a separate Python process, whereas built-in functions run inside the engine.
From the lesson PySpark 4.2 DataFrames and Spark SQL
-
Why is the shuffle the step people blame for a slow Spark job?
It is the only step that moves rows between tasks so that equal keys meet. Every task writes its rows into buckets and every reader fetches its own, which costs serialisation, disk and network and splits the job into stages.
From the lesson Spark joins, shuffles and partitions
-
Why does a filter plan no Exchange while a count per zone plans one?
A filter decides each row where it already sits. A count needs every row of one zone in one place, and only a shuffle puts them there.
From the lesson Spark joins, shuffles and partitions
-
What are Spark's three join strategies, and which move the big side?
A broadcast hash join sends one whole side to every executor and leaves the other in place. A sort-merge join and a shuffled hash join both shuffle the two sides on the key; the first sorts and merges, the second builds a hash table per partition.
From the lesson Spark joins, shuffles and partitions
-
When does Spark broadcast one side of a join?
When its estimated size is under spark.sql.autoBroadcastJoinThreshold, 10485760 bytes by default. A value of -1 turns broadcasting off, and a BROADCAST hint wins even above the threshold.
From the lesson Spark joins, shuffles and partitions
-
Joining 1,200,000 delivery events to 8 city zones, how many event rows crossed the network?
None. The 8-row table sat far below the broadcast threshold, so the plan was a BroadcastHashJoin and the events never left their tasks.
From the lesson Spark joins, shuffles and partitions
-
Where do the partitions of a file read and of a shuffle come from?
A read packs files into partitions of up to spark.sql.files.maxPartitionBytes, 128 MB by default, charging each file an open cost of 4 MB. A shuffle declares spark.sql.shuffle.partitions, 200 by default, which adaptive execution may coalesce.
From the lesson Spark joins, shuffles and partitions
-
A stage has 7 of 8 tasks finished and one still running. What do you look at first?
The rows each task holds. In the lesson that one task held 820,000 of the stage's 1,000,000 rows, and adding cores would not take a single row off it.
From the lesson Spark performance: AQE, skew and spills
-
Which three shapes explain most slow Spark stages?
Skew, with one task far above the rest; spills, with every task too large for its memory; and thousands of near-empty tasks whose start costs more than their work.
From the lesson Spark performance: AQE, skew and spills
-
How do you put a number on skew?
Count each partition's rows with spark_partition_id() and divide the largest by the mean. Hashed on the city id, one of 8 partitions held 82.0 % of 1,000,000 rows, 6.56 times the mean.
From the lesson Spark performance: AQE, skew and spills
-
What does adaptive query execution do, and is it on in Spark 4.2?
It re-plans a query between stages from the sizes that finished stages report. AQE and its skew-join handling are both on by default; the skew handling covers the two joins that shuffle both sides.
From the lesson Spark performance: AQE, skew and spills
-
Where does the Spark UI report a spill?
On the stage page, for each task, as shuffle spill (memory) and shuffle spill (disk).
From the lesson Spark performance: AQE, skew and spills
-
Which Spark cost shows up in no task count?
A DataFrame that two actions read is computed twice unless it is cached; unpersist() frees the cache once the actions are done.
From the lesson Spark performance: AQE, skew and spills
-
Why is a Spark DataFrame write really a commit?
Each task writes its files into an attempt folder under _temporary, one attempt per task is committed, and the job commit publishes the files and writes _SUCCESS.
-
Writing 2 orders into a Parquet folder that holds 3, what does each save mode leave?
errorifexists, the default, refuses with PATH_ALREADY_EXISTS and keeps 3. append leaves 5, overwrite leaves 2, and ignore keeps 3 without raising anything.
-
Does any Spark save mode lock the folder or commit atomically?
No. An overwrite deletes the old data before writing the new, so a job that dies halfway can lose rows that were there before.
-
Why is the ignore save mode dangerous for a daily refresh?
When data already exists it writes nothing and raises nothing, so the job reports success while readers keep yesterday's rows.
-
How does dynamic partition overwrite differ from Spark's default static mode?
Dynamic mode rewrites just the partitions that receive new rows, while static mode first deletes all partitions the write matches. The session setting chooses the mode, and a write's own option overrides it.
-
How do insertInto and saveAsTable match a DataFrame's columns to a table?
insertInto matches them by position. saveAsTable in append mode, like SQL's INSERT INTO ... BY NAME, matches them by name.
-
What do Delta Lake and Apache Iceberg offer for overwriting part of a table?
Delta's replaceWhere overwrites only the rows matching a condition, and Iceberg's overwritePartitions() and overwrite() replace partitions or filtered rows; each is a single atomic change to the table.
-
What do you write in Spark Declarative Pipelines, and what does SDP do with it?
You declare each dataset with the query that fills it. SDP builds a dataflow graph from the names those queries read, runs the flows in dependency order and keeps their checkpoints.
-
How does SDP know which dataset to build first?
From the table names the queries read, as dbt does with ref(): delivered_orders selects from orders_raw, so orders_raw runs first, and a plain table name is enough.
-
How do a streaming table, a materialised view and a temporary view differ in SDP?
A streaming table appends only input that is new since its checkpoint, a materialised view is recomputed by one batch flow, and a temporary view is never stored and is seen only by the flows of the same run.
-
Which three spark-pipelines commands take a project from nothing to updated tables?
init creates the project and its pipeline spec, dry-run checks the whole graph without moving data, and run updates the tables.
-
How do run --refresh and run --full-refresh differ in SDP?
--refresh updates the datasets it names without clearing them; --full-refresh clears their data and checkpoints first, and --full-refresh-all does so for every dataset.
-
What do an append flow and a sink add to an SDP pipeline?
An append flow adds another query that writes into an existing streaming table, such as orders from a second app. A sink sends a streaming flow's rows out of the pipeline, to a Kafka topic for example, and is declared in Python only.
-
Over 10 million orders on 2 cores per engine, how did local Spark compare with DuckDB?
Starting the Spark session alone took over five times as long as DuckDB needed to start and answer all three queries, and every engine returned the same answers.
From the lesson Single-node engines or Spark: how to choose
-
Why can DuckDB answer before a local Spark session has even started?
DuckDB is a library inside the program, so starting it means opening a connection. Spark first starts a Java process with its driver and scheduler, then plans, splits and schedules each query.
From the lesson Single-node engines or Spark: how to choose
-
When does a single-node engine win, and when does Spark pay?
A single-node engine wins while the data, the time window and the arrivals fit one machine. Spark pays when the work must spread over machines, the platform already runs Spark, or a job needs Structured Streaming or one of Spark's data sources.
From the lesson Single-node engines or Spark: how to choose
-
What is the difference between scaling up and scaling out?
Scaling up moves a job to a bigger machine; scaling out spreads it over more machines, as Spark does.
From the lesson Single-node engines or Spark: how to choose
-
What must an engine benchmark report to be fair?
The cold run apart from the runs after a warm-up, so that start-up is not mistaken for query time.
From the lesson Single-node engines or Spark: how to choose
-
What does a cluster's total cost of ownership include beyond its bill?
The people and time its upgrades, security patches, capacity planning and on-call take. A managed Spark platform takes over part of that work.
From the lesson Single-node engines or Spark: how to choose
-
How does polars use one machine?
It spreads DataFrame work over all the machine's cores, and its streaming engine runs a query in batches when the data does not fit in memory.
From the lesson Single-node engines or Spark: how to choose
-
Why can an ops dashboard and a finance dashboard read different totals from one order stream?
Ops groups orders by the minute they arrived and finance by the minute they were placed. An order delayed on a phone changes only the second total, so both numbers are honest.
-
Why do streaming aggregations live in windows, and which shape fits which question?
A stream never finishes, so totals are taken over windows: tumbling for a fixed cadence, hopping for a moving total and session windows for bursts of activity.
-
Which clock should a streaming total be grouped by?
The one users ask about. Event time, ingestion time and processing time answer different questions, and the pipeline's own time is only housekeeping.
-
What is a watermark in stream processing?
A declaration that event time in the stream has reached a given point. It lets the engine close a window, emit the window's total and drop its state.
-
When does an order count as late in a windowed stream?
Only when its window has already closed, not as soon as its event time falls below the watermark. Neither Spark nor Flink adds it to the total by default; Flink can keep the window open for an allowed lateness or hand late orders to a side output.
-
For the 10:00 to 10:10 window, what did the same eight orders total by arrival, by event time and with a watermark?
720 by arrival, 1,220 by event time once every order was in, and 920 by event time with the watermark.
-
Which delivery promises can a streaming pipeline keep end to end?
At-most-once, at-least-once or effectively-once. The useful end-to-end promise comes from pairing Kafka's guarantee with an idempotent or transactional sink.
-
When is streaming worth its cost over a micro-batch?
When a decision loses its value within minutes, late data is minutes rather than hours, and the team can carry the operational load. Otherwise a 5-minute micro-batch is simpler, cheaper and enough.
-
Why does Kafka split a topic into partitions?
So consumers can share the work while each key's events keep their order. One ordered queue makes every canteen wait behind the slowest event, and a queue per canteen means hundreds of queues to run.
-
How does a one-node Kafka 4.3 lab start without ZooKeeper?
Kafka 4.0 removed ZooKeeper, so the node is formatted with kafka-storage.sh format --standalone, which writes the cluster id and the first metadata log, and runs in KRaft mode as both broker and controller.
-
Why does the Kafka lab write its own copy of config/server.properties?
The shipped file binds every network interface. The lab's copy binds 127.0.0.1 only and skips authentication, which suits a throwaway lab; a real cluster adds TLS, SASL and ACLs.
-
What is the one ordering guarantee Kafka gives?
A consumer of a topic-partition reads its records in the order they were written. Across partitions nothing is ordered.
-
How do the Java client and kafka-python map a record's key to a partition?
They take the key's MurmurHash2 value modulo the number of partitions. Every record carrying that key therefore lands on one partition until someone changes the partition count.
-
A partition's log-end offset is 5 and the group has committed offset 1. What is its lag?
4 records. Lag is the log-end offset minus the group's committed offset, which each group keeps per partition.
-
Does reading an event remove it from a Kafka topic?
No. Events stay in the topic after a consumer reads them, until retention or compaction removes them.
-
Two services keyed one order's events chennai, yet they landed on partitions 2 and 0. How?
One used kafka-python, which hashes keys with murmur2 like the Java client, and the other confluent-kafka, whose default partitioner hashes with CRC32. Two hashes place a key on the same partition only by chance.
-
How does the Java client compute the partition of a keyed record?
murmur2(key) & 0x7fffffff, taken modulo the partition count; kafka-python does the same.
-
How do you make librdkafka producers place keys as the Java client does?
Set partitioner=murmur2_random in every librdkafka producer that writes the topic, and prove it with one record per key.
-
What happens to keys when a topic grows from 3 to 6 partitions?
They move: chennai went from partition 2 to 5. Records already written stay where they were.
-
How can a retry put one key's records out of order, and what prevents it?
A failed batch resent after the next batch succeeded lands behind it. An idempotent producer, or one request in flight, prevents that.
-
Where does the Java client send records that have no key?
To a sticky partition that it changes after a batch.size worth of bytes, favouring faster brokers: even load and no order. kafka-python 3.0.11 still picks a random partition for each record.
-
What does a hot key do to a consumer group, and what spreads it?
It puts most of the traffic on one partition and one consumer. A composite key such as city:order-id spreads the load and keeps each order's events together.
-
Which five producer settings shape a send once its partition is chosen?
acks, the replicas that must store a batch; idempotence, so a resent batch is stored once; retries within the delivery timeout; batching with compression; and a delivery callback that reports each record's outcome.
-
How many members of one consumer group can a 3-partition topic keep busy?
At most 3. Within a group a partition belongs to a single member at any moment, so the number of partitions caps the members doing work.
-
A consumer has handled offsets 0 to 3. Which offset does it commit?
4, the offset it should be given next. The committed offset is all a restart has to go on.
-
What did committing after processing do in the crash drill?
It gave at-least-once delivery: the restart was handed the uncommitted batch again, so one event reached the file twice, 13 writes for 12 events, never more than one batch repeated.
-
What did committing before processing do in the same crash?
It gave at-most-once delivery: 3 of the 12 events were never processed, while the lag read 0.
-
Why does a consumer lag of zero not prove the pipeline is correct?
Lag is only the distance from the group's committed position to the newest record of the partition; it cannot see what the code did with any record.
-
Why do the drills turn enable.auto.commit off?
It is on by default in Kafka 4.3. The Java consumer then stays at-least-once only if each poll's records are finished before the next poll, and librdkafka commits every 5 seconds from a background thread, whatever it has handed over.
-
What do max.poll.records and max.poll.interval.ms control?
max.poll.records, 500 by default in Kafka 4.3, caps what one poll hands over; max.poll.interval.ms, 5 minutes, is how long the loop may stay away before the member counts as failed.
-
What do share groups and dead-letter topics add to Kafka consumers?
Share groups let several members read one partition and acknowledge records singly, with no committed offset. A dead-letter topic is an ordinary topic for records a consumer has given up on, so one poison message cannot hold a partition for ever.
-
What are a Kafka Connect worker, connector, task and converter?
A worker is a JVM whose REST API takes connector configurations. A connector is a plugin that reads from or writes to one external system, split into tasks that run in parallel. A converter turns a record's key or value into bytes and back.
-
Where does a distributed Kafka Connect worker keep its state?
In three Kafka topics: config.storage.topic for connector configurations, offset.storage.topic for the position of every source task, and status.storage.topic for the state of each connector and task.
-
How do Kafka Connect's standalone and distributed modes differ?
Standalone runs every task in one JVM with no fault tolerance. Distributed runs connectors across JVMs that share a group.id and the three topics, so a joining worker takes a share and a leaving one hands its share over.
-
How is a connector created on a distributed Connect worker?
By a POST of its JSON to /connectors, which stores it in config.storage.topic for every worker. Passing the JSON as a file to connect-distributed.sh starts the worker but creates no connector.
-
What does one Debezium PostgreSQL change record carry?
A key made of the table's primary key, and a value with before, after and op: r for a snapshot read, c for an insert, u for an update and d for a delete.
-
How does a schema registry decide whether to accept a new schema version?
It stores schemas by subject, named after the topic plus -value by the Confluent-compatible default, and checks each new version against the subject's rule: BACKWARD, FORWARD or FULL.
-
How are Apicurio, Karapace, Confluent's and Redpanda's registries licensed?
Apicurio and Karapace under Apache 2.0. Confluent's registry under the Confluent Community licence and Redpanda's under Business Source 1.1: source-available, readable and runnable, but not to be offered as a competing hosted service.
-
Where does a Kafka Connect sink keep its progress?
As the committed offset of the sink's own consumer group; the offsets topic holds source connectors' positions only.
-
After a failed batch was redone, a reader summed 9,211 rupees for six settlements worth 6,727. Why?
Three records were in the log twice, and the reader used Kafka's default isolation level, so it also counted the records of the aborted transaction.
-
What does Kafka's exactly-once cover, and with which three mechanisms?
Reading a topic, processing and writing back to a topic in one cluster: an idempotent producer, a transaction over the output records and the input offsets, and a reader at isolation.level=read_committed.
-
What do exactly-once pipelines actually promise?
One effect per event, not one delivery. Kafka's own default is at-least-once: a producer that never sees the broker's answer resends, and a consumer that dies reprocesses what it had read.
-
How do a consumer group's offsets become part of a Kafka transaction?
Committed offsets are records in an internal topic, so the transactional producer sends them with send_offsets_to_transaction() and then commits or aborts the batch.
-
Does Kafka's Java client skip records of aborted transactions by default?
No. A reader opts in with isolation.level=read_committed. The Java console consumer at the default printed all six records, aborted ones included, and three with read_committed.
-
How does a sink outside Kafka make a repeated write harmless?
It upserts by an event id, names its files by offset range, or stores its offsets together with its output.
-
What is a dual write, and how does the outbox pattern remove it?
Writing to a database and then producing to Kafka can succeed in one and fail in the other. With an outbox the service inserts the business row and an event row in one database transaction, and change data capture publishes the event.
-
How should a team state a pipeline's delivery guarantee?
In words that can be tested at the sink, naming the key that removes duplicates and how long that key is remembered. The phrase exactly once tells an on-call engineer nothing.
-
Why did the same six orders total 150 in three runs and 151 in one?
Where the micro-batches began and ended decided whether an order stamped 10:03, arriving after one stamped 10:16, was dropped or counted. Nothing failed and no row was malformed; the watermark rule decided.
-
When does Spark write a window's row in append mode?
Once, in the first micro-batch whose watermark, the latest event time minus the delay, reaches the window's end. With a 10-minute delay no window is written sooner than 10 minutes of event time after it ends.
-
Which watermark does Spark 4.2 use to judge a batch's rows, and which to close windows?
It judges the rows a batch reads against the previous batch's watermark and closes windows with the current one, so a row is dropped only when its window had already ended.
-
A row stamped 10:05 arrives when the watermark is 10:06. Does Spark drop it?
No, as long as its window, 10:05 to 10:10, is still open. A row behind the watermark whose window has not ended still counts, and a dashboard that calls it lost reports drops that never happened.
-
How do you test a windowed streaming query so it gives the same windows every time?
Fix where the batches begin and end, then assert on the windows written, the rows dropped and the windows still held.
-
What can a pipeline do with an order that arrives too late for its window?
Drop it and count the drop, send it to a side output, wait for it with a longer delay or allowed lateness, or restate the window from a batch recompute. The readers' needs decide which.
-
Why does a Flink batch run close a window that Spark still holds open?
In batch mode Flink closes every window when the input ends, so over all six orders it gives the 151 that a batch recompute publishes, while Spark waits for a later watermark to reach the last window's end.
-
What are the four parts of a Spark Structured Streaming query?
A source, a transformation written as for a batch DataFrame, a sink, and a checkpoint folder in which Spark records its progress.
-
After a restart, how did the streaming query avoid counting old files again?
Its checkpoint recorded what batches 0 and 1 had read, so run 2 read only the new file, as batch 2, and the table ended with 5 paid orders, each once. The query id stayed; only the run id changed.
-
When does Spark write offsets/n and commits/n in a streaming checkpoint?
offsets/n before batch n runs, commits/n once the batch's sink has returned. On restart a batch with both files is done, and one with no commit runs again with the same offsets.
-
Why must a streaming sink expect to receive the same batch twice?
A batch counts as done when its commit file exists, not when its output does, so a crash between writing and committing replays it.
-
What happens when a streaming query's checkpoint folder is deleted?
The next start is a brand-new query with no record of what the old one read.
-
How do no trigger, processingTime and availableNow differ in Structured Streaming?
With no trigger the next batch starts as soon as the last ends, until stopped. processingTime starts a batch at each interval and keeps running. availableNow reads everything available, then stops, so a scheduler can start it every hour.
-
Why did Spark 4.2's real-time mode refuse a query with a foreachBatch sink?
Its allowlist holds only stateless operators and the console, foreach and Kafka sinks, and its source must support the mode, as Kafka's does. It suits stateless queries from Kafka to Kafka.
-
What do Spark's append, update and complete output modes write?
Append, the default, writes only rows that will never change; update writes the rows a batch changed; complete writes the whole result every time.
-
What does a WATERMARK clause on ts with a 10-minute delay do in a Flink SQL table?
It makes ts the table's event time, with bounded out-of-orderness: the watermark is the largest timestamp seen so far minus 10 minutes.
-
Which columns does a Flink window table function add to each row?
window_start, window_end and window_time, which is the end minus 1 ms. Grouping by window_start and window_end then sums each window.
-
Over five orders, how many rows did a 5-minute TUMBLE and a 10-minute HOP sliding by 5 print?
TUMBLE placed each order in exactly one of four windows. HOP printed seven rows, with every order in two of its windows.
-
What do Flink's CUMULATE and SESSION window functions do?
CUMULATE grows a window from a fixed start, step by step, up to a maximum size. SESSION closes a window after a gap without rows, and runs in streaming mode only.
-
What does a streaming window query keep in Flink's keyed state?
The running SUM and COUNT of every open window, held until the watermark passes the window's end, in what Flink describes as an embedded key/value store partitioned with the stream.
-
Where do Flink's three state backends keep the state?
Hashmap, the default, as objects on the Java heap; RocksDB in an embedded database on the TaskManager's local disk; ForSt, still experimental, keeps its files on remote storage, S3 for example.
-
How do a Flink checkpoint and a savepoint differ?
Both snapshot the job's state by the same mechanism. A checkpoint is Flink's: taken on an interval, used to recover from a failure and removed on cancel unless retained. A savepoint is yours, taken before a planned change, and never deleted by Flink.
-
A Flink job crashes tonight and moves to a new version next week. Which snapshot serves each?
The crash needs a checkpoint, from which Flink restarts on its own. The move needs a savepoint you trigger first and start the new version from; its canonical format also survives a change of state backend.
-
What is a lambda architecture, and what is its lasting cost?
A fast speed job beside an exact nightly batch job, with their results joined at query time. Each rule, such as what counts as a late delivery, lives in two programs that must stay identical.
-
How does a kappa architecture apply a changed rule to past events?
It keeps one stream job. A second version of it starts at the oldest retained offset and writes a new table, readers switch once it has caught up, and the old job and table are removed.
-
What does a kappa architecture need from its Kafka topic?
Events kept for as long as a fix may reach back. Kafka keeps 7 days unless retention.ms says otherwise, so a 30-day recompute needs that setting raised well before the fix is written.
-
How does lakehouse streaming serve a live board and a finance report from one place?
A landing job appends each minute's events to a table in an open format such as Iceberg or Delta Lake, and the board, the morning report and any rule fix all run SQL on that one table.
-
What does lakehouse streaming cost in freshness and upkeep?
The board was about 95 seconds old, and the table took 1,440 commits a day, whose small files and snapshots need a daily upkeep job.
-
For the same 2-minute board, what separates lambda, kappa and lakehouse streaming?
Not the machines: all three met the target on 2,880 to 2,895 core-hours a month. They differ in how many copies of the rule exist, how much history is kept and whether two readers get the same rows.
-
Which engines can run a streaming design, and where does each run?
Kafka Streams is a Java library inside your own services; Apache Flink runs jobs on a cluster of its own; Spark Structured Streaming runs a series of small batch jobs; ksqlDB and RisingWave let a team write stream jobs in SQL.
-
An orders table holds 6 orders, but its data folder holds 10 rows in 4 files. Why?
Each write left new files, and the folder also keeps files that only older snapshots list. The table format's metadata says which files the current snapshot reads, so a scan through it returned 6 rows.
-
Why must a reader not simply read every Parquet file in a table's data folder?
It counts rows of old versions too, orders 1001 to 1004 twice in the lesson, and a read during a write sees a half-made mix. Read by name from the catalogue or from a snapshot.
-
What does one Apache Iceberg commit write, and what does a Delta Lake commit write instead?
Iceberg writes data files, a manifest, a manifest list and a metadata file, then swaps one pointer. Delta Lake writes the next numbered file of its log.
-
What happens when two writers commit to an open table at the same time?
The swap is a condition checked at the last moment: it succeeds only if the table is still at the version the writer read, and the writer that loses re-reads the table and tries again.
-
How do Iceberg, Delta Lake, Hudi and DuckLake differ in what they are built around?
Iceberg serves many engines, Delta Lake centres on Spark, Hudi is built for streaming upserts, and DuckLake keeps its metadata in an SQL database.
-
What does a catalogue do for an open table format?
It maps each table name to its current metadata file and performs the swap a commit needs. The Hive metastore, Iceberg REST servers such as Apache Polaris, Unity Catalog and Project Nessie are open options.
-
What decides the choice of table format as much as its features?
Which engines support it: Iceberg 1.12.0 had no Spark 4.2 runtime yet, while Delta 4.4 builds for it.
-
Monday's report counted 2 orders; Tuesday the same query for Monday counted 3. How does Iceberg show which count the report saw?
Each write commits a new snapshot and leaves earlier ones readable by id, so a scan at the first snapshot returns the 2 orders Monday's report read.
-
What makes up an Iceberg table on disk and in the catalogue?
A catalogue row naming the current metadata file; metadata files holding the schema, partition spec and snapshots; a manifest list and manifests that list the data files with row counts and column bounds; and the Parquet data.
-
When is PyIceberg's SQL catalogue on SQLite the right choice?
For exploration and development only, because SQLite does not handle concurrent writers. Shared tables use PostgreSQL behind the same catalogue, or a REST catalogue.
-
What did two appends of 2 and then 4 orders leave in the Iceberg table's snapshots?
Two snapshots, the second naming the first as its parent, with added-records 2 then 4 and totals 2 then 6. The first still lists 1 data file and the current one 2.
-
What are time travel and rollback in Iceberg, and what do both need?
Time travel reads the table at an earlier snapshot; rollback makes an earlier snapshot current again. Both need that snapshot to exist, because maintenance expires old snapshots and then deletes files only they listed.
-
When does Iceberg fit better than Delta Lake or DuckLake?
For tables that several engines share and layouts that change: the project ships runtimes for Spark and Flink, and engines such as Trino and DuckDB build in their own Iceberg support.
-
How does a Delta Lake table record its commits?
In a _delta_log folder inside the table's own folder: one numbered JSON file per commit, listing actions that add or remove data files and set the schema or protocol. Creating the next numbered file is the commit.
-
Why does a second write_deltalake call to the same folder fail without a mode?
The default mode is error, recorded in the history as ErrorIfExists, so it refuses a table that already exists. The next day's orders need mode=append.
-
Where does DuckLake keep a table's versions?
In one SQL catalogue database, which can be a single file, with the data in Parquet. Each catalogue transaction is one snapshot, so a commit is exactly as atomic as that database.
-
Why did a DuckLake table with four orders, an update and a delete hold 0 Parquet files?
Writes that small stayed as rows of the catalogue database, which DuckLake calls data inlining; the snapshots' changes read inlined_insert and inlined_delete.
-
Which catalogue database should a DuckLake setup use?
By DuckLake's guide: a DuckDB file for a single client, a SQLite file for several processes on one machine, and PostgreSQL for a team writing from different machines.
-
Which team fits Delta Lake, DuckLake or Iceberg?
Spark and Python jobs with no catalogue to run point to Delta Lake, whose log travels with the folder; a DuckDB team that can keep a database running points to DuckLake; tables many engines share point to Iceberg.
-
A streaming job left an Iceberg table with 180,000 files of about 2 MB. What does that do, and what fixes it?
Scans spend their time listing and opening files, and planning slows as the manifests grow. Compaction packs them into about 1,440 files of 250 MB, 125 times fewer, and commits a snapshot listing them.
-
Which three routine jobs keep an open table fast and small?
Compaction packs small files, snapshot expiry trims metadata and lets old files go, and orphan-file removal sweeps files that no snapshot ever referenced.
-
Does compaction delete the small files it replaced?
No. Older snapshots still reference them, so time travel keeps working; snapshot expiry and orphan-file removal remove them later.
-
What are the default retentions for Iceberg orphan removal and delta-rs VACUUM, and why?
Iceberg's orphan removal defaults to three days, since an interval shorter than the longest write can corrupt a table. delta-rs VACUUM keeps seven days, because an active reader of an older version must still find its files.
-
How should a table's retention window be chosen?
From the reader furthest behind and the longest an erasure may wait to be complete: expiry sets the floor of time travel, and the right to erasure pulls the other way.
-
Which open catalogue fits which platform?
Iceberg REST, such as Apache Polaris, for a plain Iceberg platform; Unity Catalog for mixed formats under one governance; Nessie for branch-like isolation; the Hive metastore for backward compatibility.
-
Which six data quality dimensions does one night of defective orders break?
Validity, completeness, uniqueness, consistency, accuracy and timeliness. A pipeline that only checks that the file arrived publishes all six defects, from an amount below zero to a load an hour and forty minutes late.
-
When does a data quality dimension become useful?
Once it is a check whose answer is a number, such as the rows that break a rule or the distance between a figure and a trusted source. Zero means the rule holds; anything else names what to fix.
-
An order of 18,000 paise passed every column rule, yet the day was 3,000 paise too high. Which dimension caught it?
Accuracy. The value was a positive whole number, so validity accepted it; only a comparison with the provider's figure showed the error.
-
What does each kind of quality check need to compare against?
Validity and uniqueness need only the table they guard; consistency needs a second value about the same thing, often in another table; accuracy needs a source the pipeline does not produce, such as the provider's settlement report.
-
A revenue mart matches the staging rows it came from. Does that prove the revenue is accurate?
No, it shows only consistency between two layers. Both come from the same rows, so they can agree and both be wrong; accuracy needs something the pipeline did not write, such as a bank statement.
-
Where should each kind of data quality check run?
At the first place that can see its defect: single rows at ingestion, keys and relationships while the models are built, totals and freshness just before people read.
-
What should be settled for each check before it first fails?
Its response, owner and runbook line: fail the run when the whole table would be wrong, quarantine rows that break a rule on their own, and warn when the data is right but late or drifting.
-
A fee rule switched from nearest paisa to always rounding down. Why did no type, range or format check notice?
1,251 of the week's 4,293 fees came out a paisa short, but each was still a positive whole number of paise, so every such check accepted it. A unit test with a case on the rounding edge caught it.
-
What does each of a pipeline's unit, schema and data tests look at, and when?
A unit test calls a function on values written in the test and reads no table, on every commit. A schema test holds each incoming batch to its columns, types and allowed values before it loads. A data test queries the stored tables after each load.
-
Which of six parametrised fee cases failed on the rounded-down copy, and how?
One: the 2,333 m trip, whose fee is 1,749.75 paise, failed with assert 1749 == 1750. The other five passed on the broken copy as well.
-
What is mutation testing?
Planting changes in the code on purpose, such as a different rounding rule, to see which of them a test suite notices.
-
Which tool covers which testing layer in the lesson?
pytest runs unit tests, pandera checks DataFrames against a schema, Great Expectations checks batches against expectations, and Soda Core checks warehouse tables.
-
Where in the test pyramid does each check belong?
In the cheapest layer that can see its failure. A failure low in the pyramid names one function and one input; a data test sees real rows, but only once they have loaded.
-
A release began sending amounts in paise instead of rupees. Why did every schema check accept it?
A schema check compares names and types, and the column was still a whole number, so a 980-rupee day showed as 98,000 rupees.
-
What does a data contract between producers and consumers write down?
The dataset's fields and types, what each value means and in which unit, its keys and quality rules, how fresh it must be, and who owns it, each line checked by some program.
-
A contract range of 10 to 3,000 rupees refused five events after the unit change. Why did order 1706 still pass?
Its 2,500 paise lies inside the range, so a range limits the damage of a unit change without seeing it. The unit itself belongs in the contract, where only a new version can change it.
-
What is the Open Data Contract Standard, and what does validating a file against it prove?
An open YAML format for contracts, kept by the Bitol project of the LF AI & Data Foundation under Apache 2.0, with a JSON Schema for the file. A valid file proves its form only, not that the events keep the contract.
-
What does each side of a data contract check?
The producer runs the contract's rules on sample events in CI and refuses to deploy a release that breaks them. Consumers validate what arrives at ingestion, set broken events aside, hold a day when too many break and tell the owner.
-
How does semantic versioning apply to a data contract?
A breaking change takes a new major version and a backward-compatible change, such as an optional field, a new minor one. A breaking change then ships with both versions side by side until a deprecation window ends.
-
A table loaded on time with every row-level test green, yet held 64 % of a normal Tuesday. What caught it?
Only a volume check against the median of earlier Tuesdays: one canteen's tablets had stopped syncing, so nothing in the rows that arrived was wrong.
-
What do a freshness check and a volume check each ask?
Freshness asks how long ago the table last loaded successfully; volume asks how much the load held. In the replay each caught one planted incident and missed the other.
-
Which five signals of data observability cover most incidents?
Freshness, volume, schema and distribution, which describe a table, and lineage, which names everything the table feeds.
-
What are schema drift and distribution drift?
Schema drift is a column that disappears or changes type; distribution drift is a change in the values, such as a jump in empty amounts. Both can leave freshness and volume looking perfect.
-
What does data lineage add to an alert on one table?
The list of marts and dashboards built from it and the teams that own them, the blast radius of the bad load, so you know whom to tell and what to rerun.
-
A table's freshness check is green every morning. Why can it still be wrong?
Freshness says only when the table last loaded, not what the load held: a source can be missing, every row doubled, a column renamed or a column empty. Watch volume per weekday, the schema and the null rates readers rely on.
-
Which five habits protect a pipeline's credentials and data in systems you own?
A narrow identity for each pipeline, secrets fetched at run time, bound parameters with an allowlist for identifiers, services bound to private networks, and a log of who reads the data.
-
How should a pipeline's database access be granted?
Through a login role of its own with the narrowest grants its job needs, with people reaching data through a group role, and ALTER DEFAULT PRIVILEGES so tables a later migration adds get the same grants.
-
Why is an environment variable a weak place for a database password?
Every subprocess can read it, container inspect commands show it, Airflow does not mask it when a DAG prints the environment, and a stack trace can echo it. Ask the secrets backend at run time, inside the task that needs it.
-
What should a loader write to its logs about the credential it uses?
The credential's name or identifier, never its value, with a redacting filter on every logger as a safety net.
-
How should a pipeline put input values and table names into SQL?
Bind every value with the driver's placeholder, check any identifier that must vary against a short allowlist of known-good names, and let a static scanner in CI fail the build on an f-string passed to execute().
-
Why is escaping quotes in input no substitute for bound parameters?
It misses cases: a numeric context has no quotes to escape, and the rules differ by database and character set. OWASP strongly discourages it as fragile, and binding is the shorter code anyway.
-
Where should a pipeline's data services be reachable from?
From private networks only, with a public endpoint just where the design needs one, and a date on each such endpoint by which someone checks it is still needed.
-
What should a data platform's access log capture, and how is it kept?
Who read the critical datasets, not only who changed the configuration, on an append-only store with a stated retention period, and examined on a schedule with queries you have written and tested.
-
Of the customers table's 12 columns, how many were personal, and how many did the analyst extract keep unchanged?
9 were personal, and the extract kept 3 columns as they were: the plan, the order count and the city.
-
What counts as personal data in a pipeline?
Data that identifies someone on its own, such as a name, or once combined with other columns, such as a birth date beside a delivery point. It also hides in notes people type, such as a delivery note, where no column name warns of it.
-
What should happen to every column before a copy leaves the source?
It gets one of four classes, public, internal, confidential or personal, and data minimisation keeps only what the copy's purpose needs: a report of orders per city needs the city, never the name.
-
Which four treatments can a personal column get in an analyst extract?
Dropping it; masking part of the value; replacing it with a pseudonym that stays the same for one person everywhere, so tables still join; or aggregating it into a group, such as an age band.
-
How do keyed hashing and tokenisation each make a stand-in for an identifier?
Keyed hashing computes it from the value and a secret key, for example with HMAC-SHA-256, so only a key holder can compute it again. Tokenisation draws a random token and keeps each pair in a guarded vault.
-
What is a masked value good for, and why is it a poor join key?
It suits a value a person reads, such as support checking the last digits of a mobile number with a caller, but last digits repeat across customers.
-
Why could the city stay whole in the analyst extract when it is personal data?
Many customers share each city value, so it points at nobody on its own; every other personal column was tokenised, masked, grouped or dropped.
-
A DELETE removed a customer's 3 orders from the curated table. Why was her erasure not finished?
Nine of her rows were still on disk in 5 other files, two raw batches, a BI extract and two nightly snapshots, so a rebuild or a restore would bring her back.
-
What does a catalogue entry need for each dataset?
An owner, a classification, documentation and lineage, so that people and jobs can find every dataset and every copy of it.
-
Is an extract built from personal data still personal?
Yes, unless its catalogue entry records what was removed: lineage carries the class forward to every copy.
-
How do central and federated data governance differ?
Under central governance one data team owns every catalogue entry. Under federated governance, the model of a data mesh, each domain team owns its own datasets under shared rules.
-
Where can a person's rows survive a DELETE on the curated table?
In raw landing files until rewritten, table-format snapshots until expired, caches until flushed, extracts until rebuilt and backups until they age out.
-
What does a DELETE without a version id do in a versioned S3 bucket?
It only adds a delete marker: reads answer 404 Not Found, but the old version stays stored until it is deleted by its version id or a lifecycle rule expires noncurrent versions.
-
What makes a row due for deletion, and what does the deletion log keep?
A retention period that ends, or a person's request; one job serves both. The log keeps the request, the copies searched and the day each was clear, never the deleted rows.
-
A slim CI run ends with PASS=1 of 1. What has it not checked?
Whether the numbers moved. The changed model built without an error, but a diff of the key metric showed delivered revenue 9.20 % higher on the branch, a change its author never meant to ship.
-
Which three data environments share one project, and where may a pull request write?
A developer's own schema, a short-lived CI schema or branch, and the production warehouse that readers point at. A pull request never writes to production.
-
In what order does a CI job for data run its checks?
Cheap checks first (lint, unit tests and a workflow linter), then a slim dbt build with --defer, then a data diff on key metrics. The build asks whether the project compiled; the diff asks whether the numbers moved.
-
Why can a mart with no data test of its own pass CI while its number is wrong?
It passes as soon as its SQL runs without an error. Only a diff of its key metric shows where the number went.
-
What does write-audit-publish do with a batch that fails its audits?
It skips the one atomic step that moves a staged batch into the live table, so the bad batch is a quiet log line rather than a page every reader sees.
-
Why does a CI workflow file need a linter of its own?
The workflow file is code too: a linter catches a wrong key and an untrusted github.event expression before the workflow is pushed, and an environment variable keeps such an expression away from the shell.
-
What makes a data stack repeatable?
Pinned toolchains, locked packages, services bound to a loopback address, and a warehouse described by an infrastructure-as-code plan that is read first and applied second.
-
Which four meters make up most of a cloud data bill?
Bytes stored, bytes processed, compute time and bytes sent out of a region. A lever that cuts one barely moves the others, so first name the meter that dominates the workload.
-
What does a scan-priced warehouse reward, and what does a time-priced one reward?
A scan-priced warehouse, such as BigQuery on-demand, rewards reading less: partitioned, columnar tables and fewer columns per query. A credit-priced one, such as Snowflake, rewards running less: short auto-suspend and sizes that fit the work.
-
How does BigQuery on-demand count the bytes a query processes?
As logical bytes, each value sized by its data type however well the files compress, with at least 10 MB billed for every query.
-
What should a monthly cost estimate for a data platform start from?
List prices copied from each vendor's own pricing page, with the region named, and the shape of the workload the team already runs. It is a baseline for comparing workloads, never a quote.
-
Which four levers cut a cloud data bill?
Layout cuts the bytes each query reads, scheduling cuts the runs each day, right-sizing cuts idle compute, and guardrails are set before the first run.
-
How can scheduling lower a BigQuery dashboard's bill?
A dashboard that refreshes only when its data changes runs fewer queries, and BigQuery charges nothing for a query its result cache answers.
-
What does each cost guardrail actually do?
A byte cap refuses a query estimated over its limit, a resource monitor can suspend warehouses that reach a credit quota, a budget alert only sends a message, and a cost allocation tag records who owns each part of the bill.
-
Two dashboards on one table showed 1,406 and 1,284 delivered orders. What caused the gap?
Each dashboard wrote its own filter for a delivered order and the two had drifted apart. The 122 orders are a difference of definition, not a time-zone glitch or a late load.
-
What are the four parts of a metric definition in a semantic layer?
A measure, the filter that picks its rows, the time dimension that places each row in a day, and the grains a reader may cut the number by.
-
Why generate every dashboard's SQL from one metric definition?
The filter then lives in one place, so for a count the day and canteen breakdowns agree by construction, and every dashboard asking for the metric gets the same number.
-
Which two engines carry a semantic layer into production?
dbt's MetricFlow, a SQL generator that reads semantic models and metrics from a dbt project, and Spark 4.2.0's metric views, created with CREATE VIEW ... WITH METRICS.
-
How do a view, a built table, a materialized view and a BI extract compare for serving a metric?
A view is as fresh as the warehouse but costs compute on every read; a built table is fast but only as fresh as its last build; a materialized view stays fresh at close to table speed; a cached extract is fastest, stalest and hardest to govern.
-
What are the two safe ways to hand modelled numbers to other systems?
Reverse ETL, which writes modelled rows into an operational tool such as a CRM keyed by a stable sync key, and a small read-only data API with its own keys, page sizes and limits. Neither hands out a warehouse login.
-
What makes a reverse-ETL sync and a partner data API safe?
A safe sync survives a restart, slows down when the tool pushes back and skips customers without consent. A safe API refuses unknown keys, never pastes a value into its SQL and caps what each partner reads.
-
A delivery-time model looked right offline but missed at the live lunch rush. What did its training join do?
It took each canteen's newest queue reading, so 10 of 12 training rows held a value measured after their own order, and the model learnt a number only the future knew.
-
What is point-in-time correctness, and what is its breach called?
The rule that every value attached to an event must have been computable before that event. A trained row that breaks it holds feature leakage.
-
Why did a known-at join, not an ASOF join, give a training table without leaks?
The ASOF join matched on event time alone and still left 2 rows with a reading that reached the warehouse after the order. The known-at join keeps a reading only when both measured_at and recorded_at were at or before the order.
-
Why can a DuckDB ASOF join not test measured_at and recorded_at together?
It takes a single inequality, and DuckDB refused the second with Multiple ASOF JOIN inequalities. Two plain inequalities and a window function that keeps each order's newest known reading do the job.
-
What is training-serving skew, and where does it hide?
A gap between the number a training job wrote and the number a live scorer read for the same event. It hides in feature code computed by a batch path and an online path that look alike.
-
What must every chunk of a retrieval index keep, and why?
A trace back to the page it came from, so that the pipeline can update that page or forget it on request.
-
How are machine-extracted fields checked before anyone relies on them?
Malformed outputs go to quarantine, each with its reason, and a person compares a seeded sample of the remaining outputs with the original scans.
-
In what order does the design method spend a twenty-minute data platform question?
Readers and their targets; volumes and the busiest minute; a flow from sources to serving; batch or stream for each path; the key tables; quality, security, cost and operations; and last the trade-offs and growth at ten times the volume.
-
Why do readers and targets come before any technology in a design answer?
Every later choice reads back to one of them. An answer that spends its first five minutes naming engines has nothing left to choose them by.
-
How should a design round's capacity estimate be given?
As a formula with every input said out loud, sized by the busiest minute rather than the average: 31.1 events a second for Campus Bite.
-
What should a data platform flow sketch show besides the stages?
Each stage labelled by its purpose, from sources on the left to readers on the right, with a replay zone, a dead-letter path and an idempotent reload marked on it.
-
How is batch, micro-batch or stream chosen in a platform design?
Path by path, from the freshness target of that path's reader; one or two key tables are then described by grain, partition key and reader.
-
How much time do quality, security, cost and operations get in a design answer?
One short sentence each, with an owner. The trade-offs then name the designs not taken and what breaks first at ten times the volume.
-
How are the twenty minutes of a design round split, and how does the answer end?
Two minutes for readers and three for every other step, ending with the answer held against a rubric by level.
-
Why does the sale's clickstream design need two paths over one stream of taps?
Marketing's funnel within 5 minutes has to be counted while some phones are still offline, and finance's exact day by 08:00 has to wait for those phones, so no single path can serve both.
-
At twelve times a normal day, what peak and volume did the sale's estimator give?
66,667 events a second, 46.7 MB of JSON a second, and about 1 TB of raw events over the two days. The peak sizes the collector, Kafka and the streaming job; the volume sizes storage.
-
What is the first design for the sale's live funnel and its exact numbers?
One Kafka topic feeding a streaming job for marketing, and a raw lakehouse table that a nightly batch reads for finance.
-
Why does the live funnel stream while the finance numbers run as a nightly batch?
Five minutes rules out a nightly job, and a sale day is complete only once offline phones have reported.
-
How does the clickstream design form sessions and remove copies and scripts?
A session window collects one phone's taps and ends once that phone has been silent for a chosen gap; the event id removes taps sent twice; a rate rule removes scripts.
-
How does the clickstream design deal with late taps and small files?
A watermark limits how long the figure for one live minute can still move, nightly reruns add what offline phones sent later, and compaction rewrites the streaming writer's many small files into a few large ones.
-
Which three readers pull the payments platform in different directions?
Finance wants a settlement that matches the bank to the paisa, risk wants a payer's counts within seconds, and audit wants to see months later what a merchant looked like, so nothing may be overwritten.
-
At 900 payments a second, what do the payments ledger and its outbox write?
157,386,240 ledger rows a day, about 26.4 GiB, and 91.2 GiB of outbox; the busiest second writes 4,918 ledger rows and 7,290 outbox events. The day decides storage, the busiest second partitions and consumers.
-
Which table is the system of record in the payments design?
The payments ledger, kept in whole paise with an idempotency key on every write; every other table is derived from it.
-
Which six decisions decide whether the payments sketch survives a month?
Whole paise with one key per request, an outbox row in the payment's own transaction, point-in-time fraud features, merchants kept as versions, a reconciliation that names each break, and the localisation, retention and audit duties.
-
What should the reconciliation against the bank's file report?
Each break by payment, paise and cause, rather than a single total.
-
Which two promises does the food-delivery operations design write down?
The city team gets each area's numbers within about a minute and acts on figures that may still move; the next-morning reports count every event once and must match what the screen showed.
-
At the dinner peak, how do the rider pings compare with the orders?
Riders send 1,125 pings each second while customers place 3 orders, so pings outnumber orders 375 to 1, and the question is what the pipeline keeps about the pings and for how long.
-
With a 10-minute bound, how much can the stream-to-stream join of pings and orders hold?
At most 744,480 rows, about 57 MiB, if both streams are kept for the whole bound.
-
Which five decisions does the food-delivery sketch leave open?
A join that keeps a known amount of state, watermarks that decide when a minute is published, a key made from latitude and longitude, one shared rule for a late delivery, and the failures worth an alert.
-
A purchase register holds 48,000 lines and the suppliers' statements name 47,920. What must the warehouse explain?
The 80-line gap, line by line: an amount that drifted, a registration number spelled differently, a date booked apart, or an invoice one side never saw.
-
Which targets follow from the reconciliation warehouse's readers?
Amounts in paise for every tax component, every decision tagged with the rule that made it, and the whole month rebuildable on demand.
-
Why match invoices with a keyed join instead of comparing every line with every other?
An all-against-all compare examines 48,000 times as many pairs as a join on supplier, document number and financial year. The key, not the compare, carries the matching.
-
Why is the invoice reconciliation designed as a batch rather than a stream?
Both feeds, the purchase register and the suppliers' statements, arrive once a month, so the design is batch.
-
What is the Invoice Reference Number of an Indian e-invoice?
The unique number the Invoice Registration Portal returns for a business-to-business invoice: a SHA-256 hash over the supplier's GSTIN, the invoice's number and type, and the financial year of its date.
-
Which five skills can a data engineering interview loop test in one week?
A window query, a skewed Spark join, a fact table's grain, a streaming window and a platform design.
-
What does a portfolio that a hiring team trusts contain?
One project with tests that run, documentation a stranger can follow and an account of how the project is operated.
-
Two items tie for second place in a canteen's top two by revenue. How many rows should that canteen get?
Three, because both tied items take place 2. A window function numbers each canteen's items, and QUALIFY keeps places 1 and 2.
-
How do row_number(), rank() and dense_rank() treat two tied rows?
row_number() gives every row its own number, so one of the tied rows is cut and which one is undefined. rank() shares the number and then skips, 1, 2, 2, 4; dense_rank() shares it without a gap, 1, 2, 2, 3.
-
How does the SQL drill spend its six minutes on one question?
One minute to name the edge case, such as a tie at second place, four to write the query and one to check the result.