Qualifications Profile

Around *+ years of extensive experience in ETL (Extract Transform Load), Data Integration and Data Warehousing using Informatica, Teradata and Oracle technologies.

Very good understanding of Teradata’s MPP architecture such as Shared Nothing, Nodes, AMPs, BYNET, Partitioning, Primary Indexes etc.

Extensively created and used various Teradata Set Tables, Multi-Set table, global tables, volatile tables, temp tables.

Extensively used different features of Teradata such as BTEQ, Fastload, Multiload, SQL Assistant, DDL and DML commands. Very good understanding of Teradata UPI and NUPI, secondary indexes and join indexes.

Experience in Mainframe applications development. Mainframe skills include TSO, COBOL II, JCL, DB2, SQL, SPUFI, QMF, IMS, IDMS, CICS and VSAM.

Extensive knowledge in Business Intelligence and Data Warehousing Concepts with emphasis on ETL and System Development Life Cycle (SDLC).

Working Knowledge of Data warehousing concepts like Star Schema and Snowflake Schema, Data Marts, Kimball Methodology used In Relational, Dimensional and Multidimensional data modelling.

Extensive knowledge on Data Profiling using Informatica Developer tool.

Implemented Slowly changing dimension types (I, II &III) methodologies for accessing the full history of accounts and transaction information designed and developed change data capture solutions (CDC) for the project, which captures and analyses changes from daily feeds to maintain history tables.

Experience in Data Modeling involving both Logical and Physical modeling using DM tools Erwin and ER Studio. Created data models for both 3NF and Dimensional Data models including Star Schemas.

Proficient with the concepts of Data Warehousing, Data Marts, ER Modeling, Dimensional Modeling, Fact and Dimensional Tables with data modeling tools ERWIN and ER Studio.

Experience in Data Analysis, Data Profiling and Data Mapping. Used SQL and Data Profiler tools.

Proficiency in design and developing the ETL objects using Informatica Powercenter with various Transformations like Joiner, Aggregate, Expression, SQL, Lookup, Filter, Update Strategy, Stored Procedures, Router, Rank, normalizer transformations etc.

Involved in the POC of Big data solution to implement efficient summarization DW processes using Hadoop platform with Vertica as the DW database.

Involved in Data Migration projects from DB2 and Oracle to Teradata. Created automated scripts to do the migration using UNIX shell scripting, Oracle/TD SQL, TD Macros and Procedures.

Automated the BTEQ report generation using UNIX scheduling tools on weekly and monthly basis. Well versed with understanding of Explain Plans and confidence levels and very good understanding of Database Skew. Knowledge in Query performance tuning using Explain, Collect Statistics, Compression, NUSI and Join Indexes including Join and Sparse Indexes.

Extensively worked on PMON/Viewpoint for Teradata to look at performance Monitoring and performance tuning. Well versed with Teradata Analyst Pack including Statistics Wizard, Index Wizard and Visual Explain. Experience in programming with SQL and PL/SQL (Stored Procedures, Functions, Cursors, and Database Triggers).

Very good experience in Oracle database application development using Oracle 10g/9i/8i/x, SQL, PL/SQL, SQL Loader.

In-depth expertise in the Teradata cost based query optimizer, identified potential bottlenecks with queries from the aspects of query writing, skewed redistributions, join order, optimizer statistics, physical design considerations (PI/USI/NUSI/JI etc) etc.

Scheduling the automated jobs for daily, weekly and monthly jobs using UNIX Shell scripts for Autosys scheduling.

Usage of Analytical functions like CSUM, RANK, and QUALIFY to speed up order-based analytical queries.

Strong working experience on Data Warehousing applications, directly responsible for the Transformation, Extraction and Loading data of multiple formats into Data Warehouse.

Proficient in Database Performance Monitoring, SQL Query Tuning.

Familiar with working on MVS systems utilizing JCL.

Managed scheduling of large ETL systems using CTRL-M and Shell Scripts.

Coded well-tuned SQL and UNIX Shell scripts for high volume data warehouse instances.

Well versed with Data Warehousing concepts, design techniques and dimensional modeling

Extensive experience in designing and developing jobs using DataStage Designer, Data Stage Manager, DataStage Director and DataStage Debugger..

