Artificial Intelligence & Data Science · 1.3

Data Engineer

Design, build, and maintain scalable data infrastructure and pipelines that power analytics and ML.

6–9 months6 phases26 skills

Foundations

Python for pipelinesscripting, file handling (CSV/JSON/Parquet using csv module, json module, pyarrow), error handling with structured logging (Python logging module with formatters), API consumption with requests library (pagination, rate limiting with tenacity retry decorator), unit testing with pytest (fixtures, parametrize, mocking with unittest.mock)

Advanced SQL in PostgreSQL

Advanced SQL in PostgreSQLDDL (CREATE TABLE, ALTER, DROP), DML (INSERT, UPDATE, DELETE, MERGE/UPSERT with ON CONFLICT), indexing strategies (B-tree for equality/range, GIN for full-text/JSONB), query optimization with EXPLAIN ANALYZE (reading execution plans — sequential scan vs index scan, nested loop vs hash join), table partitioning by date range (PARTITION BY RANGE), materialized views for precomputed aggregations, recursive CTEs for hierarchical data, LATERAL joins, JSONB operations (-> and ->> operators, jsonb_array_elements)

Linux/CLI proficiency

Linux/CLI proficiencyfile navigation (ls, find, du), text processing (grep, awk, sed, cut, sort, uniq), process management (ps, top, kill, nohup, screen/tmux), bash scripting (variables, if/else, for loops, functions, cron scheduling with crontab -e), SSH for remote access, SCP for file transfer

Networking and APIs

Networking and APIsHTTP protocol (methods, status codes, headers), REST API design principles, authentication (OAuth 2.0 flow, API keys, Bearer tokens), JSON and XML parsing

Phase 02 · 19h

Data Modeling & Warehousing

Dimensional modeling (Kimball)

Dimensional modeling (Kimball)star schema design (fact tables with foreign keys and measures surrounded by dimension tables with descriptive attributes), snowflake schema (normalized dimensions), Slowly Changing Dimensions (SCD Type 1 = overwrite, Type 2 = add versioned row with effective/expiry dates and is_current flag, Type 3 = add previous_value column), conformed dimensions shared across fact tables

Normalization vs denormalization

Normalization vs denormalizationwhen normalized OLTP structure is appropriate vs when denormalized OLAP structure improves query performance

Snowflake cloud data warehouse

Snowflake cloud data warehousethree-layer architecture (storage, compute via virtual warehouses, cloud services), creating databases/schemas/tables, loading data with COPY INTO from stages, SnowPipe for continuous auto-loading from S3, zero-copy cloning for dev/test environments, time travel (query data as it existed up to 90 days ago), VARIANT data type for semi-structured JSON, FLATTEN for exploding nested arrays, cost optimization (AUTO_SUSPEND, warehouse sizing, RESOURCE MONITORS)

Data lake vs data warehouse vs data lakehouse

Data lake vs data warehouse vs data lakehousedata lake (raw data in any format on S3 — cheap and flexible but can become a "data swamp"), data warehouse (structured, modeled, optimized for queries), data lakehouse (open formats like Parquet on object storage queried by SQL engines like Databricks or Trino)

Metadata management and data catalogs

Metadata management and data catalogsusing DataHub (open-source by LinkedIn) for searchable data inventory with automatic metadata ingestion from Snowflake, Airflow, and dbt

Phase 03 · 23h

ETL/ELT & Orchestration

ETL vs ELT

ETL vs ELTETL transforms before loading (traditional), ELT loads raw data first then transforms inside the warehouse using SQL (modern — dbt handles the "T" in ELT)

Apache Airflow

11h

Apache Airflowcore concepts (DAGs, Operators — PythonOperator, BashOperator, PostgresOperator; Task Instances, XComs for small data passing), writing DAGs in Python (with DAG() as dag: task1 >> task2 >> task3), task dependencies and fan-in/fan-out patterns, BranchPythonOperator for conditional execution, Sensors (S3KeySensor waits for a file to arrive), TaskGroups for visual organization, Connections and Hooks for external system integration, monitoring via the Airflow UI (DAG runs, task logs, gantt charts), email/Slack alerting on failure, best practices (idempotent tasks, no side effects, version-control DAGs in Git)

dbt (data build tool) with dbt-snowflake adapter

dbt (data build tool) with dbt-snowflake adapterproject structure (models/, seeds/, tests/, macros/), model layers (stg_* staging views for light cleaning, int_* intermediate for business logic joins, marts/* final business-ready tables), materializations (view, table, incremental with is_incremental() and unique_key, ephemeral as CTE), built-in tests (unique, not_null, accepted_values, relationships defined in YAML schema files), custom tests, dbt-expectations package for advanced data quality tests, documentation (descriptions in schema.yml, generate docs site with dbt docs generate && dbt docs serve showing the DAG of dependencies), Jinja templating ({{ ref('stg_orders') }} for referencing models, {% if is_incremental() %} for conditional logic, macros for reusable SQL)

Data ingestion

Data ingestionbatch (full vs incremental extract using watermark columns), Change Data Capture with Debezium (streams database transaction log changes to Kafka as JSON events), managed ingestion tools (Fivetran for 300+ pre-built SaaS connectors — pay per Monthly Active Rows; Airbyte as an open-source self-hosted alternative with 350+ connectors)

Data quality

Data qualityGreat Expectations (define expectations like expect_column_values_to_not_be_null, run validation checkpoints as Airflow tasks, generate data docs), dbt tests for every model, data contracts between producer and consumer teams, circuit breaker pattern (halt pipeline on quality failure rather than loading bad data)

Phase 04 · 18h

Big Data & Streaming

Apache Spark

Apache Sparkdistributed computing concepts (driver, executors, partitions, shuffles), DataFrames and SparkSQL for SQL-like queries on distributed data, PySpark (reading/writing Parquet/CSV/JSON, filter/groupBy/join/window operations, UDFs, broadcast variables for small lookup tables), performance tuning (partition management, caching, avoiding shuffles, predicate pushdown), running Spark on Databricks (managed Spark with notebooks, Delta Lake integration, Unity Catalog for governance)

Apache Kafka

Apache Kafkadistributed event streaming fundamentals (producers publish messages to topics, consumers read from partitions within consumer groups, brokers store and replicate data), offsets for tracking consumer position, Schema Registry with Avro schemas for enforcing data contracts between producers and consumers, Kafka Connect for no-code source/sink connectors (Debezium PostgreSQL source connector, Snowflake sink connector, S3 sink connector in Parquet format)

Stream processing

Stream processingFaust (Python stream processing on Kafka — define agents that process messages in real-time, windowed aggregations with tumbling/hopping/session windows) for simpler use cases; Apache Flink (overview — Java/Python, exactly-once semantics, Flink SQL for stream queries) for complex streaming requirements

Spark Structured Streaming

Spark Structured Streamingreading from Kafka topics as streaming DataFrames, applying transformations, writing to sinks (Delta Lake, console, Kafka), watermarking for handling late data

Phase 05 · 10h

Cloud & Infrastructure

AWS data services

AWS data servicesS3 (data lake storage with lifecycle policies for cost management, organized by prefixes like s3://datalake/raw/orders/year=2024/month=03/), AWS Glue (Crawlers for automatic schema discovery into the Glue Data Catalog, PySpark-based ETL jobs), Amazon Athena (serverless SQL on S3 — $5/TB scanned, optimize with Parquet and partitioning), Lambda (event-driven functions for lightweight processing like file validation), Kinesis (real-time streaming — Kinesis Data Streams for collection, Kinesis Firehose for loading into S3/Redshift), IAM (roles with least-privilege policies for pipeline service accounts)

Infrastructure as Code with Terraform

Infrastructure as Code with TerraformHCL syntax for defining resources (S3 buckets, Snowflake databases, IAM roles), terraform init/plan/apply/destroy workflow, state management with S3 backend and DynamoDB locking, modules for reusable infrastructure patterns, workspaces for dev/staging/production environments

Docker for data engineering

Docker for data engineeringDockerfiles for pipeline environments (multi-stage builds for smaller images), Docker Compose for local development stacks (Airflow + PostgreSQL + MinIO), .dockerignore for excluding data files

CI/CD with GitHub Actions

CI/CD with GitHub Actionsrun dbt tests and linting on pull requests, deploy Airflow DAGs on merge to main, automated Docker image builds

Monitoring and alerting

Monitoring and alertingCloudWatch for AWS resource monitoring, Airflow task-level alerting (email_on_failure, Slack via SlackWebhookOperator), pipeline SLA tracking (alert if a DAG hasn't completed by expected time)

Phase 06 · 7h

Capstone

Capstone Project 1

Capstone Project 1Full ELT platform — Airflow DAG extracts from PostgreSQL + REST API + S3 CSV files, loads raw data into Snowflake, dbt transforms through staging → intermediate → mart layers (star schema with fact_orders and dimension tables), dbt tests at each layer, Slack alerts on failure, architecture diagram in draw.io

Capstone Project 2

Capstone Project 2Real-time streaming pipeline — Python producer simulates ride-sharing events to Kafka, Faust stream processor computes rolling 5-minute aggregations per zone, Kafka Connect Snowflake sink loads raw events for historical analysis, Grafana dashboard connected to TimescaleDB shows real-time metrics

Capstone Project 3

Capstone Project 3Data platform migration — refactor a monolithic Python ETL script (provided) into modular Airflow tasks with dbt transformations, Snowflake dimensional model, Great Expectations quality checks, incremental loading, before/after performance benchmarks

Ready to start Data Engineer?

Start this roadmap to track progress and generate courses for each skill. Login required only to begin.