Data Engineering

Slowly Changing Dimension Type 2 Generator

Builds a Type 2 dimension table and its load statement from a CREATE TABLE statement.

Loading the tool…

Processing happens locally in your browser. What you paste or load is processed by this page and is not uploaded to a server. Nothing is stored unless you use a control that says it stores something, and you can clear anything this site has kept from the privacy page.

How to use this tool

  1. Paste the CREATE TABLE statement for the source table.
  2. Name the business key columns if the primary key in the DDL is not the natural key.
  3. Set the dimension and staging table names, then select Generate dimension.
  4. Review the null handling and the identity syntax against your platform before running any of it.

What scd type 2 generator does

A Type 2 dimension keeps history by closing the old row and opening a new one whenever a tracked attribute changes, which is easy to describe and fiddly to implement. The surrogate key, the validity dates, the current flag and the null handling all have to line up, and a mistake in any of them shows up months later as a total that cannot be reconciled.

This generates the dimension DDL and the load statement from the source table definition, with the business key and the tracked columns worked out from the DDL unless you name them. Treat it as a starting point rather than a finished job: the identity syntax is PostgreSQL, and how you handle a null in a tracked column is a modelling decision no generator should make for you.

Frequently asked questions

One that keeps history. When a tracked attribute changes, the existing row is closed off with an end date and a new row is opened, so a fact joined on the date range sees the attribute as it was at the time rather than as it is now.

From the primary key in the DDL unless you name the columns yourself. That guess is right often enough to be useful and wrong often enough to check: the natural key of a dimension is frequently not the primary key of the source table.

No. Review it first. The identity syntax is PostgreSQL and needs changing elsewhere, and how nulls in a tracked column are compared is a modelling decision — under standard SQL semantics, null does not equal null, which will produce a new version on every load if it is not handled.

No. Type 2 costs storage and query complexity, and it is only worth it for attributes where the historical value actually changes an answer. A corrected typo should overwrite; a customer moving to a different region probably should not.