Build a Medallion architecture in MS Fabric Real time analytics — II

How to build Medallion architecture in real time data using Microsoft Fabric?

Summary

In this article you will find,

• How to promote raw data to silver layer using Update polices?
• How to aggregate data using Materialized view and promote that to gold layer?

Previous steps for this exercise can be found here — https://medium.com/@suryaprakashmcetit/build-a-medallion-architecture-in-ms-fabric-real-time-analytics-d17df5dd3379

How to promote raw data to silver layer using Update polices?

Validate that your mock data from EventHub is reaching the KQL bronze/raw table.

Create Silver table using KQL query

.create table SilverStockTableV3 (Name: string, Price: dynamic , Volume: int, MarketCap: dynamic, Open: real, High: real, Low: real,
close:real, IngestionDate: datetime  )

You can find same columns that are available in bronze table alone with that you see one new column IngestionDate added.

In Medallion silver layer is used for cleansing the data, deduplicate the record so it will be suitable for golden layer.

In our use case, I plan to add the ingestion time to the incoming data. This will allow us to identify the most recently arrived data.

.alter table
SilverStockTableV3 
policy update @'[{"Source": "NewRealTime", "Query": "NewRealTime | extend IngestionDate = ingestion_time()", "IsEnabled" : true, "IsTransactional": true }]'

In above KQL query, Source table is bronze table name‘NewRealTime’ and destination table is Silver named ‘SilverStockTableV3’.

Query parameter loads everything from source table + adds IngestionDate.

Update policy executes whenever new record inserted into bronze/source table and load the new data into destination table with defined changes.

Now if you trigger the mock data from EventHub that should load the data into bronze table and Update policy load the same data into Silver table.

When you run the below query for silver table you should see new data with addition IngestionDate column in silver table.

SilverStockTableV3
| take 100

Promote data to golden layer using Materialized View:

Create golden layer table using following query

.create table GoldStockTableV2 (Name: string, Price: dynamic , Volume: int, MarketCap: dynamic, Open: real, High: real, Low: real,
close:real, IngestionDate: datetime  )

This creates a new table with same schema structure as silver table

Create Materialized view to get the aggregates of the new data.

.create materialized-view with (backfill=true) GoldStockTableV2 on table SilverStockTableV3
{
    SilverStockTableV3
    | summarize arg_max(IngestionDate, *) by Name
}