Sr. Database / Etl Consultant Resume
Newark, DE
SUMMARY
- 10+ Years of experience in analysis, design, developement and implementation of business applications with various clients. Confidential, Confidential Insurance etc. .
- Experience in creating transactional databases and data warehouses in Oracle 19c/12c/11g/10g for OLAP & OLTP environment.
- Extensive experience in identifying Oracle DB performance issues, debugging and performance tuning using Explain plan, Indexing, Hints and Partitioning.
- Strong skills in Oracle programming which includes SQL, PL/SQL Packages, Stored Procedures, Functions, Cursors, Triggers, Views, Materialized Views and Scheduling Jobs.
- To improve performance, analyzed the DB and created many automation processes to reduce the load to the Front - end application and database.
- Experience in oracle supplied packages, collections and ref cursor.
- Expertise in different types of loading like Normal and Bulk loading challenges. Involved in Initial Loads, Incremental Loads, Daily loads and Monthly loads.
- Experience in migrating DB Objects from MS SQL Server to Oracle, Oracle to Snowflake Cloud DW and Saleforce to Snowflake Cloud DW.
- Experience on Monitoring the Data Quality, generating weekly/monthly/yearly statistics reports on production processes - success / failure rates for causal analysis as maintenance part and Enhancing exiting production scripts.
- Experience in Data Warehouse development working with Data conversion, Data migration and (ETL) Extraction/Transformation/Loading using Informatica Power Center with Oracle.
- Expertise in RDBMS, Data Warehouse Architecture and Modeling. Thorough understanding and experience in data warehouse and data mart design, Star schema, Snowflake schema, Slowly Changing Dimensions (SCD Type 1, Type 2, and Type 3), Normalization and Demoralization concepts and principles.
- Created shell scripts for invoking SQL scripts and automated many processes by scheduled them using crontab.
- Generated multiple reports using SQR scripts with PeopleSoft Team.
- Worked in weekly and bi-weekly agile sprints on SCRUM with team sizes over 10.
- Ability to assemble, analyze and evaluate data and be able to make appropriate and well-reasoned recommendations and decisions to support the business and project teams.
- Ability to work well with a diverse team as well as ability to work independently and with minimal supervision.
TECHNICAL SKILLS
Operating Systems: Windows, Linux.
Databases: Oracle 9i/10g/11g/12c/19c & Snowflake Cloud DW, SQL Server
Tools: and Utilities: SQL Developer, PL/SQL Developer, ERwin 9, Oracle Reports 10g
ETL TOOLS: Informatica 8.x, 9.x, 10.x & Informatica Cloud (IICS)
Languages: SQL, PL/SQL, UNIX Shell Scripting, SQR.
PROFESSIONAL EXPERIENCE
Confidential, Newark, DE
Sr. Database / ETL Consultant
Environment: Informatica Cloud (IICS), Snowflake Cloud DW, Salesforce, Oracle 19c & 12c, SQL Developer, Informatica powercenter 10, Windows, Linux.
Responsibilities:
- Gathering the functional requirements from business team and converting them into Technical requirements to get sign-off before the execution.
- Provided technical guidance and training to developers and conducted sessions for code review.
- To improve performance, analyzed the DB and created many automation processes to reduce the load to the Front-end application and database.
- Experience in creating transactional databases and data warehouses in Oracle 12c for OLAP & OLTP environment.
- Extensive experience in identifying performance issues, debugging and performance tuning using Explain plan, Indexing, Hints, Partitioning and Oracle Code Coverage Utilities.
- Strong skills in Oracle programming which includes SQL, PL/SQL Packages, Stored Procedures, Functions, Cursors, Triggers, Views, Materialized Views and Scheduling Jobs.
- Build and maintain SQL scripts, indexes, and complex queries for data analysis and extraction.
- Involved in the continuous enhancements and fixing of production problems.
- Extensively involved in using hints to direct the optimizer to choose an optimum query execution plan.
- Experience in Data Migration from oracle and salesforce to Snowflake cloud data warehouse.
- Created complex mappings in Power Center Designer using Aggregator, Expression, Filter, Sequence Generator, Rank, Lookup and Joiner transformations.
- Developed, Deployed and Tested mappings to load Fact and Dimension tables, SCD Type 1 and SCD Type 2 dimensions and Incremental loading of the mappings.
- Monitoring the Data Quality, generating weekly/monthly/yearly statistics reports on production processes - success / failure rates for causal analysis as maintenance part and Enhancing exiting production scripts.
- Generated various reports on PL/SQL with output format in Excel/Pdf/Html.
Confidential, Newark, DE O
Sr. Database / ETL Consultant
Environment: Informatica 9.6, Oracle 12c, SQL Server, PL/SQL Developer, SQL Developer, ERwin 9, Windows, Linux.
Responsibilities:
- Experience in designing & creating databases in Oracle 11g/12c for OLAP & OLTP applications.
- Extensive experience in identifying performance issues, debugging and performance tuning using Explain plan, Indexing, Hints, Partitioning and Oracle Code Coverage Utilities.
- Experience in defining new architecture, infrastructure design for Informatica, Oracle DB environments and reporting tools.
- Created and Deployed database packages, procedures, functions, triggers, views, materialized views using Oracle PL/SQL.
- Experience in data migration from SQL Server to Oracle.
- Experienced in Automation using Shell Scripting.
- Designed complete database design for Bank 360, Outbound, Ultra Lite and ESB services.
- Analyzing performance of database objects and suggesting DBA for Indexes, schema gathering, partitioning, execution Plan and Shrink Tables.
- Used Oracle's Advanced Queuing mechanism to send messages to clients and interact with other systems (UNIX and App server).
- Created complex mappings in Power Center Designer using Aggregator, Expression, Filter, Sequence Generator, Lookup, Joiner and Update Strategy transformations.
- Working on Dimension & Fact tables, developed mappings and loaded data on to the relational database.
- Developed logical and physical data models that capture current/future state data elements.
- Developed and Deployed mappings to load Fact and Dimension tables, SCD Type 1 and SCD Type 2 dimensions and Incremental loading and unit tested the mappings.
- Implemented PL/SQL to perform application security and batch job scheduling
- Generated various reports for customers using Oracle 10g Reports.
- Actively done deployment of objects on production environment and go live.
- Performed Unit testing in development environment and had a close interaction with system test and user acceptance team to complete technical and functional testing.
Confidential
Oracle PL/SQL/ ETL Developer
Environment: Informatica 9.6, Oracle 10g, Reports 10g, PL/SQL Developer, SQR, TOAD, Windows 7, Linux.
Responsibilities:
- Developed new Oracle Reports, modifying existing Reports, Planning, implementing, and coordinating with team members in AGILE project management environment Programming, Unit Testing, Planning, implementing, and coordinating with team members in AGILE project management environment. Also, close interaction with Client as was acting as an Onsite Coordinator for the team.
- Experience in analyzing and fixing the issues of the existing application as per requirement.
- Experience in ETL Informatica Admin related activities, Upgradation and applying patches, Backup repositories, domain and restore them, also Purging old versions.
- Experience in Project level administration activities like folder creation, assigning permission, deployments and connection.
- Developed complex mappings with transformations like dynamic lookup, Union, router, aggregator, expression, update strategy, and joiner Created mappings, sessions and workflows for the specified requirements.
- Developed mappings to load Fact and Dimension tables, using SCD Type 2 model and Incremental loading.
- Provided the dimensional data model to give optimal performance by changing the Primary Index of tables and applying various performance tuning techniques on Oracle 11g data warehouse environment.
- Worked on Dimension & Fact tables, developed mappings and loaded data on to the relational database.
- Monitoring the Data Quality, generating weekly/monthly/yearly statistics reports on production processes - success / failure rates for causal analysis as maintenance part and Enhancing exiting production scripts.
- Attending SCRUM meetings regularly to discuss the day-to-day progress of the individual teams and the overall project.
- Experienced in interacting with Business Analyst, Developers and analyze business requirement and translating requirements to functional and technical design specifications.
- Good knowledge in front end development using oracle Reports 10g.
- Experience in generating multiple reports using SQR scripts with the help of PeopleSoft Team.
- Debugging and Fixing Production Issues.
- Developed PL /SQL cursors, procedures & functions for updating payment terms.
- Extensively used Joins, Cursors, Triggers, Stored Procedures and Functions in PL/SQL.
- Participated in Unit Testing, System Testing & Release Testing support of the project.
Confidential, Wilmington, DE
Oracle PL/SQL / ETL Developer
Environment: Oracle 11g, SQL, PL/SQL, Win 2008, RHEL 5, PL/SQL Developer, Informatica 9.0.
Responsibilities:
- Developed reports using business analytics and design the reports format, attaching functional area and generating reports.
- Interacted with Business Analysts and participated in user requirement sessions to analyze and gather business requirements.
- Developed ETL programs using Informatica to implement the business requirements.
- Strong knowledge of Extraction Transformation and Loading (ETL) processes.
- Effectively worked on Onsite and Offshore work model.
- Enhancing the source to staging and staging to EDW ETL mappings by adding the new fields and the corresponding business logic.
- Developed complex mappings with transformations like XML generator, dynamic lookup, Union, router, aggregator, expression, update strategy, and joiner Created mappings, sessions and workflows for the specified requirements
- Worked on reusable code known as Tie outs to maintain the data consistency. It compared the source and target after the ETL loading is complete to validate no loss of data during the ETL process.
- Designed workflows with many sessions with decision, assignment task, event wait, and event raise tasks, used informatica scheduler to schedule jobs.
- Reviewed and analyzed functional requirements, mapping documents, problem solving and trouble shooting.
- Developed PL /SQL cursors, procedures & functions for updating payment terms.
- Extensively used Joins, Cursors, Triggers, Stored Procedures and Functions in PL/SQL.
- Implemented Security by creating different users/roles and assigning roles to users.
- Participated in Unit Testing, System Testing & Release Testing support of the project.
Confidential, Newark, DE
ETL\Oracle PL/SQL Developer
Responsibilities:
- Generates the NAFTA Certificates for different parts supplied by the suppliers
- Stores and process the rules by different Rule Book Chapters for all the Parts
- Performs data quality checks
- PROMOTE sustainable development
- ESTABLISH clear and mutually advantageous rules governing their trade
- The goods is produced entirely in the territory of one or more of the Parties exclusively from originating materials
Environment: Oracle 10g, SQL, PL/SQL, TOAD, Win XP, RHEL 4, Informatica 8.6.