Identity column has null values

rachit-prodigal — Tue, 25 Jun 2024 14:00:52 GMT

I created a table in databricks using a dbt model pre hook

CREATE TABLE IF NOT EXISTS accounts (
  account_id BIGINT GENERATED ALWAYS AS IDENTITY,
  description STRING
  other columns
)

I use the same dbt model to merge values into this table in the post-hook

post_hook = [
"
  MERGE INTO accounts AS target
  USING incremental_accounts AS source
  ON <join-condition>
  WHEN MATCHED THEN UPDATE SET
  target.description = source.description
  WHEN NOT MATCHED BY target THEN
  INSERT(description)
  VALUES (source.description)
"

the model code itself is simple in the file incremental_accounts.sql

SELECT description from new_accounts

The table accounts was created by this model and only updated by this model by the post-hook queries.

The problem is that I noticed null values in account_id which should not be the case as it is auto-generated.

I identified the same issue with 4 other tables which were built similarly using dbt models.

Can you please help me with this issue.

topic Identity column has null values in Get Started Discussions

Identity column has null values