Question in Model Kind Documentation

[gtsiatsios]

On example Example of SCD Type 2 By Column in Action its mentioned: We configure the SCD Type 2 By Column model to check the columns Name and Price for changes as I checked on the model definition a column should be declared as or composition as unique_key

If the model definition is like the example on the same example above:

MODEL (
  name db.menu_items,
  kind SCD_TYPE_2_BY_COLUMN (
    unique_key id,
    columns [name, price]
  )
);

SELECT
  id::INT,
  name::STRING,
  price::DOUBLE,
FROM
  stg.current_menu_items;

how is possible to have multiple ids for the same [Name,Price] since id is the unique_key?

Thanks

[Tom-Fynes]

I think the wording is just a little confusing, the key is unique to the underlying table not the model.

When the model runs it checks the id and if the combination for columns associated to that id has had an update, if so then a new row is added to the SCD 2 table with a new valid_from and valid_to.

It is then downstream where you need to workout the latest row.

[gtsiatsios]

The model is tracking changes on columns by using unique_key id? So on this example can the model array keep multiple unique_keys ids ? Did you try to reproduce the example ?

[Tom-Fynes]

Yeah so in the example you have given on the first run you will have something like the following:

id	name	price	valid_from	valid_to
1	item 1	1.0	10/02/2026 00:00:00	NULL
2	item 2	1.0	10/02/2026 00:00:00	NULL
3	item 3	1.0	10/02/2026 00:00:00	NULL

But then the next day you run your model again and for ID 1, the name or the process has changed so your table then becomes

id	name	price	valid_from	valid_to
1	item 1	1.0	10/02/2026 00:00:00	11/02/2026 00:00:00
1	item 1	1.25	11/02/2026 00:00:00	NULL
2	item 2	1.0	10/02/2026 00:00:00	NULL
3	item 3	1.0	10/02/2026 00:00:00	NULL

This is because you are keeping the story of that row, you then need to account for this bay only taking the latest row for each ID or whatever the requirements are.

[gtsiatsios]

I think for this example is if the table is like above then you add in the stg.current_menu_items

1 | item 1 | 1.25

and already it has a row

1 | item 1 | 0

I think nothing will happen. Only if I update the row in the stg.current_menu_items and do something like:

UPDATE stg.current_menu_items SET price = 0 WHERE id = 1 and name = 'item 1'

it will trigger when you run sqlmesh run dev :
1 | item 1 | 1.0 | 10/02/2026 00:00:00 | 11/02/2026 00:00:00
1 | item 1 | 0 | 11/02/2026 00:00:00 | NULL

which database are you using?

[Tom-Fynes]

Yeah that is correct, a new row will only be added to your SCD table if an update for a change detected in stg.current_menu_items for that row.