Consultant, Product Architect

Nationwide Mutual Insurance•Columbus, OH

5d•Hybrid

About The Position

The Product Architect designs and architects end-to-end enterprise data integration and data-warehousing solutions, translating business requirements into current-state and target-state architectures and ETL patterns for enterprise analytics. The role uses data modeling to design, review, and refine logical, physical, and dimensional data models for the data warehouse, including star and snowflake schemas that support efficient aggregations, consistent business hierarchies, and scalable reporting. The position develops and supports ETL and data integration pipelines using Informatica PowerExchange and Oracle PL/SQL to extract, transform, validate, and load data across source, staging, warehouse, and data mart environments. It also develops shell scripts for Unix environments and supports AWS-based cloud environments to improve infrastructure creation and efficiency. The role optimizes ETL and database performance across Informatica mappings, SQL queries and stored procedures, and Teradata BTEQ scripts in MPP environments. The position is hybrid in a normal office environment, and some travel may be required. Overtime eligibility is exempt (not eligible).

Requirements

Master’s degree in Computer Science or Information Technology.
Three years of experience performing the listed duties.
Experience with Informatica PowerExchange and Oracle PL/SQL for ETL and data integration.
Experience with data modeling, including logical, physical, dimensional, star, and snowflake schemas.
Experience developing shell scripts for Unix environments and supporting AWS-based cloud environments.
Experience optimizing Informatica mappings, SQL queries and stored procedures, and Teradata BTEQ scripts in MPP environments.

Responsibilities

Design and architect end-to-end enterprise data integration and data-warehousing solutions.
Translate business requirements into current-state and target-state architectures and ETL patterns for enterprise analytics.
Design, review, and refine logical, physical, and dimensional data models for the data warehouse, including star and snowflake schemas.
Develop and support ETL and data integration pipelines using Informatica PowerExchange and Oracle PL/SQL to extract, transform, validate, and load data across source, staging, warehouse, and data mart environments.
Develop shell scripts to manage Unix environments and support AWS-based cloud environments.
Tune Informatica mappings through partitioning and pushdown optimization.
Optimize SQL queries and stored procedures through execution-plan analysis, indexing, and partitioning.
Optimize Teradata BTEQ scripts in MPP environments through statistics collection, spool-space optimization, and workload distribution.
Improve throughput, scalability, and resource utilization.
Regularly and consistently demonstrate Nationwide Values.

Benefits

Medical, dental, and vision coverage.
Life insurance.
Short- and long-term disability coverage.
Paid time off, including a minimum of 18 days per full calendar year for newly hired associates, prorated quarterly based on hire date.
Nine paid holidays.
Eight hours of Lifetime paid time off.
Eight hours of Unity Day paid time off.
401(k) with company match.
Company-paid pension plan.
Business casual attire.