ETL Architect / developer

Swapnil B. Adulkar Contact: 312-***-****

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

Experience Summary:

More than 14 years of IT experience including 12 years in the Data

Warehousing world.

Leadership - Led ETL architecture & development effort and

mentored/assisted the team members.

Client interaction - Interacted extensively with the clients managers,

stake holders, client's technical team members on multiple projects

regarding requirements, ETL strategies, defect remediation and

deliverables.

Estimation - Hands-on experience estimating design and development effort

on various projects and ETL project planning.

Methodology - Experience working in projects using AGILE, Waterfall

methodologies.

Database Exposure - Worked with different databases including Oracle,

Teradata, SQL Server.

ETL Architecture - Designed and developed ETL framework and flows by

leveraging the power of the ETL tool, database utilities and scripts (UNIX

shell, SQL, Teradata - MLOAD, BTEQ, TPT, FEXP, etc.).

Change data capture- Implemented near Real time Change Data Capture (CDC)

using Informatica PowerExchange - Oracle Adapter and Oracle's built-in CDC

on database version 9.2.0.5. Implemented traditional CDC in Informatica

to load a Type-2 table throughout.

Data Modeling Experience-

Knowledge of Data Vault modeling technique in warehouse designing.

Extensive exposure to 3NF warehouse models and Star-Schemas for datamart

implementation.

Experience using ERWIN as a Data Modeler and can navigate through data

models, objects and their details as an ETL architect. Used it throughout

my career.

Performance Tuning - Experience in performance tuning ETL flows by

leveraging the different features of the ETL tool such as Informatica

session partitioning, and the database such as Oracle hints, etc.

ETL Job Auditing - Designed ETL job audit tables and used existing

framework whenever available to capture ETL job statistics.

Error Handling - Experience using warehouse error control tables/routines

to fix erroneous foreign keys in target tables, checking logs and target

data files for errors.

Scheduling - Designed and developed detailed job scheduling structure

considering inter-dependencies of relational tables. Configured scheduler

jobs to execute Informatica workflows via UNIX shell scripts.

Production Support - Extensive experience supporting production loads,

recovering corrupted tables, fixing erroneous records in target tables,

performance tuning of mappings processing bulk loads.

UNIX Shell Scripting- Developed shell scripts throughout the past 12 years

to perform various tasks such as ETL execution, database query execution,

etc.

SQL and PL/SQL experience -

Developed Stored Procedures and triggers using Oracle PL/SQL and dynamic

SQL.

Developed Microsoft SQL Server stored procedures.

Developed customized applications using Forms 5.0/Reports 3.0 Oracle

Developer 2000 with embedded PL/SQL programs.

Excellent communication, client interaction and problem solving skills.

Tools Used:

ETL Tools Informatica Power Center

5.1/6.2.2/7.1.1/7.1.4/8.1.1/8.5.1/8.6/9.1

PowerConnect for SAP R/3

Power Exchange for Oracle

Database and Oracle 8/8i/9i/10g/11g

Utilities Teradata V2 R5/R6

Teradata Utilities - FAST EXPORT, BTEQ, MLOAD, TPUMP,

Parallel Transporter.

MS SQL Server 2008

Database Access TOAD 8.0/9.0.1/10

clients Teradata SQL Assistant 6.1/6.2/7.1/13

PLSQL Developer

Oracle SQLPlus

Data Modeling ERWIN

Tools

Schedulers Used Autosys, Control-M, Tivoli Workload Scheduler

Reporting Tools Oracle Reports, Business Objects

Exposure

Programming Java, C, C++, PL/SQL, PERL, PHP

Languages

Other Tools Developer 2000 (Forms 5.0, Reports 3.0), IBM Clearcase and

Clearquest

Operating Systems Windows (various versions), Linux, UNIX (Sun and HP-UX)

Web Technologies PHP, HTML, XML, WML

Defect Tracking JIRA

