Problem

This is the story of a project I worked on to design and implement an Azure database along with an ETL (Extract, Transform, Load) pipeline. The project’s objective was to efficiently load files into the database while maintaining data integrity and optimizing performance.

After several attempts by others to tackle the complexities of the project, I was brought in to deliver a solution. My task was to leverage my expertise in Azure services and data integration processes to meet the project's goals.

This covers the project challenges and my solution.

Solution

The client requested that the data be extracted from the source and loaded into a database ready to be published in PowerBI.

I firstly designed a architecture solution and liaising with the client to confirm of how the solution will work.

To facilitate this, I first created Azure Blob Storage and Data Lake Storage to collect raw data and store the cleaned, transformed data in the appropriate locations.

I set up copy activities, linked services, and datasets, integrating all of these into the pipeline and configuring them accordingly for seamless data flow. Before processing, I implemented checks to verify data availability, ensuring the pipeline only triggered when the required data was present.

I then applied data validation steps by collecting the metadata and using if conditions to ensure that the incoming data adhered to the correct schema, preventing any malformed data from entering the pipeline. If the data doesn't meet the standard, I set up a notification system to let me know.

I was then able to validate my datasets in Microsoft Azure Storage Explorer to show that my datasets have been stored in the storage created.