- Nearly 8 years of experience in ETL Development working on data integration projects. Knowledge in both agile and waterfall projects in requirements gathering, design, development, implementation and testing of data warehousing and business intelligence applications.
ETL Tools: Informatica Power Center 10.1/9.6.0/9.5.0/9.1.0/8.6/8.5 , SSIS.
Operating Systems: Linux, Unix & Windows.
SDLC Methodologies: Agile, ATDD, BDD & Waterfall.
Database: MS - SQL Server 2005/2008/2012 , Oracle 10g/11g, IBM DB2, Teradata.
Database Tools: SQL Server Management Studio, TOAD, SQL Developer, Dbeaver.
Cloud Technologies: Snowflake Datawarehousing.
Automation Framework: Ruby Cucumber, Rspec, Page Object Model, Selenium.
Other Tools: ESP scheduler, Control-M, Jenkins & Urban Code Deploy.
Confidential, Columbus, Ohio
- Develop ETL jobs to support Confidential Application Datawarehouse and downstream applications.
- Designed and maintained Informatica mappings in a multi team project based on the analytical requirements of the down streams.
- Worked on creating mappings & workflows to extract source data from multiple source systems like Oracle, Flat files etc., and loaded them into Oracle.
- Triggered Web services through informatica and loaded XML data into Oracle database.
- Involved in enhancements and maintenance activities of the data warehouse including tuning and building new ETL code for enhancements.
- Wrote Unit Test cases for ETL pipelines - Test Driven Development approach
- Design data quality checks for reconciliation - Reduced downstream defects by 40%.
- Created multiple mappings from an existing mapping in a single workflow to enhance performance.
- Generating Flat Files and Building Reports according to user Requirement on daily and monthly basis.
- Identified bottlenecks and improved the performance of informatica mappings. Attended performance dojo meetings with requirement analysts, worked on sql queries and etl improvements.
- Involved in developing unit testing automation framework for validating informatica workflows and mapping following Data Integration Framework standards with PERL script, JRuby and Rspec. Utilized dif environment, Rspec, cucumber, sequel, factorybot and rake gems.
- Designed test scripts with data and no-data contexts in the ruby spec files using Rspec and complete understanding of establishing connection with informatica through ruby spec files.
- Wrote Perl scripts to perform initial validations on the input files for the newly added informatica code.
- Scheduled daily jobs for running ruby specs using Jenkins tool for testing integrated code.
- Supported monthly and off-production releases on the migration issues, troubleshoot the code problems and validate the correctness of moved code in production environment.
- Executed informatica flows with different environments using ESP Scheduler.
- Utilized GIT as version control tool for both Informatica workflows and ruby unit test specs.
- Experience in using Urban Code Deploy tool for code deployments between several environments.
- Performed unit testing at various levels of the ETL by writing Ruby scripting test cases in all environments.
- Used JIRA as the Project Management tool for Agile activities.
- Use Snowflake Data Warehousing to migrate Data warehouse and implement best practices.
Confidential, Columbus, Ohio
- Create a change-data-capture framework to manage full and incremental loads of DW tables, and to maintain LSMS application.
- Addressed critical areas of impact in terms of performance, data integrity, runtime errors, user interface, application maintenance and development.
- Involved in designing Error Handling strategy using Error detection/Notification/Re-Processing processes.
- Created source to target mappings according to business requirements.
- Worked on the transformations like Source Qualifier, Expression, Router, Sequence Generator, Aggregator, Filter, Joiner, Normalizer, Update Strategy, Transaction Control, Java and Lookup.
- Involved in Setting up Environments for Development, Test and UAT, which involved Configuring Informatica, Rep Server and running full and incremental loads loads in all the three environments.
- Used Informatica reusability at various levels of development, created mapplets for the common and reusable logic.
- Performance tuning was done at the functional level and map level. Used relational SQL wherever possible to minimize the data transfer over the network.
- Involved in fine-tuning the ETL mapping by studying the session logs and coming up with a better strategy
- Participate in code review, documentation review and coordinate with other developers in technical and business aspects.
- Created jobs for automation of the Informatica Workflows and moving of files using ESP Scheduler.
- Used debugger in identifying bugs in existing mappings by analyzing data flow, evaluating transformations
- Utilized ServiceNow for creating migration requests of Informatica code and Oracle control table updates to production.
- Extensive experience in developing SQL scripts to validate the databases tables and reports data.
- Developed PL/SQL scripts to transfer data across the schemas and databases.
- Used Informatica Version Control for checking in all versions of the objects used in creating the mappings, workflows to keep track of the changes in the development, test and production environment.
- Utilized HP Quality Center tool for working on the defects created by system testers.
Confidential, Columbus, Ohio
- Created technical design specification documents for Extraction, Transformation and Loading Based on the business requirements.
- Extracted the provider records based on given requirement and sending the error messages for the records excluded from state file.
- Wrote SQL overrides and used filter conditions in source qualifier thereby improving the performance of the mapping. Created multiple mappings for complex logic
- Designed and developed mappings using Source Qualifier, Expression, Lookup, Router, Aggregator, Filter, Sequence Generator, Stored Procedure, Update Strategy, joiner and Rank transformations.
- Extensively used auto memory settings and concurrent lookup caches for fine tuning the Informatica processes.
- Extensively used SQL Scripts and worked in Windows Environment.
- Created a new table that holds process details and auto generate Batch ID to populate the data in the extract, exclusion tables.
- Exported, imported the mappings, sessions, worklets, and workflows from development to Test Repository and promoted to Production.
- Used Session parameters, Mapping variables, parameters and created Parameter files for imparting flexible runs of workflows based on changing variable values.
- Worked with re-usable sessions, decision task, control task and Email tasks for on success, on failure mails.
- Involved in creating SSIS ETL’s for generation of data from sources to custom views, several reports and letters.
- Followed Agile Methodology and attended Daily Standups, Sprint Planning’s, Weekly Retrospectives, 3 amigo sessions and story card discussions.
- Executed informatica job flows using Control-M Scheduler.
Senior System Engineer
- Designed metadata tables for storing parameter files values and developed mappings for loading the same.
- Created mappings to perform the tasks such as cleaning the data and populate that into staging tables, Populating the Enterprise Data Warehouse by transforming the data into business needs & Populating the DataMart with only required information.
- Created reusable transformations to clean the data, which were used in several mappings.
- Implemented slowly changing dimension methodology for accessing the full history of accounts.
- Developed complex mappings using Slowly Changing Dimensions Type I, II & III based on the user requirements.
- Created and Executed workflows and Worklets using Workflow Manager to load the data into the Oracle Database.
- Responsible for monitoring scheduled, running, completed and failed sessions. Involved in debugging the failed mappings and developing error-handling methods.
- Worked with Informatica Administrator to setup project folders in development, test and production environments.
- Wrote UNIX Shell Scripts for Informatica Pre-Session, Post-Session and Autosys scripts for scheduling the jobs.
- Generated queries using SQL to check for consistency of the data in the tables and to update the tables as per the Business requirements.
- Involved in Unit testing, User Acceptance Testing to check whether the data is loading into target, which was extracted from different source systems according to the user requirements.