Pipelines — SWE/DE — Thomas To

Each topic follows the North Star order: what the decision is, why first principles favor one answer, and how to apply it.

ETL vs ELT: decide where transformation runs

ETL transforms before loading; ELT loads raw data first and transforms inside the destination. The choice follows where compute is cheap and what history you must keep.

What are you trying to do?

Place transformation before or after the load by asking where compute is cheapest and whether raw history must survive.

Why are you doing it?

ETL came from an era when warehouse storage and compute were bought together and were expensive, so you transformed outside and loaded only curated rows. Cloud warehouses and lakehouses separate cheap, durable storage from elastic compute, so landing raw data first is inexpensive, keeps the original for audit and replay, and lets you rebuild every derived table when business logic changes. ELT pays for that flexibility in two places: raw (possibly sensitive) data now sits in the destination, so access control and masking move later in the flow, and transformation compute is billed where the data lives. ETL still wins when data must be filtered, masked, or reduced before it may legally or economically land.

ETL — transform before load

Extract
Read from the source
Transform
Clean and reshape in an external engine
Load curated
Only the finished tables arrive

ELT — load first, transform in place

Extract
Read from the source
Load raw
Immutable copy kept for replay
Transform
SQL runs where the data lives
Serve
Curated tables for consumers

The same four verbs in a different order. The order decides where compute is paid for and whether raw data is kept.

Data Pipelines Pocket Reference Fundamentals of Data Engineering Analytics Engineering with SQL and dbt Data Engineering Design Patterns

How are you going to do it?

Default to ELT when storage is cheap and the destination scales compute elastically: land raw, then transform with versioned SQL.
Choose ETL (or an in-flight transform) when data must be minimized, masked, or validated before it is allowed to land.
Keep the raw layer immutable. A logic change then becomes a re-run over retained data instead of a re-extraction from a source that may have changed.

ETL and ELT compared
Question	ETL	ELT
Where transformation runs	External engine before the destination	Inside the warehouse or lakehouse
Raw history	Often discarded after the transform	Kept as an immutable raw layer
When logic changes	Re-extract from the source, if it still has the data	Re-run SQL over retained raw data
Governance exposure	Sensitive fields can be dropped before landing	Raw sensitive data lands, so masking and access control move later
Fits when	Data must be reduced or masked before landing	Storage is cheap and compute is elastic

Batch vs streaming: buy latency only when a decision needs it

Streaming lowers latency and raises complexity and cost. Most "real-time" requirements turn out to mean "fresh enough for the decision".

What are you trying to do?

Pick batch, micro-batch, or streaming from how quickly a consumer must act, not from what sounds modern.

Why are you doing it?

Batch processes a bounded, complete slice of data, so it is simple to reason about, easy to re-run, and cheap because compute can be released between runs. Streaming processes unbounded data continuously, which forces the engine to handle out-of-order events (event time differs from processing time), keep state across events, checkpoint it for recovery, and run always-on. Each step toward lower latency buys freshness with operational complexity and continuous cost. The right latency is set by when a consumer must act: a nightly finance report gains nothing from sub-second data, while fraud screening does. Lambda architectures run a batch and a streaming path side by side and pay for two code bases; Kappa architectures replay a durable log through one streaming path.

Batch — minutes to hours

Accumulate
Collect a bounded window of data
Run job
Process the whole window at once
Publish
A complete, re-runnable result

Micro-batch — seconds to minutes

Short window
Collect a few seconds of events
Run small job
Same engine, tiny batches
Append result
Frequent incremental output

Streaming — milliseconds to seconds

Event arrives
Possibly late or out of order
Update state
Windows keyed by event time; checkpointed
Emit continuously
Watermark decides when a window closes

Processing models ordered by latency. Each step to the right trades simplicity and cost for freshness.

Source: Apache Flink — Timely Stream Processing Streaming Systems Designing Data-Intensive Applications (2nd ed.)Kafka: The Definitive Guide (2nd ed.)Stream Processing with Apache Flink

How are you going to do it?

Ask the consumer what decision the data drives and how stale it can be, then pick the slowest option that satisfies it.
Start with batch or micro-batch; move to streaming only where the freshness requirement is real and the team can operate state and checkpoints.
If you stream, design for late and out-of-order events up front: use event time, a watermark policy, and an idempotent sink.

