[Bug] Update comment_markdown fields with coalesces so that Snowflake concatenation returns not null elements

[fivetran-avinash]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Snowflake does not support concatenation on null elements. If any fields are being concatenated that are null, the resulting output of the concatenation is null. We will need to perform coalesce on all fields being concatenated so that it always returns a not null value.

Relevant error log or model output

The relevant code being impacted lies in our intermediate models, where the dbt.concat function is applied, say int_rag_zendesk__ticket_comment_document.

       cast(
            {{ dbt.concat([
                "'### message from '", "commenter_name", "' ('", "commenter_email", "')\\n'",
                "'##### sent @ '", "comment_time", "'\\n'",
                "comment_body"
            ]) }} as {{ dbt.type_string() }})
            as comment_markdown

If any one of these fields is null, say commenter_email, or commenter_name, then the output of the comment_markdown will also be null in Snowflake, regardless of any other not-null elements present.

Expected behavior

When concatenated, the output of comment_markdown is any concatenation with not null elements are populated.

Possible solution

We apply our coalesce_cast macro to all fields that are concatenated and create a not-null standardized value to be safe.

dbt Project configurations

Standard

Package versions

packages:

• package: fivetran/fivetran_utils
  version: [">=0.4.0", "<0.5.0"]

What database are you using dbt with?

snowflake

How are you running this dbt package?

dbt Core™

dbt Version

1.7.4

Additional Context

No response

Are you willing to open a PR to help address this issue?

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