[FEATURE] Derived column does not replace original

[gjvanvuuren]

Describe the bug

I followed the documentation on Derived Columns to try and replace a source column AnzahlLagerplätze with an alias AnzahlLagerplaetze:

{{ config(materialized='table') }}

{%- set yaml_metadata -%}
source_model: test_seed
ldts: 'current_timestamp()'
rsrc: '!test'

include_source_columns: true

derived_columns:
  AnzahlLagerplaetze:    <----+
    value: AnzahlLagerplätze
    datatype: int

enable_ghost_records: True
{%- endset -%}

{{ datavault4dbt.stage(yaml_metadata=yaml_metadata) }}

The result is that a new aliased column is added, instead of replacing the original:

derived_columns AS (SELECT
  
        `load_date_ts`,
        `record_source`,
        `anzahllagerplätze`,
        `anothercol`,
  
  `anzahllagerplätze` AS `anzahllagerplaetze`    <----+

  FROM ldts_rsrc_data
)

Environment

• dbt version: dbt-fusion 2.0.0-preview.176
• datavault4dbt version: 1.17.0
• Database/Platform: Databricks

To Reproduce

• Create a seed file test_seed.csv:

AnzahlLagerplätze,AnotherCol
1,"abc"

• Create a staging model test_stage_rename_col.sql:

{{ config(materialized='table') }}

{%- set yaml_metadata -%}
source_model: test_seed
ldts: 'current_timestamp()'
rsrc: '!test'

include_source_columns: true

derived_columns:
  AnzahlLagerplaetze:
    value: AnzahlLagerplätze
    datatype: int
    src_cols_required: AnzahlLagerplaetze

enable_ghost_records: True
{%- endset -%}

{{ datavault4dbt.stage(yaml_metadata=yaml_metadata) }}

• The resulting compiled SQL:

WITH
source_data AS (
    SELECT
    
        `anzahllagerplätze`,
        `anothercol`

  FROM `01_rowavault_dev`.`raw`.`test_seed`
  ),

ldts_rsrc_data AS (

  SELECT
    CAST( current_timestamp() as timestamp ) AS load_date_ts,
    CAST( 'test' as string ) AS record_source,
      
        `anzahllagerplätze`,
        `anothercol`,
        `anzahllagerplaetze`
    
  FROM source_data),

derived_columns AS (

SELECT

        `load_date_ts`,
        `record_source`,
        `anzahllagerplätze`,
        `anothercol`,
  
  `anzahllagerplätze` AS `anzahllagerplaetze`

  FROM ldts_rsrc_data
),

unknown_values AS (

    SELECT

    TO_TIMESTAMP('1899-01-01 00:00:01', 'yyyy-MM-dd HH:mm:ss') as load_date_ts
    ,'system' as record_source
        , CAST('-1' as BIGINT) as AnzahlLagerplätze
        , '(unknown)' as AnotherCol
      
        , CAST('-1' as INT) as AnzahlLagerplaetze
),

error_values AS (

    SELECT

    TO_TIMESTAMP('2999-12-31 23:59:59', 'yyyy-MM-dd HH:mm:ss') as load_date_ts
    ,'error' as record_source
        , CAST('-2' as BIGINT) as AnzahlLagerplätze
        , '(error)' as AnotherCol
    
        , CAST('-2' as INT) as AnzahlLagerplaetze
),

ghost_records AS (
    SELECT * FROM unknown_values
    UNION ALL
    SELECT * FROM error_values
),

columns_to_select AS (

    SELECT
   
        `load_date_ts`,
        `record_source`,
        `anzahllagerplätze`,
        `anothercol`,
        `anzahllagerplaetze`

    FROM derived_columns

    UNION ALL
    
    SELECT

    
        `load_date_ts`,
        `record_source`,
        `anzahllagerplätze`,
        `anothercol`,
        `anzahllagerplaetze`

    FROM ghost_records
)

SELECT * FROM columns_to_select

Expected behavior

Since Databricks doesn't allow German umlauts in column names, I have to rename and replace the original colum(s). Including the original column does not solve the issue. It has to be replaced in all subsequent CTEs with with the aliased version.

[tkirschke]

Hi @gjvanvuuren and thanks for reaching out!

The described behavior for overwriting a column only covers the case where colA should be replaced with a different input, but the column name remains colA.

I definetely see the value of your desired behavior, but it will require us to add a new parameter to each derived column, something as overwrite_src_cols: true / false. I will discuss this with the team but push towards implementing it. In any case, this might take some time and I will get back to you here once this feature is live.

In the meantime, you could try a workaround by changing the parameter include_source_columns to false, which might partly achieve what you intend, but has other implications. Let me know if this works!

[gjvanvuuren]

Hi Tim,

thanks for looking into this. Due to the quick fix you provided in the related issue #460, I now have an alternative I can work with while this feature is being implemented.

Cheers
Gideon