Type 0: Retain Original

With Type 0, the dimension attribute value never changes. Once it is written, it remains static forever.

Example (Type 0)

Alice signed up in New York (NY) on a specific date. She later moves to California (CA). Her original_state and sign_up_date are Type 0 attributes.

customer_id	name	original_state (Type 0)	sign_up_date (Type 0)
C-101	Alice	NY	2023-01-15

After the move: The row remains exactly the same. Even if the source system updates her state to CA, the data warehouse intentionally discards that change for the original_state attribute.

Type 0 Attributes vs Pure Type 0 Dimensions

Type 0 is generally applied in two distinct ways:

Type 0 Attributes

Individual attributes are locked when the row is inserted, while other attributes in the same row can still change. Examples:

• original_sign_up_date
• account_creation_branch
• date_of_birth

Pure Type 0 Dimensions

Every column is locked, meaning a row is never updated after insertion. This is common in three scenarios:

• Date and Time Dimensions (dim_date, dim_time): The attributes of a specific date never change. For example, January 1, 2024, will always be a Monday and a public holiday. Once generated, this table is completely static.
• Static Reference Data (dim_country_code, dim_currency): These are lookup tables defining fixed business concepts or external standards. The definition of "USD - US Dollar" does not change over time.
• Immutable Event Profiles: These dimensions record the exact state of an event the moment it happens and cannot be altered. For example, a dim_checkout_context table capturing the device, app version, payment method and promo code used for a specific order.

Error corrections do not count as SCD changes

Type 0 means the real-world value never changes, not that the row is never touched. If a sign_up_date was entered incorrectly, it still gets fixed. Correcting a mistake is not considered a true change because the old value was never accurate to begin with.