SCD & History interview questions

1. A.A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
2. B.A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.
3. C.A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
4. D.A handling rule where the attribute never changes once set (retain original), such as a customer's original signup date.

Answer + AI explanation with Pro

1. A.SCD Type 0
2. B.mini-dimension
3. C.point-in-time table
4. D.SCD Type 4

Answer + AI explanation with Pro

1. A.SCD Type 4 — The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.
2. B.SCD Type 4 — A boolean column (often is_current) marking the active version of a Type 2 dimension row so queries can filter to the latest state cheaply.
3. C.SCD Type 4 — A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
4. D.SCD Type 4 — A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.

Answer + AI explanation with Pro

1. A.A hybrid combining Type 1, 2, and 3 (the name comes from 1+2+3) that keeps versioned rows plus current-value columns on each row for easy as-was and as-is reporting.
2. B.A boolean column (often is_current) marking the active version of a Type 2 dimension row so queries can filter to the latest state cheaply.
3. C.A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
4. D.A handling rule where the attribute never changes once set (retain original), such as a customer's original signup date.

Answer + AI explanation with Pro

1. A.mini-dimension
2. B.versioning surrogate
3. C.SCD Type 6
4. D.current flag

Answer + AI explanation with Pro

1. A.SCD Type 6 — Issuing a new surrogate key for each Type 2 version of a business key so facts join to the dimension state that was current at event time.
2. B.SCD Type 6 — A hybrid combining Type 1, 2, and 3 (the name comes from 1+2+3) that keeps versioned rows plus current-value columns on each row for easy as-was and as-is reporting.
3. C.SCD Type 6 — A separate dimension carved out of rapidly changing attributes (like age band or income band) to avoid exploding Type 2 history on the main dimension.
4. D.SCD Type 6 — A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.

Answer + AI explanation with Pro

1. A.A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.
2. B.A handling rule where the attribute never changes once set (retain original), such as a customer's original signup date.
3. C.A separate dimension carved out of rapidly changing attributes (like age band or income band) to avoid exploding Type 2 history on the main dimension.
4. D.A table the database itself versions over system or application time, automatically retaining history and supporting AS OF queries.

Answer + AI explanation with Pro

1. A.versioning surrogate
2. B.mini-dimension
3. C.point-in-time table
4. D.effective date range

Answer + AI explanation with Pro

1. A.mini-dimension — A boolean column (often is_current) marking the active version of a Type 2 dimension row so queries can filter to the latest state cheaply.
2. B.mini-dimension — Issuing a new surrogate key for each Type 2 version of a business key so facts join to the dimension state that was current at event time.
3. C.mini-dimension — A separate dimension carved out of rapidly changing attributes (like age band or income band) to avoid exploding Type 2 history on the main dimension.
4. D.mini-dimension — The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.

Answer + AI explanation with Pro

1. A.A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
2. B.A table the database itself versions over system or application time, automatically retaining history and supporting AS OF queries.
3. C.The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.
4. D.A hash of a row's tracked attributes compared between loads to detect changes efficiently, common in Data Vault satellites and SCD Type 2 loads.

Answer + AI explanation with Pro

1. A.versioning surrogate
2. B.mini-dimension
3. C.effective date range
4. D.temporal table

Answer + AI explanation with Pro

1. A.effective date range — A hash of a row's tracked attributes compared between loads to detect changes efficiently, common in Data Vault satellites and SCD Type 2 loads.
2. B.effective date range — A hybrid combining Type 1, 2, and 3 (the name comes from 1+2+3) that keeps versioned rows plus current-value columns on each row for easy as-was and as-is reporting.
3. C.effective date range — A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.
4. D.effective date range — The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.

Answer + AI explanation with Pro

1. A.A boolean column (often is_current) marking the active version of a Type 2 dimension row so queries can filter to the latest state cheaply.
2. B.A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
3. C.The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.
4. D.A hybrid combining Type 1, 2, and 3 (the name comes from 1+2+3) that keeps versioned rows plus current-value columns on each row for easy as-was and as-is reporting.

Answer + AI explanation with Pro

1. A.current flag
2. B.mini-dimension
3. C.SCD Type 4
4. D.hash diff

Answer + AI explanation with Pro

1. A.current flag — A hash of a row's tracked attributes compared between loads to detect changes efficiently, common in Data Vault satellites and SCD Type 2 loads.
2. B.current flag — A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
3. C.current flag — A boolean column (often is_current) marking the active version of a Type 2 dimension row so queries can filter to the latest state cheaply.
4. D.current flag — A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.

Answer + AI explanation with Pro

