Data Engineer Projects

# Incremental order analytics with replay and data quality checks

> Project scaffold: replace TODO fields with your implementation and measured results before publishing. This template does not contain a completed application.

## Objective
Turn synthetic order events into daily revenue and customer metrics using a pipeline that handles duplicate deliveries, late updates and reruns without double-counting.

## Architecture
```mermaid
flowchart TD
  N0["Synthetic order event files"]
  N1["Immutable raw partitions"]
  N2["Validated staging and quarantine"]
  N3["Deduplicated current orders"]
  N4["Daily analytics marts"]
  N5["Quality report and reconciliation"]
  N0 --> N1 --> N2 --> N3 --> N4 --> N5
```

Keep raw input immutable for replay. Select the latest event per order using an update timestamp plus a deterministic tie-breaker. Recompute affected aggregate partitions when late updates change earlier orders.

## Tools and prerequisites
Python, SQL joins, basic data modeling and Git.
- Python for deterministic fixture generation and ingestion
- Parquet for local raw partitions
- DuckDB and dbt for transformations and tests
- A scheduled local command or Kestra for orchestration
- GitHub Actions for fixture-based pipeline checks

## Repository structure (proposed)
```text
README.md
src/                 # Your implementation
tests/               # Unit, integration and failure-case tests
fixtures/            # Small synthetic or permitted inputs
config/              # Non-secret configuration examples
docs/architecture.md # Decisions and tradeoffs
docs/runbook.md      # Recovery, rerun and cleanup procedures
reports/             # Sanitized evidence and measured results
.env.example         # Variable names and safe placeholders only
```

## Setup and execution
- TODO: Record supported OS, runtime versions, pinned dependencies and hardware requirements.
- TODO: Add exact commands to install, configure, start and run a sample input after implementing them.
- TODO: Document environment variables in .env.example; keep secrets and local .env files out of Git.
- TODO: Include expected sample output and any optional hosted-service costs.

## Implementation checklist
- [ ] Define order_id, event_id, updated_at, amount, currency and status. Generate reproducible fixture files containing duplicates, cancellations, late updates and invalid amounts; state the currency and revenue rules.
- [ ] Write ingestion that stores raw partitions plus a manifest of checksums, row counts and processing status. Keep source event time separate from ingestion time.
- [ ] Validate required fields and types before transformation. Send rejected records to a quarantine output with reasons, while reporting accepted plus rejected counts against input totals.
- [ ] Build staging and current-order models. Deduplicate by event ID, select the latest order state deterministically and test key uniqueness and required values.
- [ ] Build daily revenue and order-count marts with documented cancellation handling. For late updates, recompute affected dates or use a justified lookback window with an explicit older-data backfill path.
- [ ] Persist the processing checkpoint only after the batch succeeds. Rerun the same batch and assert unchanged row counts and totals; simulate interruption and verify safe retry.
- [ ] Reconcile mart totals against accepted source events and test a late cancellation that changes a previous day's revenue. Add a full-rebuild comparison for the small fixture dataset.
- [ ] Measure runtime and processed rows for a stated dataset size. Publish the lineage diagram, data dictionary, quality report and backfill runbook without describing the local lab as a production deployment.

## Tests and acceptance evidence
- [ ] Deterministic fixtures for duplicates, late events and invalid records
- [ ] An unchanged result after replaying the same batch
- [ ] Incremental outputs matching a full rebuild on fixtures
- [ ] A reconciliation report and a documented backfill procedure
- TODO: Add the exact test command and a link to a passing run.
- TODO: Explain at least one failure case and how it is detected or recovered.

## Results
Do not replace missing measurements with estimates presented as observations.

| Metric | Baseline | Result | Dataset / hardware / run link |
| --- | --- | --- | --- |
| TODO: choose a project metric | Not measured | Not measured | TODO |

## Limitations and next steps
- TODO: State what this lab does not establish about production reliability, security or model quality.
- TODO: Document cleanup, retained data and optional infrastructure charges.
- TODO: Link an issue for the next improvement and explain its priority.

## Interview preparation
- What is the difference between event time and ingestion time in this pipeline?
- How does a late cancellation affect already published daily aggregates?
- What happens if the job stops after writing output but before saving its checkpoint?
- Which quality checks detect a plausible but incorrect revenue total?

## Resume draft (use only after completing the work)
Replace bracketed values with real evidence and remove claims you did not implement.
- Built an incremental order analytics pipeline with immutable raw data, deterministic deduplication, quarantine handling and tested batch replay.
- Validated late-update handling against a full rebuild on [N] synthetic events, reconciling source and mart totals and measuring [runtime] on [hardware].

## References, attribution and your contribution
- [Data Engineering Zoomcamp](https://github.com/DataTalksClub/data-engineering-zoomcamp): Learning reference covering ingestion, orchestration, warehousing, dbt modeling and pipeline projects. The proposed local DuckDB orders dataset and recovery tests are original project choices.

Use the curriculum for ingestion and modeling concepts. Implement your own event contract, interruption fixtures, reconciliation rules and backfill tests; this is an independent portfolio brief, not an official course assignment.

- TODO: Record the exact upstream commit/tag you consulted and any files reused or modified.
- TODO: Preserve required copyright/license notices and check each repository's reuse terms before copying code. Choose a license only for work you have rights to license.
- TODO: Explain your own implementation and link its commits; do not claim authorship of upstream code.

Project brief: [BonusMantra](https://bonusmantra.com/career-roadmaps/data-engineer/projects/); reference pages checked 2026-09-06.