Good Knowledge of Data Modelling, Data Extraction, Data Integration and Data Mining for DSS as a Data Warehouse developer using ETL (Ab Initio, Data Stage, Informatica) and OLAP Tools.

Involved in Data Migration between Teradata, Oracle Peoplesoft, and MS SQL server.

Strong SQL experience in Teradata from developing the ETL with Complex tuned queries including analytical functions and BTEQ scripts.

Extensively used Mapping Variables, Mapping Parameters, and Dynamic Parameter Files for improved performance and increased flexibility and also worked with XML Sources & Targets.

Developing workflows with Worklets, Event waits, Assignments, Conditional flows, Email and Command Tasks using Workflow Manager.

Knowledge of push down optimization concepts and tuning Informatica objects for optimum execution timelines.

Experienced with identifying Performance bottlenecks and fixing code for Optimization in Informatica and Oracle.

Created UNIX shell scripts for Informatica post and pre session operations, database

Areas of Expertise

Data Warehousing and ETL Tools

Data Migration

Teradata Query Optimization

Data Quality

Data Analysis/Data Modeling

Education

Bachelor of Science & Technology, JNTU, India

Technical Acumen

Primary Tools:

Informatica Power Center 9.5/9.1/8.6, Ab Initio (Co>Op 3.0.3.9/2.15/2.14, GDE 3.0.4/1.15/1.14), IBM Information Server 9.1/8.5/8.0.1, Teradata 14.0/13.0, Teradata Tools and Utilities, Oracle 10g/9i, MS SQL Server 6.5/7.0/2000

Languages:

Teradata SQL, COBOL, JCL, REXX, SQL, BTEQ

Teradata Utilities:

BTEQ, Fast Load, Multi Load, Tpt, SQL Assistant, Viewpoint, Query Monitor

Databases:

Teradata 14.10/14/13.10//13/12, Oracle 10g/9i, DB2/UDB, SQL Server

Operating Systems:

Windows 95/98/NT/2000/XP, UNIX, Linux, NCR MP-RAS UNIX

Data Modeling:

Erwin, ER Studio, Power Designer

Scheduling tools:

Control M, Autosys, UC4

Training

BigData – Hadoop, HDFS, HIVE, PIG, HBASE, Flume, SQOOP

Informatica 9.6.1

Datastage 8.1

Erwin/ER Studio/Power Designer

Teradata – SQL and Advanced SQL, Teradata Physical Design and Tuning

Certifications

Teradata Certified Professional

Teradata Certified SQL Specialist

Teradata Certified Implementation Specialist

Teradata Certified Design Architect

Professional Experience

AT&T, Irving, TX

Teradata Developer JUN 2016 Sep 2017

Environment: Teradata 14.10/14/13.10, Teradata SQL Assistant, Teradata Utilities (TPT, BTEQ, Multi load, Fast load), Informatica Power Center 9.1/9.6, Workflow Manager, Workflow Monitor, Warehouse Designer, Source Analyzer, Transformation developer, Mapplet Designer, Mapping Designer, Repository manager, UC4, Teradata Viewpoint, UNIX, Putty, Power Designer, Oracle Golden Gate, oracle SQL Developer.

Notable Contributions:

Involved in scrum (agile) implementation for this project.

Designed and developed data solutions to help the product and business teams make data driven decisions.

Worked closely with Business Systems Analyst’s (BSA’s) to get the requirements and translate them into technical requirements and deliver the solution to end users.

Lead end-to-end efforts, including design, development, and implementation, of the data integration process. Adhered to the best standards for naming conventions and coding practice to ensure consistency of data model.

Responsible for Performance Tuning of High CPU consuming queries and tables with high skew for daily batch jobs.

Interacted closely with data infrastructure and engineering teams to build and extend ETL processes.

Provided consultation to business partners such as analysts, management, end users, and developers to clarify objectives, determine scope, drive consensus, identify problems and recommend solutions.

Supported end users on ad hoc data usage and be a subject matter expert on functional side of the business.

Designed and developed Informatica’s Mappings and Sessions based on business user requirements and business rules to load data from oravle Golden Gate to flat files and then to target teradata tables.

Created reusable mapplets and Transformations starting concurrent batch process in server and did backup, recovery and tuning of sessions.

