Venkatesan Rajendran

Email: adx5sz@r.postjobfree.com Cell: 302-***-****

LinkedIn: https://www.linkedin.com/in/venkat-rajendran-b9813016/ SUMMARY

• AWS Certified Cloud Practitioner, Data Architect, Data Modeler, ETL Architect, Oracle Database Developer with 22+ years of total industry experience in Data Warehousing and Big Data Analytics in Healthcare, Communications, Retail, Banking, and Financial Services domain.

• Technology – Amazon Redshift, Snowflake, PostgreSQL, Oracle, Python, Matillion, Pentaho, Looker, Tableau, AWS and Data Modeling tools such as SQLDBM and Erwin.

• Roles and Skills - Data Architect, Data Analysis, Data Visualization, ETL Architect, Database Developer, Data Modeler along with proven excellence in Project Management, Problem Solving, Continuous Improvement for Fortune 500 companies with actively involved in the design, architecture, development, implementation and maintenance of databases and software systems.

• Extensive work experience in defining and implementing ETL processes, System Integration Architecture, building data warehouse using Dimensional Data Modeling methodology (Kimball’s approach), Business Intelligence Fact Tables, Type 1, Type 2, Type 3 SCDs (Slowly Changing Dimensions), Incremental ETL workflow using (CDC) Change Data Capture.

PROFESSIONAL EXPERIENCE

Artera (formerly WELL Health Inc.) Santa Barbara, CA (Remote) Data Architect Jan. 2022 – Jun. 2023

• Data Model Total Refactor (DMTR) – Feb.2022 – Mar.2023

As part of Artera’s major initiative for the Data Analytics Team and as there wasn’t any standard Data Modeling methodologies been implemented or followed, causing huge increasing storage cost and the delay in data due to performance issues within the Matillion ETL jobs, took up and worked individually on this critical total refactor project with the goal of creating a new Data Model from the scratch for the Data Team’s Amazon Redshift data warehouse and its majority of source data is coming from Amazon RDS

(PostgreSQL).This involved extensive analysis work on finding the current gaps and issues with the existing systems, gathered various business requirement related info to determine what Data Modeling methodology would be best suitable for our day-to-day ETL development work and eventually for building BI reporting dashboards (Looker). After various discussions with the Engineering teams who owns the Source DB Data Model and its design, Data Engineers, Data Analysts, Data Scientists, AWS Redshift Specialists and with the different business stakeholders finalized the Kimball’s Dimensional Modeling methodology using Star schema approach to be implemented.

Worked on end-to-end analysis starting from the source DB tables (PostgreSQL) and few structures in MongoDB vs target (Redshift) DB tables, eliminated any existing redundancies and anomalies at the table design and column levels, created a robust mapping documentation (Logical Data Mapping sheet) meticulously capturing all the necessary key info required at the table, field, data type level and along with identified Slowly Changing Dimensions and its columns with the respective SCD Types such as Type 1 and 2 (history), also identified, fine-tuned and captured all the required business rules to be applied as part of the transformation logic within the ETL jobs that’s been applied for both the core dimensions and for the fact tables used for BI reporting.

Built and generated Conceptual and Logical Data Model(s) using SQLDBM a cloud-based Data Modeling tool, had many review sessions with all the aforementioned teams and finally built a Physical Data Model, generated DDL scripts and implemented them within the Amazon Redshift data warehouse.

After migrated from Redshift to Snowflake Cloud Data Warehouse, scheduled meetings with Snowflake Data Model experts, studied the best practices, re-visited the entire Data Model and adjusted them for Snowflake such as convert Redshift Data Types into Snowflake’s native data types for better performance and efficient storage handling of semi-structured (JSON) data using VARIANT data type.

• Amazon Redshift to Snowflake Migration – Dec.2022 – Mar.2023

Actively involved in Amazon Redshift to Snowflake Cloud Data Warehouse migration project another company’s cost benefitting initiative by partnering with a company called Ki.Pi, setup environment, written and automated UNLOAD/COPY scripts via AWS S3 for data transfer, adjusted Matillion ETL jobs to match with Snowflake’s syntax and datatype, data validation and finally created Snowflake monitoring dashboards

