How it Works

The cardinality_equality test from dbt-utils™ validates that two columns (potentially from different models) have the same number of distinct values. This is useful for verifying that a transformation hasn't collapsed or expanded the cardinality of a key column unexpectedly, or for confirming cross-table consistency.

Steps and Conditions

1. Column Selection: Identify the column to test.

2. Comparison: Specify the reference model and its column using to and field.

3. Execution: The distinct count of each column is computed and compared.

4. Outcome: Pass if the distinct counts are equal; fail if they differ.

Example Usage: Dimension Tables

A data team wants to confirm that the number of distinct product_id values in their fact_orders model matches the number of products in the dim_products dimension table.

models:
  - name: fact_orders
    columns:
      - name: product_id
        tests:
          - dbt_utils.cardinality_equality:
              to: ref('dim_products')
              field

models:
  - name: fact_orders
    columns:
      - name: product_id
        tests:
          - dbt_utils.cardinality_equality:
              to: ref('dim_products')
              field

models:
  - name: fact_orders
    columns:
      - name: product_id
        tests:
          - dbt_utils.cardinality_equality:
              to: ref('dim_products')
              field

A mismatch would indicate that orders reference products not present in the dimension table, causing broken lookups in BI tools.