Use of column alias in snowflake external table seems wrong

DennisWagner commented 2 months ago

Describe the bug

When using the alias for a column in a Snowflake external table, the alias used both as a column name as well as within the column expression

Steps to reproduce

Define external table in sources.yml
Set alias for one column
dbt run-operation stage_external_sources --vars "ext_full_refresh: true"

sources.yml:

version: 2

sources:

name: datalake schema: datalake

tables:
- name: external_table_test external: location: '@datalake.batch_load_stage/
  ' file_format: datalake.parquet_format auto_refresh: true integration:
  columns:
  - name: FILECREATETIME alias: created_at_utc data_type: TIMESTAMP_NTZ

Expected results

I expected the alias to be used in either the expression, or the column name - not both places.

create or replace external table .datalake.external_table_test( created_at_utc TIMESTAMP_NTZ as ((case when is_null_value(value:FILECREATETIME) or lower(value:FILECREATETIME) = 'null' then null else value:FILECREATETIMEend)::TIMESTAMP_NTZ) ) location = @datalake.batch_load_stage/ auto_refresh = True

Actual results

The alias is used in the expression and as the column name in the table resulting in the column always returning null values.

Screenshots and log output

Output from log: create or replace external table .datalake.external_table_test( created_at_utc TIMESTAMP_NTZ as ((case when is_null_value(value:created_at_utc) or lower(value:created_at_utc) = 'null' then null else value:created_at_utc end)::TIMESTAMP_NTZ) ) location = @datalake.batch_load_stage/ auto_refresh = True

System information

The contents of your packages.yml file: packages:

package: dbt-labs/dbt_utils version: 1.1.1
package: dbt-labs/dbt_external_tables version: 0.9.0
package: dbt-labs/codegen version: 0.11.0

Which database are you using dbt with?

[ ] redshift
[x] snowflake
[ ] other (specify: ____)

The output of dbt --version: 2024.8.235 (from dbt cloud using versionless)

The operating system you're using: dbt cloud

The output of python --version: N/A

Additional context

kyleburke-meq commented 2 months ago

@DennisWagner Are you using an expression and an alias? Cause if you're defining an expression you wouldn't want to use an alias.

DennisWagner commented 2 months ago

@kyleburke-meq No, I only used name and alias, but when that did not work as expected, I used name and expression. Unfortunately, that makes me have to replicate the logic in the package for null handling, which I would like to avoid for simply renaming a column.

kyleburke-meq commented 2 months ago

@DennisWagner I believe I found the issue and have put in a PR to fix it: https://github.com/dbt-labs/dbt-external-tables/pull/318

@cakkinep FYI

DennisWagner commented 2 months ago

Wauv, that was fast. Thanks a lot for your help.

dbt-labs / dbt-external-tables