[Epic] Cross-database cast macro

[dbeatty10]

This epic comprises the changes across dbt Labs repos necessary to implement a cross-database cast macro as described in dbt-labs/dbt-adapters#84.

Implementation

The cast function is specified in ISO/IEC 9075-2 (part 2 of the SQL standard), so there is a natural adapter-agnostic implementation:

cast({{ expression }} as {{ data_type }})

Utility

It can be useful for end-users when they are looking for an adapter-agnostic method to cast a SQL expression to a specific data type.

Having a cross-database cast macro has been suggested as a way to address a variety of things including, but not limited to:

• case-insensitive comparisons in unit testing, base unit testing test dbt-adapters#55
• [fix] use cast macro in default__get_empty_schema_sql dbt-adapters#165
• test constraints for geometry and geography types dbt-snowflake#969
• [CT-816] [Bug] Incremental materialization - cast column when changing column type #5445 (comment)

For example, it can be useful in implementations of safe_cast (falling back to cast for types that don't support a try / safe cast, e.g. in snowflake).

dbt-adapters

Beta Give feedback

1. case-insensitive comparisons in unit testing, base unit testing test dbt-adapters#55
   +0
   
2. [CT-1229] [Feature] Cross-database cast macro dbt-adapters#84
   enhancement +1
   
3. Add tests for cross-database cast macro dbt-adapters#173
   +0

dbt-postgres

Beta Give feedback

1. Import relevant pytest(s) for cross-database cast macro dbt-postgres#76
   +0
2. Import relevant pytest(s) for cross-database cast macro dbt-postgres#77
   cla:yes +1

dbt-redshift

Beta Give feedback

1. Import relevant pytest(s) for cross-database cast macro dbt-redshift#804
   +0
2. Import relevant pytest(s) for cross-database cast macro dbt-redshift#805
   cla:yes +1

dbt-snowflake

Beta Give feedback

1. [Snowflake] test types for unit testing dbt-snowflake#896
   cla:yes +1
2. Import relevant pytest(s) for cross-database cast macro dbt-snowflake#1009
   +0
3. Import relevant pytest(s) for cross-database cast macro dbt-snowflake#1010
   cla:yes +1

dbt-bigquery

Beta Give feedback

1. Import relevant pytest(s) for cross-database cast macro dbt-bigquery#1214
   +0
2. Import relevant pytest(s) for cross-database cast macro dbt-bigquery#1215
   cla:yes +1

dbt-spark

Beta Give feedback

1. Import relevant pytest(s) for cross-database cast macro dbt-spark#1028
   +0
2. Import relevant pytest(s) for cross-database cast macro dbt-spark#1029
   cla:yes +1

Documentation

Beta Give feedback

1. Cross-database cast macro docs.getdbt.com#5278
   content dbt-core v1.8 size: small +3
2. Include in migration guide for adapter maintainers