Designed and developed complex mappings to move data from multiple sources into a common target area such as Data Marts and Data Warehouse using lookups, Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, Normalizer, Sequence Generator, and Update Strategy from varied transformation logics in Informatica.

Performed Development using Teradata utilities to populate the data into BI DW like BTEQ, Fast Load,, Multi Load and Tpt

Written complex SQLs using joins, sub queries and correlated sub queries. Expertise in SQL Queries for cross verification of data.

Developed the Teradata BTEQ’s to load data into Incremental/Staging tables and then move data from staging into Base tables

Executing test scripts to verify actual results against expected results by using Oracle Golden Gate for source validation and Teradata for target validations.

Developed the FTP process from the Unix servers to the file services location for vendor delivery

Handled customer service data into the EDW for the BI team to generate reports for the end users.

Executed unit tests and validated expected results; iterating until test conditions has passed.

Utilized Teradata DBQL to monitor the queries running in production and modified for better SLA’s

Performed Teradata SQL assistant Import and Export utility to move data from production to Development to refresh staging tables.

Developed several jobs to improve performance by reducing runtime using different partitioning techniques.

Johnson & Johnson, Raritan, NJ

Teradata/Informatica Consultant Mar 2015 May 2016

Environment: Teradata 14.10/14/13.10, Teradata SQL Assistant, Teradata Utilities (TPT, BTEQ, Multiload, Fastload, FastExport), Informatica Power Center 9.1/9.6, Workflow Manager, Workflow Monitor, Warehouse Designer, Source Analyzer, Transformation developer, Mapplet Designer, Mapping Designer, Repository manager, Informatica Cloud, File aid, Control-M, Teradata PMON/Viewpoint, UNIX, SSH secure shell, TOAD, ERWIN.

Notable Contributions:

Performed analysis of source systems, business requirements and identification of business rules.

Worked closely with Business Intelligence Systems Analyst’s (BISA’s) to get the requirements and translate them into technical requirements and deliver the solution to end users.

Performed analysis and prepared documentation for the TD upgrade project from TD 13 to TD 14 and ensured successful transition to the new server.

Adhered to the best standards for naming conventions and coding practice to ensure consistency of data model.

Designed and developed Informatica’s Mappings and Sessions based on business user requirements and business rules to load data from source flat files and oracle tables to target tables.

Created reusable mapplets and Transformations starting concurrent batch process in server and did backup, recovery and tuning of sessions.

Responsible for Performance Tuning at the Mapping Level, Session Level, Source Level and the Target Level for Slowly Changing Dimensions Type1, Type2 for Data Loads.

Using various transformations like Filter, Expression, Sequence Generator, Update Strategy, Joiner, Stored Procedure, and Union to develop robust mappings in the Informatica Designer.

Analyzed and Identified the complex and under performing mappings which are right candidates for PDO. Implemented source-side Pushdown Optimization (PDO) to optimize performance issues of these complex mappings involving numerous transformations.

Implemented full pushdown Optimization (PDO) for Sematic layer implementation for some of the complex aggregate/summary tables instead of using the ELT approach.

Designed and developed complex mappings to move data from multiple sources into a common target area such as Data Marts and Data Warehouse using lookups, Source Qualifier, Router, Filter, Expression, Aggregator, Joiner, Normalizer, Sequence Generator, Update Strategy from varied transformation logics in Informatica.

Extensively worked on Informatica cloud intergration tool for moving data from Sales Force application and other source systems

Performed Development using Teradata utilities to populate the data into BI DW like BTEQ, FastLoad, BTEQ, FastExport, MultiLoad and Tpt

Developed various workflows to pull objects from Sales force application using Informatica Cloud.

Extracted data from BIDW and Application replication databases using BTEQ extracts

Executing test scripts to verify actual results against expected results by using Power Connect for source (DB2) validation and Oracle for target validations.

Developed the FTP process from the Unix servers to the file services location for vendor delivery

Handled data from SQL plus extracts to load the data into the Teradata tables using SQL loader

Handled STMS (call related information) data into the BIDW and generate reports for the Marketing campaign team to take intelligent decisions.

Executed unit tests and validated expected results; iterating until test conditions has passed.

