Data Engineering Interview Questions — Dimensional Data Modeling
Reviewed by Mark Dickie · Last updated
4 questions on this page you can answer and see marked on the spot. Go to the first one
Dimensional data modeling is the design approach that organizes a data warehouse into fact and dimension tables so business users can query large volumes of data quickly and intuitively. For interviews, you should be able to explain the difference between star and snowflake schemas, identify the grain of a fact table, and walk through each Slowly Changing Dimension (SCD) type and when to apply it. Interviewers also expect you to reason about surrogate vs. natural keys, conformed dimensions across subject areas, and factless fact tables.
What does a dimensional data modeling interview test?
Most questions probe whether you can translate business requirements into a physical schema and defend your design choices. You will be asked to identify the grain, pick the right fact table type, decide on SCD handling for a given column, and explain trade-offs between query performance and storage. Some questions add a twist: a rapidly growing dimension, a bridge table for many-to-many relationships, or a requirement to track historical attribute changes across a reporting period.
| Concept | What the interviewer wants you to cover |
|---|---|
| Star vs. snowflake | Query performance, join complexity, when denormalization helps or hurts |
| Fact table grain | The lowest level of detail stored, why grain must be declared before adding measures |
| SCD types | How Type 1, 2, and 3 handle attribute changes and the reporting implications of each |
| Conformed dimensions | Dimensions shared across fact tables, consistency, and master data management |
| Surrogate keys | Why they are generated in the warehouse instead of reusing source-system keys |
| Factless fact tables | Coverage tables and event tables with no measures, just dimensional references |
Which SCD type should I choose for a given column?
The answer depends on whether history matters and how you want to report on the change. A common interview pattern is a customer dimension where the address or sales-region assignment changes over time and the business needs to attribute revenue to the region that was active at the time of the sale.
- Identify the business requirement: do they need historical tracking, or is the current value enough?
- If only the current value matters, apply Type 1 (overwrite the attribute in place).
- If history matters and you need to query by both old and new values, apply Type 2 (add a new row with effective dates and a current flag).
- If you only need to track the previous value for a limited set of attributes, apply Type 3 (add a previous-value column).
- For mixed requirements, split columns across types in the same dimension table rather than forcing one strategy everywhere.
What are the fact table types I should know?
Interviewers expect you to name and distinguish the three core fact table types, then pick the right one for a scenario. Transaction fact tables record one row per event at a point in time and are the most common. Periodic snapshot fact tables capture a state at regular intervals (end-of-day inventory balances, monthly account totals). Accumulating snapshot fact tables represent a process with a defined start and end, updating the same row as milestones occur (order placement through fulfillment). Knowing when to use each type lets you model pipelines, inventories, and long-running workflows correctly.
Key facts
- Tarmac has 38 Data Engineering interview questions on this topic, 10 of them on this page, at difficulty 1–5 of 5.
- Tarmac tracked 1,538 job postings asking for Data Engineering in September 2026.
- Roles asking for Data Engineering advertise a median base salary of £75,000, across 448 job postings as of September 2026.
- Tarmac last reviewed these Data Engineering interview questions on 19 October 2026.
At a glance
| Questions | 10 shown · 38 in the bank |
|---|---|
| Difficulty | 1–5 of 5 |
| Formats | Multiple choice, Ordering, Multiple answer, Code output, True / false, Find the bug, Short answer, Fill in the blank, Coding exercise, Flashcard |
| Interactive | 1 run your code against tests, in the app |
What you'll review
- fact dimension tables
- star snowflake schema
- slowly changing dimensions
Practice questions
Try one before you open the answer. Pick an option and press Check; it's marked on the spot.
In a dimensional model (star schema), which statement best describes a fact table?#
Options
Show answer
A fact table stores measurable business events such as sales or orders, containing numeric measures and foreign keys that link to dimension tables. Descriptive attributes like customer name or product category live in dimension tables instead, which is what fact tables are joined to for filtering and grouping.
Fact tables record measurable events (for example, each sale line item) at a declared grain. They hold numeric measures such as quantity and amount, plus foreign keys pointing to dimension tables like date, customer, and product. Descriptive attributes belong in dimension tables, not fact tables, and overwriting latest state describes slowly changing dimension handling rather than facts. Staging tables are a loading concept, not a modeling role.
Put the steps of Kimball's four-step dimensional modeling process for a single fact table into the correct order, from first to last.#
Put these in order
Show answer
The Kimball four-step dimensional design process runs: choose the business process, declare the grain, identify the dimensions, then identify the facts. The grain must be fixed before dimensions and measures, because it defines what one fact row represents and which attributes and numeric values are valid at that level.
Kimball's four-step process starts by selecting the business process to model, because everything else depends on which events are being captured. The grain must be declared next, since it defines exactly what a single fact row represents and therefore which dimensions and measures are valid. With the grain fixed, the dimensions that describe each event can be identified, and only then are the measures chosen, since each measure must be recorded at that declared grain.
In a star schema, which of the following statements about fact tables and dimension tables are correct? Select all that apply.#
Options
Pick every one that applies.
Show answer
A fact table in a star schema stores measurable numeric events at a declared grain, and its foreign keys reference the surrogate keys of its dimension tables. Descriptive text attributes belong in dimension tables instead. Fact tables usually hold far more rows than their dimensions, since they grow with every recorded event.
A fact table records measurable events (such as sales amounts or quantities) at a declared grain, meaning one row represents one specific event or measurement. It connects to dimension tables through foreign keys that point to the dimensions' surrogate keys. Descriptive attributes like customer name or region belong in dimension tables, not the fact table. Fact tables are usually the largest tables in the schema because they accumulate one row per event, so they typically have far more rows than their dimensions, not fewer.
A star schema has a fact table fact_sales and a dimension table dim_product. The data is below (SQLite syntax):#
SELECT SUM(f.amount)
FROM fact_sales f
LEFT JOIN dim_product d ON f.product_key = d.product_key
WHERE d.category IS NULL;Show answer
5.0
The LEFT JOIN keeps every fact row. Sales 1, 2, 3 and 5 match a product and get a non-NULL category. Sale 4 has a NULL product_key, so it matches no dimension row and its category is NULL, which is the only row passing the IS NULL filter. The sum of its amount, 5.0, is the result. This is the orphaned-fact-row case: an inner join would silently drop it, while a left join makes it visible.
A periodic snapshot fact table stores one row per account per day, with a balance measure holding the account's end-of-day balance. Summing balance across all days in a month gives the meaningful total balance for that month.#
Options
Show answer
False. Summing a daily account balance across the days of a month does not produce a meaningful total, because balances are semi-additive. They can be summed across accounts at one point in time, but across time you need the period-end value or an average.
The balance measure is semi-additive: it can be summed across accounts at a single point in time, but not across the time dimension. Adding end-of-day balances for each day of a month double-counts the same money many times. The meaningful month-level figure is an end-of-period value (for example, the last day's balance) or an average across days, not a sum. Summing across the account dimension is valid; summing across the date dimension is not.
A star schema has a Type 1 product dimension dim_product(product_key INT PRIMARY KEY, product_id VARCHAR, product_name VARCHAR), where product_key is the surrogate key and product_id is the natural key, with exactly one row per product. The fact table fact_sales(product_key INT, sale_date DATE, amount DECIMAL(12,2)) must reference the dimension by surrogate key. The following ETL statement loads the fact table from a staging table orders(product_id, order_date, amount). Which line contains the bug? Give the 1-based line number.#
INSERT INTO fact_sales (product_key, sale_date, amount)
SELECT p.product_id, o.order_date, o.amount
FROM orders AS o
JOIN dim_product AS p
ON o.product_id = p.product_id;Show answer
The bug is on line 2.
The fact table's product_key column is meant to hold the dimension's surrogate key, so the SELECT must project p.product_key. Line 2 instead projects p.product_id, the natural key (a VARCHAR such as 'SKU-123'). That value either fails the type/foreign-key constraint against the integer surrogate key or, if it happens to be numeric, silently points at the wrong dimension row. The join on line 5 is correct for a Type 1 dimension with one row per natural key, so the defect is only the projected column on line 2.
A warehouse has a fact table fact_order_line at the grain of one row per order line, with columns order_line_id, customer_sk, order_ts, amount. It references a dim_customer table that is a Type 2 slowly changing dimension with columns customer_sk (surrogate key), customer_id (business key), address, valid_from, valid_to, and is_current. Each new version of a customer gets a new customer_sk. Order lines can arrive in the warehouse up to 3 days after order_ts because of upstream batch delays.#
Show answer
The load job should not look up the customer by is_current = true. For each fact row it should join on the business key customer_id and select the dimension version whose validity window contains the event timestamp, i.e. order_ts >= valid_from AND order_ts < valid_to (with the open-ended current row treated as valid_to = max date). The customer_sk of that matched row is written into the fact row, so the fact stores a point-in-time (as-of) reference. This works for late-arriving rows because the lookup is driven by the event timestamp, not the load time, and the full version history must be retained so that older versions still exist when a late row arrives. If the job used is_current = true, every historical order for a customer who later moved would be attributed to the new address's surrogate key, so historical reporting by address (or by any attribute that changed) silently restates past sales to the customer's current attributes, and late rows that predate a change would be keyed to a version that did not yet apply at the time of sale.
The core issue is that a Type 2 dimension stores history as versioned rows, and a fact row must bind to the version that was valid at the moment of the business event. Matching on the business key together with the validity range against the event timestamp gives a point-in-time (as-of) key, which is correct even when the row arrives days late, provided the dimension history is retained and not overwritten. Using is_current instead binds every fact to the latest version, so changes in attributes such as address are retroactively applied to all past facts, which misstates historical analysis.
A customer dimension that would be one denormalized table in a star schema is instead split into separate normalized tables: customer → city → state → country, each linked by a foreign key. This layout is called a _____ schema. Compared with the star layout, it reduces storage redundancy in the dimension tables but adds more _____ to every query that needs geography attributes.#
Show answer
A customer dimension that would be one denormalized table in a star schema is instead split into separate normalized tables: customer → city → state → country, each linked by a foreign key. This layout is called a snowflake schema. Compared with the star layout, it reduces storage redundancy in the dimension tables but adds more joins to every query that needs geography attributes.
Normalizing a dimension into a chain of related lookup tables (customer, city, state, country) produces a snowflake schema, because the dimension branches out like a snowflake's arms. A star schema keeps each dimension as one wide, denormalized table, so it avoids that chain. The normalized form saves storage by storing each state or country name only once, but any query that needs those attributes must join the extra tables, so the cost shows up as additional joins at query time.
You are modeling a Type 2 slowly changing dimension in SQLite. The table dim_customer holds one row per customer version. valid_from is inclusive and valid_to is exclusive; a NULL valid_to means the version is still current. The table fact_orders holds one row per order, with order_ts as the time the order was placed.#
Starter code
SELECT f.order_id, d.customer_sk
FROM fact_orders f
LEFT JOIN dim_customer d
ON d.customer_id = f.customer_id
ORDER BY f.order_id;Your solution must pass
- default history: boundary at valid_to is exclusive, unknown customer gets NULL
This one is written and run, not read. Solve it in the app and your code is executed against these tests and the hidden ones.
In a Kimball-style star schema that uses a Type 2 slowly changing dimension (surrogate keys, effective-dated rows), a fact row arrives with business key C-17 and event time T, but no dimension row for C-17 valid at time T has been loaded yet (a late-arriving dimension). What is the standard technique for loading the fact, and how is the placeholder later reconciled when the real dimension record arrives?#
Show answer
Insert an 'inferred member' placeholder dimension row for C-17 with a new surrogate key, so the fact can reference it immediately. Flag the row as inferred and populate only the business key with default or unknown attributes. When the real C-17 record arrives, update the inferred row in place (Type 1 style overwrite of its attributes, setting its effective dates and clearing the inferred flag) rather than creating a new surrogate key, so existing facts keep their valid foreign key.
Late-arriving dimensions are a classic Type 2 edge case: the fact load cannot wait for the dimension, and it cannot be joined to a wrong version. Kimball's answer is the inferred member: a placeholder row carrying the business key and an inferred flag, which gives the fact a valid surrogate key. The key subtlety is reconciliation. Because facts already point at the placeholder's surrogate key, the arriving dimension data must overwrite that same row, not generate a new surrogate key, which would orphan or misattribute the already-loaded facts. The effective date range of the real record is then set so that point-in-time lookups for T resolve to the corrected attributes.
Related interview questions
Job market
See data-engineering salaries and hiring demand from live job postings.
The other 28 questions
This page shows 10 and marks what you pick. That's as far as a page can go. A free account opens the other 28 and keeps every answer. What you miss comes back until it's right: after a day, then at longer gaps.
Free · the whole bank · 100 marked answers per 30 days · written feedback on the paid plan