Senior Etl Consultant Resume
Raleigh, NC
SUMMARY:
- 10+ years of experience in IT with expertise in Informatica Power Center, PL/SQL programming (Oracle & Teradata) and Unix Shell Scripting.
- Experienced in designing and developing efficient ETL solutions to load huge volumes of data to/from Flat files, XML files & Relational DBs i.e. Oracle, Teradata, SQL Server & Netezza.
- Extensive experience in writing complex Oracle PL/SQL Packages, Stored procedures, Functions, Cursors and Triggers.
- Extensive working experience in the Analysis, Design, Development, Testing and Implementation phases of various Data Warehousing applications.
- Worked in both Waterfall & Agile methodologies including SCRUM.
- Good understanding of the various Data warehousing concepts like OLTP, OLAP, Data Modeling, Normalization, De - Normalization, Slowly Changing Dimensions etc.
- Experience in ETL Informatica Architecture i.e. Nodes, Domain, Services.
- Well-versed with ETL Informatica performance tuning process involving bottleneck identifications, analyzing thread statistics, optimizing components and using parallel partitions.
- Proficient in writing complex SQL Queries using sub-queries, co-related sub-queries, inline views, joins, aggregate & analytic functions.
- Expertise with Collections, Records, Dynamic SQL and Exception Handling.
- Extensive experience in performance tuning of SQL queries using Explain Plan and TKPROF utilities.
- Hands-on experience with Teradata utilities such as BTEQ, FastExport, FastLoad, MultiLoad, TPT &TPump.
- Working experience in Informatica Data Quality tool (IDQ) in implementation of the data profiling, creating scorecards and documenting DQ metrics/dimensions for the accuracy, completeness, duplication, validity & consistency.
- Hands-on experience with Data Modelling tool ERwin to design the Logical and Physical models for the OLTP and OLAP applications.
- Proficient in writing efficient Unix Shell scripts. Developed various shell scripts to automate manual tasks &generate batch status reports.
- Experienced in scheduling tools Autosys & Control-M to create/modify batch jobs, enable alerts and set up the calendars.
- Designed & developed various macros using Microsoft Excel VBA to validate, import and export the Informatica objects to/from the repositories.
- Proficient in debugging and trouble-shooting of any technical and performance issues.
- Excellent written and verbal communication skills with enthusiasm to learn new tools and technologies.
TECHNICAL SKILLS:
ETL Tools: Informatica Power Center 10.x/9.x/8.x/7.x
DQ Tools: Informatica Data Quality 9.x
Databases: Oracle11g/10g/9i/8i, Teradata 12/13/14SQL Server 2012/2008/2005, Netezza 7.x
Database Tools: Teradata SQL Assistant, SQL Developer, TOAD, SSMS
Reporting Tools: Business Objects XI R2
Data Modelling Tools: ERwin 7.x
Languages: C, C++, Java, C#, Perl, HTML, XML, SQL, T-SQL, PL/SQL, Shell Scripting, Excel VBA
Operating Systems: Windows, UNIX, AIX, Red Hat Linux & Sun Solaris
PROFESSIONAL EXPERIENCE:
Confidential, Raleigh, NC
Senior ETL Consultant
Responsibilities:
- Business requirement gathering, Impact Analysis & Effort Estimations for the various stories implemented using Agile Methodology
- Preparation of the ETL design documents (HLD & LLD) using the mapping specification document
- Development of the complex ETL flows using Informatica Power Center 10.1
- Development of the complex SQL queries for the underlying ETLs using sub-queries & Analytic functions
- Development of various Oracle objects i.e. tables, views, indexes, partitions, functions & Stored procedures
- Designed an efficient ETL process to load the historical claims of volume around 8 billion
- Fined tuned the workflows using Bulk Loads, Informatica & Database partitions
- Development of various shell scripts to archive, compress & merge the files
- Design and development of the batch jobs for the ETLs using CA Workstation
- Lead the deliverables from Onshore & assignment of the work to the offshore counterparts
- Developed the architecture for the traceability of the various historical one shots
- Prepare the deployment plan and post- implementation plan
- Troubleshoot the production issues and implement the bug-fixes
- Preparation of technical documents required for the knowledge transition
Environment: Informatica Power Center 10.1, Oracle 11g, SQL Server 2012, Flat Files, Linux 2.6, CA Workstation 11, TortoiseSVN 1.7, Toad 12.6, Putty
Confidential, Charlotte, NC
Senior ETL Consultant
Responsibilities:
- Gathering the business requirements from the Users for the various products i.e. Deposits, Loans, Mortgages, Credit Cards, Derivatives etc.
- Developed the ETL solutions to load the Transactional data and Reference data for the corresponding products.
- Created various Informatica mappings to validate the transactional data against Business rules, extract look up values and enrich the data as per the mapping documents.
- Developed various Informatica Workflows to load the data from various upstream systems using different methodologies i.e. trigger based pull, direct pull & file based push.
- Designed the ETL architecture for the Deposits product to process huge volumes of Deposits data(~100M) on daily basis.
- Implemented various techniques for the faster processing of high volume data by creating parallel partitions and using Teradata Fast Export and Bulk Loads.
- Developed various SQL queries using join, sub-queries & analytic functions to pull the data from various relational DBs i.e. Oracle, Teradata & SQL Server.
- Created complex Datamart views for the corresponding products.
- Created various complex PL/SQL stored procedures to manipulate/reconcile the data and generate the dashboard reports.
- Performed Unit Testing & prepared the deployment plan for the various objects by analyzing the inter dependencies.
- Developed several UNIX shell scripts for the files Archival & Compression.
- Created various Autosys jobs for the scheduling of the underlying ETL flows.
- Co-ordinated with various team members across the globe i.e. Application teams, Business Analysts, Users, DBA and Infrastructure team to resolve any technical and functional issues in UAT and PROD.
- Created various technical documents required for the knowledge transition of the application which includes re-usable objects (Informatica & UNIX) and holiday processing of the feeds.
Environment: Informatica Power Center 9.6, Oracle 11g, Teradata 14.10, Netezza 7.1, Flat Files, RHEL 2.1, AutoSys, Toad 12, Tortoise SVN, Teradata SQL Assistant, Putty
Confidential, Raleigh, NC
Sr ETL Developer
Responsibilities:
- Requirements gathering from the Business Users and Impact Assessment.
- Responsible for design and development of the ETL process to load the data for the various Financial products (Equities, Bonds, Currencies, Derivatives etc.) from multiple sources.
- Created several complex Informatica mappings to validate the data against various Business rules defined in the metadata tables, transform & load the data.
- Developed ETL solution to implement the incremental load of the daily transactional data.
- Created various Oracle DB objects such as tables, views, partitions, indexes & triggers.
- Worked with SQL*loader to load the data into Oracle tables from various flat files
- Developed various PL/SQL procedures using Records and Collections, Bulk Bind, Ref Cursors, Nested tables and Dynamic SQL.
- Tuned the performance of various SQL queries using Explain Plans and SQL*TRACE.
- Developed several UNIX shell scripts for file transfer/archival/Email notifications.
- Created and modified several batch jobs (Control-m) for scheduling of the ETL flows.
- Perform Unit testing of the code and provide technical and business support for the various issues raised in the UAT environment.
- Developed a Perl script to parse the structure the various source files from upstream and validate it against the defined structure in the metadata files
- Automated the process of generating various ad-hoc reports by storing the underlying SQLs in the DB table and executing them dynamically using Shell Script.
- Automated the batch status reports generation using UNIX Shell scripting
- Enhanced and implemented the bug fixes to the various Anti-fraudulent & Financial Market compliance models implemented using Actimize tool.
- Prepared various technical documents required for the transition to the PROD Team.
Environment: Informatica Power Center 9.5, Oracle 10g/9i, DB2, PL/SQL, Sun Solaris, Control-M, Toad, Actimize, Toad, Putty
Confidential
ETL Developer
Responsibilities:
- Participated in requirement gathering meetings with Business Analysts and Users to understand the Source Data, Data Warehouse data model, Technical Metadata, etc.
- Worked on ERwin tool to design the Logical and Physical models.
- Used forward engineering approach to designing and create the DB objects
- Worked on IDQ to build scorecards and the share the data quality results to business users.
- Designed the data quality rules for the error handling mechanism of the ETL Process
- Developed various complex Informatica workflows to perform ETL operations.
- Worked with XSD and XML files (sources & targets) to read & parse the data and load into the target DB.
- Enhanced the performance the Informatica Workflows by optimizing the Look-up caches and implementing push-down optimization.
- Created various Oracle objects (tables, views, synonyms, indexes, partitions, functions, triggers etc.).
- Developed various PL/SQL procedures using cursors, records, collections & dynamic SQL.
- Developed the Unit test case documents and the test strategies for the ETL workflows.
- Created a shell script for the dynamic generation of parameter file for Informatica objects to load the data from various source systems into the target DB.
- Designed and developed various Control-M batch jobs to schedule the ETL workflows.
- Enhanced SSIS packages to extract, transform and the load the data into the SQL Server DB for a standalone application.
- Designed& customized the Tableau reports used by the Business Users in Production.
- Involved in code reviews as per ETL/Informatica standards and best practices.
- Automated the batch status reports generation using UNIX Shell scripting
Environment: Informatica Power Center 9.1, Oracle 10g/9i, PL/SQL, Sun Solaris, Flat Files & XML Files, ERwin 8, Control-M, Toad, Tortoise SVN
Confidential
ETL Developer
Responsibilities:
- Business requirements gathering for the various Investment Banking Products.
- Worked with IDQ to analyze the data for the products to check accuracy, completeness, duplication, validity, consistency
- Developed various Informatica mappings & mapplets to read the data from various source systems, validate the data against the complex business rules and the load the data into the Global Ledger.
- Designed and developed the ETL logic to process multiple files for a single product using Indirect loading and generating multiple files for the same product using Transaction Control transformation.
- Developed the Teradata Metadata entries, indexes and partitions for the various feeds sourced into the Global Ledger.
- Worked on the Teradata Utilities BTEQ, Fast Export, Fast Load and Multi Load to extract and load huge volumes of data into the GGL.
- Created a complex view to look up the static data for the various feeds from the database table maintained by Business Users.
- Implemented concurrent workflow option to execute large number of multiple workflows concurrently.
- Fine-tuned various long-running feeds by implementing Informatica partitions, splitting the Look up cache, using Persistent caches and enabling SONG feature (Session on Grid).
- Designed and developed Unix Shell scripts to generate the run time parameters for the underlying ETL Informatica workflows from the DB Metadata entries.
- Perform unit testing and provide support to the users for the defects raised in the UAT environment
- Co-ordinated with Business users, DBA teams & other stakeholders for any issues identified in UAT and work on the resolution.
- Created a deployment tool using shell scripting to dynamically export and import the tools from the Informatica Repository onto the Unix & Windows File System.
- Involved actively in the Informatica upgrade from 8 to 9 by executing the workflows in both environments and comparing the record count and timelines between the both.
Environment: Informatica Power Center 8.6, Oracle 10g, Teradata 12, SQL Server 2008, PL/SQL, RHEL, Autosys, Toad, SQL Server Management Console, Teradata SQL Assistant, Putty, Tortoise SVN
Confidential
Junior ETL Developer
Responsibilities:
- Worked on Erwin tool to design the database objects for the application.
- Developed Informatica Mappings and workflows to load the data for various products into the Data Warehouse.
- Implemented Type II slowly changing dimensions using date-time stamping method using Dynamic Look-Up cache.
- Created the DDL scripts and DML scripts for the underlying ETL objects.
- Created various Oracle objects i.e. tables, views, synonyms, sequences, triggers etc.
- Developed PL/SQL stored procedures and packages for generating extracts to the downstream systems using UTL FILE package.
- Extensively used bulk collection in PL/SQL stored procedures for improving the performance.
- Worked on the customization of the reports developed using Crystal Reports & Business Objects.
- Created indexes & partitions on the tables for faster retrieval of the data to enhance the performance of the various Business Reports.
- Designed & developed re-usable tool to validate, export and import the objects from the Informatica repository using Excel VBA scripting
Environment: Informatica Power Center 7.1, Oracle 9i, PL/SQL, Sun Solaris, Control-M, Erwin 7, Business Objects XI R2, Toad, Putty