Our client in Acton, Massachusetts is seeking a Senior ETL Modernization Engineer to lead the re-platforming of an existing SSIS-based pipeline suite onto ODI with ADW as the target. This includes building new integrations (Apptio API, Micro Focus), migrating stored procedures, automating outbound SFTP feeds, supporting SIT/UAT, and delivering knowledge transfer to the client’s development team. This is a remote role, but you must reside within the continental United States.
Key Responsibilities
- Re-platform SSIS ? ODI: Redesign and deliver ~55 ODI mappings for inbound, intermediate, and outbound flows targeting ADW.
- Integrations: Automate Apptio extracts (via REST APIs) and stage Micro Focus time sheet data for daily incremental loads.
- Database Programming: Rebuild and create PL/SQL procedures, implement ADW views, and ensure performance and scalability.
- Outbound Interfaces: Deliver ~9 daily outbound files securely via OCI SFTP with parameterization and monitoring.
- Testing & Cutover: Lead SIT, support UAT, and coordinate production migration with concise documentation and run-books.
- Documentation & Training: Produce technical designs and deliver targeted training to client developers (~10 participants).
Required Qualifications
- 5–8+ years building enterprise ETL/ELT solutions on ODI with Oracle/ADW targets.
- Hands-on SSIS ? ODI migration experience (mapping redesign, orchestration, dependencies).
- Advanced PL/SQL development (procedures, packages, tuning on ADW).
- Proven patterns for daily incremental loads (change data capture, restartability, late-arriving data).
- API integration experience (OAuth, pagination, retries), ideally with Apptio APIs.
- SFTP automation expertise (secure keys, error handling, verification).
- Strong data validation and reconciliation practices; SIT/UAT support.
- Git-based CI/CD pipelines and environment promotion experience.
Nice-to-Have
- Familiarity with Micro Focus time sheet data.
- Experience with OCI services (Object Storage, secrets, networking).
- ADW performance tuning (partitioning, stats, external tables).
- Knowledge of downstream iHub file formats/naming conventions.
- Prior large enterprise/regulatory environment experience.
Success Milestones (First 90 Days)
- Week 2–3: Technical design approved; API connectivity validated; ADW schemas confirmed.
- Week 4–6: First ODI mappings delivered; core PL/SQL procedures operational in DEV.
- Week 7–8: End-to-end incremental loads operational (Apptio/Micro Focus ? ADW); SFTP deliveries verified.
- Week 9–12: Full migration complete; SIT executed; UAT supported; production migration plan finalized.
- Go-Live: Production cutover executed, run-book delivered, training completed.
Tools & Environment
ODI (Non-Prod/Prod), ADW, OCI SFTP, SQL Server (source reference), Apptio APIs, Git/DevOps, standard ticketing & change control.