[Unit Testing] Implement unit_testing_column_schema_strategy macro and use it unit materialization

[MichelleArk]

Housekeeping

• I am a maintainer of dbt-adapters

Short description

In order to support unit testing complex types in adapters that can't rely on adapter.get_schema_from_relation for column types that are usable in safe_cast, provide an alternative column schema strategy (adapter.get_columns_in_relation) to what is currently in place by default: https://github.com/dbt-labs/dbt-adapters/blob/main/dbt/include/global_project/macros/materializations/tests/unit.sql#L11

This should be selectable based on the specific adapter as some strategies work very well for some adapters, but not for others, and vice versa. The ability to choose a strategy here is key for adapter maintainers!

Context

dbt-labs/dbt-core#8423 (comment)

Acceptance criteria

Tasks

Preview Give feedback

1. adapters can overwrite the unit_testing_column_schema_strategy to be one of 'empty_query' or 'relation'
2. update unit materialization to handle:
3. If unit_testing_column_schema_strategy is relation -> use existing column schema strategy: https://github.com/dbt-labs/dbt-adapters/blob/main/dbt/include/global_project/macros/materializations/tests/unit.sql#L11
4. If unit_testing_column_schema_strategy is empty_query -> use adapter.get_column_schema_from_query instead

Suggested tests

Tasks

Preview Give feedback

1. Existing base functional tests should be sufficient as this is behaviour can only be triggered by certain adapters that override unit_testing_column_schema_strategy
2. dbt-postgres & dbt-redshift test overrides

Are there any security concerns with these changes?

nope

Consequences

This will enable adapter implementations to have more flexibility in the column schema strategy that the unit materialization works, allowing more seamless extension for complex types depending on what's best-suited for the adapter.

[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.