Summary
Overview
Work History
Education
Skills
Websites
Personal Information
Certification
Languages
Timeline
Generic

JAYRAJ GUPTA

COLUMBUS,OH

Summary

Data-driven Engineering Architect with 13 years of IT experience in Data Warehouse Architecture, Database Design, Business intelligence, Data Modeling, Data Analytical BI Solutions, and Data Integration implementation.

  • Senior Data Warehouse Architect with 13 years of expertise in Data Warehouse Architecture, Database Design, Multi-dimensional Enterprise Data modeling, Data pipeline implementation, and Business Intelligence. Demonstrates exceptional skills in Snowflake, Azure, SNOWPIPE streaming, Kafka connector, SnowSpark, PySpark, Power Designer, Erwin, Power BI, Qlikview/Qlik Sense, Business Objects, Cognos, MicroStrategy, BigData, Spark programming, AtScale, ADF, DataStage, Informatica, Boomi., driving innovative data solutions and seamless integration. Passionate about leveraging advanced analytics and data modeling to transform business insights into strategic actions.
  • Over 10 years of experience in leading Data and BI Teams in full SDLC of Data Warehouse projects from Scoping, Analyzing detailed Business Requirements, Data Analysis, Understanding Business procedures, and problems reviewing existing systems, cost, performance, and capabilities, architect Data warehousing, Designing databases, modeling Enterprise data models and data governance solutions, implementation of complex data integration processes to improve and build new BI Analytical systems.
  • 10 Years of specialization in conducting Proof Of Concepts and rapid prototyping a model to find the most efficient Data Integration / BI solutions. Completed several POCs using different data integration, BI tools such as Kafka, Google Cloud BigQuery, Azure Machine Learning, HD Insight, Apache Beam, AtScale, and SQL dbm by prototyping a model to demonstrate leadership efficiency, features, and cost savings.
  • Led team to successfully migrate more than 100 TB databases from Legacy Databases to Snowflake databases in the most efficient way by laying out a detailed plan, using the right tools and procedures, and creating technical documentation to guide and instruct the team.
  • Excellent experience in sizing of integration Snowflake warehouses by estimating query load, configuring security role-based access control hierarchy, TPT scripts/pipelines, snow pipe, azure blob storage, and Snowflake stages to migrate historical data/ongoing data from Legacy database into the Snowflake. Also, built a data validation tool to automate end-to-end row-level data comparison mechanism between Snowflake and Legacy databases.
  • Good amount of experience in setting up the secured data shares, listings, and reader accounts in Snowflake to securely share the data with consumers and build dynamic tables, and streams from databases from inbound data shares.
  • Proficient in existing data model optimization, query profiling, data profiling, virtual warehouse, cluster sizing, setting up resource monitor, and building cost usage dashboards to help cut down the Snowflake or any database credit usage by different applications, and to optimize the large-size databases.
  • Highly skilled in designing Data Lake architecture, streaming Data pipeline, Semantic layer data model on databases like Snowflake, setting up ELT pipelines to consume operational data in JSON/XML from various messaging queues using integration solutions like – Snowflake Tasks, Streams, External stages, and Snowflake Dynamic tables, SNOWPIPE, Azure, PySpark, SnowSpark to facilitate close to real-time data availability.

Overview

14
14
years of professional experience
1
1
Certification

Work History

SNOWFLAKE DATA WAREHOUSE ARCHITECT

Big Lots Inc
11.2022 - Current
  • Conducted POC using different BI Modeling tools such as AtScale, and Tableau Editor to facilitate Semantics of data to Business.
  • Built semantic data model, and defined data governance across different business data sets, configured pipelines to populate data in data marts using Snowflake dynamic table, Materialized views, and Tasks.
  • Performed POC using different Integration solutions to build a Data Lake architecture design template in Snowflake from upstream systems like SAP, OMS, POS, and WMS.
  • Worked on setting up a streaming real-time integration to read semi-structured data published into Kafka topic and ingest daily transaction, and inventory, marketplace data in raw tables in Snowflake using Snowpipe streaming and Kafka connector.
  • Built and scheduled dynamic tables to cleanse and transform the raw tables in the data lake into Final tables in data marts in the semantic layer.
  • Set up storage accounts, Event grid, queue, and system topic on Azure to auto-ingest data files from SAP system into Snowflake raw tables using Snowpipe.
  • Set up Database, schemas, role-based access control, and Snowflake warehouse sizes for the Data Lake integration model.
  • Created data model and designed solution to bring historical data and incremental data using tools- SnowPIPE, Snowspark, and SnowSQL from SAP, OMS, and POS in a real-time mode.
  • Worked on setting up data shares, listings, and snowflake reader accounts to share the secured data with vendors.

