Data Engineer
Data Engineering
Build the data pipelines that every analyst, ML engineer, and product team depends on. Over 12 weeks you'll design and ship ETL/ELT flows, work with cloud data warehouses, handle schema evolution gracefully, and operate both batch and streaming workloads on real datasets. By the end you'll be comfortable writing dbt models a senior engineer would merge without hesitation, debugging Airflow DAGs at 2 a.m., and explaining why your warehouse bill went up this month. Topics covered: ETL/ELT design, SQL fluency, dbt, Apache Airflow, data warehousing (Snowflake / BigQuery / Redshift patterns), streaming basics (Kafka), data quality testing, cost optimisation.
Time commitment
10-15 hours per week
Prerequisites
SQL fluency (joins, window functions, aggregations). Basic Python or shell scripting. Comfort with git and the command line. No prior dbt or Airflow experience required.
You'll leave with
A production-grade dbt project with documented lineage, an orchestrated pipeline running on a real schedule, and a runbook explaining how to debug it when it breaks. The kind of work sample that gets you past a data-engineering screen.
What you receive
Structured weekly plan
Clear objectives, defined tasks, and concrete deliverables for every week of the program.
AI-led evaluation
Every submission is reviewed across technical proficiency, ownership, communication, and learning.
Human expert on request
Request a human expert reviewer for advanced topics, escalations, or specialised guidance.
Verifiable credentials
Formal offer letter on approval. Verifiable experience letter on completion.
The 12-week roadmap
Every week is planned before you start.
- Week 1
Local warehouse + your first dbt model
Stand up a working local data warehouse (DuckDB or Postgres), ingest a small set of source datasets, and ship your first dbt staging + mart model with tests passing. This is the muscle you'll exercise weekly — the goal is to make the second model feel routine.
What you'll do
- Install DuckDB (or Postgres) locally; install dbt-core + the matching adapter
- Load 2-3 sample CSV datasets into raw tables; create dbt source declarations
- Build one staging model (cleaning + renaming) and one mart model (joining/aggregating)
- Add at least 4 dbt tests (not_null, unique, accepted_values, relationships) — all passing
- Write a short README explaining the lineage and how to reproduce
You hand in: GitHub repo with the dbt project, sources YAML, both models, passing test output, and a lineage diagram (text or generated).
- Week 2
Orchestration: schedule + monitor + handle a real failure mode
Wire your dbt project into an orchestrator (Apache Airflow, Prefect, or Dagster — pick one) so it runs on a schedule. Intentionally introduce one failure mode (e.g. a source file going missing) and make sure your DAG fails loudly with a useful error. By end of week you should be able to say what runs when, what depends on what, and what happens when it breaks.
What you'll do
- Set up Airflow (or Prefect / Dagster) locally; get the basic UI running
- Wrap your dbt models in a DAG with proper dependencies between staging and mart steps
- Add a daily schedule; verify it runs on the next tick
- Simulate a source-data failure (rename or empty a CSV); confirm the DAG fails clearly
- Document the failure-recovery procedure in your README
You hand in: Updated GitHub repo with the DAG file, screenshot of the orchestrator UI showing successful runs, and a screenshot/log of the failure mode failing as expected.
- Week 3
Incremental models and the cost of a full refresh
Move your heaviest mart off a full rebuild and onto an incremental strategy. Measure the run time and rows scanned before and after, so the improvement is a number rather than a feeling. Then break it deliberately — backfill a late-arriving day and prove the model self-heals rather than silently double-counting.
What you'll do
- Identify your slowest model; record its current runtime and row count
- Convert it to an incremental model with a sensible unique key and partition column
- Run a backfill for a past date range; verify no duplicate rows appear
- Simulate late-arriving data and confirm the merge strategy handles it
- Record before/after runtime in the README with the query you used to measure
You hand in: The incremental model, a before/after timing note, and a short write-up of what your merge strategy does with a row that arrives twice.
- Week 4
Ingestion: pull from an API you do not control
Build an ingestion job against a real external API — rate limits, pagination, flaky responses and all. The point is not the API; it is writing a loader that can be re-run safely after it dies halfway through, because eventually it will.
What you'll do
- Pick a public API with pagination and a rate limit; document both in your README
- Write an extractor with pagination, retry-with-backoff, and a request budget
- Land raw responses unmodified before any parsing, so a parser bug is recoverable
- Make the loader idempotent: run it twice and assert the row count is unchanged
- Kill the job mid-run deliberately; re-run it and prove it resumes cleanly
You hand in: The loader, the raw landing zone, and evidence from an interrupted run followed by a clean resume.
- Week 5
Data quality as code
Stop finding out about bad data from a dashboard. Add tests that fail the pipeline at the boundary where bad data enters, distinguish a warning from an error, and route each to somewhere a human will actually look.
What you'll do
- Add freshness checks on every source; decide the threshold per source and justify it
- Add row-count anomaly detection against a rolling baseline
- Split tests into warn and error severities; make errors stop downstream models
- Wire failures to a notification channel you will genuinely read
- Introduce a real defect upstream and confirm it is caught before the mart
You hand in: The test suite, the severity policy written down, and a screenshot of the alert produced by your deliberately broken data.
- Week 6
Modelling for change: slowly changing dimensions
Handle the fact that the world edits itself. Implement history tracking on a dimension that genuinely changes, and be able to answer 'what did this record look like on that date' — the question that separates a warehouse from a database dump.
What you'll do
- Pick a dimension with attributes that change over time; document which ones matter
- Implement a Type 2 snapshot with valid-from and valid-to columns
- Write a query that reconstructs the state of the world on an arbitrary past date
- Decide and document which attributes are Type 1 (overwrite) and why
- Add a test asserting no overlapping validity windows for any key
You hand in: The snapshot model, the point-in-time query with its output, and a note on the Type 1 versus Type 2 decision.
- Week 7
Performance: partitioning, clustering and file layout
Make a slow query fast for structural reasons rather than by adding hardware. Read a query plan, change the physical layout of the data, and measure the effect. Learn where the win comes from so you can predict the next one.
What you'll do
- Take your slowest analytical query and read its execution plan
- Apply partitioning on the column the plan shows is doing the most scanning
- Experiment with file formats and compression; measure size and read time
- Re-read the plan and record what changed
- Write down the rule of thumb you would give a colleague from what you learned
You hand in: Before and after query plans, a timing table, and your written rule of thumb.
- Week 8
Streaming, and whether you actually need it
Build a streaming ingestion path and then argue honestly about whether it earns its complexity here. Most 'real-time' requirements are an hourly batch with better communication, and knowing which is which is the skill.
What you'll do
- Stand up a message broker locally (Kafka, Redpanda or equivalent)
- Produce events from a simulated source and consume them into your warehouse
- Handle a duplicate event and an out-of-order event explicitly
- Measure end-to-end latency and compare it against your batch pipeline
- Write a one-page recommendation: stream, batch, or micro-batch, and why
You hand in: The working stream, the latency comparison, and the recommendation with its reasoning.
- Week 9
Contracts and schema evolution
Make a breaking upstream change safe. Define what your pipeline promises its consumers, detect when a producer breaks it, and evolve a schema without a coordinated outage across three teams.
What you'll do
- Write a data contract for one mart: columns, types, nullability, freshness, owner
- Add automated contract validation to CI so a breaking change fails the build
- Add a column and remove one, using an expand-then-contract migration
- Version the interface so an old consumer keeps working through the change
- Document the deprecation path and the date you would remove the old shape
You hand in: The contract file, the failing CI run from a deliberate breach, and the migration write-up.
- Week 10
Cost, and who is paying for that query
Find out what your pipeline costs to run and reduce it without breaking it. Attribute spend to individual models, cut the worst offender, and prove the output is unchanged.
What you'll do
- Instrument your runs to record bytes scanned or compute time per model
- Rank models by cost; identify the top three
- Optimise the worst one and measure the saving
- Prove output equivalence before and after with a row-level comparison
- Write down the monthly saving and what you traded away to get it
You hand in: A cost breakdown by model, the optimisation diff, and the equivalence proof.
- Week 11
Operability: the runbook and the 3am test
Make the pipeline supportable by someone who did not build it. Write the runbook, make the alerts actionable, and then have someone else follow your instructions cold while you stay quiet.
What you'll do
- Write a runbook covering the three most likely failures and their recovery steps
- Make every alert name the affected model, the likely cause, and the first action
- Add a backfill command that is safe to run under pressure
- Have a peer follow the runbook on a broken pipeline without your help
- Revise the runbook based on everywhere they got stuck
You hand in: The runbook, both versions, and a short note on what your peer could not follow the first time.
- Week 12
Ship it and write the decisions down
Bring the pipeline to a finished state and produce the document that makes it inheritable: what it does, how it is shaped, what you chose against, and what you would do next with more time.
What you'll do
- Complete any outstanding tests and documentation
- Produce an end-to-end lineage diagram from source to consumed mart
- Write architecture decision records for your three biggest choices
- List the known limitations honestly, including the ones nobody asked about
- Record a short walkthrough of the pipeline running end to end
You hand in: The finished repository, the lineage diagram, the decision records, the limitations list, and the walkthrough.