Foundation Level • Production Systems

Data Engineering Master Course | Pipelines, Lakehouses & SQL

Master data engineering with PrepSpaces. Learn data pipeline architecture, OLTP vs OLAP, batch and stream processing, ETL/ELT, data lakes, Apache Iceberg, Delta Lake, data quality, and Python data pipelines.

Course Overview & Engineering Foundations

Direct Course Definition: Comprehensive, industry-aligned engineering course covering the full data engineering lifecycle, data pipeline architecture, OLTP vs OLAP workloads, batch vs continuous stream processing, ETL vs ELT pipelines, enterprise data warehouses, data lakes, modern lakehouses (Delta Lake, Apache Iceberg, Apache Hudi), data quality dimensions, and local Python pipeline development.

Curriculum Modules

  • Module 1: Introduction to Data Engineering and Data Systems — Foundations of data infrastructure, lifecycle, OLTP vs OLAP, batch vs stream, ETL vs ELT, warehouses, lakes, lakehouses, data quality dimensions, and practical Python pipeline.
  • Module 2: Python Programming for Data Engineering — Python fundamentals, OOP, generators, iterators, CSV/JSON/XML parsing, chunk processing, regex, logging, API extraction, SQLite/SQLAlchemy, Pandas, Polars, and Pytest.
  • Module 3: Advanced SQL and Relational Database Systems — PostgreSQL architecture, normal forms 1NF/2NF/3NF, DDL/DML, multi-table joins, CTEs, recursive CTEs, window functions, views, stored procedures, triggers, ACID transactions, MVCC, and indexing optimization.
  • Module 4: Data Modeling and Database Design — Conceptual, logical, and physical modeling, ER diagrams, 1NF/2NF/3NF/BCNF normalization, Star and Snowflake schemas, fact table grain, Slowly Changing Dimensions (SCD Type 1 & 2), event-driven modeling, and schema evolution.
  • Module 5: Database Internals and Performance Engineering — 8KB pages, buffer pool caching, B+ Trees vs LSM Trees vs Hash indexing, Bloom filters, cost-based query optimization, nested loop/hash/sort-merge joins, ACID transactions, MVCC tuple versioning, WAL crash recovery, table partitioning, and PostgreSQL performance tuning.
  • Module 6: Data Formats, Serialization and Data Integration — Row vs columnar storage, CSV, JSON, XML, Apache Avro, Apache Parquet, Apache ORC, Apache Arrow in-memory, Protocol Buffers, compression codecs (Snappy, GZIP, ZSTD, LZ4), schema evolution, Confluent Schema Registry, REST API ingestion with cursor pagination, rate limits, webhooks, and data contracts.
  • Module 7: Data Warehousing and Analytical Systems — Enterprise warehouses, OLAP architectures, multidimensional dimensional modeling, star/snowflake schemas, columnar storage engines, MPP parallel processing, partitioning, clustering, materialized views, analytical query optimization, and modern cloud warehouse internals (Snowflake, BigQuery, Redshift, ClickHouse).
  • Module 8: Data Lakes and Lakehouse Architecture — Cloud object storage (S3/ADLS/GCS), HDFS, Raw/Processed/Curated zones, Medallion Architecture (Bronze, Silver, Gold), Parquet/Avro/ORC, open table formats (Apache Iceberg, Delta Lake, Apache Hudi), ACID transactions, snapshot isolation, MVCC, time travel, schema and partition evolution, catalogs, small file compaction, and data governance.
  • Module 9: Data Ingestion and Change Data Capture (CDC) — Batch & incremental ingestion, full loads vs incremental extraction, log-based CDC (MySQL Binlog, PostgreSQL WAL logical decoding), REST API ingestion (cursor pagination, rate limits, backoff), file/cloud discovery, Debezium, Kafka transport, watermarks, offsets, checkpointing, deduplication, idempotent upserts, DLQs, and backpressure.
  • Module 10: ETL, ELT and Batch Data Processing — ETL vs. ELT architectures, SQL/API/CDC extraction, transformations (cleansing, standardization, filtering, aggregation, joins, enrichment), loading strategies (full refresh, append, upsert/MERGE, partition overwrite), incremental processing with watermarks, late-arriving data, deduplication, idempotent pipeline design, failure recovery, data reconciliation with hash-based checksums, Airflow DAG orchestration, and distributed Spark batch processing.
  • Module 11: Distributed Data Processing with Apache Spark — Spark cluster architecture (Driver, Executors, Tasks), RDD lineage, DataFrames, Datasets, Spark SQL & Catalyst Optimizer (predicate pushdown, column pruning), narrow vs. wide transformations, lazy evaluation, data partitioning (repartition vs coalesce), shuffle mechanics, broadcast joins, memory management, data skew mitigation (salting, AQE), Spark UI performance tuning, Parquet internals, Delta Lake Medallion Architecture, and production pipelines.
  • Module 12: Workflow Orchestration with Apache Airflow — Modern workflow automation, DAG mechanics, TaskFlow API (@task), Scheduler and Executor architectures (Local, Celery, Kubernetes), standard and provider operators, sensors and deferrable triggerers, conditional branching, trigger rules (none_failed_min_one_success), 5-field cron scheduling and data intervals, task retries, exponential backoff, idempotency, backfilling and catchup, dynamic task mapping (.expand / .partial), Variables, Connections, XCom metadata, secrets backends (Vault, AWS Secrets Manager), monitoring, alerts, data-aware scheduling (Airflow 3 Assets), production deployment, and Dagster Software-Defined Assets.
  • Module 13: Analytics Engineering and dbt — Modern Analytics Engineering principles, dbt Core & v2 architectures, modular 3-tier layering (staging, intermediate, marts), materializations (view, table, incremental, ephemeral, materialized view), SCD Type 2 snapshots, seeds, data testing (generic unique/not_null/accepted_values/relationships & custom singular assertions), Jinja templating, reusable macros, dbt-utils, model contracts, schema evolution, Slim CI/CD with state-based deferral, and Airflow orchestration.
  • Module 14: Data Quality, Testing, Metadata and Lineage — Enterprise data quality engineering, dimensions of quality (accuracy, completeness, consistency, uniqueness, validity, timeliness, freshness, integrity, reliability), structural and statistical profiling, rule-based validation and DLQ quarantine separation, JSON schema evolution and schema registries, null vs empty string detection, window-based deduplication, referential integrity and orphan record detection, freshness lag formulas and SLAs, source-to-warehouse count and financial reconciliation, statistical Z-score and IQR anomaly detection, automated testing with Great Expectations, Soda, Deequ, and dbt tests, unit testing with pytest, pipeline idempotency, formal data contracts, technical/business/operational active metadata, data catalog architectures (DataHub, OpenMetadata), and column-level data lineage with blast radius impact analysis.
  • Module 15: Distributed Systems for Data Engineers — Distributed computing architecture, client-server and multi-tier systems, synchronous vs asynchronous IPC, gRPC and REST APIs, serialization formats (JSON, Avro, Protobuf, Parquet), TCP/UDP protocols, distributed storage (HDFS, S3 object storage, Cassandra), data partitioning (range, hash, list, round-robin, sharding, skew mitigation), synchronous vs asynchronous replication, leader-follower architectures, failover and split-brain mitigation, consistency models (strong, eventual, read-your-writes, monotonic), CAP theorem, PACELC, quorum-based systems (R+W>N, majority voting), fault tolerance and checkpointing, heartbeats and failure detectors, consensus fundamentals (Raft algorithm, log replication, Paxos), distributed locks, leases, fencing tokens, exponential backoff with jitter, circuit breakers, reactive backpressure, Kafka consumer lag management, message delivery guarantees (at-most-once, at-least-once, exactly-once), idempotency keys, and distributed system reliability (SLIs, SLOs, SLAs, RPO, RTO).
  • Module 16: Event-Driven Architecture and Apache Kafka — Event-driven architecture fundamentals, events vs commands, choreography vs orchestration, message brokers vs work queues, Apache Kafka architecture, KRaft controller quorum vs ZooKeeper, topic partitions as append-only logs, producers and consumers (confluent-kafka), consumer groups and partition assignment math, offset commits (earliest/latest/manual commits), per-partition ordering and partition keys, replication factor and in-sync replicas (ISR), min.insync.replicas, log retention and compaction (tombstone records), producer acknowledgements (acks=0, acks=1, acks=all), delivery semantics and idempotent producers, Confluent Schema Registry (BACKWARD, FORWARD, FULL, NONE compatibility), Avro and JSON serialization, Kafka Connect source and sink connectors, Log-based Change Data Capture (CDC) with Debezium, Dead-Letter Queues (DLQ) for poison messages, message replay workflows, Kafka security (SASL/PLAIN/SCRAM, TLS encryption, ACLs), real-time ingestion pipelines, log segments and zero-copy transfer internals, Kafka transactions (transactional.id, read_committed), consumer lag monitoring (Lag = LogEndOffset - CommittedOffset), and the Transactional Outbox pattern.
  • Module 17: Stream Processing and Real-Time Analytics — Stream processing foundations, batch vs stream processing, bounded vs unbounded datasets, Lambda and Kappa architectures, real-time processing pipeline layers, event time vs processing time vs ingestion time, windowing concepts (tumbling windows, sliding/hopping windows, session windows with dynamic inactivity gaps), watermarks and event-time progression (Watermark = max(EventTime) - AllowedDelay), out-of-order and late-arriving event handling (allowed lateness, side outputs, historical reconciliation), stateful stream processing (keyed state, operator state, RocksDB state backend, State TTL), stream-stream joins and time-bounded state retention, distributed checkpointing and savepoints, exactly-once end-to-end processing guarantees, Apache Flink architecture (JobManager, TaskManager, task slots, operator chaining), Spark Structured Streaming (micro-batch execution, Real-time mode, Kafka source integration, Append/Update/Complete output modes), and production real-time analytics dashboard design.
  • Module 18: Cloud Data Engineering and Cloud Platforms — Cloud computing foundations (elasticity vs scalability, IaaS vs PaaS vs SaaS), cloud infrastructure architecture (regions, availability zones, VPC networking, security groups vs network ACLs), Identity and Access Management (IAM least privilege, roles vs users, JSON policy design), cloud security & encryption (Shared Responsibility Model, KMS encryption at rest & in transit), AWS data ecosystem overview, Amazon S3 multi-tier data lake architecture (Landing, Raw, Trusted, Curated zones, lifecycle transition policies), AWS Glue (Data Catalog metadata repository, Crawlers, serverless PySpark ETL jobs), Amazon Athena (serverless SQL analytics, partition pruning, columnar scan optimization), Amazon Redshift (MPP architecture, Leader/Compute nodes, distribution styles KEY/EVEN/ALL, compound/interleaved sort keys, Redshift Serverless), Amazon EMR (managed Spark/Hadoop clusters, EC2 vs Serverless vs EKS), Amazon Kinesis (Data Streams, shards, partition keys, Firehose streaming delivery), cloud managed databases (RDS, Aurora, DynamoDB NoSQL, OLTP vs OLAP), cloud ETL vs ELT pipelines, serverless data architectures (AWS Lambda triggers, event-driven pipelines, cold starts), cloud cost optimization strategies, multi-cloud equivalence mapping across Microsoft Azure and Google Cloud, hands-on e-commerce platform capstone, and comprehensive interview questions.
  • Module 21: Data Reliability, Observability and Performance Optimization — Comprehensive Data Reliability Engineering (DRE) foundations, software reliability vs data reliability, series system reliability modeling, fault tolerance and high availability, pipeline monitoring metrics (execution status, duration, processing rates, success percentage), the three telemetry pillars (structured JSON logs, metrics counters/gauges/histograms, distributed traces), the 5 pillars of data observability (Freshness, Volume, Distribution, Schema, Lineage), statistical volume anomaly detection with Z-scores, schema evolution and drift handling (additive vs breaking changes, schema registries), automated data quality testing and alert fatigue reduction, service level management (SLIs, SLOs, error budget exhaustion management, formal SLAs), incident management lifecycle and blameless postmortems, Root Cause Analysis (5 Whys and Fishbone diagrams), pipeline failure recovery (checkpointing, replay, historical backfills, dead-letter queues), exponential backoff retries with jitter and 3-state circuit breakers (Closed, Open, Half-Open), backup strategies and enterprise disaster recovery, Recovery Point Objective (RPO) and Recovery Time Objective (RTO) engineering, compute right-sizing and autoscaling, data skew remediation (salting, adaptive query execution), query performance tuning (partition pruning, early filtering, avoiding non-sargable functions, avoiding SELECT *), horizontal vs vertical scalability, stream backpressure handling, FinOps cost optimization (cost per million records), and the 19-point production readiness review (PRR) checklist.
  • Module 22: Data Products, Serving Architectures and AI Data Engineering — Comprehensive guide to enterprise data products, product-oriented data architecture, Data Mesh decentralized domain ownership, self-service data platforms, federated computational governance, data contracts, analytical data marts, star schema design, semantic layers (dbt Semantic Layer, Cube, Looker), OLTP vs OLAP workload characteristics and systems (ClickHouse, Snowflake, Redshift), high-concurrency Data APIs (FastAPI, GraphQL), in-memory caching architectures with Redis (Cache-Aside, Write-Through, TTL, and cache invalidation strategies), Reverse ETL data activation (Census, Hightouch), graph databases (Neo4j, Cypher relationship traversals), NoSQL architectures (Document, Key-Value, Wide-Column) and CAP theorem trade-offs, machine learning data engineering, feature engineering pipelines, feature store architecture (Feast, Tecton), offline historical storage vs online low-latency serving, training-serving skew prevention, point-in-time correctness (AS-OF joins) for data leakage prevention, AI data preparation pipelines, dense vector embeddings, vector databases (Qdrant, Pinecone, Chroma), cosine similarity search, Retrieval-Augmented Generation (RAG) ingestion workflows, incremental knowledge index refreshing, and AI data quality, security, and governance frameworks.
  • Module 23: Data Engineering System Design and Final Capstone — Complete data engineering system design, quantitative capacity estimation, batch vs streaming trade-offs, Change Data Capture (CDC), Medallion lakehouse modeling (Apache Iceberg, Parquet), Airflow orchestration, dbt analytical data marts, and the ShopSphere production e-commerce capstone.
  • Module 20: Data Security, Governance and Privacy — Comprehensive enterprise data security lifecycle (at rest, in transit, in use, during sharing, and deletion), CIA Triad with cryptographic hash verification vs keyed HMAC-SHA256 pseudonymization, Zero Trust architecture (NIST SP 800-207) and Defense in Depth, automated data classification tiers (Public, Internal, Confidential, Restricted), PII and indirect quasi-identifier discovery with re-identification risk management, authentication (MFA, certificates, federation, IAM execution roles) vs authorization, RBAC vs dynamic ABAC policy engines, least privilege IAM policies with default deny, envelope encryption using AWS KMS (DEK vs KEK lifecycle, key rotation, KMS policies and grants), cloud secrets management (AWS Secrets Manager, SSM Parameter Store, HashiCorp Vault), static vs dynamic data masking (format-preserving masking, SQL masking views), privacy engineering (k-anonymity, l-diversity, token vaults, differential privacy), secure data sharing and data clean rooms, immutable access auditing with AWS CloudTrail and Amazon Macie, automated data retention and right-to-erasure workflows across distributed datastores with legal hold handling, multi-jurisdiction regulatory compliance (India's DPDP Act 2023, EU GDPR, California CCPA/CPRA, HIPAA Security Rule, PCI DSS), data governance frameworks (DAMA-DMBOK, NIST CSF 2.0 Govern function, ISO/IEC 27001/27701, SOC 2), RACI governance accountability (Data Owner, Data Steward, Data Custodian, Data Engineer), STRIDE threat modeling, and production-ready secure cloud data lake capstone.
  • Module 19: DataOps, Docker, CI/CD and Infrastructure as Code — Modern DataOps workflow, software engineering practices for data systems (modular code design, separation of responsibilities, idempotency, logging and metrics), Git version control and team collaboration (branching strategies, merge conflicts, pull requests, code reviews, .gitignore), Docker fundamentals and containerization (images vs containers vs VMs, Dockerfile instructions, multi-stage builds, layer caching, Docker Compose multi-container development stacks), environment isolation (development, staging, production), Continuous Integration and Continuous Deployment (CI vs Continuous Delivery vs Continuous Deployment, GitHub Actions workflow automation, runners, OIDC cloud authentication), automated pipeline testing (unit testing, integration testing, data quality checks, schema contract testing, testing pyramid), Infrastructure as Code with Terraform (declarative HCL, providers, resources, variables, init/plan/apply/destroy, remote state backends with S3 and DynamoDB locking, infrastructure drift detection, modules), configuration management, secrets management (environment variables, AWS Secrets Manager, Kubernetes Secrets), database schema migrations (versioning, safety rules, expand-and-contract zero-downtime migration strategy), production deployment strategies (recreate, rolling, blue-green, canary, shadow execution), rollback techniques and data recovery reconciliation, Kubernetes for data workloads (control plane, worker nodes, Pods, Jobs, CronJobs, ConfigMaps, resource requests and limits, OOMKilled recovery), and end-to-end DataOps capstone platform.

Core Engineering Competencies

  • Pipeline Architecture: Ingestion, cleaning, transformation, storage, serving, orchestration, monitoring, and governance.
  • Workload Segregation: OLTP operational databases vs OLAP analytical warehouses and dimensional modeling.
  • Processing Paradigms: Scheduled batch processing vs event-driven continuous stream processing with Kafka and Flink.
  • Lakehouse Infrastructure: Open columnar formats (Parquet), table formats (Iceberg, Delta Lake), and cloud warehouses (Snowflake, BigQuery).
  • Data Reliability: Idempotent operations, automated quality checks, dead-letter queues, backfills, and SLAs/SLOs.

Related Systems Engineering Masterclasses