Senior ETL Developer – Informatica IDMC

Senior ETL Developer – Informatica IDMC

• Client: Land & Resources Cluster
  
• Location: Toronto, Ontario
  
• Work Arrangement: Hybrid – Mandatory 3 days onsite per week
  
• Office Location: 40 St. Clair Avenue West, Toronto
  
• Start Date: February 12, 2027
  
• End Date: March 31, 2027
  
• Security Clearance: No clearance required
  

Position Overview
We are seeking a highly skilled and experienced Senior Software Developer – ETL to support the Land & Resources Cluster within the Ministry of Public and Business Service Delivery and Procurement. The successful candidate will design, develop, migrate, test, optimize, and maintain complex ETL and data integration solutions.
This role requires strong hands-on expertise in Informatica Intelligent Data Management Cloud (IDMC), Informatica PowerCenter v10.2, Oracle databases, PL/SQL, SQL performance tuning, data warehousing, Azure Data Factory, and cloud technologies. The consultant will work closely with business analysts, developers, QA teams, vendors, and other stakeholders to deliver reliable, scalable, and high-quality data solutions.

Key Responsibilities
The successful candidate will be responsible for:

• Designing, developing, maintaining, and supporting ETL processes using Informatica Cloud (IDMC).
  
• Migrating Informatica PowerCenter v10.2 ETL code, including embedded SQL and PL/SQL developed using Oracle 11g and Oracle 19c.
  
• Testing migrated SQL code and PL/SQL-based stored procedures.
  
• Performing SQL and PL/SQL performance tuning and optimization to improve scalability and application performance.
  
• Conducting root cause analysis and resolving ETL issues, including performance tuning, recovery, and optimization.
  
• Performing data extraction, transformation, and loading from multiple data sources into data warehouses using star and snowflake schemas.
  
• Translating technical specifications into fully tested applications and data pipelines.
  
• Collaborating with vendors, developers, QA teams, business analysts, and project stakeholders on unit testing, debugging, validation, and issue resolution.
  
• Preparing technical designs, deployment guides, support documentation, and knowledge-transfer materials.
  
• Participating in Agile/Scrum development cycles.
  
• Ensuring compliance with data governance, security, access control, architecture standards, and development methodologies.
  
• Providing post-implementation and application support.
  
• Supporting database administration activities, including configuration, backup/recovery, user roles, and database management.
  

Requirements

Mandatory Technical Skills
Candidates must demonstrate at least 5 years of experience in the following areas:
ETL, Data Warehousing & Database Development

• Data warehousing design and development.
  
• Extracting, transforming, and loading data from multiple sources.
  
• Developing data warehouses using star and snowflake schemas.
  
• Designing, building, automating, and optimizing complex ETL/ELT processes.
  
• Strong hands-on experience with PL/SQL and T-SQL.
  
• Developing complex procedures, packages, views, and scripts for:
  
  • Data denormalization
    
  • Data extraction
    
  • Data transformation and cleansing
    
  • Data loading
    
• Creating denormalized data assets from highly normalized Oracle databases and loading them into other DBMS platforms, including Microsoft SQL Server and PostgreSQL.
  
• Advanced SQL development and query optimization.
  
• Data modelling, including relational and dimensional modelling.
  
• Data replication and database administration.
  

Informatica & ETL Migration
The candidate must have demonstrated experience with:

• Informatica Intelligent Data Management Cloud (IDMC).
  
• Informatica PowerCenter v10.2.
  
• Migrating ETL code from Informatica PowerCenter to Informatica Cloud.
  
• Migrating embedded SQL and PL/SQL code developed using Oracle 11g and Oracle 19c.
  
• Testing migrated SQL code and PL/SQL-based stored procedures.
  
• ETL troubleshooting, performance tuning, failover, recovery, and production support.
  

Azure & Cloud Technologies

• Advanced hands-on experience with Azure SQL Server.
  
• Advanced experience with Azure Data Factory.
  
• Building and maintaining Azure Data Factory pipelines.
  
• Experience with copy activities and custom Azure development.
  
• Experience with cloud technologies, including Microsoft Azure, AWS, and Google Cloud.
  

Programming & Scripting

• Advanced SQL and PL/SQL programming.
  
• T-SQL.
  
• Python scripting.
  
• Unix Shell scripting.
  
• Programming for database change tracking and data warehousing.
  

Database & Oracle Technologies
Strong experience with relational databases, including:

• Oracle
  
• MySQL
  
• Microsoft SQL Server
  
• PostgreSQL
  

