Articles
Data Engineering Projects for Interviews: Build, Break, and Explain a Pipeline
Build a data engineering interview project with tested reruns, late events, schema checks, and rollback. Download a small Python and SQLite pipeline example.
The strongest data engineering projects for interviews give you something concrete to defend: an input contract, a repeatable load, a checked output, and evidence of what happened when the process failed. A diagram containing twelve tools cannot answer those questions by itself.
Start with one small pipeline that you can finish, break deliberately, and explain without hiding behind a framework. This guide includes an original downloadable Python-and-SQLite project, its actual local test results, and the follow-up questions those results invite.
The project and preparation advice are original illustrations, not a reported employer assignment. Technical statements use official documentation checked on September 12, 2026. No candidate reports establish how often an interviewer asks for this architecture.
Choose a project with a visible consumer
Give the pipeline a question it must support. Our synthetic consumer wants daily counts of delivery events and total delivered units. Each event has an ID, an event date, and a positive integer quantity. The consumer must receive revised historical totals when a new event arrives late.
This scope is intentionally small. It makes mistakes inspectable: a duplicated event changes the total, a renamed field violates the contract, and an incomplete batch must not become a successful load. You can explain the output without needing an industry dataset or a cloud account.
A different domain is fine. Public weather readings or generated inventory movements can serve the same learning purpose if you define units, licensing, keys, and update rules. Downloading a large dataset does not resolve those decisions. Avoid private workplace data unless you have explicit authorization to use and disclose it.
Official project guidance can help calibrate scope. DataTalks.Club's Data Engineering Zoomcamp asks for an end-to-end pipeline, documentation, and a dashboard. Its project page allows choices including local DuckDB, while excluding the classroom NYC taxi dataset from the final project. Our smaller exercise is interview practice; it is not presented as satisfying that course's graduation rubric. Official project requirements.
Build one small end-to-end pipeline
Download the original pipeline example. The ZIP contains source code, instructions, an MIT license, expected output, and a saved run log. All fixtures are synthetic and generated by the demo; there is no customer information or copied employer assessment.
We ran it with CPython 3.9.6 and SQLite 3.51.0 on macOS. It uses no pip packages. The code requires Python 3.9 or later with the sqlite3 module; other version combinations were not tested. The bundle records its own version as 1.0 so the described results have a concrete reference.
After extracting it, inspect the source and open a terminal in pipeline-example:
python3 -c "import sys, sqlite3; print(sys.version); print(sqlite3.sqlite_version)"
python3 demo.py
The demo creates fixed CSV inputs and a database in a temporary directory. It runs the checks below, writes run-log.json and expected-report.csv into the project folder, then cleans up its temporary workspace. Repeating the entire demo starts a fresh experiment. Within each experiment, the replay test uses the same database, so it actually checks persistent state.
For a persistent manual run, the README provides a two-row CSV and these commands:
python3 pipeline.py local.db input.csv daily.csv
python3 pipeline.py local.db input.csv daily.csv
The second command recognizes the committed batch and regenerates the report without adding the events again. Python's official documentation describes SQLite as a disk-based database that does not require a separate server process. That makes it suitable for this local exercise, not automatically suitable for every production workload. Python sqlite3 documentation.
Define what the loader promises
The source header is exactly event_id,event_date,units. An event ID identifies an immutable event. Repeating the same event is harmless; changing its date or quantity under the same ID is an error. Corrections would require a different, explicitly designed contract.
Batch identity is separate. The loader records a filename and SHA-256 digest after a successful load. An exact replay is skipped. Reusing that committed filename with different bytes is rejected, because silently treating it as already processed would hide an upstream change.
This is a deliberately narrow design: batch basenames must be unique within the input collection. A production source might need a namespaced object key and generation identifier. Naming that limitation is stronger than calling the checksum a universal solution.
The loader begins an explicit database transaction, validates the whole input, writes events, and records the successful batch in the same transaction. If an exception occurs before commit, it rolls back. In the implementation, transaction acquisition happens before parsing so concurrent writers cannot race the manifest check; that also holds the write reservation longer.
That trade-off is acceptable for these tiny files. At scale, you might validate into staging first, then keep the final transaction short. The official SQLite transaction documentation explains the transaction boundaries and how BEGIN IMMEDIATE acquires a write transaction. Our test establishes the behavior of this implementation, not a guarantee about every storage configuration. SQLite transactions.
Check outputs before adding more infrastructure
The initial input contains two events: A delivers 2 units on January 1; B delivers 3 on January 2. The total is 5. Event C then arrives in a later batch with event date January 1 and quantity 4. The January 1 total becomes 6, even though that date has already appeared in a report.
The database stores events and a successful-batch manifest. A SQL view groups by event date. The CSV is a derived export of that view, so it can be regenerated rather than treated as the only copy of the result.
The export uses a temporary file and replacement, but it happens after the database commit. Those are two separate operations. If export fails after commit, rerunning the same input recognizes the committed batch and tries the report again. Do not describe the database and CSV as one atomic distributed transaction.
The final checked report is small enough to audit by hand:
event_date,events,units
2026-01-01,2,6
2026-01-02,2,4
2026-01-03,2,5
The rows total six events and fifteen units. The demo asserts the exact report contents. A screenshot of a successful command would be weaker evidence: it could miss a duplicated row, a stale export, or an incorrect grouping date.
Test reruns, late data, and failures
These are actual local results from the downloadable demo. “Committed batches” means successful manifest records, not attempted executions.
| Check | Input or disruption | Observed result |
|---|---|---|
| Normal load | A=2 and B=3 | 2 events, 5 units, 1 committed batch |
| Exact replay | Same filename and bytes | State stays 2 events, 5 units, 1 batch |
| Late event | C=4 for January 1 | 3 events, 9 units, 2 batches; earlier date revised |
| Schema mismatch | Rename units to quantity | Rejected; state stays 3 events, 9 units, 2 batches |
| Schema repair | Restore the expected header on the uncommitted file | 4 events, 10 units, 3 batches |
| Mid-run exception | Raise an exception after inserting the first of two new rows | Rollback leaves 4 events, 10 units, 3 batches |
| Recovery | Retry that failed batch without injection | 6 events, 15 units, 4 batches |
The demo also rejects a conflicting immutable event and a modified previously committed batch. These are different problems: one violates event identity; the other violates the source-batch record. Keeping those checks distinct makes diagnosis easier.
The late arrival is not itself corrupt input. It is a normal arrival pattern that the reporting rule must support. A loader that filters only on the greatest event date seen could lose it. This example processes new batches independently of event-date ordering and recomputes the small report from stored events.
The schema repair does not rewrite a successful batch. The bad file never committed, so its corrected version can be processed. For a successfully committed source, introduce a new versioned batch or a governed migration rather than quietly editing history.
Explain the recovery boundary honestly
The failure test raises a Python exception after an insert and before commit. That exercises the explicit rollback path. We did not kill the process, remove power, simulate a disk failure, or test multiple distributed workers. The run log states this boundary.
The design also reads a whole CSV into memory and scans the complete event table for the report. Those choices simplify inspection at this scale. They are not throughput benchmarks, and the sample does not justify claims about millions of events per second.
In an interview, distinguish what you tested from what you would test next. For example: “I verified rollback on an injected exception. I would separately test process termination and output recovery, with assertions about both the committed manifest and the rebuilt report.” That answer makes a precise claim and proposes a meaningful next experiment.
If an interviewer changes the requirement to mutable events, do not simply remove the conflict check. Decide how versions, corrections, and deletion records should behave. Then change the tests alongside the contract. A useful project remains understandable when one assumption changes.
Turn the repository into an interview explanation
Open with the consumer and invariant: “The report counts each immutable delivery event once, and late events revise their original date.” Then show one failure before describing implementation details. That gives the listener a reason to care about the manifest and transaction.
Keep a short decision record beside the code. Explain why you used immutable IDs, why a changed committed batch fails, and why the report is rebuildable. Include an alternative you rejected and the condition that would make you reconsider it.
Separate your own modifications from the starting example. The MIT license permits reuse; it does not turn someone else's work into your experience.
These questions help turn the evidence into a technical discussion:
| Practice question | Project evidence to bring |
|---|---|
| Design idempotent daily loads with deduping | Replay result and separate event/batch identities |
| Diagnose data quality and pipeline performance issues | Rejected input, unchanged state, and memory limits |
| Design an Incremental Rolling-Metrics Data Pipeline | Why late input must revise an earlier reporting period |
| Design Data Quality and Observability Pipeline | Distinguish attempted, rejected, and committed batches |
| Design a 25,000-CSV ETL pipeline | What breaks when whole-file parsing and full scans grow |
Add one extension only when it answers a requirement
Extension one: orchestration and operational visibility. Run the existing command through a scheduler, capture exit status, and alert on failures. Keep ingestion correctness in the pipeline rather than assuming the scheduler makes retries safe. Demonstrate a failed run, its notification, and the resulting state before adding more tasks.
Extension two: larger or continuously arriving data. Replace whole-file parsing and complete report scans with a design suited to the stated size and latency. You might stage bounded chunks, recompute affected dates, or move to a streaming approach. First define what latency the consumer needs; streaming adds state and recovery questions of its own.
Neither extension was implemented in this bundle. Choose one based on a target role, write the expected failure behavior, and then build it. The next valuable portfolio improvement is the smallest experiment that proves a responsibility you currently cannot demonstrate.