Batch, micro-batch, and streaming compared
Dimension	Batch	Micro-batch	Streaming
Typical latency	Minutes to hours	Seconds to minutes	Milliseconds to seconds
Completeness	Whole window, easy to verify	Small windows, late data needs a policy	Always partial until a watermark closes the window
Operational complexity	Low: stateless between runs	Medium: scheduler plus state across batches	High: state, checkpoints, ordering, backpressure
Cost shape	Compute released between runs	Frequent short runs	Compute always on
Re-run after a bug	Re-run the window	Re-run affected batches	Replay the log from an offset

Idempotent loads: running twice must equal running once

Retries are inevitable, so a load must leave the same final state however many times it runs.

What are you trying to do?

Design every write so a retry or backfill cannot create duplicates or double counts.

Why are you doing it?

Pipelines fail midway and orchestrators retry them, so each task effectively runs at least once. A write that appends is not safe under that: a retry inserts the same rows again. A function is idempotent when applying it twice gives the same result as applying it once, so the write must depend only on the input window, never on how many attempts happened. That needs two things: a deterministic input (a logical date or data interval, not "now"), and a write that replaces or merges by key instead of appending blindly. Overwriting a partition atomically, deleting and inserting a slice inside one transaction, and merging on a stable key all qualify.

Append — not idempotent

Run 1
Insert 3 rows → table has 3
Retry
Insert the same 3 rows again
6 rows
Duplicates; counts are wrong

Delete the partition, then insert — idempotent

Run 1
Delete slice, insert 3 → 3 rows
Retry
Delete slice, insert same 3
3 rows
Same final state

Merge on a stable key — idempotent

Run 1
Match on key: insert 3 → 3 rows
Retry
Keys match: update in place
3 rows
Same final state

The same retry under three write strategies. Only the last two are safe to re-run.

Data Pipelines with Apache Airflow (2nd ed.)Data Pipelines Pocket Reference Designing Data-Intensive Applications (2nd ed.)Data Engineering Design Patterns

How are you going to do it?

Parameterize each run by the data interval it owns, so a retry reads exactly the same input.
Write by replacing the interval (partition overwrite or delete-then-insert in one transaction) or by upserting on a stable business key.
Test it: run the same interval twice in staging and assert the row counts and checksums are identical.

Write strategies and what they need to be safe to re-run
Strategy	Run twice yields	Needs	Watch out for
Blind append	Duplicate rows	Nothing, which is the problem	Silent double counting after any retry
Overwrite the partition	Same rows	Data partitioned by the run interval; atomic replace	Late data landing in an old partition needs a re-run of that interval
Delete then insert, one transaction	Same rows	A key that identifies the slice (batch id or interval)	Both statements must commit together
Merge or upsert on a key	Same rows	A stable, unique business or event key	Choosing a key that is not truly unique silently drops rows

Orchestration: pipelines are graphs with retries and intervals

An orchestrator tracks task state per data interval, runs tasks in dependency order, retries transient failures, and re-runs history on demand.

What are you trying to do?

Model each pipeline as a directed acyclic graph and make every task safe to retry and backfill.

Why are you doing it?

Tasks depend on each other, so the work forms a graph, and the graph must be acyclic or no valid run order exists; a topological sort of a DAG always gives one. The orchestrator records state for each (task, data interval) pair rather than for "the pipeline", which is what makes selective re-runs and backfills possible: ask for the missing intervals and it runs only those. Failures split into transient (a timeout, a throttled API) and permanent (a bug, bad data). Retries with exponential backoff absorb the first kind, and they are only safe when tasks are idempotent. Downstream tasks of a permanent failure are skipped and an alert fires, ideally on a missed freshness deadline, not just on the failure.

Normal run for one interval

Extract
Reads interval [start, end)
Validate
Schema and row-count checks
Transform
Runs after validate succeeds
Publish
Atomic swap into the serving table

Failure path

Task fails
Timeout or throttled source
Retry with backoff
Wait longer each attempt; safe if idempotent
Retries exhausted
Downstream tasks are skipped
Alert
Page on the missed freshness deadline

Backfill

Find missing intervals
State is stored per interval
Run each interval
Same tasks, different parameters
Verify
Counts match; re-running changes nothing

