Overriding other libraries usage of casting with :: to avoid Incorrect syntax near '::' error

[alison985]

SQL Server doesn't support :: casting.

dbt_utils default__current_timestamp() uses :: to perform the cast. I think this adapter, dbt-sqlserver, should adjust to support this case and any others lurking in one of the supported libraries.

[dataders]

hey @alison985! nice to see you on here. have you happened upon tsql-utils yet?

That said, last fall we moved a sizeable chunk of macros from dbt-utils into the adapters themselves, and it looks like current_timestamp() was one of those migrated macros. So in theory dbt_utils.current_timestamp() shouldn't need the tsql-utils pacakge installed for it to work properly.

I see it in dbt/include/sqlserver/macros/utils/timestamps.sql and it was released as part of dbt-sqlserver 1.4.0.

dbt-sqlserver/dbt/include/sqlserver/macros/utils/timestamps.sql

Lines 1 to 3 in 9546c40

	{% macro sqlserver__current_timestamp() -%}
	SYSDATETIME()
	{%- endmacro %}

[alison985]

Hi @dataders! Thanks for the response and the background. I do already have tsql-utils installed. It was listed on the docs page. Here's the long story.

Versions of all the things

Right now I'm on dbt v1.4.6 so this dbt-sqlserver package works in Docker to SQL Server 2019, and then dbt Cloud is set to 1.5 with dbt_project.yml saying require-dbt-version: [">=1.4.6", "<1.6.0"] to allow both to work.

pip list says dbt-core is 1.4.6 and dbt-sqlserver is 1.4.3.

My packages.yml is:

packages:
  - package: calogica/dbt_expectations #auto imports dbt-date and dbt-utils
    version: 0.6.0
  - package: dbt-labs/dbt_utils
    version: [">=0.9.0", "<2.0.0"] #audit_helper requirement as of 5/22/23
  - package: dbt-labs/audit_helper
    version: 0.9.0
  - package: dbt-msft/tsql_utils #needed for dbt-utils to work with SQL server 7/11/23
    version: 0.9.0

Specific example that led me down this path

This works in Redshift.

      - dbt_utils.expression_is_true:
          expression: due_date < convert_timezone('EDT', 'UTC', current_timestamp::timestamp)::date

Now try a) getting it to work in SQL Server as well and b) keep it DRY between Redshift and T-SQL. 😬 While:

• we can't inherit YAML 🤞 yet 🤞 , see Doc (and potentially, Test) Inheritance dbt-labs/dbt-core#6527 (comment)
• Redshift must be current_timestamp for us because I went through all the permutations of ways to get at the concept and most return different results - including timezone complications - so I standardized us to that one keyword. Note it's not a function, or AKA current_timestamp() doesn't work. Also I'm ignoring the eventual deprecation of the Redshift keyword for now.
• In Redshift SYSDATETIME() doesn't exist and current_timestamp has a timezone.
  
• In SQL Server 2019 both the SYSDATETIME() function and the keyword exist. I can handle losing 4 digits of precision in microseconds. However, there's no timezone included in either.
  

dbt compile on the model with the Redshift example test works against SQL Server. So I proceeded to dbt build the model that has the written-for-Redshift test against SQL Server. I get back ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near '::'. (102) (SQLMoreResults)").

In order to get around the '::' error, I reconfigured the test statement to get rid of my uses of '::'. It became: due_date < cast( convert_timezone('EDT', 'UTC', current_timestamp) as date )

That errored as: ('42000', "[42000] [FreeTDS][SQL Server]'convert_timezone' is not a recognized built-in function name. (195) (SQLMoreResults)"). Okay, so I try the below which uses what should be dialect abstracted items for timezone and timestamp.

dbt_project.yml
vars:
  sql_server_timezone: 'EDT'

schema.yml
      - dbt_utils.expression_is_true:
          expression: >
                      due_date < cast(
                          {{ dbt_date.convert_timezones( 
                                current_timestamp()
                                , 'UTC'
                                , var('sql_server_timezone') 
                              ) }}
                         as date)

Error: 'dict object' has no attribute 'convert_timezones'. This can happen when calling a macro that does not exist. Check for typos and/or install package dependencies with "dbt deps".

Well, dbt_date.convert_timezone() looks okay. Highlight: I mistakenly put a 's' at the end even though this function doesn't have it.

tsql_utils.convert_timezone() looks okay. Note it does have an 's' on the end of the file name. On the other hand, the macro name does not have the s on the end.

Meanwhile, dbt_utils.current_timestamp() goes to the "default__" macro which has a ::timestamp.

Then I read your comment and asked myself "what am I missing??" That made me look at the tsql-utils repo's README itself. It said I needed to add dispatch info to dbt_project.yml so it would get recognized, which I did.

dispatch:
  - macro_namespace: dbt_utils
    search_order: ['tsql_utils', 'dbt_utils']
  - macro_namespace: dbt_date
    search_order: ['tsql_utils', 'dbt_date']
  - macro_namespace: dbt_expectations
    search_order: ['tsql_utils', 'dbt_expectations']
  - macro_namespace: audit_helper
    search_order: ['tsql_utils', 'audit_helper']

So now, in theory, I should be fully set up and fine once I change dbt_date.convert_timezones to dbt_date.convert_timezone, right? For complete reference:

      - dbt_utils.expression_is_true:
          expression: >
                      due_date < cast(
                          {{ dbt_date.convert_timezone( 
                                current_timestamp()
                                , 'UTC'
                                , var('sql_server_timezone') 
                              ) }}
                         as date)

No go.

Completed with 1 error and 0 warnings:
19:12:19  
19:12:19  Compilation Error in test dbt_utils_expression_is_true_MODEL_NAME_due_date_cast_dbt_date_convert_timezone_current_timestamp_UTC_var_sql_server_timezone_as_date_ (models/MODEL_SUBFOLDER/MODEL_SUBFOLDER2/schema.yml)
19:12:19    Warning: the `type_timestamp` macro is now provided in dbt Core. It is no longer available in dbt_utils and backwards compatibility will be removed in a future version of the package. Use `type_timestamp` (no prefix) instead. The DBT_PROFILE_NAME.dbt_utils_expression_is_true_MODEL_NAME_due_date_cast_dbt_date_convert_timezone_current_timestamp_UTC_var_sql_server_timezone_as_date_ model triggered this warning.
19:12:19    
19:12:19    > in macro xdb_deprecation_warning (macros/cross_db_utils/deprecated/xdb_deprecation_warning.sql)
19:12:19    > called by macro default__type_timestamp (macros/cross_db_utils/deprecated/datatypes.sql)
19:12:19    > called by macro type_timestamp (macros/cross_db_utils/deprecated/datatypes.sql)
19:12:19    > called by macro sqlserver__convert_timezone (macros/dbt_date/calendar_date/convert_timezones.sql)
19:12:19    > called by macro convert_timezone (macros/calendar_date/convert_timezone.sql)
19:12:19    > called by test dbt_utils_expression_is_true_MODEL_NAME_due_date_cast_dbt_date_convert_timezone_current_timestamp_UTC_var_sql_server_timezone_as_date_ (models/MODEL_SUBFOLDER/MODEL_SUBFOLDER2/schema.yml)

I'm now basically in a game of inception.