Fabric Dataflow Gen2 Destination Schema

[Yizi]

Hi Chaps, I'm not sure if this has been answered, but I did some digging and couldn't find anything. 

I have Gen2 Dataflow in Fabric, which picks a warehouse as the destination. I've created 3 schemas, and I like the data to be placed in the schema of my choice; however, this is not the behaviour as it places the files automatically in the dbo schema. I do not see any options for this, and manually moving the table to another schema is not ideal.

My 3 schemas:

- Bronze <-- this is where I like to drop the files from dataflows

- Silver

- Gold

Any ideas? 

[RV0251]

Hi , Dataflow Gen2 does not automatically write to a custom schema in a Warehouse by default. When you pick a Warehouse destination in Dataflow Gen2, it will initially show only the dbo schema and create new tables there unless you explicitly select an existing table in another schema. That’s because the UI doesn’t expose a direct “schema” dropdown in the wizard by default. There is a way to pick a non-dbo schema if you’ve already created the table in that schema ahead of time: Pre-create your destination table in the Bronze (or other) schema. In the Dataflow Gen2 destination settings, change the destination and select the existing table in your desired schema. This will load your data into that schema instead of dbo. Alternatively, some community members noted that an Advanced option (“Navigate using full hierarchy”) sometimes lets you see and pick schemas when selecting your destination, but this can be hard to find and isn’t obvious in the default wizard. So the current behavior is: Default: Gen2 writes to dbo schema unless you select an existing table in another schema. Workaround: Pre-create tables in Bronze/Silver/Gold, then point your Gen2 flow to those existing tables. There isn’t yet a simple schema picker dropdown for new tables in the Gen2 destination wizard. Hope that helps clarify the behavior!

[ToddChitt]

My point is this:

It is NOT easy to figure out. It is a HORRIBLE user experience. It SHOULD be easier.

See this Ideas item: Dataflow Gen2 support for Destinations OTHER than ... - Microsoft Fabric Community

[ToddChitt]

This works, but to be honest, the user interface to make it happen is HORRIBLE. I NEVER would have found this on my own. It is NOT easily found!

For years, those of us that work in databases have been referring to objects with TWO-PART nomenclature (<schema name>.<table name>) so to NOT be able to specify that in the table name destination input box is a big setback.

At the very MINIMUM Microsoft should have put in TWO boxes there, one for schema name and one for table name. 

[DarioTero2024]

Hi

On Data destination:
Go to Advanced options and set full hierarchy True

Now you can see all shcemas

Regards,

Dario

[ToddChitt]

Agreed. It would be very beneficial to configure a Dataflow Gen2 to have its Warehouse destination be a NEW table in something other than the dbo schema. 

If you try to use two-part reference in the (new) Table Name option while configuring the destination, yoiu get an error that the name can contain only letters and numbers, etc.

Side note: two-part object reference is STANDARD OPERATING PROCEDURES and also considered a BEST PRACTICE. I ALWAYS reference tables with two-part names, even when they are in the dbo schema. 

NOT having this is a huge pain!.

[DM_W]

Hello, I have the same problem, I want to chose a different schema for my dataflow destination, did you find a solution except the one that is manual?

Thank you

[Imjkrao]

I have had the same issue. Unable to load the data into schemas other than dbo. I had to create a table under my own schema and then select the existing table in the destination option. 

It would be great if we could select the 'new table' and select the destination warehouse and drill it down to select the required schema.

[Yizi]

There is a shortcut I managed to figure out, and it does the job. If you create the table under the new schema, you can change the destination and select the existing table under the correct schema. 

Ideally this option should be available under the wizard but I understand the app is still in beta mode.

[ToddChitt]

Yes, this works, but if your table is of any decent size, manually creating it is a pain, so SSMS comes into play here as it allows the "Script table as >> Create..." option. Fabric does NOT have this capability, unfortunately.