ETL QA

Satish Udakhe (ETL/BI-Test Lead)

Mobile: +1-404-***-****

Email: *******@*****.***

Summary:

Over 11 years’ experience in DWH ETL Development and testing area mainly in:

oData warehouse Testing/BI ( ETL, BI )

oData warehousing Datamart and Report Development using Tableau as BI tool and Informatica as ETL.

Strong experience of development of Data warehousing application using Informatica as ETL tool and BOXI as reporting tool.

Data processing knowledge in designing and implementing Data Warehouse applications, mainly Transformation processes using ETL tool Informatica Power Center 6.x, 7.x and 8.x etc.

Thorough knowledge on Data warehouse concepts like Star schema, Snowflake schema, Fact constellation, Data Marts, ODS, Dimension and Fact tables.

Strong experience of preparing SQL queries for performing ETL, Database, Migration, Data Comparison, Reports (SSRS, BO, Cognos) testings

Good experience in formulating test strategies for Functional & BI projects (ETL, Data Migration, Reports)

Experience in analyzing functional requirements and breaking them into high level & low level scenarios

Good experience in preparing test data covering all types of scenarios for ETL, DWH, Reports testing

Testing of job scheduling using control-m,job dependency & debugging the failures

Have around 4 years of Team Lead experience and managed geographically distributed team of 10 members

As an lead, performed end to end STLC activities encompassing On boarding, Estimation, Planning, Execution, Monitoring and Closure phases

Worked in domains like Insurance,Investment Banking, Finance & Securities and Capital Markets

Professional Experience:

Organization

Designation

Duration

TechMahindra Ltd

Associate Technical Specialist

Sept 2013 – till date

Wipro Technologies

Senior Software Engineer

May2010 – Aug 2013

Syntel Ltd

Analyst Programmer

Oct 2005 – April 2010

Skill Sets:

DWH Tools : Informatica ETL, Power Designer as Data Modeling tool, Control-m

Databases : SQL Server, Oracle, DB2.

Reporting Tools : Tableau

Database Tools : SQL Developer, TOAD

Test Management Tool : Quality Centre, JIRA, Clear Quest

Configuration Management: VSS, Clear Case, SharePoint,

Languages & Scripting : HTML, CSS, XML, PL/SQL

Test Tools : Test Complete, QTP,

Operating Systems : Windows, UNIX

Experience Details:

TechMahindra Ltd Feb 2014 – Till date Associate technical Specialist

Since Jun 2017, I am working at Atlanta GA location as Onsite counterpart for SIWEB (Service Intelligence Web Portal) Reporting project for AT&T USA and responsible for end to end testing for entire SIWEB application.

Project Name : SIWEB (Service Intelligence Web Portal) Reporting

Client : AT&T Inc. USA

Role : ETL and Data Quality specialist

Duration : Aug 2014 till date

Location : Pune

Team Size : 6+3

Project Description:

SIWEB is a reporting portal where the applications are monitored based on the performance, availability, reliability, transactions aspects. Each aspect has metrics associated with it like volume, success rates, error rates etc. SIWeb tracks which application is in and out of ATL (Alert Threshold Level). Example- If ATL for success rate of Myatt Mobile application is 90% and if it is below 90% then an alert (e-mail notification) is send to the concerned team. Thus it ensures the application health by identifying potential issues and take actions before it impacts customers. There are Fusion charts used on the portal which is a flash-based charting tool. These charts give graphical representation of the applications that are monitored based on the metrics.

The relevant data is collected on a prior day basis from different data sources that supports the applications, all the metrics are computed with the available data and the data is then published on SIWeb portal.

SIWEB also tracks some applications on near real-time (NRT) basis where data is collected for every 15 minutes.

Front-end: SIWEB portal is designed using JAVA/J2EE web page. The front end is built in jsp which is dynamic in nature. For Alerting, SIWEB uses RUBY which generates tickets when ATL is crossed and send e-mail to the concerned team.

Back-end: There are three databases used- Dashboard, SIWEB and NRT. Each database have fact tables (contains raw data), dimensional tables (contains aggregated data), views and materialized views.

Roles:

• ETL (Informatica & PL-SQL) : Get data from different sources to look at different aspects

• Java (Java & PL-SQL): Portal handling (FE)

