[Bug] Union Failure, Different Datatypes

[moodybox]

Is there an existing issue for this?

• [X] I have searched the existing issues

Describe the issue

In the union for ap_ar_enhanced, there are incompatible types. Department ID seems to fail here. If you cast the department ID to a string, then the model runs without issue.

Relevant error log or model output

"Writing runtime sql for node "model.sage_intacct.sage_intacct__ap_ar_enhanced"
BigQuery adapter: https://console.cloud.google.com/bigquery?project=...
BigQuery adapter: Retry attempt 1 of 1 after error: BadRequest('Column 13 in UNION ALL has incompatible types: STRING, INT64 at [143:5]')"

Expected behavior

Here is one of the offending lines:

ap_bill_item.department_id,

And here is the fix:

cast(ap_bill_item.department_id as string) as department_id,

The other offending line also requires a similar fix:

ar_invoice_item.department_id, ->
cast(ar_invoice_item.department_id as string) as department_id,

dbt Project configurations

I have the default setting of true for sage_intacctusing_invoices, sage_intacctusing_bills.

Package versions

From packages.yml:

• package: fivetran/sage_intacct version: 0.2.2

What database are you using dbt with?

bigquery

dbt Version

1.6, for some reason dbt --version in the IDE isn't working.

Additional Context

No response

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

• [X] Yes.
• [ ] Yes, but I will need assistance and will schedule time during our office hours for guidance
• [ ] No.

[fivetran-reneeli]

Hi @moodybox, thanks for raising this to our attention! Just curious if this is your first time using the package and you've run into this from the beginning, or has this package always worked fine for you up til now?

The fix is relatively straightforward; we'll do an explicit data type cast in the upstream staging models in order for the downstream models to run successfully.

We will fold this into our sprints. This issue will contain updates as we progress!

[moodybox]

This is my first time using the package.

I think that fix will definitely work. I'll look forward to that fix coming down the pipeline.

[fivetran-jamie]

this is in v0.2.1 of the source package! so the transform package will automatically pick it up