[Bug] Bigquery fails on stg_qualtrics__distribution_contact

davejroth commented 9 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Describe the issue

dbt throws an error when building stg_qualtrics__distribution_contact. I believe the issue is that the model contains a column named statuswhich is a reserved keyword in bigquery. I was able to successfully build the model by replacing line 38 with status as response_status.

Relevant error log or model output

17:33:01  1 of 1 START sql table model prod_qualtrics_source.stg_qualtrics__distribution_contact  [RUN]
17:33:06  Unhandled error while executing target/run/qualtrics_source/models/stg_qualtrics__distribution_contact.sql
400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/alder-aim/datasets/prod_qualtrics_source/tables/stg_qualtrics__distribution_contact?prettyPrint=false: The description for field status is too long. The maximum length is 1024 characters.
17:33:06  1 of 1 ERROR creating sql table model prod_qualtrics_source.stg_qualtrics__distribution_contact  [ERROR in 5.23s]
17:33:06  
17:33:06  Finished running 1 table model in 0 hours 0 minutes and 13.39 seconds (13.39s).
17:33:06  
17:33:06  Completed with 1 error and 0 warnings:
17:33:06  
17:33:06    400 PATCH https://bigquery.googleapis.com/bigquery/v2/projects/alder-aim/datasets/prod_qualtrics_source/tables/stg_qualtrics__distribution_contact?prettyPrint=false: The description for field status is too long. The maximum length is 1024 characters.

Expected behavior

the model should materialize without an error

dbt Project configurations

I tried aliasing the column using the instructions provided in the git repo but it is not working:

models:
  +persist_docs:
    relation: true
    columns: true
  warehouse:
    +materialized: table
    staging:
      +schema: staging
vars:
    qualtrics__distribution_contact_pass_through_columns:
    - name: "status"
      alias: "response_status"

Package versions

packages:

package: dbt-labs/dbt_utils version: 1.1.1
package: dbt-labs/dbt_external_tables version: 0.8.5
package: fivetran/qualtrics version: [">=0.1.0", "<0.2.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt=1.7.2

Additional Context

I think the resolution is to rename the column status -> response_status

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

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

fivetran-jamie commented 9 months ago

Hey there! So looking at the error message, i think this is related to the length of this doc description and the fact that you're persisting the docs. According to these BigQuery docs status should be okay to use as a field name, but BQ doesn't like the length of the column documentation

I think the most appropriate fix here would be to shorten the field's description to just be the link to the distribution status's Qualtrics API docs instead of listing them all out.

we'd be happy to prioritize reviewing a PR if you're open to creating one, otherwise i'd advise setting persist_docs to false for the time being. I think you could potentially just turn off persist_docs for this specific model by scoping the configuration to something like

models:
  +persist_docs:
    relation: true
    columns: true
  qualtrics_source:
    stg_qualtrics__distribution_contact:
      +persist_docs:
        relation: true
        columns: false

fivetran-catfritz commented 8 months ago

Hi @davejroth I was able to reproduce the error on my end, and was also able to resolve it by making the changes @fivetran-jamie suggested previously. It would be helpful if you could verify this works for your case, so I have created a test branch that should enable you to run the package normally, without having to add passthrough vars or disabling persist_docs.

To install it, use the below snipped in your packages.yml in place of the current qualtrics lines.

packages:
  - git: https://github.com/fivetran/dbt_qualtrics.git
    revision: bug/persist-docs
    warn-unpinned: false

If you are able to test it out, please let me know how it works. Thanks!

davejroth commented 8 months ago

Thank you! I'm out of the office until Monday but will test next week and let you know.

On Tue, Jan 2, 2024 at 2:49 PM fivetran-catfritz @.***> wrote:

Hi @davejroth https://github.com/davejroth I was able to reproduce the error on my end, and was also able to resolve it by making the changes @fivetran-jamie https://github.com/fivetran-jamie suggested previously. It would be helpful if you could verify this works for your case, so I have created a test branch that should enable you to run the package normally, without having to add passthrough vars or disabling persist_docs.

To install it, use the below snipped in your packages.yml in place of the current qualtrics lines.

packages:

git: https://github.com/fivetran/dbt_qualtrics.git revision: bug/persist-docs warn-unpinned: false

If you are able to test it out, please let me know how it works. Thanks!

— Reply to this email directly, view it on GitHub https://github.com/fivetran/dbt_qualtrics_source/issues/3#issuecomment-1874656955, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABSP5I2DUPTBANMAJA3MGBDYMSFHJAVCNFSM6AAAAABBADKEDKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQNZUGY2TMOJVGU . You are receiving this because you were mentioned.Message ID: @.***>

fivetran-catfritz commented 8 months ago

Hi @davejroth thanks! I actually am going to go ahead and release this since I will be out of the office next week, but if it is not working for you after I release it, please let us know in this issue!

fivetran / dbt_qualtrics_source