Bridging the Gap: Unifying Legacy SQL and Modern NoSQL Data

The Challenge

This created a major reporting and engineering bottleneck. To analyze long-term trends, teams had to manually combine legacy SQL records with modern NoSQL data. Reporting became fragmented, time-consuming, and difficult to scale.

Beyond reporting, we faced a significant architectural hurdle: passing this data to downstream systems was nearly impossible. Because the legacy and modern sources used completely different structures, downstream models like our fraud detection system couldn't consume the data easily.

To solve this, we built a unified data platform that made the underlying source system irrelevant to the end user. Whether the data came from MySQL or MongoDB, it was engineered to land in a single Snowflake warehouse.

Our Approach

We combined a one-time high-integrity load of legacy SQL data and an automated pipeline for MongoDB data, creating a single unified data model across historical and current data.

To make this reliable and scalable, we implemented a manifest-driven orchestration framework using Snowflake, Python, AWS S3, and Snowpipe.

We chose to use Snowflake’s native capabilities as much as possible, allowing us to build the pipeline without depending on external ETL tools and helping to keep overall platform costs lower.

At a high level:

• Extraction: Our Python engine pulls data from MongoDB and generates a Metadata Manifest file containing record counts and file paths, pushing it to AWS S3.
• Event-Driven Ingestion: The S3 upload triggers Snowpipe, which immediately loads the manifest into a SOURCE_EXTRACT_INFO table.
• Change Tracking: A Snowflake Stream on the SOURCE_EXTRACT_INFO table detects the new entry or change, acting as a reactive gatekeeper.
• Orchestration: The Stream trigger activates a Snowflake Task, which executes a Master Stored Procedure. This procedure validates the manifest and only then initiates the COPY command to bring the actual data files from AWS S3 bucket to Snowflake.

How the Pipeline Works

The framework runs in four stages:

1. Intelligent Configuration Activation

We don't run compute resources blindly. The Master Stored Procedure parses the incoming manifest file. It checks the record count; if the count is greater than zero, it updates the ETL_ingest_config table by enabling a flag active for the tables that the data is present. This ensures Snowflake compute resources are only engaged when there is actual data to process, significantly optimizing warehouse costs.

This ensures that Snowflake compute resources are only engaged when there is fresh data to process, significantly reducing warehouse costs.

2. Ingestion

Using a Master Stored Procedure, the ingestion layer dynamically loads data from S3 into landing tables using the COPY command. However, the system only targets tables specifically enabled with a 'Y' flag from the initial configuration activation step.

During this phase, the system performs a critical validation check by comparing the actual records loaded against the Metadata Manifest. All outcomes successes or discrepancies are logged in audit tables

3. Curated transformations

The core of this system is a single, intelligent Master Stored Procedure. Rather than using hard coded scripts for each table, the procedure reads from a Configuration Table to determine the exact processing logic required for  every  object.

• Automated Logic Selection: The procedure checks the metadata to see if a table is flagged as SCD Type 1 (current state), SCD Type 2 (historical tracking), or an Incremental Fact Table.
• Dynamic Execution: Based on those flags, it dynamically generates and executes the corresponding  merge or insert logic.
• Referential Integrity Sequencing: The metadata also governs the processing order, ensuring Dimensions are fully updated before the Fact tables are processed.

4. Cleanup and reset

After processing, landing tables are truncated, and pipeline flags are reset, preparing the system for the next run.

The Outcome

This solution didn't just move data; it transformed how the client understands their business. By centralizing the entire history of Legacy MySQL and the real-time flow of Modern MongoDB into Snowflake, we’ve eliminated the manual switches between multiple systems analysis teams used to perform.

• Clients can access a single reporting view across legacy and modern data
• We have integrated a machine learning-based Fraud Detection Model that consumes data directly from the unified Curated Layer.This model specifically leverages the unique advantage of having unified data. By combining historical, structured data from MySQL with current, complex data from MongoDB, the model identifies fraud patterns that were invisible when the systems were disconnected.
• Reports refresh automatically every morning
• Every load is validated and auditable

Tech Stack