BigQuery Cost Optimization: How to Build a FinOps Query Router for On-Demand and Capacity Pricing

Part 1: The Anatomy of a Cloud Extortion and “Boxed” Illusions

So, your enterprise finally decided to become “data-driven.” Congratulations. The executives read a few Forbes articles, hired a dozen data engineers, and migrated everything to Google Cloud. Fast forward six months, and instead of making brilliant strategic decisions, your CEO is sweating over a $150,000 monthly BigQuery invoice, wondering if it would have been cheaper to just ask a fortune teller.

Welcome to the reality of modern cloud computing, where you are billed not for the answers you get, but for the sheer volume of data you accidentally scan while looking for them.

To understand the beauty of the solution we are about to build, you first need to understand the trap. BigQuery offers two ways to ruin your budget:

1. On-Demand Pricing: You pay roughly $6.25 for every terabyte scanned. Google gives you up to 2,000 slots (compute power units) for free. It is lightning-fast. But if an intern writes a SELECT * query on a 10-terabyte table just to check a timestamp, you instantly lose $62.50. It takes 10 seconds, but it hurts.
2. Capacity Pricing (Editions): You buy slots and pay for the time you use them. If a heavy, unoptimized ETL process with terrible regular expressions scans only 10 gigabytes of data but runs for three hours chewing through 500 slots, it will burn a massive hole in your pocket. Under On-Demand, this same query would cost exactly $0.06.

This creates a beautiful paradox: fast, massive data scans are cheap on Capacity but incredibly expensive on On-Demand. Slow, small data transformations are cheap on On-Demand but financially catastrophic on Capacity.

You would think someone has already solved this, right? Let us look at the “out-of-the-box” solutions the market generously offers.

The “Out-of-the-Box” Illusions

1. Google’s Native Autoscaler (BigQuery Editions) Google will happily tell you to just use their native autoscaler. It sounds great: you set a baseline of 0 slots and a maximum of 1,000, and Google gives you power when you need it. The catch? It only works within the Capacity model. You cannot natively ask Google to dynamically switch a specific query to On-Demand just because it is cheaper. Google is a tech giant, not a charity; they have absolutely zero motivation to optimize your billing at their own expense.
2. Enterprise FinOps SaaS (e.g., DoiT BigQuery Lens) These are massive B2B platforms. Yes, they do exactly what we want: intelligent routing and cost control. But they come with enterprise-level price tags, aggressive sales teams, and mandatory long-term contracts. You are essentially paying a massive tax to a third-party vendor just to stop Google from taxing you too much. It is the corporate equivalent of paying the mafia for protection.
3. Open-Source Proxies (Envoy, Presto, Trino) If you hate SaaS, you can deploy an open-source SQL router. Suddenly, your infrastructure team is maintaining a giant Kubernetes cluster, dealing with Java memory leaks, and managing complex network topologies. You wanted to save $10,000 on BigQuery, but now you are paying a DevOps engineer $12,000 a month just to keep the proxy server alive. The math does not add up.

We do not need a bloated SaaS or a Java monolith. We need a surgical strike. We need a lightweight, lightning-fast proxy that intercepts every query, predicts its cost in milliseconds, and routes it to the cheapest billing model without the user ever noticing. And we are going to build it using strict, compiled code that runs on pennies.

Part 2: The Hub & Spoke Architecture and the F# FinOps Proxy

So, how do we fix this financial bleeding without turning our infrastructure into a bloated, overpriced Java monolith? We use physics, pure logic, and a language designed for data processing: F#.

We are going to build a BigQuery FinOps Router. The core architectural pattern behind this solution is called Hub & Spoke, and it exploits a fundamental secret of Google Cloud: BigQuery strictly separates Storage (where your data lives) from Compute (the power that runs your queries).

Here is how we set up the battlefield.

The Infrastructure Setup (No Magic, Just GCP Rules)

The client creates three distinct projects in Google Cloud:

1. project-data-lake: This is the central storage hub. All raw tables, logs, and historical datasets live here. Crucially, no one executes heavy queries here, and billing is configured strictly for storage costs.
2. project-compute-ondemand: An empty project with zero tables. Its billing is set to On-Demand ($6.25 per terabyte).
3. project-compute-capacity: Another empty project where the company purchased a fixed amount of slots (Capacity / Editions) for heavy, predictable ETL tasks.

Now, instead of letting your analysts and BI tools (like Looker Studio or Tableau) connect directly to the data lake and cause financial chaos, we place a custom-built proxy between them.

The Engine: Why F# and Cloud Run?

For the proxy backend, we reject heavy frameworks and write a clean, high-performance service in F#, compiled using Native AOT (Ahead-of-Time) and deployed on Google Cloud Run.

Why F#? Because when you are handling thousands of incoming SQL requests concurrently, you need immutability, pattern matching for query parsing, and lightning-fast execution without garbage collection hiccups. Cloud Run ensures that our proxy scales from zero to thousands of instances instantly, costing practically nothing when idle.

The Mechanism of Action: Step-by-Step

When an analyst or an automated script sends a SQL query, our F# FinOps Router intercepts it before it ever reaches BigQuery. Here is the exact lifecycle of a request:

1. Interception: The BI tool hits our Cloud Run proxy endpoint instead of the standard BigQuery API. The F# app authenticates the request and extracts the raw SQL text.
2. The Cost Oracle (dryRun = true): Before spending a single cent on heavy calculations, the proxy fires a lightweight, free API request to BigQuery with dryRun = true. BigQuery instantly evaluates the query plan and returns exact metrics: how many bytes it will scan and whether it uses complex regex or heavy joins.
3. The Routing Logic: Our custom mathematical model inside the F# application evaluates the metrics in milliseconds:
   • Scenario A: The query scans 500 megabytes and will execute in 2 seconds. Running this on Capacity wastes slots. The proxy rewrites the execution target to project-compute-ondemand.
   • Scenario B: The query scans 45 terabytes with a messy REGEXP and will take 2 hours. Running this on On-Demand would cost $280 instantly. The proxy redirects it to project-compute-capacity.
4. The Execution: The proxy sends the query to the chosen compute project, passing the data lake reference via cross-project IAM permissions (BigQuery Data Viewer on the storage, BigQuery Job User on the compute projects).
5. The Return: The query finishes, and the result is safely returned to the user. The data never moved; only the bill was intelligently rerouted.

Part 3: Implementation, Costs, Timeline, and the Bitter Truth of Pros & Cons

So, you are actually considering building this instead of just complaining about your cloud bill over Friday beers. Excellent. Let us talk about what it takes to bring this F# FinOps Router to life, how much it will cost, and whether your company is even a candidate for it.

Timeline & Implementation Steps (From Zero to Saved Money)

You do not need a six-month migration project or an army of consultants. Because we are using serverless components and modern tooling, a senior engineer can build, test, and deploy this in roughly two weeks:

1. Days 1–3: The Terraform Foundation
   • Provision the three GCP projects (data-lake, compute-ondemand, compute-capacity).
   • Lock down IAM policies using the principle of least privilege. Grant your service account BigQuery Data Viewer on the storage hub and BigQuery Job User on the compute projects.
2. Days 4–8: The F# Proxy Core
   • Write the lightweight web service using Giraffe or ASP.NET Core minimal APIs in F#.
   • Implement the HTTP handler that intercepts incoming SQL, triggers the dryRun = true API call, and applies the cost-routing mathematical matrix.
3. Days 9–14: Deployment & Cutover
   • Compile the F# app using Native AOT for lightning-fast cold starts, wrap it in a minimal Docker container, and deploy it to Cloud Run.
   • Update the connection strings in your BI tools (Looker Studio, Tableau) to point to your new proxy URL instead of standard BigQuery.

Costs: Pennies vs. Thousands

Let us talk about the infrastructure cost of running the actual solution:

• Google Cloud Run: If your proxy processes roughly 1 million incoming queries a month, running a 1 vCPU / 512 MB RAM container will easily fit inside the free tier or cost around $2 to $5 per month.
• Internal Network Egress: Zero. Requests within the same GCP region are completely free.
• The ROI: If this router saves your company from just two accidental 10-terabyte SELECT * queries per month on the On-Demand tier, it pays for itself for the next ten years.

Who Actually Needs This? (Target Audience)

Let us be completely honest: if your monthly BigQuery bill is under $3,000, stop reading this and go back to writing features. This solution is overkill.

This architecture is built exclusively for mid-to-large enterprises (fintech, e-commerce, ad-tech) where:

• The monthly BigQuery bill ranges from $25,000 to $150,000+.
• You have a dozen data analysts and heavy BI dashboards running unoptimized queries 24/7.
• Management is actively threatening to cut the data team’s budget because “the cloud is becoming more expensive than the office.”

