Data Engineering interview questions

Data engineering: designing, building, and maintaining the infrastructure and pipelines that collect, store, transform, and serve data reliably at scale. Core responsibilities: - Build and maintain data pipelines (ETL/ELT) - Design data warehouse/lake/lakehouse schemas - Ensure data quality, reliability, and freshness - Optimise query performance - Manage data infrastructure (Spark clusters, Kafka, Airflow) Data engineer vs Data scientist: | Aspect | Data Engineer | Data Scientist | | Focus | Infrastructure, pipelines | Analysis, modelling | | Output | Clean, reliable data | Insights, models | | Skills | SQL, Python, Spark, Kafka, Cloud | Python, R, ML, statistics | | Tools | Airflow, dbt, Spark, Snowflake | Pandas, scikit-learn, TensorFlow | | Analogy | Builds the roads and plumbing | Drives the car | Data engineer vs Data analyst: - Analyst: queries existing data to find insights (SQL, Tableau) - Engineer: builds and maintains the systems that make the data available to analysts Why it matters: a data scientist without a data engineer is like a chef without a kitchen — the ingredients (raw data) exist but are unprocessed, unreliable, and inaccessible. Modern data engineering stack: - Ingestion: Kafka, Fivetran, Airbyte - Storage: S3, GCS, HDFS - Processing: Spark, dbt, Flink - Warehouse: Snowflake, BigQuery, Redshift - Orchestration: Airflow, Prefect, Dagster - Serving: Postgres, Elasticsearch, Redis

ETL (Extract, Transform, Load): 1. Extract data from source (API, DB, files) 2. Transform in a processing engine (clean, join, aggregate) — BEFORE loading 3. Load transformed data into target (warehouse, DB) Used when: - Data must be cleaned/masked before entering the warehouse (compliance/PII) - Target system has limited compute (loading raw data would be too expensive) - Traditional on-premise data warehouses (Teradata, Oracle) where compute is expensive ELT (Extract, Load, Transform): 1. Extract from source 2. Load RAW data directly into the warehouse (S3, BigQuery, Snowflake) 3. Transform inside the warehouse using SQL (dbt, stored procs) Used when: - Cloud data warehouses (BigQuery, Snowflake, Redshift) provide cheap, scalable compute - Raw data preservation is important (transform later, replay if logic changes) - dbt is the transformation layer — SQL-based, version-controlled Modern preference: ELT with dbt on cloud warehouses - Raw data is stored (reproducible) - dbt transforms using SQL — version-controlled, tested, documented - Warehouse compute is elastic and cheap Key difference table: | | ETL | ELT | | Transform where? | Before loading (external) | In the warehouse (SQL) | | Raw data stored? | Sometimes not | Always | | Tools | Informatica, SSIS, Spark | dbt, Snowflake, BigQuery | | Flexibility | Less (hard to re-transform) | More (re-run transforms anytime) | Reverse ETL: opposite direction — move warehouse data back to operational systems (CRM, marketing tools). Tools: Census, Hightouch.

Data Warehouse: - Structured, curated data optimised for analytical queries (OLAP) - Schema-on-write: data must conform to schema before loading - High query performance via columnar storage, partitioning, clustering - Expensive per TB - Tools: Snowflake, BigQuery, Redshift, Synapse Data Lake: - Stores raw, unstructured/semi-structured data cheaply (S3, GCS, ADLS) - Schema-on-read: schema applied when data is queried - Cheap storage, flexible, preserves original data - Performance can be poor without organisation - Tools: S3 + Athena, HDFS + Hive, Azure Data Lake Problems with lakes: slow queries, no ACID transactions, no versioning — called "data swamps" when poorly managed. Data Lakehouse (modern approach): - Combines lake (cheap storage) + warehouse (ACID, performance, governance) - Open table formats (Delta Lake, Apache Iceberg, Apache Hudi) bring warehouse features to object storage - Schema enforcement + ACID transactions on S3/GCS - Supports both structured SQL queries AND ML workloads on the same data - Tools: Databricks (Delta Lake), Snowflake + Iceberg, AWS Glue + Iceberg Comparison: | | Warehouse | Lake | Lakehouse | | Data format | Structured | Any | Any | | Schema | Write-time | Read-time | Both | | Transactions | Yes | No | Yes | | Cost | High | Low | Medium | | ML support | Limited | Yes | Yes | | Query perf | High | Low | High | Recommended for new projects: Lakehouse (Delta Lake on Databricks, or Iceberg on S3 + query engine)

