- IT Professional with 8+ years of strong experience in performing ETL operations like Data Extraction, Data Transformation and Data Loading with Informatica Power Center 9.x, 8.x, 7.x.
- Experience in all the phases of the Data Warehouse project life cycle.
- Strong experience in Understanding Logical and Physical Data Modeling, Dimensional Data Modeling - Star Schema/Snow Flake modeling, Fact and Dimension Tables.
- Experience on Informatica components like transformations, reusable transformations, mappings, Mapplets, Worklets and workflows.
- Extensive knowledge of various kinds of Informatica Transformations such as Source Qualifier, Aggregate, Lookup (Connected & Un Connected), Rank, Joiner, Filter, Router, Sorter, Sequence, Union, Update Strategy, Stored Procedure, Java, Normalizer and xml Transformations.
- Experience on different Databases like Oracle, Teradata, MySQL, DB2 and SQL Server.
- Hands on experience with Teradata utilities like BTEQ, Fast Export, Fast Load, and Multi Load to export and load data to/from different source systems including flat files.
- Experience with Tuning Teradata and other DB queries.
- Experience in Teradata upgrade process from TD 12/TD 14.
- Good Experience with Change Data Capture for pulling Delta data.
- Experience in writing efficient SQL queries, PL/SQL Scripts and fine-tuning queries.
- Extensively experience in creating Mappings using Informatica Designer and processing tasks using Workflow Manager to configure data flows from multiple data sources (Relational, Flat files, XML Files & Application) to targets.
- Experience in maintenance and enhancement of existing ETL Process, and improving loading process for faster efficiency.
- Extensive experience in writing UNIX Shell Scripts.
- Ability to work in groups as well as independently with minimum supervision with ability to quickly adapt to new technologies and project environments.
D ata Warehousing: Informatica Power Center 9.6.1/9.5.1 / 9.1.1/8.6.1 /8.5/8.1.1/7.1.2, IDQ
Data Analysis: Requirement Analysis, Business Analysis, detail design, data flow diagrams, data definition table, Business Rules, data modeling, Data Warehousing, system integration.
Data Modeling: Dimensional Data Modeling (Star Schema, Snow-Flake, FACT-Dimensions), Conceptual, Physical and Logical Data Modeling, ER Models, OLAP, OLTP concepts.
Databases: Oracle 12C/11g/10g/9i/8i, Teradata 15/14/12, MY SQL, DB2, SQL Server 2012/2008, Sybase
Reporting: Cognos, Business Object, Dash Board
Programming: SQL, PL/SQL, UNIX Shell Scripting, Java, XML
Environment: Windows, UNIX, Linux
Other: Agile & waterfall methodology, MS Office Tools, Visio
Confidential, Richardson, TX
Sr. ETL Informatica / Teradata Developer
- Worked on the requirements with Business Analyst and business users also involved in working with data modelers.
- Worked closely with data population developers, multiple business units and data solutions engineer to identify key information for implementing the Data warehouses.
- Analyzed logical and physical data models for Business users to determine common data definitions and establish referential integrity of the system.
- Parsed high-level design spec to simple ETL coding and mapping standards.
- Used Informatica power center as an ETL tool to create source/target definitions, mappings and sessions to extract, transform and load data into staging tables from various sources.
- Wrote Teradata BTEQs & as well Informatica mappings using TPT to load data from Staging to base.
- Fine-tuned Teradata BTEQs as necessary using explain plan and collecting statistics
- Extensively worked on data extraction, conversion loading from various sources including flat files, Oracle SQL Server, DB2, Syndicated File System IMS, HL 7, CCR and CCD.
- Worked on the various modules like Subscriber/Member, Groups, Enrollment, Claims, Billing, Accounting, Provider, MTM and Utilization Management.
- Worked on FACETS CTP (Claims Test Pro) and FACETS Testing
- Developed and tested all the backend programs, Informatica mappings and update processes
- Populated the Staging tables with various Sources like Flat files (Fixed Width and Delimited), Oracle, MySQL, and Informix.
- Created mappings using various Transformations such as Source qualifier, Aggregator, Expression, lookup, Router, Filter, Rank, Look up, Sequence Generator, java and Update Strategy.
- Created and used the Normalizer Transformation to normalize the flat files in the source data.
- Extensively built mappings with SCD1, SCD2 implementations as per requirement of the project to load Dimension and Fact tables.
- Used Evaluate expression options to validate and fix the code using Debugger tool while testing Informatica code.
- Handled initial (i.e. history) and incremental loads in to target database using mapping variables.
- Used Debugger to debug mappings and created breakpoints for better analysis of mappings.
- Worked with Workflow Manager for maintaining Sessions by performing tasks such as monitoring, editing, scheduling, copying, aborting, and deleting.
- Worked on performance tuning at both the Informatica level and Database as well by finding the bottlenecks.
- Worked on Maestro job scheduling and Unix Scripting.
- Developed UNIX shell scripts to run the pmcmd functionality to start and stop sessions, batches and scheduling workflows.
- Involved in migrating the ETL Code to different environments from Dev to UAT and then to Production with ETL Admins.
- Performed Unit testing and created unit test plan of the code developed and involved in System testing and Integration testing as well.
- Coordinated with the testers and helped in the process of integration testing.
- Heavily involved in Production support on rotational basis and supported DWH system using the ticketing tool for the issues raised by Business users.
Environment: Informatica Power Center 9.6.1/9.5.1, Oracle, MySQL, Informix, Teradata 14/13/12, Flat File, Cognos, Rally, Putty, Maestro/Informatica Scheduler, UNIX, Windows
Confidential, Boston, MA
Sr. ETL Informatica Developer/ Data Analyst
- Involved in all phases of SDLC from requirement, design, development, testing and support for production environment.
- Extensively used Informatica Client tools like Informatica Repository Manager, Informatica Designer, Informatica Workflow Manager and Informatica Workflow Monitor.
- Used Teradata utilities Fast Load, Multi Load, TPump to load data.
- Created Sources, Targets in shared folder and developed re-usable transformations, Mapplets and user defined function (UDF) to re-use these objects in mappings to save the development time.
- Developed mappings using Mapping Designer and worked with Aggregator, Lookup (connected and unconnected), Filter, Router, Joiner, Source Qualifier, Expression, Stored Procedure, Sorter and Sequence Generator transformations.
- Created mappings which involved Slowly Changing Dimensions Type 1 and Type 2 to implement business logic and capturing the deleted records in the source systems.
- Involved in migration projects to migrate data from data warehouses on Oracle/DB2 and migrated those to Teradata.
- Worked on high volume data sets from various sources like Oracle, Text Files and Teradata Tables
- Used debugger extensively to identify the bottlenecks in the mappings.
- Modified PL/SQL stored procedures for Informatica mappings.
- Created Sessions and Workflows to load data from the SQL server, flat file and Oracle sources that exist on servers located at various locations all over the country.
- Configured the session properties i.e. high value for commit intervals to increase the performance.
- Involved in unit testing, Integration testing and User acceptance testing of the mappings.
- Involved in Migrating the Informatica objects using Unix SVN from Dev to QA Repository.
- Worked on developing workflows and sessions and monitoring them to ensure data is properly loaded on to the target tables.
- Responsible for scheduling workflows, error checking, production support, maintenance and testing of ETL procedures using Informatica session logs.
- Performance tuning on sources, targets mappings and SQL (Optimization) tuning.
Environment: Informatica Power Center 9.1.1/8.6.1, Flat Files, Oracle 11g, Teradata 12/13, SQL, PL/SQL, TOAD, SQL Assistant, Windows XP, Unix, Maestro, SVN
Confidential, Greensboro, NC
ETL Informatica Developer
- Extensively worked on Informatica Designer, Workflow Manager, and Workflow Monitor as a senior Informatica Developer.
- Extracted data from Oracle, SFDC and Flat files and loaded data into Oracle Data warehouse
- Worked with Source Analyzer, Data Warehouse Designer, Workflow monitor, Mapping Designer, Mapplets, and Transformation Developer in Informatica designer
- Developed several complex Mappings, Mapplets and Reusable Transformations to facilitate daily weekly and monthly loading of Data
- Extensively used transformations like Aggregator, Expression, Sorter, Sequence Generator, Joiner, Filter, Router, Rank, look up and Update Strategy transformations to model various standardized business processes
- Used Informatica features to implement Type I, II changes in slowly changing dimension tables
- Created Data Breakpoints and Error Breakpoints for debugging the mappings using Debugger
- Created Workflow, Worklets, Assignment, Decision, Event Wait and Raise and Email Task, scheduled Task and Workflow based on Client requirement
- Participated in reconciling data drawn from multiple systems across the company like Oracle, flat files into Oracle data warehouse
- Involved in data Transformations between different Informatica folders and also Repositories along with DBA team.
- Generated Informatica Mappings to populate the data into dimension and Fact tables for data classifications to end developers.
- Used different transformations like Aggregator, Lookup (connected and unconnected), Filter, Expression, Router, Update Strategy and Sequence Generator for data transformations.
- Created and Configured Workflows, Work lets and Sessions to transport the data to target warehouse Oracle tables using Informatica Workflow Manager.
- Developed reusable transformations to load data from various data sources to the DW.
- Fixing invalid Mappings, testing of Stored Procedures and Functions, Unit and Integration Testing of Informatica Sessions, Batches and Target Data.
- Scheduled Informatica sessions and workflows using Informatica Scheduler in Business process as Requested in SLA.
- Performed extensive debugging and performance tuning of mappings, sessions and workflows including partitioning, memory tuning and cache management.
- Created and maintained various project related documents like high level design documents etc.
- Created, executed and documented associated test cases.
Environment: Informatica Power Center 9.1.1/8.6.1 (Informatica Designer, Workflow Manager, Workflow Monitor), Oracle 10g, Oracle 11g, Flat files, ODBC, Windows NT, UNIX, Shell Scripts, Toad 7.5, Apex explorer/loader, SQL
Confidential, Cincinnati, OH
ETL Informatica Developer
- Participated in Functional Meetings to understand various Source systems to be consolidated and involved in designing the data model for the Warehouse.
- Designed the procedures for getting data from all systems, like IMS to Data Warehousing system. The data was standardized to store various Business Units in tables.
- Worked closely with Business Analysts to Design & Develop SQL Statements, Data Conversions, and Data Cleansing, Data Quality Analysis to determine cleansing requirements.
- Used Oracle, SQL Server, DB2 and Flat Files as sources and SQL Server 2005 as target.
- Worked on Coding and Debugging, Sorting out their time-to-time technical problems.
- Worked with almost all Transformations like Stored Procedure, Connected and Unconnected lookups, Update Strategy, Filter, Router, Expression, Aggregator, Joiner etc.
- Wrote simple and complex SQL-overrides in source qualifier as well as look-up transformations according to the Business Requirements.
- Developed Informatica mappings and tuned them for Better Performance and validate the data.
- Worked on Version Control in Informatica to maintain multiple versions of an object, control development on the object and track changes.
- Involved in Batch processing & Scheduling Informatica jobs on weekly/monthly basis using Maestro.
Environment: Informatica Power Center 8.5, Toad, SQL Server 2005, DB2 7.2, Oracle 9i, SQL, Windows XP
ETL Informatica Developer
- Discussed with Group and Technical Manager to identify the business issues to bridge the gap between user needs.
- Involved in Data model reviews and validated tables, columns and data types.
- Created shared folders, local and global shortcuts to reuse metadata.
- Configured database and ODBC connectivity for various sources and targets.
- Created ETL package for data conversion using various transformations tasks.
- Worked with various transformation types: Lookup, Update, Merge, Joiner, Filter, Sorter and Aggregation.
- Extracted Data from sources: SQL Server, and Flat files to load into ODS (staging area).
- Applied partitioning at session for data loads using target lookup and avoided duplicates records.
- Implemented Mapping Variables and Parameters in Transformations.
- Reduced complexity and time using re-usable components: Worklets, Mapplets, and transformations.
- Implemented Performance Tuning techniques on Sources, Targets, Mappings, and Workflows.
- Utilized Informatica tasks: Session, Command, Timer, Email, Event-Raise, and Event-Wait.
- Created Parameter file for session parameters and called in the sessions.
- Used Debugger wizard to remove bottlenecks at source, transformation, and target for optimum loads.
- Worked with session logs, Error Handling and validated data populated by Unit testing and Integration testing on mappings.
- Worked with QA team and fixed bugs that were reported and validated code in the mappings
Environment: Informatica Power Center 7.1.1/8.1.1, SQL Server 2000, Flat Files, Oracle, TOAD 8.0, Windows.