A run is a walk over the graph for one data interval. Retries and backfills reuse the same tasks.

Source: Apache Airflow — DAG Runs Data Pipelines with Apache Airflow (2nd ed.)Fundamentals of Data Engineering

How are you going to do it?

Express dependencies explicitly in the graph; do not rely on "this usually finishes before that starts".
Make every task idempotent and parameterized by its data interval, then configure retries with backoff and a timeout.
Alert on missed deadlines (a freshness SLA), and backfill by interval range instead of re-running everything.

Data modeling: declare the grain, then everything follows

Dimensional modeling picks a business process and a grain, then splits data into additive facts and descriptive dimensions that preserve history.

What are you trying to do?

Model analytical data around a declared grain so measures aggregate correctly and history is kept.

Why are you doing it?

Analysts ask the same questions repeatedly (totals by customer, product, and month), and the cheapest way to answer them is to store measurements once at a fixed grain, such as one row per order line, and describe them through small dimension tables. The grain is the contract: every fact row means the same thing, so sums and counts are valid. A star schema accepts some redundancy in dimensions to avoid deep join chains, and columnar engines scan the narrow fact table fast. Dimension attributes change over time, so a type 2 slowly changing dimension closes the old row and inserts a new one with validity dates and a surrogate key, letting old facts keep joining to the attributes true at the time. Wide denormalized tables trade flexibility for simplicity, and Data Vault trades query simplicity for auditability and parallel loading.

Dimensional design, in order

Business process
For example, order fulfillment
Declare the grain
One row per order line
Dimensions
Customer, product, date: who, what, when
Facts
Additive measures: quantity, amount

Type 2 slowly changing dimension

Attribute changes
Customer moves to a new region
Close the old row
Set valid_to; mark not current
Insert a new row
New surrogate key, valid_from, current
Facts join by surrogate key
Old facts keep the old attributes

Design order for a star schema, and the row mechanics that preserve dimension history.

Source: Kimball Group — Dimensional Modeling Techniques The Data Warehouse Toolkit (3rd ed.)Fundamentals of Data Engineering Analytics Engineering with SQL and dbt

How are you going to do it?

Follow the four decisions in order: pick the business process, declare the grain, choose the dimensions, then identify the facts.
Use surrogate keys for dimensions and keep natural keys as attributes, so history rows can coexist.
Use type 2 for attributes whose history matters to reporting; overwrite (type 1) only when history has no analytical value.

Warehouse, lake, lakehouse: add atomic commits to cheap files

Object storage is cheap and durable but cannot update many files atomically. A table format adds a metadata layer that can.

What are you trying to do?

Understand what a lakehouse adds to a data lake, and how layered (medallion) tables use it.

Why are you doing it?

A warehouse couples storage and compute and enforces a schema on write, which gives reliability and speed at a higher price. A data lake stores open files in object storage cheaply with schema on read, but object stores update one object at a time, so a job that writes many files can fail halfway and expose partial data. A lakehouse keeps the open files and adds a table format: a transaction log or manifest tree that lists which data files make up each version of the table. A commit writes new data files first, then atomically publishes one metadata entry that points at them; readers resolve a snapshot, so they see the old version or the new one and never a mixture. The same metadata enables time travel, schema evolution, and merge operations. The costs are small files that must be compacted and a catalog that must be kept consistent. Layering tables as raw (bronze), cleaned (silver), and business-ready (gold) is a common convention for where quality gates sit.

How the architecture evolved

Warehouse
Coupled storage and compute; schema on write
Data lake
Cheap open files; schema on read; no atomic commits
Lakehouse
Open files plus a transaction log

Layered tables (medallion convention)

Raw (bronze)
As received; immutable
Cleaned (silver)
Deduplicated, typed, conformed
Business (gold)
Aggregates and models for consumers

Atomic commit on object storage

Write data files
Invisible until referenced
Publish metadata entry
One atomic pointer swap
Readers pin a snapshot
Old or new version, never a mix

Each generation fixes the previous one. The commit protocol is what makes files behave like a table.

Source: Apache Iceberg — Table Spec Source: Delta Lake — Transaction Log Protocol Fundamentals of Data Engineering Learning Spark (2nd ed.)Data Management at Scale (2nd ed.)Delta Lake: Up and Running Apache Iceberg: The Definitive Guide