Academics:

BS in Electrical Engineering, NIT, Jamshedpur, India (1998)

MS in Computer Science, Illinois Institute of Technology, Chicago, IL

(2002).

Project Summary:

US Cellular

April 2012 - April 2015

Role: ETL Development Lead / Senior ETL designer

US Cellular is a regional (midwest) mobile telephony service provider with

5 million plus subscribers. The project involved building an enterprise

data warehouse for accurate and intelligent business decision making which

would affect the customer base and hence, the company's performance.

Responsibilities:

Led the development effort on one of the three development teams for the

EDW. Delivered several ETL designs and Informatica mappings as part of my

team's deliverable. Moved to stabilization after the development phase to

handle complex issues arising from the warehouse.

Provided technical guidance on several issues to team members.

Assisted in creating the priority list to deliver a phase given the

squeezed timelines.

Provided valuable input on the ETL designing during its infancy in the

project.

Designed several reusable ETL components which were extensively used in the

ETL code.

Assisted developers on development and defect triaging tasks.

Took ownership of critical System testing and User acceptance testing loads

to simulate a production like load execution.

Assisted in developing complex ETL code.

Performance tuned ETL logic.

Assisted in job dependency definitions which were critical to the job

schedule development.

Helped stabilize the warehouse by addressing several complex production

issues which required reloads and redefining the rules.

Used JIRA for issue tracking in an Agile based environment.

Environment: Windows XP, UNIX, Oracle 11g on Exadata machines, JIRA

Software : Informatica PowerCenter 9.1, TOAD

Abbott Labs

Sep 2011 - Feb 2012

Role: ETL Architect/ Lead developer

Abbott Labs is one of the top medical devices and pharmaceutical products

provider in the world. This engagement included designing and releasing

the first project of the warehouse and defining the standards for the

future projects. I provided ETL development & architecture leadership and

expertise for a brand new data warehouse.

Responsibilities:

Designed and developed the entire ETL framework which will be used by all

the projects in the data warehouse -

generic data flow from sources to the landing/staging area

generic shell scripts for Informatica/UNIX code execution and performing

other generic functions.

reusable logging components to capture the stage at which the warehouse

load is at.

reusable email component

reusable file move component

reusable SQL script execution component via Teradata BTEQ

Informatica templates to be used by the developers.

Informatica Error logging

Defined standards for documentation, code development & design for the

warehouse.

Led a small offshore team to deliver the first project during the first

four months of the warehouse.

Designed the ETL flow for the project.

Successfully released two versions of the project in Production

environment.

Released a second project into production by providing Automation via

Autosys.

Environment: Windows XP, Linux, Teradata, Oracle

Software : Informatica PowerCenter 9.1, Teradata SQL Assistant, TOAD,

Autosys

US Xpress Enterprises, Inc.

Nov 2009 - Sep 2011

Role: Data Architect/ETL Architect and Lead

US Xpress Enterprises is a leader in road transportation. The company has a

big IT department housing several sophisticated technologies. I have had a

great experience performing various roles since I began working at USX. The

current assignment deals with data architecture for an OLTP system. I am

working on adding new modules to help cover the new requirements for the

business.

Responsibilities:

Develop normalized data model to encompass the new requirements for the

business.

Develop load strategies to load the new entities.

Develop retention policy for new tables.

Develop design, advantages, entity definition, and other formal documents

required to support a data model.

Coordinate with developer in creation of new entities in AS400 environment.

Coordinate with developer in creation or changing of stored procedures in

SQL Server environment.

Coordinate communication between teams.

Coordinate team meetings.

Ensure that the enterprise IT standards are followed throughout the

process.

Worked as a part-time SQL Server DBA for a brief time. Designed solutions

to port data from MS Access into SQL Server environment.

Tuned and rewrote several SQL Server stored procedures used by the OLTP

system.

Developed Mappings to read data from webservices exposed by other

applications.

