How it Works

The expect_table_columns_to_contain_set test from the dbt-expectations package checks that a model, seed, or source contains all columns from a predefined list. Unlike stricter schema tests, this test does not fail if the table has additional columns beyond those specified — it only validates that the required ones are present. An optional transform argument (e.g., lower) can be applied to normalize column name casing before comparison.

Steps and Conditions

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

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

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

4. Outcome: Pass if all listed columns exist; fail if any are missing.

Example Usage: Logistics Platform

A logistics platform needs to ensure its shipments source table always contains the core fields needed for downstream reporting, regardless of any extra columns the upstream team may add.

sources:
  - name: logistics
    tables:
      - name: shipments
        tests:
          - dbt_expectations.expect_table_columns_to_contain_set:
              column_list: ["shipment_id", "carrier", "status", "dispatched_at"]
              transform

sources:
  - name: logistics
    tables:
      - name: shipments
        tests:
          - dbt_expectations.expect_table_columns_to_contain_set:
              column_list: ["shipment_id", "carrier", "status", "dispatched_at"]
              transform

sources:
  - name: logistics
    tables:
      - name: shipments
        tests:
          - dbt_expectations.expect_table_columns_to_contain_set:
              column_list: ["shipment_id", "carrier", "status", "dispatched_at"]
              transform

This guards against upstream schema changes that might silently drop critical fields, protecting downstream models from breaking unexpectedly.