How it Works

The expect_table_columns_to_match_set test from the dbt-expectations package validates that a table's columns exactly match a defined set of column names, regardless of their order. This is more lenient than expect_table_columns_to_match_ordered_list (which requires order) but stricter than expect_table_columns_to_contain_set (which allows extras). It ensures the column set is exactly right — no more and no less.

An optional transform argument enables case-insensitive matching.

Steps and Conditions

1. Define Column Set: List all expected column names.

2. Optional Transform: Apply lower or other transforms for case-insensitive matching.

3. Execution: The actual columns are compared against the expected set.

4. Outcome: Pass if the sets match exactly; fail if any columns are added or missing.

Example Usage: Data Contract

A data team has a formal data contract for the billing_summary model that specifies exactly which columns must be present, with no extras permitted.

models:
  - name: billing_summary
    tests:
      - dbt_expectations.expect_table_columns_to_match_set:
          column_list: ["account_id", "billing_period", "amount_due", "amount_paid", "balance"]
          transform

models:
  - name: billing_summary
    tests:
      - dbt_expectations.expect_table_columns_to_match_set:
          column_list: ["account_id", "billing_period", "amount_due", "amount_paid", "balance"]
          transform

models:
  - name: billing_summary
    tests:
      - dbt_expectations.expect_table_columns_to_match_set:
          column_list: ["account_id", "billing_period", "amount_due", "amount_paid", "balance"]
          transform

Any schema drift — whether adding undocumented columns or removing contracted ones — causes the test to fail.