fabric__query_test_result_rows creates test-sample view in wrong database context (package database ignored)

[tderk]

Summary

fabric__query_test_result_rows (in macros/edr/materializations/test/test.sql) creates the temporary test-sample view in the wrong database context. It resolves the elementary package schema but silently discards the package database, so the CREATE VIEW / SELECT / DROP VIEW all run against whatever database the active connection happens to point at (target.database) instead of the elementary package database.

Affected code

{% set _edr_db, _edr_schema = elementary.get_package_database_and_schema() %}
{% set view_schema = _edr_schema if _edr_schema else target.schema %}
{% set full_view_name = view_schema ~ "." ~ view_name %}

{# Create view from the compiled test SQL #}
{% do run_query("create view " ~ full_view_name ~ " as " ~ sql) %}

get_package_database_and_schema() returns (_edr_db, _edr_schema), but _edr_db is never used. The view name is built as a two-part schema.view identifier, so the DDL executes in the connection's current database — target.database.

Impact

When the elementary package is configured to live in a different database than target.database (a common setup in Fabric / multi-database deployments, where elementary artifacts get a dedicated database), the sample view is created in the wrong database. Depending on the setup this results in either:

• a failure, because the elementary schema does not exist in target.database, or
• the sample view (and therefore the persisted test result rows) ending up in the wrong database, mismatched from where elementary reads its artifacts.

The inline comment states the intent is to use the elementary package location "guaranteed to exist", but only the schema part of that location is actually applied.

Why a simple three-part name does not fix it

T-SQL does not allow a three-part name in CREATE VIEW (create view db.schema.view as ... is rejected — CREATE VIEW must be the only statement in the batch and cannot be cross-database qualified). So prepending _edr_db to the identifier is not a valid fix; the DDL must instead be executed inside the package database context.

Suggested fix

Execute the CREATE/DROP VIEW inside the package database context via sp_executesql, and qualify the SELECT with the full three-part name:

{% set _edr_db, _edr_schema = elementary.get_package_database_and_schema() %}
{% set view_db = _edr_db if _edr_db else target.database %}
{% set view_schema = _edr_schema if _edr_schema else target.schema %}
{% set short_view_name = "[" ~ view_schema ~ "].[" ~ view_name ~ "]" %}
{% set qualified_view_name = "[" ~ view_db ~ "].[" ~ view_schema ~ "].[" ~ view_name ~ "]" %}

{% set create_inner = "create view " ~ short_view_name ~ " as " ~ sql %}
{% set create_stmt = "EXEC [" ~ view_db ~ "]..sp_executesql N'" ~ create_inner | replace("'", "''") ~ "'" %}
{% do run_query(create_stmt) %}

{% set query %}
    select {% if sample_limit is not none %} top {{ sample_limit }} {% endif %} *
    from {{ qualified_view_name }}
{% endset %}
{% set result = elementary.agate_to_dicts(elementary.run_query(query)) %}

{% set drop_inner = "drop view if exists " ~ short_view_name %}
{% set drop_stmt = "EXEC [" ~ view_db ~ "]..sp_executesql N'" ~ drop_inner | replace("'", "''") ~ "'" %}
{% do run_query(drop_stmt) %}

Environment

• adapter: dbt-fabric (Microsoft Fabric / SQL Server T-SQL)
• macro: fabric__query_test_result_rows

[tderk]

I have opened #1009 as a draft suggestion that addresses this — it is just a proposal and may help as a starting point, feel free to take or adapt whatever is useful.