How do you handle data versioning in a data warehouse to ensure data integrity?

Official answer

Separate three needs: reproducing a dataset snapshot, tracking an entity's business history, and versioning the transformation/schema. Record source boundaries, pipeline version and output version so a result can be reconstructed within the retention policy.

For customer history, Type 2 slowly changing dimensions can store a surrogate version key, business key, valid_from and valid_to. Use a documented interval convention such as [valid_from, valid_to), prevent overlapping versions for an entity and resolve each fact to the version valid at the relevant business time. A correction received today may describe an earlier effective time; ingestion time and valid time are different.

Table formats such as Iceberg can expose snapshots and historical queries, but availability depends on retained metadata/data files and supported engine operations. Snapshot expiration or cleanup can make old states unavailable. Time travel is not a substitute for an independently recoverable backup, and not every rollback is safe after new writes.

An append-oriented source log can help audit and replay, but “never delete anything” is not an acceptable universal retention rule. Apply approved deletions, holds and retention to versions and derived copies. Validate completeness, overlapping intervals, stable keys and source reconciliation; preserved history alone does not prove that its contents are correct.