Objective

The Main objective of this process is to automate the data which is available in data lake and ensure the high-quality analytical data from various sources.To make this process easily scalable and check the quality of data using Great Expectation which helps to reduce the workload of data scientists and manual process.

To provide the scorecard of data quality in Power BI which provides helpful insights to improve the quality of data.

Solution Architecture

The diagram below shows a high-level architectural design for the data quality analyzing process using Great Expectations, Azure data lake,Azure blob storage,Azure databricks and Power BI.

Tech-Stack

• Great Expectations
• Azure Databricks
• Azure Blob storage
• Azure Data lake

Data Quality Analyzing Process

• Collecting sampled dataset from data table using spark in the Azure databricks.
• Mounting the Blob Storage path of the Great Expectation folder into the Azure databricks.
• Preparing the batch of data to be evaluated.
• Executing the expectation validation on top of the selected sampled data.
• Collecting the Great Expectation results and store those results in a separate data lake table.
• Consuming the result data using the Power BI to visualize the data.

Introduction:

The Main objective of this ETL process to data will be extracted from 3 types of sources,and ingest those raw data into Azure Synapse and transform it to load Facts and Dimension tables.

Ingest pipeline design describes how the raw data transformed from source systems to sink (Synapse) and shows how Azure Data Factory activities are used during the data ingestion phase.

Raw data ingestion design

Below diagram shows a high-level design for copying data from sources ARGUS - SQL server, SAP ECC, and flat files to target data warehouse (Sink) on cloud Azure Synapse Analytics.

In this process configuration driven framework is copying the data from sources to target using a csv file which consists of source & destination schema,table and path info which is stored in ADLS2. using these configuration files to be read and passed to the pipeline dynamically.

Step 1:
Pipeline reads data from config file to get database, tables, path

Step 2:
Using ADF linked service and data set objects, copy data from source to sink

Step 3:
All raw data ingestion load is configured to perform “Truncate and load”

Azure Synapse destination:

Pipeline auto-creates tables directly based on source column names and data types

Data transformation design

Data transformation describes how raw data gets transformed and restructured into facts and dimension tables as per the designed data model using Star schema.

data transformation will be implemented using two approaches

SQL script driven

Pipeline reads data from config file to get database, tables, path

Visual way of transformation – Code free

Using ADF Data Flow Activity to transform & load data into Synapse

Transformation using T-SQL

Both our Dim and Fact implement using Slow changing dimensional type1 approach in TSQL.

Dimension And Fact Load:

Step 1: Create SQL views for dimension that holds transformation logic

• Surrogate key – MD5 Hash based on natural key
• MD5_HASH column – for all dimensional attributes to track the changes
• LAST_LOAD_TS - housekeeping column
• Dimensional attributes

Step 2: Create Store Procedure to perform Inserts / Updates for loading SCD Type 1 dimensions. This procedure takes source table, target table names and primary key column as inputs

Step 3: Create and load Dimensional tables from Staging VIEWS and Store Procedure

Introduction:

In this ETL process data will be extracted from 3 types of sources,and ingest those raw data into Snowflake and transform it to load Facts and Dimension tables.

Ingest pipeline design describes how the raw data transformed from source systems to sink (Snowflake) and shows how Azure Data Factory activities are used during the data ingestion phase.

Raw data ingestion design

Below diagram shows a high-level design for copying data from sources ARGUS - SQL server, SAP ECC, and flat files to target data warehouse (Sink) on cloud Snowflake.

In this process configuration driven framework is copy the data from sources to target using a csv file which consists of source & destination schema,table and path info which is stored in ADSL2.using these configuration files read and passed to pipeline dynamically.

Step 1:

Pipeline reads data from config file to get database, tables, path

Step 2:

Using ADF linked service and data set objects, copy data from source to sink

Step 3:

All raw data ingestion load is configured to perform “Truncate and load” method

In Snowflake, ADF does not provide auto-create tables option. Table creations will be created using DDL scripts

Data transformation design

Data transformation describes how raw data gets transformed and restructured into facts and dimension tables as per the designed data model using Star schema.

data transformation will be implemented using two approaches

SQL script driven

Pipeline reads data from config file to get database, tables, path

Visual way of transformation – Code free

Using ADF Data Flow Activity to transform & load data into Synapse

Transformation using T-SQL

Both our Dim and Fact implement using Slow changing dimensional type1 approach in TSQL.

Dimension And Fact Load:

Step 1: Create SQL views for dimension that holds transformation logic

• Surrogate key – MD5 Hash based on natural key
• MD5_HASH column – for all dimensional attributes to track the changes
• LAST_LOAD_TS - housekeeping column
• Dimensional attributes

Step 2: Create Store Procedure to perform Inserts / Updates for loading SCD Type 1 dimensions. This procedure takes source table, target table names and primary key column as inputs

Step 3: Create and load Dimensional tables from Staging VIEWS and Store Procedure

Introduction:

The Main objective of this ETL process to data will be extracted from 3 types of sources,and ingest those raw data into Azure Synapse and transform it to load Facts and Dimension tables.Transformed from source systems to sink (Synapse) and connect the sql dedicated pool to power BI for generating reports based on their business needs.

Below diagram shows a high-level architectural design for ETL using azure data factory,azure apache spark and Power BI in Azure Synapse Analytics.

Ingestion RAW data

For fintech application data needs to be extracted from multiple sources such as postgreSQL,mongodb and flat files which ingest raw data into azure data lake gen 2.Since data ingestion will be huge between postgreSQL and synapse.

In our implementation ,handled with 3 different approach

1. Ingest postgreSQL tables to Azure data lake storage gen2 and copy same data from ADLS gen2 to SQL dedicated pool
2. For Ingesting mongodb transformation data will be in BSON format.these data will convert and flatten the relational database format using apache spark and migrate the same data into SQL dedicated sql pool.
3. Ingest Flat files into Azure data lake storage gen2 and convert into external view to SQL dedicated pool

Transformation of RAW data

Dimension And Fact Load:

Step 1: Create SQL views for dimension that holds transformation logic

• Surrogate key – MD5 Hash based on natural key
• MD5_HASH column – for all dimensional attributes to track the changes
• LAST_LOAD_TS - housekeeping column
• Dimensional attributes

Step 2: Create Store Procedure to perform Inserts / Updates for loading SCD Type 1 dimensions. This procedure takes source table, target table names and primary key column as inputs

Step 3: Create and load Dimensional tables from Staging VIEWS and Store Procedure

Power BI representation:

Setting up the power BI tools and connecting with synapse for designing the reports based on business requirements.