Data Engineer Technical Interview Questions & Answers (2026)

This is a classic gaps-and-islands problem. I'd use the technique of subtracting a row number from the date to identify consecutive groups. WITH daily_activity AS (SELECT DISTINCT user_id, DATE(activity_timestamp) as activity_date FROM user_events), consecutive_groups AS (SELECT user_id, activity_date, activity_date - INTERVAL '1 day' * ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) as group_id FROM daily_activity) SELECT user_id, MIN(activity_date) as streak_start, MAX(activity_date) as streak_end, COUNT(*) as consecutive_days FROM consecutive_groups GROUP BY user_id, group_id HAVING COUNT(*) >= 3 ORDER BY consecutive_days DESC. How it works: for consecutive dates, subtracting an incrementing row number produces the same value (the 'group_id'). Jan 1 - 1 = Dec 31, Jan 2 - 2 = Dec 31, Jan 3 - 3 = Dec 31 — same group. A gap breaks the pattern: Jan 1 - 1 = Dec 31, Jan 3 - 2 = Jan 1 — different group. The first CTE deduplicates to one row per user per day (a user might have multiple events per day). I'd test with edge cases: user active on exactly 3 days, user with multiple separate streaks, and a single-day activity.

I'd design an ELT pipeline with these stages: Ingestion: Kafka for real-time event streams (clickstream, transactions) with schema registry for contract enforcement. Fivetran or Airbyte for database CDC (change data capture) from operational databases and SaaS API sources. All raw data lands in a cloud storage staging layer (S3/GCS) in Parquet format, partitioned by date. Staging: raw data loaded into the warehouse (Snowflake/BigQuery) 'as-is' in a raw schema. This preserves the original data for debugging and replay. Transformation: dbt for SQL-based transformations organized in layers — staging models (rename, type-cast, deduplicate), intermediate models (business logic joins), and marts (fact and dimension tables for analytics). dbt provides lineage tracking, testing, and documentation. Quality: Great Expectations or dbt tests at each layer checking row counts, null rates, value ranges, and referential integrity. A failed test blocks downstream models. Orchestration: Airflow DAG with sensor → ingest → test → transform → test → publish stages. Each stage is idempotent for safe retries. Monitoring: pipeline latency dashboard, data freshness SLOs (mart tables updated within 2 hours of source events), and PagerDuty alerts for pipeline failures. I chose ELT over ETL because the warehouse handles transformation compute, and dbt provides better developer experience than custom ETL code.

Slowly changing dimensions track how dimension attributes change over time. Type 1: overwrite the old value (lose history). Type 2: add a new row with versioning (preserve full history). Type 3: add columns for old/new values (limited history). For Type 2 implementation, each dimension row has: surrogate_key (auto-increment, used for fact table joins), natural_key (business identifier), all dimension attributes, valid_from (timestamp when this version became active), valid_to (timestamp when replaced, NULL for current), and is_current (boolean flag for easy current-record queries). When an attribute changes: UPDATE the current row setting valid_to = NOW() and is_current = FALSE. INSERT a new row with updated attributes, valid_from = NOW(), valid_to = NULL, is_current = TRUE. In dbt, I'd use the dbt snapshot feature which handles this automatically using either 'check' strategy (compare specific columns) or 'timestamp' strategy (use a last_modified column). Query patterns: current state uses WHERE is_current = TRUE. Point-in-time analysis uses WHERE valid_from <= target_date AND (valid_to > target_date OR valid_to IS NULL). Fact tables join on surrogate_key to get the dimension values that were active when the fact event occurred. Trade-off: Type 2 grows the dimension table over time and complicates queries, but provides complete historical analysis capability.

I approach data quality at three levels: Prevention: schema enforcement at ingestion (Kafka schema registry rejects messages that don't match the expected schema), data contracts between teams defining expected formats and SLAs, and input validation in source applications. These prevent bad data from entering the pipeline. Detection: automated tests at each pipeline layer. I'd implement: row count checks (alert if today's count deviates >20% from the 7-day average), null rate monitoring (alert if a required column exceeds 1% nulls), value range checks (negative ages, future dates), referential integrity tests (fact table foreign keys match dimension tables), and freshness checks (alert if source data is >2 hours stale). I'd use dbt tests for transformation-layer checks and Great Expectations for ingestion-layer checks. Remediation: when quality issues are detected, the pipeline should: 1) quarantine bad records in a separate table for investigation (never silently drop data), 2) alert the on-call data engineer via PagerDuty with the specific test failure and sample bad records, 3) continue processing good records if possible (degrade gracefully rather than halt the entire pipeline), 4) provide a backfill mechanism to reprocess data once the upstream issue is fixed. I'd also maintain a data quality dashboard showing quality metrics over time, making it easy to spot degradation trends before they become incidents.

Batch processing handles bounded data sets on a schedule (hourly, daily). Tools: Spark, dbt, Airflow. Strengths: simpler to reason about (process all data, then done), easier error handling (rerun the batch), cost-efficient (use spot instances, process during off-peak), and well-suited for aggregations and complex joins across large datasets. Stream processing handles unbounded data in real-time. Tools: Kafka Streams, Flink, Spark Streaming. Strengths: low latency (seconds to minutes), event-driven architecture, and continuous processing. Weaknesses: harder to debug (state management, exactly-once semantics), more expensive (always-on compute), and complex windowing logic for aggregations. Choose batch when: latency of hours is acceptable, you need complex transformations across large datasets, cost efficiency matters, or the downstream consumers (BI dashboards, reports) are refreshed periodically. Choose streaming when: low latency is required (fraud detection, real-time personalization, alerting), events need immediate action, or the data naturally arrives as a stream. Hybrid (Lambda/Kappa architecture): many modern pipelines use both. Stream processing handles real-time use cases with approximate results, while batch processing runs the same logic nightly to produce exact results that reconcile any streaming approximations. I prefer the Kappa approach (single streaming pipeline with replay capability) when the team has streaming expertise, as it's simpler to maintain one codebase.