SUMMARY

• Overall 8 years of experience in Informatica Power Center, Data Warehouse, Data modeling and responsible for Data Extraction, data transformation and data loading from multiple sources into the data warehouse.
• Experience in understanding SDLC (Software Development Life Cycle), analyzing business requirements, design and development, Data Modeling, Implementation and testing of Data warehousing.
• Experience with Erwin, strong knowledge of Relational database modeling and Dimensional database modeling concepts, star/snowflake schemas implementation, good knowledge on OLTP and OLAP systems and expertise in integrating various source’s - Database, XML and Flat files.
• Designing Star schemas & Snowflake Schemas for OLAP Systems and extensive understanding of Transactional and Dimensional Data Modeling concepts.
• Work experience has given me the opportunity to acquire an in-depth knowledge of the Auto Insurance, Healthcare, Banking and Tax domains, implementation techniques, system development as well as building customer relationship skills.
• Expertise in ETL methodology in a corporate wide ETL Solution using Informatica Power Center 10.2/10.1/9.6/9.1/8.6/8.1/7.1 , Informatica MDM 9.5, IDQ, Oracle 11g/10g applications, Teradata, SQL Server, SQL and PL/SQL.
• Experience working with MDM, creating mappings, trust and validation rules,match rules, loading into landing tables of MDM, used hierarchy tool to manage HM in MDM HUB implementation.
• Extensive experience in developing mapping transformations, workflows, worklets, mappings, Mapplets, Sessions, Tasks, schedule Workflows and sessions, slowly changing Dimensions and CDC implementation..
• Proficient in Implementation of Data warehouse from Requirement gathering, Data Analysis, data modeling, design and development, data cleansing, and help create test plan and write test scenarios for data validation.
• Good experience in Teradata using queries and utilities like BTEQ, MLOAD, FLOAD and TPUMP and Fast Export.
• Worked on Effort Estimation, coordination with various teams, worked with Clients, Users, Onsite team and offshore team and time management in line with the deadlines.
• Good experience in Debugging and performance tuning of sources and SQL performance tuning, targets, mappings, transformations and sessions.
• Experience in Task Automation using UNIX Shell Scripts, Job scheduling and communicating with Server using pmcmd to schedule jobs through Autosys Scheduler.
• In depth knowledge in Azure Data Factory, Synapse Analytics learned this skill from client training and got hands on by practising for some Clients use cases.
• Excellent analytical, problem solving and communication skills with ability to interact with individuals at all levels.

TECHNICAL SKILLS

Data Modeling Tool: Erwin 7.1.3/6.0

Data Warehouse/ETL Tool: Informatica Power Center 10.2,10.1, 9.6/9.1/8.6/7.1 (PC and IDQ).

Databases: Oracle 11g,10g,9i/8i/7.x, Teradata 15/14/13, SQL Server

Languages: SQL, UNIX shell scripting,PL/SQL, C, C++.

Web Tools: HTML, XML.

Tools: Oracle SQL Developer 19.2, DB Visualizer 10.0.16, Teradata SQL Assistant 15, SQL Server Management Studio (SQL Server 2012), TOAD, SQL plus, Data Ladder, Tableau.

Operating Systems: Microsoft 95/98/2000/XP/NT, UNIX, Linux

PROFESSIONAL EXPERIENCE

Confidential

ETL Developer

Responsibilities:

• Analyze business requirements, interact with BSA to understand transformation and accounting rules, schedule calls to discuss open questions.
• Perform data profiling on Prod data, develop source queries and share data extracts for data review and Source query sign off from BSA.
• Interact with Source Atlas team to check on data ingestion schedule for availability of data in GP DW tables.
• Work with the DEV team to design ETL flow from source to target, implement CDC logic to capture source changes and SCD type 2 for target loads.
• Develop mappings with mapping parameters and variables defined for variable assignment, create workflows with email and command tasks for calling shell scripts.
• Create Trans, Stage and Warehouse tables as per LFG naming standards and promote to higher environments.
• Work with DBA to grant permissions for database objects/table access. Request DBA for Dev Proxy access.
• Work with Informatica admins for folder access, Unix server access for AD Groups, folder access on NAS location to place outbound files.
• Create param files for parameterizing table, schema, and connection objects.
• Implemented Batch Audit table logic for incremental loading.
• Migrate code to higher environment using ACM tool.
• Collaborate with Autosys admin to create box jobs, job schedules and job dependencies.
• Create design, mapping and load plan document for BC Daily project for future support.
• Participate in scrum calls for status updates and discuss roadblocks with the team for resolution.
• Troubleshoot loading failures and debug mapping to fix data/code issues.
• Generate daily Control reports with Out of balance entries, and SIF file with forced balance GL entries. These files are placed in DI Hub to feed People soft.

