Business Intelligence

Overview

Design, develop and implement an ingestion framework from Oracle source to Azure Data Lake, covering initial load and incremental ETL.

• Design the ingestion framework and change data capture, prioritizing Oracle Golden Gate and Azure Data Factory.
• Prepare design artifacts and collaborate with IT partners on Golden Gate configuration.
• Maintain a dynamic ETL pipeline to add new tables and data elements.
• Map data source to reporting destination via physical data modeling.
• Review requirements, database tables, and database relationships; identify gaps and recommend optimizations for the current reporting environment.
• Develop scripts and update database and data lake structures.
• Access and work with Oracle DB, SQL Server, SSIS, SQL Developer, Azure Data Studio, Azure Data Factory, Databricks, and other tools to build solutions.
• Communicate proactively with business and IT experts on changes required for conceptual, logical, and physical models, reviewing dependencies and risks.
• Develop ETL strategy and solutions for various data modules; understand tables and relationships.
• Create low‑level design documents and test cases.
• Design package workflows, develop and test incremental and full load processes.
• Build high‑quality ETL mappings, scripts, and jobs from source to data warehouse and from warehouse to data mart.
• Perform unit testing; monitor performance and improve as needed.
• Conduct consistency checks, troubleshoot performance and load issues, and log activity for each package and transformation.
• Review overall ETL performance.
• Execute end‑to‑end integrated testing for full load and incremental load.
• Plan for go‑live, production deployment, and configure parameters and scripts.
• Test and review deployment instructions; create release documents and support code deployment across servers.
• Provide go‑live support and review post‑deployment.
• Assess and recommend improvements to existing ETL processes, tools, and timelines.
• Review infrastructure pain points and improve overall process efficiency.
• Transfer knowledge to ministry staff by developing documentation and delivery of training sessions.
• Document troubleshooting steps, configuration, and scripts; oversee transfer of all deliverables.

• 7+ years of experience with SQL Server, SSIS, and T‑SQL development or similar.
• 2+ years of experience with Azure SQL Database, Azure Data Factory, Azure Databricks, and Python development.
• Experience building data ingestion and change data capture using Oracle Golden Gate.
• Experience designing and developing data warehouses, dimensional data marts, and handling delta and full loads.
• Proficiency with SSIS, ADF, Databricks, and other cloud‑based ETL tools.
• Experience with Microsoft SQL Server both on‑premise and within Azure.
• Knowledge of data modeling tools such as SAP Power Designer and Visio.
• Familiarity with Snowflake and star‑schema modeling.
• Experience designing solutions with slowly changing dimensions.
• Deep knowledge of database performance optimization and SQL (including PL/SQL).
• Understanding of data warehouse architecture with Delta Lake and dimensional models.
• Strong analytical, design, development, and testing skills for ETL programs.

• 7+ years with SSIS, stored procedures (must have).
• 2+ years with Azure Data Lake, Data Warehouse, Azure Data Factory, and Azure Databricks pipelines (must have).
• 2+ years Python (must have).
• Oracle Golden Gate, SQL Server, Oracle experience.
• Ability to present technical requirements to the business.
• Major asset: experience building ingestion, history, and change data capture using Oracle Golden Gate.

• Design documentation and analysis skills (30 points): creation of functional and detailed design documents, fit‑gap analysis, requirement reviews, coding exercises and reviews.
• Development, database and ETL experience (60 points): practical experience in Microsoft software development, Azure Dev Ops, data vault and dimensional data mart schemas, ETL development, and ongoing support of Azure pipelines and SSIS.
• Knowledge transfer (10 points): ability to conduct training sessions, develop learning activities, and ensure complete knowledge transfer to other team members.

• Must have prior work experience conducting knowledge transfer and training sessions.
• Develop learning activities using a review‑watch‑do methodology and demonstrate ability to prepare and present.
• Produce documentation and materials as part of knowledge transfer.
• Facilitate classroom‑based or virtual instructor‑led sessions for developers.
• Monitor milestones and status reports to confirm completion of knowledge transfer.