Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Model grain and uniqueness tests

dbt · Workflow, CI & Performance

Model grain and uniqueness tests

Mediumdbt-52
model-graindata-qualitytestingduplicates

Question

How do you protect a fact model from duplicate rows?

Solution

Protecting a fact model from duplicate rows requires explicitly declaring its grain and enforcing that grain with automated tests at every transformation step. You document what one row represents, generate surrogate keys for composite grains, test for uniqueness in CI, and validate that intermediate joins do not inadvertently multiply row counts.

Defining and documenting grain

Every fact table must have an explicitly documented grain. The grain defines the real-world entity or event that a single table row represents.

Document the grain clearly in the model description in YAML:

models:
  - name: fct_order_items
    description: "Grain: One row per line item within a customer order."

If engineers and analysts know the intended grain, they can quickly identify whether a query result or join condition violates table design.

Automated uniqueness tests

Once the grain is established, attach automated tests to guarantee uniqueness on that grain:

  • If the table has a natural single-column primary key, apply the standard unique and not_null tests to that column.
  • If the grain is composite, create a hashed surrogate key in SQL using dbt_utils.generate_surrogate_key on the key columns, and test uniqueness on that surrogate column.
  • Alternatively, apply the dbt_utils unique_combination_of_columns test directly across the composite columns in YAML.

Configure these tests with severity error so that any uniqueness failure halts CI pipelines before deployment.

Guardrails against join explosion

Duplicate rows in fact models almost always stem from fan-out joins in intermediate models, such as joining a 1-to-many relationship without prior aggregation.

To detect join fan-outs before they reach production:

  • Write custom data tests comparing row counts between the staging source and the downstream fact table.
  • Use packages like dbt-expectations with tests like expect_table_row_count_to_equal_other_table when row counts must match 1-to-1.
  • Test intermediate model outputs immediately after performing joins rather than waiting to test the final mart table.

Catching cardinality mismatches early prevents silent data duplication from corrupting downstream financial and operational reports.

PreviousNext