Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

'tmp_relation' is undefined when altering a column's datatype on an incremental model #248

Open
baldwicc opened this issue May 24, 2024 · 1 comment

Comments

@baldwicc
Copy link
Contributor

We're seeing error messages in v1.7.3 when the datatype of a column is altered (but the column name remains the same).

04:07:16  Running with dbt=1.7.15
04:07:16  Registered adapter: synapse=1.7.3
12:34:56    Compilation Error in model example_model_name (models/xxx/example_model_name.sql)
  'tmp_relation' is undefined
  
  > in macro alter_column_type (macros/adapters/columns.sql)
  > called by macro materialization_incremental_default (macros/materializations/models/incremental/incremental.sql)
  > called by model example_model_name (models/xxx/example_model_name.sql)

Root cause appears to be the upstream fabric adaptor not supporting ALTER TABLE, but also having it's own bug in this macro where tmp_relation is indeed never defined:

dbt-msft/dbt-sqlserver:
https://github.com/dbt-msft/dbt-sqlserver/blob/f789ab0815b926bd68af6e901cd0e33b2895db3f/dbt/include/sqlserver/macros/adapters/columns.sql#L1-L18

microsoft/dbt-fabric:
https://github.com/microsoft/dbt-fabric/blob/45dacbc87e1b45602dd46218c75f3aaa441dbb95/dbt/include/fabric/macros/adapters/columns.sql#L55-L117

@baldwicc
Copy link
Contributor Author

baldwicc commented May 24, 2024

In our local projects we've just overridden this by lifting the alter_column_type macro from dbt-msft/dbt-sqlserver as we update to v1.7.x, as Synapse does support ALTER TABLE and sp_rename for columns:

{# baldwicc 2024-05-24 - fabric adapter doesn't support ALTER TABLE statements, but synapse does... and so did sqlserver... #}

{% macro alter_column_type(relation, column_name, new_column_type) %}

  {%- set tmp_column = column_name + "__dbt_alter" -%}

  {% call statement('alter_column_type') -%}
    alter {{ relation.type }} {{ relation }} add "{{ tmp_column }}" {{ new_column_type }};
  {%- endcall -%}
  {% call statement('alter_column_type') -%}
    update {{ relation }} set "{{ tmp_column }}" = "{{ column_name }}";
  {%- endcall -%}
  {% call statement('alter_column_type') -%}
    alter {{ relation.type }} {{ relation }} drop column "{{ column_name }}";
  {%- endcall -%}
  {% call statement('alter_column_type') -%}
    exec sp_rename @objname = '{{ relation(database=False) | replace('"', '') }}.{{ tmp_column }}', @newname = '{{ column_name }}', @objtype = 'column'
  {%- endcall -%}
  
{% endmacro %}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant