Thamizharasan Gnanaprakasam

Client: Depository Trust and Clearing Corporation

Project: Global Trade Repository (GTR) Cloud Ingestion for EU and UK

It is an application that processes the XML files submitted by the client to S3, flattens them, and loads them to a Snowflake table for reporting purposes.

Responsibilities:

• Analyzed the stories and requirements in JIRA that were groomed in Scrum calls.
• Written Snowpark UDFs using Python language to consume the inbound XML files from S3, do an initial validation on the XML, flatten the data, and load it into the Snowflake table.
• Created an AWS CodePipeline for the deployment of SQL files from Bitbucket to the S3 bucket.
• Created Snowflake tasks and streams to orchestrate our process and to execute the SQL files uploaded to the S3 bucket.

Operating System: Unix, Windows

Languages: SQL, Python, and Unix Shell Scripting.

Cloud Services: AWS S3, AWS Code Pipeline

Cloud Database: Snowflake

Client: Depository Trust and Clearing Corporation

Project: Global Trade Repository (GTR) REFIT for EU and UK

It is an application that processes the trade submission made by parties, calculates the trade position, reconciles the submission with the other party, and generates multiple reports for clients and regulators.

Responsibilities:

• Analyzed the stories and requirements in JIRA that were groomed in Scrum calls.
• Designed Architecture digram for our Project
• Led the development of an ingestion data application to process client-submitted trade files and generate status reports for clients.
• Created Buckets in AWS S3 service
• Created storage integration, external stage for AWS S3 storage, file formats for CSV and JSON file type, and Snowpipe to load inbound JSON and CSV files from S3 to Snowflake tables.
• Designed and implemented the End of Day Reporting Application for trade position calculation and reconciliation, and orchestrated our workflow using DAG tasks and streams.
• Developed vectorized UDFs in Snowpark using the Python language for XML report generation and schema validation using XSLT and XSD.
• Developed a common framework using Python to connect to Snowflake (using the python-snowflake-connector) and S3 (using the boto3 package) to execute the SQL files in Snowflake, unload data from Snowflake to S3, download files from S3 to on-prem, upload files from on-prem to S3, and move/copy files within S3 buckets.
• Written Autosys JILs to schedule our jobs in the Autosys Scheduler.

Operating System: Unix, Windows

Languages: SQL, Python, Unix Shell Scripting, and Autosys Job Information Language (JIL).

Cloud Services: AWS S3, AWS Lambda

Cloud Database: Snowflake

Client: Depository Trust and Clearing Corporation

Project: Data Pipeline Framework

It is a framework designed to migrate data from on-premises databases such as Greenplum and Oracle to Snowflake. The framework comprises multiple layers, and each layer operates independently in parallel without dependencies. It is designed in a way to accommodate any on-premises database as the source with very minimal changes.

Responsibilities:

• Designed the end-to-end flow of the data pipeline using Microsoft Visio.
• Developed the Metadata jobs for the Source DB.
• Designed Data model for the data pipeline.
• Developed Python programs using Oracle (SQL Spool) and Greenplum(gpfdist) for Data Extraction.
• Upload the extracted data from on-prem to AWS S3 bucket using boto3 python package
• Written Snowflake Copy into Statement to load the files in S3 to Snowflake tables.
• At last, reconciled the data between Source and Target DB using python program

Operating System: Unix, Windows

Languages: SQL, Python, Unix Shell Scripting, and Autosys Job Information Language (JIL).

Cloud Services: AWS S3

Cloud Database: Snowflake

Client: Depository Trust and Clearing Corporation

Project: Data Xchange Tool

It is a tool developed in the Python language to convert the CSV file to XML data using XSLT and XSD.

Responsibilities:

• Led the 3 member team to built this tool.
• Designed an architecture digram for this tool.
• Developed a python program to take CSV file as input, split it into small files, convert the CSV data to XML using XSLT and validate using XSD using lxml package.
• Implemented multiprocessing in Python to process multiple files in parallel.
• Implemented a Load Balancer to run this tool on multiple servers.
• Each CSV contains 2 million pieces of data and processes around 10 files at a time.

Operating System: Unix, Windows

Languages: Python, Unix Shell Scripting, and Autosys Job Information Language (JIL).

Client: Depository Trust and Clearing Corporation

Project: Global Trade Repository (GTR) - Reporting Application

It is a reporting application that takes trade files submitted by clients, calculates the outstanding position for each party, reconciles both sides of the trade, and generates multiple reports for participants, regulators, and other TRs.

Responsibilities:

• Led the 10 member team to develop the reporting application for GTR EU and UK Regions.
• Analyzed the stories and requirements in JIRA that were groomed in Scrum calls.
• Developed Greenplum Functions to calculate the outstanding positions of each party, reconcile both sides of trades.
• Written Perl script to connect to Greenplum to execute the functions and write CSV files to On-Prem server.
• Implemented fork in Perl to run the process in parallel.
• Implemented TEE Unix command in Perl to write sane data to multiple files in parallel
• Written autosys JIL to orchestrate our reporting process.
• Reviewed the Greenplum functions written by my team members.
• Did Unit Testing on the components I worked on.

Operating System: Unix, Windows

Languages: SQL, Perl, Unix Shell Scripting, Autosys Job Information Language (JIL)

Database: Greenplum

Client: Depository Trust and Clearing Corporation

Project: Global Trade Repository (GTR) - Migration

It is a migration project where we need to convert the SQLs, PL/SQLs written in Oracle to Greenplum standards, and generate a report.

Responsibilities:

• Gathering the oracle components that need to be converted.
• Created Oracle equivalent DDLs in Greenplum
• Created Greenplum functions for the Assets Commodity, Equity and Interest Rates written in Oracle and in Data Stage
• Written Perl script to connect to greenplum, to execute the functions created in Greenplum and generate CSV reports.
• Create Autosys JIL to schedule and orchestrate our process.

Operating System: Unix, Windows

Languages: SQL, Perl, Unix Shell Scripting, Autosys Job Information Language (JIL)

Database: Greenplum, Oracle

ETL Tool: Data Stage