Ankit Vohra

I spearheaded a project employing SQL, Python, Amazon RDS (PostgreSQL), AWS Lambda, and ETL (Matillion-Snowflake). The focus was on ingesting substantial Excel files into the Snowflake Data Warehouse via the Matillion ETL tool. Additionally, I utilized AWS Glue to extract data from a PostgreSQL table in a separate AWS account, transferring it into a CSV file in AWS S3. This required meticulous configuration of AWS components, including roles and network settings.
Also, I orchestrated AWS Lambda Python functions to fetch data from an external Kobo API and seamlessly ingest it into the Amazon RDS PostgreSQL database. This involved configuration of components such as roles, policies, and network settings, facilitating Lambda's access to the internet, Amazon RDS, AWS Secrets Manager, and Amazon S3.

Furthermore, I undertook the responsibility of generating Excel files using Python and seamlessly uploading them to Google Drive through the functionality provided by the Google Drive API.

I worked on migrating an on-premise Hadoop-HDFS-PySpark2 application to the AWS cloud, utilizing S3 storage, Parquet format, RDS database, and EMR clusters to run code in PySpark 3.
Also, the same application was made compatible with Azure cloud, utilizing Databricks clusters for processing, dbfs/ADLS2 storage, and PySpark3 code.
The whole code was made Python3 compatible in the process.
Also, the application was made cloud agnostic, i.e., it was ensured that the same code could run successfully in on-premise, AWS, and Azure cloud environments.
The application was run and tested on the cloud and compared with on-premise output results.

Automated on-demand data export and import among tables in remote Oracle databases via shell scripting. The script can be called by a user with a session ID argument, using a DBMS scheduler job.

Input Parameters:
SOURCE_DATABASE
TARGET_DATABASE
SOURCE_SCHEMA
TARGET_SCHEMA
SOURCE_TABLE
TARGET_TABLE

The user can load the parameters into a CONTROL_TBL_, where session_id is some randomly generated number; for example, CONTROL_TBL_12345.

The job will accept the same session_id as an argument, read from the corresponding control table present in the target DB, and process the records to load data from the source table into the target table (after dropping if it already existed).

As required, the solution:
i) Imported and refreshed data as per provided input.
ii) Put table metadata (indexes, constraints, triggers, grants, etc.) into a CLOB column (in control table only), which the user could retrieve and run when needed.
iii) Put the error log into the same CLOB column for analysis purposes in case of failure.

An Oracle data warehouse had been refreshed from a source DB via nightly running PL/SQL jobs that were, in turn, triggered via Tivoli Workload Scheduler.

Jobs used to span around six to eight hours and continuous monitoring was required for any failures/errors followed by steps to fix data sync issues after the job run window.

I automated the monitoring process via a Unix shell script that did the following:
1. Connected to the Oracle DB via SQL Plus.
2. Queried job(s) status and picked any failing jobs with an error message.
3. Dumped the information in an intermediate log file.
4. Emailed the file to the intended recipients.

The automated job was scheduled via cron to run in 30-minute intervals and send error emails after each job failure, rather than waiting six to eight hours until the end of the job. In instances of success, only one email was sent at the end run of the job to confirm successful completion.

A data warehouse database had two huge partitioned tables, 500 and 300 GB in size, with foreign key relationships among them. The client was concerned about query performance and table size. The requirement was to purge the tables and retain only the last two years of data while maintaining referential data integrity in the process and retaining original tables as history tables.

PROCESS
• Let the original huge tables as P (parent) and C (child).
• Let P_NEW and C_NEW as the tables with the last two years of data.

1. Exported data for P & C via parallelism.
2. Renamed constraints and indexes of P & C as the import; will attempt to create them with the same names and will fail.
3. Imported DMP files in the P_NEW table with parallelism and disabled logging.
4. Imported DMP files into C_NEW with parallelism by EXCLUDING REF_CONSTRAINT and disabled logging.
5. Created FK constraint on C_NEW referencing P_NEW in with NOVALIDATE.
6. ENABLE VALIDATE the REF constraint was tricky. I resolved the referential data mismatch by catching missing parent keys in child via an EXCEPTIONS table mechanism and then inserting it into parent via a loop.
7. Renamed P and C to P_HISTORY and C_HISTORY. Renamed P_NEW and C_NEW to P and C.

The client required denormalized data from three different systems to be dumped into Amazon S3 for efficient data retrieval for business intelligence. I built a generic data model to accommodate those entities.

Three similar systems (A, B, C) stored account and finance data for a healthcare client. None of the systems had all the required data, and all three had to be referenced for data analysis. The requirement was to extract the data of relevant common entities from the systems and fit it into a generic, denormalized data model that would store data from all the systems in Amazon S3 for intelligence gathering via Spark clusters.

I first understood the database structure for the systems, then built a generic physical model with entities, attributes, and queries to fetch data from each database. System A had demographic data, B had account data, and C had product-related info in normalized tables.

Here, a generic, denormalized account entity was required to have a single record for a given customer with all the info in a single row (e.g., account, demographic, and product info). Data was pulled via queries through an Informatica ETL process, merged into parquet files, and placed in Amazon S3 for analysis via Spark clusters.

As part of migrating a data warehouse application from an AIX (Unix) server to a Windows server, the code in driving shell scripts had to be rewritten into a Windows batch script.

Functionality included the following:
1. Check the presence of a control file for a job.
2. Call the stored procedure corresponding to the control file's content.
3. Check the final status of the executed job and write it to a file.

I converted the Unix shell script code into a Windows batch script as per the functionality and rescheduled the new job call commands on the new Windows server via the Tivoli Workload Scheduler.

While working on an energy and utility client's electrical outage management system application—a network manager distribution management system (ABB-NMDMS)—a customer communication project (CCP) came up. I wrote code for the following functionalities in Oracle PL/SQL, SQL, and Unix shell scripting:

1. For each electrical outage, get all impacted customer account IDs from two different data sources and put the non-duplicate IDs in a file every 30 minutes. The file would be consumed by an external application to send SMSs to customers when they have an electrical outage at their home, along with an estimated power restoration time.

2. Generate a separate file for duplicate account IDs and send an email (via uuencode) to intended recipients only when a different account ID is present. If an email for the same ID was already sent and the same ID comes again, then the same email should not be sent.

I built the technical specification document and did the coding for the same.

A database application for a basketball league management system (BLMS) as per the requirements given. I developed the database model and schema for entities, such as players, teams, seasons, and contracts, and I created required objects, such as tables, database links, procedures, and functions, to fulfill business requirements.

Link to the full document: https://dbheart.com/oracle/files_to_download/AID_BLMS_AV.pdf

Examples of reporting functionality:
1. Most expensive players
2. Most expensive lineup
3. Injured players
4. Player trading among teams
5. Replication via database links

Additional deliverables:
1. System context diagram
2. Use case model
3. Architecture overview
4. Location model
5. Logical and physical deployment models
6. Entity Relationship diagram (data modeling)

Developed Python code to read word meanings from a JSON file, suggest alternatives when words are misspelled, and display the results of the matching word.

I also developed Python code to scrape a website with these options:
i) Check all the pages of a bookstore website to get all unique authors and the top-10 tags from the page.
ii) Check all pages of a bookstore and get books with 5-star ratings only.

A website with several topics related to Oracle Database and Unix technology. The contents can be viewed easily on a PC, mobile, or tablet via a browser. I built the website with CSS, HTML, and Bootstrap.