How it Works

The expect_column_distinct_count_to_equal_other_table test from the dbt-expectations package verifies that the number of distinct values in a specified column matches the distinct count of a column in a reference table. This is useful for validating cross-table consistency — for example, ensuring that the number of unique customers in a fact table matches the number in the dimension table.

Steps and Conditions

1. Column Selection: Identify the column to evaluate.

2. Reference Table: Specify the comparison model and column.

3. Execution: Both distinct counts are computed and compared.

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

Example Usage: Data Warehouse

A data warehouse team wants to confirm that all customer IDs in the orders fact table have a corresponding entry in the dim_customers dimension table.

models:
  - name: orders
    columns:
      - name: customer_id
        tests:
          - dbt_expectations.expect_column_distinct_count_to_equal_other_table:
              compare_model: ref('dim_customers')
              compare_column_name

models:
  - name: orders
    columns:
      - name: customer_id
        tests:
          - dbt_expectations.expect_column_distinct_count_to_equal_other_table:
              compare_model: ref('dim_customers')
              compare_column_name

models:
  - name: orders
    columns:
      - name: customer_id
        tests:
          - dbt_expectations.expect_column_distinct_count_to_equal_other_table:
              compare_model: ref('dim_customers')
              compare_column_name

A mismatch indicates that some customer IDs in orders lack dimension records, which would cause broken lookups in reporting.