PROFESSIONAL SUMMARY:

• Over 9 years of experience and 5 years of Teradata development and design of ETL methodology for supporting data transformations & processing in a corporate wide ETL Solution using Teradata TD14.0/TD13.0/TD12.0, Ab Initio, administration, analyzing business needs of clients, developing effective and efficient solutions and ensuring client deliverable within committed deadlines.
• Proven track record in planning, building, managing successful large - scale Data Warehouse and decision support systems. Comfortable with both technical and functional applications of RDBMS, Data Mapping, Data management, Data transportation and Data Staging.
• 5+ years OLTP, ODS and EDW data modeling (logical and physical design, and schema generation) using Erwin, ER/Studio and other tools for Teradata.
• 6+ years administering large Teradata database system in development, staging and production.
• Expert Developer skills in Teradata RDBMS initial Teradata DBMS environment setup, development.
• Expert in using administrative utilities like Archive/Restore, Table Rebuild, Check Table, Configuration, Reconfiguration, Filer, DIP. ), OLAP, OLTP, ETL and BI.
• Strong hands on experience using Teradata utilities (FastExport, MultiLoad, FastLoad, Tpump, BTEQ and QueryMan).
• Proficient in Teradata TD14.0/TD13.0/TD12.0 database design (conceptual and physical), Query optimization, Performance Tuning.
• Over 6+ years of industry experience in developing strategies for ETL (Extraction, Transformation and Loading) mechanism using Ab Initio tool in complex, high volume Data Warehousing projects in both Windows and UNIX.
• Strong hands on experience with Ab Initio GDE (3.1.1/1.15/1.14/1.13), Co>Op (3.1.1/2.15/2.14/2.13/2.12/2.11).
• Well versed with various AB Initio parallelism techniques and implemented Ab Initio Graphs using Data parallelism and MFS techniques.
• Expertise in testing the Ab Initio graphs and Teradata jobs by using JILs and scheduling in Autosys.
• Configured Ab Initio environment to talk to database using db config, Input Table, Output Table, Update table Components.
• Developed various UNIX shell scripts to run Ab Initio and Data base jobs
• Expert in designing Star Schema and well versed with UNIX shell wrappers, KSH and Oracle PL/SQL programming.
• Good Knowledge in Dimensional Data modeling, Star/Snowflake schema design, Fact and Dimensional tables, Physical and Logical data modeling.
• Experience with business intelligence reporting tools using Business Objects, Cognos and Hyperion.
• Experience in supporting large databases, troubleshooting the problems.
• Experience in all phases of SDLC like system analysis, application design, development, testing and implementation of data warehouse and non-data warehouse projects

PROFESSIONAL EXPERIENCE:

Confidential, Charlotte, NC

Application Developer

Responsibilities:

