[v2 Bug] [BigQuery] Unit tests on incremental models fail with "Unrecognized name: _PARTITIONTIME" when expect is empty or format: sql #16695

Is this a new bug in dbt v2.x compared to the latest version of dbt 1.x?

• I believe this is a new bug in dbt v2.x
• I have searched the existing issues and could not find a duplicate

Current Behavior

A unit test on a BigQuery incremental model fails with:

Unrecognized name: _PARTITIONTIME

This happens when all of these are true:

• the model is partitioned on a column (partition_by with field)
• the test sets overrides.macros.is_incremental: true
• expect is empty (rows: []) or uses format: sql

v2 builds the expected-vs-actual comparison from the columns of the model's existing table, and that column list includes the pseudo-columns _PARTITIONTIME and _PARTITIONDATE. They end up in the typed expect STRUCT and in the comparison SELECT / ORDER BY. The model's actual output has no such columns, so BigQuery rejects the query.

Compiled SQL (project and dataset names shortened):

`..._my_model_expect` as (SELECT *  FROM UNNEST(ARRAY<STRUCT<id INT64, created_at TIMESTAMP, _PARTITIONTIME TIMESTAMP, _PARTITIONDATE DATE>>[])),
...
SELECT * FROM (
  (SELECT id, created_at, _PARTITIONTIME, _PARTITIONDATE, 'actual' AS actual_or_expected FROM `..._my_model_actual`)
  UNION ALL
  (SELECT id, created_at, _PARTITIONTIME, _PARTITIONDATE, 'expected' AS actual_or_expected FROM `..._my_model_expect`)
) unit_test_diff
ORDER BY id, created_at, _PARTITIONTIME, _PARTITIONDATE

These variants pass on v2:

• the same test with expect rows given as a dict
• the same empty expect with is_incremental: false
• the same test on an unpartitioned incremental model

Partition expiration makes no difference.

Expected Behavior

Pseudo-columns (_PARTITIONTIME, _PARTITIONDATE) are left out of the unit test's expect schema and comparison column list, as in dbt 1.x. The test compares only the model's real output columns and passes.

The same project passes on dbt-core 1.12.5 / dbt-bigquery 1.12.

Steps To Reproduce

dbt_project.yml:

name: repro
version: "1.0.0"
profile: repro

models/my_model.sql:

{{
    config(
        materialized = "incremental",
        incremental_strategy = "merge",
        unique_key = "id",
        partition_by = {"field": "created_at", "data_type": "timestamp", "granularity": "day"},
    )
}}

select * from (select 1 as id, timestamp('2026-01-01') as created_at) as src
{% if is_incremental() %}
where id not in (select id from {{ this }})
{% endif %}

models/_unit_tests.yml:

unit_tests:
  - name: test_empty_expect
    model: my_model
    overrides:
      macros:
        is_incremental: true
    given:
      - input: this
        rows:
          - {id: 1, created_at: "2026-01-01 00:00:00"}
    expect:
      rows: []

  - name: test_sql_expect
    model: my_model
    overrides:
      macros:
        is_incremental: true
    given:
      - input: this
        rows:
          - {id: 2, created_at: "2026-01-01 00:00:00"}
    expect:
      format: sql
      rows: |
        select 1 as id, timestamp('2026-01-01') as created_at

1. Point the repro profile at any BigQuery dataset.
2. dbt run -s my_model (the table must exist so its columns can be introspected).
3. dbt test -s my_model.

Relevant log output

[error] [JinjaError (dbt1501)]: Error materializing unit test unit_test.repro.my_model.test_empty_expect: [BigQuery] googleapi: Error 400: Unrecognized name: _PARTITIONTIME at [14:33], invalidQuery
[error] [JinjaError (dbt1501)]: Error materializing unit test unit_test.repro.my_model.test_sql_expect: [BigQuery] googleapi: Error 400: Unrecognized name: _PARTITIONTIME at [14:33], invalidQuery
==================== Execution Summary =====================
Summary: 2 total | 0 success | 2 error

Environment

- OS: macOS (Darwin 27.0.0)
- CPU: ARM (arm64)
- dbt distribution and version: dbt 2.0.8 (local binary from the official install script). Also reproduced on 2.0.6, both locally and on the dbt platform `fusion-stable` release track. BigQuery adapter, location `europe-west1`.

Which database adapter are you using?

bigquery

Is this a discrepancy vs. dbt 1.x?

• Yes — this works in dbt 1.x but not in dbt v2.x

Additional Context

Same family as #14640 and #15357: BigQuery pseudo-columns leaking from relation column introspection. #15357 fixed the mock input schemas (given), but the column list for the expected/actual comparison still includes the pseudo-columns when the expect schema is taken from the existing relation. Related open issue: #14383 (pseudo-columns in SELECT * expansion).

Workaround: give expect rows as a dict with explicit columns. For tests that expect an empty result we found no workaround: rows: [] and format: sql both fail.

Impact: this blocks moving our production jobs to v2. 6 incremental-model unit tests in our project fail on v2 and pass on 1.12.5.