Repository: dbt-labs/dbt-core
Pull request: Incremental models with a contract don't need their columns modified
resolves #10362


### Problem

Incremental models with existing tables had their columns updated by "expand_target_column_types", but this is unnecessary when models have a contract.

### Solution

Do not call "expand_target_column_types" when an incremental model has an enforced contract.

### Checklist

- [x] I have read [the contributing guide](https://github.com/dbt-labs/dbt-core/blob/main/CONTRIBUTING.md) and understand what's expected of me  
- [x] I have run this code in development and it appears to resolve the stated issue  
- [x] This PR includes tests, or tests are not required/relevant for this PR
- [x] This PR has no interface changes (e.g. macros, cli, logs, json artifacts, config files, adapter interface, etc) or this PR has already received feedback and approval from Product or DX
- [x] This PR includes [type annotations](https://docs.python.org/3/library/typing.html) for new and modified functions


Problem statement:
[Bug] Incorrect column data type with incremental contracted model and varchar data_type
### Current Behavior

A temporary table is created for incremental models when the table already exists. When this temporary table is created and one of the column contracted data_types is "character varying(1)" (size is not significant) the string size is lost in the creation of the temporary relation and instead the column is altered after the temporary table is created like: alter table "xxx"."xxxx"."xxx" add column "<column_name>__dbt_alter" character varying(256);

The column is created with the wrong string size.

### Expected Behavior

The column is created with the string size that's specified in the contract. 

### Steps to recreate

Create an incremental model, update it with additional rows (causing the creation of a temporary table) and observe the the wrong string size is used.

### Additional Context

Reported internally by Lee Bond-Kennedy.

Hints from issue discussion:
I was able to reproduce this for both `varchar(1)` and `character varying(1)`. In both cases running dbt-postgres 1.6.16 the following occurs on the incremental run.

Both models are the same SQL
```
with source_data as (

    select 1 as id, 'a' as vchar

)

select *
from source_data
```


yml
```
models:
  - name: my_first_dbt_model
    config:
      materialized: incremental
      on_schema_change: append_new_columns
      contract:
        enforced: true
    description: "A starter dbt model"
    columns:
      - name: id
        description: "The primary key for this table"
        data_type: int
      - name: vchar
        description: "Test varying char contract"
        data_type: character varying(1)

  - name: my_other_model
    config:
      materialized: incremental
      on_schema_change: append_new_columns
      contract:
        enforced: true
    columns:
      - name: id
        description: "The primary key for this table"
        data_type: int
      - name: vchar
        description: "Test varying char contract"
        data_type: varchar(1)
```

logs
```
^[[0m10:17:46.720110 [debug] [Thread-1 (]: SQL status: SELECT 2 in 0.0 seconds
^[[0m10:17:46.720852 [debug] [Thread-1 (]: Changing col type from character varying(1) to character varying(256) in table database: "lee"
schema: "lee"
identifier: "my_first_dbt_model"

^[[0m10:17:46.724173 [debug] [Thread-1 (]: Using postgres connection "model.pgtest.my_first_dbt_model"
^[[0m10:17:46.724366 [debug] [Thread-1 (]: On model.pgtest.my_first_dbt_model: /* {"app": "dbt", "dbt_version": "1.6.16", "profile_name": "pgtest", "target_name": "dev", "node_id": "model.pgtest.my_first_dbt_model"} */

    alter table "lee"."lee"."my_first_dbt_model" add column "vchar__dbt_alter" character varying(256);
    update "lee"."lee"."my_first_dbt_model" set "vchar__dbt_alter" = "vchar";
    alter table "lee"."lee"."my_first_dbt_model" drop column "vchar" cascade;
    alter table "lee"."lee"."my_first_dbt_model" rename column "vchar__dbt_alter" to "vchar"
```


```
^[[0m10:21:12.614573 [debug] [Thread-1 (]: SQL status: SELECT 2 in 0.0 seconds
^[[0m10:21:12.615230 [debug] [Thread-1 (]: Changing col type from character varying(1) to character varying(256) in table database: "lee"
schema: "lee"
identifier: "my_other_model"

^[[0m10:21:12.618429 [debug] [Thread-1 (]: Using postgres connection "model.pgtest.my_other_model"
^[[0m10:21:12.618613 [debug] [Thread-1 (]: On model.pgtest.my_other_model: /* {"app": "dbt", "dbt_version": "1.6.16", "profile_name": "pgtest", "target_name": "dev", "node_id": "model.pgtest.my_other_model"} */

    alter table "lee"."lee"."my_other_model" add column "vchar__dbt_alter" character varying(256);
    update "lee"."lee"."my_other_model" set "vchar__dbt_alter" = "vchar";
    alter table "lee"."lee"."my_other_model" drop column "vchar" cascade;
    alter table "lee"."lee"."my_other_model" rename column "vchar__dbt_alter" to "vchar"
```

Commit message: Changie

Patch to review:
diff --git a/.changes/unreleased/Fixes-20240728-221421.yaml b/.changes/unreleased/Fixes-20240728-221421.yaml
new file mode 100644
index 00000000000..420414e3f52
--- /dev/null
+++ b/.changes/unreleased/Fixes-20240728-221421.yaml
@@ -0,0 +1,6 @@
+kind: Fixes
+body: Do not update varchar column definitions if a contract exists
+time: 2024-07-28T22:14:21.67712-04:00
+custom:
+  Author: gshank
+  Issue: "10362"
diff --git a/dev-requirements.txt b/dev-requirements.txt
index 20605e632b8..facac36c2e6 100644
--- a/dev-requirements.txt
+++ b/dev-requirements.txt
@@ -1,4 +1,4 @@
-git+https://github.com/dbt-labs/dbt-adapters.git@main
+git+https://github.com/dbt-labs/dbt-adapters.git@incremental_contract_varchar
 git+https://github.com/dbt-labs/dbt-adapters.git@main#subdirectory=dbt-tests-adapter
 git+https://github.com/dbt-labs/dbt-common.git@main
 git+https://github.com/dbt-labs/dbt-postgres.git@main
diff --git a/tests/functional/materializations/test_incremental_with_contract.py b/tests/functional/materializations/test_incremental_with_contract.py
new file mode 100644
index 00000000000..a9321f10118
--- /dev/null
+++ b/tests/functional/materializations/test_incremental_with_contract.py
@@ -0,0 +1,131 @@
+import pytest
+
+from dbt.tests.util import (
+    check_relations_equal,
+    get_relation_columns,
+    relation_from_name,
+    run_dbt,
+)
+
+seeds_base_csv = """
+id,name_xxx,some_date
+1,Easton,1981-05-20T06:46:51
+2,Lillian,1978-09-03T18:10:33
+3,Jeremiah,1982-03-11T03:59:51
+4,Nolan,1976-05-06T20:21:35
+5,Hannah,1982-06-23T05:41:26
+6,Eleanor,1991-08-10T23:12:21
+7,Lily,1971-03-29T14:58:02
+8,Jonathan,1988-02-26T02:55:24
+9,Adrian,1994-02-09T13:14:23
+10,Nora,1976-03-01T16:51:39
+""".lstrip()
+
+
+seeds_added_csv = (
+    seeds_base_csv
+    + """
+11,Mateo,2014-09-07T17:04:27
+12,Julian,2000-02-04T11:48:30
+13,Gabriel,2001-07-10T07:32:52
+14,Isaac,2002-11-24T03:22:28
+15,Levi,2009-11-15T11:57:15
+16,Elizabeth,2005-04-09T03:50:11
+17,Grayson,2019-08-06T19:28:17
+18,Dylan,2014-03-01T11:50:41
+19,Jayden,2009-06-06T07:12:49
+20,Luke,2003-12-05T21:42:18
+""".lstrip()
+)
+
+incremental_not_schema_change_sql = """
+{{ config(materialized="incremental", unique_key="user_id_current_time",on_schema_change="sync_all_columns") }}
+select
+    1 || '-' || current_timestamp as user_id_current_time,
+    {% if is_incremental() %}
+        'thisis18characters' as platform
+    {% else %}
+        'okthisis20characters' as platform
+    {% endif %}
+"""
+
+incremental_sql = """
+  {{ config(materialized="incremental") }}
+  select * from {{ source('raw', 'seed') }}
+  {% if is_incremental() %}
+  where id > (select max(id) from {{ this }})
+  {% endif %}
+"""
+
+schema_base_yml = """
+sources:
+  - name: raw
+    schema: "{{ target.schema }}"
+    tables:
+      - name: seed
+        identifier: "{{ var('seed_name', 'base') }}"
+
+models:
+  - name: incremental
+    config:
+      contract:
+        enforced: true
+      on_schema_change: append_new_columns
+    columns:
+      - name: id
+        data_type: int
+      - name: name_xxx
+        data_type: character varying(10)
+      - name: some_date
+        data_type: timestamp
+"""
+
+
+class TestIncremental:
+    @pytest.fixture(scope="class")
+    def project_config_update(self):
+        return {"name": "incremental"}
+
+    @pytest.fixture(scope="class")
+    def models(self):
+        return {"incremental.sql": incremental_sql, "schema.yml": schema_base_yml}
+
+    @pytest.fixture(scope="class")
+    def seeds(self):
+        return {"base.csv": seeds_base_csv, "added.csv": seeds_added_csv}
+
+    def test_incremental(self, project):
+        # seed command
+        results = run_dbt(["seed"])
+        assert len(results) == 2
+
+        # base table rowcount
+        relation = relation_from_name(project.adapter, "base")
+        result = project.run_sql(f"select count(*) as num_rows from {relation}", fetch="one")
+        assert result[0] == 10
+
+        # added table rowcount
+        relation = relation_from_name(project.adapter, "added")
+        result = project.run_sql(f"select count(*) as num_rows from {relation}", fetch="one")
+        assert result[0] == 20
+
+        # run command
+        # the "seed_name" var changes the seed identifier in the schema file
+        results = run_dbt(["run", "--vars", "seed_name: base"])
+        assert len(results) == 1
+
+        # check relations equal
+        check_relations_equal(project.adapter, ["base", "incremental"])
+
+        # change seed_name var
+        # the "seed_name" var changes the seed identifier in the schema file
+        results = run_dbt(["run", "--debug", "--vars", "seed_name: added"])
+        assert len(results) == 1
+
+        # Error before fix:  Changing col type from character varying(10) to character varying(256) in table:
+        #  "dbt"."test<...>_test_incremental_with_contract"."incremental"
+        columns = get_relation_columns(project.adapter, "incremental")
+        # [('id', 'integer', None), ('name_xxx', 'character varying', 10), ('some_date', 'timestamp without time zone', None)]
+        for column in columns:
+            if column[0] == "name_xxx":
+                assert column[2] == 10


Code review comments:
diff_hunk: @@ -1,4 +1,4 @@
-git+https://github.com/dbt-labs/dbt-adapters.git@main
+git+https://github.com/dbt-labs/dbt-adapters.git@incremental_contract_varchar
path: dev-requirements.txt
text: @user1:
Reminder to revert this prior to merging.