• Development of scripts for loading data into base tables in EDW and to load the data from source to staging and staging area to target tables using FastLoad, MultiLoad and BTEQ utilities of Teradata.
• Well versed with using Teradata Manager Options such as PMON, Dashboard, Trending and Usage reports, Teradata Administrator etc.
• Writing scripts for data cleansing, data validation, data transformation for the data coming from different source systems.
• Performed application level DBA activities creating tables, indexes, monitored and tuned Teradata BETQ scripts using Teradata Visual Explain utility.
• Written complex SQLs using joins, sub queries and correlated sub queries. Expertise in SQL Queries for cross verification of data.
• Developed the Teradata Macros, Stored Procedures to load data into Incremental/Staging tables and then move data from staging into Base tables.
• Performed Space Management for Perm & Spool Space.
• Reviewed the SQL for missing joins & join constraints, data format issues, mis-matched aliases.
• Developed procedures to populate the customer data warehouse with transaction data, cycle and monthly summary data, and historical data.
• Dealt with initials, delta and Incremental data as well Migration data to load into the Teradata.
• Analyzing data and implementing the multi-value compression for optimal usage of space.
• Query Analysis using Explain for unnecessary product joins, confidence factor, join type, order in which the tables are joined.
• Very good understanding of Database Skew, PPI, Join Methods and Join Strategies, Join Indexes including sparse, aggregate and hash.
• Used extensively Teradata Analyst Pack such as Teradata Visual Explain, Teradata Index Wizard and Teradata Statistics Wizard.
• Tuning of Teradata SQL statements using Explain analyzing the data distribution among AMPs and index usage, collect statistics, definition of indexes, revision of correlated sub queries, usage of Hash functions, etc…
• Flat files are loaded into databases using FastLoad and then used in the queries to do joins.
• Use SQL to query the databases and do as much crunching as possible in Teradata, using very complicated SQL Query optimization (explains plans, collect statistics, data distribution across AMPS, primary and secondary indexes, locking, etc.) to achieve better performance
• Excellent experience in performance tuning and query optimization of the Teradata SQLs.
• Developed mappings in Ab Initio to load the data from various sources using various Ab Initio Components such as Partition by Key, Partition by round robin, Reformat, Rollup, Join, Scan, Normalize, Gather, Merge etc.
• Implemented Data parallelism by using Multi-file System, Partition and De-partition components and also preformed repartition to improve the overall performance
• Extensively used Partition components and developed graphs using Write Multi-Files, Read Multi-Files, Filter by Expression, Run Program, Join, Sort, Reformat, and Dedup.
• Used Data profiling task to identify problems in the data that have to be fixed.
• Performed validations, Data Quality checks and Data profiling on incoming data.
• Used Enterprise Meta Environment (EME) for version control, Control-M for scheduling purposes.
• Used AIR commands to do dependency analysis for all ABI objects
• Testing and tuning the Ab Initio graphs and Teradata SQL’s for better performance
• Developed UNIX shell scripts to run batch jobs in Autosys and loads into production.
• Providing suggestions for best join plans while visualizing SQL queries with Visual Explain and Explain while recommending best Join Indexes such as Single table or multi table join indexes.
• Supporting jobs running in production for failure resolution with tracking failure reasons and providing best resolution in timely manner.
• Provide 24*7 production support for the Teradata ETL jobs for daily, Monthly and Weekly Schedule.

Confidential, Newark, DE

Teradata Application Developer

Responsibilities:

• Involved in full Software Development Life Cycle (SDLC) - Business Requirements Analysis, preparation of Technical Design documents, Data Analysis, Logical and Physical database design, Coding, Testing, Implementing, and deploying to business users.
• Providing technical support/guidance to the offshore team to address complex business problems.
• Involved in gathering business requirements, logical modelling, physical database design, data sourcing and data transformation, data loading, SQL and performance tuning.
• Defining the schema, staging tables, and landing zone tables, configuring base objects, foreign-key relationships, complex joins, and building efficient views.
• Expertise in writing scripts for Data Extraction, Transformation and Loading of data from legacy systems to target data warehouse using BTEQ, FastLoad, MultiLoad, and Tpump.
• Performed Query Optimization with the help of explain plans, collect statistics, Primary and Secondary indexes. Used volatile table and derived queries for breaking up complex queries into simpler queries.
• Extracted data from DB2 database on Mainframes and loaded it into SET and MULTISET tables in the Teradata database by using various Teradata load utilities. Transferred large volumes of data using Teradata FastLoad, MultiLoad, and T-Pump.
• Architected and developed FastLoad and MultiLoad scripts developed Macros and Stored procedures to extract data, BTEQ scripts to take the date range from the database to extract data.
• Created JCL scripts for calling and executing BTEQ, FastExport, Fload, and Mload scripts.
• Developed Teradata BTEQ scripts to implement the business logic and work on exporting data using Teradata FastExport.
• Wrote highly complex SQL to pull data from the Teradata EDW and create AdHoc reports for key business personnel within the organization.
• Created data models for information systems by applying formal data modeling techniques.
• Strong expertise in physical modeling with knowledge to use Primary, Secondary, PPI, and Join Indexes.
• Designed Fact tables and Dimension tables for star schemas and snowflake schemas using ERWIN tool and used them for building reports.
• Performed reverse engineering of physical data models from databases and SQL scripts.
• Provided database implementation and database administrative support for custom application development efforts.
• Performance tuning and optimization of database configuration and application SQL by using Explain plans and Statistics collection based on UPI, NUPI, USI, and NUSI.
• Involved in comprehensive end-to-end testing- Unit Testing, System Integration Testing, User Acceptance Testing and Regression.
• Running and monitoring daily scheduled jobs by using Work Load manager for supporting EDW (Enterprise Data Warehouse) loads for History as well as incremental data.
• Investigating failed jobs and writing SQL to debug data load issues in Production.
• Writing SQL Scripts to extract the data from Database and for Testing Purposes.
• Familiar with Agile software methodologies (scrum).

