Prepare for data engineering interviews with practical explanations of dimensional modeling, star and snowflake schemas, SCD Type 2, late-arriving data, many-to-many relationships, fact table grain, and denormalization.
1. How would you design a dimensional model for an e-commerce business?
Interview answer: Begin with the business processes and analytical questions, not the table names. For e-commerce, typical processes are order placement, payment, shipment, return, and inventory snapshots. Define a separate fact table at the appropriate grain for each process and connect it to conformed dimensions.
Example dimensional model
| Table | Grain | Example columns |
|---|---|---|
| fact_order_line | One line item on an order | order_id, line_id, customer_key, product_key, order_date_key, quantity, net_sales |
| fact_payment | One payment transaction | payment_id, order_id, payment_date_key, amount, status |
| fact_inventory_daily | One product-location-day snapshot | product_key, warehouse_key, date_key, on_hand_qty |
| dim_customer | One customer version | customer_key, customer_id, segment, city, effective dates |
| dim_product | One product version | product_key, sku, category, brand |
| dim_date | One calendar date | date_key, month, quarter, year |
-- Revenue by product category (order-line grain)
SELECT p.category, SUM(f.net_sales) AS revenue
FROM fact_order_line AS f
JOIN dim_product AS p ON f.product_key = p.product_key
GROUP BY p.category;2. What is the difference between a fact table and a dimension table?
Interview answer: A fact table records measurable business events or states at a declared grain. A dimension table provides descriptive context used to filter, group, and label those facts.
| Aspect | Fact table | Dimension table |
|---|---|---|
| Purpose | Measures and business activity | Business descriptions and attributes |
| Examples | quantity, revenue, balance | product category, customer segment |
| Typical keys | Foreign keys to dimensions; event identifiers | Surrogate primary key; business key |
| Growth | Often high-volume | Usually smaller, but not always |
Facts may be additive (sales amount across all dimensions), semi-additive (account balance across customers but not time), or non-additive (ratios such as conversion rate).
3. When would you choose a star schema over a snowflake schema?
Interview answer: Choose a star schema when analyst usability, simpler joins, and predictable reporting are primary goals. Consider a snowflake schema when dimension hierarchies need normalized management, are reused independently, or governance benefits outweigh query complexity.
| Criteria | Star schema | Snowflake schema |
|---|---|---|
| Dimensions | Denormalized | Normalized into related tables |
| Joins | Usually fewer | Usually more |
| Ease of use | Often simpler for BI | More complex navigation |
| Redundancy | More repeated attributes | Less repeated hierarchy data |
Example: In a star schema, dim_product contains SKU, category, and department. In a snowflake schema, category and department might be stored in separate dimension tables linked to product.
4. How would you implement SCD Type 2?
Interview answer: Slowly Changing Dimension Type 2 preserves historical versions of tracked attributes. Use a surrogate key for each version, a stable business key, validity dates, and a current-row indicator. When tracked values change, close the old version and insert a new one.
Example history
| customer_key | customer_id | city | valid_from | valid_to | current |
|---|---|---|---|---|---|
| 101 | C1 | Pune | 2025-01-01 | 2026-03-01 | False |
| 205 | C1 | Bengaluru | 2026-03-01 | NULL | True |
Use half-open validity intervals: valid_from <= event_time and event_time < valid_to (or an open-ended NULL).
-- Step 1: expire changed current versions (illustrative)
UPDATE dim_customer AS d
SET valid_to = :change_time, is_current = FALSE
FROM staged_customer AS s
WHERE d.customer_id = s.customer_id
AND d.is_current = TRUE
AND d.city IS DISTINCT FROM s.city;
-- Step 2: insert new and changed versions.
-- Compare staging to current versions and insert rows
-- where no matching current version exists.In production, the expire-and-insert operations must be coordinated transactionally or through a warehouse-specific atomic strategy. Use NULL-safe comparisons, deterministic change ordering, and a uniqueness test for one current row per business key.
5. How do you handle late-arriving facts and dimensions?
Interview answer: Distinguish late facts (an event arrives after its business timestamp) from late dimensions (the descriptive entity arrives after the fact). Keep both event time and processing time, and make reprocessing possible.
Late-arriving facts
Load the fact into the correct historical date or partition based on event time. Recompute affected aggregates or partitions when required, using an overlap window and idempotent merges.
Late-arriving dimensions
One approach is to create an inferred dimension member with the known business key and placeholder attributes. Later, enrich it when the full dimension arrives. Another approach is to quarantine the fact until dimension resolution; the right choice depends on freshness and completeness requirements.
-- Resolve the customer version valid when the order occurred
SELECT f.order_id, d.customer_key
FROM staging_orders AS f
LEFT JOIN dim_customer AS d
ON f.customer_id = d.customer_id
AND f.order_ts >= d.valid_from
AND (f.order_ts < d.valid_to OR d.valid_to IS NULL);6. How would you model many-to-many relationships?
Interview answer: Use a bridge table when a dimension member can relate to multiple members of another dimension, such as an account having multiple owners or a product belonging to multiple tags.
Example: Orders and promotions
| Table | Relevant columns |
|---|---|
| fact_order | order_key, net_sales |
| dim_promotion | promotion_key, promotion_name |
| bridge_order_promotion | order_key, promotion_key, allocation_weight |
-- Allocate sales across multiple promotions
SELECT p.promotion_name,
SUM(f.net_sales * b.allocation_weight) AS attributed_sales
FROM fact_order AS f
JOIN bridge_order_promotion AS b
ON f.order_key = b.order_key
JOIN dim_promotion AS p
ON b.promotion_key = p.promotion_key
GROUP BY p.promotion_name;If a $100 order has two promotions and both receive full credit, summing by promotion yields $200 of attributed revenue. Allocation weights, such as 0.5 and 0.5, can avoid double counting when the business requires allocated totals.
7. How do you choose the grain of a fact table?
Interview answer: The grain states exactly what one row represents. Declare it before choosing measures and foreign keys. The grain must support the required analytical questions without ambiguous duplication.
Three common fact table patterns
| Pattern | Example grain | Use case |
|---|---|---|
| Transaction fact | One order line | Sales and product analysis |
| Periodic snapshot | One product per warehouse per day | Inventory balance trends |
| Accumulating snapshot | One order fulfillment lifecycle | Tracking ordered, packed, shipped, delivered milestones |
For an order-line fact, quantity and line_revenue belong naturally at line grain. A header-level shipping charge should not be repeated on every line without an allocation rule, or it will be overcounted.
8. When would you denormalize a data model?
Interview answer: Denormalize when read performance, analyst usability, or reduced join complexity provides more value than strict normalization. Common examples include a reporting star schema, precomputed aggregates, and a wide table for frequently accessed dashboard attributes.
Benefits and tradeoffs
| Benefits | Costs |
|---|---|
| Fewer joins for consumers | Data duplication and storage overhead |
| Simpler reporting queries | More complex update and consistency logic |
| Precomputed metrics may improve latency | Refresh cost and possible stale results |
Example: A dashboard repeatedly joins an order fact with product, category, and brand metadata. A denormalized product dimension or materialized reporting table may simplify queries and reduce repeated work.
Quick Revision: Interview Flashcards
What is a fact table's grain?
The precise meaning of one row, such as one order line or one product-warehouse-day snapshot.
Why use surrogate keys in SCD Type 2?
To distinguish multiple historical versions of the same business entity and link facts to the correct version.
What is a conformed dimension?
A shared dimension with consistent meaning and attributes across multiple business processes or fact tables.
What causes double counting?
Joining measures across incompatible grains or one-to-many relationships without aggregation or allocation rules.
What is an inferred dimension member?
A placeholder dimension row created when a fact arrives before the full dimension details are available.
What is a factless fact table?
A fact table that captures an event or relationship without a numeric measure, such as attendance or eligibility.
Hands-On Design Challenge
Key Takeaways
- Declare the grain before designing the fact table.
- Model each business process at its natural level of detail.
- Use dimensions for descriptive context and facts for events or states.
- Choose star or snowflake structures based on usability, governance, and measured performance.
- Handle historical versions and late arrivals with explicit time semantics.
- Use bridges and allocation rules for many-to-many relationships.
- Denormalize intentionally, with clear consistency and maintenance tradeoffs.
SQL examples are illustrative and may need adjustments for PostgreSQL, Snowflake, BigQuery, SQL Server, or other databases. Validate historical joins and SCD implementation against your platform's supported syntax and transaction behavior.
Comments
Post a Comment