Build pipelines, lakes, and warehouses.
2 topics
Data engineers must be SQL experts — not just for querying, but for designing schemas, optimizing performance, and building transformations. Beyond basic queries, master: complex JOINs across multiple tables, window functions for ranking and running calculations, CTEs and recursive CTEs, set operations (UNION, INTERSECT, EXCEPT), advanced aggregations (GROUPING SETS, CUBE, ROLLUP), query optimization with EXPLAIN ANALYZE, index strategies (B-tree, hash, partial, covering), partitioning for large tables, and materialized views for caching expensive queries. Understand data warehousing concepts: star schema (fact and dimension tables), slowly changing dimensions (SCD Types 1-3), and surrogate keys. Most data warehouses (Snowflake, BigQuery, Redshift) use SQL variants, so deep SQL knowledge transfers across platforms.
2 resources
Python is the primary programming language for data engineering. Beyond basic Python, focus on: working with files and APIs (requests, json, csv, parquet), data manipulation with Pandas (for smaller datasets) and Polars (faster alternative), database connectivity (SQLAlchemy, psycopg2, database drivers), building CLI tools (click, argparse), error handling and logging for robust pipelines, and testing data pipelines (pytest, Great Expectations for data quality). Understand generators for memory-efficient processing of large files, async/await for concurrent API calls, and multiprocessing for CPU-intensive transformations. Package management with Poetry or pip, virtual environments, and Docker containers ensure reproducible environments. Write clean, maintainable code — data pipelines run in production for years.
Data pipelines move data from source systems to destinations where it can be analyzed. ETL (Extract, Transform, Load) transforms data before loading into the warehouse. ELT (Extract, Load, Transform) loads raw data first, then transforms in the warehouse — this is the modern approach enabled by powerful cloud warehouses. Key concepts: incremental vs full loads, idempotency (running a pipeline twice produces the same result), schema evolution (handling changing source schemas), data quality checks (null rates, uniqueness, referential integrity), backfilling (processing historical data), and exactly-once processing. dbt (data build tool) is the industry standard for the "T" in ELT — it lets you write SQL transformations as version-controlled models with testing, documentation, and lineage tracking.
Orchestration tools schedule, coordinate, and monitor data pipelines. Apache Airflow is the industry standard — it uses Python to define DAGs (Directed Acyclic Graphs) of tasks with dependencies, schedules, and retry policies. Key concepts: DAGs (workflow definitions), operators (tasks — BashOperator, PythonOperator, SQL operators, cloud operators), sensors (wait for conditions — file arrival, API response), XComs (passing data between tasks), connections and hooks (database/API credentials), pools (limiting concurrency), and the Airflow UI for monitoring and debugging. Alternatives include Prefect (more Pythonic), Dagster (asset-based), and Mage (notebook-style). In managed environments, use MWAA (AWS) or Cloud Composer (GCP). Design DAGs to be idempotent, keep tasks atomic, and use templating for date-parameterized pipelines.
Apache Spark is the dominant engine for large-scale data processing. It distributes computation across a cluster of machines, enabling you to process terabytes to petabytes of data. PySpark is the Python API. Key concepts: RDDs (Resilient Distributed Datasets — the low-level abstraction), DataFrames (structured, SQL-like API — preferred), transformations (lazy operations: filter, map, groupBy) vs actions (trigger computation: collect, count, write), partitioning (how data is distributed across the cluster), shuffles (expensive data redistribution — minimize these), caching/persisting intermediate results, and broadcast variables (efficiently sharing small data with all workers). Spark SQL lets you query structured data with SQL. Spark Streaming handles real-time data. Delta Lake adds ACID transactions on top of data lakes. Cloud-managed Spark options include Databricks, AWS EMR, and Google Dataproc.
Stream processing handles data in real-time as it arrives, rather than in batches. Apache Kafka is the dominant event streaming platform — it provides durable, ordered, distributed logs (topics) that producers write to and consumers read from. Key concepts: topics and partitions, producer and consumer groups, offsets and commit strategies, retention policies, exactly-once semantics, and schema management with Schema Registry (Avro, Protobuf). Stream processing frameworks — Kafka Streams, Apache Flink, and Spark Structured Streaming — enable real-time transformations, aggregations, windowing (tumbling, sliding, session windows), and joins on streaming data. Use cases: real-time dashboards, fraud detection, event-driven microservices, CDC (Change Data Capture) from databases, and real-time feature stores for ML.
Modern cloud data warehouses separate storage from compute, enabling independent scaling. Snowflake is the most popular — it offers multi-cluster shared data architecture, time travel (query historical data), zero-copy cloning (instant environment creation), and semi-structured data support (JSON, Avro, Parquet). Google BigQuery is serverless (no cluster management) and excels at ad-hoc queries with its columnar storage. Amazon Redshift is tightly integrated with the AWS ecosystem. Key concepts across all platforms: columnar storage (efficient for analytics), partitioning and clustering for query performance, materialized views, data sharing across organizations, and cost management (pay for compute/storage/queries). Design your warehouse with dimensional modeling — fact tables (events, transactions) surrounded by dimension tables (users, products, dates).
A data lake stores raw, unprocessed data in its native format (files on cloud storage like S3 or GCS). Unlike warehouses that require upfront schema design, lakes accept any data — structured (CSV, Parquet), semi-structured (JSON, XML), and unstructured (images, logs, documents). The lakehouse architecture combines the flexibility of data lakes with the reliability and performance of data warehouses. Delta Lake (Databricks), Apache Iceberg, and Apache Hudi add warehouse-like features to data lakes: ACID transactions, schema evolution, time travel, and efficient upserts. A typical modern data stack: data ingestion (Fivetran, Airbyte) → data lake (S3 + Iceberg) → processing (Spark, dbt) → serving (Snowflake, dashboards). Data catalogs (DataHub, Amundsen) track metadata, lineage, and data quality.