Sr. ETL Developer
Resume:
Kaushik Erra
***@******************.***
OVERVIEW
Over 8 Years of IT experience in gathering Business requirements, OLAP, ETL Development, Programming, Testing, Performance tuning, Implementation, Troubleshooting and error handling in the field of Data warehousing and Application Development with exceptional communication skills and leadership qualities to deal with everyday situations and challenges. Excellent analytical and problem solving skills in designing, developing, and implementing innovativeETL methodologies and provide system solutions using new approaches, technology and training for various applications across Health care, Banking, Financial, Oil and gas industry modules.
PROFESSIONAL SUMMARY
Excellent experience with Informatica 10.x/9.x/8.x (Informatica Power Center, Informatica Power Exchange, Informatica Data Quality, and Informatica Data Explorer) as an ETL tool.
Expertise in writing the technical specification documents from business requirements after analyzing the systems.
Experienced in creating mappings using various transformations, and developing strategies for Extraction, Transformation and Loading (ETL) mechanism using Informatica.
Extensively worked on Informatica Components-Source Analyzer, Warehouse Designer, Mapping Designer & Mapplet Designer, Transformation Developer, Workflow Manager and Workflow Monitor
Experience in working with different data sources like Oracle (12c,11g/10g/9i/8i), SQL Server, DB2, Teradata, Netezza, Flat files, XML, CSV Files etc. and experience in developing ETL integrations to invoke Webservices.
Extensively worked with Informatica Power Exchange to access Mainframes Systems.
Excellent track record of leading end-to-end implementation and integration projects to successful completion under tight deadlines.
Good experience in designing and developing complex mappings from varied transformations like Connected Lookups, Unconnected Lookups, Router, Filter, Expression, Aggregator, Joiner, Normalizer, Union, Rank, XML Parser, XML Generator, Sequence Generator, Update Strategy, Source Qualifier Transformations, Mapplets, Reusable Transformations and Data Recovery Process.
Experience with Initial & Incremental loading toTargetdatabases using Slowly Changing Dimensions Type1/Type2/Type3 loads.
Knowledge in using Dimensional Data Modeling, Star Schema Modeling, Snow-Flake Modeling,FACT and Dimensions Tables.
Good experience in Performance of Data warehouse by SQL tuning, Query optimizer and Indexing writing stored procedures, Functions, Packages and Database Triggers using PL/SQL.
Proficient in writing Procedures and Functions using Dynamic SQL and written complex SQL queries using joins, sub queries and correlated sub queries. For maintaining the company and performance standards
Developed SQL Loader control programs, interfaces and PL/SQL validation scripts to transfer flat file data into oracle database and Oracle Applications base tables.
Proficient in coding of optimizedTeradata batch processing scriptsfor data transformation, aggregation and load usingBTEQ.
Expertise in RDBMS, databaseNormalization and Denormalizationconcepts and principles.
Strong experience in Creating Database Objects such as Tables, Views, Functions, Stored Procedures, Indexes, Triggers, Cursors in Teradata.
Excellent in Data Analysis, Data profiling, and data Cleansing using Informatica tools like Informatica Data Quality (IDQ) and Informatica Power Exchange (IPE).
Experience with Oracle PL/SQL programming, stored procedures, creating triggers, sequence generators on the target tables.
Performed Scheduling Tasks in the business environment with Autosys, Tidal, Control M and WLM.
Wide knowledge and experience in UNIX shell scripting and Windows Batch Scripting.
Strong technical background, and possesses excellent analytical and logical programming skills with a good understanding at the conceptual level and possess excellent presentation, interpersonal skills with a strong desire to achieve any responsibility or challenges.
TECHNICAL SKILLS:
CURRENT ROLE
:
ETL Developer
SYSTEMS WORKED ON
:
Informatica PowerCenter 8.1.1/8.6/9.1.0/9.5/9.6/10.1, Informatica Data Quality (IDQ) 8.5/8.6, Informatica Data Explorer (IDE) 8.5/8.6, Informatica PowerExchange 9.1.0, Visio, Oracle, Unix.
LANGUAGES KNOWN
:
SQL, PL/SQL, HTML, TSQL, CSS, Java-Script, C/C++
OPERATING SYSTEMS
:
Windows 98/NT/2000/XP/Windows 7, DOS, AIX Server 5.2, UNIX,MacOS
DATABASES WORKED ON
:
Teradata, SQL Server, ORACLE 12c/8i/9i/10g, Sybase, DB2 UDB, IBM DB2 (UDB 7.2)
PROFESSIONAL EXPERIENCE:
Anthem INC, Indianapolis, IN June2019 – Present
ETL Developer
Anthem is a leading healthcare company with benefits dedicated to improving lives and communities, and making healthcare simpler. Through its affiliated companies, Anthem serves more than 106 million people, including more than 42 million within its family of health plans aiming to be the most innovative, valuable and inclusive partner. Our project includes automating a process to extract FEP (Federal Employee Program) internal associates data and load it into Oracle database to generate a Medical Management dashboard in business objects which provides the capability to track the quality, productivity and staffing needs contributing better health and quality treatments to our associates members.
Responsibilities:
Worked on gathering technical and business specifications to design and implement the data flow architecture and ETL Procedures following company standards.
Involved in business meetings and daily stand up’s to meet the timely deadlines.
Working on Informatica 10.2.1 Hotfix version for designing ETL mappings and workflows.
Worked on implementing various transformations such as router, expression, update strategy, lookup’s etc.,
Conducted various levels of testing including functional, regression, user acceptanceand performance to verify the business needs are met accordingly.
Assisted in building the technical specification documents and published on share point site.
Working on Various databases like Oracle 12c, SQL server, Mainframes, Flat Files which consists of anthem internal associates data.
Worked on error handling procedures during testing and fixing production defects.
Involved in writing SQL, PL/SQL codes, stored procedures, packages and analyzing them which are enforced on business objects for generating reports.
Worked on UNIX scripts to generate a daily summary file of the data counts and automating the ETL process.
Automated all the workflows in Control M and was involved in production support.
Environment: Informatica Power Center 10.2.1 Hotfix - Repository, Designer, Workflow manager, Monitor, Informatica Metadata Manager, Toad, SQL, Oracle, Server 2012, Control M, putty –UNIX.
BBVA Compass, Birmingham, AL Nov2017 – June2019
ETL Lead Developer
BBVA Compass Bancshares, Inc. (formerly Compass Bancshares) is a bank holding company headquartered in Birmingham, Alabama. It has been a subsidiaryof the Spanish multinational Banco Bilbao Vizcaya Argentaria since 2007 and operates chiefly in Alabama, Arizona, California, Colorado, Florida, New Mexico, and Texas. Project worked on is the Fraud data engineering which deals with building a robust system that identifies the Fraud that happen during bank transactions. Our goal was to get the data from SAS and re-design it to Informatica system and PL/SQL automation processes that efficiently handles the fraud and efficiently handle the operations and transaction details and behavior of the customers and any unusual fraud transactions by building a new data model for Fraud Analytics and Risk Management Reporting.
Responsibilities:
Involved in gathering technical and business specifications to design, implement and efficiently code the data according to the data flow procedures.
Built the data models and identified the relationship between different tables that helps in better architecture building.
Developed Business Architecture using requirements such as scope, processes, alternatives, and risks.
Conduct various levels of testing including functional, regression, user acceptance, integration and performance to verify the client’s need are met.
Communicate client’s business requirements by constructing easy-to-understand data and process models.
Assisted in building the ETL source to Target specification documents understanding the business requirements using technical specification documents.
Working on informatica9.6 and Informatica 10.2 versions for handling designing, migrations, workflows and monitoring the workflows.
Working on Various data base like Teradata, Oracle, SQL server, Flat Files to pull different data sources and written complex queries and used Oracle 12c as central database.
Identify and reconcile errors in client data to ensure accurate business requirements.
Worked on various complex Deliverables which support critical Business Driven Reports.
Involved in writing SQL, PL/SQL codes, stored procedures, packages and analyzing them.
Used Quality center to interact, co-ordinate, track and report for seamless integration of various project related tasks.
Worked on testing the workflows by writing test cases based on the transformations made in development
Automated all the workflows in Control M and was involved in production support.
Environment: Informatica Power Center 9.6, 10.1 - Repository, Designer, Workflow manager, Monitor, Informatica Metadata Manager, IDQ, Toad, SQL, Oracle, Server 2008,TSQL, Control M, putty –UNIX.
Centene Corporation, St Louis, MO Jan2016–Nov2017
Sr. Application Software Engineer (Extract Transform Load)
Centene is a multiline healthcare enterprise which provides services to government health programs and provides health plans through Medicaid, Medicare, Health Insurance Marketplace and other Health Solutions. Centene is a Fortune 500 managed care enterprise that serves more than 11 million members across the United States. It contracts with other healthcare and commercial organizations such as lab corp, Peace Health, Legacy Health, Quest Diagnostics and many other throughout the nation to provide specialty services, including behavioral healthcare services through Cenpatico, care management software, correctional systems healthcare, in-home health services, life and health management, vision, pharmacy benefits management, specialty pharmacy and tele health services
Responsibilities:
Design, build, test and maintain scalable and stable off the shelf application or custom built technology solutions to meet business needs. Act as a SME for developers and engineers to present recommendations, accurate assessments and provide with an effective outcome.
Worked on complete data warehouse development life cycle from gathering requirement specifications to implement programming concepts with a variety of computer languages such as Teradata SQL, SQL Server, UNIX and deliver the test product.
Expertise in a variety of the logical concepts, practices and procedures related to the ETL (Informatica) design and development.
Responsible to load data from different heterogeneous sources such as .datfiles, XML files, Flat files,Teradata, SQL Server into Stage layer and moving forward to Enterprise data warehouse using a variety transformations.
Transformations include Lookups - Connected and Unconnected, Aggregator, Joiner, Router, Update Strategy, Expression, Normalizer, Mapplet, Sorter, Router etc.,
Responsible for development, testing and deployment of project deliverables to implement defined solution within a planned release schedule.
Recommended solutions by defining database physical structure, functional responsibilities and maintenance of database following the company standards and specifications with desired data warehousing concepts.
Conduct root cause analysis and advance performance tuning for complex business processes functionality such as replacing ODBC connections with the TPT (Teradata parallel transporter), modifying existing code in time taking or an error feed mappings. Modifying the stored procedures.
Involved in writing Teradata SQL Scripts tocreate tables, views, stored procedures and packages or modifying the existing for performance boosting and analyzing them, also worked on morphing to protect thesensitive data of patients.
Wrote, tested and implemented Teradata Fastload, Multiload and BTEQ scripts, DML and DDL.
Wrote views based on user and/or reporting requirements to maintain the PHI information.
Wrote Teradata Macros and used various Teradata analytic functions.
Involved in migration projects to migrate data from data warehouses on Oracle/DB2 and migrated those to Teradata.
Performance tuned and optimized various complex SQL queries.
Extensively worked on UNIX - performing ftp, creating folders, directories, new param files or modifying existing files
Worked with ETL Developers, Administrators, BA’s, DBA’s to architect data model, design, implement, enhance and maintain the data flow
with designated ETL programming code procedures.
Responsible for creation and maintenance of comprehensive documentation (High level/Low level) technical solutions, development plans and installation procedures etc.,
Participate in code review sessions and resolve complex issues (if any) communicating with other team members.
Worked on Tidal Job schedulerfor scheduling workloads/jobs daily, weekly or monthly bases.
Environment: Informatica Power Center 9.6 - Repository, Designer, workflow manager, Monitor, Informatica Metadata Manager, Teradata SQL Assistant, SQL, Oracle, Server 2008,TSQL, Tidal, putty -UNIX, Confluence, TOAD.
Blue Cross Blue Shield Association, Chicago, IL Sep2014 – Dec2015
Informatica Developer
The Blue Cross Blue Shield Association (BCBSA) is a federation of 36 separate United States healthinsurance organizations and companies, providing health insurance to more than 106 million Americans. I was involved in the gathering the data of the reviews of patients with physicians withinwww.bcbsa.com and aggregating that into the National database using Informatica.
Responsibilities:
Worked on complete data warehouse development life cycle from gathering requirement specifications to delivering the test product and documenting them.
Responsible to load the CSV files with an XML tag from the web service platform into the National database (Oracle) of association i.e., into dimensional physical layer using Informatica 9.5.
Worked with various sources including Oracle, Flat files, XST files, .bat Files, CSV Files etc.
Coordinating with various groups and to understanding, gathering business requirements, designing, testing and production.
Involved in creatingtesting scripts, customizing connections, sessions, workflows, Java transformations, HTTP Connections etc.
Involved in Performance Tuning of ETL Architecture, help develop optimum solutions for deliverables.
Managed and customized the ETLInformatica/DACloads, RPD and Web catalog metadata according to the reporting needs of the client.
Involved in writing SQL, PL/SQL codes, stored procedures, packages and analyzing them.
Developed SQL* loader scripts to load data in the custom tables
Created indexes on tables and Optimizing Stored Procedure queries
Modified existing codes and developed PL/SQL packages to perform certain Specialized functions / enhancement on oracle application
Involved in preparing test plans, unit testing, System integration testing, implementation and maintenance
Created global variables and cursors for procedures and functions through PL/SQL packages.
Review data transformation rules and provide technical suggestions in data transformation logic andpseudo code (PL/SQL).
Used RANK, DENSE_RANK functions to involve in the development of custom stored procedures, functions, packages, triggers, SQL and PL/SQL.
Used PLSQL Records, Collections (Nested Tables and Varrays) in complex backend packages.
Involved in gathering the upgraded versions of different business requirements and documented them within the technical supplementary and specification documents and posted them into ReqPro (SharePoint).
Used PMCMD to run workflows and to automate their schedules.
Extracted data from flat files, XML and oracle database to load them in the central SQL Server database.
Successfully moved theSessions and Batches from the development to production environment.
Developed complex mappings from varied transformation logics like Connected, Unconnected Lookups, Aggregator, Parser, Joiner, Router, Update Strategy and created slowly changing dimension type 1 and 2.
Worked on UNIX/Solaris platform.
Worked on Control M for scheduling workloads weekly or monthly bases.
Environment: Informatica Power Center 9.5, Informatica Data Quality, Informatica Metadata Manager, Oracle, Oracle Exadata, 11g, SQL Server 2008,TSQL, PL/SQL, MS Visio, SQL Developer, Control M,UNIX Solaris, Microsoft SharePoint, TOAD.
CVS Caremark, Providence, RI Oct2013 – Aug2014
Sr. ETL Developer
CVS Caremark, is one of the nation's leading pharmacy benefit management (PBM) companies, provides comprehensive prescription benefit management services to over 2,000 health plans, including corporations, managed care organizations, insurance companies, unions and government entities. Caremark operates a national retail pharmacy network with over 60,000 participating pharmacies, as well as 56 retail specialty pharmacy stores, 20 specialty mail order pharmacies and 9 mail service pharmacies located in 26 states and the District of Columbia.
Responsibilities:
Responsible to load the dimensional physical layer from various sources including SQL Server, Oracle, Flat file, XML Files etc.
Coordinating with various groups to understanding requirements, designing, testing and involved in production support.
Responsible for on-call activities in production environment once in a month as a secondary person.
Worked on complete data warehouse development life cycle from requirement specifications to delivering the test product.
Involved in upgrade project from Informatica 8.6 to 9.5.
Extracted data from Flat files, DB2, Oracle, XML and Loaded into Teradata target data warehouse.
Wrote several Teradata BTEQ scripts to implement the business logic.
Used Teradata Utilities (FASTLOAD, MULTILOAD, and FASTEXPORT) and queried the Target database for validation
Involved in creating upgradation plan, testing scripts, customizing connections, sessions, workflows, Java transformation for upgrade.
Involved in Testing, Debugging, Validation and Performance Tuning of ETL Architecture, help develop optimum solutions for Informatica deliverables.
Involved in some production support and update-exiting mapping.
Experience in writing SQL, PL/SQL codes, stored procedures and packages.
Used pmcmd to run workflows to automate their schedules.
Extracted data from flat files, XML and oracle database to load them in the central oracle 11g database.
Normalized the tables/relationships to arrive at effective Relational Schemas without any redundancies.
Identified the Facts & Dimensions Tables and established the Grain of Fact for dimensional Models.
Created Snowflake Schemas by normalizing the dimension tables as appropriate, and creating a Sub Dimension named Demographic as a subset to the Customer Dimension.
Managed and customized the ETL Informatica/DAC loads, RPD and Web catalog metadata according to the reporting needs of the client.
Maintained effectiveness in creating the dashboards by appropriately including guided navigational links and drilldowns.
Successfully moved the Sessions and Batches from the development to production environment.
Developed complex mappings from varied transformation logics like Connected and Unconnected Lookups, Router, Aggregator, Joiner, Update Strategy etc…
Create slowly changing dimension type 1 and 2.
Worked on Control M for scheduling workloads on a daily,weekly or monthly bases.
Worked with different Informatica tuning issues and fine-tuned the transformations to make them more efficient in terms of performance.
Environment: Informatica Power Center 9.5, Informatica Data Quality, Informatica Metadata Manager, Teradata, Erwin, Oracle Exadata, 10g/11g, SQL Server 2008, PL/SQL, MS Visio, SQL Developer, Control M,UNIXSolaris/AIX, Microsoft SharePoint
Freddie Mac, McLean, VA Jun2012 -Jul 2013
Application Software Engineer
The Federal Home Loan Mortgage Corporation (FHLMC), known as Freddie Mac, is a public government-sponsored enterprise (GSE) performing an important role in the nation’s housing finance system – to provide liquidity, stability and affordability to the mortgage market. They provide liquidity (ready access to funds on reasonable terms) to the thousands of banks, savings and loans, and mortgage companies that make loans to finance housing.
Responsibilities:
Implementation of Data requirements gathering, Design, Development and Unit Testing.
Interacted with Business users to analyze the business process and requirements.
Worked with the Business Analyst and Data Modeler during the functional design and technical design phases.
Designed and developed various Informatica Mappings using Active & Passive Transformations.
Utilized Informatica IDQ to complete initial data profiling and matching/removing duplicate data.
Developed standard, re-usable mappings and mapplets using various transformations like expression, aggregator, joiner, source qualifier, router, lookup, Router, Rank and Update Strategy.
Implemented Slowly Changing Dimensions (SCD Type 2) to update the dimensional schema.
Monitored workflows and collected performance data to maximize the session performance. Unit Testing and debugging of ETL objects. Involved in reviewing the Test Cases written by the peers.
Worked on NZLOAD utilitythat use Massive Parallel Processing (MPP) architectures to provide high query performance and platform scalability.
Responsible for Data quality checking and interacting with the business analysts.
Created Task Session, Email and Workflow to execute the mappings. Used workflow monitor to monitor the jobs, reviewed error logs that were generated for each session, and rectified any cause of failure.
Involved in creating, upgrading plan, testing scripts, customizing connections, sessions, workflows, Java transformations.
Wrote SQL, PL/SQL, stored procedures for implementing business rules and transformations.
Worked on performance tuning of mappings, identifying and resolving performance bottlenecks in various levels like sources, targets, mappings, and sessions.
Facilitated team and client meetings effectively and held regular status meetings with project team. Resolved/Elevated issues and ensured communication of relevant project information to all stake holders.
Used tools like TOAD to run the queries and validate the data loaded into power center and different other layers like Data Warehouse.
Used Autosys for scheduling sessions in ETL.
Environment: Informatica Power Center 9.1, Netezza,Teradata, Oracle 11g, SQL Server 2008, PL/SQL, Toad 9.1,, UNIX, Autosys.
St.Elizabeth Hospitals, Erlanger, KY Sep2011 – May2012
Associate Software Developer ETL
St. Elizabeth Healthcare is one of the oldest, largest and most respected medical providers in the Greater Cincinnati region. For more than 150 years, St. Elizabeth has been the heart and soul of healthcare in Northern Kentucky. Founded with one small hospital in 1861, St. Elizabeth Healthcare now operates six facilities throughout Northern Kentucky Area providing a vast variety of healthcare services.
Responsibilities:
Involved in requirements gathering, functional/technical specification, designing and development of end-to-end ETL process.
Interact with Source system/application team & the business users to understand the data & layout the specification to bring the data into the landing area.
Identified data attributes across multiple applications, document and resolve anomalies and gaps across data sources.
Interacted with business analysts and translate business requirements into technical specifications.
Prepared ETL coding and Mapping standards.
Developed the ETL mappings, sessions and workflows to load the data using Informatica Power Center tool.
Developed re-usable mappings and mapplets using various transformations based on the requirement provided, also developed mapping parameters in ETL logic.
Worked with Oracle, Teradata and flat files to the load into target Teradata tables.
Created and debugged various PL/SQL packages, procedures and functions to support various areas of development
Constructed standard and materialized views for viewing and extracting usable data from Various sources to get a clear real-time view of business-critical information
The chief development tools used in this work include Oracle PL/SQL, Oracle materialized Views and dynamic result set generation through Oracle PL/SQL ref-cursors
Extensively involved in developing PL/SQL packages that includes advanced PL/SQL concepts like REF_CURSOR, BULK COLLECTS
Using SQL Plus and PL/SQL to query and analyze data and perform basic DML and DDL operations in the Oracle Database
Extensively involved in Oracle query (SQL) tuning, PL/SQL execution optimization, Explain Plan, SQL Trace, tkprofandSQL*PLUS Auto trace
Experience in Performance Tuning across many critical functions, and included appropriate use of: SQL hints, in-line views, Materialized views, stored outlines for execution plan stability; establishing and restructuring of indexes, fast full index scans, index organized tables
Worked with shell scripts (UNIX and Windows) to transfer the files to various stages in ETL process.
Involved in extensive Data validation by writing several complex SQL queries and Involved in back-end testing and worked with data quality issues.
Participated in various release data cycles for the code deployment.
Involved in peer evaluation and reviews of ETL codes.
Supported post deployment window after production deployment.
Worked on Tidal for scheduling sessions.
Environment: Informatica Power Center 8.6, Oracle 10g/11g, SQL Server, Toad 9.1, UNIX, SQL Server 2008, Tidal.
EDUCATIONAL QUALIFICATION:
Bachelors in Technology – Electronics and Communications – JNTU, Hyderabad
Contact this candidate