[Feature] Further macro optimizations and potential refactoring of dbt.concat and coalesce_cast

[fivetran-avinash]

Is there an existing feature request for this?

• I have searched the existing issues

Describe the Feature

Several issues

1. dbt.concat does not handle the Snowflake null element case very well (more details here and could be rebuilt with our own macro to do so.

2. Currently the coalesce_cast macro is applied at the intermediate layer, which is non-standard--usually casting and coalescing would happen at the staging layer to reduce macro operations. This can lead to some of the base tables being coalesce_cast'ed at multiple intermediate models (like user_name and created_by in Zendesk) when it could just be cast in the staging layer once.

3. We do bring in a '1970-01-01 00:00:00.00' timestamp value for non-null cases in our coalesce_cast for timestamp values like created_time, which could lead to some confusion in the data if brought in. It'd be useful to figure a way to bring this value in as a similar UNKNOWN without specific

How would you implement this feature?

Early thoughts:

1. we can create a custom dbt.concat macro that handles the snowflake null case we solved for.
2. We can explore bringing the macro upstream and apply it at the staging layer and see if it causes any issues.
3. Casting the timestamp value to string within coalesce_cast and bringing the original timestamp value downstream for any additional operations it's needed for.

Describe alternatives you've considered

No response

Are you interested in contributing this feature?

• Yes.
• Yes, but I will need assistance.
• No.

Anything else?

No response