A slowly changing dimension is easy to describe and surprisingly easy to get wrong. The hard parts are not just adding a valid_from column: they are defining what “true at the time” means, preserving history without creating overlapping versions, and making facts join to the intended version.
This guide focuses on those temporal-modeling problems. It is not a general guide to change-data capture or backfill mechanics. The examples use PostgreSQL syntax, but the modeling questions apply to other warehouse platforms.
For each question, listen for explicit time semantics, stated assumptions, and safeguards against ambiguous history—not just familiarity with SCD terminology.
Fundamentals
1. What problem does a Type 2 slowly changing dimension solve?
What the interviewer is testing: Whether you understand the business purpose of historical versions, not just the mechanics of inserting rows.
Model answer:
A Type 2 dimension preserves changes to descriptive attributes as separate versions. If a customer moves from the “Standard” segment to “Enterprise,” a Type 2 model keeps both versions with their effective periods. A report can then describe the customer as they were when a transaction occurred, rather than applying today’s attributes to all past activity.
A typical row has a surrogate key, a stable business key, the tracked attributes, and an effective interval. I prefer half-open intervals: valid_from is inclusive and valid_to is exclusive. A row is valid at time t when valid_from <= t AND t < valid_to. Half-open intervals avoid ambiguity at the boundary between consecutive versions.
Type 2 is not automatically right for every attribute. The team must decide which changes merit historical preservation, and what time the interval represents: source-effective time, warehouse-observed time, or another business-defined time.
2. Why use a surrogate key as well as a natural key?
What the interviewer is testing: Whether you can distinguish entity identity from a particular historical version.
Model answer:
The natural key identifies the business entity, such as customer_id. The surrogate key identifies a specific dimension row—a particular version of that entity. A fact table’s foreign key can therefore point to the customer version that was valid when the fact occurred.
The natural key can change, be reused, or have different representations across source systems. A warehouse-controlled surrogate key decouples fact relationships from those source details. It also gives each Type 2 version a distinct key.
I would still retain the natural key on the dimension for lookup, reconciliation, and analyst usability. The surrogate key does not replace the need to define what counts as the same real-world entity.
3. What is the difference between valid time and system time?
What the interviewer is testing: Whether you can explain two separate timelines instead of treating every timestamp as “the date.”
Model answer:
Valid time answers: “When was this fact true in the business?” System time answers: “When did this database know or record it?” Those times can differ. For example, a customer’s address may have become effective on Monday, while the warehouse receives the correction on Wednesday.
A conventional SCD2 table usually tracks valid time, even if its created_at column also records when the row was loaded. That load timestamp is not a complete system-time history: an update might overwrite evidence of what the warehouse previously believed.
When auditability or correction history matters, model both timelines explicitly or use a database capability that preserves system-time history. Avoid claiming that a single updated_at field provides bitemporal semantics.
4. How should a dimension represent “unknown” or missing attributes?
What the interviewer is testing: Whether you understand referential integrity and the difference between missing information and a missing dimension row.
Model answer:
A fact should generally retain a valid dimension reference even when the source has not supplied the descriptive attributes yet. A warehouse can use a reserved “unknown” member, or an inferred member keyed by the known business identifier. The choice depends on whether the entity itself is unknown or known but incomplete.
An inferred member is useful when a fact arrives before the full dimension record: the fact can reference a stable surrogate key, and later processing can populate or version that member according to the warehouse’s temporal rules. A single generic unknown member is simpler, but it cannot later be resolved to a specific entity without updating the fact relationship.
I would distinguish missing, not applicable, and not yet received when those meanings affect reporting. A nullable column alone often collapses those states.
Intermediate
5. What constraints should a Type 2 dimension enforce?
What the interviewer is testing: Whether you can turn temporal assumptions into enforceable invariants.
Model answer:
At a minimum, define the business key, surrogate key, tracked attributes, effective start and end, and a clear convention for the current row. Common invariants include:
- A version has
valid_from < valid_to. - A business key has at most one current version.
- Versions for the same business key do not overlap.
- Consecutive versions meet at the intended boundary if gaps are not allowed.
- Each source update is applied using an explicit effective timestamp.
A partial unique index can enforce one current version per business key. Preventing all interval overlaps may require a range constraint or platform-specific equivalent. If the warehouse cannot enforce the rule declaratively, validate it in the load and make the check observable.
Do not use is_current as a substitute for interval semantics. It is a convenient flag, but the timestamps are what support historical as-of queries.
6. How would you apply a simple Type 2 change from an ordered snapshot?
What the interviewer is testing: Whether you can implement the version transition and state the assumptions behind it.
Model answer:
For an incoming snapshot with a changed tracked attribute, close the current version at the incoming effective time, then insert a new current version beginning at that same time. If the tracked values are unchanged, do nothing. The update and insert belong in one transaction so readers do not observe an intermediate state with no current row.
This minimal example assumes one ordered change per business key and that the incoming effective time is later than the current version’s start. A production load must validate those assumptions and define what happens for out-of-order changes.
CREATE TEMP TABLE dim_customer (
customer_sk bigint GENERATED ALWAYS AS IDENTITY,
customer_id integer NOT NULL,
segment text NOT NULL,
valid_from timestamptz NOT NULL,
valid_to timestamptz NOT NULL,
is_current boolean NOT NULL
);
INSERT INTO dim_customer
(customer_id, segment, valid_from, valid_to, is_current)
VALUES
(7, 'Standard', '2025-01-01 00:00:00+00', 'infinity', true);
BEGIN;
UPDATE dim_customer
SET valid_to = '2025-02-01 00:00:00+00',
is_current = false
WHERE customer_id = 7
AND is_current
AND segment IS DISTINCT FROM 'Enterprise';
INSERT INTO dim_customer
(customer_id, segment, valid_from, valid_to, is_current)
SELECT 7, 'Enterprise', '2025-02-01 00:00:00+00', 'infinity', true
WHERE NOT EXISTS (
SELECT 1
FROM dim_customer
WHERE customer_id = 7
AND is_current
);
COMMIT;
SELECT customer_id, segment, valid_from, valid_to, is_current
FROM dim_customer
ORDER BY valid_from;
Notice that the new version starts exactly where the old one ends, and the transaction keeps the transition atomic.
7. What changes when a dimension update arrives late or out of order?
What the interviewer is testing: Whether you notice that closing the current row is not enough to preserve correct history.
Model answer:
First clarify what the source timestamp means and whether the event is a correction to an earlier period or a genuinely late notification. A change effective before the current version began cannot safely be treated as a new current change. Doing so can create incorrect ordering, overlaps, or gaps.
If the full history is available, the load must locate the interval containing the effective timestamp and split or revise the relevant period. If only current snapshots are available, the warehouse may not have enough information to reconstruct prior states. In that case, the answer should state the limitation and choose an explicit policy: reject for investigation, retain a correction record, or apply a documented approximation.
The key interview signal is not a particular algorithm. It is recognizing that arrival time and effective time are not interchangeable and describing how the system detects cases it cannot safely apply.
8. How should a fact resolve its dimension version?
What the interviewer is testing: Whether you understand the temporal join between an event and a dimensional history.
Model answer:
For an event-time attribution, resolve the dimension row whose natural key matches the fact and whose valid interval contains the event timestamp:
SELECT f.order_id, d.customer_sk
FROM staged_order AS f
JOIN dim_customer AS d
ON d.customer_id = f.customer_id
AND f.ordered_at >= d.valid_from
AND f.ordered_at < d.valid_to;
The fact should store the resolved surrogate key if that is the warehouse’s chosen dimensional model. The exact timestamp matters: order time, settlement time, and ingestion time can produce different valid attributions.
Some teams intentionally restate facts when dimension history is corrected; others preserve the originally assigned key for auditability. That is a business and governance decision. State it explicitly, and test what happens when no dimension version matches.
Senior and system-design level
9. When is a bitemporal model warranted instead of ordinary SCD2?
What the interviewer is testing: Whether you can justify temporal complexity based on audit and correction requirements.
Model answer:
Use bitemporal modeling when consumers need to answer both “what was true in the business at time X?” and “what did the warehouse believe at time Y?” For example, a compliance review may need to reproduce a report as it appeared before a later correction while also showing the corrected historical truth.
A bitemporal record tracks a valid-time interval and a system-time interval. When a correction arrives, the warehouse can preserve the previous belief in system time while recording the corrected valid-time history. That adds query complexity and storage, so it should be driven by requirements such as audit, regulated reporting, or reproducible historical decisions—not adopted simply because two timestamps sound more complete.
A useful design exercise is to write the exact “as of” questions users need to ask before selecting the model.
10. How would you test that a temporal dimension is correct?
What the interviewer is testing: Whether you can express correctness as testable invariants rather than visual inspection.
Model answer:
Test structural rules and business behavior. Structural checks include duplicate current rows, invalid intervals, overlapping versions, and unexpected gaps. Behavioral tests should cover an unchanged snapshot, a changed attribute, repeated application of the same input, a late effective date, and a missing dimension match.
For example, a basic overlap check for half-open intervals is:
SELECT a.customer_id, a.customer_sk, b.customer_sk
FROM dim_customer AS a
JOIN dim_customer AS b
ON a.customer_id = b.customer_id
AND a.customer_sk < b.customer_sk
AND a.valid_from < b.valid_to
AND b.valid_from < a.valid_to;
An empty result means this check found no overlaps. It does not prove the intervals cover the required business timeline or that each fact was assigned correctly. Add reconciliation checks for unmatched facts and boundary tests where an event occurs exactly at valid_from or valid_to.
11. How would you design the load for concurrent and repeatable execution?
What the interviewer is testing: Whether you can reason about atomicity, duplicate work, and competing writers.
Model answer:
Define the unit of work and serialize conflicting changes per natural key, or use an equivalent concurrency-control strategy supported by the warehouse. Enforce uniqueness for the current version where possible. Make source rows deterministic before applying them: one accepted change per business key and effective timestamp, with an explicit conflict policy if multiple candidates exist.
The following example handles an ordered snapshot batch. It creates a current-row uniqueness constraint, rejects a changed value whose effective time is not later than the current version’s start, closes changed rows, and inserts new versions in one transaction. It assumes stage_customer contains one row per customer for this batch; it deliberately does not solve historical interval surgery.
CREATE TEMP TABLE dim_customer (
customer_sk bigint GENERATED ALWAYS AS IDENTITY,
customer_id text NOT NULL,
segment text NOT NULL,
valid_from timestamptz NOT NULL,
valid_to timestamptz NOT NULL DEFAULT 'infinity',
is_current boolean NOT NULL,
CHECK (valid_from < valid_to)
);
CREATE UNIQUE INDEX dim_customer_one_current
ON dim_customer (customer_id)
WHERE is_current;
CREATE TEMP TABLE stage_customer (
customer_id text PRIMARY KEY,
segment text NOT NULL,
effective_at timestamptz NOT NULL
);
INSERT INTO dim_customer
(customer_id, segment, valid_from, is_current)
VALUES
('C-7', 'Standard', '2025-01-01 00:00:00+00', true);
INSERT INTO stage_customer (customer_id, segment, effective_at)
VALUES
('C-7', 'Enterprise', '2025-02-01 00:00:00+00'),
('C-8', 'Standard', '2025-02-01 00:00:00+00');
BEGIN;
LOCK TABLE dim_customer IN SHARE ROW EXCLUSIVE MODE;
DO $$
BEGIN
IF EXISTS (
SELECT 1
FROM stage_customer AS s
JOIN dim_customer AS d
ON d.customer_id = s.customer_id
AND d.is_current
WHERE d.segment IS DISTINCT FROM s.segment
AND s.effective_at <= d.valid_from
) THEN
RAISE EXCEPTION
'Changed snapshot is not later than the current version start';
END IF;
END
$$;
UPDATE dim_customer AS d
SET valid_to = s.effective_at,
is_current = false
FROM stage_customer AS s
WHERE d.customer_id = s.customer_id
AND d.is_current
AND d.segment IS DISTINCT FROM s.segment;
INSERT INTO dim_customer
(customer_id, segment, valid_from, is_current)
SELECT s.customer_id, s.segment, s.effective_at, true
FROM stage_customer AS s
LEFT JOIN dim_customer AS d
ON d.customer_id = s.customer_id
AND d.is_current
WHERE d.customer_id IS NULL;
COMMIT;
SELECT customer_id, segment, valid_from, valid_to, is_current
FROM dim_customer
ORDER BY customer_id, valid_from;
Notice that unchanged current rows remain untouched, while newly seen and changed keys get a current version; the table lock is intentionally simple, not a universal high-throughput strategy.
12. How would you explain the trade-offs between common dimension-history patterns?
What the interviewer is testing: Whether you can choose a history model based on the questions users need answered.
Model answer:
Start with the reporting requirement, not the pattern name. Type 1 is appropriate when historical values should be overwritten. Type 2 preserves effective versions. Type 3 keeps a limited previous value in the same row. A bitemporal model preserves both business validity and what the warehouse knew over time.
A practical comparison:
| Pattern | What it preserves | Useful when | Main limitation |
|---|---|---|---|
| Type 1 | Current value only | History is irrelevant or deliberately corrected away | Past reports can change |
| Type 2 | Multiple effective versions | Reports need attributes as of an event time | More rows and temporal join logic |
| Type 3 | A small, fixed amount of prior state | Users need a specific previous/current comparison | Not a general history |
| Bitemporal | Business-valid and system-recorded history | Audits need both corrected truth and prior warehouse belief | More complex writes and queries |
These patterns can coexist by attribute. For example, a corrected spelling may be Type 1 while a customer’s segment is Type 2. The classification should be documented because it determines whether historical facts are restated or retain their earlier attribution.
A temporal model at a glance
This flow separates incoming data from the dimension history and shows where temporal rules belong.
flowchart LR
Source["Source snapshot or change"] --> Stage["Staging and validation"]
Stage --> Dedup["Resolve duplicate business keys"]
Dedup --> Rules["Apply effective-time rules"]
Rules --> Dimension["Versioned dimension"]
Dimension --> Facts["Resolve fact dimension key"]
Facts --> Checks["Temporal and reconciliation checks"]The important boundary is between validation and version application: do not mutate history until the effective-time assumptions are checked.
Common mistakes candidates make
- Treating load time as business-effective time. Name the timestamps and state their meaning.
- Using a current-row flag as the whole temporal model. Keep interval boundaries authoritative and consistent.
- Applying every late change by closing the current row. Determine whether the change belongs inside existing history.
- Assuming one SCD policy fits every attribute. Classify attributes according to reporting and audit requirements.
- Ignoring the fact-side join. Explain which event timestamp selects the dimension version.
- Forgetting boundaries. Half-open intervals make a timestamp at a version boundary belong to exactly one row.
- Claiming idempotency without defining a key. Explain how duplicate source records are identified and resolved.
- Overlooking concurrency. Two writers can both observe no current version unless the design serializes or constrains the operation.
- Using a generic unknown member for every missing state. Distinguish unknown entity, incomplete entity, and not-applicable data.
- Calling a pair of timestamps bitemporal. Bitemporal behavior requires preserving both timelines through corrections.
Observability for temporal ETL
A successful job status does not prove correct history. Instrument the invariants that protect downstream attribution, and keep enough run context to trace a bad dimension version back to its input.
Log: Emit structured events for the load start and end, validation failures, rejected or late changes, dimension inserts and closures, unmatched fact keys, and reconciliation outcomes. Include run_id, source_name, model_name, batch_id, and a trace identifier. Avoid logging sensitive dimension attributes unless policy permits it.
{
"event": "dimension_load_completed",
"run_id": "run-2026-11-14-01",
"source_name": "crm",
"model_name": "dim_customer",
"batch_id": "batch-1842",
"rows_inserted": 128,
"rows_closed": 94,
"late_changes_rejected": 2,
"unmatched_fact_keys": 0,
"duration_ms": 48320,
"outcome": "success"
}
Metrics: Use RED for the ETL service or job interface—request or run rate, error rate, and duration. Use USE for the resources it consumes—utilization, saturation, and errors for the database, worker pool, and storage. Add domain metrics such as rows read, rows changed, duplicate business keys, temporal overlaps, and unmatched fact keys. Label metrics with bounded dimensions such as model and outcome, not customer identifiers.
Tracing: Propagate a trace across orchestration, extraction, staging, validation, and warehouse writes. Use spans for meaningful stages and attach batch identifiers as attributes. Python example using the OpenTelemetry SDK:
from opentelemetry import trace
from opentelemetry.sdk.resources import Resource
from opentelemetry.sdk.trace import TracerProvider
from opentelemetry.sdk.trace.export import (
BatchSpanProcessor,
ConsoleSpanExporter,
)
provider = TracerProvider(
resource=Resource.create({"service.name": "warehouse-etl"})
)
provider.add_span_processor(BatchSpanProcessor(ConsoleSpanExporter()))
trace.set_tracer_provider(provider)
tracer = trace.get_tracer("warehouse.dimension_load")
with tracer.start_as_current_span("apply_dimension_batch") as span:
span.set_attribute("warehouse.model", "dim_customer")
span.set_attribute("warehouse.batch_id", "batch-1842")
# Run validation and transactional dimension updates here.
SLI/SLOs: Treat these targets as starting proposals, not universal guarantees. Set them against business freshness needs and observed workload.
| SLI | Example starting SLO | Why it matters |
|---|---|---|
| Scheduled dimension runs completed on time | 99% per month | Downstream consumers receive usable history |
| Successful loads without temporal invariant violations | 99.9% per month | Prevents silent corruption of version intervals |
| Fact-to-dimension match rate | At least 99.9% per load | Detects missing or unresolvable dimension members |
| Load completion duration | Within the agreed freshness window | Keeps reporting aligned with business expectations |
Page on: A run failure that will breach a freshness commitment; any detected interval overlap or multiple-current-row violation; sustained fact-to-dimension match degradation; and a growing queue or resource saturation that threatens the next scheduled run. Route isolated, recoverable late changes to an operational review queue unless they indicate a broader correctness failure.
Quick-revision checklist
Before you answer a temporal-modeling interview question, check that you can:
- Explain the business question Type 2 history answers.
- Separate the natural key from the version’s surrogate key.
- Define valid-time boundaries and distinguish them from load time.
- State which attributes use Type 1, Type 2, or another policy.
- Describe the expected behavior for late or out-of-order changes.
- Show how facts resolve to a dimension version.
- Name invariants for current rows, interval validity, and overlap.
- Explain the transaction and concurrency strategy.
- Describe how you test corrections, boundaries, and missing members.
- Identify logs, metrics, traces, and alerts that expose incorrect history—not just failed jobs.
This article was generated with AI. #AIGenerated