Question 1

Describe a scenario where partitioning and bucketing would improve query performance.

Accepted Answer

Situation: An events table with billions of rows serving time-range and user-level analytics. Task: Achieve sub-second query latency while controlling storage and compute costs. Why Partitioning: Partition pruning at read time eliminates entire data scans—a query filtering by date_range only touches relevant partition dirs. This reduces I/O by orders of magnitude (e.g., 365 partitions → scan 1 vs all)....

Question 2

What is the small-file problem in Spark, and how do you solve it?

Accepted Answer

Problem: Thousands of tiny files (KB–MB) cause metadata overhead, slow S3/HDFS listing, many small tasks, I/O thrashing. Root causes: High parallelism, over-partitioning, streaming micro-batches. Why it hurts: Each file = task; 10K files = 10K tasks = scheduling overhead. S3 LIST is rate-limited; listing 100K files can take minutes. Solutions: 1) coalesce/repartition before write to target 128MB–1GB per file. 2) Delta Lake/Spark auto-compaction. 3) Larger partition sizes....

Question 3

Implement a query to find the top 5 customers by total sales amount.

Accepted Answer

**Architectural Logic**: Two approaches. 1. GROUP BY + ORDER BY + LIMIT: SELECT customer_id, SUM(sales_amount) total_sales FROM sales GROUP BY customer_id ORDER BY total_sales DESC LIMIT 5. 2. Window: SELECT * FROM (SELECT customer_id, SUM(sales_amount) total_sales, RANK() OVER (ORDER BY SUM(sales_amount) DESC) rk FROM sales GROUP BY customer_id) t WHERE rk <= 5. **Why**: LIMIT is simpler, stops early in some engines....

Question 4

Write an SQL query to find duplicate emails in a users table.

Accepted Answer

**Architectural Logic**: Simple: SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1. With context: SELECT email, COUNT(*) cnt, ARRAY_AGG(user_id) ids FROM users GROUP BY email HAVING COUNT(*) > 1. Window: SELECT DISTINCT email FROM (SELECT email, COUNT(*) OVER (PARTITION BY email) cnt FROM users) t WHERE cnt > 1. **Why**: GROUP BY + HAVING is standard; window useful if you need other columns per duplicate....

Question 5

What is the small-file problem in Spark, and how do you solve it?

Accepted Answer

Small-file problem: Too many tiny files (KB–MB) cause metadata explosion (S3/HDFS list operations), slow scans, and many small tasks. **Root causes**: High parallelism (many partitions), over-partitioning by high-cardinality key, streaming append with small batches. **Why it hurts**: S3 list costs $0.005/1000 requests; 1M files = $5 just for listing. Query engines (Athena, Presto) open each file; latency grows with file count....

Question 6

Why a batch process over real-time?

Accepted Answer

Situation: Batch vs real-time. Task: Trade-off analysis. Action: Why batch: latency allows it; cheaper; simpler; some sources batch-only; exactly-once easier. Real-time when: sub-second needed (fraud); event-driven. Best: start batch; add real-time when needed. Many 'real-time' use cases served by frequent batch (e.g. 5 min)....

Question 7

Glue ETL optimization: Performance improvement strategies?

Accepted Answer

Strategies: (1) Right-size DPUs; auto-scaling. (2) Partition read/write; predicates. (3) Parallelism—parallelCopies, repartition. (4) Parquet/ORC; compact small files. (5) Pushdown at source. (6) Job bookmarks for incremental. (7) Salting for skew. Trade-off: Over-parallelism can throttle source....

Question 8

How to manage AWS IAM roles and policies for data security?

Accepted Answer

Principles: (1) Least privilege. (2) Roles over users for services. (3) SCPs for org; IAM for accounts. (4) Conditions (MFA, IP, tags). (5) Rotate keys; prefer roles. Best practice: Fine-grained policies; MFA for humans; permission boundaries; CloudTrail and Access Analyzer; periodic review.

Daniel Wellington Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies

Daniel Wellington Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies