Data Etl

SUMMARY

Certified Informatica, Oracle and Teradata/PL-SQL IT professional with 10+ years of technical and 4+ managerial experience (4+ years with Fortune 500 Company) in Data Analysis, Informatica Power Center 9.x/8.x, 10 Hot_fix2, Informatica IDQ, Oracle, Teradata, Data Warehousing, Big Data (Pig, Hive, HDFS), UNIX shell and Python scripting, and Tableau reporting.

Involved in development of Informatica mappings with required transformations like (Source Qualifier, Rank, Joiner, Lookup, Filter, Router, Sequence Generator, Union, Sorter, Update Strategy, Aggregator, and Expression)

Demonstrated experience with design and implementation of Informatica (IDQ v9.1), Data Quality applications for the business and technology users across the entire full development life-cycle.

Excellent understanding of Ralph Kimball Dimensional Modeling using Star schema/ Snow Flake schema methodology.

Designing data models (including SCD Type1, Type 2, Type3 dimensions) using ERWIN and implementation of database on Teradata RDBMS.

Strong experience in Extraction, Transformation, Loading (ETL) data from various heterogeneous source systems like Oracle 11g/10g, Teradata, SQL Server 2008/2005, DB2 10, Flat files, XML into Data Warehouses and Data Marts using Informatica Power Center (Designer, Workflow Manager, Workflow Monitor, Metadata Manger).

Expert in creating and using Mapplets, Worklets, and Advance Transformation like SQL, Transaction control, Normalizer, Java, HTTP, Web Services, and Sales force.

Expert in creating and using Stored Procedures, Functions, Triggers, Views, Stubs in Teradata 15 and Oracle 11g/10g/9i.

Extensively worked on Teradata Utilities (Fast Load, MLoad, TPT and Fast Export).

Demonstrated experience with design and creation of Tableau Dashboard reports for Data Analysis and reporting purposes.

Demonstrated experience and designing of ETL ingestion/cleansing flows using Python.

Good understanding of Azure cloud infrastructure.

As an onsite lead responsible to coordinate requirements and assigning development work and guide the team during Development and Unit testing Phases to implement logic and troubleshoot the issue that they were experiencing.

Responsible for projects estimates, Project Plan, risk mitigation plan, design documents, Best Practices Document for Informatica, Oracle and Teradata.

Experience in setting up of IBM MQ queues for extracting messages used for reconciliation processes.

Extensive experience in Large Data Warehouses (working on Large volume of data sets)

Worked on both waterfall model as well as agile methodology projects.

Demonstrated experience in designing data marts for finance domain.

Extensive experience in parallel data processing through ETL tools, PL/SQL and UNIX Makefiles.

Extensive experience in Banking/Finance (Wealth Management) and Healthcare (Managed Markets) domain.

A highly motivated self-starter and a good team-player with excellent verbal and written communication skills.

Experience in leading team and possess excellent communication and interpersonal skills, ability to quickly grasp new concepts, both technical and business related and utilize as needed.

EDUCATION:

Bachelors in Electronics and Communication Engineering from Punjab Technical University, India.

Diploma in Electronics and Communication Engineering from Thapar University, India.

PROFESSIONAL EXPERIENCE:

Working as an Application Architect in Morgan Stanley from December 2015 till date

Worked as a Lead ETL/Database Developer in Eisai Pharma, Merck Pharma and JP Morgan chase (Employer: Cognizant Technology Solutions) from Jan 2010 to Dec 2015.

TECHNICAL SKILLS:

ETL Tools: Informatica power center 9.x/8.x, Informatica IDQ, SSIS

MDM Tools: Informatica MDM

Reporting Tools: Tableau

Databases: Teradata 15, Oracle 11g/10g, SQL Server 2000/2005,

Languages: SQL, PL/SQL, Shell Scripting, Python

Operating Systems: UNIX, Linux, Windows 95/98/NT/2000/XP and DOS

DB Tools: Teradata SQL Assistant/Power Studio, SQL Plus, SQL Loader, Toad, Erwin

Scheduling Tools: Autosys, Control-M, TWS

Methodologies: Agile and waterfall.

Utilities: Teradata Mload, Fload, TPT

Cloud: Azure

Certifications:

Informatica Power Centre 8.x Designer Certification

Oracle 11g PL/SQL fundamental certification

Teradata 14.0 basics (TE0-141 Teradata 14 Basics) and SQL (TE0-142 Teradata 14 SQL) certified.

EXPERIENCE:-

Present Employer: Morgan Stanley

Wealth Management Division (Retail Statements) NY Dec 2015 till date

Role- Associate/Application Architect

The legacy client statements delivery process was based on a collection of background

Mainframe batch processes that generate the various client statements. These statements are then

delivered to various internal GWM systems and to a vendor BFS who prints and mails the

statements to Morgan Stanley clients. However, because statements generation is based on

various ad-hoc mainframe processes the current system is error prone and difficult to

manage. To end that the GWM Client Reporting team are building a new system to deliver

more consistent reports with higher data quality. This is part of the Client Reporting

