Query across multiple workspaces

[chinwobble]

Hi there,

I want to know is it psosible to query across multiple workspaces?

My project has strict data privacy requirements.
I want to two workspaces.

• raw data workspace where dynamic data masking is applied and users only have "Viewer" permissions
• A prod data workspace that contains all the cleaned and transformed and data

I want my dbt project to read from raw data workspace and write to the prod workspace. Is that possible?

[M4Al]

In the current fabric implementation you cannot do this via the SQL Endpoints that DBT uses.

You could use the 'shortcut' feature of Fabric if your data lives in a Lakehouse maybe?

[chinwobble]

@M4Al thanks for your response.
The 'shortcut' feature works with Fabric lakehouse.
Does that mean I can achieve this with the dbt-fabricpark dbt adapter instead?
https://github.com/microsoft/dbt-fabricspark

[ThomsenS]

You can access shortcuts in a lakehouse from your warehouse as long as they are in the same workspace using 3 part naming. In your query include the lakehouse name, e.g. select * from lakehouse_name.schema_name.shortcut_name.

We define them as sources in our projects. The source macro will then automatically create queries using 3 part naming.

version: 2

sources:
- name: source_name
  database: lakehouse_name
  schema: schema_name
  tables:
    - name: table_name/shortcut_name

[TheBishop-98]

If this capability isn't on the roadmap, it should be. My company has used the 'shortcut' method, but we shouldn't have to. We should be able to read across WS and still write to the same DW, if the user has access to those workspaces. I imagine we would have to define a fabric specific workspace property to further define and link to read those LH/DW.

[prdpsvs]

Shortcut capability is in the roadmap to the DW as well. Currently, the only way to access data from other workspace is to create a shortcut from LH or duplicate data.

With DW shortcut capability, the tables from other DW's in the same/across workspaces can be accessed in the same DW, similar to LH. Once this feature is enabled by the platform, the adapter will support shortcut sources from different workspaces.

[prdpsvs]

@M4Al thanks for your response. The 'shortcut' feature works with Fabric lakehouse. Does that mean I can achieve this with the dbt-fabricpark dbt adapter instead? https://github.com/microsoft/dbt-fabricspark

Yes, dbt-fabricspark creates shortcuts for you to access data from other LHs in the same or different workspaces and then access data. This is not currently possible with DW though.

[shreveasaurus]

We'd love to see this functionality from Microsoft too. Having to create a separate CI/CD process outside of dbt Cloud orchestration to facilitate shortcut creation is not an ideal workaround.

[prdpsvs]

I will add the ability to add shortcuts to the warehouse via json for automation purposes as part of dbt run infrastructure.
You can avoid creating a process outside of dbt executions.

[prdpsvs]

Adding text from other issue created by @boxysean

As Shortcuts become the preferred way to share datasets between different aspects of Fabric, support managing these shortcuts directly within dbt (similar to grants, post-hooks, or run-operations). dbt developers regularly manage both database objects and access to those objects within the same dbt project, and so giving them the primitives required to stay in one tool will boost developer productivity.

This should unlock the following:

dbt can write to the data lake from the Fabric Warehouse
dbt can operate on multiple workspaces (as captured in #220)