Data Engineering

Understand how operational and analytical systems store and use data.

• Structured, semi-structured and unstructured data; relational and non-relational stores.
• Tables, keys, constraints and database relationships.
• SELECT, filtering, joins, aggregations and subqueries.
• Common table expressions, window functions and query readability.

Practical exerciseWrite analytical queries over a sample sales database and validate totals against the source records.

Build small, repeatable programs to read data from files and services.

• Python collections, functions, modules and exception handling.
• CSV, JSON and columnar file-format concepts.
• API pagination, authentication, rate limits and bounded retries.
• Configuration, logging and separating secrets from code.

Practical exerciseCreate an ingestion script that reads paginated sample data and records failures without duplicating successful records.

Organise data so that business questions can be answered consistently.

• Operational versus analytical models and choosing the grain of a table.
• Facts, dimensions, star schemas and surrogate keys.
• Slowly changing dimensions and historical data requirements.
• Warehouse, lake and lakehouse concepts; partitioning and storage choices.

Practical exerciseDesign a sales reporting model with a data dictionary and examples of the questions it supports.

Move data through staged transformations while keeping reruns predictable.

• Extraction, staging, transformation and loading responsibilities.
• Data cleansing, deduplication and business-rule transformations.
• Full loads, incremental loads, watermarks and change-data capture concepts.
• Idempotency, late-arriving data, schema changes and backfills.

Practical exerciseBuild an incremental source-to-reporting pipeline and demonstrate that rerunning it does not create duplicates.

Explore when data volume or arrival patterns require a different processing approach.

• Distributed computation, partitions and parallel processing.
• Spark DataFrames, transformations, actions and join/shuffle concepts.
• Batch versus streaming, events, topics and consumer concepts.
• Event time, windows, delivery semantics and checkpointing.

Practical exerciseTransform a sample dataset with a distributed-processing workflow and diagram a streaming alternative.

Coordinate pipeline tasks and understand the services needed to run them reliably.

• DAGs, dependencies, schedules, retries and task state.
• Airflow concepts, parameters, backfills and failure notifications.
• Cloud object storage, managed processing and warehouse service patterns.
• Permissions, secrets, environment separation and cost controls.

Practical exerciseCreate a scheduled pipeline workflow with dependency checks and a documented retry and recovery process.

Make data trustworthy and give downstream users visibility into its condition.

• Completeness, uniqueness, validity, freshness and referential-integrity checks.
• Quarantine, reconciliation, lineage and ownership.
• Sensitive data, retention, access controls and audit trails.
• Query plans, partition pruning, monitoring and performance trade-offs.

Practical exerciseAdd quality gates and operational metrics to a pipeline, including an example of rejected data.

Deliver a documented pipeline that turns source records into a useful analytical dataset.

• Source contracts, architecture and a data model.
• Ingestion, transformations, orchestration and quality checks.
• Operational runbook, failure recovery and cost/performance considerations.
• SQL, Python, pipeline-design and production-support interview scenarios.

Practical exercisePresent a source-to-reporting project with validated outputs, a pipeline diagram and a recovery demonstration.