Use of column alias in snowflake external table seems wrong

[DennisWagner]

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

1. Define external table in sources.yml
2. Set alias for one column
3. 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
• 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]

@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]

@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]

@DennisWagner I believe I found the issue and have put in a PR to fix it: #318

@cakkinep FYI

[DennisWagner]

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