ML Pipeline Architecture in Google Cloud: Data Preparation in BigQuery, Dataform vs dbt, and FinOps

Executive Summary

The accuracy of a machine learning model depends entirely on the quality of its training data. In enterprise environments, preparing this data requires complex, repeatable pipelines. When data engineering is separated from ML engineering, companies face slow deployment cycles, high cloud computing costs, and quiet data failures. This article explores how to design reliable structured data pipelines in Google Cloud Platform (GCP) using BigQuery and Dataform. We will analyze the core business problems, compare Dataform with dbt, review architectural patterns, and provide practical code examples. The goal is to build an infrastructure that scales efficiently from a simple data transformation to an enterprise-grade ML data platform.

The Business Problem: Why ML Pipelines Fail Early

Before an algorithm can predict an outcome, the data must be extracted, cleaned, transformed, and validated. In practice, many organizations struggle at this foundational stage. Based on standard industry challenges and cloud consumption patterns, companies usually encounter the following problems:

  • Quiet Data Corruption: NULL values, duplicates, and changed data types in source tables do not stop a standard SQL query. However, they silently destroy the predictive power of an ML model.
  • Uncontrolled Cloud Costs: Running heavy SELECT * queries or performing full table overwrites every day causes massive BigQuery usage. This leads to sudden budget spikes and quota exceeded errors, severely impacting FinOps targets.
  • Lack of Version Control: When data transformations are stored as scheduled queries without a Git repository, it is impossible to track changes, review code, or roll back to a previous state.
  • Workflow Fragmentation: Analytical teams and ML engineers often use the same tables without environment isolation (Dev, Staging, Prod). This creates conflicts and broken dependencies during feature engineering.

To solve these issues, data teams need an ELT (Extract, Load, Transform) orchestration tool integrated directly with the data warehouse.

Transforming Data in BigQuery: Dataform vs dbt

When building data transformation pipelines in BigQuery, the two most common systems are Dataform and dbt (data build tool). Both tools operate on the ELT principle: data is first loaded into BigQuery, and then the tool sends SQL commands to transform the data directly inside the warehouse.

However, their internal mechanisms, scaling models, and pricing differ significantly.

Internal Mechanisms and Architecture

Both systems use a declarative approach and build a Directed Acyclic Graph (DAG) to manage dependencies. Neither tool moves the data; they act as a control plane pushing queries down to the compute layer.

  • Dataform: Developed specifically for BigQuery and deeply integrated into the GCP ecosystem as a fully managed serverless service. It uses SQLX, an extension of SQL, to define tables, dependencies, and data quality tests in a single file.
  • dbt: An open-source tool (with a commercial cloud version) that supports multiple data warehouses. It uses standard SQL combined with Jinja templating to handle logic and dependencies.

Detailed System Comparison

Feature / ComponentGoogle Cloud Dataformdbt (Core / Cloud)
Execution EngineServerless, fully managed inside GCP. No infrastructure to maintain.Requires external hosting (dbt Core) or a dbt Cloud subscription.
Configuration LanguageSQLX (SQL + JavaScript). Highly concise and localized.SQL + Jinja. Very flexible but syntax can become complex.
Integration with GCPNative. Direct connection to Cloud Logging, IAM, and Eventarc.Standard API integration via service accounts.
ScalingOrchestration scales automatically. Compute scales with BigQuery slots.Orchestration scaling depends on hosting (K8s/Cloud). Compute scales with BigQuery.
Pricing ModelFree. Users only pay for the BigQuery compute used during the queries.dbt Core is free. dbt Cloud charges per developer seat.

Strengths and Weaknesses

Dataform

  • Strengths: Zero orchestration cost, native GCP security integration (IAM), seamless GitHub/GitLab integration, and an incredibly low barrier to entry for teams already using BigQuery. The web UI in Google Cloud Console is built-in.
  • Weaknesses: Strictly locked into the Google Cloud ecosystem. It does not support Snowflake or AWS Redshift. The open-source CLI version is not the primary focus of development.

dbt

  • Strengths: True multi-cloud support. A massive community and hundreds of pre-built packages for standard integrations. Excellent documentation and wide market adoption.
  • Weaknesses: Jinja can be difficult to read and debug for standard SQL developers. Managing dbt Core requires maintaining your own orchestration infrastructure (e.g., Cloud Run or Airflow), which increases DevOps overhead.

Strategic Choice for ML on GCP

If the infrastructure is exclusively built on Google Cloud and the primary data warehouse is BigQuery, Dataform is the most efficient choice. It provides enterprise-grade data transformation, Git integration, CI/CD capabilities, and environment isolation at zero additional software cost. By reducing DevOps overhead, data engineers can focus entirely on preparing clean, reliable features for machine learning models.

