Read Articles
← Back to Categories

Data Engineering Interview Questions

Comprehensive list of 30 questions to test your knowledge.

Theory & Concepts

1
ETL vs ELT?
2
Warehouse vs Lake vs Lakehouse?
3
Kimball Dimensional Modeling?
4
SCD Types?
5
Medallion Architecture?
6
Idempotency?
7
CDC?
8
Late-arriving data?
9
Parquet vs CSV?
10
Data Quality?
11
Airflow DAG?
12
CAP theorem?
13
Data Governance?
14
Data Vault?
15
Kafka Topic/Partition?
16
IaC / Terraform?
17
Horizontal vs Vertical scaling?
18
Schema evolution?
19
Lambda vs Kappa Architecture?
20
Data Catalog?
21
OLTP vs OLAP?
22
CI/CD for data?
23
Data Mesh?
24
PII handling?
25
Data backfilling?

Hands-on & Coding Scenarios

1

E-commerce Pipeline Architecture

Context / Sample Data

Source Systems:

1. PostgreSQL (Transactional)
   Tables: users, orders, inventory
   Volume: ~50GB new data/day
   Update frequency: Real-time

2. Kafka (Clickstream)
   Topics: page_views, button_clicks
   Volume: ~500GB/day (~50K events/sec peak)

3. REST API (Zendesk Support Tickets)
   Endpoint: GET /api/v2/tickets
   Polled hourly, returns JSON

Target:
  BI Dashboard for Marketing & Support teams
  Must be updated at least hourly.

Tasks:

  • Design a high-level cloud architecture to ingest and process this data.
  • Model the Gold layer for Marketing conversion analysis.
  • Design an alerting strategy for pipeline failures.

Follow-up / Optimization:

  • Ensure idempotency?
  • Handle API rate limits?
2

Slowly Changing Dimensions (SCD Type 2)

Context / Sample Data

Current customer_dim table:

| customer_sk | customer_id | name  | address  | valid_from | valid_to   | is_active |
|-------------|-------------|-------|----------|------------|------------|-----------|
| 1001        | C1          | Alice | New York | 2023-01-01 | 9999-12-31 | TRUE      |
| 1002        | C2          | Bob   | Chicago  | 2023-03-15 | 9999-12-31 | TRUE      |

Incoming update batch:

| customer_id | name  | address | update_date |
|-------------|-------|---------|-------------|
| C1          | Alice | Boston  | 2024-02-15  |

Tasks:

  • Explain step-by-step how to apply this update as SCD Type 2.
  • Write the SQL MERGE logic.
  • How to generate the surrogate key (customer_sk)?

Follow-up / Optimization:

  • What if updates arrive out of order?
  • Implement in dbt?
3

Airflow DAG Design

Context / Sample Data

Pipeline Tasks:

  extract_api_data  -> Pulls data from REST API
  extract_db_data   -> Reads from PostgreSQL
  transform_data    -> Joins and cleans both sources
  validate_quality  -> Runs data quality checks
  load_warehouse    -> Loads into Snowflake Gold layer
  send_slack_alert  -> Notifies team on failure

Schedule: Daily at 2:00 AM UTC
Requirement: extract tasks run in parallel,
             then transform, validate, load run sequentially.

Tasks:

  • Write the Airflow Python code defining task dependencies.
  • Configure the DAG to run daily at 2:00 AM UTC.
  • Add a Slack alert ONLY if validate_quality fails.

Follow-up / Optimization:

  • What is a Sensor in Airflow?
  • Explain execution_date (logical_date) for backfilling.
4

Data Quality & Dead Letter Queues

Context / Sample Data

Incoming batch of user records:

| user_id | name    | age | email           | status  |
|---------|---------|-----|-----------------|---------|\n| U001    | Alice   | 28  | alice@co.com    | active  |
| U002    | Bob     | 150 | bob@co.com      | active  |
| U003    | null    | 35  | carol@co.com    | pending |
| U004    | Dave    | 42  | dave@co.com     | UNKNOWN |
| U005    | Eve     | 22  | eve@co.com      | active  |

Data Quality Rules:
  1. user_id must be NOT NULL and UNIQUE.
  2. age must be between 18 and 120.
  3. status must be in ['active', 'inactive', 'pending'].

Tasks:

  • Design a system to enforce these rules before data reaches the warehouse.
  • Write pseudocode using dbt tests or Great Expectations.
  • What should the pipeline do if 5% of records fail validation?

Follow-up / Optimization:

  • Track data quality trends over time?
  • What is a Dead Letter Queue (DLQ)?
5

Cloud Infrastructure with Terraform

Context / Sample Data

Requirement:

Provision the following AWS resources for a data pipeline:

1. S3 Bucket: 'company-raw-data-lake'
   - Versioning enabled
   - Lifecycle rule: move to Glacier after 90 days

2. IAM Role: 'data-pipeline-role'
   - Allows read/write to the S3 bucket
   - Allows writing CloudWatch logs

3. EC2 Instance: t3.large
   - Runs Airflow
   - Attached to the IAM role above

Tasks:

  • Write the Terraform block for the S3 bucket.
  • Write the Terraform block for the IAM role and policy.
  • Attach the IAM role to the EC2 instance.

Follow-up / Optimization:

  • Manage Terraform state in a team?
  • terraform plan vs apply?