All 51 questions
What are slowly changing dimensions, and when would you use them?
Asked in 9 of 362 interviews · last asked Oct 26 · Concept · Mid-level
Show answer
Slowly changing dimensions are warehouse dimensions whose attributes change over time, such as a customer's address. SCD methods control whether we overwrite old values or keep history. I use them when descriptive data changes, especially when reports must show what was true at the time.
- Type 1 overwrites old values. Use it for corrections or when history is not needed.
- For Type 2, match by business key, detect attribute changes, and close the old version's effective period.
- Insert a new version with a new surrogate key, effective dates, and a current-row flag.
- Link facts to the version valid when the event occurred. Avoid overlapping effective periods.
What the interviewer checks: The interviewer checks whether you can choose between overwriting values and keeping history, and correctly track Type 2 versions.
Other ways it was asked
- “How would you implement a Slowly Changing Dimension Type 2 in an ETL process?”
- “How do you implement Slowly Changing Dimension Type 2 (SCD Type 2) in a data warehouse?”
- “What are slowly changing dimensions (SCDs) in data warehousing?”
What is the difference between SCD Type 1 and SCD Type 2?
Asked in 4 of 362 interviews · last asked Nov 12 · Concept · Mid-level
Show answer
SCD Type 1 overwrites old values in a dimension row, so it does not keep change history. SCD Type 2 adds a new row for each change and keeps earlier versions. Use Type 1 when only the latest value matters, and Type 2 when reports need values as they were at the time.
- Type 1 is simpler and uses less storage, but loses old values.
- Type 2 versions share a business key but have different surrogate keys.
- Type 2 usually tracks versions with start dates, end dates, and a current-row flag.
- Fact rows reference the Type 2 version that applied when the event occurred.
What the interviewer checks: The interviewer checks whether you understand how dimension changes affect history and reporting.
Other ways it was asked
- “What are SCD Type 1 and Type 2, and how do they differ?”
- “What is the difference between SCD Type 1 and Type 2?”
How would you handle corrections and repeated updates without creating duplicate fact records?
Asked in 3 of 362 interviews · last asked Sep 21 · Scenario · Mid-level
Show answer
I define what one fact row represents and give it a stable business key. I use that key to update an existing row or insert a new one, with a unique constraint to prevent duplicates. I track the source version so repeated or older updates cannot overwrite newer data. If history is needed, I store each version separately.
- The key must identify one fact, such as a shipment event, not just the shipment.
- Deduplicate incoming updates before applying them.
- Reprocessing the same update should leave the result unchanged.
- Keep correction history separate from the current fact, or clearly mark the current version.
What the interviewer checks: The interviewer checks whether you can define fact identity and safely handle retries, corrections, and out-of-order updates.
Other ways it was asked
- “How would you prevent multiple updates to the same shipment from creating duplicate fact records?”
- “How would you model corrections to an existing operational event and prevent duplicate fact records?”
- “How would you handle corrections or repeated updates without creating duplicate fact records?”
When is using yesterday’s customer dimension safe for today’s transaction feed, and what checks and approvals are required?
Asked once · last asked Sep 29 · Scenario · Senior
Other ways it was asked
- “If SQL checks find no transactions linked to today’s duplicate IDs, but today’s feed includes 200 new customers who were not in yesterday’s dimension, would you use yesterday’s dimension, and what would you tell the service owner?”
- “If today's fact contains transactions for 200 customers absent from yesterday's dimension and the duplicate keys have no matching transactions, would you use yesterday's dimension for the 5 p.m. feed, or what would you tell the service owner?”
- “Under what exact conditions would you recommend using yesterday’s customer dimension for today’s transaction feed, and who would approve that decision?”
You need to store each customer's product price history for 1–20 years. Which type of database would you choose, and why?
Asked once · last asked Apr 15 · Scenario · Mid-level
How would you map customers_src (customer ID, name, country) to warehouse table customers_dim (customer key, customer ID, name, country, is_active), including logic for populating customer keys and is_active and handling new versus existing customers?
Asked once · last asked May 18 · Scenario · Mid-level
Other ways it was asked
- “Suppose you have a source table called customers_src and a target table called customers_dim in a data warehouse. The target requires customer key, customer ID, name, country, and is_active, but the source provides only customer ID, name, and country. How would you design a simple mapping and logic to populate the target table, including handling new versus existing customers?”
How would you apply an update to custom classification logic to events already stored in a normalized database?
Asked once · last asked Jul 20 · Design · Senior
How does SCD Type 2 represent changes in a dataset?
Asked once · last asked Aug 18 · Concept · Junior
Can you provide a four- or five-row example dataset showing what SCD Type 2 records look like?
Asked once · last asked Aug 18 · Concept · Junior
Can you show how a target table is populated during an incremental load for employee ID and employee name, including the date columns?
Asked once · last asked Aug 18 · Scenario · Mid-level
In an SCD Type 2 pipeline, how would you set the start date, end date, and active flag using the current date?
Asked once · last asked Aug 18 · Concept · Mid-level
How would you separate actual and historical data in an SAP Datasphere solution?
Asked once · last asked Aug 28 · Scenario · Mid-level
When a BigQuery MERGE statement updates existing records and inserts new ones, does that implement SCD Type 1 or Type 2?
Asked once · last asked Sep 10 · Concept · Mid-level
What are snapshots in dbt?
Asked once · last asked Sep 14 · Concept · Mid-level
How would you handle an audit log requirement when the same order gets updated over time?
Asked once · last asked Sep 18 · Scenario · Mid-level
How would you model several updates for the same operational event when its status or timestamp changes more than once?
Asked once · last asked Sep 21 · Scenario · Mid-level
How would you handle repeated deliveries or later corrections in a dimensional model without changing the fact-table grain or creating duplicate fact records?
Asked once · last asked Sep 21 · Scenario · Senior
How would you define a unique operational event and prevent repeated deliveries or later corrections from creating duplicate fact records?
Asked once · last asked Sep 22 · Scenario · Senior
How would you distinguish a repeated delivery of the same event from a new business event or a correction to an earlier event?
Asked once · last asked Sep 22 · Scenario · Senior
If a farm moves from one region to another in July and a manager asks for last year’s interactions grouped by the farm’s current region, how would you model that while avoiding confusion with historical region reporting?
Asked once · last asked Sep 23 · Scenario · Senior
A shipment arrives late Monday, but its WMS record arrives just after midnight Tuesday; which timestamp would you use to match it to a daily capacity snapshot, and how would you handle snapshots that record only end-of-day capacity?
Asked once · last asked Sep 28 · Scenario · Senior
Other ways it was asked
- “A shipment arrives late on Monday, but its WMS record is received just after midnight on Tuesday. Which timestamp would you use to match the shipment to the daily capacity snapshot, and what would you do if the snapshot only records capacity at the end of each day?”
A customer's risk changes from medium to high; how would you model a Monday transaction loaded Friday to reflect risk at transaction time, and what would you publish if the source provides only current risk, without history or effective dates?
Asked once · last asked Sep 28 · Scenario · Senior
Other ways it was asked
- “Suppose a customer’s risk category changes from medium to high, but a transaction occurred on Monday and was loaded late on Friday. How would you model the customer dimension so the Monday transaction reflects the category that applied when it occurred? If the source only gives the current category, with no effective date or history, can you assert that the category was medium on Monday, and what would you publish?”
If the unit conversion changed from 12 to 10 units last month, how would you preserve historically correct quantities when rerunning and reporting on past periods?
Asked once · last asked Sep 28 · Scenario · Senior
How would you prevent repeated ETA changes from making a supplier look reliable?
Asked once · last asked Sep 29 · Scenario · Senior
How would you report historical customer risk when the source provides only the current classification?
Asked once · last asked Sep 29 · Scenario · Senior
Other ways it was asked
- “If a customer-risk history report is required but the source only supplies each customer’s current risk category and has no change log, can you report a Monday transaction as high risk when the customer is marked high risk later? What would you put in the model and tell the business analyst?”
- “If the only available customer risk category is the current value, marked high on Friday, and a Tuesday shipment is due in a historical risk report, what value would you put in the risk-category field, and how would you explain it to the service owner?”
If a shipment was received Tuesday but ingested Friday, and two customer-dimension rows have the same business key with different validity periods, which dimension row should you join to, and how would you test that choice?
Asked once · last asked Sep 29 · Scenario · Senior
If both mappings point to the same region but their effective date ranges overlap, would you keep the latest mapping, and why or why not?
Asked once · last asked Oct 1 · Scenario · Senior
How do you handle slowly changing dimensions (SCD types) in dbt?
Asked once · last asked Oct 2 · Tooling · Mid-level
A product can change suppliers over time, and a store can change banners. Should a 2025 report show historical or current values, and how would you implement each option?
Asked once · last asked Oct 2 · Scenario · Senior
How would you implement supplier history over time in a data model?
Asked once · last asked Oct 2 · Scenario · Senior
When would you use slowly changing dimensions (SCD) to preserve supplier or store-banner history?
Asked once · last asked Oct 2 · Concept · Mid-level
How would you change a courier's delivery-platform assignment in a system while preserving their historical data?
Asked once · last asked Oct 7 · Scenario · Mid-level
How should a functional specification define courier status transitions so that historical data is preserved?
Asked once · last asked Oct 7 · Scenario · Mid-level
Why should deactivating a product preserve its historical sales records?
Asked once · last asked Nov 18 · Concept · Mid-level
Other ways it was asked
- “How would you implement product deactivation while preserving historical sales records?”
How do you maintain an audit history for transactions?
Asked once · last asked Oct 26 · Design · Senior
Other ways it was asked
- “How do you systematically maintain an audit history for transactions?”
What is SCD Type 3, and how does it retain historical values?
Asked once · last asked Oct 26 · Concept · Mid-level
Other ways it was asked
- “What is in SCD Type 3, and how do you retain the values?”
How will you implement an SCD Type 3 in an ETL process?
Asked once · last asked Oct 26 · Tooling · Mid-level
How would you design a payment service to help customers understand what happened to a transaction and what changed?
Asked once · last asked Oct 26 · Design · Senior
When should an application store before-and-after values for changes to a shared portfolio?
Asked once · last asked Oct 28 · Design · Senior
How would you design event timestamps for user activity so ordering is correct across client clock skew, time zones, and offline capture? What fields would you store, and which clock would you trust?
Asked once · last asked Nov 1 · Design · Senior
In an SCD Type 2 table, how do you identify the latest record versus historical records?
Asked once · last asked Nov 12 · Concept · Mid-level
In Delta Lake, how would you implement an SCD type 1 or type 2?
Asked once · last asked Nov 13 · Tooling · Mid-level
Why should you store each job event's timestamp alongside its status when finding the latest event?
Asked once · last asked Nov 13 · Concept · Mid-level
How would you identify changed records in an SCD Type 2 process?
Asked once · last asked Nov 18 · Concept · Mid-level
How do you handle document versioning, revocation, and incremental ingestion without reprocessing everything?
Asked once · last asked Nov 20 · Design · Senior
Should a questionnaire be versioned, and why?
Asked once · last asked Nov 20 · Design · Mid-level
In a category with rapidly changing versions, for example earbuds, how do you maintain a clean price history and comparisons when SKUs change, listings merge or split, and retailers rename bundles?
Asked once · last asked Nov 21 · Scenario · Senior
How would you track an order as it moves through a sequence of states?
Asked once · last asked Oct 15 · Concept · Mid-level
Can you briefly explain SCDs and their types?
Asked once · last asked Oct 26 · Concept · Junior
How would you track changes to shared portfolios?
Asked once · last asked Oct 28 · Design · Senior
When recording an account balance change, should you store the difference between the old and new balances or the new balance?
Asked once · last asked Nov 5 · Concept · Mid-level
More Data Modeling interview questions
- All Data Modeling interview questions
- Data Modeling Basics Interview Questions
- Database Schema Design Interview Questions
- Data Warehouse Architecture Interview Questions
- Lakehouse, Apache Iceberg, and Delta Lake Interview Questions
- Semantic Modeling and Power BI Interview Questions
- Database Performance and Scalability Interview Questions
- Data Quality and Governance Interview Questions
How this list is made
Every question here was asked by a real interviewer in a real job interview that a candidate ran MeetAssist in. After the call, the interviewer's questions are pulled from the transcript. Practice sessions, tests, exams and anything that was not a job interview are dropped.
Questions are rewritten so they make sense on their own: names of people, employers and clients are removed, and speech-to-text mistakes are fixed. Small talk, logistics and questions about one candidate's own résumé are left out. Rephrasings of the same question are merged, and the count is the number of different interviews it came up in.
The interviews come from candidates who use MeetAssist, so they lean towards software, data and AI roles: the shares describe these interviews, not every Data Modeling interview. Answers to the most asked questions are written and checked by an editor; a "common mistake" is shown only when reviews of real answers in these interviews show it, with the count.
362 interviews, 213 candidates, Dec 2025 to Nov 2026. Updated every month with new interviews.
Or add the Chrome extension