Apache Spark: a unified analytics engine for large-scale data processing. Processes data in memory (100x faster than MapReduce for many workloads). Core components: - SparkContext / SparkSession: entry point, coordinates the application - Driver: process where your Spark code runs; creates the logical execution plan - Cluster Manager: allocates resources (YARN, Kubernetes, Mesos, Spark Standalone) - Executors: JVM processes on worker nodes that do the actual computation - Tasks: individual units of work sent to executors Execution flow: 1. User code defines transformations on RDD/DataFrame/Dataset 2. Driver creates a logical plan (DAG of operations) 3. Catalyst optimizer transforms logical plan to physical plan 4. Tungsten engine generates optimised bytecode 5. DAGScheduler splits the plan into stages (split at shuffle boundaries) 6. TaskScheduler sends tasks to executors 7. Executors process data partitions, return results to driver Two types of operations: - Transformations (lazy): filter, map, select, join, groupBy — do nothing until an action is called; build the DAG - Actions (eager): collect, count, show, write — trigger actual execution Lazy evaluation benefit: Spark can optimise the entire pipeline before running. Key abstractions (API evolution): - RDD (Resilient Distributed Dataset): low-level, functional, fault-tolerant - DataFrame: distributed table with named columns (schema); SQL-like API; optimised by Catalyst - Dataset: type-safe DataFrame (Scala/Java); mostly subsumed by DataFrame in PySpark Resilience (fault tolerance): - RDD lineage: if a partition is lost, re-compute from lineage - Checkpointing: save RDD/DataFrame to disk for long lineages

Transformations: operations on a DataFrame/RDD that return a new DataFrame/RDD. They are LAZY — they don't execute immediately but build a logical execution plan (DAG). Common transformations: df.filter(col("age") > 18) df.select("name", "email") df.withColumn("full_name", concat(col("first"), lit(" "), col("last"))) df.groupBy("city").agg(count("*").alias("user_count")) df.join(other_df, "user_id", "left") df.dropDuplicates(["email"]) df.orderBy(col("score").desc()) df.limit(1000) Narrow vs wide transformations: - Narrow: each input partition contributes to one output partition (map, filter, select). No shuffle needed. Fast. - Wide: input partitions contribute to multiple output partitions (groupBy, join, distinct). Requires shuffle. Expensive. Actions: operations that trigger actual execution of the accumulated plan and return a result. Common actions: df.show(20) // print first 20 rows (triggers execution) df.count() // count rows df.collect() // bring all data to driver as a list (caution: OOM for large datasets) df.first() // get first row df.write.parquet("s3://bucket/output") // write to storage Lazy evaluation benefits: 1. Optimisation: Catalyst optimiser can reorder, combine, and eliminate unnecessary operations e.g., filter pushed before join → join on smaller dataset 2. Fault tolerance: lineage allows re-computation of lost partitions 3. Efficiency: no intermediate results materialised unless needed Example: df.filter(col("country") == "IN") # lazy .join(products, "product_id") # lazy .groupBy("category").count() # lazy .show() # ACTION — executes everything above Without lazy evaluation, each line would execute separately and materialise intermediate DataFrames.

Shuffle: redistributing data across partitions/nodes so that all records with the same key end up on the same partition. Required for wide transformations (groupBy, join, distinct, repartition). Why shuffles are expensive: - Serialise data to disk (spill if memory insufficient) - Transfer data over the network to target executors - Deserialise on target executor - Can cause: disk I/O bottleneck, network saturation, OOM errors, long GC pauses Shuffle-triggering operations: - groupBy, agg - join (most expensive) - distinct, dropDuplicates - repartition (but NOT coalesce to fewer partitions) - orderBy / sort (requires global sort across partitions) How to minimise shuffles: 1. Filter and select early (reduce data volume before shuffle): df.filter(col("active") == True).select("id","email").groupBy("city").count() // Filter + select first, then groupBy → smaller shuffle 2. Broadcast join (eliminates shuffle for small table): from pyspark.sql.functions import broadcast df_large.join(broadcast(df_small), "user_id") // Small table sent to ALL executors — no shuffle needed // Use when one table fits in memory (~10MB-2GB depending on broadcast.threshold config) 3. Bucket join (pre-partitioned tables, no shuffle needed): // Write both tables bucketed by the join key at write time // When reading and joining on same bucket key → no shuffle 4. Choose right spark.sql.shuffle.partitions: // Default 200 — too many for small data, too few for large spark.conf.set("spark.sql.shuffle.partitions", "400") // tune to ~2-4x CPU cores 5. AQE (Adaptive Query Execution, Spark 3.0+): spark.conf.set("spark.sql.adaptive.enabled", "true") // Auto-merges small partitions, converts sort-merge joins to broadcast joins dynamically

