search

dbt-labs / dbt-external-tables

dbt macros to stage external sources
https://hub.getdbt.com/dbt-labs/dbt_external_tables/latest/
Apache License 2.0
297 stars 119 forks source link

Blank double quoted fields in CSVs treated as varchar instead of null in Snowflake External Tables #132

Closed nic-sharesies closed 2 years ago

nic-sharesies commented 2 years ago

Describe the bug

After we upgraded to dbt v1.0.0, we started having errors selecting from Snowflake External Tables. This was due to the CSV files in S3 containing blank double quoted values, e.g. "". Snowflake was storing these as text as opposed to null, leading to cast type errors.

Steps to reproduce

1) Have a CSV file in S3 that contains blank double quoted fields using a non char data type, i.e. timestamp 2) Create a Snowflake External Table using that CSV as the source 3) Select from that table in Snowflake

Expected results

Blank fields cast to data type as expected and Snowflake query returns result set.

Actual results

Snowflake fails to cast to data type and throws an error.

Screenshots and log output

System information

The contents of your packages.yml file:

packages:
  - package: dbt-labs/dbt_external_tables
    version: 0.8.0

  - package: calogica/dbt_expectations
    version: 0.5.1

Which database are you using dbt with?

The output of dbt --version:

installed version: 1.0.0
   latest version: 1.0.0

Up to date!

Plugins:
  - snowflake: 1.0.0
  - postgres: 1.0.0

The operating system you're using: MacOS Monterey 12.1 The output of python --version: 3.9.9

Additional context

PR created with fix https://github.com/dbt-labs/dbt-external-tables/pull/131

jtcohen6 commented 2 years ago

Thanks @nic-sharesies, for the detailed issue and the PR with the fix! Thanks also to @jeremyyeo for bumping this one to my attention, so that we could make the connection to #118.

Rather than an increasingly complex case when statement defined within the macro, I'm going to close this one, in favor of consolidating with the change requested + proposed in #118.