How are you going to do it?

Land raw data unchanged in a first layer, apply cleaning and conformance in a second, and publish business-level aggregates in a third; put quality checks at each boundary.
Compact small files on a schedule and expire old snapshots, or read cost and latency degrade.
Choose a table format for its engine support and catalog story, since commit semantics are similar across the major formats.

Change data capture: read the database log, not the tables

A database already records every committed change in an ordered log. CDC reads that log instead of polling tables or writing to two places.

What are you trying to do?

Replicate changes from an operational database by consuming its transaction log.

Why are you doing it?

Polling a table with an updated-at column misses deletes and any intermediate updates between polls, and it loads the source with repeated queries. Writing to the database and to a message bus from application code (a dual write) can leave them inconsistent when one write fails. The database log is the authoritative, ordered record of committed changes, kept for crash recovery and replication, so a connector that reads it sees every insert, update, and delete in commit order without querying the tables. Changes are published per key in order, deletes travel as tombstones, and the consumer applies them as upserts and deletes. Delivery is at least once, so sinks must be idempotent. To bootstrap, take a consistent snapshot, record the log position, then tail the log from that position so nothing is missed or applied twice.

Log-based CDC

Write commits
Insert, update, or delete
Recorded in the log
Write-ahead or binary log, in commit order
Connector reads the log
No queries against the tables
Change events per key
Ordered; deletes as tombstones
Sink applies
Idempotent upserts and deletes

Bootstrap without gaps or overlap

Record log position
Mark where the snapshot starts
Consistent snapshot
Copy existing rows
Tail the log
Resume from the recorded position

Outbox pattern (application-published events)

One transaction
Business row and outbox row commit together
Relay reads outbox
Often via CDC on the outbox table
Publish event
No dual write to keep consistent

The log is the single ordered source of change; the outbox pattern gives applications the same guarantee.

Source: Debezium Documentation Designing Data-Intensive Applications (2nd ed.)Building Event-Driven Microservices (2nd ed.)Kafka: The Definitive Guide (2nd ed.)

How are you going to do it?

Prefer log-based CDC over timestamp polling when you need deletes, every change, or low load on the source.
Bootstrap with a snapshot at a recorded log position, then stream from exactly that position.
Where the application must publish events itself, write the event to an outbox table in the same transaction as the business row, and relay it from there instead of dual writing.

Data quality and contracts: check at the boundary, quarantine the rest

Bad data costs more the further it travels. Contracts state expectations between producer and consumer; checks enforce them where someone can fix the cause.

What are you trying to do?

Move quality checks to the boundaries between teams and stages, and agree expectations in a contract.

Why are you doing it?

A wrong value caught at ingestion costs one conversation with its producer; the same value caught in a dashboard costs a retraction and lost trust. Checks therefore belong at each boundary: when data enters, after each transformation, and before it is served. A data contract makes the expectation explicit and owned: the schema, the meaning of fields, freshness and volume targets, and who to contact when it changes. Schema changes are the commonest break. Adding an optional field is usually safe for existing consumers, while removing or renaming a field, or changing a type, breaks them, so such changes need versioning and notice. When only some rows are bad, validate and quarantine them in a dead-letter table instead of failing the entire load, and alert on the quarantine rate. Common dimensions to monitor are freshness, volume, schema, value distribution, and lineage.

Checks along the happy path

Contract
Schema, meaning, freshness, owner
Ingestion checks
Schema, types, nulls
Transformation tests
Unique, referential, accepted values
Publish and monitor
Freshness and volume watched

When rows fail

Quarantine rows
Dead-letter table with the reason
Alert on the rate
A spike means a source changed
Notify the owner
Fix upstream, then replay

Checks sit at every boundary. Failures are isolated and routed to an owner instead of blocking everything.

Source: Open Data Contract Standard Source: Confluent Schema Registry — Schema Evolution and Compatibility Source: OpenLineage Documentation Data Management at Scale (2nd ed.)Data Mesh Fundamentals of Data Engineering Data Quality Fundamentals Data Contracts Data Governance: The Definitive Guide Implementing Service Level Objectives Database Reliability Engineering

How are you going to do it?

