PrepPilotPractise free

Data Modeling interview questions

OLTP vs OLAP, dimensional modeling, SCDs, lakehouse

17 questions · star schema · updated November 2026

Practise these as flashcards. One or two lines each; the ones you miss come back first.

Start free

Core questions

What is the main difference between OLTP and OLAP systems?

OLTP systems are designed for fast, reliable transaction processing, like inserting or updating records, while OLAP systems are optimized for complex queries and analytics over large datasets. OLTP focuses on data integrity and concurrency, whereas OLAP focuses on query performance and aggregation.

Why do we normalize data in OLTP databases?

Normalization reduces data redundancy and ensures data integrity by organizing data into related tables. This makes updates and inserts more reliable and prevents anomalies.

What are facts and dimensions in a data warehouse?

Facts are numeric measurements or events, like sales amount, while dimensions are descriptive attributes, like product or date, that provide context for the facts.

What is a star schema?

A star schema is a data modeling approach where a central fact table is surrounded by denormalized dimension tables, making queries simple and fast for analytics.

Intermediate questions

When would you choose a snowflake schema over a star schema?

I would choose a snowflake schema if dimension tables are very large or have hierarchical relationships that benefit from normalization, which can save storage and improve maintainability. However, this can make queries more complex and slower due to additional joins.

Explain the concept of grain in a fact table.

The grain defines the level of detail for each row in a fact table, such as one row per product sale per day. Setting the grain clearly is crucial because it determines what questions the table can answer and prevents ambiguity.

What is a Slowly Changing Dimension (SCD) and why is it important?

A Slowly Changing Dimension tracks changes in dimension attributes over time, such as a customer's address. It's important because businesses often need to analyze historical data as it was at different points in time.

Describe the difference between SCD Type 1 and Type 2.

SCD Type 1 overwrites old attribute values with new ones, so history is lost. SCD Type 2 creates a new record for each change, preserving the previous values and enabling historical analysis.

What are the main components of a Data Vault model?

Data Vault consists of Hubs, which store unique business keys; Links, which represent relationships between hubs; and Satellites, which store descriptive attributes and track changes over time.

How does the medallion architecture improve data quality and analytics?

By organizing data into bronze (raw), silver (cleaned), and gold (business-ready) layers, the medallion architecture isolates data quality issues and allows for incremental improvements, making analytics more reliable and maintainable.

How do you implement SCD Type 2 in SQL?

I compare incoming records with current dimension records on key attributes. If a change is detected, I insert a new row with updated values and effective dates, and mark the old row as expired.

How does dimensional modeling improve query performance in OLAP systems?

Dimensional modeling denormalizes data, reducing the number of joins needed for queries and allowing for efficient aggregations, which speeds up reporting and analytics workloads.

What is the purpose of the bronze, silver, and gold layers in a data pipeline?

See the answer in the app, free

Senior questions

How would you model a customer dimension that needs to track address changes for historical reporting?

See the answer in the app, free

What are the trade-offs between Data Vault and dimensional modeling?

See the answer in the app, free

Describe how you would structure a dbt project for a new analytics warehouse.

See the answer in the app, free

How do you handle schema evolution in a lakehouse architecture?

See the answer in the app, free

More Data Engineering topics