[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

Preview 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

Preview 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

Preview 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

Preview 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

Preview 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

Preview 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

Preview 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

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.