Interview Questions

Describe strategies for optimizing a slow-running query on a massive dataset.

Discuss strategies for handling schema evolution in data warehouses.

Duplicate characters in a string (e.g., '123a!' to '112233aa!!').

ER Modeling vs. Dimensional Modeling?

Explain CTE vs Temp Table. What are the differences and use cases?

Explain Data Modeling SCD Types (Type 1, 2, 3).

Explain Dynamic Partition Pruning error and how to fix it.

Explain Fact Table and Star Schema.

Explain Redshift Data Distribution (EVEN, KEY, ALL).

Explain Union vs Union All in SQL.

Implement a recursive query for hierarchy (employee-manager). Explain the termination guarantees, depth limits, and when a recursive CTE becomes a scalability bottleneck. What alternatives exist for graph-scale hierarchies in Spark or a data lake?

Compare Glue partition discovery with Hive MSCK/ADD PARTITION. Explain the operational and cost implications of crawler-based vs. partition-projection approaches. When does partition projection become necessary, and what are its limitations?

Explain how partitioning and bucketing in Hive/Spark optimize queries. What are the trade-offs in bucket count, partition cardinality, and small-file problem? When does over-partitioning or over-bucketing become counterproductive?

Explain how to implement cumulative sum in SQL.

Explain how you would implement partitioning and bucketing for data stored in S3 to improve query performance.

Explain how you would optimize Redshift query performance for a reporting system with large fact tables.

Explain how you would use repartition or coalesce effectively to optimize processing when analyzing data only for a specific region.

Explain indexing and its impact on database performance.

Explain normalization and its disadvantages.

Explain offset management, Sync vs. Async commits, partition assignment strategies and Consumer groups, and handling backpressure in Kafka streams.