MSSQL to Snowflake Integration for a Retail Giants: Accelerating Real-Time Analytics and Driving Growth
Client Overview: A Retail Giant
Client Requirement to Integrate Snowflake in Place of MSSQ
Data is transferred from the MSSQL Server to an Amazon S3 bucket in the form of CSV files.
An ETL pipeline, utilizing a Python script, is to be established to replace the current SSIS process and move data from the MSSQL Server to the Amazon S3 bucket.
The data from the S3 bucket needs to be loaded into Snowflake.
How GetOnData Helped Implement Snowflake?
Created reconfiguration tables containing select queries for all necessary fields, source table names, target table names, header names, process names, an S3 key, and a destination folder in AWS.
Developed a Python script that runs daily at a specific time using a cron job on an EC2 instance.
The script retrieves metadata from the configuration tables and connects to the source MSSQL Server to fetch all records.
Delimited data is then written to CSV files.
The CSV files are then transferred to the specified destination folder in the S3 bucket.
Processes are logged in Snowflake tables.
Then the data is moved from the AWS S3 bucket to the Snowflake Data Warehouse using the lambda function.
GitLab is being used as a version control tool for managing and collaborating on code development.
How the Retail Giant Benefited by Integrating Snowflake?
Reusable Python Script
The new design allows the reuse of the generic Python script for future data imports.
Eliminates SSIS Package Creation
The need to create a new SSIS package or SQL Server Agent job for each table import is eliminated.
Reduces SSIS Developer Team
This new design architecture allows customer to reduce the SSIS developer team.
Cost Saving
The result in cost savings on investing in SSIS licenses.