Experience with Oracle technologies and tools including:

• Oracle Enterprise Manager (OEM Cloud 12c and OEM 11g Grid)
  
• Oracle Net Services
  
• Oracle Connection Manager
  
• Oracle Advanced Security
  
• Oracle RAC
  
• Oracle Data Guard
  
• Oracle Exadata
  
• Oracle Warehouse Builder (OWB)
  
• RMAN backup, recovery, restore, and environment refresh activities
  
• Data migration and integration across Windows and UNIX environments.
  

Additional Technical Requirements

• Elasticsearch for search/reporting and performance optimization.
  
• Experience with JIRA and Azure DevOps.
  
• Experience using GitHub for version control and data model changes.
  
• Experience with issue management, troubleshooting, debugging, and application sustainment.
  
• Experience working with middleware, gateways, communication protocols, and integration technologies.
  

Core Software Development Skills
Candidates must have at least 5 years of experience with:

• Technical analysis and application design.
  
• Program code and detailed programming specifications.
  
• Writing, generating, testing, and debugging code.
  
• Unit testing and application integration.
  
• Developing and maintaining system design models, technical documentation, and specifications.
  
• Evaluating technical design options and conducting gap analysis.
  
• Translating business requirements and functional specifications into technical and application designs.
  
• Developing and supporting large, complex, mission-critical applications.
  
• Working closely with QA teams to test and debug applications, extensions, and integration services.
  
• Analysis, design, and development of complex multi-tier web-based applications.
  
• Microservices architecture and off-the-shelf solutions.
  
• Proof-of-technology exercises, application load simulation, and performance tuning.
  
• Incident management and application functionality sustainment.
  
• Business intelligence, data analytics, data warehouse design, and data modelling.
  
• End-to-end SDLC experience using Agile/Scrum and Waterfall methodologies.
  
• Ensuring compliance with architecture frameworks, system standards, security, privacy, and development methodologies.
  

Public Sector Experience – Preferred
Candidates with the following experience will be considered an asset:

• Experience working on inter-jurisdictional projects involving federal, provincial, or municipal governments.
  
• Knowledge of provincial environmental services and related legislative frameworks.
  
• Experience with data migration and integration across environmental systems.
  
• Knowledge of OPS Enterprise Architecture artifacts, processes, and practices.
  
• Experience producing technical documentation aligned with industry and enterprise architecture standards.
  
• Experience working within or adhering to:
  
  • OPS Unified I&IT Project Methodology
    
  • OPS Enterprise Architecture
    
  • OPS Gating Process
    
  • OPS Standard Systems Development Methodologies.
    

General Skills & Competencies
The ideal candidate will demonstrate:

• Strong analytical, problem-solving, troubleshooting, and decision-making abilities.
  
• Excellent verbal and written communication skills.
  
• Strong interpersonal, consulting, negotiation, and stakeholder management skills.
  
• Ability to gather and document requirements from business stakeholders and subject matter experts.
  
• Experience investigating data issues within ETL pipelines and recommending effective solutions.
  
• Strong documentation skills.
  
• Ability to work collaboratively across technical and business teams.
  
• Strong listening and issue-resolution skills.
  
• Excellent customer service skills, tact, and diplomacy.
  
• Ability to manage multiple priorities and meet strict deadlines.
  
• A highly motivated, flexible, detail-oriented, and collaborative approach.
  

Mandatory Candidate Requirements
Please submit only candidates who clearly demonstrate the following mandatory experience:

1. 5+ years of ETL and data warehousing experience.
   
2. Hands-on experience with Informatica IDMC / Informatica Cloud.
   
3. Hands-on experience with Informatica PowerCenter v10.2.
   
4. Experience migrating ETL code, embedded SQL, and PL/SQL from Oracle 11g and/or Oracle 19c environments.
   
5. Strong experience testing migrated SQL and PL/SQL stored procedures.
   
6. Advanced SQL, PL/SQL, and performance tuning experience.
   
7. Experience developing ETL solutions for star and snowflake data warehouse schemas.
   
8. Experience with Azure SQL Server and Azure Data Factory, including pipeline development.
   
9. Strong experience with Oracle, SQL Server, and other relational database technologies.
   
10. Experience with Python and Unix Shell scripting.
    
11. Experience with ETL troubleshooting, root cause analysis, recovery, and performance optimization.
    
12. Experience translating technical specifications into fully tested applications and data pipelines.
    
13. Ability to work in a hybrid environment with a mandatory 3 days onsite per week in Toronto.