All 51 questions

  1. 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?”
  2. 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?”
  3. 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?”
  4. 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?”
  5. 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

  6. 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?”
  7. 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

  8. How does SCD Type 2 represent changes in a dataset?

    Asked once · last asked Aug 18 · Concept · Junior

  9. 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

  10. 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

  11. 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

  12. How would you separate actual and historical data in an SAP Datasphere solution?

    Asked once · last asked Aug 28 · Scenario · Mid-level

  13. 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

  14. What are snapshots in dbt?

    Asked once · last asked Sep 14 · Concept · Mid-level

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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?”
  22. 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?”
  23. 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

  24. How would you prevent repeated ETA changes from making a supplier look reliable?

    Asked once · last asked Sep 29 · Scenario · Senior

  25. 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?”
  26. 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

  27. 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

  28. How do you handle slowly changing dimensions (SCD types) in dbt?

    Asked once · last asked Oct 2 · Tooling · Mid-level

  29. 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

  30. How would you implement supplier history over time in a data model?

    Asked once · last asked Oct 2 · Scenario · Senior

  31. When would you use slowly changing dimensions (SCD) to preserve supplier or store-banner history?

    Asked once · last asked Oct 2 · Concept · Mid-level

  32. 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

  33. How should a functional specification define courier status transitions so that historical data is preserved?

    Asked once · last asked Oct 7 · Scenario · Mid-level

  34. 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?”
  35. 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?”
  36. 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?”
  37. How will you implement an SCD Type 3 in an ETL process?

    Asked once · last asked Oct 26 · Tooling · Mid-level

  38. 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

  39. When should an application store before-and-after values for changes to a shared portfolio?

    Asked once · last asked Oct 28 · Design · Senior

  40. 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

  41. 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

  42. In Delta Lake, how would you implement an SCD type 1 or type 2?

    Asked once · last asked Nov 13 · Tooling · Mid-level

  43. 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

  44. How would you identify changed records in an SCD Type 2 process?

    Asked once · last asked Nov 18 · Concept · Mid-level

  45. How do you handle document versioning, revocation, and incremental ingestion without reprocessing everything?

    Asked once · last asked Nov 20 · Design · Senior

  46. Should a questionnaire be versioned, and why?

    Asked once · last asked Nov 20 · Design · Mid-level

  47. 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

  48. How would you track an order as it moves through a sequence of states?

    Asked once · last asked Oct 15 · Concept · Mid-level

  49. Can you briefly explain SCDs and their types?

    Asked once · last asked Oct 26 · Concept · Junior

  50. How would you track changes to shared portfolios?

    Asked once · last asked Oct 28 · Design · Senior

  51. 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

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.