How it Works

The expect_table_columns_to_not_contain_set test from the dbt-expectations package verifies that none of the columns in a specified list are present in the model. This is the inverse of expect_table_columns_to_contain_set and is useful for ensuring that deprecated columns, PII fields, or columns that should have been dropped are no longer part of a model's schema.

An optional transform argument (e.g., lower) enables case-insensitive comparison.

Steps and Conditions

1. Define Disallowed Columns: List the column names that must NOT be present.

2. Optional Transform: Apply a transform for case-insensitive matching.

3. Verification: Each disallowed column is checked against the actual schema.

4. Outcome: Pass if none of the disallowed columns exist; fail if any are found.

Example Usage: GDPR Compliance

A GDPR compliance team wants to ensure that personally identifiable fields like full_name and date_of_birth have been removed from a reporting-layer model.

models:
  - name: reporting_customers
    tests:
      - dbt_expectations.expect_table_columns_to_not_contain_set:
          column_list: ["full_name", "date_of_birth", "ssn"]
          transform

models:
  - name: reporting_customers
    tests:
      - dbt_expectations.expect_table_columns_to_not_contain_set:
          column_list: ["full_name", "date_of_birth", "ssn"]
          transform

models:
  - name: reporting_customers
    tests:
      - dbt_expectations.expect_table_columns_to_not_contain_set:
          column_list: ["full_name", "date_of_birth", "ssn"]
          transform

If any of these PII columns remain in the reporting model, the test fails and blocks the deployment.