Interview Questions

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

Explain Coalesce vs ISNULL. What are the differences in SQL?

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

Explain Dynamic Partition Pruning error and how to fix it.

Explain ETL process flags and segregation of steps.

Explain Fact Table and Star Schema.

Explain Kafka messaging guarantees and Snowflake schema evolution.

Explain Native vs. External Tables.

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

Explain Slowly Changing Dimensions (SCD) and its types

Explain Streams and Tasks in Snowflake.

Explain Time Travel in Snowflake.

Explain Triggers in SQL with examples and scenarios for use.

Explain Union vs Union All in SQL.

Explain a project where you had to influence stakeholders without having authority.

Articulate the architectural decisions, scalability trade-offs, and cost implications of designing an AWS data platform. How would you justify glue vs. EMR, Redshift vs. Athena, and when would each choice become cost-prohibitive at scale?

Explain the architectural rationale for using LeftAntiJoin vs. NOT IN vs. NOT EXISTS in a distributed context. When does LeftAntiJoin become a performance or scalability bottleneck, and how do broadcast vs. shuffle joins affect cost?

Describe a cross-team data project where you had to align architectural boundaries, ownership, and SLAs. How did you handle conflicting priorities, technical debt, and the scalability of communication as the number of stakeholders grew?

Walk through a production incident where data freshness or correctness was at risk. How did you balance immediate mitigation vs. root-cause remediation? What architectural changes would prevent recurrence, and what are the cost vs. reliability trade-offs?

Explain the architectural trade-offs when optimizing a query on 100M+ rows: indexing vs. partitioning vs. materialized views. When does each approach become cost-prohibitive or operationally burdensome, and how do you quantify impact?