[Preview] bc2adls and Fabric Open Mirroring

At the end of last year the Microsoft Fabric team introduced the feature of Open Mirroring inside Microsoft Fabric.
Accelerate app innovation with an AI-powered data platform | Microsoft Fabric Blog

With Open Mirroring you can create your own mirroring database inside Fabric. There was already the capability to use mirroring for Azure SQL, Snowflake, CosmoDB and PostgreSQL. But now you can extend it with your own application.

In that case you have only to put your files into the “Landing Zone” and Fabric will do the processing.

This was all based on .parquet files. A couple of weeks ago also the .csv file format is supported.
Open Mirroring UI enhancements and CSV support to help you get started today | Microsoft Fabric-blog | Microsoft Fabric
And that is a good thing. In that case the BC2ADLS tool for exporting data to Microsoft Fabric can also use it. So no need for running a notebook anymore. Fabric will do the CDC processing for you.
The benefit is then that you can use the data faster!
Open Mirroring (Preview) – Microsoft Fabric | Microsoft Learn

BC2ADLS support

Please mention that this feature is still in preview in bc2adls but also in Microsoft Fabric!
In the following version of bc2adls it is implemented:
Release 26.32 · Bertverbeek4PS/bc2adls
If you have any feedback just create a issue on the GitHub repo and we will follow that.

In bc2adls there is a new option added to support the open mirroring:

Also here you can setup your Landing Zone string (which you can copy from the Open Mirroring page in Microsoft Fabric).
The rest of the authentication is the same as the regular integration with Microsoft Fabric. See also:
BC2ADLS: MS Fabric enabled part 1 – Discover Microsoft Business Central
BC2ADLS: MS Fabric enabled part 2 – Discover Microsoft Business Central

Metadata

First you need to create the _metadata.json file by exporting the “Schema Export” in bc2adls. This will tell Open Mirroring how the structure will be for this table:

{
    "keyColumns": [
        "systemId-2000000000",
        "$Company"
    ],
    "SchemaDefinition": {
        "Columns": [
            {
                "Name": "Code-1",
                "DataType": "String"
            },
            {
                "Name": "Description-5",
                "DataType": "String"
            },
            {
                "Name": "CalcPmtDisconCrMemos-6",
                "DataType": "Boolean"
            },
            {
                "Name": "LastModifiedDateTime-8",
                "DataType": "DateTimeFormat"
            },
            {
                "Name": "timestamp-0",
                "DataType": "Int"
            },
            {
                "Name": "systemId-2000000000",
                "DataType": "String"
            },
            {
                "Name": "SystemCreatedAt-2000000001",
                "DataType": "DateTimeFormat"
            },
            {
                "Name": "SystemCreatedBy-2000000002",
                "DataType": "String"
            },
            {
                "Name": "SystemModifiedAt-2000000003",
                "DataType": "DateTimeFormat"
            },
            {
                "Name": "SystemModifiedBy-2000000004",
                "DataType": "String"
            }
        ]
    },
    "fileFormat": "csv"
}

RowMarker

Then you can just export the files. If you look closer there is also a column __rowMarker__ added at the end (In a couple of weeks you can also please it in front of it).

With the __RowMarker__ column Fabric knows what to do with it:
– 0 is for inserts
– 1 is for updates
– 2 is for deletes
– 4 is for upserts (which in bc2adls we are not using)
Open Mirroring (Preview) Landing Zone Requirements and Formats – Microsoft Fabric | Microsoft Learn

Be aware that also the file name must be in a 20 digit format and must be in a continuous number. If you do a reset of the table the number will also be reset!

Inside Mirroring Database

In Microsoft Fabric you can see the process if everything is correctly:

You can see here all the uploaded files but also the status of that specific table and how many rows he replicated (be aware this is not the amount of records that is in the table!)

Then when you want to see the data just go to the “SQL analytics endpoint” and run the sql commandlet:

All the data that is in Business Central is now also in Microsoft Fabric without running the a notebook script to process the delta’s!