1. LandOffer
  2. Blog
  3. All articles
  4. Data Engineer Portfolio: Run One Pipeline End to End and Show Failure Recovery

Job search

Data Engineer Portfolio: Run One Pipeline End to End and Show Failure Recovery

An actually executed synthetic Python/SQLite pipeline demonstrates source contracts, single-process orchestration, idempotent published content, rollback, recovery, and a completed bounded portfolio writeup.

9 min readLandOffer team

A data engineer portfolio should let another person run a pipeline, inspect its output, repeat the run, and see what happens when a step fails. Build a small source-to-table workflow with a clear input contract and an observable commit point. A dashboard can display the result, but the engineering evidence is the source contract, execution order, validation, publication boundary, and recovery procedure behind it.

For this guide, we built and executed a self-contained Python and SQLite demonstration using synthetic orders. Baseline, replay, injected failure, recovery, and invalid-input checks ran locally on October 20, 2026. The example uses no external database or cloud account. To connect your eventual case study to relevant openings, browse recent roles on LandOffer.ai; choose projects that demonstrate the responsibilities those roles actually request.

A data engineer moves order slips through a sorting workbench while preserving an intact earlier ledger during a paused update.

Define a source contract before choosing more tools

Our source is a complete CSV snapshot with four fields: order_id, order_date, status, and amount_cents. One row represents one order. IDs must be unique and nonempty; dates use canonical ISO formatting; statuses are paid or cancelled; amounts are nonnegative integer cents. The input contains one currency and no personal information.

The pipeline rejects unexpected headers, missing fields, duplicate IDs, unrecognized statuses, invalid dates, invalid amounts, and empty snapshots. Rejecting an empty file is a deliberate safeguard for this demonstration: a missing extract should not erase the previously published result. A real source might legitimately contain zero rows and require a different contract.

Python’s CSV documentation explains that ordinary CSV reading returns strings rather than automatically enforcing the desired types. Our implementation therefore parses and validates explicitly. Treat a parsed file as readable input, not proof that the records satisfy your business rules.

This contract also defines recovery. A replacement file contains the whole intended snapshot, including older orders. We do not claim to support an incremental event stream, source offsets, delete events, or out-of-order update versions.

Make the execution order and publish point visible

The demonstration’s command-line program orchestrates these stages in order: read and hash the input; validate every row; begin a SQLite transaction; replace the order snapshot; aggregate paid orders by day; reconcile paid revenue; commit. For the tested exception and duplicate-input failure, the process returned exit code 1 and saved a JSON error receipt.

The published tables are orders and daily_sales. Both replacements occur inside the same transaction. SQLite’s transaction documentation describes explicit BEGIN, COMMIT, and ROLLBACK boundaries. We use explicit SQL transaction control with Python’s connection configured for that approach, rather than depending on an unstated default.

The aggregation counts only paid orders and sums their integer cents. Reconciliation compares the source’s paid amount with the aggregate’s total before publication. Reconciliation can catch a mismatch, but the source calculation and SQL could share the same mistaken business interpretation. The verification script also asserts specific expected daily rows.

This is orchestration within one local process. There is no scheduler, distributed task runner, automatic retry service, or cloud deployment hidden behind the word “pipeline.” Add a scheduler or task runner when a specific execution requirement calls for one.

Worked run: baseline, replay, failure, and recovery

The first completed asset is the actual execution record. We ran the program using Python 3.12.14 with SQLite 3.53.4. Version-one input contains six orders, including one cancelled order. Its five paid orders total 8,500 cents across three dates.

Version two retains all six records and adds two paid orders: a late-arriving 900-cent order dated October 18 and a 1,100-cent order dated October 20. Its expected paid total is 10,500 cents. “Late-arriving” here describes a record added in the second snapshot with an older order date; no live source arrival time was observed.

Actual run Input and intervention Observed published result
Baseline Version one; ordinary execution Six orders; 8,500 paid cents; successful commit
Replay Same version-one file again Identical ordered published rows and semantic hash
Injected failure Version two; exception after table writes but before commit Exit code 1; rollback; six-order version remains published
Recovery Version two without injected exception Eight orders; 10,500 paid cents; successful commit
Invalid source Version two plus a duplicate order ID Exit code 1; previously recovered output unchanged

A separate SQLite connection read the previously committed tables while the replacement transaction was still open. After the injected failure, both the semantic content and the database file’s SHA-256 matched their pre-failure values. The verifier asserted those comparisons; these are observed results from this local run, not a general guarantee about every filesystem or failure mode.

After recovery, the daily table contained:

Day Paid orders Revenue in cents
2026-10-18 3 4,400
2026-10-19 2 2,500
2026-10-20 2 3,600

The replay demonstrates idempotency of published content for this full-snapshot operation: applying the same source again does not add duplicate records or change the result. It does not establish exactly-once delivery across external systems. Logs may have different timestamps even when table content is identical. The verification report preserves the exact source hash for each invocation, so a reviewer can distinguish a true replay from a run against changed input. It also retains the failed command’s standard output, error stream, and JSON receipt. The absence of an error message is weaker evidence than an asserted exit code and a comparison of the tables the consumer reads.

Explain what the failure test actually proves

