Business Intelligence Specialist

**Position: Business Intelligence Specialist - Senior** **Location: 315 Front Street, Toronto, ON** **Security Level: CRJMC** **Must haves**: - 7+ years in ETL tools such as Microsoft SSIS, stored procedures (Must Have) - 2+ Azure Data Lake and Data Warehouse, and building Azure Data Factory pipelines (Must Have) - 2+ years Python (Must Have) **Description** **General Responsibilities** - Design, develop and implement ingestion framework from Oracle source to Azure Data Lake - initial load and incremental ETL. Used tools are: - Oracle Golden Gate (knowledge and experience are an asset but not required) for data ingestion and change data capture (currently in final stages of proof of concept) - Azure Data Factory (expert knowledge) to maintain pipeline from Oracle to Azure Data Lake - Azure Databricks/PySpark (good Python knowledge required) to build transformations of raw data into curated zone in the data lake - Azure Databricks/Python/SQL (good SQL knowledge required) to develop and/or troubleshoot transformations of curated data into datamart model **Review the requirements, database tables, and database relationships - Identify gaps and inefficiencies in current production reporting environment and provide recommendations to address them in the new platform.** **Design ingestion framework and CDC - preferred tools are Oracle Golden Gate and Azure Data Factory** - Prepare design artifacts - Work with IT partner on configuration of Golden Gate - responsible to provide direction and "how to". - Maintain dynamic pipeline for ETL ingestion to add new tables and data elements **Data design - physical model mapping from data source to reporting destination.** - Understand the requirements. Recommend changes to the physical model. - Assist with data modelling and updates of source-to-target mapping documentation - Develop scripts for the physical model, and update database and/or data lake structure. - Access Oracle DB and SQL Server environments, use SSIS, SQLDeveloper, Azure Data Studio, Azure Data Factory, Databricks and other tools for developing solution. - Proactively communicate with business and IT experts on any changes required to conceptual, logical and physical models, communicate and review dependencies and risks. **Development of ETL strategy and solution for different sets of data modules** - Understand the Tables and Relationships. - Create low level design documents and test cases. - Create the workflows of package design **Development and testing of data with Incremental and Full Load.** - Develop high quality ETL mappings/scripts/jobs - Develop ETL from source to Data Warehouse - Develop ETL from Data Warehouse to Data Mart - Perform unit testing - Ensure performance monitoring and improvement **Performance Review, data Consistency checks** - Troubleshoot performance issues, ETL Load issues, log activity for each Individual package and transformation. - Review Performance of ETL overall. **End-to-end Integrated testing for Full Load and Incremental Load** **Plan for Go Live, Production Deployment.** - Create production deployment steps. - Configure parameters, scripts for go live. Test and review the instructions. - Create release documents and help build and deploy code across servers. **Go Live Support and Review after Go Live.** - Review existing ETL process, tools and provide recommendation on improving performance and reduce ETL timelines. - Review Infrastructure and any pain points for overall process improvement **Knowledge Transfer to Ministry staff, development of documentation on the work completed.** - Document share and work on the ETL end-to-end working knowledge, troubleshooting steps, configuration and scripts review. - Transfer documents, scripts and review of documents. **Experience and Skill Set Requirements** Please note this role is part of a Hybrid Work Arrangement and resource(s) will be required to work at a minimum of 3 days per week at the Office Location. **Experience**: - Experience of 7+ years of working with SQL Server, SSIS, and T-SQL development or similar - Experience of 2+ years of working with Azure SQL Database, Azure Data Factory, Databricks and Python development - Experience building data ingestion and change data capture using Oracle Golden Gate - Experience working with building databases, data warehouses and dimensional data marts and working with delta and full loads - Experience with any ETL tools such as SSIS, ADF, Databricks, other cloud tools - Experience working with MS SQL Sever on premise and within Azure Environment - Experience on Data modeling, and tools - e.g. SAP Power Designer, Visio - Experience with snowflake and star schema model. Experience in designing data warehouse solutions using slowly changing dimensions. - Experience working with SQL Server SSIS and other ETL tools, solid knowledge and experience with SQL, other RDBMS (Oracle, PL/SQL) - Understanding data warehouse architecture with a delta lake, dimensiona