Utilized Teradata Viewpoint to monitor the queries running in production and modified for better SLA’s

Performed Teradata SQL assistant Import and Export utility to move data from production to Development to refresh staging tables.

Written several SQL queries for the BI team for report generation and report formatting.

Developed several jobs to improve performance by reducing runtime using different partitioning techniques.

Involved in scrum (agile) implementation for this project.

M&T Bank, Buffalo, NY

Sr. Teradata Developer Apr 2014 – Dec 2014

Environment: Teradata 13.10/13, BTEQ, FastLoad, MultiLoad, Fast Export, Teradata SQL Assistant, OBIEE 11g/10g, DB2, ERwin R7.3, IBM Mainframes MVS/OS, JCL, TSO/ISPF, Changeman, SPUFI, FileAid, COBOL, ZEKE, DB2, UNIX, FTP.

Notable Contributions:

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.

Responsible for requirements gathering for an enhancement requested by client. Involved in analysis and implementation for an Intranet Based Information Management Information System.

Provided maintenance and support of Online and Batch Programs using COBOL, DB2, CICS, JCL.

Writing queries using SPUFI to extract data from various DB2 Views for reporting purpose

Converting the Table data from DB2 region to teradata region using fastload and multiload Utilities.

Responsible for Coding, Unit Test Plans, Unit Test Results, Functional Testing and Regression Testing.

Migrated mainframe DB2 data to Teradata for one of their critical application.

Synchronizing all regions PCR, Unit and System while migration changes from lower region to acceptance.

Wrote several DB2 Stored Procedure scripts to implement the business logic.

Handling Ad-Hoc Report Requests.

Reviewing programs for QA and Testing.

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.

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.

The Coca-Cola Company (Coke), Atlanta, GA

Informatica/Teradata Developer Nov 2012 – Mar 2014

Environment:Teradata 12 (FastLoad, MultiLoad, FastExport, BTEQ), Teradata SQL Assistant, Informatica Power Center 8.6, Unix, SQL, PL/SQL, Work Load Manager, MS Access, UNIX.

Notable Contributions:

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 and 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. Streamlined the Teradata scripts and shell scripts migration process on the UNIX box.

Worked on Informatica Power Center tools - Designer, Repository Manager, Workflow Manager, and Workflow Monitor.

Using various transformations like Filter, Expression, Sequence Generator, Update Strategy, Joiner, Stored Procedure, and Union to develop robust mappings in the Informatica Designer.

Developing as well as modifying existing mappings for enhancements of new business requirements mappings to load into staging tables and then to target tables in EDW. Also created mapplets to use them in different mappings.

Working on different tasks in Workflows like sessions, events raise, event wait, e-mail, command, worklets and scheduling of the workflow.

Creating sessions, configuring workflows to extract data from various sources, transforming data, and loading into enterprise data warehouse.

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.

Interacting with the Source Team and Business to get the Validation of the data.

Involved in Transferring the Processed files from mainframe to target system.

Supported the code after postproduction deployment.

Familiar with Agile software methodologies (scrum).

Allianz Life Insurance, Golden Valley, MN

Teradata Developer/ETL Developer Apr 2011– Oct 2012

Environment: Teradata 12, Informatica 8.6/8.1(Designer, Repository Manager, Workflow Manager, Workflow Monitor), Informatica 8x, Oracle 10G, UNIX, Citrix, Toad, Putty, PL/SQL Developer

Notable Contributions:

Development of scripts for loading the data into the 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. 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 and 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, casting errors.

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.

Used extensively Derived Tables, Volatile Table and GTT tables in many of the ETL scripts.

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

Use PMON, Teradata manager to monitor the production system during online day.

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.

Created checkpoints, phases to avoid dead locks and tested the graphs with some sample data then committed the graphs and related files into Repository from sandbox environment. Then schedule the graphs using Autosys and loaded the data into target tables from staging area by using SQL Loader.

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.

Involved in designing Load graphs using Ab Initio and Tuned Performance of the queries to make the load process run faster.

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.

Interaction with different teams for finding failure of jobs running in production systems and providing solution, restarting the jobs and making sure jobs complete in the specified time window.

Provide 24*7 production support for the Teradata ETL jobs for daily, Monthly and Weekly Schedule.

Contact this candidate