ETL Architect - Informatica

About the Role :

We are seeking an ETL Architect with deep Informatica expertise to lead the integration and middleware retrofit on an enterprise asset management modernization program for an energy client.

As the client's asset management platform moves to a vendor-managed SaaS instance and its legacy reporting platform retires, the role owner will design and lead the re-engineering of Informatica pipelines, the SaaS table replication and the retrofit of middleware integrations, so that operational reporting continues without disruption.

Architecture & solution ownership :

- Own the target-state integration architecture for retrofitting Informatica pipelines and middleware integrations after the source platform moves to a vendor-managed SaaS instance.

- Define reusable mapping patterns, templates and coding standards, and review design and code across the delivery team.

Middleware Integration Retrofit :

- Retrofit outbound interfaces and re-validate outbound touchpoints to downstream systems.

- Design and rebuild inbound interfaces through an alternative write-back mechanism where direct write-back to SaaS tables is not available, confirming the approach with client IT and the SaaS vendor early in the program.

SaaS Table Replication :

- Design and validate replication of SaaS tables into the reporting layer using AWS DMS, including the read approach and which customizations carry forward.

- Run targeted validation of tables with structural change before build begins.

Informatica Workflow Re-engineering :

- Lead the re-engineering of Informatica workflows and mappings (source to staging, staging to target) and repoint them to the replicated SaaS tables.

- Tune high-complexity workflows involving complex joins, update strategy, multiple target loads and workflow dependencies.

Schema & SQL Remediation :

- Rebuild the internal integration schema built on the current source-system tables.

- Guide the remediation of dependent SQL reports and stored procedures.

Scheduling & Dependency Management :

- Realign scheduled loads, job schedules and workflow dependencies on the enterprise job scheduler (JAMS).

- Maintain the data lineage and dependency register, and freeze integration contracts early.

Testing & Validation :

- Lead system integration testing (SIT) for retrofitted pipelines and integrations.

- Drive legacy versus new data reconciliation and parallel runs.

- Support user acceptance testing (UAT) and plan the cutover runbook.

Reporting & Governance :

- Provide weekly status on progress, milestones, risks and escalations.

- Participate in daily standups, sprint ceremonies and steering reviews, and guide data engineers on the team.

Qualifications :

- Bachelor's degree in computer science, engineering, or related field (master's degree is a plus).

- Demonstrated continued learning through one or more technical certifications or related methods.

- At least 10 years of relevant experience in data integration, including 4 or more years as an ETL or integration architect.

Required Skills :

- Informatica PowerCenter and/or IDMC : mappings, sessions, workflows, worklets and parameterization.

- Informatica transformations (Expression, Filter, Router, Lookup, Joiner, Aggregator, Update Strategy) and multi-target loads.

- Performance tuning of high-volume, high-complexity ETL workflows (pushdown optimization, partitioning, lookup caching).

- Re-engineering existing ETL after a source-system change, such as an on-premises application moving to SaaS or cloud.

- Integration and middleware design : inbound and outbound interfaces, and alternatives where direct write-back is not available.

- Data replication and change data capture using AWS DMS or an equivalent tool.

- Advanced SQL, stored procedures and schema design on enterprise relational databases.

- Data lineage, dependency mapping and impact analysis.

- SIT, data reconciliation, parallel runs and cutover planning.

- Enterprise job schedulers (JAMS, Control-M or similar).

- Exceptional communication skills for engaging client IT leads, business SMEs and vendor teams.

Preferred Skills :

- Experience with enterprise asset management (EAM) platforms such as Hitachi Energy Asset Suite, IBM Maximo or SAP PM.

- Energy, utilities or power-generation domain exposure, especially supply chain and maintenance reporting.

- AWS data services beyond DMS (S3, RDS, Glue).

- Familiarity with BI platforms such as MicroStrategy, Power BI or Amazon QuickSight.

- Exposure to migration accelerators or automation frameworks for ETL conversion and validation.

- Certifications in Informatica or AWS.

- Prior consulting experience on large-scale data migration or modernization programs.