I want to implement the Gold layer in the synapse delta lake how to do that

Question

WE have implemented the Synapse with delta lake mechanism till silver i want to implement gold layer now dont understand how to do it we are using Azure server less sql

how the gold layer model and how i can automate the process

Answer 1

Medallion Architecture (or Milti hop architecture) in Data Lakehouse recommends multi-layered approach to building a single source of truth for enterprise data products. Below are the 3 layers describing the quality of the data in each of these layers.

BRONZE layer contains raw data. This layer contains just raw data located on csv subfolder.

SILVER layer contains refined data (Example: adding good column names, data types, etc.). This holds the cleansed and transformed data in Delta format.

GOLD layer contains business-level aggregates. Gold layer contains a model in star schema, in Facts and Dimensions.

Since you have the Silver layer already implemented, below are the next steps you can think of implementing using Azure Synapse Analytics notebooks for your Gold layer. Create a Synapse notebook and implement Steps 1 to 4.

Step 1: Create a dataframe on the silver table

dfs = spark.read.load("your silver table location")

Step 2: Keep only the related columns

dfc= dfs.select("column1","column2")

Step 3: create a temporary view on the dedublicated data set

dfc.createOrReplaceTempView("stg_dim")

Step 4: Create your dimension and facts tables

%sql
MERGE INTO edw_gold.dim_table as target
USING stg_dim as source
ON target.column1=source.column1
WHEN NOT MATCHED
  THEN INSERT (column1,column2) values (source.column1,source.column2)

Step 5: Create a Synapse pipeline and import the notebook.

Step 7: Run your pipeline to load the data to Gold layer Dimension and Fact tables.

Step 8: Validate the data load in Gold layer

%sql

SELECT 'DIM_TABLE' AS TABLE_NAME ,count(*) as Count FROM EDW_GOLD.DIM_TABLE

Hope the below steps will help to implement your Gold layer in Synapse Delta lake.