Standard Extracts Framework

• Tech Stack: PowerApp, SQL Server, Azure Data Factory, Azure Data Lake Storage, SFTP
• Github URL: Project Link

The objective of this project was to create a simple, maintainable and extensible framework for outbound client and vendor files. This framework currently generates thousands of files on an annual basis. To achieve this, I created:

• SQL Schema: In the schema, a set of dimension tables store file specifications and standard code templates, and another set of fact tables store file run statistics and participants included on files for auditing purposes.
• PowerApp: Serves as a front-end for loading individual file specification attributes, scheduling details, search parameters and executable code. The app serves as a means to monitor the status of files during the development process, review last and next run dates, easily configure parameters for executable code (especially if the file uses a standard code template).
• Azure Data Factory: One dynamic ETL pipeline runs daily to identify which files need to run on a given day. By running a SQL query against the main file spec dimension table, the pipeline retrieves important file attributes, executable code and destination details. The pipeline then generates a raw file, processes the raw file for client needs, logs audit data to SQL stores and copies raw files from Azure Data Lake Storage to destination SFTP sites.