Closed jtcohen6 closed 4 months ago
@prdpsvs would you be able to take a look at this?
so I found out why ref
was overridden and why within create_materialized_view_as.sql
of all places.
The Create Materialized View as Select (CMVAS) statement in Azure Synapse does not support three-part names in either the name of the materialized view or any table or view mentioned within the SELECT
statement.
Materialized Views in Azure Synapse cannot re I get the following error when executing TestMaterializedViewsBasicSynapse
:
Names must be in two-part format
Database Error in model my_mv (models/my_mv.sql)
('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
Cannot schema bind view 'test.my_mv' because name 'dbtmsft.azsyn.cipool.test.my_seed' is invalid for schema binding.
Names must be in two-part format and an object cannot reference itself.
(4512) (SQLExecDirectW)")
another update -- this isn't just a limitation of materialized view. it's all of Azure Synapse.
Accordingly, there are in this adapter
relation.include(database=False)
and[{{relation.schema}}].[{{relation.identifier}}]
Once the Relation class is properly implemented, all 14 of those can simply be relation
. More to come tomorrow
Hi! I'm one of the folks who helps maintain
dbt-core
.We noticed while debugging some failures in our automated tests that
dbt-synapse
overrides theref
macro here:https://github.com/microsoft/dbt-synapse/blob/8f4a6a3ab82ea20652bc162b0acbaee461702445/dbt/include/synapse/macros/materializations/models/materialized_view/create_materialized_view_as.sql#L1-L5
This isn't the right way of achieving the desired behavior (rendering relations without the
database
identifier), and it precludes users from being able to specify two-argumentref
.Instead, if the goal is to render relations without a three-level hierarchy —
schema.identifier
instead ofdatabase.schema.identifier
—dbt-synapse
should do whatdbt-spark
does. Something like: