Data Engineering Fundamentals: From Pipelines to Production

Objectives: Explain why a dedicated local development environment is essential for data engineering; Define virtual environments, version control, and database servers in plain terms; Describe the role of each tool (Pyth

Write Python scripts using variables, built-in data types (int, float, str, bool, list, dict), conditionals, and loops to manipulate sample data records.

Create reusable Python functions and modules with type hints, docstrings, and structured exception handling to process CSV and JSON data files.

Execute SELECT, FROM, WHERE, ORDER BY, and LIMIT clauses in PostgreSQL to retrieve and filter data from a sample e-commerce database schema.

Write SQL queries using INNER/LEFT JOINs, GROUP BY with aggregate functions (COUNT, SUM, AVG), and subqueries to answer business questions from a normalized schema.

Implement Python scripts that connect to PostgreSQL using psycopg2 and SQLAlchemy Core to perform CRUD operations and execute parameterized queries safely.

Design and implement an end-to-end ETL pipeline in Python that extracts sales data from CSV files, transforms it using Pandas, loads it into PostgreSQL, and validates row counts and data quality checks with SQL queries —

Draw an entity-relationship diagram (ERD) for a sample e-commerce domain that captures entities, attributes, relationships, and cardinalities using standard notation.

Apply first, second, and third normal form rules to a denormalized dataset to produce a logical schema free of update anomalies.

Write and execute DDL statements to create tables, primary keys, foreign keys, indexes, and check constraints that match a given logical schema in PostgreSQL.

Design a star schema (fact table + dimension tables) for a sales analytics use case, identifying grain, dimensions, and additive/non-additive measures.

Implement additive schema changes (new nullable columns, new tables) and write reversible migration scripts using a version-control workflow.

Define a Protobuf schema for a streaming event, register it in a schema registry, and validate producer/consumer compatibility checks.

Deliver a complete schema package — ERD, normalized logical schema, PostgreSQL DDL, star-schema dimensional model, migration scripts, and Protobuf data contracts — that passes automated validation tests for a retail orde

Explain Spark's distributed execution model including driver, executors, and cluster manager roles in batch ingestion workflows.

Implement batch ingestion pipelines using DataFrameReader to load CSV, JSON, and Parquet files with explicit schema enforcement.

Design resilient ingestion logic that handles schema drift, missing fields, and corrupt records using Spark's schema merging and bad record handling options.

Implement partitioned writes to cloud storage (S3/ADLS/GCS) using partitionBy and bucketBy to optimize downstream query performance.

Build exactly-once ingestion pipelines using Delta Lake's ACID transactions and merge operations to prevent duplicate data on re-runs.

Evaluate Spark UI metrics (shuffle spill, task skew, GC time) and apply configuration tuning (executor memory, parallelism) to meet SLA targets.

Deliver a working batch ingestion pipeline that loads daily order CSV files from cloud storage into a partitioned Delta table with schema enforcement, idempotency, and monitoring alerts — success criteria: zero data loss

Explain how Kafka brokers, topics, partitions, and replication factor interact to provide fault-tolerant, ordered event streaming.

Implement a Python producer using the kafka-python library that sends Avro-serialized events with idempotent delivery guarantees.

Implement a Python consumer that joins a consumer group, processes Avro events, and commits offsets manually to achieve exactly-once processing semantics.

Register, retrieve, and evolve Avro schemas using Confluent Schema Registry, enforcing backward compatibility rules for producer and consumer clients.

Design a consumption pattern that routes poison-pill messages to a dead-letter topic after configurable retries with exponential backoff.

Instrument producers and consumers with Prometheus metrics, visualize consumer lag in Grafana, and configure alerting rules for lag thresholds.

Deliver a working artifact: a Docker Compose stack that ingests simulated clickstream JSON events into Kafka, validates them via Schema Registry, processes them with a resilient consumer group writing to PostgreSQL, and

Explain the role of the Scheduler, Webserver, Executor, and Metadata Database in Airflow's architecture and how they coordinate DAG execution.

Deploy a local Airflow instance using the official Docker Compose file, verify component health, and access the UI.

Implement a DAG using BashOperator and PythonOperator that runs a simple multi-step data workflow with explicit task dependencies.

Refactor a DAG to use Jinja templating for dynamic file paths and Airflow Variables for environment-specific configuration.

Configure task retries, execution timeouts, SLAs, and email callbacks to build a fault-tolerant pipeline that meets reliability requirements.

Design a DAG that incrementally ingests partitioned CSV data into a local data lake, handles schema evolution, and registers datasets for downstream consumers.

Deliver a version-controlled Airflow project containing a parameterized, monitored, and tested DAG that reliably ingests daily partitioned data into a local data lake, with success defined by: (1) DAG parses with zero er

Compare the architectural trade-offs of Snowflake, BigQuery, and Redshift to select the optimal cloud data warehouse service for a given analytical workload profile.

Implement a secure, cost-optimized cloud data warehouse instance using Infrastructure-as-Code (Terraform) with configured network policies, IAM roles, and resource monitors.

Design a dimensional model (star schema) comprising fact and dimension tables optimized for query performance and business question alignment.

Implement modular, version-controlled ELT transformation pipelines using dbt (data build tool) including models, tests, and documentation generation.

Configure dbt incremental models and snapshots to efficiently process changing data volumes while maintaining historical accuracy for slowly changing dimensions.

Evaluate query execution plans to apply clustering keys, partitioning, and result caching; implement resource monitors and query queues to enforce cost governance SLAs.

Deploy an end-to-end cloud data warehouse solution — including provisioned infrastructure, staged raw data, transformed star schema via dbt, automated incremental loads, and cost monitoring — validated by successful exec

Explain the six core data quality dimensions (accuracy, completeness, consistency, timeliness, validity, uniqueness) and map each to specific failure modes in a batch ingestion pipeline.

Implement an automated data profiling suite using Great Expectations to generate expectation suites for a sample CSV dataset, capturing column distributions, null rates, and format constraints.

Integrate Great Expectations validation checkpoints into an Apache Airflow DAG as branching operators that halt downstream tasks on critical failures and log warnings for non-critical anomalies.

Design a dead-letter queue pattern using cloud storage (S3/GCS) and a reprocessing DAG that isolates failed records, applies correction logic, and re-validates before merging back into the main pipeline.

Implement SQL-based cross-table referential integrity checks (foreign key validation, orphan detection) between staging and dimension tables, scheduled as independent Airflow tasks with alerting on threshold breaches.

Build a Superset dashboard visualizing daily data quality metrics (completeness %, validity rate, latency hours, quarantine volume) with SLA thresholds and automated email alerts via Airflow on breach.

Deliver a production-ready data quality framework artifact: a Git repository containing (1) Great Expectations suites for orders/clicks data, (2) Airflow DAG with validation gates, quarantine, and remediation, (3) cross-

Explain the core principles and components of CI/CD pipelines tailored for data engineering, distinguishing them from traditional software CI/CD.

Implement Git branching strategies and repository structures that support collaborative development and automated testing of data pipelines.

Design and implement automated unit and integration tests for SQL and Python-based data transformations using pytest and Great Expectations.

Create reproducible, versioned Docker images for data pipeline components using multi-stage builds and Makefiles for build automation.

Configure GitHub Actions workflows to lint, test, build, and scan container images on every pull request for a data pipeline project.

Design and implement continuous deployment strategies using GitHub Actions to promote validated pipeline artifacts to staging and production environments with approval gates.

Deliver a complete, documented CI/CD system for a modular data pipeline including: a GitHub repository with branching strategy, automated test suite (unit/integration), containerized components, GitHub Actions CI/CD work