Environment: Windows XP, SQL Server 2008, AS400

Software : Informatica PowerCenter 9, SQL Management Studio, iSeries

Navigator.

DELL, Inc

Feb 2009 - Nov 2009

Role: ETL Architect/Lead Developer

Worked as a Lead developer, ETL architect and participated/co-ordinated

development tasks across projects during my stay at DELL. The projects

were primarily related to data warehousing program.

Responsibilities:

Designed and developed ETL flows using Informatica Powercenter 8.6.

Helped design data model on several projects.

Co-ordinated ETL development tasks with team members offsite. Developed

several mappings and workflows on various projects, sometimes being the

single architect/developer on a project.

Managed various roles - management, development, designing and deployment

co-ordination.

Environment: Windows XP, Oracle 10g, UNIX, Teradata

Software : Informatica PowerCenter 8.6, Teradata SQL Assistant

AT&T

May 2008-Feb 2009

Role: ETL Architect/Developer

The assignment was related to sustainment effort within the data warehouse

team in marketing and sales subject areas. Sustainment team dealt with

complex issues within the warehouse and devising a mechanism to fix the

issues going forward and recovering the corrupted data within warehouse. My

role was related resolving such issues and providing solutions to fix the

process; such a role requires expertise as an architect and technical

knowledge of the tools being used in the ETL processes. I had worked on

this warehouse for nearly 3 years prior to coming back to AT&T in May 2008

which proved to be helpful.

Responsibilities:

Addressed issues related to various tables within the warehouse and

provided solutions.

The role required understanding various applications feeding the warehouse

and the various modules loading the warehouse as part of the research.

Understanding the existing processes by reading ETL code (Informatica, UNIX

scripts, Teradata BTEQ scripts) and digging through the process to identify

the root cause.

Formulating multiple solutions and the providing the best possible solution

which would cause minimal impact on the warehouse and minimize the down

time if any.

Changes to the ETL code involved updating UNIX scripts, Teradata BTEQ

scripts, ETL code, Maestro jobs etc.

Provided solution reviews, code reviews to various teams before releasing

the solution in the production environment.

Prepared various reviews and support documents to release the solution in

production environment via operations team.

Attended to various emergency failures which required my expertise on the

applications I had previously worked on at AT&T.

Interacted with the users and clients to determine the business side of the

issue and translating that into technical knowledge to drill down to the

root cause of the issue.

Communicated with other team members to get knowledge on applications they

were familiar with while addressing issues related to those applications.

Environment: Windows XP, Oracle 10g, UNIX, Teradata

Software : Informatica PowerCenter 8.6, TOAD, Teradata SQL Assistant

Dell Inc. (Multiple projects) Austin, TX

August 2007-April 2008

Role: ETL Lead/Architect/Developer

Led the ETL effort, architected the solutions, developed ETL code using

Informatica, UNIX, Teradata scripts.

Delegated tasks to ETL developers while personally taking care of

architecture and ETL lead's responsibilities.

Met the IT management on regular basis to provide project updates.

Performed a proof-of-concept to uncompress BLOB oracle datatypes and parse

the embedded XML string via Informatica's XML parser. Attempted various

methods to uncompress the BLOBs and parse XML strings.

Assisted data architects in deciding the primary key set for teradata

friendly models. Helped standardize data models as per the models.

Led high-level and detailed design reviews, initial and final code reviews

for the project.

Developed Informatica mappings, UNIX shell scripts, Oracle stored

procedures to automate the ETL loads. Developed UNIX shell scripts to

timestamps flat files generated from Informatica and load them into

Teradata database using MLOAD utility.

Tuned the code to improve performance via Informatica session partitioning,

index definitions on relational staging tables, avoiding multiple reads of

large sources, avoiding large aggregations, normalizing within the

relational read, using uncached lookups, using checksum in lookup tables

for faster comparison and breaking large reads into multiple low volume

reads.

