Sql Developer Data Warehouse
Resume:
Bharath Perumandla
(***********@*****.***)
SUMMARY:
20+ years of experience in Information Technology and Systems of which over 12+ years in Health Care.
Certified Hands on Snowflake WebUI Essentials.
Experience and understanding of architecting, designing and operationalization of large scale data and analytics solutions on Snowflake Cloud Data Warehouse.
Implemented very large-scale data intelligence solutions around Snowflake Data Warehouse.
Experience in Data Migration from RDBMS to Snowflake cloud data warehouse.
Experience with Snowflake utilities, SnowSQL, Snow Pipe.
Have sound knowledge in implementing Operational Data Store, Data Warehouse Implementation & Analytics.
Worked as Data Architect / Project Manager Technical / Technical Lead
Lead positions in complex engagements in the areas of Health Care (MMIS system) Data warehousing, Operational Data Stores, Client /Server Enterprise Application Development and Data Analytics.
Strong experience in Databases, Data Modelling, DB programming (PL/SQL).
Experience in US health care domain using CNSI Proprietary tools like eCAMS/evoBrix, AS-One, RecTrace, HEDIS and ClaimsSure.
Experience in handling multiple projects as Technical Lead.
Lead HIPAA EDI 5010 implementation project.
Has good knowledge on the Health Care - HIPAA X12 EDI transactions both 4010 and 5010 for 837(P/D/I), 270/271, 276/277,277U, 278, 834, 820 and 835. Handled DDE and Paper transactions of claims.
Strong experience in designing/building and implementing applications.
Thorough understanding of MMIS and its subsystems like Core Claims (FFS/Encounters), Provider, Member, Managed Care, and Contracts Management.
Good understanding of the Medicaid Information Technology Architecture (MITA).
Strong experience in ETL Design, Analysis, Development, Modelling, design of data schemas and Data migration/conversion.
Experience in meta-data and master data management.
Experience in Quality Management; Compliance to SEI CMM Level 5
EMPLOYMENT:
Name of the Company
Designation
Duration
Global Visse Inc
Senior Data Engineer
July 2020 to Till Date
Client Network Service Inc, USA
Senior Data Engineer
September 2007 – July 2020
Hanusoft, Inc
Programmer Analyst
Sep 2006 – August 2007
GSS America
Programmer Analyst
May 2006 – Aug 2006
Tata Consultancy Services Ltd
Assistant Consultant
September 1998 – April 2006
EDUCATION:
Bachelor of Engineering (B.E) from Osmania University, Hyderabad.
Work Authorization Status: Permanent Resident Card (GC) holder.
TECHNICAL SKILLS:
Hardware
PCs, SUN, HP-UX 9000, IBM AIX,
Systems Software
Microsoft Operating Systems, UNIX, LINUX
Tools
SQL Developer, SQL Developer Data Modeler, Power Designer, Erwin, Jasper Reports, Birt Reports, Data Stage, Informatica, Jira, GitHub, TOAD, Control-M, MS VSS, CCC Harvest, Edifecs, Gentran, Clear Case, Clear Quest
Languages
SQL, PL/SQL, Unix Shell scripting/awk programming, C Programming, Python, Java Script.
Databases
Oracle 19c/12c/11g/10g/9.x, MS Access, Redshift, NoSQL, Snowflake
CNSI Proprietary Tools
eCAMS/evoBrix, As-One, ClaimsSure, HEDIS, RecTrace
Cloud Technologies
AWS, S3, EC2, Bigdata, Hadoop, Hive
WORK EXPERIENCE:
Company: Global Visse Inc July 2020 to Till Date.
Technology: Oracle 12c/19c, PL/SQL, SQL Developer,
Snowflake, SQL Developer Data Modeler, SVN, Tableau, Birt,
SVN, Jira, AWS, S3, Talend, Linux server.
Role: Senior Data Engineer
Project Name: Central Data Warehouse and Reporting
Central Data Warehouse and Reporting Team loads daily transactional data from Transactional system and other data sources into the central data warehouse and provides business specific data marts. The team develops and supports the dashboards and reports to the users.
The data is ingested from various source and integrated to meeting the business specific needs.
1.Involved in the design and development of ETL jobs to load data from multiple data sources to meet reporting and dashboard requirements.
2.Involved in the design of framework and automation to load data from sources to the central data warehouse.
3.Worked with business users to design dashboards.
4.Developed ETL jobs and pipelines for automated loading of data using Talend, S3.
5.Effectively used cache for better processing of data.
6.Loading jobs are written to resume and suspend the warehouse after the jobs are complete.
Company: CNSI July 2014 to July 2020.
Technology: Oracle 11g/12c, PL/SQL, SQL Developer, Snowflake,
SQL Developer Data Modeler, oracle Golden Gate,
Data Stage, Clear case, Clear quest, Jira, AWS, S3,
Azure, Azure BLOB, Python, Talend, AWS Glue,
Linux Server.
Role: Senior Data Engineer
Project Name: Operational Data Store and Data Analytics
(Data Services Group).
Data Services group deals with all the database development work at the organizational level. It includes data conversion, data analytics, database performance tuning, Data marts and new projects development work. The group deals with all the new initiatives and enhancements related to databases.
Data Analytics / Operational Data Store/ Data Conversion
Data Analytics:
Data from various sources are loaded into Snowflake using S3 and after transformation and aggregation file are generated and placed on S3 for downstream systems for reporting and for dashboard purposes.
Used Snowflake to ingest data from multiple sources from AWS S3.
Operational data store project provides near Realtime replication of OLTP data into ODS database. The ODS framework allows near Realtime replication with minimum configuration to replicate the data and allows derivation and aggregation of data. The framework was developed using oracle PL/SQL and AWS Glue.
Built star schema data model to load dimension and fact data related to Member enrollment, capitation and utilization (Encounter claims) data.
Data Warehouse Extracts: Data Warehouse extracts provides data extracts to enterprise data warehouse daily / weekly based on the configuration. This project provides incremental extracts from the OLTP systems. The project involves building automated configurable framework which extracts data based on the metadata queries.
Data Conversion: As part of implementing new MMIS system, the data from the Agency systems must converted to the new MMIS system. The Conversion includes various health care sub-systems data like Claims, Provider, Member, Benefits Administration, Prior Authorization and Reference Data, TPL, and Finance.
Used Data Stage ETL tool along with PL/SQL programs for data conversion.
Responsibilities:
Developed ETL pipes to load data from S3 to Snowflake by utilizing different types of table objects and time travel options.
Developed ETL jobs in Snowflake to ensure the compute warehouses are resumed when the loading is started and suspended after the loading is complete.
Applied transformation in snowflake utilizing the result and warehouse cache properly by creating the computing warehouses and assigning for the subject specific data.
Data from source system is stored in S3(Data lake) to use for future needs.
Designed framework data models to implement ODS real-time replication and automate process to load aggregated data for Analytics.
Created data models, solution designs and data architecture documentation.
Designed Partitioned tables, partitioning strategy and type of Partitioning and indexing design.
Designed and created oracle Batch process using PL\SQL programs for huge data cleansing and data quality processes in oracle stage database.
Designed and developed automated Realtime ODS replication process using PL/SQL
Implemented naming standards and data dictionary for data models.
Worked with SME’s and BA’s to prepare the design and mapping documents.
Implement configurable parallel processing mechanism to dynamically create parallel jobs to load/replicate data.
Created Triggers, complex packages, procedures, functions views.
Developed python scripts for data processing.
Implemented Collection objects, Nested Tables, Record types, Cursors, Ref Cursors, Views, FORALL, Bulk Collect, Regular Expressions, Autonomous Transactions.
Identified problem queries and re-designed the SQL queries for better performance.
Mentored junior team members.
Data Stage installation and setup for new projects.
Company: CNSI May 2011 to Jun 2014.
Client: CNSI eCAMS product development, Chennai, India.
Technology: Oracle 10/11, PL/SQL, SQL Developer,
SQL Developer Data Modeler, Toad, Cognos,
Clear case, Clear quest.
Role: Project Manager - technical
Project Name: eCAMS (Electronic claims adjudication management system).
Client Network Services Inc., Chennai, India May2011 – JUN2014
Technical Manager
CNSI is a health care product development company has developed various products catering to US health care domain especially in MMIS (Medicaid Management Information System) adhering to MITA architecture called as eCAMS (Electronic claims adjudication management system).CNSI also developed products like claims sure which is used for finding improper claims, HEDIS (Healthcare Effectiveness Data and Information Set) which are integrated with core eCAMS tool. CNSI developed project collaboration tool As-One which is used in project/product development and RecTrace tool for requirements traceability
As a project Manager Technical, lead the product enhancements in database areas which includes enhancing the EDI loading framework to support EDI 5010 version from 4010 version, develop tools like ClaimsSure to identify improper claims, HEDIS (Healthcare Effectiveness Data and Information Set) which are integrated with core eCAMS product. Involved in the project collaboration tool As-One which is used in project/product development and RecTrace tool for requirements traceability
Responsibilities:
Lead EDI 5010 implementation.
Involved in the 837P/D/I claims and encounters, 270/271, 276/277, 277U, 834 and 835 PL/SQL 5010 changes.
Worked with BA’s on EDI 4010 to 5010 gaps mappings and implemented PL/SQL programs to accept 5010 ED files.
Created Data Model for ClaimsSure product to process the claims data to identify the inappropriate claims.
Handled huge volumes for data utilizing 60 months for claims data for ClaimsSure Project.
Implemented SQL and PL/SQL tuning techniques wherever required for better performance
Lead Metadata driven HEDIS tool development using Shell Scripting, SQL, PL/SQL, SQL*Loader to create data marts for HEDIS specific measures.
Designed partitioning and indexing strategy.
Created denormalized structures wherever required for better performance.
Implemented partitioning and indexing the claims and capitation tables for better performance.
Company: CNSI Sep 2007 to Apr2011.
Client: CNSI eCAMS product development, MD -USA / Chennai, India.
Technology: Oracle PL/SQL, Unix Shell Scripting, Perl, Spec Builder,
TOAD, Clear case, Clear quest, Erwin.
Role: Project Lead / Programmer.
Project Name: eCAMS (Electronic claims adjudication management system)
CNSI is a health care product development company has developed various products catering to US health care domain especially in MMIS (Medicaid Management Information System) adhering to MITA architecture called as eCAMS (Electronic claims adjudication management system). HEDIS (Healthcare Effectiveness Data and Information Set) integrated with core eCAMS tool. CNSI developed project collaboration tool As-One which is used in project/product development.
Responsibilities:
Configure and setup Meta Data for EDI transaction in eCAMS EDI;
Configure HIPAA files loading edits in eCAMS;
Write new edits in eCAMS for EDI transactions;
Create packages, procedures, functions to load validate the EDI transactions 837P/D/I, 270/271, 276/277, 277U, 834 and 820.
Involved in the data modelling activity to design the work in progress and final tables for the EDI transactions data.
Worked with Data warehouse team to provide the data extracts for all the subsystems from OLTP system to Data warehouse.
Company: HanuSoft Sep 2006 to Aug 2007.
Client: First Health Services, Richmond, VA
Technology: Oracle 8i/9i (PL/SQL, SQL*Loader), Perl,
UNIX and Shell Scripting.
Role: Systems Analyst
Project Name: NPI project
First Health is a unique national managed health care company that offers comprehensive services to payors across the country—including all 50 states, the District of Columbia and Puerto Rico. Serving the group health, workers' compensation and state public program markets, and the company’s core competency is managing total health care costs while educating and empowering consumers.
NPI:
National provider Identifier is a New ID which will be supplied to all the registered and working pharmacies across the country from NCPDP. It’s required that providers have to use this identifier in order to submit claims for processing and payments. First health Services Corporation has to remediate the systems in order to take new NPI ID numbers and process them for payment.
Responsibilities:
Involved in writing UNIX shell scripts, Perl and ORACLE-PL/SQL programs.
Built complex business logic into the application using UNIX Shell and PL/SQL programming techniques.
Understand the issues involved and resolutions to major data quality problems from source data systems.
Preparation and review of Acceptance Test Plan and Specifications.
Company: GSS America May 2006 to Aug 2006.
Client: WellPoint, Inc., Columbus, OH
Technology: Oracle 9i (PL/SQL, SQL*Loader), Power Designer,
UNIX and Shell Scripting.
Role: Data Architect
Project Name: Life & Disability Datamart project
WellPoint is the nation's leading health benefits company serving the needs of approximately 34 million medical members nationwide. WellPoint is currently transforming its existing L&D organization, L&D Strategic focus and supporting technical infrastructure. Due to this transformation, there are a few systems that are going to be sunset, and the focus of some systems is going to change.
Life & Disability Datamart:
The Life & Disability Datamart project will provide an enterprise systems environment for the collection, analysis and reporting of L&D and performance metrics information.
Responsibilities:
Involved in writing UNIX shell scripts and ORACLE-PL/SQL programs.
Built complex business logic into the application using UNIX Shell, PL/SQL and AWK programming techniques.
Analyzed user requirements and involved in Data modeling using Power Designer.
Defined data structures and technical specifications.
Involved in the data mappings from 22 legacy source systems to Target (Datamart).
Understand the issues involved and resolutions to major data quality problems from source data systems.
Preparation and review of Acceptance Test Plan and Specifications.
Conducting, coordinating and managing QA Testing, Regression Testing and BAT/UAT
Involved in SQL scripts tuning. Used Explain Plan as a tuning tool. Performed code review, worked with materialized views, partitioning, indexing strategies (btree/bitmap/function based), de-normalization and other techniques to improve performance.
Wrote wrapper Shell scripts for PL/SQL programs.
Company: TATA Consultancy Services Aug 2002 Till Apr 2006.
Client: Procter & Gamble, Cincinnati, OH .
Technology: Oracle 8i/9i (PL/SQL, SQL*Loader), Erwin, UNIX Shell,
AWK Scripting, Control-M scheduling, CCC Harvest,
CasePac, IPMS.
Role: Project Manager / Onsite-Offshore Co-Ordinator
Project Name: Operational Data Store – Global Point of Sale.
Procter & Gamble is a global corporation and one of the biggest and most successful consumer goods companies in the world. Has a large IT division with two major groups BIS (Business Information Systems) and ITBS. P&G has built a data warehouse to have globally standard, accurate and integrated data. The data warehouse has been built in a building block approach covering ETL, consolidation/aggregations and information delivery to end users for effective business decisions. Involved in the following projects/implementations has Senior Developer.
GPOS Data Acquisition:
GPOS DA uses Informatica tool, UNIX and shell scripting through which the P&G retail customers' (cash registry scanner/POS) data can be validated/transformed/loaded into the GPOS DA database and generate a standard output to provide data in a format suitable for uploading into GPOS. GPOS DA tool provides the features like auto identification of files, compression/uncompressing of files. This application is based on Informatica, UNIX shell scripting and Oracle 9i technology.
Global Point of Sales (GPOS):
This Project loads the standard format fact and dmnsn files into data warehouse. GPOS has various implementations, one for each MDO/Customer. Each of these implementations run through the respective Building Blocks (Applications developed by P&G) of the data warehouse for validation and finally Materialized Views/Regular views will be created. These Materialized Views will be used in cubes building and reporting. This application is based on Oracle 8i/9i (PL/SQL, SQL*Loader), Unix Shell and AWK Scripting, Control-M scheduling, CCC Harvest technologies.
Responsibilities:
Involved in developing UNIX shell scripts and PL/SQL programs.
Built complex business logic into the application using UNIX Shell, PL/SQL and AWK programming techniques.
Involved in the end-end implementation of 180 retail customers (located in NA,EUROPE and Asia) POS data for P&G Data Warehouse .Major Implementations include WALMART, KMART, COSTCO, Family Dollar, Dollar General, TESCO, ASDA... etc .
Understand the issues involved and resolutions to major data quality problems in source data systems.
Technical mentoring for the team members.
Creation and maintenance of database objects like tables, views, indexes, materialized views etc.
Preparation and review of Acceptance Test Plan and Specifications.
Conducting, coordinate and managing QA Testing, Regression Testing and BAT/UAT
Involved in Data modeling using Erwin tool.
Worked closely with Technical Leads, Programmers, DBA, on a day-to-day basis.
Program, test, implement and maintain data extraction software from the operational systems.
Use TCS proprietary tool IPMS(Integrated Project Management System)
for project delivery followed standard quality assurance process.
Use TCS’ proprietary CASE based tool CasePac, which enables it to analyze and reuse the data definitions and mapping rules in its repository.
Company: TATA Consultancy Services Apr 2001 Till Jul 2002.
Client: Procter & Gamble, Cincinnati, OH
Technology: Oracle 8i/9i (PL/SQL, SQL*Loader), UNIX Shell
and AWK Scripting, Reports 6i, Control-M scheduling,
CCC Harvest.
Role: Senior Developer
Project Name: SourceOne data warehouse - Building Blocks
P & G has a data warehouse to meet its DSS needs for finance, market, and shipping and supply chain measures. The data warehouse is comprised of many building blocks. This project aimed at Developing, implementation and maintenance of the building blocks (ETL Extraction, transformation and Loading). This highly reliable system has capability of loading fact and dimension data. This application is generic and is meta-data driven, used by various business bundle users of P&G located across the world.
Responsibilities:
Involved in the Design of Building blocks.
Involved in the Data Modeling and implemented using Erwin tool.
Worked closely with Analysts, Module Leads, Programmers, DBA, on a day-to-day basis.
Understand different business areas within an organization and to relate these into a common framework.
Built complex business logic into the application using UNIX Shell, PL/SQL and AWK programming techniques.
Developed reports using reports 6i.
Use TCS proprietary tool IPMS(Integrated Project Management System)
for project delivery followed standard quality assurance process.
Use TCS’ proprietary CASE based tool CasePac, which enables it to analyze and
reuse the data definitions and mapping rules in its repository.
Procter & Gamble, Cincinnati,OH/Warsaw Poland /India Sep 1999 Mar2001
SourceOne data warehouse – Global Management reporting applications
System Analyst
P & G has a data warehouse to meet its DSS needs for finance, market, shipping and supply chain measures. The data warehouse is implemented in Global office and all the four regions (Latin America, North America, Europe and Asia). The Global Management Reporting systems will provide key business information at the hands of P & G users running the business and will aid them in making sound business decisions. This project aimed at developing/generating various standard reports for the business users. Business users access these reports through GKC (Global Knowledge Catalog on P&G’s intranet) in XL format which are formatted using the XL formatter (Visual Basic script) from the text file generated by Oracle Developer 2000 Reports 3.0
Responsibilities:
Analyzed user requirements, designed enhancements.
Built complex business logic into Oracle views using SQL and PL/SQL.
Involved in SQL query optimization and performance tuning of the reports by creating temporary tables.
Worked in France, Germany and Holland to prepare GAP analysis report working with end users as part of IAMS pet food company acquisition.
Development and testing of reports using Dev/2000 Reports 3.0/6i.
Reports developed in the following domains: Finance, Supply Chain and Market measures.
Scheduled jobs using Control-M.
Use TCS proprietary tool IPMS(Integrated Project Management System)
for project delivery followed standard quality assurance process.
Use TCS’ proprietary CASE based tool CasePac, which enables it to analyze and
reuse the data definitions and mapping rules in its repository.
Technologies used: Oracle 8i/9i (PL/SQL, SQL*Loader), Oracle Reports 3.0, Cognos, UNIX Shell and AWK Scripting, Control-M scheduling, CCC Harvest.
Procter & Gamble, Cincinnati, OH May 1999 Till Aug 1999
Source One data warehouse.
P & G has a data warehouse to meet its DSS needs for finance, market, shipping and supply chain measures. The data warehouse is implemented in Global office and all the four regions (Latin America, North America, Europe and Asia).
Responsibilities:
Involve in the planned design meetings
Assessed the end users business processes and corresponding data requirement.
Prepare and provide Gap analysis document with the legacy system.
Technologies used: Oracle 7.3, Window NT.
Enterprise Business Application (E.B.A), Nov 1998 Till Apr 1999
Kellogg’s- Sydney
Developer
EBA comprised Oracle Material Management, Manugistics and supply chain management. The objective of the system was to develop reports for DSS team.
Responsibilities:
Requirements gathering.
Review of specifications, development and testing of reports using Dev/2000 Reports 3.0.
Tuning of SQL queries.
Written stored procedures using PL/SQL.
Involved in IQA.
Technologies used: SQL, PL/SQL, Oracle 7.1, Reports 2.5.
Contact this candidate