Write the contract with the producer: schema, field meaning, freshness, volume range, owner, and the change process.
Validate at ingestion (schema, types, nulls), test after each transform (uniqueness, referential integrity, accepted values), and monitor freshness after publish.
Quarantine failing rows with the reason attached, alert on the rate, and fix the cause upstream.

Testing pipelines: test the code on fixtures and the data in place

Code tests prove the logic is right; data tests detect when the input changed. A pipeline needs both, plus a re-run test for idempotency.

What are you trying to do?

Layer unit, data, and contract tests, and promote changes through staging with a data diff.

Why are you doing it?

A pipeline can fail because the code is wrong or because the data is different from what the code assumed, and only separate kinds of test catch each. Unit tests run transformation logic on small hand-built fixtures, fast and without infrastructure, so they belong in every commit. Data tests assert properties of real output, such as uniqueness, non-null keys, accepted ranges, and row counts against expectations, so they detect drift the fixtures never saw. Contract tests check that the output schema still satisfies its consumers. A staging run on production-shaped data, with a diff against current production output, shows the effect of a change before it ships. Because retries and backfills are routine, a re-run test (run one interval twice and compare) checks idempotency directly.

Test layers, fast to slow

Unit tests
Pure logic on small fixtures
Data tests
Unique, not null, ranges, counts on real output
Contract tests
Output schema still fits consumers
Staging run
Production-shaped data, full path

Promoting a change

Pull request
Review the code and the SQL
CI tests
Unit and contract tests pass
Staging data diff
Compare output with production
Promote
Deploy, then watch the data tests

Fast, cheap tests run on every change; slower, data-dependent tests run where the data lives.

Software Engineering at Google Data Pipelines with Apache Airflow (2nd ed.)Fundamentals of Data Engineering Analytics Engineering with SQL and dbt Data Quality Fundamentals

How are you going to do it?

Keep transformations as small, pure functions or single-purpose SQL models so they can be tested on fixtures.
Run unit and contract tests in CI on every change, and data tests on every run in production.
Before promoting, run in staging and diff the output against production; add a run-twice test for every load.

SQL patterns: number the rows, then group by something derived

Window functions compute a value per row over a partition without collapsing rows. Most interview SQL reduces to a handful of window patterns.

What are you trying to do?

Recognize the four window patterns behind most data engineering SQL problems: dedup, running totals, islands, and sessions.

Why are you doing it?

GROUP BY collapses rows into one per group, which loses the row-level detail many problems need. A window function evaluates over a set of related rows (a partition, ordered within it) and returns a value for each input row, so you can rank, accumulate, and compare to neighbors in a single pass. Engines implement this by sorting or partitioning the data by the partition keys, so a supporting index or pre-sorted layout makes it cheaper. Many hard-looking problems are the same few moves: number rows to keep the latest per key, accumulate with a frame, subtract a row number from a sequence so that consecutive runs share a constant value, or flag gaps and take a cumulative sum to label sessions. Fluency comes from recognizing which move a problem needs, then checking the edge cases: ties, nulls, and the window frame.

Latest row per key

PARTITION BY key
ORDER BY updated_at DESC
ROW_NUMBER()
Add a tiebreaker column
Keep row 1
Filter in an outer query

Running total

Order in partition
For example by date within account
Frame
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
SUM() OVER
One value per input row

Consecutive runs (gaps and islands)

ROW_NUMBER()
Over the ordered values
Value minus row number
Constant within a run
GROUP BY the difference
MIN and MAX give each run

Sessionization

LAG(timestamp)
Previous event per user
Flag a gap
For example more than 30 minutes
Cumulative SUM(flag)
The running count is the session id

Each pattern is a short pipeline of window steps. Name the pattern first, then write the SQL.

Learning SQL (3rd ed.)PostgreSQL Query Optimization SQL Antipatterns, Volume 1

How are you going to do it?

Latest row per key: ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC), then keep rows numbered 1. Add a tiebreaker column so the result is deterministic.
Running totals and moving averages: an aggregate OVER an ordered partition with an explicit frame (ROWS BETWEEN … AND CURRENT ROW).
Consecutive runs and sessions: the difference between a value and ROW_NUMBER() is constant within a run; LAG() plus a gap flag plus a cumulative SUM() labels sessions. Read the query plan to confirm sorts are not repeated.

Partitioning and cost: you pay for bytes read and moved

In scan-priced and cluster-priced engines, cost tracks bytes scanned and shuffled. Layout decides how many.

What are you trying to do?

Lay out data so common queries touch few bytes, and treat cost as a design input.

Why are you doing it?

Analytical engines are billed, directly or through cluster time, for the data they read and move. Partitioning splits a table by a commonly filtered, low-cardinality column such as date, so the engine reads partition metadata, discards partitions the predicate rules out, and never opens those files. Columnar formats let it read only the referenced columns, and sorting or clustering within files lets zone maps skip more. A wide join or aggregation instead redistributes rows across the network by key (a shuffle), and a hot key (skew) overloads one worker. The layout has costs too: too many tiny partitions create small files and metadata overhead that slow every query. Cost therefore belongs in design reviews: estimate bytes per query and per pipeline run, tag spend to owners, and track unit cost against the value delivered.

Layout matches the filter

Read partition metadata
WHERE event_date = one day
Prune partitions
1 of 365 survives
Read needed columns
Columnar files skip the rest
Few bytes scanned
Low cost, low latency

Filter does not match the layout

No pruning possible
Filter is on a non-partition column
Open every file
All partitions read
Many bytes scanned
High cost, high latency

Shuffle and skew

Wide join or group
Rows must meet by key
Redistribute over network
Bytes moved cost time and money
Hot key overloads a worker
Salt the key or broadcast the small side

Layout decides bytes, and bytes decide cost. Prune first, shuffle as little as possible.

Source: FinOps Foundation — Framework Designing Data-Intensive Applications (2nd ed.)High Performance Spark (2nd ed.)Spark: The Definitive Guide Cloud FinOps (2nd ed.)

How are you going to do it?

Partition by the column most queries filter on and that yields partitions of meaningful size; cluster or sort by the next most common filter.
Select only needed columns, filter early, and check the plan for the share of partitions and bytes scanned.
Watch for skew and small files; compact files, salt hot keys, and track cost per query and per pipeline run with owners attached.

The cloud data landscape: reason by category, then map the names

Every cloud sells the same handful of categories under different names. Map each service to the lifecycle stage it serves and the trade-off it makes.

What are you trying to do?

Place any managed data service on the lifecycle by the job it does, then compare on a few decision axes.

Why are you doing it?

Vendors rename the same building blocks, so memorizing product names ages badly while the categories stay stable. Each lifecycle stage has a typical category: operational databases and SaaS APIs generate data, message buses and managed connectors ingest it, object storage and warehouses or lakehouse tables store it, SQL and distributed compute engines transform it, and BI tools, online stores, and reverse-ETL tools serve it. Identity and key management, catalogs, orchestration, and monitoring cut across all of them as the undercurrents. Comparing services then needs only a few axes: managed versus self-operated, whether storage and compute scale separately, the pricing unit (per byte scanned, per cluster hour, or per credit), egress charges for moving data out, and how open the storage format is, which sets how hard it is to leave.

Lifecycle stage → typical service category

Generation
Operational databases, SaaS APIs, event producers
Ingestion
Message bus, managed connectors, CDC
Storage
Object storage, warehouse, lakehouse tables
Transformation
SQL engines, distributed compute
Serving
BI, online stores, reverse ETL

Spanning every stage

Identity and keys
Roles, least privilege, encryption keys
Catalog and lineage
Discoverability and impact analysis
Orchestration
Schedules, dependencies, retries
Monitoring and cost
Freshness, failures, spend by owner

Categories are stable across vendors. Names change; the job each category does does not.

Fundamentals of Data Engineering Data Management at Scale (2nd ed.)

How are you going to do it?

For any unfamiliar service, ask which lifecycle stage it serves and which category it belongs to before reading the feature list.
Compare candidates on the five axes: operations burden, storage-compute separation, pricing unit, egress cost, and format openness.
Prefer open storage formats and portable interfaces where switching cost matters, and note where egress charges make moving data expensive.

CI/CD and infrastructure as code for data pipelines

Treat pipeline code and the infrastructure under it as versioned, reviewed, and promoted artifacts, with a data diff before production.

What are you trying to do?

Ship pipeline changes and infrastructure changes the way you ship application code, adding checks that only data work needs.