Apache Kafka: a distributed event streaming platform designed for high-throughput, fault-tolerant, real-time data pipelines and streaming applications. Core concepts: Topic: a named log of events. Think of it as an append-only log file. Producers write to it; consumers read from it. Partition: each topic is divided into ordered, immutable partitions. Partitions enable: - Parallelism: multiple consumers read different partitions simultaneously - Ordering: ordering is guaranteed within a partition (not across partitions) - Scalability: distribute across multiple brokers Producer: sends messages to a topic. Key decisions: - Partitioner: round-robin (default), or by message key (same key → same partition → ordered per key) - Acknowledgement level: acks=0 (fire-and-forget), acks=1 (leader ack), acks=all (all replicas ack) Consumer: reads messages from a topic starting at an offset. Key properties: - Offset: position in the partition. Consumers track their own offset. - Consumer group: multiple consumers sharing a topic; each partition assigned to exactly one consumer. Enables parallel consumption. - Rebalance: when a consumer joins/leaves, partitions are reassigned Message retention: messages persist on disk for a configurable period (default 7 days), regardless of whether they've been consumed. Consumers can replay from any offset. Broker: a Kafka server. A cluster has multiple brokers. Each partition has a leader broker (handles reads/writes) and replica brokers (fault tolerance). Key Kafka guarantees: - At-least-once delivery (default) - Exactly-once (with idempotent producer + transactional API) - Ordering within a partition Real use case: user activity events (clicks, page views) → Kafka → real-time analytics + Spark Streaming + Elasticsearch.

Consumer group: a set of consumers cooperating to read a topic. Kafka assigns each partition to exactly one consumer in the group. Rules: - One partition → assigned to exactly one consumer per group - One consumer → can handle multiple partitions - Number of consumers > number of partitions → extra consumers are idle - Number of consumers < number of partitions → some consumers handle multiple partitions Example: topic with 6 partitions, consumer group with 3 consumers: - Consumer 1: partitions 0, 3 - Consumer 2: partitions 1, 4 - Consumer 3: partitions 2, 5 Scale out: add more consumers (up to partition count) to increase parallelism. Scale up: add more partitions (and proportionally scale consumers). Multiple consumer groups: - Different groups each get their own independent offset cursor - Group A (analytics service) reads at offset 1000; Group B (email service) reads at offset 500 - Neither affects the other — each group maintains its own state - This is why Kafka is described as a "publish-subscribe + queue" hybrid Rebalancing: - Triggered when a consumer joins, leaves, or crashes - Protocol: Group Coordinator (a broker) detects change → triggers rebalance → partitions reassigned - During rebalance: all consumers in the group stop consuming (stop-the-world) - Static membership (Kafka 2.3+): reduces rebalances when consumers restart (uses static group.instance.id) Offset management: - Auto commit (enable.auto.commit=true): commits at intervals — risk of duplicate processing on crash - Manual commit (recommended for at-least-once): commitSync() after processing each batch

Apache Airflow: an open-source workflow orchestration platform for authoring, scheduling, and monitoring data pipelines programmatically. Problem it solves: - Data pipelines have dependencies: "run Step B only after Step A succeeds" - Without orchestration: cron jobs with no visibility, no retry, no dependency management - Complex pipelines need: scheduling, retries on failure, alerting, logs, monitoring, backfilling Core concept — DAG (Directed Acyclic Graph): - Each pipeline is a DAG of tasks - Tasks: individual units of work (run a SQL query, call an API, trigger a Spark job) - Edges: dependencies between tasks - DAGs are defined in Python code Example DAG: from airflow import DAG from airflow.operators.python import PythonOperator from airflow.providers.amazon.aws.operators.s3 import S3CreateObjectOperator from datetime import datetime with DAG('daily_user_pipeline', start_date=datetime(2024,1,1), schedule='@daily') as dag: extract = PythonOperator(task_id='extract', python_callable=extract_users) transform = PythonOperator(task_id='transform', python_callable=transform_users) load = PythonOperator(task_id='load', python_callable=load_to_warehouse) extract >> transform >> load # dependency chain Airflow components: - Scheduler: parses DAGs, triggers tasks when schedule + dependencies met - Executor: runs the tasks (LocalExecutor, CeleryExecutor, KubernetesExecutor) - Webserver: UI for monitoring runs, viewing logs, triggering manually - Metadata DB: stores DAG/task state (PostgreSQL, MySQL) - Workers: processes executing the tasks (for CeleryExecutor) Key features: - Backfilling: run historical date ranges easily - Retries: automatic retry with configurable delay - Sensors: wait for external conditions (file arrival, partition ready) - XCom: pass data between tasks - Connections: encrypted credentials for databases, APIs, cloud Alternatives: Prefect, Dagster, Luigi, Temporal.

