sp_rename in dbt 1.9 breaks lineage tracking for SQL Server views (should drop & recreate instead)

[bssulfikkar]

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