Environment: Informatica Power Center 10.2v, Oracle, Flat File, Unix Shell Scripting, Autosys.

Confidential

ETL Developer

Responsibilities:

• Analyze business requirement, interact with BA to understand value of ETL process
• Create Mapping document for data flow design
• Understand data model, identify sources and analyze source data to design ETL logic
• Integration of data from Flat files and tables into staging area to Enterprise DWH
• Create Mappings using various transformations to load data to Dimension and Fact tables
• Used Shortcuts to re-import and re-map source definitions in Mapping to inherit changes automatically
• Create Mapping Parameters and Variables to support ETL process, create parameter files and scripts for purging, copying files etc.
• Loaded currently processed file names to the output files as per Outbound team requirement
• Create batches for incremental loading and schedule them through Autosys scheduler
• Implemented SCD type 1 and type 2 to update Dimension tables
• Create triggers on Source database to read only changes for Incremental aggregation
• Write PMCMD commands for job scheduling and for SFTP of files from Remote to Informatica Server.
• Used MDM for validation, exporting repositories from DEV to TEST.
• Identified duplicate records for customer data coming from different sources using MDM.
• Created validation and match rules for fetching right master records using MDM.
• Used hierarchy tool to define Foreign key relationship, entity types, profiles as subject areas in IDD.
• Created subject area groups and IDD display packages in HUB.
• Write Shell scripts to process XML files for Benefit Focus (Customer) file process.
• Interact with Admin team to configure Database connections, folder permissions, create NAS volume for shared files
• Resolve on going maintenance issues and bug fixes on priority like code page compatibility between Informatica 10.1 and 10.2 versions
• Created Unit test documentation with all possible test cases and test scripts for data validation
• Migrate code to QA and support testing team with Integration and Regression testing efforts
• Debug mapping to gain trouble shooting information about data and error conditions
• Monitor jobs and work on failed jobs associated with Incident tickets as per SLA
• Prepare Migration Log and checklist to deploy Informatica objects to Prod

Environment: Informatica Power Center 10.2v, MDM 9.5, Sybase, SQL Server, Shell Scripting.

Confidential

ETL Developer

Responsibilities:

• Involved in gathering requirements from client through JIRA board on daily basis.
• Analyze requirement and Database object involved in Data migration.
• Migrate and integrate data from multiple source systems like salesforce, oracle then transform and load into PA Teradata data warehouse which is used by downstream reporting team.
• Understand business specification and create 490 Mapping and design document.
• Develop ETL mappings implementing business rules provided by business using transformations like Expression, Lookup, Joiner, Update strategy. Create Mapplets for re-usability of logic.
• Create Incremental load process using SCD type 2 technique. Developed partitions for large volume data.
• Performed data cleanse using cleanse function in MDM.
• Responsible for loading customer data set into MDM from various source systems.
• Implement mapping, create sessions, worklets and workflows in DEV.
• Debug existing mapping to resolve incident tickets created by business.
• Schedule jobs using scheduler. Testing in Dev and Test with test data.
• Involved in Rewriting Informatica mapping logic on Teradata by implementing Database Views. Consolidate multiple views of a business line into a single view which is pulled as source in mapping to improve performance.
• Dump XML to gather mapping derivations and rewrite into SQL code as per Confidential standards. Used source PDO on Teradata and worked with different Teradata load operators.
• Write minus queries to test data between Teradata View and ETL Mapping targets.
• Create parameter files for each business line like Auto, Workers comp, Property etc.
• Check session and workflow logs for errors during execution. Deploy code to higher environments UAT and Prod as per guidelines.
• Participate in Agile scrum discussing blockers for the day, dependencies on task, status updates, release planning, update JIRA Kanban board tasks.

Environment: Informatica Power Center 9.6, MDM 9 .5, Teradata 15.0, Oracle 11g, Erwin Data modeler.

Confidential - Columbus, OH

ETL Developer

Responsibilities:

