Row Number surrogate keys with dbt

Add an integer row number surrogate key to any table with this dbt package.

Integer row numbers are often used as part of the High Water Mark + Row Number strategy to create surrogate keys. This page includes a dbt project with a generic surrogate-key generator, plus a Data Productivity Cloud pipeline that demonstrates how to use it. This functionality is useful as an initial transformation, immediately after extracting and loading new data.

Start by opening and running the sk_gen_demo orchestration pipeline.

The two loops each run over three database tables. Inside the loop, a TMP_ table is created with no surrogate key. It is sent to the dbt sk_gen model, with two parameters:

• v_source: the source table name
• v_target: the target table name (which will be re-created if necessary)

The private pipeline variable pv_name contains each table name in turn. Note the syntax of the dbt core command inside the loop, which runs the model supplying the two parameters:

dbt run --select "sk_gen" --vars "{\"v_source\": \"${pv_name}\", \"v_target\": \"${pv_name}_with_sk\"}"

Output of the pipeline is six new tables, named as per the Iteration Values in the Fixed Iterator components. There are three original tables, plus three more with the suffix _WITH_SK added. The second group of tables have an identical schema, plus one new SK column.

Downloads

Licensed under: Matillion Free Subscription License

• Download dbt-SK-DBKS.zip
  • Target: Databricks
• Download dbt-SK-SF.zip
  • Target: Snowflake