fivetran / dbt_hubspot_source

Data models for Hubspot built using dbt.
https://fivetran.github.io/dbt_hubspot_source/
Apache License 2.0
33 stars 31 forks source link

[Bug] Quotes missing around column names and aliases when hubspot__pass_through_all_columns is enabled #128

Closed aryan-wisdom closed 3 weeks ago

aryan-wisdom commented 2 months ago

Is there an existing issue for this?

Describe the issue

The models/stg_hubspot__company.sql, models/stg_hubspot__contact.sql and models/stg_hubspot__deal.sql files are not compiling because quotes are not present around column names when hubspot__pass_through_all_columns is true.

Relevant error log or model output

....

 as company_annual_revenue 
(     
        -- just pass everything through if extra columns are present, but ensure required columns are present.
        property_hs_all-deprecated-85213efe-a820-40c1-815f-4c19fed96ad7 as hs_all-deprecated-85213efe-a820-40c1-815f-4c19fed96ad7,
    from base
)

Expected behavior

....

as company_annual_revenue (
-- just pass everything through if extra columns are present, but ensure required columns are present. "property_hs_all-deprecated-85213efe-a820-40c1-815f-4c19fed96ad7" as "hs_all-deprecated-85213efe-a820-40c1-815f-4c19fed96ad7", from base )

Possible solution

A for loop around the code:

                fivetran_utils.remove_prefix_from_columns(
                columns=adapter.get_columns_in_relation(ref('stg_hubspot__company_tmp')), 
                prefix='property_', exclude=get_macro_columns(get_company_columns()))

which applies fivetran_utils.quote_column on each column name and alias would help.

dbt Project configurations

vars: hubspot__pass_through_all_columns: true

Package versions

packages:

What database are you using dbt with?

redshift

How are you running this dbt package?

dbt Core™

dbt Version

1.7.7

Additional Context

No response

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

fivetran-avinash commented 1 month ago

Hi @aryan-wisdom , thanks for reporting this issue to us. We were able to reproduce an error locally that when columns were unquoted, the model would not run.

We reconfigured our logic to utilize a new macro that handles quoting columns, and were able to resolve this error so that columns were quoted and the model did compile.

Screenshot 2024-09-11 at 8 14 09 AM

However, we did have to rename the columns so that dashes were now underscores (because that was the source of the problem). Is this acceptable for your data? Just want to check before we proceed forward with this solution.

aryan-wisdom commented 1 month ago

Thanks for taking a look @fivetran-avinash ! That sounds good to me.

aryan-wisdom commented 1 month ago

Any update on this @fivetran-avinash ?

fivetran-avinash commented 1 month ago

Apologies for the delay in responding @aryan-wisdom !

Yes, if you are okay with this approach, we will move this task to accepted and aim to address it in one of the coming sprints.

aryan-wisdom commented 1 month ago

Thanks @fivetran-avinash

fivetran-joemarkiewicz commented 1 month ago

Hi @aryan-wisdom I just wanted to reach out and provide an update that we are planning to address this in the last sprint in the month of October. You can expect us to roll out an update to the HubSpot dbt package then which will address this issue. Thanks for your patience!

fivetran-reneeli commented 3 weeks ago

Hi @aryan-wisdom ! We have addressed this in our most recent version of the hubspot package, v0.19.0! Feel free to upgrade! As such I'll close this out; Let us know if you have any other questions!

aryan-wisdom commented 3 weeks ago

Thank you @fivetran-reneeli !