Role/Responsibility:

Preparing estimates for JIRA items for every release

Understanding the requirements, preparing the high level test scenarios (positive, negative, exceptional) and writing SQL queries for validating the data against the mapplets in IDQ

Pre-validation checks on feed files from UNIX, file validation, file load and file archival

Preparing test feeds for validating the source filter conditions, delta checks (SCD type 2 checks) etc

Identifying the test data requirements and preparing test data for validating functionalities like transformation, duplicates, delta loads, rejection, fuzzy match, trust matrix-linkages

Data Quality analysis, ETL testing, Control-m testing, standardization & cleansing, matching and linking, fuzzy matches, workflow dependencies and testing, SCD Type 2 checks, de-duplication etc

Control-m job scheduling & testing, job dependency & debugging the failures

Sending test progress report to client and all stakeholders

TechMahindra Ltd Sept 2013 – Feb 2014 Associate technical Specialist

Project Name : Commodity Credit Risk Management system (CCR)

Client : Scotia Bank, Canada

Duration : July 2013 till date

Location :Pune

Location : Pune

Team Size : 6+2

Project Description:

Commodity Credit Risk Management system (CCR) is client-server application which manages the trades in various stages. It constitutes of various reports based on different criteria which gives the overview of trade, this project revolves around the interaction between SCOTS (Scotia Commodity Trading System) and other in-house like Commodity Risk Watch (CRW) and Commodity Risk Ware House (CRWH). SCOTS is a full front-to-back commodity trading and risk management system, handling trade entry, operations (confirmations and scheduling), settlement, valuation and risk management functions across multiple commodities in real-time. RWH is a unified repository accumulating EOD risk, accounting and VAR data. This program was formed within GBM aiming to

Help to collect more info about client

Perform Risk Assessment Methodology over customers

Identify higher risk by monitoring progress that flags up unusual transactions

Data quality is major issue in SCOTIA Bank due to its hundreds of isolated and disjoint IT systems across globe. In order to accurately discover the entire GBM client population significant amount of data analysis & cleansing is required which gave birth to the Client Discovery program where Informatica Data Quality (IDQ) solution is leveraged. The IDQ provides many tools like Informatica Analyst, IDQ Developer, Informatica Data Directory and integration with Jasper reports

Role/Responsibility:

Preparing estimates for JIRA items for every release

Understanding the requirements, preparing the high level test scenarios (positive, negative, exceptional) and writing SQL queries for validating the data against the mapplets in IDQ

Pre-validation checks on feed files from UNIX, file validation, file load and file archival

Preparing test feeds for validating the source filter conditions, delta checks (SCD type 2 checks) etc

Identifying the test data requirements and preparing test data for validating functionalities like transformation, duplicates, delta loads, rejection, fuzzy match, trust matrix-linkages

Data Quality analysis, ETL testing, Control-m testing, standardization & cleansing, matching and linking, fuzzy matches, workflow dependencies and testing, SCD Type 2 checks, de-duplication etc

Control-m job scheduling & testing, job dependency & debugging the failures

Sending test progress report to client and all stakeholders

Experience Details:

Wipro Technologies Ltd May 2010 – Aug 2013 Senior Software Engineer

1.Project Name : NUL Finance Transformation

Client : Aviva Insurance, York UK

Role : Test and Data quality Analyst

Duration : Mar 2012 to Aug 2013

Location : Norwich UK

Team Size : 10

Project Description:

Aviva Insurance, York is a UK.-based Insurance and financial services holding company. Aviva has engaged Servicer to migrate its Oracle Data Mart for five of its applications to Teradata platform. Currently the existing 5 applications are using Oracle databases at the backend; Oracle to Teradata migration project will convert the Oracle database schemas to Teradata. ETL and Reporting layer is currently pointing to Oracle, but after migration to Teradata, it will point to Teradata database.

The new system will replace the existing oracle database schemas with teradata for enhanced maintainability, scalability, reliability and performance.

Role/Responsibility:

Preparing & uploading the test data as per test scenarios for Pre landing schema to validate the ETL mapping & transformation rules

Preparing the test scenarios and writing SQL queries for validating the mapping & transformation rules in ETL, validating data in Landing, Staging and Base Objects, validate the match functionality

Validating the source and target counts, lookups, cleanse functions, data duplication etc

Sending test progress report to client and all stakeholders

Publishing the project Lessons Learned document among stakeholders

Formulating test plan and BI test strategies for

oData Migration

oETL Testing

oReports Testing (Cognos, Actuate, Spotfire)

Preparing the high level test scenarios (positive, negative, exceptional) and writing complex SQL queries for validating the mapping & transformation rules in ETL, testing stored procedures

Identifying accelerators for test activities like data comparison & ETL testing

Sending test progress report to client and all stakeholders.

2.Project Name : TARDIS Support and Development

Client : Aviva Insurance, UK

Role : ETL Developer

Duration : May 2010 to Mar 2012

Team Size : 6+3

Project Description:

AVIVA is the world's sixth largest insurance group and the largest in UK. It is 300 year old company. Its business in the UK is Aviva UK Life (formerly Norwich Union Life) and Aviva UK Insurance (formerly Norwich Union Insurance). It is the largest provider of life, pensions and long-term financial services in the UK, with a market share of around 10.8% in the first half of 2009. It has huge data warehouse support for its financial applications and data marts.

TARDIS (Transparent Actuarial Reporting Database Insights System) is a wholesome approach to suffice the Data warehousing need of Aviva UK Life which comprises of data movement from its seven major sources(Admin,Unisure,P1l70,Paymaster,Lancelot,Alpha and HDS) to various designated Data Marts through Inbound and Outbound staging area. This entire process includes exhaustive usage of ETL tool Informatica, Database Oracle, Programming language Perl and operating systems like Windows XP and UNIX. Post data population into various Data Marts they are used to generate reports using Business Objects and Crystal Reports.

Role/Responsibility:

Develop/Modify Informatica Mappings, Workflows, and different session tasks.

Worked extensively on different types of transformations like Expression, Filter, Aggregator, Rank, Lookup, Update Strategy, Sequence Generator and Joiner etc.

Created and maintained Informatica Mappings.

Prepare UTR, TDS & Unit Test Cases for the Mappings.

Sort out the internal Defects of Developments.

Testing - unit testing & integration testing.

Worked on migration of mappings & workflows across environments

Tuning the Informatica Code for Optimum Performance.

Performance tuning of mappings as per production timeline for different upstream and downstream.

Experience Details:

Syntel Ltd, Pune Oct 2005 – April 2010 Analyst Programmer

1. Project Name : MIS Redesign

Client : ACE Insurance

Role : ETL Developer

Duration : July 2008 – April 2010

Location : Philadelphia USA.

Team Size : 11

Project Description:

This is the MIS Redesign project for ACE Insurance to populate the five Data mart’s tables.Previously the systems used the Stored Procedure and DTS and load the data into SQL Server 2005. But from end to end it was taking 14 to 16 hours to complete. MIS Resign comes into picture due to time taking of previous system. In the new design system uses the Informatica 8.x and loads it into UDB (IBM Database). Genius System ftp .dat Binary file to SAN LANDING site on the daily basis. We use Power Exchange maps to convert the binary file into readable format and push the into Intermediate table. This intermediate table we are using the sources to populate the individual Data marts. First we load the data into stg table and the nonstg table.

Role/Responsibility:

Worked extensively on different types of transformations like Expression, Filter, Aggregator, Rank, Lookup, Update Strategy, Sequence Generator and Joiner etc.

Created and maintained Informatica Mappings.

Prepare UTR, TDS & Unit Test Cases for the Mappings.

Sort out the internal Defects of Developments.

Used Workflow Manager to create, schedule, execute, and monitor sessions and batches that perform source to target data loads.

Created Reusable Transformations and Mapplets.

Repository Manager, Designer, Workflow Manager and Workflow Monitor.

Implemented Informatica Best Practice strategies while developing Transformations, Mappings and Mapplets using Informatica power Center.

Created Sessions using Workflow Manager.

Identifying & assessment of resources, allocation, on boarding, assigning roles & responsibilities

Identifying learning & certification plans for team and executing them

Performing team appraisals, setting goals & objectives, providing constructive feedback with improvement areas

Good understanding of stakeholder management

Identifying risks, escalating on time with appropriate stakeholders and mitigating them