The injected exception occurs after deleting and replacing rows within the transaction, before commit. That location matters. A failure before any write would not test whether incomplete replacement data could become visible. A successful rerun alone would not demonstrate preservation of the previous output.

SQLite’s atomic-commit explanation describes the transaction’s all-or-none behavior and its underlying assumptions. Our observed rollback supports the narrow claim that this controlled exception did not publish a partial snapshot. We did not kill the process, unplug a disk, simulate power loss, test a corrupt filesystem, or exercise concurrent writers.

Keep the log boundary honest as well. JSON logs are written outside the database transaction. A log-write failure after commit could leave committed tables without the expected receipt. Our test did not exercise that failure. Before retrying after ambiguous completion, inspect the actual published state rather than treating a missing log as proof that nothing committed.

The final PRAGMA integrity_check returned ok. SQLite documents this integrity check as a database check; it does not know whether “paid revenue” matches the meaning your consumer intended. Preserve the row-level expectations and business reconciliation alongside it. The invalid-source execution in this package specifically checks a duplicate ID. The program contains additional validation branches, but this run record is not an exhaustive test suite for every malformed file. A reviewer can see that limitation and add focused cases for a new contract instead of assuming one successful rejection covers all input failures.

Give the reviewer a runnable package and useful logs

The local draft includes a demo folder with pipeline.py, verify_demo.py, two synthetic CSV snapshots, a README, and saved run evidence. The scripts use Python’s standard library. The reviewer does not need credentials, an external API, or a private dataset to reproduce this particular example.

From that folder, the complete verification command is:

python3 verify_demo.py

It creates a fresh proof directory, executes five subprocess runs, asserts their expected exit codes, and saves their logs and final database. To inspect the main program separately, use a demo-only output path:

python3 pipeline.py --input orders-v1.csv \
  --db my-run/warehouse.sqlite \
  --log my-run/baseline.json --run-id baseline

The README also provides the replay, failure-injection, and recovery commands. The failure command is supposed to return exit code 1; a shell chain that only continues on success will stop there. Read the failure evidence, then run the recovery command intentionally. Rerunning a full older snapshot after recovery would deliberately restore the older data, because this program implements replacement rather than append-only history.

Each JSON log records the run ID, source hash, stage sequence, status, and published rows with a semantic hash. Source-byte hashes distinguish input versions; sorted-row hashes compare published meaning. A raw SQLite file hash has a narrower use here: checking the same file immediately before and after this injected failure. Do not require physical database bytes to match across unrelated machines or SQLite versions.

Worked portfolio writeup: describe the executed project accurately

The second completed asset is a concise case study for the actual demonstration. It gives a reviewer enough context to decide what to inspect without claiming invented employer outcomes.

Problem and consumer: “Produce a daily paid-order table from a small complete orders snapshot. A downstream reader needs the last successful table to remain available when a replacement run fails.” The consumer is part of this synthetic scenario, not a real business client.

Implementation and ownership: “I built a Python command-line workflow that validates the CSV contract, loads orders into SQLite, aggregates paid revenue, reconciles the total, and commits both published tables together. The package includes synthetic inputs, run instructions, and an automated verification script.” This describes the supplied code; a candidate should use first person only for work they actually performed.

Observed evidence: “On the recorded local runtime, six orders produced 8,500 paid cents. Replaying the same snapshot preserved table content. A controlled pre-commit exception rolled back the replacement, and recovery with the expanded snapshot produced eight orders and 10,500 paid cents. A duplicate-ID input was rejected without changing the recovered output.”

Trade-off and next boundary: “Full replacement makes this tiny example easy to reason about, but it loads the source into memory and reprocesses all rows. I have not implemented incremental offsets, distributed retries, production access controls, or crash-recovery tests. The next change should depend on source size and delivery requirements.”

A reviewer can follow the case study in a practical order: inspect the two fixtures, read the transaction boundary, run the verifier, then compare the failure and recovery receipts. If an observed number differs, start with the runtime and input hashes. Do not rewrite the narrative to preserve an expected success claim; investigate the discrepancy and keep the actual output.

The writeup makes the implemented behavior and untested boundaries available for review. It links each claim to something inspectable and gives the interviewer useful follow-up questions. It also leaves room to discuss what failed, why the recovery procedure works, and what you would change for a different source contract.

An earlier completed ledger stays beside a transaction workbench while a failed update is rolled back and a corrected batch moves toward publication.

Extend the project around the role you want

For an ingestion role, the next useful extension might be a real public source with a documented retrieval policy, rate limits, and reproducible source versions. For an orchestration role, consider bounded retry behavior and state inspection after ambiguous completion. For warehouse work, consider load strategy, schema changes, and consumer contracts. These are proposed extensions, not features already demonstrated here.

Add one change and a meaningful check. For example, an incremental version should define the event key and update ordering before using an upsert. A multi-task workflow should explain what happens when one task retries after another has committed. A larger dataset should be measured under a stated environment before claiming throughput or scalability.

Keep secrets and employer data out of the public portfolio. This demonstration uses synthetic fixtures specifically so the source and failure evidence can be inspected together. Use a diagram, dashboard, or README to help a reviewer locate the executable work and its evidence. When selecting your next extension, explore LandOffer.ai for relevant job requirements and match the evidence to the responsibilities you can truthfully demonstrate.

Sources and Further Reading