The Good, the Bad, and the Ugly: Pros & Cons

The Pros:

• Surgical Precision: You control the exact routing logic based on your business rules, not a black-box vendor algorithm.
• Zero Data Movement: Data never leaves its storage bucket; only the billing target changes.
• Full Ownership: No mandatory vendor lock-in, no aggressive sales calls, and no per-user licensing fees.

The Cons:

• It Is Custom Code: Someone in your team has to maintain the F# repository (even though F# code is notoriously stable and rarely breaks).
• Behavioral Shift: Analysts must point their tools to your proxy endpoint, which requires a brief cultural adjustment (“Stop querying BigQuery directly, use the gateway!”).

The Final Value Block: Core Recommendations for Success

Before you open your IDE, take these four golden rules to heart:

1. Audit Before You Route: Never build routing logic blind. Run an initial query against BigQuery’s INFORMATION_SCHEMA to find out who is wasting your money and what kind of queries are killing your budget. Target the worst offenders first.
2. Start with a Passive Shadow Mode: Do not turn the router into an active blocker on day one. Deploy the F# app in “Shadow Mode”—let it calculate where it would route the query and log the potential savings without actually redirecting traffic. Validate the math for a week.
3. Keep the Logic Stupidly Simple: Do not try to machine-learn every single query on day one. Start with a binary threshold: if bytes scanned > 50 GB and expected runtime > 60 seconds, push to Capacity. If it is a fast dashboard ping, push to On-Demand.
4. Use It as a Sales Argument: If you run an agency or a technical consultancy (like Tech Macro), this architecture is your ultimate foot-in-the-door. Show a potential enterprise client their own wasted cloud spend, show them this blueprint, and watch how fast they sign a high-ticket consulting contract for your time.

Part 4: The Elephant in the Room — BI Tool Integration and Authorization Nightmares

So, you built a pristine F# proxy, deployed it on Cloud Run, and you are ready to watch your cloud bills plummet. Then, your lead data analyst walks up to your desk looking like they’ve seen a ghost, because half your dashboards just threw connection errors. Welcome to the hardest wall this architecture hits: BI tool integration and authorization.

If you thought writing the routing logic was hard, try explaining to modern cloud business intelligence tools that they are no longer allowed to talk directly to Google. Here is why this step breaks weaker architectures:

1. The Hardcoded API Wall: Many native cloud BI tools (looking right at you, Looker Studio) have bigquery.googleapis.com hardcoded deep into their native drivers. They do not feature a convenient “Base URL” input field where you can just paste your Cloud Run proxy address. They expect a direct, unmediated line to Google, period.
2. The OAuth & Security Minefield: BigQuery relies heavily on user-level authentication and Row-Level Security (RLS) so that a junior marketing intern cannot accidentally read the executive payroll table. If your proxy sits in the middle, it must handle OAuth token relay seamlessly. Screw up the token passthrough, and either nobody can see any data, or everyone inherits admin privileges. Neither option pleases the Chief Information Security Officer.

How to Bypass the BI Nightmare

Do not try to force a real-time proxy into cloud-native BI tools that completely reject custom hosts. Instead, apply a pragmatic engineering split:

• For Heavy Enterprise BI (Tableau, Power BI, Superset via JDBC/ODBC): Custom proxies do work here because their database drivers allow you to explicitly configure custom host addresses and routing parameters.
• For Cloud Dashboards: Shift the optimization upstream. Move your cost routing and data optimization logic directly into your dbt or Dataform pipelines. Let heavy transformations and cost-sensitive queries run during off-peak hours using structured scheduling, leaving the BI tools to read clean, pre-aggregated, lightweight tables where slot contention is already eliminated at the source.

Uncontrolled BigQuery queries, over-provisioned infrastructure, and hidden cloud waste often build up silently as data platforms scale. Rather than cutting resources blindly or imposing rigid limits that stall engineering velocity, effective cost control requires a precise, architectural review of your workload. My FinOps on GCP service is designed to identify query inefficiencies, optimize data partitioning, and align your cloud expenses directly with technical and business value. We focus on finding the root causes of runaway bills—from unoptimized transformations to redundant storage—without compromising system performance. If you are looking for a calm, data-driven approach to make your Google Cloud environment predictable and cost-efficient, I invite you to explore the details.