Confidential, Charlotte, NC

Application Developer

Responsibilities:

• Involved in understanding the Requirements of the End Users/Business Analysts and developed strategies for ETL processes.
• Extracted data from DB2 database on Mainframes and loaded it into SET and MULTISET tables in the Teradata database by using various Teradata load utilities. Transferred large volumes of data using Teradata FastLoad, MultiLoad, and T-Pump.
• Architected and developed FastLoad and MultiLoad scripts developed Macros and Stored procedures to extract data, BTEQ scripts to take the date range from the database to extract data.
• Created JCL scripts for calling and executing BTEQ, FastExport, Fload, and Mload scripts.
• Developed Teradata BTEQ scripts to implement the business logic and work on exporting data using Teradata FastExport.
• Worked on creation of logical and physical modeling for the different subject areas using Erwin.
• Created Teradata physical models by identifying PI, PPI, De-normalization and indexing techniques.
• Wrote highly complex SQL to pull data from the Teradata EDW and create Adhoc reports for key business personnel within the organization.
• Created data models for information systems by applying formal data modeling techniques.
• Designed Fact tables and Dimension tables for star schemas and snowflake schemas using Erwin tool and used them for building reports.
• Designed data models both logical and physical such as 3NF, Associative entities.
• Involved in design and development complex ETL mappings and stored procedures in an optimized manner.
• Used Incremental Aggregation, And Persistent Cache in order to increase the performance
• Performed reverse engineering of physical data models from databases and SQL scripts.
• Provided database implementation and database administrative support for custom application development efforts.
• Performance tuning and optimization of database configuration and application SQL by using Explain plans and Statistics collection based on UPI, NUPI, USI, and NUSI.
• Developed mappings in Ab Initio to load the data from various sources using various Ab Initio Components such as Partition by Key, Partition by round robin, Reformat, Rollup, Join, Scan, Normalize, Gather, Merge etc.
• Implemented Data parallelism by using Multi-file System, Partition and De-partition components and also preformed repartition to improve the overall performance
• Developed graphs separating the Extraction, Transformation and Load process to improve the efficiency of the system.
• Extensively used Partition components and developed graphs using Write Multi-Files, Read Multi-Files, Filter by Expression, Run Program, Join, Sort, Reformat, and Dedup.
• Developed OLAP reports and Dashboards using the Business intelligence tool - OBIEE.
• Involved in comprehensive end-to-end testing- Unit Testing, System Integration Testing, User Acceptance Testing and Regression.
• Provided 24/7 On-call Production Support for various applications and provided resolution for night-time production job abends, attend conference calls with business operations, system managers for resolution of issues.

Confidential, OR

ETL Developer

Responsibilities:

