How to import Warehouse View in Dataflow Gen2?

[rvp_ordix]

My data is stored various tables of a Fabric Warehouse, and now I want to build a Dataflow to transform that data. To do that, however, I need to create a control table from the various Views of INFORMATION_SCHEMA within that Warehouse and I haven't found a way to achieve that. This is the query I want to implement in Fabric.

Pressing "Get Data" and then selecting the Warehouse only shows tables, not the views I need. I can't construct the table via Warehouse's Query Editor either, since inserting anything from those Views would always return the error "The query references an object that is not supported in distributed processing mode."

How am I meant to do this?

[v-kongfanf-msft]

Hi @rvp_ordix ,

You can create the storage table with the following sql statement.

CREATE PROCEDURE PopulateControlTable AS
BEGIN
    -- Insert data from 'datamart' schema
    INSERT INTO ControlTable (TABLE_NAME)
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA = 'datamart';
    
    -- Insert data from 'core' schema
    INSERT INTO ControlTable (TABLE_NAME)
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA = 'core';
    
    -- Insert data from 'staging' schema
    INSERT INTO ControlTable (TABLE_NAME)
    SELECT TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA = 'staging';
END;

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rvp_ordix]

I've already tried that, as mentioned in my post, and it doesn't work. Running that procedure throws the error "The query references an object that is not supported in distributed processing mode".