Why are you doing it?

Pipelines are software with a data side effect, so the discipline that makes application delivery safe applies: every change is in version control, reviewed, tested automatically, and promoted through environments by an automated process rather than by hand. Data adds one wrinkle: a deploy can be correct code and still change results, so promotion compares the new output with production on the same inputs (a data diff) and deploys must be backfill-safe, meaning a new version can re-process history without corrupting it. Infrastructure as code applies the same idea to storage, compute, permissions, and schedules: you declare the desired state in files, a plan step shows the difference from the current state, a reviewer approves it, and an apply step converges the real world to the declaration. Drift detection catches manual changes that bypass the process.

Code path

Commit
Code and SQL in version control
CI checks
Lint, unit, and contract tests
Deploy to staging
Run on production-shaped data
Data diff
New output versus production
Promote
Backfill-safe release

Infrastructure as code

Declare desired state
Storage, compute, permissions, schedules
Plan
Show the diff from current state
Review
A person approves the plan
Apply
Converge the real world to the code
Detect drift
Find manual edits that bypass review

Both paths end in a reviewed, automated change. The data diff is the step application delivery does not have.

Source: Terraform — Introduction Source: DORA — DevOps Research and Assessment Software Engineering at Google Release It! (2nd ed.)Fundamentals of Data Engineering Terraform: Up and Running (3rd ed.)Infrastructure as Code (3rd ed.)

How are you going to do it?

Put pipeline code, SQL, and infrastructure definitions in version control; require review and passing CI before merge.
Promote through environments automatically; compare staging output with production on identical input before the final promotion.
Review the infrastructure plan, not just the code, and run drift detection so out-of-band edits are found and reverted.

What a data engineering interview tests, and how to structure answers

Loops typically probe SQL, coding, modeling, pipeline design, and cross-functional judgment. A repeatable structure turns design questions into trade-off discussions.

What are you trying to do?

Answer design questions by moving from requirements to the lifecycle, trade-offs, failure modes, and operations.

Why are you doing it?

Interviewers are sampling for the same thing the job needs: reasoning from vague requirements to a design whose trade-offs you can defend and whose failures you have anticipated. Loops commonly combine a SQL screen, a coding exercise, a modeling or schema question, a pipeline design discussion, and a conversation about working with stakeholders. Design questions are open-ended on purpose, so structure carries the answer: clarify volume, velocity, freshness, and consumers; map the problem onto the lifecycle stages; choose a technology and state the trade-off at each; then walk through what fails and how you recover (retries, idempotency, backfill, quality checks); finish with cost and operations. Expectations scale with level: implementing well-specified work reliably, then owning a design with its trade-offs and failure modes, then setting contracts and platform direction across teams.

Structure of a design answer

Clarify
Volume, velocity, freshness, consumers
Map to the lifecycle
Generation through serving
Choose and justify
State the trade-off at each stage
Failure modes
Retries, idempotency, backfill, quality
Cost and operations
Monitoring, SLAs, spend

Typical loop areas

SQL
Window functions, joins, tuning
Coding
Data structures and transforms
Data modeling
Grain, facts, dimensions, history
Pipeline design
The structure above
Cross-functional
Contracts, incidents, stakeholders

Structure is the part of the answer you control. Practice it until the order is automatic.

Fundamentals of Data Engineering Designing Data-Intensive Applications (2nd ed.)

How are you going to do it?

Open every design question by confirming requirements: data volume, arrival pattern, freshness target, consumers, and constraints.
Walk the lifecycle left to right, naming the decision and the alternative you rejected at each stage, then cover the undercurrents that apply (security, quality, orchestration).
Close with failure modes and recovery, cost, and monitoring, and explain how you would communicate changes to each stakeholder group.

Reading path

What to read for the pipelines level

8
Pipeline design in practice
Pipelines
Data Pipelines Pocket Reference Data Engineering Design Patterns Analytics Engineering with SQL and dbt Delta Lake: Up and Running Apache Iceberg: The Definitive Guide Data Quality Fundamentals Data Contracts
You can now explain: Where to transform, how to load idempotently, how to model and store tables, and how to enforce quality and contracts — the decisions the Pipelines level argues from first principles.

Design the pipeline from source to consumer

What to read for the pipelines level

Pipeline design in practice