Practical Use Cases: From Simple Aggregations to ML Feature Engineering

To understand how Dataform builds a reliable data foundation, we will follow a logical progression. We start with basic data preparation and advance toward complex pipelines designed specifically for machine learning models.

Case 1: The Basics — Building a Fact Table

The simplest requirement before ML modeling is joining raw data into a clean, unified view. In a standard setup without Dataform, you might schedule a query that drops and recreates a table. This approach causes downtime and lacks testing.

In Dataform, we define a table and its dependencies using SQLX. Dataform automatically builds the execution order. If the source table fails to update, this node will not run, preventing downstream errors.

// definitions/core/user_daily_activity.sqlx
config {
  type: "table",
  schema: "data_prep",
  name: "user_daily_activity",
  description: "Aggregated daily activity per user for modeling."
}

SELECT 
  user_id,
  DATE(timestamp) as activity_date,
  COUNT(event_id) as total_events,
  SUM(session_duration) as total_duration
FROM 
  ${ref("raw_events")} // Dependency injection
GROUP BY 
  1, 2

Case 2: FinOps Focus — Incremental Updates

Machine learning models often require years of historical data. Overwriting a terabyte-scale table daily is a massive waste of cloud resources and severely impacts the BigQuery bill. The solution is incremental processing.

Dataform allows you to append only new records. The critical rule for efficiency is to combine incremental updates with BigQuery table partitioning and clustering to minimize data scanning.

// definitions/features/incremental_transactions.sqlx
config {
  type: "incremental",
  schema: "ml_features",
  uniqueKey: ["transaction_id"], // Prevents duplicates on rerun
  bigquery: {
    partitionBy: "DATE(transaction_timestamp)",
    clusterBy: ["user_id"]
  }
}

SELECT 
  transaction_id,
  user_id,
  amount,
  transaction_timestamp
FROM ${ref("raw_transactions")}
// The following block only executes during incremental runs
${when(incremental(), `WHERE transaction_timestamp > (SELECT MAX(transaction_timestamp) FROM ${self()})`)}

Architecture Note: When incremental() evaluates to true, Dataform only processes the delta. Because the target table is partitioned by date, BigQuery only scans the relevant partitions, drastically reducing compute costs.

Case 3: Data Quality Gates (Assertions)

The most common cause of ML model degradation is silent data failure in production. Dataform uses Assertions to create quality gates. An assertion is a query that returns zero rows if the data is correct. If it returns rows, the assertion fails, and the pipeline halts.

Instead of writing complex SQL tests manually, Dataform provides built-in configuration blocks for standard checks.

// definitions/features/user_profiles.sqlx
config {
  type: "table",
  schema: "ml_features",
  assertions: {
    uniqueKey: ["user_id"], 
    nonNull: ["age", "account_status"],
    rowConditions: [
      "age >= 18", 
      "account_status IN ('active', 'suspended', 'closed')" 
    ]
  }
}

SELECT user_id, age, account_status FROM ${ref("raw_users")}

If a bug in the source system introduces a negative age or a NULL status, the assertion fails before the data reaches the machine learning training pipeline.

Case 4: Infrastructure Automation — Triggering Dataform with F# and Terraform

In an enterprise environment, data pipelines should not run on static schedules if data arrival is unpredictable. You need event-driven architecture.

We can deploy a Cloud Function that triggers the Dataform execution API whenever new data lands in Cloud Storage or when an upstream orchestration tool (like Dagster or Airflow) completes a task.

First, we provision the Dataform repository using Terraform:

# infrastructure/dataform.tf
resource "google_dataform_repository" "ml_data_repo" {
  provider = google-beta
  name     = "ml-feature-repo"
  region   = "europe-west1"
  
  git_remote_settings {
    url = "https://github.com/company/ml-dataform-repo.git"
    default_branch = "main"
    authentication_token_secret_version = google_secret_manager_secret_version.github_token.name
  }
}

Next, to demonstrate cross-platform capability, here is an example of an event-driven trigger written in F# (.NET), deployed to Google Cloud Run. This function calls the GCP API to compile and execute the Dataform workspace.

// DataformTrigger.fs
open System
open System.Net.Http
open System.Net.Http.Headers
open System.Text.Json
open System.Threading.Tasks
open Microsoft.AspNetCore.Http

