[Constraints] Constraint enforcement fails for types that cannot be cast to directly using cast function

[MichelleArk]

Is this a new bug?

• I believe this is a new bug
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

While enforcing a model contract, dbt build does a CAST(null as GEOGRAPHY) which is invalid for Snowflake, because we are not using the adapter-specific cast macro in get_empty_schema_sql here.

Casting null to GEOGRAPHY or GEOMETRY via the cast function is invalid for Snowflake, and needs to be to_geography or to_geometry instead (See https://docs.snowflake.com/en/sql-reference/functions/to_geography).

This can be solved at the base adapter level now that we have a global, overridable cast macro. Snowflake provides an override that would handle the geography and geometry cases. No other first-party maintained adapter has a custom override yet.

Expected Behavior

dbt is able to enforce contracts that include types that cannot be cast to directly via the cast function such as geometry and geography in Snowflake.

Steps To Reproduce

1. Create a model with a geography and/or geometry type field in snowflake

--- model.sql
SELECT 
TO_GEOMETRY('POINT(1820.12 890.56)') as geometry_field, 
TO_GEOGRAPHY('POINT(-122.35 37.55)') as geography_field

2. define an enforced contract for that model on its geography type:

models:
  - name: model
    config:
      contract:
        enforced: true
    columns:
      - name: geometry_field
        data_type: geometry
      - name: geography_field
        data_type: geography

4. Run dbt build and see the error message:

01:38:59    Database Error in model my_model_data_type (models/model.sql)
  001007 (22023): SQL compilation error:
  invalid type [CAST(null AS GEOGRAPHY)] for parameter 'TO_GEOGRAPHY'

Relevant log output

No response

Environment

- OS:
- Python:
- dbt-adapter:

Additional Context

No response