Snapshots fail with `Invalid object name '..._dbt_tmp'` on their second run; contract-enforced models silently lose `pre_hook` writes

[Benjamin-Knight]

Summary

SQLServerAdapter.get_column_schema_from_query executes the query it is asked to describe, reads cursor.description, then returns without fetching the rows or closing the cursor. When that abandoned cursor is closed while the server is still producing the result set, the driver cancels the request. The cancel reaches SQL Server as an attention, and because every connection this adapter opens runs SET XACT_ABORT ON (#718), SQL Server answers an attention by rolling back the open transaction.

Nothing raises. The rollback is silent, so the damage surfaces later and somewhere else — most visibly as a snapshot failing against a staging table it successfully built moments earlier in the same transaction.

Symptoms

1. Snapshots fail on their second and subsequent runs.

Runtime Error in snapshot snap (snapshots/snap.sql)
  ('42S02', "[42S02] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
  Invalid object name 'dbt_test.snap__dbt_tmp'. (208)")

The first run succeeds (the snapshot is created, not merged). The second run builds the staging table, the transaction is then rolled back out from under it by the probe, and the merge a statement later cannot see it.

2. Contract-enforced models silently lose their in-transaction pre_hook writes.

No error at all — the model builds and reports success, but any pre_hook that wrote inside the same transaction has been rolled back. This is the same mechanism with a worse failure mode: nothing tells you it happened.

Reproduction

Any snapshot, run twice, with enough source data that the server is still streaming when the probe returns:

-- models/snap_source.sql
{{ config(materialized='table') }}
select top (5000)
    row_number() over (order by (select null)) as id,
    cast(replicate('x', 500) as varchar(8000)) as payload,
    cast('2024-01-01' as datetime2) as updated_at
from sys.all_objects a cross join sys.all_objects b

-- snapshots/snap.sql
{% snapshot snap %}
{{ config(target_schema=schema, unique_key='id',
          strategy='timestamp', updated_at='updated_at') }}
select * from {{ ref('snap_source') }}
{% endsnapshot %}

dbt run --models snap_source
dbt snapshot --select snap                 # succeeds (create)
# change the source so the second run has rows to merge, then:
dbt snapshot --select snap                 # Invalid object name '..._dbt_tmp'

Both snapshot strategies reproduce it — timestamp and check both build their staging query through sqlserver__snapshot_staging_table.

Why only some queries

sqlserver__get_empty_subquery_sql neuters a probe query by wrapping it as select * from (...) where 1 = 0. A query opening with a CTE cannot be wrapped that way and is passed through untouched, so it runs in full and returns real rows. Everything else returns nothing and so has no result set to abandon.

That is exactly why the affected paths are the ones they are:

• snapshot staging queries, which are emitted as with snapshot_query as (...) — both strategies
• contract-enforced models whose SQL opens with a CTE

Everything else stayed quiet.

Why volume matters (and why it isn't a size threshold)

The trigger is timing, not size. A cancel only raises an attention while the request is still in flight — if the server has already finished, the close is harmless. Measured against SQL Server 2022:

• a few hundred rows is already enough at zero client delay
• ~10ms of client-side work before the close makes even a 20MB result set safe

So there is no row count or byte size that divides safe from unsafe; a faster server or a slower client moves the line. Larger result sets simply make it near-certain.

Why existing tests didn't catch it

The functional coverage for these paths used seed-sized fixtures — tens of narrow rows — which complete before the cursor is abandoned and so never trip it. The contract + CTE combination had no coverage at all.

Affected surface

get_column_schema_from_query is reached from check_time_data_types → get_updated_at_column_data_type (snapshots) and from contract enforcement. The row-count probe in expand_column_types has the same shape and leaks its cursor on every call, though its select count(*) returns a single row and so does not reproduce the rollback.

Environment

• dbt-sqlserver 1.12.0rc2, dbt-core 1.12.0
• SQL Server 2022, ODBC Driver 18
• Any connection opened by this adapter — SET XACT_ABORT ON is applied at session open for all of them (Evaluate SET XACT_ABORT ON for SQL Server DML refresh transactions #718)