Performing project assessment on process areas & implementing solutions for improvement

2. Project Name : Statements Renovation

Client : Morgan Stanley (GWM)

Role : ETL Developer

Duration : July 2007 – Jun 2008

Location : Mumbai

Environment : Informatica 8X for ETL, BOXI for BI reporting, Oracle, UNIX, and Distributed Environment

Team Size : 9

Project Description:

Phase I of the Financial Snapshot Project (initially known as Statement Renovation) is to provide a new Pre Year End Summary (PYES) document to Morgan Stanley clients through changes to the delivery, printing, formatting, processing and data sourcing of the document. The PYES document will provide a consolidated view of the client’s financial relationship with Morgan Stanley and should prompt year-end planning conversations between clients and Financial Advisors (FAs). Monthly, quarterly and annual statements will continue to be produced in parallel to the PYES document.

Role/Responsibility:

Study the Requirements for Phase I & II

Develop/Modify Informatica Mappings, Workflows, and different session tasks

Worked extensively on different types of transformations like Expression, Filter, Aggregator, Rank, Lookup

Preparing scenarios and writing test cases for E2E testing of the application

Writing medium to complex SQL queries using Business Rules for comparing data against Teradata database

Exclusively worked on validating the new monthly statement with the current statement from OnDemand

Querying EDW for the required account classifications/account types and transactions as needed

Validating the transaction codes for Activity section, identifying the missing transactions in new statements

Defect management with onsite lead and QA manager

Preparing different status reports for E2E testing

Preparing module-wise induction document for project

Coordinated and mentored new team members

3.Project Name : ALGO

Client : Allstate, Chicago, USA

Role : ETL Developer

Duration : Oct 2005 – June 2007

Location : Pune

Environment : Informatica Power Centre 8.x for ETL, BOXI for reporting, SQL Server 2000,Unix

Team Size : 6

Project Description:

The Portfolio Management Group selected the Algorithmic Market Risk Solution to support a “top-down” view of the portfolios, and as the mechanism to provide consistent, transparent and standard methods for obtaining key analytics calculations. The implementation approach will be to deploy the solution in three (3) releases to help reduce delivery risk while building in-house expertise for the Algorithmic solution. The Centralized Risk Management & Fixed Income Analytics Project will involve the analysis, design, build, test and implementation of the Algo Market Risk Solution, consisting of the following software components: Algo Risk Watch, Algo Scenario Engine (ASE), and Algo Reporting Analysis (ARA) tool. This solution will unify the Allstate Investment’s analytical framework.

Role/Responsibility:

Study the Requirements for different work packets

Preparing traceability matrix and writing test cases for Functional, Integration, Systems, GUI and Localization testing(English and German versions)

Test data creation for financial ratios and performance data of various financial instruments

Querying SQL Server, preparation of complex SQL queries for test execution

Closely worked with Dev team for successfully deploying first application build for QA team

Defect management and escalation

Coordinated with developers, mentored new team members

Notable Accomplishments & Recognition:

TechMahindra Ltd

Client: Scotia Bank

1. As an value addition, proactively helped team for preparing POC on automation using Test Complete automation tool, which paved way to win new project from client

2. As an innovation, conceptualized & developed QC Test Case Download tool with the help of team, saving efforts while updating & uploading test cases multiple times

3. As an best practice, prepared a twiki site for all projects which helps in quick on boarding of new joiners and got much appreciated by client

4. Proposed centralized QC structure, which helped client to standardize QC usage by users across different projects in different domains

5. Complex SQL queries prepared that helped entire team to verify data against the application

6. Received appreciation from client for taking quick transition of major modules

Educational Qualification:

Degree

Institute/University

Year of Passing

B.E.

Amravati University, Maharashtra

2003

H.S.C

Amravati Board

1999

S.S.C

Amravati Board

1997

Professional Qualification:

DNIIT certificate from NIIT

Undergone corporate training on IBM Rational Functional Tester

Undergone LEAP – Leadership Enhancement Skills training from Wipro

Personal profile:

Address : 10385 Morris Road, Unit 10108 Alpharetta, GA,30004

Nationality : Indian

Marital Status : Married

Language Known : English, Marathi and Hindi

Passport Number : L6850637

Contact this candidate