Generating Surrogate Keys Across Warehouses

Metadata

• Author: getdbt.com
• Full Title:: Generating Surrogate Keys Across Warehouses
• Category:: 🗞️Articles
• Document Tags:: dbt, SQL,
• URL:: https://docs.getdbt.com/blog/sql-surrogate-keys
• Finished date:: 2023-10-08

Highlights

every table should have a primary key. Primary keys are critical for many reasons: • They ensure that you don’t have duplicate rows in your table • They help establish relationships to other tables • They allow you to quickly identify the grain of the table (ex: the customers table with a PK of customer_id has one row per customer) • You can test them in dbt, to ensure that your data is complete and unique (View Highlight)

For example, maybe you have joined your users to the set of features that they use in your product, such that your data should be expected to be unique on the user_id + product_id basis. The question is - in this situation, how are you supposed to set a primary key? Meet the surrogate key. (View Highlight)