Renovation program, which includes the Statements Renovation initiative.

At a high level the new system (Open Statements) is comprised of ETL (Informatica)

extraction adapters that pull data from existing data sources – the bulk of the data will be

sourced from the Enterprise Data Warehouse (Teradata). Any errors are corrected or flagged before the ETL (Informatica) transformation adapters prepare the data for delivery to various internal systems such as On Demand, Accounts On-Line and E-Delivery and the existing print and mail vendor BFS.

The Wealth management businesses also has a dedicated Merchant analytics management business designed to meet the needs of high net worth individuals. This Solution is concerned with the development of a cost-effective Data Warehouse using Hadoop and Hive for storage of large amount of historical data and log data. The raw data will be coming from various sources and dumped directly into Hadoop file system through Sqoop (data extracting tool used to extract data from RDBMS (Oracle, Db2, Teradata, etc.). Then, data is processed (like un-normalization, partitioning, bucketing, etc.) using hive queries. After that, the data is updated (using customized and optimized queries) into hive and ad-hoc queries can be run to get any form of data.

Environment: PL/SQL, Informatica PowerCenter9.x, Teradata, SQL Server, Flat Files/Make files and UNIX Shell/Pearl Scripting, Tableau reporting, IBM MQs.

Responsibilities:

Lead and guide development of an Informatica/Teradata based ETL architecture. Develop solution in highly demanding environment and provide hands on guidance to other team members.

Head complex ETL requirements and design. Implement an Informatica/Teradata based ETL solution fulfilling stringent performance requirements.

Lead and guide database implementation including Enterprise Data warehouse on Teradata RDBMS

Designing logical and physical data models/marts.

Designing data models (including SCD Type1, Type 2, Type3 dimensions) using ERWIN and implementation of database on Teradata RDBMS.

Collaborate with product development teams and senior designers to develop architectural requirements to ensure business satisfaction with product.

Act as a subject matter expert for stakeholders to depend upon.

Lead the team to make Statements application in compliance with Agile SDLC standards.

Act as a scrum master for sprint planning, effort estimation and drive the daily/weekly scrum calls to gather status information on various Epics/Jira’s and resolve any pending issue.

Assess requirements for completeness and accuracy. Determine if requirements are actionable for ETL team.

Extensively work on Informatica Mappings, Sessions, Workflows and UNIX Shell scripts to provide end to end solution to ID Recertification process.

Extensively work in writing and creating complex Teradata Stored Procedures/PL-SQL.

Work closely with DBAs for code deployments, indexing requirements and performance tuning on tables.

Analyzing and guiding team in Performance/Query tuning, Generation/interpretation of explain plans and tuning SQL to improve performance.

Involved in writing UNIX shell scripts to run and schedule batch jobs.

Working on JSON/CSV files for consumption using ETL pipelines built using Python scripting.

Involved in unit testing and documentation of the ETL process.

Extensively involved in creating Mload/Fload scripts for data loading into Teradata RDBMS.

Extensively work on Release Management with source control software such as Git/Stash/ TeamCity/Apache Subversion

Develop Tableau dashboard reports for the live run time data analysis of Statement Application.

Working on scheduling tools TWS/Autosys for setting up of application jobs.

Extensively involved in coordinating with Morgan Stanley print vendor company by working closely and guiding developers there to deliver Morgan Stanley enhancements.

Working closely on the analysis and design of migrating Statements Application onto Azure private cloud.

Employer: Cognizant Technology Solutions

Eisai Pharmaceuticals, NJ Dec 2014 to Dec 2015

Informatica and Teradata database Consultant/ Application Architect

The MC CLM initiative is spearheaded to come up with an automated solution within EDW to overcome Eisai’s

current challenges of cross marketing channel analysis, HCP integration with Rx data faced by Brand teams.

This system allows users to view consolidated data from various sources & provides a better ability to make

business decisions. Merged existing data for marketing channels with the data from new vendors.

The data from MC CLM supports BI dashboards to report data for ROI analysis by analyzing various KPIs. MCCIM

Project where in data is gathered from multiple sources which include finance, GPS – Sales tracking, Retail Sales

and Forecast data which is further used for reporting to create Demand Forecast Accuracy, Net Sales, Operating

Income, Market Shares and Evolution Index KPIs.

The system loads all sources like files, SQL Server, Oracle, SAP and Corp BW data into individual stage tables in

Teradata with minimum business logic as part of staging load. During data warehouse load data from stage tables

get integrated and loaded into dimensions and fact tables.

The system provides extract files from EDW and send to downstream system PiLM for analysis.

Environment: Informatica PowerCenter9.x, Teradata, Flat Files and UNIX Shell Scripting, BIG Data technologies (Pig, Hive, HDFS).

Responsibilities:

Provided Architectural Road Map, direction and work packets for ETL needs.

Created detail ETL Standards documents for Design, Development, Release Management and Production Support.

Design Detail ETL spec for offshore development and ensured Quality ETL Deliverables.

Created detail ETL Migration Processes for Informatica, Database, Scheduling, O/S and H/W teams.

Design and Develop Reusable Common objects shared across various Repositories.

Automated, Redesigned and tuned several ETL Process for optimal utilization of Time and resources.

Trouble Shoot, Tune and Maintain complex ETL Mappings.

Created several Repository Mappings and Queries facilitating rapid analysis, trouble shooting, code verification and deployment.

Developed Informatica workflows/worklets/sessions associated with the mappings across various sources like XML, COBOL, flat files, Webservices, Salesforce.

Designing/Developing and Implementing detail layout of ETL testing plan procedures.

Worked with cleanse, parse, standardization, validation, scorecard transformations.

Worked with transformations Source Qualifier, Update Strategy, XML transformation, SQL Transformation, Webservices, Java transformation, Lookup Connected and Unconnected .

Worked with Push down optimization and Partition to improve performance through Informatica.

Designing data models (including SCD Type1, Type 2, Type3 dimensions) using ERWIN and implementation of database on Teradata RDBMS.

Performance/Query tuning, Generation/interpretation of explain plans and tuning SQL to improve performance.

Extensively work in writing and creating complex Teradata Stored Procedures/PL-SQL.

Extensively involved in creating TD Mload/Fload scripts for data loading into Teradata RDBMS.

Involved in writing UNIX shell scripts to run and schedule batch jobs.

Involved in creating data lakes on Hadoop file system.

Provide estimations for ETL deliverables and oversee the progress for quality ETL Deliverables.

Extensively work on Release Management with source control software such as TeamCity/Apache Subversion/Git/Stash

Working on scheduling tools TWS/Autosys for setting up of application jobs.

Employer name: Cognizant Technology Solutions June 2011 – Dec 2014

Merck Pharmaceuticals (India, Pune)

Database Developer

A Merck business initiative to replace all Managed Care applications with standardized solutions from a vendor-provided application suite.Align is the Managed Market domain application. A Merck business initiative to replace all Managed Care applications with standardized solutions from a vendor-provided application suite.

The purpose of Align is to build a Managed Market Warehouse that obtains data from contributing systems (ModelN, legacy systems and master data sources) and transforms and loads that data to support reporting and analytics i.e. To obtain data from ModelN in the form of XML files and to transform and load that data into Managed Market Warehouse to support downstream consumption.

Environment: Informatica PowerCenter9.x, Oracle, DB2, Flat Files and UNIX Shell Scripting.

Responsibilities:

Created detail ETL Standards documents for Design, Development, Release Management and Production Support.

Design Detail ETL spec for offshore development and ensured Quality ETL Deliverables.

Created detail ETL Migration Processes for Informatica, Database, Scheduling, O/S and H/W teams.

Design and Develop Reusable Common objects shared across various Repositories.

Automated, Redesigned and tuned several ETL Process for optimal utilization of Time and resources.

Trouble Shoot, Tune and Maintain complex ETL Mappings.

Design and develop and Implement detail layout of ETL testing plan procedures.

Provide estimations for ETL deliverables and oversee the progress for quality ETL Deliverables.

Created several Repository Mappings and Queries facilitating rapid analysis, trouble shooting, code verification and deployment.

Developed Informatica workflows/worklets/sessions associated with the mappings across various sources like XML, COBOL, flat files, Webservices, Salesforce.

Worked with SCD Type1, Type 2, Type3 to maintain history in Dimension tables.

Worked with cleanse, parse, standardization, validation, scorecard transformations.

Worked with transformations Source Qualifier, Update Strategy, XML transformation, SQL Transformation, Webservices, Java transformation, Lookup Connected and Unconnected.

Extensively work in writing and creating complex Teradata Stored Procedures/PL-SQL.

Extensively involved in creating TD Mload/Fload scripts for data loading into Teradata RDBMS.

Involved in writing UNIX shell scripts to run and schedule batch jobs.

Worked with Push down optimization and Partition to improve performance through Informatica.

Employer: Cognizant Technology Solutions Jan 2010 – June 2011

JP Morgan chase

Informatica/Database Developer - (India, Pune)

"Information One" is the Operational Data Store for Retail Lending applications.

Information One loads data from 100+ systems on a daily and monthly basis into Oracle database. It provides data for management reporting purposes and feeds to downstream and upstream systems.

Supports and creates client/server and web-based applications. Because Information One is linked to many research/development tools, a variety of reports and interfaces may be developed.

Environment: Informatica PowerCenter 9.x, Oracle, PL/SQL, Toad 8.0, UNIX Shell Scripting.

Responsibilities:

Support of existing JP Morgan chase retail lending application processes.

Developed Informatica workflows/worklets/sessions associated with the mappings across various sources like XML, COBOL, flat files, Webservices, Salesforce.

Worked with SCD Type1, Type 2, Type3 to maintain history in Dimension tables.

Worked with cleanse, parse, standardization, validation, scorecard transformations.

Worked with transformations Source Qualifier, Update Strategy, XML transformation, SQL Transformation, Webservices, Java transformation, Lookup Connected and Unconnected.

Contact this candidate