incremental models don't work cross-database

[dataders]

@jckardos flagged to @prdpsvs and I of this bug last week.

reproduction

1. create an incremental model with the following config, where other_db is already created and different from that specified in the target profile

   -- fct_invoice.sql
   {{
     config(
       database = 'other_db'
       materialized = 'incremental',
       unique_key = 'id',
       )
   }}

2. build the model (dbt run -s my_incremental) the initial table will be created
3. run again so that incremental build any only new runs may be inserted
4. failure with below message

('42S01', "[42S01] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]
There is already an object named 'fct_invoice' in the database. (2714) (SQLMoreResults)")
13:13:17 1 of 1 ERROR creating sql incremental model sales.dbt_dpurdy.fct_invoice ....... [ERROR in 1.28s]

relevant log snippet

```sql USE [sales]; EXEC('create view "dbt_dpurdy"."fct_invoice_temp_view" as select il.invoice_lines_hk as fct_invoice_hk, il.invoice_line_id, i.invoice_id, i.customer_fk, i.bill_to_customer_fk, i.order_fk, i.delivery_method_fk, i.contact_person_fk, i.accounts_person_fk, i.salesperson_person_fk, i.packed_by_person_fk, il.stock_item_fk, il.package_type_fk, i.invoice_date, i.customer_purchase_order_number, i.is_credit_note, i.credit_note_reason, i.comments, i.delivery_instructions, i.internal_comments, i.delivery_run, i.run_position, i.returned_delivery_data, i.confirmed_delivery_time, i.confirmed_received_by, il.description, i.total_dry_items, i.total_chiller_items, il.quantity, il.unit_price, il.tax_rate, il.tax_amount, il.line_profit, il.extended_price, i.last_edited_by as invoice_last_edited_by, i.last_edited_when as invoice_last_edited_when, il.last_edited_by as invoice_line_last_edited_by, il.last_edited_when as invoice_line_last_edited_when from "vault"."dbt_dpurdy_dbo"."invoice_lines" il join "vault"."dbt_dpurdy_dbo"."invoices" i on il.invoice_fk = i.invoices_hk ;'); EXEC('CREATE TABLE [sales].[dbt_dpurdy].[fct_invoice] AS (SELECT * FROM [sales].[dbt_dpurdy].[fct_invoice_temp_view]);'); USE [sales]; EXEC('DROP view IF EXISTS "dbt_dpurdy"."fct_invoice_temp_view";'); ```

possible root cause

In the discussion last week, we came to suspect dbt-fabric's ability to correctly look at other databases.

Currently there are two patterns for referencing other databases

1. three-part names: i.e. `database.relation.
2. calling USE [<DATABASE>]; before a statement that uses a two-part name.

for example the truncate macro shim explicitly excludes the databsae name

https://github.com/microsoft/dbt-fabric/blob/0b3a9065dbb8dabe4aa37fb8e48cdfe622682af1/dbt/include/fabric/macros/adapters/relation.sql#L98

[prdpsvs]

@dataders , The issue is related to metadata retrieval. By default, the metadata queries are using default DW. I have to explicitly use USE statement to run the metadata queries in the database where relation exists. That fixed the issue.

I am now thinking to update all metadata queries to use the relation.database instead. Any thoughts before I make this change? I could not think of other alternatives.

[dwpurdy]

I don't see why this wouldn't work but I don't know how dbt and the adapters work internally. Do we know how it's done for other adapters that support 3-part naming? Happy to help you test.

[prdpsvs]

@dwpurdy , I released 1.8.3, that should fix the issue. Please test and let me know if you see any issues.