Suresh Makkena

Implemented dynamic loading of dimensions and facts in Azure Synapse Analytics using stored procedures. This approach ensures efficient and scalable data processing, maintaining the integrity and performance of a data warehouse.

The procedure dynamically loads data into 15 dimension tables. It handles SCD and ensures that only new or updated records are processed. Another procedure dynamically loads data into eight fact tables. It ensures that surrogate keys are correctly handled and only new or updated records are processed.

Dynamic SQL allows the procedures to handle multiple tables without hardcoding table names:
• Cursor-based looping is used to iterate the list of dimension and fact tables, ensuring each table is processed.
• The MERGE statement is used to perform upserts (update or insert), efficiently handling incremental data loads.

ARCHITECTURE AND COMPONENTS
• Metadata Table stores information about the tables to be processed, including queries and timestamps for incremental loading.
• Stored procedures handle data extraction, transformation, and loading based on the metadata.
• A single pipeline uses the metadata table to process multiple tables within a single pipeline dynamically. It is split into two branches to handle incremental and full load scenarios separately.
• Data is loaded into Delta tables in Azure Data Lake Storage Gen2 (ADLS Gen2). These tables provide ACID transactions, scalable metadata handling, and efficient data processing.
• Serverless SQL is utilized in Azure Synapse Analytics to query data stored in Delta tables. It avoids needing a dedicated SQL data warehouse, significantly reducing costs.

In this project, I have designed and implemented a dynamic data pipeline to migrate data from on premise systems to the cloud. By leveraging a metadata-driven approach, I efficiently manage the ETL process for multiple tables using a single pipeline, optimizing performance and cost.

Customized the inbound-outbound tool for commercial line applications to suit requirements. I automated this tool by creating the skeleton for the most frequently used jobs required to run on commercial line applications. I utilized the data acquired through extensive analysis and research on the technology and concepts best suited for implementing this functionality.

This tool provides a 'skeleton' framework for the job flows and allows for quickly tailoring each flow to specific requirements. With this framework in place, batch flows can be prepared and deployed rapidly, ensuring faster time to market and minimizing production delays. Since adopting this tool, the process of implementing batch flows has become notably quicker and more efficient.