Informatica Architect

The NYWIC (New York Women, Infants, and Children) system is a statewide application that supports the administration of New York's WIC nutrition program, which provides nutritional assistance and health services to eligible women, infants, and children. The Division of Nutrition relies on custom applications to support the delivery of these programs. The NYWIC MIS-EBT project provides a .NET/Oracle-based platform that enables efficient program operations and ongoing enhancements to meet evolving business needs. Within this environment, the DataMart team is responsible for the design, development, and ongoing maintenance of the enterprise data warehouse and reporting layer supporting the NYWIC application.

About the role

This is primarily an ETL Developer position focused on design and development of complex mappings, and integration of data from multiple heterogeneous source systems. The resource will also serve as backup support for OBIEE and data modeling processes, assisting with troubleshooting, production support, and issue resolution as needed.

Responsibilities

• Perform data analysis of current and future WIC systems, including system comparison, to support ongoing and future data needs.
• Collaborate with customers, vendors, and technical teams to gather requirements, perform gap analysis, and prioritize reporting needs.
• Design and maintain enterprise data architecture, including conceptual, logical, and physical data models, while performing ongoing data modeling and schema design.
• Define and enforce data standards, naming conventions, governance practices, and ensure data quality, integrity, and consistency across all environments.
• Develop and maintain documentation of business processes, workflows, and data flows using modeling techniques and flowcharts.
• Develop, review, and optimize ETL processes and mappings for data migration (Sybase, MS Access to Oracle), improving performance and efficiency.
• Design and support reporting solutions, including dashboards and prototypes, aligned with end-user requirements.
• Develop and execute test plans, scripts, and validation scenarios to ensure data accuracy and compliance with business requirements.
• Monitor and optimize database performance through query tuning, indexing, partitioning, and overall system optimization.
• Implement and maintain database security, access controls, auditing, and compliance with organizational and state data privacy requirements.
• Evaluate implementation strategies, identify risks and issues, and support resolution and mitigation planning.
• Provide technical guidance, conduct design reviews, mentor team members, and support release planning and post-implementation reviews.
• Facilitate and document meetings, support reporting development discussions, and ensure proper tracking of outcomes.
• Perform administrative duties including attendance at meetings and completion of NYS LATS timecard and monthly timesheets.

Requirements

• 15+ years of experience determining database structural requirements by analyzing client operations, applications, and programming; reviewing objectives with clients; evaluating current systems; and providing database support by coding utilities, responding to user questions, and resolving problems.
• 84 months of experience using Informatica 10.x or higher in enterprise data warehouse environments, including advanced warehousing concepts, design and development of mappings, and integration of data from multiple heterogeneous source systems such as Oracle databases and flat files.
• 72 months of experience leading ETL teams and designing enterprise data integration architecture using Informatica, including establishing data standards, best practices, and governance policies for data warehouse and reporting environments, and mentoring team members.
• 72 months of experience implementing enterprise database security and data protection controls, including Fine Grained Access Control Audit (FGACA) and Virtual Private Database (VPD), ensuring compliance with HIPAA and protection of sensitive healthcare data including Medicaid and Pharmacy information.
• 72 months of experience working with Oracle databases, including translating large SQL, PL/SQL (stored procedures/functions), and SAS code into optimized Informatica mappings and performing query optimization and performance tuning for data warehouse systems.
• 72 months of experience developing complex UNIX shell scripting solutions to automate file validation, secure file transfers (FTP/SFTP), organizing batch jobs, and integration with ETL workflows, ensuring consistent, repeatable, and error-free data processing.
• 72 months of experience implementing Informatica Data Quality (IDQ) solutions, including data profiling, data cleansing, standardization, matching, and merging, along with defining business rules, metrics, and processes to ensure accurate and reliable data for reporting and analytics.
• 48 months of experience designing and maintaining dimensional and enterprise data models for Datamart and data warehouses, including conceptual, logical, and physical modeling using tools such as Erwin, ensuring optimal storage, query performance, and alignment with business requirements.
• 36 months of experience specifying and conducting system testing (unit, integration, regression, load, performance, and UAT) and supporting code migration and releases across DEV, QA, and PROD environments.
• 24 months of experience designing and implementing enterprise OBIEE dashboards and reports, including repository (RPD) development, metadata modeling, and performance tuning for high-volume Datamart data.
• 24 months of experience establishing and enforcing data governance, lineage, and quality frameworks across enterprise Datamart and reporting environments, collaborating with Program team to ensure data integrity, compliance, and actionable reporting.
• Bachelor's Degree in IT field.
• Certification in Informatica.
• Copy of Candidate Identification (e.g., Driver's License/Green Card/Visa and Passport if applicable).
• Any documents referenced in the qualifications (e.g., professional certifications, degrees).

Schedule

This is a 100% onsite position.