• Involved in Requirement gathering, business Analysis, Design and Development, testing and implementation of business rules.
• Developed mappings to load data from Source systems like oracle, SAP BI, SQL SERVER to Data Warehouse.
• Writing MultiLoad scripts, FastLoad and BTEQ scripts for loading the data into stage tables and then process into BID.
• Dealt with Incremental data as well Migration data to load into the Teradata.
• Created proper Teradata Primary Indexes (PI) taking into consideration of both planned access of data and even distribution of data across all the available AMPS. Considering both the business requirements and factors, created appropriate Teradata NUSI for smooth (fast/easy) access of data.
• Implemented different performance features for several Subject Areas by choosing the right Primary Indexes, ETL level statistics collection, Column Compressions. Also implemented Partitioning on the periodic monthly and Daily tables to increase the performance of range queries.
• Collected Multi-Column Statistics on all the non-indexed columns used during the join operations & all columns used in the residual conditions.
• Write Unix shell script to perform ETL interfaces (BTEQ, MLoad, Fastload, and FastExport jobs)
• Troubleshooting an export, import, or undo-import (cleanup) operation of a session using the TSET log file and summary reports
• Worked on exporting data to flat files using Teradata FastExport
• Analyzed the Data Distribution and Reviewed the Index choices
• In-depth expertise in the Teradata cost based query optimizer, identified potential bottlenecks
• Worked with PPI Teradata tables and was involved in Teradata specific SQL fine-tuning to increase performance of the overall ETL process
• Debugging and monitoring the code using GDB commands
• Involved in designing the data flow diagram. Documented the mappings used in ETL processes
• Participated in Logical and Physical Data modeling. Identified the Entity types, their attributes and the relationship between the Entities in the organization’s business process.
• Extensively used UNIX Shell Scripting for writing SQL execution scripts in Data Loading Process.
• Written SQL scripts which are used in Database Components of Ab Initio to extract the data from different source tables and to load the target table using Update Table and Output Table components with the support of Config (.cfg) file in graphs.
• Used Ab Initio components like Reformat, Input file, Output file, Join, Sort, Partition By key, Normalize, Input Table, Output Table, Update Table, Gather Logs and Run SQL for developing graphs
• Worked on Data enrichment and Data Standardization for Quality check data coming from various source systems (i.e., Oracle, Flat files.)
• Sorted the Extraction from heterogeneous source systems, like Oracle, internal and external flat files and building of the Transformations and Loading formatted data into the Multi-file and Serial files during the intermediate and the final stages of the processes (ETL) using Ab Initio.
• Used Enterprise Meta Environment (EME) for version control.
• Executed the test scripts and unit testing of EDW/DM using Co>Operating system.
• Involved on the creation of IVR application to support the authentication of existing customers.
• Used Ab Initio GDE to create graphs for the generation of the Loan Level file without summaries and to summarize loan-level data records.
• Created UNIX Shell scripts (wrapper scripts) to be invoked using Autosys.
• Extensively used the Ab Initio tool’s feature of Component, Data and Pipeline parallelism.
• Configured the source and target database connections using .dbc files
• Generated DB configuration files (.dml, .cfg) for source and target tables and modified them according to the requirements.
• Used sandbox parameters to check in and checkout of graphs from repository Systems.

Confidential, OR

Teradata ETL Developer

Responsibilities:

• Used Ab Initio as ETL tool to pull data from source systems, cleanse, transform, and load data into databases.
• Involved in creating high level design and detailed design documents for Ab Initio graphs.
• Extensively involved in Ab Initio Graph Design, development and Performance tuning.
• Worked on the sales Data Mart for moving the enterprise data from a queue system using Continuous Flows.
• Involved in Unit testing (UAT) System testing and debugging during testing phase and used Test Director to keep track of bugs.
• Updated and inserted transactional data according to the business changes using Continuous Flows.
• Developed number of Ab Initio Graphs based on business requirements using various Ab Initio Components such as Partition by Key, Partition by round robin, Reformat, Rollup, Join, Scan, Normalize, Gather, Merge etc
• Developed complex mappings using multiple sources and targets in different databases, flat files.
• Developed BTEQ scripts for Teradata.
• Automated Workflows and BTEQ scripts using scheduling tool Autosys.
• Responsible for tuning the performances of Informatica mappings and Teradata BTEQ scripts.
• Used Repository Server Administration Console to create and backup Repositories.
• Worked with DBAs to tune the performance of the applications and Backups.
• Writing UNIX Shell Scripts for processing/cleansing incoming text files.
• Collected Multi-Column Statistics on all the non-indexed columns used during the join operations & all columns used in the residual conditions.
• Created secondary index on conditional columns to improve the performance of the query.
• Used Autosys, CVS as a versioning tool.
• Performed Unit testing, Integration testing and generated various Test Cases.
• Migrated data with help of Teradata FastExport, Insert/ Select, flat files from one system to another.
• Performance tuning, monitoring and index selection while using PMON, Teradata Dashboard, Statistics wizard and Index wizard and Teradata Visual Explain to see the flow of SQL queries in the form of Icons to make the join plans more effective and fast.
• Extensively used Teradata Manager, Teradata Query Manager, and Teradata administrator etc. to manage system in prod, test, and development environments.
• Providing suggestions for best join plans while visualizing SQL queries with Visual Explain and Explain while recommending best Join Indexes such as Single table or multi table join indexes.

Confidential, SC

Teradata ETL Developer

Responsibilities:

• Involved in understanding the Requirements of the End Users/Business Analysts and Developed Strategies for ETL processes.
• The project involved extracting data from various sources, then applying the transformations before loading the data into target (warehouse) Stage tables and Stage files.
• Created the mappings using transformations such as the Source Qualifier, Aggregator, Expression, Lookup, Router, Filter, and Update Strategy.
• Used the following components of Ab Initio in creating graphs. Dataset components (Input file, output file, lookup file, and intermediate file), Database components (Input table, output table, RunSql, Truncated Table), Transform Components (Aggregate, Dedup Sorted, Filter by Expression, Join, Normalize, Reformat, Rollup and Scan Components), Partitioning Components (Broad Cast, partition by expression, partition by key, partition by round robin), Gatherlogs, Redefine format, Replicate, Runprogram components.
• Extensively used the Ab Initio tool’s feature of Component, Data and Pipeline parallelism.
• Configured the source and target database connections using .dbc files
• Used BTEQ and SQL Assistant (Query man) front-end tools to issue SQL commands matching the business requirements to Teradata RDBMS.
• Implemented star-schema models for the above data marts. Identified the grain for the fact table. Identified and tracked the slowly changing dimensions and determined the hierarchies within the dimensions.
• Worked with DBA team to ensure implementation of the databases for the physical data models intended for the above data marts.
• Created proper Teradata Primary Indexes (PI) taking into consideration of both planned access of data and even distribution of data across all the available AMPS. Considering both the business requirements and factors, created appropriate Teradata NUSI for smooth (fast and easy) access of data.
• Adapted agile software development methodology to ETL the above data marts.
• Did the performance tuning for Teradata SQL statements using Teradata Explain command.
• Extensively worked under the UNIX Environment using Shell Scripts.

TECHNICAL SKILLS:

Teradata Tools: ARCMAIN, BTEQ, Teradata SQL Assistant, Teradata Manager, PMON, Teradata Administrator.

ETL Tools: Ab Initio (GDE3.1.1/3.0/1.15/1.14/1.13) Co-op 3.1.1/3.0/2.15/2.14/2.13/2.12/2.11), Informatica Power Center 6.2/7.1/7.1.3, SSIS

DB Tools: SQL*Plus, SQL Loader, TOAD 8.0, BTEQ, Fast Load, Multiload, FastExport, SQL Assistant, Teradata Administrator, PMON, Teradata Manager

Databases: Teradata 14.0/13.0/12.0/V2R6.2/V2R5, Oracle 10g, DB2, MS-SQL Server 2000/2005/2008, MS-Access, Oracle

Scheduling Tools: Autosys, Tivoli Maestro, CVS

Version Control Tools: Clear Case, TFS

Programming Languages: C, C++, Java, J2EE, Visual Basic, SQL, PL/SQL and UNIX Shell Scripting

Data Modelling/Methodologies: Logical/Physical/Dimensional, Star/Snowflake, ETL, OLAP, Complete Software Development Cycle. ERWIN 4.0

Operating Systems: Sun Solaris 2.6/2.7/2.8/8.0,Linux, Windows, UNIX