7. Surrogate Key for Order Lines
Difficulty: Medium · Topic: Jinja, vars & packages
Order lines have no single-column key: a line is identified by order_id and line_number together. Build fct_order_lines from {{ ref('stg_order_lines') }} with a surrogate key first.
Return order_line_key, order_id, line_number, product_id, quantity, where order_line_key is dbt_utils.generate_surrogate_key of order_id and line_number, in that order. The project already installs the dbt_utils package.
Some legacy lines have a null line_number; they must still get a key.
Your model is compiled like dbt does and run in DuckDB against 3 test cases, including hidden edge cases.
Sample inputs
{{ ref('stg_order_lines') }}
| order_id | line_number | product_id | quantity |
|---|---|---|---|
| 1 | 1 | 501 | 2 |
| 1 | 2 | 502 | 1 |
| 2 | 1 | 501 | 5 |
Expected output
| order_line_key | order_id | line_number | product_id | quantity |
|---|---|---|---|---|
| ea66c06c1e1c05fa9f1aa39d98dc5bc1 | 1 | 1 | 501 | 2 |
| 98c6f2c2287f4c73cea3d40ae7ec3ff2 | 1 | 2 | 502 | 1 |
| 8fbdbf5573b18fae93736180f8d0197a | 2 | 1 | 501 | 5 |
Hints
Hint 1
Call the macro with a list of column names:{{ dbt_utils.generate_surrogate_key(['order_id', 'line_number']) }}. Press Run to see the SQL it writes.Hint 2
Building the key yourself withmd5(order_id || '-' || line_number) gives a null key whenever a part is null, because concatenating null gives null. The macro replaces nulls with a placeholder first.More dbt problems
- Daily Revenue for a Reporting Window · Easy
- Pivot Payments with a Jinja Loop · Medium
- Smaller Builds in Development · Medium