Source freshness fails when casting loaded_at_field to timestamp

[afraijat]

General Description

Source freshness fails when casting loaded_at_field to the data type timestamp. In this case, the column is being cast from CHARACTER VARYING(500).

The names of files/tables will be omitted from the code snippets and error messages.

Error message

07:37:45  Database Error in source some_table(path/to/model)
07:37:45    ('42000', '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Type some_column2 is not a defined system type. (243) (SQLExecDirectW)')

Set up used

raw.yml:

version: 2

sources:
  - name: raw
    schema: raw

    tables:
      - name: some_table
        loaded_at_field: some_column2::timestamp
        freshness:
          warn_after: { count: 24, period: hour }
          error_after: { count: 48, period: hour }
        tags: []
        meta:
          SLA: "24 hours"
        columns:
          - name: "some_column1"
            data_type: CHARACTER VARYING(500)

          - name: "some_column2"
            data_type: CHARACTER VARYING(500)

Running with dbt-core 1.3.4 and dbt-sqlserver 1.3.2

Attempted workarounds

1. Used explicit SQL statements CAST()/CONVERT() to TIMESTAMP and DATETIMEOFFSET
2. Used explicit SQL statements CAST()/CONVERT() to DATETIMEOFFSET, DATETIME, and DATE after using SUBSTRING() to format the column to match the casted type's format
3. Surrounded the column names with [] while casting/converting with explicit SQL statements
4. Used data_type: varchar(500)

None of which helped remedy the issue.

How to repro

1. Copy the provided raw.yml file to the sources directory
2. Ensure that tables matching the schema exist and any needed credentials are set up
3. Run dbt-source freshness and then observer the error

[djagoda881]

dbt-sqlserver has released version 1.7.* think we can try if source freshness will work on this version