Testing a Compound Key

Concepts covered: Data tests

fct_order_lines holds 1 row per line of an order. No single column identifies a row: order_id repeats across the lines of one order, and line_number repeats across every order. The grain is the pair, and a unique test on either column alone fails on correct data. Proving 1 row per pair needs a test that sees both columns at once.

In the sample, order 1042 carries line 2 twice, and that fifth row is the defect. unique on order_id reports 1041 and 1042 as duplicates, which is the correct shape of the data. unique on line_number reports 1 and 2. Neither points at the row that is wrong.

A singular test over the pair

The direct route is a singular test that groups by both columns and keeps the groups holding more than 1 row:

select
    order_id,
    line_number,
    count(*) as n_records
from {{ ref('fct_order_lines') }}
group by order_id, line_number
having count(*) > 1

This is the query the unique test sends, with a 2-column group in place of 1. On the sample it returns 1 row, 1042, 2, 2, and with failures stored that row names the order to look at. The test adds no column to the model and works for a key of any width; a 4-column key is a 4-column group by.

A key column the generic tests can see

The other route changes the model. Concatenating the parts into one column gives downstream models a single join key and gives the generic tests a single column to guard. fct_order_lines builds it in its SELECT:

select
    cast(order_id as varchar) || '-' || cast(line_number as varchar) as order_line_key,
    order_id,
    line_number,
    sku,
    line_total
from {{ ref('stg_order_lines') }}

With the column in place, the properties file guards it with the 2 tests every key gets, and guards each part as well:

models:
  - name: fct_order_lines
    columns:
      - name: order_line_key
        data_tests:
          - unique
          - not_null
      - name: order_id
        data_tests:
          - not_null
      - name: line_number
        data_tests:
          - not_null

2 ways the key column lies

A concatenated key has 2 failure modes, and they are handled in different places. First, a null in either part makes the whole key null, because || with a null yields null. unique ignores nulls, so 500 lines with a missing line_number all produce a null key and unique passes over all of them. The not_null on order_line_key catches the result directly, and the not_null on each part names the cause. Second, parts that run together without a separator collide: order 1 line 12 and order 11 line 2 both become 112. The - in the model's SQL is what prevents that, keeping them apart as 1-12 and 11-2, so the separator must be a character that cannot appear in either part. Without it, the YAML would expose the collision only indirectly, as a unique failure on the key 112 even though the 2 rows behind it are different lines.

Which route to take

Build the key column when other models join to the grain: fct_refunds needs to point at one order line, and a single order_line_key column makes that a one-column join and a relationships test. Use the singular test when the pair is a grain nobody joins to, such as a daily snapshot keyed by merchant_id and snapshot_date, where a column no model reads buys nothing. In both cases, not_null on each part stays, because the grain is proven only when every part is present.

About this lesson

This section is part of the Testing: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.