Writing to a specific warehouse schema using a dataflow gen2

[pmscorca]

Hi,

I've a warehouse having some schemas for the tables to ingest. I need to write some data into a specific table of a specific schema using a dataflow gen2 (I need to use a dataflow gen2), but it isn't possible to select a specific schema for the warehouse destination.

I've noticed that there is this idea, in completed status: Write to custom schema in Dataflow Gen2 

Is this issue solved or not?
Any suggests to me, please? Thanks

[miguel]

Today, and what the original idea shared was tracking, you are able to use existing items (like databases) and schemas to navigate through them and create new tables within them. You can define the names of the new tables after picking the schema like in the example below where I first created a new schema (named schema1) inside the Warehouse experience and then through the data destination experience I get a message that a new table will be created in this schema:

In other words, there is no input box for schema. Instead, you need to select the specific schema from the navigation tree on the left as shown in the screenshot above.

If perhaps this is about having Dataflow create a new warehouse or a new schema, those are valid suggestions and we welcome such suggestions in the Fabric Ideas portal.

[miguel]

Could you share a screenshot of what you're able to see?

Also, please confirm that when you've selected the warehouse connector that you've selected the advanced option to enable schema navigation

[pmscorca]

Hi, I haven't selected the advanced option to enable the schema navigation (is it documented?).

But also selecting it the result doesn't change:

I cannot specify any schema for the new table.

[miguel]

Here's the link to the documentation about this capability:
Dataflow Gen2 data destinations and managed settings - Microsoft Fabric | Microsoft Learn

As mentioned by Antoine, once you select this option you'll be able to navigate to the schema of your choice and create or select a table within that specific schema.

The view from your screenshot fundamentally showcases all available warehouses in your workspace, so you need to expand the nodes to be able to see the schemas within them. Fundamentally navigating on the left pane to the schema of your choice.

[tayloramy]

Hi @pmscorca, 

Maybe @CharlesWebbMSFT can comment, but I've never seen the functionality to select a schema in dataflow gen 2.

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[pmscorca]

The idea status is completed: this is a wrong communication for the Fabric community!

[tayloramy]

Hi @pmscorca, 

As far as I know this is not possible. Dataflow Gen 2 automatically writes to DBO schema and there is no way to change that. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution. 

[pmscorca]

Hi @tayloramy , thanks for your reply.

So, the status of the above idea is wrong!

[anros · Accepted solution]

@tayloramy @pmscorca we implemented the capability of writing to a given schema in Azure Sql, Lakehouse, Warehouse and Fabric Sql quite some time ago already.

As mentioned by @miguel you need to ensure that you expand Advanced option when selecting the connector and set "Navigate using full hierarchy" to True.

Once done, on the next stage you'll be able to see the list of schema you can write to

Best regards,

Antoine

[pmscorca]

Hi @anros 

I'm referring when it occurs creating a new table (also if the "Navigate using full hierarchy" option is set to True): it isn't possible to specify any destination schemas. For me, this issue isn't solved!

[miguel]

I'm not sure I'm following. The schemas are in the left where you can pick them like perhaps db_datareader. Once that schema is selected you can input the name of a new table in then input box in the right.

what is the issue that you're seeing?

[pmscorca]

Hi, at the left of the input box where I can write the name of the table to create I don't see any input boxes for the related schema name.

[miguel]

I think I understand the request now. Can you confirm that what you're trying to do is actually have dataflow create a completely new schema?

[pmscorca]

Hi @miguel please follow me, thanks.

Remember like a pipeline allows to specify the schema name and the table name for a table.
Observe this picture, please

The selected schema for a new table to create doesn't maintained if I save the dataflow gen2 without running it.

I think that the actual feature to custom the schema for a destination table is incomplete and less useful: for me it isn't necessary to create a new idea to improve this feature.

Many thanks for your attention.

[miguel]

the schema is maintained and you can hover over the data destination card at the bottom of the query settings pane, right underneath the applied steps. This should give you all the information about what the data destination configuration is.

I do understand that perhaps the pipelines and dataflows experience are not exactly the same. I will pass this feedback to the appropriate set of individuals inside of my team who own this feature.

I do want to clarify that the functionality is there so, to address your initial question, Dataflows is capable of using a schema to load data as a new or existing table.

We welcome you to post any suggestions or feedback to improve the experience in the Fabric Ideas portal so other members of the community can also chime in.

[pmscorca]

Visually, when the Data destination is reopened the Navigation pane doesn't show the selected schema previously.