09 Exploring the Accelerator

1. Create Metadata for ELT Framework: Begin by creating sample metadata to extract data from Wide World Importers. Navigate to the data factory pipeline wwi-elt-framework pipeline. Run the pipeline

2. SQL Queries to Understand Metadata: Execute the following SQL queries one at a time to understand what the metadata represents.

Declare @SourceSystem VARCHAR(50), @StreamName VARCHAR(100)

SET @SourceSystem='WWI'
SET @StreamName = NULL

 -- Ingest Definition
 select * from  ELT.IngestDefinition
 where SourceSystemName = @SourceSystem
 and StreamName like  ISNULL(@StreamName,'%')

 --Next Ingestion
EXEC ELT.GetIngestDefinition @SourceSystem,@StreamName,50

--Historical Ingest Instances
 select * from ELT.IngestInstance
 where IngestID IN (SELECT IngestID from  ELT.IngestDefinition where SourceSystemName = @SourceSystem and StreamName like  ISNULL(@StreamName,'%'))
 Order by IngestInstanceID DESC

--L1 Transform Definition
 select * from ELT.L1TransformDefinition
 where IngestID IN (SELECT IngestID from  ELT.IngestDefinition where SourceSystemName = @SourceSystem and StreamName like  ISNULL(@StreamName,'%'))

--Next L1 Transform Instances
EXEC ELT.GetTransformInstance_L1 @SourceSystem, @StreamName

--Historical L1 Transform Instances
select * from ELT.L1TransformInstance
where IngestID IN (SELECT IngestID from  ELT.IngestDefinition where SourceSystemName = @SourceSystem and StreamName like  ISNULL(@StreamName,'%'))
order by L1TransformInstanceID

--L2 Transform Definition
 select * from ELT.L2TransformDefinition
 where IngestID IN (SELECT IngestID from  ELT.IngestDefinition where SourceSystemName = @SourceSystem and StreamName like  ISNULL(@StreamName,'%'))

 --Next L2 Transform Instances
 EXEC ELT.GetTransformInstance_L2 @SourceSystem, @StreamName

 --Historical L1 Transform Instances
 select * from ELT.L2TransformInstance
 where IngestID IN (SELECT IngestID from  ELT.IngestDefinition where SourceSystemName = @SourceSystem and StreamName like  ISNULL(@StreamName,'%'))
 order by L2TransformInstanceID

3. Understand ELT Framework Concepts: It is recommended to read the wiki of the ELT framework to understand the concepts of ingestion definition, instances, L1 Transformation, and L2 Transformation.

4. Run Master ELT ASQL Pipeline: After running the above queries, execute the generic Master ELT ASQL pipeline that uses this metadata. Observe how the pipeline integrates with the ELT Framework and monitor its execution to understand the sequence.

Parameter	Description	Recommended Default Value
SourceSystemName	Metadata of data source	WWI
StreamName	Metadata of table/entity name	Usually left blank to load all tables. For now maybe set it to a Stream Name like Colors to run end to end on one table
DelayTransformation	Flag to indicate whether the Level 1 Transformation of data is done as part of ingestion or delayed and done in another schedule	0
BronzeObjectID	Bronze Lakehouse ID	Replace with value relevant for your lh_bronze Lakehouse ID for e.g. c6c5024f-de55-45ca-a79a-decbe16235e3
BronzeWorkspaceID	Bronze Workspace ID	Replace with value relevant for your Workspace ID for e.g. 7e89660-0646-4c6d-b003-185d3a3234b6
SilverObjectID	Silver Lakehouse ID	Replace with value relevant for your lh_silver Lakehouse ID for e.g. c6c5024f-de55-45ca-a79a-decbe16235e3
SilverWorkspaceID	Silver Workspace ID	Replace with value relevant for your Workspace ID for e.g. 7e89660-0646-4c6d-b003-185d3a3234b6
GoldObjectID	Gold DW ID	Replace with value relevant for your dw_gold Lakehouse ID for e.g. 7e2bbf6b-43fb-498c-90e3-56199c8c3b5e
GoldWorkspaceID	Gold Workspace ID	Replace with value relevant for your Workspace ID for e.g. 7e89660-0646-4c6d-b003-185d3a3234b6
GoldDWEndpoint	Gold DW Endpoint	Replace with value relevant for your Workspace ID for e.g. 2ipjetq4tn6elkixc3xundvzbi-u4ai3dmkby5u5daorxfpvr5n5q.datawarehouse.fabric.microsoft.com

With these parameters, the pipeline will fetch metadata from controlB, land data from Wide World Importer tables to OneLake lh_bronze, perform a Level 1 transformation to OneLake lh_silver using Spark notebooks, and finally create an aggregated snapshot as Level 2 Transformation in dw_gold using stored procedures.

Check out the video for an overview of Fabric Accelerator.