How it Works

The expect_column_to_exist test from the dbt-expectations package verifies that a specified column is present in the table or model. This is one of the most basic yet essential schema validation checks, ensuring that expected columns haven't been accidentally renamed or dropped by an upstream change.

An optional column_index argument can also be provided to verify the column appears at a specific position in the schema.

Steps and Conditions

1. Column Name: Specify the column that must exist using the column_name argument.

2. Optional Index Check: Provide column_index to also validate the column's position.

3. Execution: The test inspects the model's schema metadata.

4. Outcome: Pass if the column exists (and is at the correct index if specified); fail otherwise.

Example Usage: Data Warehouse

A data engineering team wants to guard against accidental removal of the event_timestamp column from their core events table during schema migrations.

models:
  - name: core_events
    tests:
      - dbt_expectations.expect_column_to_exist:
          column_name

models:
  - name: core_events
    tests:
      - dbt_expectations.expect_column_to_exist:
          column_name

models:
  - name: core_events
    tests:
      - dbt_expectations.expect_column_to_exist:
          column_name

If a migration removes or renames this critical column, the test fails immediately and blocks the pipeline.