Summary
After upgrading from dbt 1.7 → 1.9, we observed a change in how views are handled during materialization in dbt-sqlserver.
Previously (1.7), dbt would fully drop & recreate views after creating dbt_tmp views.
In 1.9, dbt uses sp_rename to swap names instead of recreating views.
This causes a problem for lineage because:
sp_rename only renames metadata in system tables (e.g. sys.comments, sys.sql_modules)
It does not update the definition stored in sys.sql_modules or OBJECT_DEFINITION()
As a result, the view definition still references the dbt_tmp object rather than the final model name.
Steps to Reproduce
Use dbt-sqlserver adapter with dbt 1.9
Materialize a model as a view
Inspect sys.sql_modules.definition or OBJECT_DEFINITION(OBJECT_ID('final_view_name'))
Notice that the definition still contains the reference to the dbt_tmp view
Expected Behavior
Final views should be fully recreated so that sys.sql_modules.definition reflects the correct (final) SQL definition
This behavior existed in dbt 1.7 and allowed lineage tools to correctly read view definitions
Actual Behavior
sp_rename is used instead of drop/recreate
View definition is stale and continues to reference dbt_tmp views
Lineage breaks
Proposed Solution
Replace sp_rename with drop + create for finalizing views in SQL Server.
This aligns with Microsoft’s own recommendation:
Renaming a stored procedure, function, view, or trigger using sp_rename does not update the name in the definition column of sys.sql_modules or the result of OBJECT_DEFINITION(). Therefore, it’s recommended to drop and recreate the object with the new name instead of using sp_rename.
— Microsoft Docs
Environment
dbt version: 1.9.x
Adapter: dbt-sqlserver
Database: SQL Server (please specify version if possible, e.g. 2019 / 2022
Summary
After upgrading from dbt 1.7 → 1.9, we observed a change in how views are handled during materialization in dbt-sqlserver.
Previously (1.7), dbt would fully drop & recreate views after creating dbt_tmp views.
In 1.9, dbt uses sp_rename to swap names instead of recreating views.
This causes a problem for lineage because:
sp_rename only renames metadata in system tables (e.g. sys.comments, sys.sql_modules)
It does not update the definition stored in sys.sql_modules or OBJECT_DEFINITION()
As a result, the view definition still references the dbt_tmp object rather than the final model name.
Steps to Reproduce
Use dbt-sqlserver adapter with dbt 1.9
Materialize a model as a view
Inspect sys.sql_modules.definition or OBJECT_DEFINITION(OBJECT_ID('final_view_name'))
Notice that the definition still contains the reference to the dbt_tmp view
Expected Behavior
Final views should be fully recreated so that sys.sql_modules.definition reflects the correct (final) SQL definition
This behavior existed in dbt 1.7 and allowed lineage tools to correctly read view definitions
Actual Behavior
sp_rename is used instead of drop/recreate
View definition is stale and continues to reference dbt_tmp views
Lineage breaks
Proposed Solution
Replace sp_rename with drop + create for finalizing views in SQL Server.
This aligns with Microsoft’s own recommendation:
Renaming a stored procedure, function, view, or trigger using sp_rename does not update the name in the definition column of sys.sql_modules or the result of OBJECT_DEFINITION(). Therefore, it’s recommended to drop and recreate the object with the new name instead of using sp_rename.
— Microsoft Docs
Environment
dbt version: 1.9.x
Adapter: dbt-sqlserver
Database: SQL Server (please specify version if possible, e.g. 2019 / 2022