let triggerDataformExecution (req: HttpRequest) = 
    task {
        // In a real scenario, use Google.Apis.Dataform.v1beta1 client library
        // and GoogleCredential for proper authentication.
        let projectId = Environment.GetEnvironmentVariable("PROJECT_ID")
        let region = "europe-west1"
        let repo = "ml-feature-repo"
        let workspace = "production-workspace"
        
        let url = sprintf "https://dataform.googleapis.com/v1beta1/projects/%s/locations/%s/repositories/%s/workspaces/%s:compilationResults" projectId region repo workspace
        
        use client = new HttpClient()
        client.DefaultRequestHeaders.Authorization <- AuthenticationHeaderValue("Bearer", "oauth2-token")
        
        let payload = {| gitCommitish = "main" |}
        let content = new StringContent(JsonSerializer.Serialize(payload), Text.Encoding.UTF8, "application/json")
        
        let! response = client.PostAsync(url, content)
        
        if response.IsSuccessStatusCode then
            return "Dataform compilation triggered successfully."
        else
            return "Failed to trigger Dataform."
    }

This architecture ensures that machine learning features are recalculated immediately when new raw data is available, reducing latency in the predictive models.

7 Standard Data Pipeline Problems and How to Solve Them

Even with robust tools like Dataform, building ML pipelines in BigQuery introduces specific engineering challenges. Based on real-world enterprise deployments, here are the seven most common problems and their architectural solutions, ordered from simplest to most complex.

1. Hardcoded Environment Variables

Problem: Code contains hardcoded dataset names (e.g., prod_dataset.table1). When engineers try to test changes, they accidentally overwrite production data. Solution: Use Dataform’s environment variables and Workspace isolation. Each developer connects to their own Git branch and works in a dedicated BigQuery schema (e.g., dev_dmitry). Instead of hardcoding, use Dataform configuration blocks. The target schema changes dynamically depending on the active workspace or release configuration, ensuring complete isolation between Dev, Staging, and Prod.

2. The “SELECT *” Anti-Pattern and Cloud Waste

Problem: Queries use SELECT * on wide tables just to extract three columns. In BigQuery’s columnar storage, you are billed for all data scanned. If the table is 500GB, you pay for scanning 500GB even if you only need 10MB of data. Solution: Enforce strict column selection in Code Review. In Dataform, utilize standard SQL logic but combine it with BigQuery dry-runs during CI/CD. Furthermore, implement partitioning and clustering on all large tables. A query like SELECT user_id FROM ${ref("events")} WHERE event_date = CURRENT_DATE() will only scan a fraction of the table if partitioned by event_date.

3. State Race Conditions in Incremental Tables

Problem: An incremental table fails midway through a run, or data arrives late. When the pipeline runs again, data is duplicated, or a day is missed completely. Solution: Always define a uniqueKey in the Dataform configuration for incremental tables. Behind the scenes, Dataform generates a MERGE statement instead of a simple INSERT. This ensures idempotency: if the pipeline runs twice for the same data, the resulting state remains exactly the same, preventing duplicates.

4. CI/CD Pipeline Bottlenecks

Problem: Code is merged directly to the main branch without execution testing. Syntactically correct SQL might still fail due to schema mismatches or quota limits. Solution: Integrate Dataform CLI into Google Cloud Build or GitHub Actions. Before any Merge Request is approved, a CI/CD job should run:

  1. dataform compile to check for syntax and dependency errors.
  2. dataform test to run isolated queries.
  3. Execute the pipeline against an ephemeral BigQuery dataset (e.g., ci_test_schema). If the run succeeds, the dataset is dropped, and the code is approved for production.

5. Managing Complex Data Quality Thresholds

Problem: Standard Assertions (like nonNull) are binary. In machine learning, data is rarely perfect. A table might normally have 2% NULL values, which is acceptable, but a sudden spike to 20% indicates a failure. Solution: Write custom SQL Assertions in Dataform that calculate thresholds.

JavaScript

config { type: "assertion" }
SELECT
  total_rows,
  null_count
FROM (
  SELECT 
    COUNT(*) as total_rows, 
    COUNTIF(critical_feature IS NULL) as null_count
  FROM ${ref("ml_features")}
)
WHERE null_count / total_rows > 0.05 // Fails if NULLs exceed 5%

6. Siloed ML Features and Inference Discrepancies

Problem: The data engineers calculate features for training in BigQuery. The backend engineers write separate code to calculate the same features in real-time for inference. The logic drifts, leading to model degradation in production (training-serving skew). Solution: Connect BigQuery and Dataform to Vertex AI Feature Store. Dataform handles the batch calculation of features. Once calculated, a lightweight Python job (orchestrated via Airflow/Composer) syncs the resulting BigQuery table into the Vertex AI Feature Store. The ML model reads from this store during both training (batch fetch) and real-time serving (low-latency fetch), guaranteeing that the exact same logic is used in both places.

