feat(unpivot): add quote identifier parameter to handle case sensitive columns

[error418]

resolves #216

This is a:

• [ ] documentation update
• [ ] bug fix with no breaking changes
• [x] new functionality
• [ ] a breaking change

All pull requests from community contributors should target the main branch (default).

Description & motivation

Added quote_identifiers parameter to unpivot to handle case-sensitive column names. This fixes #216

Checklist

• [x] This code is associated with an Issue which has been triaged and accepted for development.
• [x] I have verified that these changes work locally on the following warehouses (Note: it's okay if you do not have access to all warehouses, this helps us understand what has been covered)
  • [x] BigQuery (tested by @Ishankoradia - see PR comments)
  • [x] Postgres
  • [ ] Redshift
  • [ ] Snowflake
• [x] I followed guidelines to ensure that my changes will work on "non-core" adapters by:
  • [x] dispatching any new macro(s) so non-core adapters can also use them (e.g. the star() source)
  • [x] using the limit_zero() macro in place of the literal string: limit 0
  • [x] using dbt.type_* macros instead of explicit datatypes (e.g. dbt.type_timestamp() instead of TIMESTAMP
• [x] I have updated the README.md (if applicable)
• [x] I have added tests & descriptions to my models (and macros if applicable)
• [x] I have added an entry to CHANGELOG.md

[error418]

rebased to current main and resolved merge conflicts in CHANGELOG.md

[error418]

Anything missing to get this merged?

[github-actions[bot]]

This PR has been marked as Stale because it has been open with no activity as of late. If you would like the PR to remain open, please comment on the PR or else it will be closed in 7 days.

[error418]

ping

[Shadowsong27]

Any upates on this? I have encountered a similar scenario where i use date yyyy-mm-dd as the format, it end up evaluating the date string into an arithmic operation. Quoting seems quite useful here.

[Ishankoradia]

Hi @joellabes and @error418 what are we waiting on for this to be merged ? I have faced a similar issue

[Ishankoradia]

@error418 and @joellabes i have tested the changes for bigquery warehouse also and its work fine.

[Abhishek-N]

hey @error418 @joellabes, facing a similar issue and need to have this merged. can you merge this asap please?

[error418]

@Shadowsong27 @Ishankoradia @Abhishek-N at the moment I am waiting for a review by @joellabes (or any maintainer of dbt-utils).

@Ishankoradia thank you for testing the changes for BigQuery

Should there be any remaining issues with this PR I am happy to provide the necessary fixes.

[pnd-mnicolau]

bump

[alejobs]

What's missing here? I need this fix to be deployed. Thanks.

[MiguelMadero]

What's missing here? I need this fix to be deployed. Thanks.

[error418]

@joellabes could you please provide some information about the status of this pull request?

This is stalling for a while now without any feedback from the maintainers, which sadly becomes a bit frustrating.

Thanks

[dbeatty10]

Hi all,

This is our highest priority PR to review right now, and I'll be diving deeper into it this week.

After taking an initial look, the heart of the implementation looks good.

The main thing I'd like to follow-up on prior to approving + merging is the testing:

• It looks like there is special configuration in the CI tests so that Snowflake and Redshift pass

So I'd like to:

• Make sure that the test is running for Redshift.
• Align how Snowflake is handled across similar tests. i.e., either standardize on prior art or standardize on the adapter.quote() approach in this PR

[dbeatty10]

@error418 I think I figured out the root cause of the Redshift test failures in CI.

As you noted, the default behavior in Redshift is to lowercase all column names even if a table is created with quoted mixed-case column names.

It looks like the solution is to set enable_case_sensitive_identifier to true. I'm going to try adding that configuration to the CI cluster or workgroup's parameter group and see if that allows this test to pass for Redshift.