We provide IT Staff Augmentation Services!

Senior Etl Consultant Resume

5.00/5 (Submit Your Rating)

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

We'd love your feedback!