Forum Discussion

Luigia-Costabil's avatar
Luigia-Costabil
Advocate I
2 months ago
Solved

Azure Synapse Link for Dataverse to Fabric - CSV management

Hi Community,

I'm evaluating Azure Synapse Link (Append Only) as the ingestion layer for a Microsoft Fabric Medallion Architecture and I'm trying to better understand how the exported CSV files should be consumed.

From the Microsoft documentation, my understanding is that:

  • Dataverse changes are continuously pushed through the trickle feed engine.

  • Periodic snapshots (T1, T2, T3, etc.) are created.

  • In Append Only mode, updates and deletes are preserved as additional records (including IsDelete).

  • model.json points consumers to the latest consistent snapshot.

Based on this, I'm wondering whether a separate Bronze Delta layer is actually required.

For example:

Dataverse
→ Synapse Link (Append Only)
→ ADLS Gen2 CSV
→ Fabric Shortcut / External Table
→ Silver SCD Type 2

Questions:

  1. Can the Synapse Link Append Only CSV exports themselves be considered the Bronze layer?

  2. If I create Fabric Shortcuts or External Tables directly on top of the exported CSV files, will I preserve the complete CDC history, including intermediate updates and IsDelete records?

  3. Is there any advantage in materializing those CSV files into Delta Bronze tables, other than performance and schema management?

  4. If I materialize Bronze Delta tables using a Fabric notebook that runs periodically, do I risk losing part of the near real-time benefit provided by Synapse Link?

  5. How do practitioners typically consume Append Only exports in Fabric:

    • directly through Shortcuts / External Tables?

    • or by creating Delta Bronze tables?

I'm trying to determine whether the ADLS Append Only export can serve as the true Bronze layer, avoiding an additional replication step.

Thanks!

data engineering
Lakehouse
  • v-dineshya's avatar
    v-dineshya
    1 month ago

    Hi Luigia-Costabil , Thank you for reaching out to the Microsoft Community Forum.

    1. Do I need Synapse SQL pool to get delta?

    You do NOT need Synapse SQL Pool to get delta (CDC) from Synapse Link (Append Only). You already have the delta in CSV form.

    2. How do I consume delta without Synapse?

    You read files incrementally from ADLS. Use columns like IsDelete and timestamps (SinkCreatedOn, modifiedon). Reconstruct CDC yourself in Fabric.


    Please refer below sample code.

    df = spark.read.csv("adls_path", header=True)

    # Deduplicate by key + timestamp
    windowed_df = (
    df.withColumn("rn",
    row_number().over(
    Window.partitionBy("primaryKey")
    .orderBy(col("SinkCreatedOn").desc())
    )
    )
    .filter("rn = 1")
    )

    Note: This is exactly what Synapse SQL pool would have done for you but you are doing it in Fabric (cheaper + more control).

    Please try below Architecture.

    1. Synapse Link (Append Only) --> Cheap ingestion and Full CDC
    2. ADLS Gen2 (CSV, cheap storage) 
    3. Fabric Shortcut --> No data duplication and Direct access
    4. Bronze Delta tables --> Reliability, Performance and Fabric-native
    5. Silver (SCD2)

    Note:  Do NOT use Synapse SQL pool.

     

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

     

    Regards,

    Dinesh

5 Replies

  • arabalca's avatar
    arabalca
    Super User
    2 months ago

    Hi Luigia-Costabil ,

     

    Export to Data Lake (CSV export) is deprecated. Microsoft announced the deprecation of the "Export to Data Lake" feature effective October 15, 2023, and the final deadline to stop using it was November 1, 2024. Therefore, the scenario with CSVs in ADLS Gen2 you describe should no longer be your starting point.

    Today you have two paths depending on your destination:

    Going to Azure → Azure Synapse Link for Dataverse

    The direct successor to Export to Data Lake. Allows exporting in Delta format (in addition to the legacy CSV it still supports). This is the option for architectures centered on Azure Synapse Analytics or your own ADLS.

    Going to Microsoft Fabric → Fabric Link (Link to Microsoft Fabric)

    This native integration enables real-time connectivity between Dataverse and Microsoft Fabric without data movement, with no need for replication or ETL.

    The new low-latency sync engine writes directly from the Dataverse database to Delta Parquet, eliminating the intermediate CSV step used by the previous pipeline.

    The flow is fully managed by Microsoft:

    • Initial load (full load): Data is converted to Delta Parquet format during the initial sync. While in progress, tables appear as "unidentified" in the Lakehouse until it completes.
    • Automatic incremental sync: Once the initial load is complete, the system continuously refreshes Dataverse updates in the Lakehouse.

    Where does the data land? A Lakehouse in Fabric is automatically created with the tables you select, ready to query as Delta tables.

    The Lakehouse generated by Fabric Link acts as your Bronze/Landing layer. If you need to handle soft deletes or upserts, you do that in the Bronze → Silver step using notebooks or pipelines, processing the IsDelete field that Fabric Link exposes in the Delta tables.

     

    More:

    https://learn.microsoft.com/en-us/power-apps/maker/data-platform/fabric-link-to-data-platform

    https://learn.microsoft.com/en-us/power-apps/maker/data-platform/fabric-link-faq

     

    If this response has been helpful, please consider giving it a 👍 and marking it as a solution so other users with the same question can find it easily. 🙌

    • Luigia-Costabil's avatar
      Luigia-Costabil
      Advocate I
      2 months ago

      Hi @arabalca  , thanks for your reply.

      I consulted the following Microsoft documentation:

      Create an Azure Synapse Link for Dataverse with Azure Data Lake in Power Apps - Power Apps | Microsoft Learn

      My goal is to avoid using Link to Fabric because the costs of Dataverse storage are high. So, I'd like to transfer the data to ADSL Gen2 because the data storage cost is low. The documentation I attached states that the format used is CSV. To obtain the delta, I'd need to connect an Azure Synapse Pool, which I'd like to avoid because I'm already paying for Fabric capacity.

      I hope I have explained my intentions more clearly.

      • v-dineshya's avatar
        v-dineshya
        Community Support
        1 month ago

        Hi Luigia-Costabil , Thank you for reaching out to the Microsoft Community Forum.

        1. Do I need Synapse SQL pool to get delta?

        You do NOT need Synapse SQL Pool to get delta (CDC) from Synapse Link (Append Only). You already have the delta in CSV form.

        2. How do I consume delta without Synapse?

        You read files incrementally from ADLS. Use columns like IsDelete and timestamps (SinkCreatedOn, modifiedon). Reconstruct CDC yourself in Fabric.


        Please refer below sample code.

        df = spark.read.csv("adls_path", header=True)

        # Deduplicate by key + timestamp
        windowed_df = (
        df.withColumn("rn",
        row_number().over(
        Window.partitionBy("primaryKey")
        .orderBy(col("SinkCreatedOn").desc())
        )
        )
        .filter("rn = 1")
        )

        Note: This is exactly what Synapse SQL pool would have done for you but you are doing it in Fabric (cheaper + more control).

        Please try below Architecture.

        1. Synapse Link (Append Only) --> Cheap ingestion and Full CDC
        2. ADLS Gen2 (CSV, cheap storage) 
        3. Fabric Shortcut --> No data duplication and Direct access
        4. Bronze Delta tables --> Reliability, Performance and Fabric-native
        5. Silver (SCD2)

        Note:  Do NOT use Synapse SQL pool.

         

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

         

        Regards,

        Dinesh