7. Orchestration Fragmentation (The Big Picture)

Problem: Dataform is excellent for BigQuery, but it cannot trigger a Dataflow job, run a Vertex AI training script, or send a Slack alert if a third-party API fails. Solution: Use Cloud Composer (Apache Airflow) as the central orchestrator. Airflow is the “conductor,” while Dataform is the “specialist.” Airflow uses the DataformCreateCompilationResultOperator and DataformCreateWorkflowInvocationOperator to trigger the Dataform pipeline. Once Dataform finishes preparing the data in BigQuery, Airflow proceeds to the next step in the DAG—triggering the Vertex AI Custom Training Job. This keeps SQL logic inside Dataform where it belongs, while cross-service orchestration remains centralized in Airflow.

Conclusion: The Architecture of Reliable Machine Learning Data

Building a data pipeline for machine learning is fundamentally different from building a pipeline for standard business intelligence. BI dashboards can tolerate a slight delay or a minor data anomaly; a human reading the chart will often spot the error and ignore it. Machine learning models, however, are unforgiving. They will learn from corrupted data, encode those errors into their weights, and confidently make wrong predictions in production.

This article demonstrated how to bridge the gap between raw data and ML-ready features within the Google Cloud ecosystem. By moving away from disjointed scheduled queries and adopting Google Cloud Dataform, organizations gain version control, environment isolation, and built-in data quality testing without the overhead of managing external orchestration infrastructure like dbt Core.

We established that Dataform acts as the perfect localized engine for BigQuery transformations. However, as the complexity of the ecosystem grows, it must be integrated into a broader architecture. Terraform handles the infrastructure provisioning, Cloud Composer (Airflow) orchestrates cross-service dependencies, and Vertex AI Feature Store acts as the final destination for calculated features to prevent training-serving skew.

Ultimately, the goal of data engineering in an ML context is not just to move data from point A to point B. It is to guarantee that the data arriving at point B is mathematically sound, consistently formatted, and produced at the lowest possible cloud compute cost.

Practical Recommendations and Implementation Checklist

For teams planning to migrate their ML data preparation pipelines to BigQuery and Dataform, follow this architectural checklist to ensure a smooth transition and maintain strict FinOps control.

1. Repository and Workspace Strategy

  • Mandate Git Integration: Never develop directly in the production BigQuery console. Connect Dataform to your corporate GitHub or GitLab.
  • Isolate Environments: Ensure every data engineer has a dedicated Dataform workspace connected to a specific BigQuery dataset (e.g., dev_[username]).
  • Enforce Code Review: Use standard Git pull requests (Merge Requests). No code should reach the main branch without another engineer reviewing the SQLX logic.

2. FinOps and Query Optimization

  • Ban Full Refreshes on Large Tables: If a source table exceeds 10GB, the downstream feature table must be configured as type: "incremental" in Dataform.
  • Mandatory Partitioning: Every fact table and feature store table must be partitioned by date (e.g., partitionBy: "DATE(timestamp)").
  • Implement Clustering: Cluster tables by high-cardinality keys used in JOIN or WHERE clauses (e.g., clusterBy: ["user_id"]) to further reduce BigQuery scanning costs.

3. Data Quality and Security

  • Set Assertions on Every Target: Never build an ML feature table without attaching uniqueKey and nonNull assertions. A pipeline should fail loudly rather than fail silently.
  • Monitor Quotas: Implement Cloud Monitoring alerts for BigQuery slot utilization and query execution times to catch runaway Cartesian joins before they impact the monthly invoice.
  • Restrict Access: ML training pipelines (service accounts) should only have roles/bigquery.dataViewer access to the final curated feature datasets. They should never have access to raw PII (Personally Identifiable Information) data. Use Cloud DLP to mask sensitive fields during the Dataform transformation phase.

4. Advanced Orchestration Integration

  • Do Not Overload Dataform: Keep Dataform strictly for BigQuery SQL transformations. Do not attempt to use it to orchestrate external API calls or Python scripts.
  • Adopt Cloud Composer (Airflow) for the Macro-DAG: Use Airflow to manage the end-to-end lifecycle. The Airflow DAG should look like this: Wait for Raw Data -> Trigger Dataform -> Wait for Dataform Success -> Trigger Vertex AI Training -> Deploy Model.

By following these principles, organizations can eliminate cloud waste, accelerate their ML development cycles, and trust that their predictive models are built on a rock-solid data foundation.

Similar Posts