(credits usage, total # of jobs executed, data storage usage, top 25 long running queries etc.,) with Snowsight.

Created RBAC setup in Snowflake DB. Involved in PHI masking initiatives and environment separation.

• Problem Solving – Practice Render Analytics Dashboard (Python) job runs every Sunday, queries the large volume Redshift DB tables via Looker BI tool for over 11,500 enterprise and practice combination, generates a bi-weekly report, converts it into a pdf and uploads it to AWS S3. And because of the slow SQL performance the job runs for more than 24~30 hours and due to resource contention other nightly ETL jobs’ runtimes were affected. As a snowball effect this caused significant delay in all the daily report generation processes in Looker BI. Upon analyzing the problem, identified the root cause. Replaced all the high-cost SQL queries with Materialized Views and thereby eliminated the heavy burden on querying the large volume tables. Finally created a separate Looker dashboard to query directly from all the new MVs and with this implementation we achieved a greater performance improvement and the entire job now runs within 6~8 hours.

• Process Improvement – Collaborated with Data Scientist to improve data architecture and query structure around the company's monthly billing process that would run for 24~30 hours against our database each month causing huge performance impact on both the ETL and BI jobs. Following our refactor with the entire ETL pipeline redesigned, the process ran in 97 minutes (improving efficiency by ~95%!) and this refactor allowed now to have a more cleaner data set with which we could forecast future client demand. This led to over $2 million in projected client overages being identified within 30 days of turning the predictive model on. NBC Universal Englewood Cliffs, NJ (Contract - Remote) Tech Lead/Sr. Developer Apr. 2020 – Jan. 2022

• Worked and supported legacy applications written in Oracle PL/SQL (12c).

• Developed and maintained ETL/Data Migration projects and enhancements using Oracle PL/SQL, Packages also involving performance tuning on slow running procedures.

• Created Oracle Triggers to achieve real-time refreshing of source data into target tables. Comcast Cable Philadelphia, PA (Contract) Tech Lead/Sr. Developer Nov. 2011 – Mar. 2020

• Worked in building multiple ETL data pipeline and data migration projects for large volume data warehouse using Pentaho Data Integration (Kettle) Tool, Oracle PL/SQL and Big Data Technologies such as Apache Kafka, Pig, Hive, HDFS and Cassandra and optimized many critical ETL workflow data pipelines for performance.

• Worked on building ER Model, Logical and Physical Design for Star Schema.

• Built ETL jobs using Dimensional Modeling data warehouse (Star Schema), used Dimension (Type 1, Type 2) and Fact Tables. And worked on understanding Business Logic, Client requirements, created LDM documents

(Source/Target mapping document), data profiling and analysis using Talend data profiling tool and developing Pentaho ETL jobs using Centos Linux, Oracle PL/SQL code and created or modifying existing DB objects like Packages, Procedures, Triggers, Views, Materialized Views, Indexes, Constraints, Tables and Table partitioning/sub- partitioning using DATE and HOUR key(s) in Oracle 12c/11g/10g versions.

• Worked in resolving complex performance issues and fine tuning both the ETL data pipeline(s) and Oracle SQLs and stored procedures, used OEM (Oracle Enterprise Manager) and gather Real-Time SQL Monitoring reports for identifying and analyzing long running SQL queries for further fine tuning. Documented thoroughly all the Database Objects related changes and used Liquibase tool for tracking, versioning and deploy all database changes and importantly for any rollbacks.

• Reduced runtime of a mission-critical ETL job developed using Pentaho PDI ETL Tool and Oracle database for C- level executives from 6 hours to 15 minutes.

• Developed Reports and dashboards using Tableau.

• As a Database Migration expert has experience in preparing and documenting the complete Migration plan document and used Toad, SQL*Plus, PL/SQL, SQL*Loader, Oracle Data Pump Utility, UNIX Shell Programming.

• XEN Data Migration Project which involves the complete data migration/transfer from existing Downingtown servers to Broad Servers to suit the need for various data analytics requirement and also developed various Tableau dashboards for Business reporting.

• Experienced in defining enterprise level data migration architecture from any Legacy Systems to Staging and ODS data into Oracle database. Proven experience in defining ETL processes and system integration architectures combining data from disparate source systems and disparate data formats from a huge database containing multi- million rows of data. Has strong hands-on in creating and scheduling tasks using Stonebranch Opswise Automation Center.

• Experience in creating Apache Pig scripts for collecting various raw XRE event type(s) data and filter the raw data based on specific message format from the HDFS, store it in the partitioned Hive table (DATE_KEY and HOUR_KEY partition) and then used Pentaho PDI Kettle job to extract data from Hive table and load it into the Oracle database.

• Handled large volume data warehousing using Big Data Technologies with good hands-on work experience in handling AWS S3, Lambda, CloudWatch, Kinesis, SNS and CloudFormation.

• Developed re-usable Databricks Notebook jobs to handle variety of data analytics requirements such as data quality analysis and develop reporting dashboards (Avro/Parquet/Json from AWS S3) through Apache Spark using Python/Scala and either SQL or DataFrame API.

• Developed Apache Spark/Scala module to stream and aggregate data from Kafka and store in Oracle database.

• Developed alert programs for Volume and Completeness Dashboard for various event types for all the delivery partners and clients using Java and configured to run in Stonebranch Opswise Automation Center. Also developed volume comparison alert programs using Splunk query and Oracle SQL embedded within Java.

• Developed a Lambda using Java which will eventually calculate Volume Metrics by consuming data from a given Kinesis stream and emit the metrics to the AWS CloudWatch.

• Maintained and supported many of the Big Data applications using Spark Streaming and SQL APIs in Scala for raw data enrichment which ingest events from Kafka, convert to avro structures, enrich and persist to Amazon Kinesis.

• Created and supported AWS Lambda that ingest data from Kinesis Data Streams and store data in S3 buckets. Used AWS CloudFormation to save time and reusability, created yaml template files based on pre-defined configurations in order to create and maintain AWS Lambda functions and AWS CloudWatch alarms. CVS Caremark Monroeville, PA (Contract) Data Architect and Oracle PL/SQL Tech Lead Sep. 2010 – Oct. 2011

• Created the Logical and Physical Data Model and built the database for Clinical Assessment and Event Management modules for RxSpecialty Clinical Management. Offered the Design and Architectural solutions for the development activities on the Humira Clinical Assessment Database (Oracle and Linux OS), led SPARCS-RxSpecialty XML Interface project for Data Migration. Worked in developing Oracle PL/SQL, (Collections and Triggers) to handle the XML document transactions between SPARCS and RxSpecialty, analyzed various on Oracle Forms and responsible for all the CM related activities in PVCS Version Management software. CVS Caremark Lincolnshire, IL (Contract) Oracle Pl/SQL Developer Jul. 2009 – Aug. 2010

• Responsible for identifying performance bottlenecks and tuning SQL queries used by the Java interface modules of Enterprise Eligibility System (EES), a Master Data Management project.

• Created many PL/SQL procedures and executed the entire EES ODS data migration scripts using Linux OS.

• Implemented Oracle fine tuning processes in order to achieve maximum database performance by using SQL TRACE and TKPROF tools for Explain Plan, SQL tuning, collection of statistics and creation of indexes for database access and indexing the required columns. Created a unique, automated solution from an existing manual process saving developers hours, Oracle XMLDB package that parsed and extracted fields from huge incoming XML data files to store directly in Oracle database

First Data Debit Services Wilmington, DE (Contract) Oracle PL/SQL Tech Lead/Developer Jul. 2007 – Jun. 2009

• Supported Debit Reporting applications by creating new programs and modelling data HEB Grocery Company San Antonio, TX (Contract) Oracle Tech Lead/Developer Oct. 2006 – May 2007

• Scoped, Developed, and Implemented PMM Upgrade 2006.1.x activities including retrofitting 30+ PL/SQL programs, testing, and creating a patch with the Oracle 10g database fine tuning scripts.

• Completed 6-month project in 3 months, allowing the client to prepone launch for increased profits Carphone Warehouse Cognizant, Chennai, India Data Architect June 2006 – Oct. 2006

• Built the Logical and Physical Data Model using Erwin for Partner Dashboard Development project, set-up and maintained the database development environment for entire team including purging activities through DB Purge PL/SQL programs for Batch Jobs.

7-Eleven Cognizant, Chennai, India Data Architect Jan. 2006 – May 2006

• Built the Logical and Physical Data Model for the database to streamline payment processing and inventory booking of incoming merchandise. Set-up and maintained database development environment for offshore development team, created all SQL queries

RadioShack Cognizant, Chennai, India Oracle PL/SQL Developer Apr. 2004 – Dec. 2005

• Designed and developed reporting projects to streamline purchase orders, costing, and inventory valuations Citibank N.A. YCS, India Pro*C, Oracle PL/SQL Developer Feb. 2002 – Apr. 2004

• Developed testing and support activities for multiple Citibank projects State Bank of India YCS, India Unix Shell Scripting / Pro*C / Oracle Developer Apr. 2001 – Jan. 2002

• Developed and supported programs for Transaction Manager TECHNICAL SKILLS

Programming / Scripting

Languages:

Java/Python/Scala (Beginner level), Oracle SQL*Plus, PL/SQL, UNIX Shell Scripting and created AWS Lambda Functions using Java.

Databases: Amazon Redshift, Snowflake, PostgreSQL, Oracle, MySQL, Apache Cassandra NoSQL database, MemSQL/Redis in-memory Databases.

Oracle Database Design, Data Modeling, Database Management and Administration in 9i/10g/11g/12c, SQL*Loader, Oracle Datapump utility, XML Schema Design (XSD) for Web Services, XML design and managing in Oracle 11g database using XMLDB. ETL Tools: Matillion ETL, Pentaho Data Integration (Kettle) Tool (6+ years), Informatica, Initiate MDM/Clover ETL Tool.

Big Data Technologies: Apache Spark 1.6.1/2.0, Apache Kafka, AWS S3/Lambda/CloudWatch/Kinesis/ CloudFormation, Apache Pig, HDFS/Hive, Cassandra and notebook jobs in Databricks platform.

Analysis & Reporting: Looker BI tool, AWS CloudWatch, Tableau 9.3, Splunk 6, Crystal Reports 9. Automation: Stonebranch Opswise Automation Center (Batch Job Scheduler). Development: IntelliJ/Eclipse using Maven, Toad for Oracle 12.6, Oracle SQL Developer. Data Modeling: SQLDBM (Kimball’s Dimensional Data Model and Data Vault), Erwin Data Modeler, Microsoft Visio 2003.

Operating Systems: Sun OS 5.6/5.2, Linux6.2, 7.1 (Red Hat, Centos, Ubuntu), SCO UnixWare, IBM AIX Unix, Win- 95/98/NT/Win2000,

MS Windows XP, MS-DOS.

Hardware: Sun Solaris, Linux, IBM AIX, DEC, HP UNIX, Pentium 4. Other: Subversion, GitHub, Liquibase, Jenkins Deployment tool, Serena Dimensions, PVCS Version Management, Microsoft VSS, MKS Integrity Suite, Clear Case. EDUCATION

Bachelor of Engineering in Computer Science University of Madras India 2000 Certifications

• AWS Certified Cloud Practitioner AWS https://www.youracclaim.com/badges/0972c4e7-815b-4d58-a67e- 9095d4bfb855 2020

• Hortonworks Certified Associate Hortonworks http://bcert.me/szqgcsrr 2017

• Splunk 6 Knowledge Objects Splunk University 2014 EMPLOYMENT HISTORY

• Artera (Formerly WELL Health Inc.) Jan-2022 – Jun-2023

• Cognizant Technology Solutions US Corp. Oct-2006 – Jan-2022

• Cognizant Technology Solutions (India Private Ltd.) Apr-2004 – Oct-2006 Chennai, India

• Yalamanchili Consultancy Services (India Private Ltd.) Jan-2001 – Apr-2004 Chennai, India

Contact this candidate