How to save tables created through Dataflow in SQL Azure?

Hi Agnes_BOURLON ,

Thanks for reaching Microsft Fabric commuinty Support. in addition to lbendlin mentioned

there are several way for setting up your flow:

 

• Power Automate can fetch the data from a location where the Dataflow outputs are stored and load it into an SQL database.
• Configure your Dataflow to output data as CSV or Excel files to a storage location like ADLS gen2 or Fabric lakehouse
• Create a database table to store historical data for each extract.

Example schema:

CREATE TABLE HistoricalData (

    ExtractDate DATE NOT NULL,

    TableName NVARCHAR(50) NOT NULL,

    Field1 NVARCHAR(MAX),

    Field2 NVARCHAR(MAX),

    -- Add fields as needed

    PRIMARY KEY (ExtractDate, TableName, Field1)

);

• Power Automate can load the exported data from the Dataflow destionation to the SQL database. Append the current date (utcNow() function in Power Automate) as the Extract Date to each record

Limitations of Power Automate:

• Performance: Not ideal for very large datasets due to throttling.
• Manual File Management: Requires storing Dataflow outputs in an intermediary location.

Alternative: Azure Data Factory (ADF)

If your data volume is large or requires higher performance:

• Use ADF to fetch data directly from the Dataflow output in Azure Data Lake Storage and write it to an SQL database.
• ADF supports incremental loading and handles large-scale data efficiently

Power Automate is suitable for small to medium-sized datasets and incremental data loading workflows.

 

 

Thanks,

Prashanth Are

MS Fabric community support.

 

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos"