valid_history incremental strategy fails with Teradata Error 9134 when tracked columns are fixed-width CHAR

[Pibbers]

Summary

The valid_history incremental strategy (teradata__get_incremental_valid_history_sql in
dbt/include/teradata/macros/materializations/incremental/strategies.sql) generates a
TD_NORMALIZE_MEET call whose RETURNS clause declares every tracked column's type from
adapter.get_columns_in_relation(source). For any column that is physically CHAR(n)
(fixed-width), the adapter reports its type as character varying(n) instead of
character(n). Teradata rejects the resulting mismatch between the declared RETURNS type
and the actual physical type with:

[Teradata Database] [Error 9134] Normalize: The output grouping column list does not match
with input grouping column list

This only surfaces on the second and subsequent runs of an incremental model using
valid_history. The first run takes the "table doesn't exist yet" branch of the incremental
materialization (a plain create table as select), which never calls
teradata__get_incremental_valid_history_sql at all — so the bug is invisible until the
model is actually run incrementally, which is the strategy's entire purpose.

Environment

• dbt-core: 1.10.15
• dbt-teradata: 1.11.0
• Teradata Database: 20.0.0.50 (Vantage Express / trial instance)
• OS: Windows 11 (client-side; server-side irrelevant to this bug)

Root cause

Two independent bugs compound to produce this:

1. teradata__get_columns_in_relation collapses CHAR and VARCHAR into the same dtype label

File: dbt/include/teradata/macros/adapters.sql, macro teradata__get_columns_in_relation
(starts line 170), the type-mapping CASE expression, lines 221–222:

WHEN ColumnsV.ColumnType = 'CF' THEN 'CHARACTER'
WHEN ColumnsV.ColumnType = 'CV' THEN 'CHARACTER'

CF is Teradata's catalog code for fixed-width CHARACTER and CV is CHARACTER VARYING
(see DBC.ColumnsV.ColumnType in Teradata docs). Both are mapped to the identical string
'CHARACTER', discarding the fixed-vs-variable distinction before it ever leaves the SQL
layer.

2. The base Column.string_type() always renders VARCHAR, and TeradataColumn doesn't override it

TeradataColumn (dbt/adapters/teradata/column.py) subclasses the generic
dbt.adapters.base.column.Column and does not override is_string() or string_type().
The base implementation (dbt-adapters, dbt/adapters/base/column.py):

def is_string(self) -> bool:
    return self.dtype.lower() in [
        "text",
        "character varying",
        "character",
        "varchar",
        ...
    ]

@classmethod
def string_type(cls, size: int) -> str:
    return "character varying({})".format(size)

is_string() correctly recognizes dtype == "character" as a string type, but
string_type() has no branch for fixed-width CHAR at all — it unconditionally returns
"character varying({size})" regardless of whether the original column was CHAR or
VARCHAR. Combined with bug (1) already having erased the distinction, there is no point in
the pipeline where a CHAR(n) column's true type survives.

Net effect: column.data_type for any Teradata CHAR(n) column returns
"character varying(n)", which is silently wrong. This is a general latent bug that could
affect anything relying on get_columns_in_relation(...).data_type to reproduce a column's
real DDL type — the valid_history strategy is simply the one place that surfaces it as a
hard failure, because it feeds the (wrong) declared type straight into a RETURNS clause
that Teradata validates strictly against the physical type flowing through
TD_NORMALIZE_MEET.

Minimal reproduction

-- 1. A source table with one CHAR(1) column among the tracked columns
CREATE TABLE demo.party_src (
    party_id VARCHAR(10),
    full_name VARCHAR(100),
    marketable_flag CHAR(1),   -- <-- fixed-width CHAR, not VARCHAR
    load_period PERIOD(DATE)
);

-- models/party_history.sql
{{ config(
    materialized='incremental',
    incremental_strategy='valid_history',
    unique_key='party_id',
    valid_period='load_period',
    use_valid_to_time='no',
    resolve_conflicts='yes'
) }}

select
    party_id,
    full_name,
    marketable_flag,          -- CHAR(1), inherited as-is
    PERIOD(current_date, date '9999-12-31') as load_period
from {{ source('demo', 'party_src') }}

Run it twice:

dbt run --select party_history          # 1st run: CTAS path — succeeds, masks the bug
dbt run --select party_history          # 2nd run: true incremental path — FAILS

The second run fails with Error 9134. I confirmed this by capturing the exact generated SQL
via dbt run --debug and re-executing it directly against Teradata with teradatasql,
bisecting column-by-column: any 1+ tracked column that is physically CHAR (not VARCHAR)
triggers the failure; declaring that column's RETURNS type as CHAR(n) instead of
VARCHAR(n) — matching its true physical type — makes the identical statement succeed.
Column count and ordering were not the cause; I initially suspected that and ruled it out
by testing 1–5 tracked columns in every combination with and without a CHAR-typed column
present.

The generated (failing) RETURNS clause looks like:

RETURNS (
    party_id character varying(10),
    full_name character varying(100),
    marketable_flag character varying(1),   -- WRONG: physical type is CHAR(1)
    load_period PERIOD(DATE)
)

Manually correcting only that one type to character(1) (or char(1)) makes the exact same
TD_NORMALIZE_MEET statement execute successfully.

Workaround

Cast any CHAR-typed tracked column to VARCHAR in the model's select before it reaches
the valid_history materialization:

select
    party_id,
    full_name,
    CAST(marketable_flag AS VARCHAR(1)) as marketable_flag,
    PERIOD(current_date, date '9999-12-31') as load_period
from {{ source('demo', 'party_src') }}

This works because adapter.get_columns_in_relation() then genuinely sees a VARCHAR
column, so the (buggy but consistent) "always render as character varying" behavior no
longer disagrees with the physical type.

Suggested fix

Either:

• In teradata__get_columns_in_relation (adapters.sql), stop collapsing CF/CV into the
  same label — e.g. WHEN ColumnsV.ColumnType = 'CF' THEN 'CHARACTER' /
  WHEN ColumnsV.ColumnType = 'CV' THEN 'CHARACTER VARYING', and
• Override is_string()/string_type() (or data_type) on TeradataColumn to render
  character(n) vs character varying(n) based on the (now-distinguished) dtype, rather than
  inheriting the base class's VARCHAR-only default.

Doing only one of the two is not sufficient — both layers currently lose the same
information independently.