Real questions on schema design, star and snowflake schemas, dimension/fact tables, normalization vs denormalization for analytics.
Data modeling is foundational to analytics and data engineering. These questions cover dimensional modeling (star and snowflake schemas), fact and dimension tables, normalization vs denormalization tradeoffs, SCD types, and schema design for both OLTP and OLAP use cases. Essential for any data warehouse or lakehouse role.
This collection contains 42 curated questions: 9 easy, 16 medium, and 17 hard. The distribution skews toward harder problems, reflecting the depth expected in senior-level interviews.
The most frequently tested areas in this set are partition (24), join (19), spark (17), optimization (14), sql (10), and etl (8). Focusing on these topics will give you the highest return on your preparation time.
Start with the easy questions to warm up and solidify fundamentals. Medium-difficulty questions form the bulk of real interviews — spend the most time here and practice explaining your reasoning out loud. Hard questions often appear in senior and staff-level rounds; attempt them after you're comfortable with the basics. For each question, try answering before revealing the solution. Use our AI Mock Interview to simulate real interview conditions and get instant feedback on your responses.
What architecture are you following in your current project, and why?
CDC During Migration - explain approaches for real-time Change Data Capture
Explain the differences between a Data Lake and a Data Warehouse.
Explain the differences between Data Warehouse, Data Lake, and Delta Lake
What strategies can you use to handle skewed data in Spark?
Describe the difference between Spark RDDs, DataFrames, and Datasets.
Explain Fact and Dimension Tables with examples.
When would you choose a Snowflake schema over a Star schema?
Detail examples of inner, outer, left, and right joins.
Explain strategies for managing schema changes in PySpark over time.
Explain the Medallion Architecture (Bronze, Silver, Gold layers).
Have you worked on Data Warehousing projects?
How do you drop columns with null values in PySpark?
How do you ensure smooth communication between data scientists, business teams, and developers?
How would you read data from a web API? What steps would you follow after reading the data?
Prioritize Spark optimizations by impact and effort. Discuss partitioning strategy, caching policy, join selection, shuffle reduction, and when each becomes a scalability or cost bottleneck.
Tell me about a time when you faced a challenging situation at work and how you handled it.
What are the key components of AWS Glue, and how do they work together?
What is normalization and denormalization? When would you use each?
What is the difference between Managed and External Tables in Databricks?
What is the difference between OLTP and OLAP?
What is the difference between Spark RDDs, DataFrames, and Datasets?
What is the difference between SQL and NoSQL databases?
What is the purpose of the Bronze, Silver, and Gold layers in a data pipeline?
When and how do you use Broadcast Join?
When would you architecturally choose Dataset[T] over DataFrame in a Scala Spark pipeline, and what are the scalability and portability trade-offs? Include type-safety benefits vs. operational constraints.
Alternatives to the Medallion Architecture
Apache Spark Architecture - RDD, DAG, cluster manager, driver node, worker node
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?
Azure Fabric in Cloud Architecture?
Business generates TBs of data daily. How would you design the data pipeline in Azure?
Can Schema Evolution lead to data inconsistencies? If so, how do you manage them?
Can you explain the trade-offs you made during the design process?
Can you share a time when you had to shift focus due to urgent tasks?
Can you share a time you faced a significant challenge and how you overcame it?
Cloud Architecture - explain
Code a simple PySpark job to read a JSON file, filter records, and write output in Parquet format.
Combine records by name with concatenated course values
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?
Compare OLTP and OLAP systems in the context of financial transactions.
Compare the star schema and snowflake schema. Which one would you use for reporting at Swiggy, and why?
Conflict Resolution - how you handled project conflicts with teammates
A star schema has a central fact table joined directly to denormalized dimension tables — fewer joins, faster queries, simpler for BI tools. A snowflake schema normalizes those dimensions into sub-dimensions, saving storage and reducing redundancy at the cost of more joins. Star is the default for analytics warehouses because query simplicity and speed usually outweigh the storage savings of snowflaking.
A fact table stores measurable, numeric events (sales amount, click count) at a defined grain, plus foreign keys to dimensions. Dimension tables store the descriptive context you filter and group by (customer, product, date, location). Facts are tall and narrow and grow constantly; dimensions are short and wide and change slowly. Defining the fact's grain first is the key modeling decision.
Normalize (OLTP) to eliminate redundancy and keep writes consistent — ideal for transactional systems with frequent updates. Denormalize (OLAP/analytics) to reduce joins and speed up reads — ideal for dashboards and reporting where data is mostly read. Data engineers typically keep normalized source data and build denormalized star schemas or wide tables for analytics consumption.
A surrogate key is a system-generated, meaningless identifier (an auto-increment integer or hash) used as a table's primary key instead of a natural business key. It stays stable when business attributes change, enables SCD Type 2 history (multiple rows per business entity), keeps joins fast with compact integers, and insulates the warehouse from source-system key changes.
Type 0 keeps the original value forever. Type 1 overwrites with the new value, losing history. Type 2 adds a new row per change with effective dates and a current flag, preserving full history (the most common in interviews). Type 3 adds a 'previous value' column for limited history. Type 4 moves history to a separate table. Type 2 is the go-to for auditable dimensions.
The Data Engineering Interview Answer Vault bundles 750+ reviewed answers into 7 focused PDF volumes — SQL, Spark, Python, System Design, Cloud, Behavioral, and Data Modeling. Study on any device, no subscription required.
800+ hands-on courses — Grokking System Design, Coding Patterns, and AI mock interviews for your DE loop.
Turn any topic or your own notes into an interactive, personalized course in 60 seconds.
The book that gets data engineers through system-design rounds. Essential reading.
Some links below are affiliate links. If you buy through them we may earn a small commission at no extra cost to you — it helps keep DataEngPrep free.
Reading answers is step one. Get instant AI feedback on your answers, run mock interviews, and track readiness — built specifically for data engineering interviews.