Etl Designer Resume
Omaha, NE
SUMMARY
- 6years 4 monthsof experience in IT Industry involving in full life cycle development including requirement gathering, analysis, design, development, testing, implementation, maintenance and documentation of Data warehousing models using ETL tools.
- 6+ years of relevant hands-on experience on AbInitio ETL, Data Mapping, Transformation and Loading in complex and high-volume data processing applications.
- Extensive experience working withTeradata sql scripting using analytical functions in analytical applications.
- Have extensive programming and debugging skills in Ab Initio ETL tool, Teradata sql scripting, Oracle and UNIX shell scripting.
- Have experience working with PDL and DML Metaprogramming in Abinitio.
- Have good experience working with Continuous Flows using Abinitio queues and MQ series queues.
- Strong ability to understand and convert the logical data modeling into ETL data flow and draw a proper mapping to physical data model.
- Ability to design the ETL considering factors like high data volume, partitioned data, join effectiveness, parallelism, generalization etc.
- Have solid experience of financial domain with good understanding of areas like cards specially Credit Cards, Prepaid Cards, Account Statements, Account Composite, Collection, and Recoveries, Bank, Personal Loans, Home Equity etc.
- Strong experience of agile project methodology.
- A Team Player with excellent communication, analytical, verbal and writingskills.
- Strong articulation and presentation skill. Take bottom line responsibilities for assigned tasks. Proven track record of delivering results in Fast Track development environment within timeframe.
Technical Skills
ETL Knowledge: Abinitio Co>Op 2.15/2.14, GDE 3.0, 1.15, 1.14, EME, PDL (Parameter Definition Language), DML Metaprogramming, generic graphs and psets, Continuous Flows, XML common data processing.
Teradata Bteq, Fast Load, Fast Export and Multi Load utilities through Abinitio.
Trillium standardization, enrichment & matching via Abinitio.
Data modeling tools: ERWIN, ER diagrams
DBMS: Teradata V2R5, Oracle8, Oracle9i, DB2 iseries via Abinitio
Concepts: Data Warehousing and data modeling concepts including dimensional and relational concepts.
Database tools: Teradata sql Assistant, SQL*Plus, DB Visualizer 7.1.3
Languages: UNIXshell scripting, SQL/PL SQL scripting, Teradata Bteq
Scheduling: Control-m
Certifications: 1). Teradata Certified Professional V2R5
2). Teradata Certified SQL Specialist V2R5
3). Teradata Certified Application Developer V2R5
Professional Experience:
Confidential, Omaha NE July'12 - till date
Data Vault - Symphony
Responsible for design and development of operational data warehouse applications at Firstdata using latest data modeling technique i.e. Data Vault and ETL Abinitio, Netezza and Unix.
Role & Responsibilities
- Lead ETL (Abinitio/Netezza/Unix/Data Vault) Designer, developer, onsite coordinator.
- Technologies used - Abinitio V3-0, UNIX shell scripting, Netezza.
- Participated in all stages of project grouped into various stories, project being agile.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping.
- Team size: 10
Confidential, Richmond VA Sept'11 - June'12
Commercial Credit Risk Management PL / SMART PL
With DMS commercial credit risk management PL project we designed and are developing presentation layer tables & Teradata sql scripts to pull credit risk data from various Capital One loan source systems, do aggregations, implement various complex transformations to present important metrics like max credit exposure, delinquencies etc. Data is delivered at various granularities i.e. account, facility, consumer level for further business analysis.
Worked as data warehouse - ETL analyst/developer and successfully implemented SMART (Sales and Incentives measurement reporting tool) using Abinitio, Teradata and Unix. As part of the project we developed presentation layer for all line of businesses at Capital One including banking, personal loans, home equities, credit cards etc.
Role & Responsibilities
- Lead ETL (Abinitio/Teradata) Designer, developer
- Technologies used - Abinitio V2-15, UNIX shell scripting, Teradata
- Development of efficient, easily maintainable Abinitio graphs, shell scripts, & Teradata sql scripts.
- Participated in all stages of project grouped into various stories, project being agile.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping.
- Developed and reviewed test plans, test scenarios, test cases as part of Unit testing.
- Responsible for setting up Production Jobs and created Production Support Document.
- Team size: 8
Confidential, Durham NC Sept'10 till Aug'11
Oneview 2.0
With Oneview 2.0 consolidated pictures or the master member record will be achieved in the MDM. Data from various sources are extracted, cleansed and brought to common record format.
Later Trillium is called for standardization and matching similar records based on window keys. Based on Trillium output MDM produces collapsed member records i.e. one view of member data.
Role & Responsibilities
- ETL Designer and Developer.
- Technologies used - Abinitio V2-15, Continuous Flows, PDL, Metaprogramming, XML data parsing.
- Development of high performing tuned Abinitio graphs.
- Responsible for Technical Design, Development, Unit Testing, Code Review, Code migration, Supporting System testing, Job scheduling.
- Developed and reviewed test plans, test scenarios, test cases as part of Unit testing.
- Responsible for setting up Production Jobs and created Production Support Document.
- Team size: 12
Confidential, Richmond VA May'10 till Sept'10
Estates
The Estates Redesign project will develop, document and deliver an effective in-house operations process to assist our Cardholder\'s loved one\'s during one of the most difficult times in life. An IT solution will be developed to support and enable the Estates redesign operations process. It will manage deceased notifications (phone & mail), process payment protection claims, and place accounts with probate vendors for recovery. This project will allow survivors to resolve accounts faster while also enhancing Capital One's recovery on these debts.
Role & Responsibilities
- ETL Designer, onsite coordinator and reviewer.
- Technologies used - Abinitio V2-15, UNIX shell scripting, Teradata, JTNM
- Development of high performing, easily maintainable Abinitio graphs and shell scripts.
- Participated in all stages of project grouped into various stories, project being agile.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping.
- Develop & review several Korn scripts to integrate workflows on Control-M.
- Developed and reviewed test plans, test scenarios, test cases as part of Unit testing.
- Responsible for setting up Production Jobs and created Production Support Document.
- Team size: 8
Confidential, Richmond VA Mar'10 till May'10
Secure Card - Fraud Extract
Secured Card is a Credit Card product that is secured by the customer's deposit. All Secured Card data is stored in the Secured Card Data Base (SCDB). New process extracts suspicious deposit transactions from SCDB and put them into a table in CDW. Fraud extract is further used by Risk operations team to catch suspicious deposits and applicants.
Role & Responsibilities
- ETL Designer and developer.
- Technologies used - Abinitio V2-15, UNIX shell scripting, Teradata, JTNM
- Development of high performing, easily maintainable Abinitio graphs and shell scripts.
- Participated in all stages of project grouped into various stories, project being agile.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping.
- Develop several Korn scripts to integrate workflows on Control-M.
- Performance Tuning on the Targets, Sources, Mappings and Sessions used Techniques like reusable Transformations, reusable Caches and partitioning etc.
- Developed test plans, test scenarios, test cases as part of Unit testing.
- Responsible for setting up Production Jobs and created Production Support Document.
- Team size: 6
Confidential, Richmond VA Dec'09 till Mar'10
Developed Abunit, an automated unit testing framework for Ab Initio.
Abunit follows the 4-phase test model of setup, execute, verify, and teardown for each test suite and its corresponding test cases. It has a core process which takes xls test suite as an input, creates actual input and expected output, executes actual test case to create actual output and then compares the two outputs to generate xls report.
Phase -1 of the tool is successfully implemented in Dec'09.
Role & Responsibilities
- ETL Designer and developer.
- Technologies used - Abinitio V2-15 - PDL, DML Metaprogramming, UNIX shell scripting.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping.
- Created reusable transformations, and implemented various slowly changing dimensional mappings.
- Designed error checking mechanisms to reduce failures and monitor error logs.
- Develop several Korn scripts to integrate workflows on Control-M.
- Performance Tuning on the Targets, Sources, Mappings and Sessions used Techniques like reusable Transformations, reusable Caches and partitioning etc.
- Team size: 8
Confidential, Richmond VA July'09 till Dec'09
Bankruptcy Redesign project involved identifying bankrupt accounts. Process involves sending of account and demographic information to data processor. Data processor collects bankruptcy info from courts, law firms associates them to account info send by Capone and replies back with bankruptcy notification and proof of claim information. The process involves processing of real time data thus required implementing significant amount of exception and error handling routines. Data quality checks, duplicate checks, checksum validation etc are some of common validation put in the Abinitio graphs and UNIX shell scripts.
Measures are implemented for ensuring performance, because of high volume of data involved.
Role & Responsibilities
- ETL Designer and developer.
- Technologies used - Abinitio V2-15, Teradata utilities - api/multiload, UNIX shell scripts
- Development of high performing, easily maintainable Abinitio graphs and shell scripts.
- Involves staging data file from source (vendor), cleaning data, publishing on Capital One data servers and later performing various operations on it before finally loading into data warehouse.
- Participated in all stages of project grouped into various stories, project being agile.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping.
- Created reusable transformations, and implement various slowly changing dimensional mappings.
- Designed error checking mechanisms to reduce failures and monitor error logs.
- Develop several Korn scripts to integrate workflows on Control-M.
- Performance Tuning on the Targets, Sources, Mappings and Sessions used Techniques like reusable Transformations, reusable Caches and partitioning etc.
- Developed test plans, test scenarios, test cases as part of Unit testing.
- Responsible for setting up Production Jobs and created Production Support Document.
- Team size: 10
Confidential, Richmond VA Apr'09 till Jun'09
Presentation Layer Dialer Enhancement to add 20 new columns in dialer presentation layer table. Presentation layer was created earlier to help Data Analysts at Capital one. It aggregates data from various source tables lying under different subject area to present aggregate picture. It involves use of complex join, grouping, transformations, and formatting.
Enhancement involved analysis of existing code to find the best place to fit the new attributes, analysis to make sure that there will be no impact on existing code, coding, data mockup and testing.
Role & Responsibilities
- ETL designed and developed using Teradata SQL scripts.
- Technologies used - Teradata- Teradata SQL Assistant, Bteq scripts, Fast load
- UNIX shell scripts, Abinitio V2-15
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping, setting up production jobs and dependencies using control-m.
- Performance tuning by the use of collect statistics, creation of volatile and global temporary tables, defining new primary indexes with keeping join keys in mind, use of driver tables to restrict the data and make the aggregations run faster.
- Team size: 8
Confidential, Richmond VA Dec'08 till Apr'09
Prepaid Relaunch is General Purpose Reloadable (GPR) product & launched as one of the initiatives to attract unbanked consumers. Prepaid Customers and Transactions related data is received from external vendor which we stage, clean, process and load into Card Data Warehouse. History is maintained to analyze on data using BC (Business Current) and PT (Point in Time) views. Processing includes cleansing of data files where we apply data quality, data integrity checks, ensure undeteriorated transmission. Performance enhancement measures are also implemented on CDW side.
Designed and developed generic graph which are called through psets is easily maintainable and reusable.
Role & Responsibilities
- ETL design and development using Abinitio graphs and UNIX shell scripting.
- Technologies used - Abinitio - Generic graph and psets, metaprogramming and pdls. Teradata - Teradata Bteq, Teradata SQL Assistant and UNIX shell scripts.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping, setting up production jobs and dependencies using control-m.
- Staging, cleansing, transforming and loading are main objectives.
- Various error handling and exception handling codes are implemented in Abinitio graphs and UNIX shell scripting.
- Psets and Generic graphs are used to handle processing of five different files through one Abinitio graph.
- Design got highly appreciated from client and set standards for every further projects.
Confidential, Pune, India Dec'07 till Dec'08
The presentation Layer project involves creation of new tables in the Card Data Warehouse for storing the data required for Analytics. The attributes in the Presentation Layer tables are defined by the Data Analytics team and the same will be the primary end user of these new tables. The primary focus of this project is to improve the CPU performance on the queries for reporting needs. This is extensively written in Teradata involving a lot of volatile and temporary tables.
Role & Responsibilities
- Writing Teradata queries & designing ETL from performance point of view specially suited for reporting purpose.
- Technologies used - Abinitio, Teradata, UNIX shell scripts.
- Use of driver table to restrict the data in joins. Performance tuning is done by making sure that join btw two tables happens always on keys, this is made sure by creation of either volatile and/or global temporary tables. Collect statistics is also done to ensure faster fetch from queries on tables.
- Various aggregation functions are used to present the composite data into PL table which further made tasks of data analysts' easier.
- Use of constructs like GROUP BY, CASE WHEN, CAST, STRING functions, SUM, MAX, MIN, AVG, COUNT, LIKE etc are common.
- Mocking up data and creating data scenarios were important parts.
- Team size: 12
Confidential, Pune Oct'07 till Dec'07
Enterprise Merchant Funded Rewards is the project associates retail merchants and offers issued by them to credit accounts. Third party data processor collects offers from merchants and replies back, ETL checks the quality of data received and associates the offers with credit accounts wherever applicable.
Code was implemented to deal with high volume of data as volume was expected to rise through the period of time.
Role & Responsibilities
- ETL design and development with Abinitio graphs and UNIX shell scripting.
- Technologies used - Abinitio, Teradata, UNIX shell scripts
- Staging, Cleansing, Transforming, Formatting and Loading are major parts.
- ETL was designed considering increase in data volume in future.
- Stressed on parallel processing, data partitioning.
- Team size: 8
Confidential, Pune May'07 till Sept'07
Master Data Management (MDM) is a system that is divided into two applications: the first being the Teradata MDM tool which is a web-based application that runs on Web logic and connects to the Teradata database; the second application being the data movement (ETL) application that will run on the DDE and unload the data from the MDM tables and load it to the target tables.
Implemented generic design for above ETL requirement. Only two graphs took care of unloading and loading for ~300 tables. Process was controlled through call to specific psets.
Design and approach for the project was really quick and appreciated by the client.
Role & Responsibilities
- ETL design and development with Abinitio graphs and UNIX shell scripting.
- Technologies used - Abinitio, Teradata, UNIX shell scripts
- Use of generic graph and psets to unload 300 tables using just one graph.
- Responsible for Requirements gathering, Technical System Design, Development, Testing, Code Review, Code migration, Supporting System testing, Job scheduling, Offshore Coordination, prepared source to target mapping, setting up production jobs and dependencies using control-m.
- Team Size: 10
Confidential, Pune Oct'06 till Apr'07
Break-Fix (Defects & Enhancements) - Analysis of various databases (production as well development) on different Capital One servers. Work is concerned with encryption of non-public (active) data and truncation of inactive data. It involves shell scripts and Abinitio graphs to perform the above two main tasks. UNIX scripts are modified from time to time to automate the above and related processes. Scrubbing (encryption) is done schema wise and truncation is done table wise.
Role & Responsibilities
- ETL design and development with Abinitio graphs and UNIX shell scripting. Various requests involved data analysis through Teradata SQL Assistant.
- Technologies used - Abinitio, Teradata, Oracle, UNIX shell scripts
- Enhancement or break-fix (defects) from various data warehouse applications across Capital One were assigned to us. Break-fixes could vary from functional defect, control-m dependency change, small enhancement, performance improvement etc.
- Requests always involved lot of analysis as was always in existing functionality. Impact analysis was a major task.
- Team size: 8
Confidential, Mysore June'06 till Oct'06
Developed an employee payroll application using UNIX shell scripts and Oracle database. Application is an interactive system which takes employee details as input and maintains the same in the database. It also calculates appropriate salary for an employee considering his position, experience, performance etc.
The project was developed as part of Infosys interfacing and leadership program.
Roles, Responsibilities & Learning
- UNIX skills are tested.
- We created the logical model using Entity Relationship diagrams and later implemented it through C++, UNIX and Oracle.
- Technologies used - UNIX shell scripts, C++, Oracle.
- Learned how to integrate the front-end with back-end for an application thus experienced the development of integrated application.
- Learned steps of software development life cycle and importance of each of its phases.
Confidential, Jabalpur Jan'06 till Jun'06
The project was an online portal which provided various interfaces to do online trading.
Links to various services are provided. Various reference websites are also linked.
Roles, Responsibilities & Learning
- Designing web interfaces, developing tables in backend and interfacing front-end to backend.
- Technologies used - Oracle, Java Servlets, and JSPs.
- Learned to develop whole live applications.
- Involved through designing, development, unit testing for this project.
- Team size: 4