DATA WAREHOUSE ARCHITECT

Big Lots Inc
03.2022 - 10.2022
  • Created a dashboard in Snowsight and PowerBI to analyze the cost/credit usage of complex SQL queries running on Snowflake by application, Warehouse.
  • Optimized data model to cut down the cost of complex queries running on Snowflake.
  • Set up a Resource monitor to monitor the virtual Warehouse credit usage.
  • Set up ELT data model and pipeline architecture to facilitate close to real-time data availability.
  • Designed data lake on Snowflake. Set up SNOWPIPE, Boomi pipeline to publish data sets and message queue JSON messages into Azure blob storage.
  • Used Tasks, Streams, External stages, and Snowflake Dynamic tables for real-time data ingestion in Data Lakes, Semantic layer for reporting.
  • Analyzed query execution patterns on a warehouse and resized the warehouse size to cut down the excessive cost.
  • Worked on analyzing the query profile and tuning of long-running SQL by adding the right cluster keys on tables, and creating materialized views, and dynamic tables in order to save credit usage.
  • Defined strategy to share scheduled data sets with Vendors by configuring Snowflake's data shares and Snowflake reader account.

DATA WAREHOUSE ARCHITECT

Big Lots Inc
09.2020 - 02.2022
  • Conducted data migration POC to move data from Teradata into Snowflake and gathered the performance statistics.
  • Defined new architecture for real-time Point-of-sale integration in Snowflake.
  • Analyzed database size, and data volume for historical data migration and designed a strategy to move historical/delta data to Snowflake most efficiently.
  • Have worked on collecting Inventory for all Data Warehouse components, and objects e.g. (ETL Jobs, bteq, multiload, shell scripts inventories, and Teradata objects to be referred to in Snowflake migration for impact analysis and effort estimation.
  • Worked on the sizing of different integration snowflake warehouses by estimating query load/volume over a period of time.
  • Defined role-based access control hierarchy to set up database security roles- Functional, operational, and schema roles.
  • Set up TPT scripts/pipelines, snow pipe, azure blob storage, and Snowflake stages to migrate historical data/delta data from Teradata into the Snowflake database.
  • Designed and developed data validation tool to automate end to end data comparison mechanism between Snowflake and Teradata
  • Designed a Data Lineage utility in JAVA to generate a detailed ETL Lineage report (Job details, source database object, target database object, Operation type, Object type, Keys) to Support planning and expedite the execution of ETL migration testing for the snowflake conversion project.
  • Set up scheduled processes using Airflow to migrate ongoing data from Teradata into Snowflake.
  • Have created a new design and development standard for any new data Integration framework on Snowflake to leverage the use of Azure blob for landing incoming files, SNOWPIPE, Raw table to ingest MQ messages, data quality/exception handling mechanism, and leverage the use of snowflake Streams/Tasks to cut down the amount of development/testing and support efforts on DataStage.
  • Guided and supported different onboarded teams with migration/DataStage testing procedures and data validation/performance test results.

ETL CONSULTANT/ARCHITECT

g2o LLC
11.2019 - 08.2020
  • Working with Business Analysts to understand the transactional system and create mapping and ETL design documents.
  • Working with Businesses to understand, scope, and analyze the detailed requirements of existing and new DW/BI projects, designed the ETL/Data Integration solution for OLTP/OLAP systems.
  • Designed and developed logical/physical optimized snowflake, star schema, 3rd normal form, relational and multidimensional data models to process and store incoming real-time various types of order/sale transaction data.
  • Worked with database administrators and created landing, staging, and reporting database layers, tables and logical views in the Teradata database to store daily incoming transactional data from multiple locations and data source points.
  • Created message queues using IBM MQ WebSphere Explorer/MQ Connector to capture the daily streaming raw order/sales transactional messages.
  • Designed and implemented a real-time ETL application to consume daily streaming order/transactional raw data from the message queue to parse the semi-structured, unstructured data (XML/JSON) and load into the Data warehouse system using the tool Hierarchical Data Stage.
  • Worked on rewriting the DataStage real-time integration for daily transactional data ingestion into Snowpipe streaming.
  • Set up Kafka topic to receive real-time transactional xmls, and jsons into snowflake variant table using Kafka connector and Snowpipe streaming. Built streams and tasks to parse the JSON and refresh the final tables from the Variant raw table.
  • Used toolkits-DataStage, MicroStrategy, SQL, Stored Procedures, Informatica, UNIX/Linux shell scripting to develop extract, transform, and load (ETL) processes for DW/BI reporting applications and Data Integration solutions.
  • Prepared documentation related to ETL process flow including technical designs, functional designs, data mapping, use cases and ETL test results.
  • Developed Unix/Linux shell scripts to automate operations within ETL.
  • Created Windows batch scripts/Stored procedures to perform Teradata load operations.
  • Set up mechanism to load ETL audit/logging information into database using Stored Procedures.
  • Have worked on troubleshooting and tuning the performance of existing running data warehouse applications.
  • Worked on designing, and developing of various complex data analytics and BI reporting applications using MicroStrategy as a data visualization tool.
  • Created MicroStrategy Business Intelligence executive dashboards with different metrics, insights, and visualization capabilities.
  • Conducted Proof of Concepts with a GCP/Azure cloud solution using various ETL/Data Integration tools like DataStage, Snowflake connector, Hadoop Spark, Python on Snowflake data warehouse.
  • Wrote complex SQLs to join structured and unstructured data to load into a final target Snowflake warehouse table.

SENIOR DATAWAREHOUSE/ETL CONSULTANT

Ford Credit
09.2019 - 11.2019
  • Working with the Product Owner to understand the project requirement on different User stories.
  • Performed analysis, and worked on the Design and Development of ETL processes using DataStage for Business intelligence and Data Warehouse projects.
  • provided technical expertise on ETL Design, Data modeling, Data Warehousing, and Data Integration.
  • Worked on Handling all aspects of the systems life cycle for ETL processes related to DB2, Teradata, and extracting files using DataStage, Database, and Warehousing skills.
  • Worked with Product owner to understand the project requirement, and data flow on different User stories and prepared data mapping document.
  • Worked with DBA to design Global Datawarehouse solution and developed relational and multidimensional Financial data models using Power Designer as a data modeling tool.
  • Developed Complex data extraction SQL queries to pull data from various operational data stores/Enterprise data warehouse tables.
  • Created GDW warehouse, mart databases, tables, and views on Teradata for several finance business areas.
  • Developed Data Integration solution to read data, transform, and load financial data into created Wearhouse/Mart tables using DataStage, Unix/Linux scripting, and Stored Procedures.
  • Developed Business reporting dashboards on top of GDW mart tables projecting several financial metrics.
  • Wrote JIL files to set up a scheduling mechanism for ETL applications in AUTOSYS.
  • Worked on preparing user test case scenarios and testing documentation.

ETL CONSULTANT/ARCHITECT

BigLots
07.2019 - 09.2019
  • Working with Business Analysts to understand the transactional system and create mapping and ETL design documents.
  • Working with Businesses to understand, scoping, and analyze the detailed requirements of existing and new DW/BI projects, designed the ETL/Data Integration solution for OLTP/OLAP systems.
  • Designed and developed logical/physical optimized snowflake, star schema, 3rd normal form, relational and multidimensional data models to process and store incoming real-time various types of order/sale transaction data.
  • Worked with database administrators and created landing,staging, and reporting database layers, tables and logical views in the Teradata database to store daily incoming transactional data from multiple locations and data source points.
  • Created message queues using IBM MQ WebSphere Explorer/MQ Connector to capture the daily streaming raw order/sales transactional messages.
  • Designed and implemented a real-time ETL application to consume daily streaming order/transactional raw data from message queue to parse the semi-structured, unstructured data (XML/JSON) and load into Data warehouse system.
  • Used toolkits-DataStage, MicroStrategy, SQL,Stored Procedures, Informatica, UNIX/Linux shell scripting to develop extract, transform, and load (ETL) processes for DW/BI reporting applications and Data Integration solutions.
  • Prepared documentations related to ETL process flow including technical designs, functional designs, data mapping, use cases and ETL test results.
  • Developed Unix/Linux shell scripts to automate operations within ETL.
  • Created windows batch scripts/Stored procedures to perform Teradata load operations.
  • Set up mechanism to load ETL audit/logging information into database using Stored Procedures.
  • Have worked on troubleshooting and tuning the performance of existing running data warehouse applications.
  • Worked on designing, development of various complex data analytics and BI reporting applications using MicroStrategy as data visualization tool.
  • Created MicroStrategy Business Intelligence executive dashboards with different metrics, insights, and visualization capabilities.

BUSINESS INTELLIGENCE LEAD

FIAT Chrysler LLC
06.2019 - 07.2019
  • Worked on the Business role to understand the Daily Warranty operational data and prepared list of key derived metrics.
  • Did analysis on Warranty Database to understand the tables, data granularity, and relationships among them.
  • Prepared Requirement understanding documents about project requirements, design and implementation plan.
  • Worked with Data team to create required Warranty mart tables in the Hadoop environment and set up the sqoop jobs to load them daily from the operational database.
  • Created Qlik Sense load scripts to generate QVD files on an increment basis for Fact and dimension tables.
  • Created Transformation and Application Qlik Data model.
  • Used Qlik Extensions from Qlik branch library to these dashboards.
  • Worked on tuning the Extraction SQLs, and load strategy to cut down the Application run time.
  • Built visualizations on Warranty Key KPIS in Qlik Sense.
  • Worked with all business stakeholders to perform the data validation.

DATAWAREHOUSE LEAD

FIAT Chrysler LLC
01.2019 - 05.2019
  • Led the data migration project from Customer Care Mainframe legacy system into Salesforce cloud Objects using IBM DataStage 11.5 as ETL DataStage senior developer.
  • Worked with Business and Analysts to understand the system and created data/ETL mapping document and business rules for the data migration task into salesforce.
  • Worked with DA and Created Landing and Staging tables to complete data migration of Powertrain PSC Customer Call Center data from a legacy system into Salesforce cloud.
  • Created complex ETL Strategies to implement File-based integration with Salesforce and other operational systems and third-party vendors.
  • Created ETL strategy to read data from JSON files shared by web services and push the data into salesforce objects incorporating data transformations and applying business rules on them
  • Have used Hierarchical Data Stage to read data from Vehicle Service System to capture Vehicle information in JSON format and load it into Salesforce cloud object.
  • Developed Customer care, Vehicle Recall warranty data visualization dashboards using Qlik.
  • Complete Design document, UAT sign-off document, and generated test results.

DATAWAREHOUSE/BUSINESS INTELLIGENCE LEAD

Delphi Automotive Systems
03.2018 - 12.2018
  • Worked on integration of ERP systems SAP, P0X, ECPTS into Enterprise Datawarehouse in Teradata using Informatica PowerCenter ETL.
  • Daily interaction with business for requirement gathering, design analysis, development, UAT meeting and launch.
  • Responsible for defining business rules for each KPI and preparation of source to Target data/ETL mapping document for Landing, Core and semantic layer Teradata databases.
  • Building data model, created Core, Semantic tables/views to inhouse ERP data into EDW system.
  • Providing ETL solutions using Teradata, Informatica PowerCenter to bring Data from different SAP ERP systems into Enterprise Datawarehouse.
  • Developed ETL Mapping, Workflow, and Sessions using Informatica tool. Created Mappings to load data using various transformations like Source Qualifier, Sorter, Lookup, Expression, Router, Joiner, Filter, Update Strategy, and Aggregator transformations.
  • Wrote numerous Complex Transactional SQL queries for Views/Nested views in Teradata to bring data from the staging database layer into core and semantic.

DATAWAREHOUSE LEAD

FIAT Chrysler LLC
12.2010 - 02.2018
  • • Designed and developed Data Integration, Workflow Solutions and Extract, Transform and Load (ETL) solutions for data warehousing.
  • • Created complex ETL Strategies to populate the Data-mart facts and Dimensions and RDBMS multidimensional and slowly changing data mart tables.
  • • Completed end to end load automation.
  • • Debugged the existing functionality to correct and improve performance.
  • • Changed the structure of the tables in the database model to improve the performance for retrieving the data.
  • • Analyzing the existing informational sources and methods, understanding the customer expectations, and identifying the problem areas.
  • • Identified business needs, evaluated business and technical alternatives, recommended solutions and participated in their implementation.
  • • Worked on converting high volume legacy see commerce system for Mopar suppliers to BI using DataStage ETL. New relational DB2 tables, automated DataStage jobs to load these tables were developed as a part of this conversion project.
  • • Analysis of the Business Rules and Mappings. Analyzed the SLD and implemented the same in the jobs.
  • • Worked on analyzing data from different ODS systems.
  • • Participated in the design and development of data mart for the new system and mappings between sources and operational staging targets by identifying suitable Fact and Dimensional tables for the schema.
  • • Defined Environment variables for the Projects by using Administrator.
  • • Developed DataStage server/Parallel jobs to extract, transform and load data into data Warehouse from various sources like relational databases (DB2)
  • • Used the DataStage Designer to develop processes for extracting, cleansing, transforming, integrating, and loading data into data warehouse database.
  • • Generations of Surrogate IDs for the dimensions in the fact table for indexed, faster data access.
  • • Created the test cases to identify bugs.
  • • Involved in Integration, testing and functional testing.
  • • Tuned performance to cut down the DataStage process time.
  • • Developed BO reporting universes and BI reports.
  • • Conducted User Acceptance Testing (UAT) to support the UAT process and answer/clarify any questions raised by business users, testing team and functional architects.
  • • Re designed and enhanced data model to cut down the processing time of business-critical Cognos reporting applications.

Education

BACHELOR OF SCIENCE - INFORMATION TECHNOLOGY

Biju Patnaik University of Technology (BPUT)
05.2010

Skills

  • Data Warehouse Architecture
  • Database Design
  • Business Intelligence
  • Data Modeling
  • Data Analytics
  • Data Integration
  • AI/Machine Learning
  • Snowflake
  • Azure
  • Snowpipe
  • Kafka
  • SnowSpark
  • PySpark
  • Power Designer
  • Erwin
  • Power BI
  • Qlikview / Qlik Sense
  • Business Objects
  • Cognos
  • MicroStrategy
  • BigData
  • Spark
  • AtScale
  • ADF
  • DataStage
  • Informatica
  • Boomi

Personal Information

Title: Senior Data Warehouse Architect

Certification

  • Certified [Job Title], [Company Name] - [Timeframe]
  • [Area of certification], [Company Name] - [Timeframe]
  • Licensed [Job Title] - [Timeframe]
  • [Area of certification] Training - [Timeframe]
  • [Area of expertise] License - [Timeframe]

Languages

English
Full Professional

Timeline

SNOWFLAKE DATA WAREHOUSE ARCHITECT

Big Lots Inc
11.2022 - Current

DATA WAREHOUSE ARCHITECT

Big Lots Inc
03.2022 - 10.2022

DATA WAREHOUSE ARCHITECT

Big Lots Inc
09.2020 - 02.2022

ETL CONSULTANT/ARCHITECT

g2o LLC
11.2019 - 08.2020

SENIOR DATAWAREHOUSE/ETL CONSULTANT

Ford Credit
09.2019 - 11.2019

ETL CONSULTANT/ARCHITECT

BigLots
07.2019 - 09.2019

BUSINESS INTELLIGENCE LEAD

FIAT Chrysler LLC
06.2019 - 07.2019

DATAWAREHOUSE LEAD

FIAT Chrysler LLC
01.2019 - 05.2019

DATAWAREHOUSE/BUSINESS INTELLIGENCE LEAD

Delphi Automotive Systems
03.2018 - 12.2018

DATAWAREHOUSE LEAD

FIAT Chrysler LLC
12.2010 - 02.2018

BACHELOR OF SCIENCE - INFORMATION TECHNOLOGY

Biju Patnaik University of Technology (BPUT)

Similar Profiles

CREATE PROFILE
JAYRAJ GUPTA