Forum Discussion

annashenapha's avatar
annashenapha
Frequent Visitor
10 months ago
Solved

Dataflow Gen2 Refresh Fails with Timeout When Writing to Lakehouse/Warehouse

Environment Details:

  • Workspace: Fabric Premium
  • Dataflow Name: Dynamics_Plants_Imports_Dataflow1_Staging_Raw_Load
  • Source: Dataverse 
  • Destination Tried:
    • Fabric Lakehouse
    • Fabric Warehouse
  • Authentication: Organizational account
  • Connection: Encrypted connection enabled

Error Details:

  • Error Code: Lakehouse036
  • Message:
  • Microsoft SQL: The timeout period elapsed prior to completion of the operation or the server is not responding.
  • Underlying Error:
  • DataSource.Error: Failed to insert a table.
  • Request IDs:
    • Lakehouse attempt: 2fbb4468-1bfc-4a54-8859-e4f80a21005a
    • Warehouse attempt: 3bf6a1f7-a1c1-4208-8fe0-cd3cc78c9cfd
  • Timestamp:
    • Lakehouse: 2025-10-22T13:40:20Z
    • Warehouse: 2025-10-22T13:54:14Z

Steps Taken:

  1. Created staging dataflow to load raw data from Dataverse.
  2. Configured destination to Lakehouse → refresh failed with timeout.
  3. Changed destination to Warehouse → refresh still failed with timeout.
  4. Verified:
    • Organizational account authentication.
    • Privacy level set to None.
    • No on-premises gateway required.
  5. Attempted both Replace and Append modes.

Impact:

  • Cannot load large Dataverse tables (trd_plantsimportcommodityline and trd_plantsimportnotification) into Fabric destinations.
  • Business requires full dataset for reporting and compliance; filtering or limiting rows is not an option.

Request:

  • Guidance on:
    • Increasing timeout or optimizing bulk insert for Lakehouse/Warehouse.
    • Alternative approach for large Dataverse tables in Dataflow Gen2.
    • Any known limitations or configuration changes required.
data warehouse
  • v-dineshya's avatar
    v-dineshya
    10 months ago

    Hi annashenapha ,

    Thank you for reaching out to the Microsoft Community Forum.

     

    Hi lbendlin , Thank you for the prompt response.

     

    Hi annashenapha , 

    The error message "The timeout period elapsed prior to completion of the operation or the server is not responding" is triggered during the WriteToDataDestination step, especially when the volume of data is large, any complex transformations or non-foldable queries, The destination Lakehouse/Warehouse is under capacity pressure or has schema mismatches,  Fast Copy is not enabled or applicable and Network latency or authentication issues occur.

     

    Please try below options.

     

    1. Go to your Dataflow Gen2 settings. Enable “Require fast copy” under the destination settings. Check your source meets the below requirements.
    For databases --> 5M+ rows and for files --> 100MB+ CSV/Parquet.


    Note: Fast Copy currently supports Lakehouse only, not Warehouse.

     

    Fast copy in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

     

    2. If you are doing transformations like column expansion, type changes, split them as

     

    Query 1 --> Load raw data from Dataverse --> stage it.
    Query 2 --> Reference staged query --> apply transformations --> write to destination.

     

    Note: This allows DW compute to handle transformations more efficiently.

     

    Best practices for getting the best performance with Dataflow Gen2 in Fabric Data Factory - Microsoft Fabric | Microsoft Learn

     

    3. Check column order and names match between source and destination. Nullable columns are correctly mapped. Non-nullable columns receiving nulls can cause failures.

     

    Solved: Re: Read data from Dataverse table via Fabric Data... - Microsoft Fabric Community

     

    4. Instead of writing directly to Lakehouse/Warehouse, Write to the DataflowsStagingLakehouse. Then use a Notebook or Pipeline to move data to the final destination. This decouples the transformation and ingestion steps, reducing timeout risk.

     

    5. Check Fabric Capacity Metrics for throttling or overload. Review refresh history for signs of slow performance or failed steps.

     

    6. Check that you have ownership of all hidden Lakehouse artifacts created by Dataflow Gen2. If the original creator has left, reassign ownership.

     

    Solved: Re: Gen2 Dataflow refreshes breaking since last we... - Microsoft Fabric Community

     

    Note: No row limit, but performance degrades with large datasets. Warehouse only supports dbo schema currently. Query folding is critical for performance ensure transformations are foldable.

     

    Alternative workaround: Use a Gen1 Dataflow to stage data from Dataverse. Chain it to a Gen2 Dataflow for transformation and loading. Use Notebooks or Pipelines for final ingestion into Lakehouse/Warehouse.

     

    Solved: Dataflows Gen2 not connecting to lakehouse - Microsoft Fabric Community

     

     

    I hope this information helps. Please do let us know if you have any further queries.

     

    Regards,

    Dinesh

4 Replies