Environment: Windows XP, Oracle 10g, Linux, SQL Server 2000/2005 databases,

Teradata

Software : Informatica PowerCenter 8.5.1, TOAD, ERWIN, Teradata SQL

Assistant

Autotrader.com Atlanta, GA

May 2007 to Aug 2007

Role: ETL Lead/Architect/Developer

Autotrader is the leading automobile sales service provider in the United

States. Autotrader's business runs via the online website and by assisting

customers (auto dealerships) setup accounts for online inventory setup,

sales, reviews etc. Hence, tracking the performance of the agents handling

the customers and their interactions is vital to Autotrader. The required

data existed in segregated form across various source systems. The current

project addresses the need to integrate data from various source systems

and reporting intelligently on it to make important business decisions.

Responsibilities:

Involved in business requirements gathering, data analysis, data model

reviews. Spent significant amount of time understanding the source systems

which included Epiphany system, AVAYA system and a custom application

database.

Led the ETL development effort for the project. Provided development,

design and testing effort estimation for the phase of the project.

Developed detailed ETL design for the initial phase of the project. The

design needed to include several key constraints such as extraction time

window, source application availability, performance of source systems,

security etc. These were addressed by extracting at within a safe window

and staging the data.

Designed Delta identification strategy based on existing standards and

availability of change identifying attributes on the source system. Some

sources required full extraction in every cycle while others had timestamp

to identify new and updated records.

Developed detailed source to target mapping document which included the

transformation on source data based on input from business users and

reporting team to develop a reporting friendly solution.

Resolved data quality issues, identified relationships in source data and

assisted in designing the data model for the warehouse.

Developed mappings using various transformations to load the warehouse

tables. The mappings were developed based on existing standards, tested

and performance tuned before moving to QA and production.

Designed and documented failure and restart strategy. All restarts were

designed via Informatica as per client standards. Provided migration

documents with assumptions and agreed considerations for the solution to

support the deliverable.

The project involved source data discoveries, quality issues, design and

development changes which had to be done in parallel throughout the phase

of the project which is typical for a initial phase of warehouse projects.

Considering the tight timeline, the ETL code was delivered on time.

Environment: Windows XP, Oracle 9.2.0.5, Sun OS

Software : Informatica PowerCenter 8.1.1, PLSQL Developer, TOAD,

ERWIN

Cingular Wireless (Multiple projects)

Jun 2005 till Feb 2007

Role: ETL Architect/Developer

Worked as an architect/developer/support analyst on multiple projects

during 2005 - 2007 time frame as part of Datawarehouse enhancement.

Assisted in developing standards as the warehouse evolved.

Involved in reviews on projects under my scope - requirements, data model,

ETL design reviews. Architected ETL solutions.

Developed Informatica mappings, UNIX scripts. Performance tuned Informatica

code.

Developed Teradata MLOAD control files required to load data from target

flat files generated via Informatica mappings, into Teradata target tables.

Developed BTEQ scripts to transform and load data from Teradata staging

table to Teradata target warehouse table.

Performed end-to-end load testing of the ETL as well as the automation

code.

Developed Support documents with details about Informatica objects, UNIX

shell scripts, load process, error handling, install procedures, Unit test

results, migration strategy and backout procedures.

Environment: Windows XP, Oracle 9.2.0.5, Teradata V2R5.0, Sun OS

Software : Informatica PowerCenter 7.1.4, TOAD 7.6/8.0, Teradata SQL

Assistant 6.2,

Teradata MultiLoad Utility (MLOAD), Teradata Fast

Export (FEXP), Teradata BTEQ

Cingular Wireless (Project 3) Atlanta/Dallas

Feb 2005 till May 2005

Role: ETL Lead/Architect/Developer, Application Architect

Second iteration of Project 1 implemented during June-Sep 2004 time frame.

Led the project development effort and delegated work to another team

member.

Altered the existing LMS warehouse by adding logic to process data from a

new source. Changed considerable code from Project 1.

Reused as much code as possible from Project 1 by creating new workflows on

existing mappings and pointing relational connection to different sources.

Developed Informatica code, new unix shell scripts for the project. Altered

Teradata control scripts to add new fields to existing target tables. New

control files were developed for new targets.

Environment: Windows XP, Oracle 9.2.0.5, Teradata V2R5.0, Sun

Software : Informatica PowerCenter 7.1.1, TOAD 7.6/8.0, Teradata SQL

Assistant 6.1,

Teradata MultiLoad Utility (MLOAD), Teradata BTEQ

Cingular Wireless (Project 2) Atlanta/Dallas

Oct 2004 till Feb 2005

Role: ETL Architect/Developer, Application Architect

Implemented CDC (Change Data Capture) process Oracle's built-in CDC.

Completed PoC on Informatica PowerExchange's Oracle Adapter and ruled out

as it was still in it infancy.

Architected the entire ETL framework while adhering to existing coporate

standards.

Developed stored procedures to implement CDC, insert/update job control

records, creating index using dynamic SQL.

Developed Informatica mappings, UNIX shell scripts and SQL scripts.

Developed TPUMP and MLOAD control scripts. Used Teradata Fast Export to

extract data from Teradata.

Implemented reprocessing logic to capture and reprocess bad data.

Supported the application in production environment for limited period

after delivery.

Environment: Windows XP, Oracle 9.2.0.5, Teradata V2R5.0, Sun OS with vtier

and fail over

Servers.

Software : Informatica PowerCenter 7.1.1, TOAD 7.6/8.0, Teradata SQL

Assistant 6.1,

Teradata MLOAD, TPUMP, Fastexport, Informatica

Powerexchange - Oracle Adapter

also known as DETAIL Oracle Adapter from Striva

Corporation, Maestro scheduler

Cingular Wireless (Project 1) Atlanta/Dallas

Jun 2004 - Sep 2004

Role: ETL Architect/Designer/ Developer, Part-time DBA, Data modeler

Developed the ETL architecture for the project. Designed and developed ETL

mappings using Informatica PowerCenter 6.2. Developed 42 mappings to build

type-2 tables.

Developed KORN Shell scripts, Teradata BTEQ scripts, Oracle SQL scripts.

Worked as a part-time DBA. Maintained the oracle staging schema.

Environment: Windows XP, Oracle 9i, Teradata V2R5.0, HP-UX, Sun OS.

Software : Informatica PowerCenter 6.2.2/7.1, TOAD 7.6.0, Teradata

SQL Assistant 6.1,

Teradata MultiLoad Utility

StorageTek, Denver, CO May

'03 - May 04

Role: ETL Developer/Data warehouse Consultant

Developed Informatica mapping, sessions, UNIX scripts to execute sessions

and created Autosys scheduling jobs to schedule sessions through UNIX

script execution.

Extracted data from SAP using PowerConnect for SAP R/3.

Monitored Production loads on daily, weekly and monthly basis.

Environment: Windows 2000, Oracle 8i/9i

Software : Informatica PowerCenter 5.1, PowerConnect for SAP R/3,

TOAD 7.5.2, Autosys 3.5

Oracle Corporation, Chicago, IL

Dec '00 - March 01

Role: Application Developer

Designed and developed Forms, reports, PLSQL procedures, DBMS jobs using

Developer 2000.

Environment: Windows 98, Oracle 8

Software : Oracle Developer 2000, Forms 5.0, Reports 3.0, TOAD

Lintas Mumbai, India

Aug'98 - Jan '00

Role: Application Developer

Developed Master-detail-detail Forms, Reports, Database procedures and

triggers, DBMS Jobs (Oracle Technology)

Environment: Windows 98, Oracle 8

Software : Oracle Developer 2000, Forms 5.0, Reports 3.0, TOAD

Contact this candidate