1. A.A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
2. B.The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.
3. C.A hybrid combining Type 1, 2, and 3 (the name comes from 1+2+3) that keeps versioned rows plus current-value columns on each row for easy as-was and as-is reporting.
4. D.A table the database itself versions over system or application time, automatically retaining history and supporting AS OF queries.

Answer + AI explanation with Pro

1. A.mini-dimension
2. B.effective date range
3. C.SCD Type 4
4. D.late-arriving dimension

Answer + AI explanation with Pro

1. A.late-arriving dimension — A separate dimension carved out of rapidly changing attributes (like age band or income band) to avoid exploding Type 2 history on the main dimension.
2. B.late-arriving dimension — A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
3. C.late-arriving dimension — A hybrid combining Type 1, 2, and 3 (the name comes from 1+2+3) that keeps versioned rows plus current-value columns on each row for easy as-was and as-is reporting.
4. D.late-arriving dimension — A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.

Answer + AI explanation with Pro

1. A.The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.
2. B.A table the database itself versions over system or application time, automatically retaining history and supporting AS OF queries.
3. C.A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
4. D.A hash of a row's tracked attributes compared between loads to detect changes efficiently, common in Data Vault satellites and SCD Type 2 loads.

Answer + AI explanation with Pro

1. A.temporal table
2. B.SCD Type 6
3. C.SCD Type 4
4. D.hash diff

Answer + AI explanation with Pro

1. A.temporal table — A hybrid combining Type 1, 2, and 3 (the name comes from 1+2+3) that keeps versioned rows plus current-value columns on each row for easy as-was and as-is reporting.
2. B.temporal table — A table the database itself versions over system or application time, automatically retaining history and supporting AS OF queries.
3. C.temporal table — A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
4. D.temporal table — A boolean column (often is_current) marking the active version of a Type 2 dimension row so queries can filter to the latest state cheaply.

Answer + AI explanation with Pro

1. A.A separate dimension carved out of rapidly changing attributes (like age band or income band) to avoid exploding Type 2 history on the main dimension.
2. B.A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
3. C.A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
4. D.A handling rule where the attribute never changes once set (retain original), such as a customer's original signup date.

Answer + AI explanation with Pro

1. A.point-in-time table
2. B.effective date range
3. C.SCD Type 0
4. D.current flag

Answer + AI explanation with Pro

1. A.SCD Type 0 — A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
2. B.SCD Type 0 — A table the database itself versions over system or application time, automatically retaining history and supporting AS OF queries.
3. C.SCD Type 0 — Issuing a new surrogate key for each Type 2 version of a business key so facts join to the dimension state that was current at event time.
4. D.SCD Type 0 — A handling rule where the attribute never changes once set (retain original), such as a customer's original signup date.

Answer + AI explanation with Pro

1. A.A handling rule where the attribute never changes once set (retain original), such as a customer's original signup date.
2. B.A boolean column (often is_current) marking the active version of a Type 2 dimension row so queries can filter to the latest state cheaply.
3. C.A hash of a row's tracked attributes compared between loads to detect changes efficiently, common in Data Vault satellites and SCD Type 2 loads.
4. D.A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.

Answer + AI explanation with Pro

1. A.SCD Type 6
2. B.mini-dimension
3. C.hash diff
4. D.late-arriving dimension

Answer + AI explanation with Pro

1. A.hash diff — A fact row that arrives before its dimension record exists; handled by inserting a placeholder dimension row and updating it later, or assigning an inferred member.
2. B.hash diff — A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.
3. C.hash diff — A hash of a row's tracked attributes compared between loads to detect changes efficiently, common in Data Vault satellites and SCD Type 2 loads.
4. D.hash diff — The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.

Answer + AI explanation with Pro

1. A.Issuing a new surrogate key for each Type 2 version of a business key so facts join to the dimension state that was current at event time.
2. B.A history technique splitting a dimension into a current-value mini table and a separate history table, keeping the main table small and fast.
3. C.The valid_from and valid_to columns on a Type 2 row that bound the period during which that version of the record was current.
4. D.A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.

Answer + AI explanation with Pro

1. A.mini-dimension
2. B.hash diff
3. C.versioning surrogate
4. D.effective date range

Answer + AI explanation with Pro

1. A.versioning surrogate — A precomputed helper table in Data Vault that snapshots which satellite versions were active at given dates to simplify as-of querying.
2. B.versioning surrogate — A handling rule where the attribute never changes once set (retain original), such as a customer's original signup date.
3. C.versioning surrogate — Issuing a new surrogate key for each Type 2 version of a business key so facts join to the dimension state that was current at event time.
4. D.versioning surrogate — A table the database itself versions over system or application time, automatically retaining history and supporting AS OF queries.

Answer + AI explanation with Pro