dbt (data build tool): an open-source transformation framework that lets data engineers and analysts write SQL SELECT statements (models), and dbt handles materialising them as tables/views in the warehouse, with testing and documentation built in. Philosophy: ELT — load raw data into warehouse, then transform in-warehouse using dbt. Core concepts: Models: SQL SELECT files (one file = one table/view in the warehouse) -- models/staging/stg_users.sql SELECT user_id, lower(email) AS email, created_at::date AS signup_date FROM {{ source('raw', 'users') }} WHERE deleted_at IS NULL dbt compiles this to: CREATE TABLE stg_users AS SELECT... Materialisation types: - view (default): CREATE OR REPLACE VIEW - table: DROP + CREATE TABLE (full refresh) - incremental: INSERT only new/changed rows (huge performance gain for large tables) - ephemeral: CTE, not materialised, only used by downstream models ref() — dependency declaration: -- models/marts/user_lifetime_value.sql SELECT u.user_id, sum(o.amount) as ltv FROM {{ ref('stg_users') }} u JOIN {{ ref('stg_orders') }} o USING (user_id) GROUP BY 1 dbt builds a DAG from ref() calls and runs in dependency order. Tests (built-in + custom): # schema.yml models: - name: stg_users columns: - name: user_id tests: [unique, not_null] - name: email tests: [not_null] # dbt test — runs all tests Documentation: # Add descriptions in schema.yml # dbt docs generate + dbt docs serve → browsable data catalog Running: dbt run # compile + execute all models dbt test # run all tests dbt run --select stg_users+ # run model + all dependents Integrates with Airflow (DbtOperator), Fivetran, Snowflake, BigQuery, Redshift, PostgreSQL.

Incremental processing: instead of reprocessing the entire dataset every run, only process new or changed records since the last run. Essential for large tables. Why incremental matters: - A table with 1 billion rows and 100K new rows per day: full scan = 1 hour; incremental = 1 minute Incremental in dbt: {{ config(materialized='incremental', unique_key='order_id') }} SELECT order_id, user_id, amount, status, updated_at FROM {{ source('raw', 'orders') }} {% if is_incremental() %} -- Only include rows newer than the last run WHERE updated_at > (SELECT max(updated_at) FROM {{ this }}) {% endif %} - On first run: builds entire table - Subsequent runs: only selects new/updated rows, merges by unique_key Incremental strategies in dbt: - append: insert new rows only (no deduplication) - merge (upsert): update existing + insert new (requires unique_key) - delete+insert: delete old partition, insert new data Incremental in Spark (Structured Streaming): - Watermarks: track maximum event timestamp seen; tolerate late-arriving events up to N minutes df.withWatermark("event_time", "10 minutes") .groupBy(window(col("event_time"), "5 minutes"), col("user_id")) .count() - Checkpointing: saves Structured Streaming state to durable storage so stream can resume after failure CDC (Change Data Capture) for incremental ingestion: - Captures database changes (INSERT/UPDATE/DELETE) as a stream - Tools: Debezium (reads Postgres/MySQL WAL → Kafka), AWS DMS - Enables keeping warehouse in near-real-time sync with operational DB Snapshot table in dbt (SCD Type 2): dbt snapshot captures historical changes and adds valid_from/valid_to columns.

Partitioning: physically dividing a large dataset into smaller, independent segments based on the values of one or more columns. Enables partition pruning — skipping irrelevant data at query time. Why partitioning matters: - Table with 10 years of daily events: 3650 partitions - Query for last 7 days: only scans 7 partitions (0.2% of data) instead of everything - Massive reduction in I/O and compute Common partition columns: - Date/time: by year, month, day (most common for time-series data) - Region/country: for geo-sharded data - Status/type: for low-cardinality categorical columns Partition strategies: Date partitioning (most common): # In Spark: df.write .partitionBy("year", "month", "day") .parquet("s3://bucket/events/") # Resulting directory structure: # s3://bucket/events/year=2024/month=03/day=15/*.parquet # Query — only scans 2024/03/15 partition: spark.read.parquet("s3://bucket/events/") .filter((col("year") == 2024) & (col("month") == 3) & (col("day") == 15)) In BigQuery: CREATE TABLE events PARTITION BY DATE(event_date) AS SELECT * FROM raw_events; In Snowflake (micro-partitioning — automatic, no manual declaration needed): -- Snowflake auto-partitions all tables; use cluster keys for query patterns: ALTER TABLE events CLUSTER BY (event_date); Bucketing (Spark) — complements partitioning: - Buckets data by hash of a column into N buckets - When two tables are bucketed by the same key + column, join = no shuffle df.write.bucketBy(32, "user_id").sortBy("user_id").saveAsTable("events_bucketed") Anti-patterns: - Too many partitions (each <10MB): too many small files = slow listing + metadata overhead - Wrong partition key (high cardinality column): millions of tiny partitions - Partition skew: one partition with 90% of data → one executor does 90% of work