• Involved in gathering requirements from business users on day to day basis.
• Worked on analyzing systems data, stored on an enterprise data warehouse (EDW)
• Developed ad-hoc queries on Teradata system for summarizing business operations and trends.
• Developed number of complex mappings using lookups (connected - unconnected), Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, normalizer, sequence generator, Update Strategy, mapplets to implement the business logic and load the data incrementally.
• Developed ETL and Data Quality mappings to load and transform data from source to DWH using Informatica Power Center 9.6 and IDQ.
• Prepared ETL mapping specification documents and Design Documents.
• Created ETL workflows, sessions and mappings.
• Scheduling jobs, creating Workflows variables or parameters dynamically, create and run Workflow task, create Workflow parameter files etc.
• Design Dimension and Fact tables using Erwin data modeler.
• Developed Teradata utilities to load external file data to custom table space for data mining.
• Created cypher queries for real-time link analysis to establish patterns between data sets.
• Identified data correlations across vendor data and business data using data matching tool.
• Worked on BTEQ scripts to push data into teradata tables.
• Created job scripts using Teradata Parallel transporter(TPT) from Files or Database, developed data flows through multiple instances of UPDATE operators to update tables.
• Developed Fast Load scripts to Load external source data into warehouse for analysis.
• Worked on Tpump and Multi Load as per Customer requirement to increase the performance while updating records.
• Automation and scheduling of UNIX shell scripts and Informatica sessions and batches using Autosys scheduling tool.
• Created a minimum viable product(MVP) data model for statistical insight to business users:
• Customized data models and tuned SQL queries for better database performance.
• Worked on Version control software IBM clear case to update statistical models, queries and documentation.
• Performed multidimensional analysis of business data, interpretation results and generated trend analysis or predictive analysis.
• Understanding data behavior for real time analytics on IBM DB2 transactional system for statistical analysis.
• Worked on code optimization, end to end testing with all possible test case scenarios.
• Part of stand-up meetings, sprint retrospective and planning as part of agile software development methodology.
• Provided documentation for business requirements, data mapping, and production release.
• Rewrite existing scripts as part of discovery program for business taxes like sales, use etc.,
• Developed dashboards on Tableau for business users, to identify fraud patterns in tax filings season to release refunds and pulled production data from IBM DB2 to Teradata database using parallel transporter, worked on BTEQ scripts to push data into Teradata tables.

Environment: Informatica Power Center 9.6, Teradata 15.0, IBM DB2, Tableau, Data LadderIBM clear case, Erwin Data modeler.

Confidential

ETL Developer

Responsibilities:

• Involved in Plan, Document, Develop and ETL development for the company’s data warehouse.
• Understanding of the Business Requirements Documents (BRDs), High Level Design Documents and High-Level Test Cases - which are inputs for us to come up with Low Level Design phase deliverables.
• Developed number of complex mappings using lookups (connected - unconnected), Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, normalizer, sequence generator, Update Strategy, Transactional Control, SQLT Transformations, mapplets to implement the business logic and load the data incrementally.
• Data loading from EDI source to claim data mart which includes Design, data analysis, development, debugging and testing of all components.
• Worked with Teradata 12 utilities like BTEQ, Fast Load, and Multi Load and Query man.
• Updated Bteq/ Sql scripts, making appropriate DDL changes and completed unit and system test.
• Developed Fast Export and MLoad scripts in control file, developed macros and Stored procedure to extract data, BTEQ scripts to take the date range from the database to extract data.
• Used advanced concepts of Informatica like PDO, TPT. Used full PDO on Teradata and worked with different Teradata load operators.
• The Load Ready File is loaded into Teradata Table using Teradata’s ODBC/Fast, Multi load connection. The Load Date and Load Time are also captured in the Teradata table
• Developing the SQL scripts in TOAD and creating Oracle Objects like tables, views,Indexes, sequences, synonyms and other Oracle Objects.
• Handled slowly changing dimensions of Type1, Type2 to populate current and historical data to dimensions and fact tables in the Data Warehouse.
• Debugged mappings by creating logic that assigns a severity level to each error and sending the error rows to error table so that they can be corrected and re-loaded into a target system.
• Develop Logical and Physical Data Model using Erwin, followed Star Schema to build Reporting data mart.
• Used PMCMD for calling Batches and Sessions from the Command mode also embed the same in UNIX shell scripts.
• Migrated the ETL code across the environments (Dev, TST, UAT and Prod).
• Documented Mappings, Transformations and Informatica sessions for further maintenance and support.
• Developing the unit test case document and performed various kinds of testing like unit testing, regression testing and system test in Dev, QA environments before deployment.
• Played key role in performance improvement activities like query tuning.
• Performed the data profiling, data cleansing and data analysis.
• Coordinating with the team and interacting with the client for implementation of deliverable.

Environment: Informatica Power Center 9.6, SQL Server, Teradata, UNIX.