Building a Serverless, Multi-Source Data Ingestion Framework on GCP (Snowflake & Databricks to BigQuery)

Introduction

In the modern data era, organizations often find themselves managing data across multiple cloud platforms and data warehouses. A common challenge is consolidating this data into a single, centralized analytics hub like Google BigQuery.

Traditional ETL pipelines often require complex infrastructure management, leading to high operational overhead and spiraling costs. Managing persistent Spark clusters just to run periodic data copies is a prime example of resource waste.

To solve this, we built a Serverless, Multi-Source Data Ingestion Framework on Google Cloud Platform (GCP). This framework dynamically orchestrates parallel data copies from both Snowflake and Databricks (Unity Catalog) into BigQuery using GCP Workflows and Dataproc Serverless.

Here is how we designed it, the challenges we solved, and why this architecture is a game-changer for data engineering teams.

The Architecture: Orchestration Meets Serverless Compute

Our goal was simple: Zero cluster management, zero idle compute costs, and maximum parallelism.

To achieve this, we decoupled orchestration from compute:

1. Orchestrator (GCP Workflows): A serverless, lightweight orchestrator that parses a JSON configuration manifest, manages authentication secrets, and dynamically triggers parallel execution steps.
2. Compute (Dataproc Serverless): Instead of maintaining a running Spark cluster, we spin up ephemeral Spark jobs via Dataproc Serverless. We only pay for the exact seconds the data copy runs.

Here is the high-level architecture:

Why GCP Workflows?

Unlike Airflow (Cloud Composer), which requires a continuously running environment, GCP Workflows is completely serverless. It charges per step execution, making it incredibly cost-effective for orchestrating batch loads that run daily or hourly. It natively handles parallel loops, allowing us to ingest dozens of tables concurrently.

Key Engineering Challenges & Solutions

Building a generic framework that handles multiple sources is never just about “connecting A to B.” Here are the production-grade stabilization measures we built into the PySpark jobs:

1. Smart Incremental Loading (Deltas)

To minimize data transfer, the framework automatically detects if it should perform a full historical load or an incremental delta load.

• It queries the target BigQuery table to find the maximum timestamp in the partition field.
• If the table exists and contains data, it extracts only newer rows from the source (Snowflake/Databricks).
• If the table is empty or doesn’t exist, it defaults to a full load.

2. “Diversity Guards” Against Duplication

When dealing with incremental loads, source systems sometimes refresh a table completely (a snapshot) instead of appending. If you blindly run a delta load on a refreshed source, you risk duplicating rows.

• Our Solution: The framework evaluates distinct timestamps in the source dataset. If it detects a single unified timestamp across the new data, it identifies this as a full snapshot refresh and automatically overrides the write mode to overwrite, preventing duplication.

3. Handling Complex Data Types (Snowflake Variants)

Snowflake makes heavy use of the VARIANT type to store semi-structured JSON. When Spark reads this via JDBC, it often struggles to map it directly to Parquet, leading to writer class-cast exceptions.

• Our Solution: We implemented programmatic schema inspection in PySpark. The job identifies MapType, ArrayType, or known Variant columns and automatically serializes them into clean JSON strings before writing to the staging area, ensuring smooth BigQuery loading.

4. Nullability & JVM Protection

Spark and BigQuery sometimes disagree on schema nullability. A source schema might mark a field as “non-nullable,” but the incoming data contains unexpected nulls. In Spark, this can trigger JVM NullPointer exceptions during view transformations.

• Our Solution: We built a “Catalyst-Suppressed Nullability” utility that programmatically overrides nullability flags on strictly non-nullable source fields when preparing the Spark DataFrame schema, making the pipeline resilient to bad source data.

5. Column Sanitization

BigQuery has strict rules about column names (e.g., no spaces, special characters, or system prefixes like _PARTITION).

• Our Solution: The framework automatically sanitizes all column names, replacing invalid characters and renaming restricted prefixes to conform to BigQuery standards.

Declarative Configuration

Data engineers don’t need to write code to add new tables. They simply update a JSON manifest:

{
  "project": "my-gcp-project",
  "snowflake_tables": [
    {
      "source_schema": "SALES_DB",
      "source_table": "TRANSACTIONS",
      "target_bq": "my-gcp-project.sales.transactions",
      "bq_partition_field": "_extracted_at",
      "write_mode": "auto"
    }
  ]
}

By setting write_mode to auto, the framework automatically handles the logic of historical vs. incremental loads.

Business & Operational Benefits

• Cost Reductions: Moving from persistent Dataproc clusters to Dataproc Serverless reduced our compute costs by up to 60% for batch workloads.
• Operational Simplicity: No Kubernetes or Spark clusters to configure, patch, or scale. GCP handles the infrastructure.
• Speed to Production: Adding a new table to the ingestion pipeline is now a configuration change, not a coding task.

Conclusion

By combining the serverless orchestration of GCP Workflows with the elastic scale of Dataproc Serverless, we built a highly resilient, cost-effective ingestion framework. It abstracts away the complexities of cross-cloud data movement, allowing data teams to focus on delivering insights rather than managing infrastructure. Please refer full solution here.

What are your biggest pain points when migrating data from Snowflake or Databricks to BigQuery? Let’s discuss in the comments!