[CT-3269] [Bug] dbt build with enforce.contract = True cannot validate datatypes from dbms extensions like postgis

[nicods-fr]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug
  Related to this enhancement : [CT-3263] [Feature] User space Jinja macro for converting DB API 2.0 type_codes into data_types #8900

Current Behavior

For a very simple model with a geometry column and contract.enforced == true, dbt build should be able to validate in some way the custom type geometry (It fails with or without Type modifiers). Instead, DBT fails with an unhandled error. For example :

With a simple model named little_geom_model

SELECT 1 AS id,
          CAST(ST_SetSRID(ST_Point(1,1), 2154) AS GEOMETRY(Point, 2154)) AS geom_column;

And its yaml associated contract:

models:
- name: little_geom_model
  config:
    contract:
      enforced: true
  columns:
    - name: id
      data_type: VARCHAR
    - name: geom_column
      data_type: Geometry(Point, 2154)
#      data_type: Geometry

the command dbt build fails like this:

07:34:22  Running with dbt=1.6.6
07:34:22  Registered adapter: postgres=1.6.6
07:34:22  Found 1 models, 156 tests, 0 seeds, 0 operation, 0 sources, 0 exposures, 0 metrics, 562 macros, 0 groups, 0 semantic models
07:34:22
07:34:26  Concurrency: 2 threads (target='xxx')
07:34:26
07:34:26  1 of 1 START sql table model xxx_schema_xxx.little_geom_model............. [RUN]
id
geom
07:34:26  Unhandled error while executing
19793
07:34:26  1 of 1 ERROR creating sql table model xxx_schema_xxx.little_geom_model.... [ERROR in 0.22s]
07:34:27
07:34:27  Running 1 on-run-end hook
07:34:27  Running dbt Constraints
07:34:27  Finished dbt Constraints
07:34:27  1 of 1 START hook: dbt_constraints.on-run-end.0 ................................ [RUN]
07:34:27  1 of 1 OK hook: dbt_constraints.on-run-end.0 ................................... [OK in 0.00s]
07:34:27
07:34:27
07:34:27  Finished running 1 table model, 1 hook in 0 hours 0 minutes and 4.32 seconds (4.32s).
07:34:27
07:34:27  Completed with 1 error and 0 warnings:
07:34:27
07:34:27    19793
07:34:27
07:34:27  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Expected Behavior

For the model above, we expect DBT to either :

1. Validate the correct type event with modifiers (Best option) : Geometry(Point, 2154)
2. Validate the type without modifiers : Geometry

Also, event if this is not possible, we expect that the exception is not "Unhandled"

Steps To Reproduce

Config: See above
Environment : POSTGIS enables Postgresql Database
command : dbt build

Relevant log output

I checked the log, this is the code that resulted in the unhandled exception stacktrace :

...
  File "C:\Users\DELGADO\Miniconda3\envs\dbt\Lib\site-packages\dbt\adapters\base\impl.py", line 307, in get_column_schema_from_query
    columns = [
              ^
  File "C:\Users\DELGADO\Miniconda3\envs\dbt\Lib\site-packages\dbt\adapters\base\impl.py", line 309, in <listcomp>
    column_name, self.connections.data_type_code_to_name(column_type_code)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\DELGADO\Miniconda3\envs\dbt\Lib\site-packages\dbt\adapters\postgres\connections.py", line 204, in data_type_code_to_name
    return string_types[type_code].name
           ~~~~~~~~~~~~^^^^^^^^^^^
KeyError: 19793

So it seems DBT depends on postgresql adapter psycopg2 knowledge of types, it should instead use the database directly to introspect types, for example using the macro https://github.com/dbt-labs/dbt-core/blob/a2d4424f922c2ba0f5884a5b39400b11901e165a/plugins/postgres/dbt/include/postgres/macros/adapters.sql#L61-L80 instead on trusting psycopg2 completeness

Environment

- OS: All (Tested on Windows, Mac, Linux)
- Python:>=3.10
- dbt:~1.6

Which database adapter are you using with dbt?

postgres

Additional Context

I think the following will lead to the same fixes : #8720

But at the same time, the problem is not necessarily related with dbt-codegen. Model contract enforcing should work with any model generated or not

[dbeatty10]

This is very timely @nicods-fr !

Good news

We just merged #8887 yesterday, and I confirmed locally that it works with your example 🎉

It worked with both of the following variants:

• data_type: Geometry(Point, 2154)
• data_type: Geometry

Two small tweaks

I did need to make two small tweaks to your example files though:

Removed the semicolon at the end:

models/little_geom_model.sql

SELECT 1 AS id,
          CAST(ST_SetSRID(ST_Point(1,1), 2154) AS GEOMETRY(Point, 2154)) AS geom_column

Changed the contracted data_type for id:

models/_models.sql

models:
  - name: little_geom_model
    config:
      contract:
        enforced: true
    columns:
      - name: id
        data_type: integer
      - name: geom_column
        data_type: Geometry

Summary

I'm going to close this as covered by #8887, but let us know if anything else comes up!