BigQuery Cost Optimisation for Financial Data Pipelines

In financial services - from London’s investment management firms to Edinburgh’s growing fintech cluster - BigQuery is becoming the standard for analytical data pipelines. Teams process transaction data, generate FCA and PRA regulatory reports, calculate risk metrics, and feed scoring models. All on data with natural temporal structure (transaction date, settlement day) and dimensional structure (client, product, currency) - which means BigQuery can be optimised aggressively if you know how.

How BigQuery Charges You

BigQuery offers two pricing models:

Key principle: In on-demand mode you pay for the amount of data a query SCANS - not the amount of results returned. A query SELECT * FROM transactions WHERE date = '2026-07-01' on an unpartitioned 500 GB table scans 500 GB, even if it returns only 1 GB.

This means every optimisation that reduces the amount of data scanned directly reduces cost.

Partitioning - The Fundamental Optimisation

What it is and why it is critical

Partitioning divides a table into physical segments by a chosen column (typically date). BigQuery scans only the partitions that satisfy the WHERE clause - the rest is skipped entirely.

For financial data, partitioning by transaction date is the absolute minimum. Without it, every daily query scans the entire historical table.

Example: transactions table

-- Table WITHOUT partitioning (bad pattern)
CREATE TABLE `project.dataset.transactions` (
  transaction_id STRING,
  account_id STRING,
  amount NUMERIC,
  currency STRING,
  transaction_date DATE,
  category STRING
);

-- Daily report query scans the ENTIRE table (e.g. 2 TB = $12.50)
SELECT account_id, SUM(amount) 
FROM `project.dataset.transactions`
WHERE transaction_date = '2026-07-01'
GROUP BY account_id;

-- Table WITH partitioning (correct pattern)
CREATE TABLE `project.dataset.transactions`
(
  transaction_id STRING,
  account_id STRING,
  amount NUMERIC,
  currency STRING,
  transaction_date DATE,
  category STRING
)
PARTITION BY transaction_date
CLUSTER BY account_id, currency;

-- Same query scans ONLY the 2026-07-01 partition (e.g. 5 GB = $0.03)
SELECT account_id, SUM(amount)
FROM `project.dataset.transactions`
WHERE transaction_date = '2026-07-01'
GROUP BY account_id;

Savings: from $12.50 to $0.03 - a 99.8% reduction (on a 2 TB table with daily partitions of ~5 GB each).

Partitioning strategies for financial data

Enforcing partition use (partition filter required)

For tables where someone might accidentally forget the date filter:

ALTER TABLE `project.dataset.transactions`
SET OPTIONS (require_partition_filter = true);

Now a query without WHERE transaction_date = ... fails with an error instead of scanning the entire table. This is a guardrail against accidental spend.

Clustering - The Second Layer of Optimisation

Clustering sorts data within partitions by chosen columns (max 4). BigQuery skips data blocks that do not match WHERE conditions on clustering columns.

When clustering delivers the biggest savings:

• Queries frequently filter by account_id, currency, or instrument_type
• Tables have millions of rows per partition
• The clustering column has reasonable cardinality (100-100,000 unique values)

When clustering will not help:

• Queries always scan the entire partition (e.g. daily aggregation without additional filters)
• Column has too-low cardinality (e.g. 3 values - better as PARTITION BY)
• Column has too-high cardinality (e.g. UUID - no meaningful grouping)

Materialized Views - Cache for Repeatable Queries

Financial pipelines have predictable patterns: the same aggregations run daily (daily positions, per-client totals, per-currency totals). Materialized Views store the aggregation result and refresh automatically.

CREATE MATERIALIZED VIEW `project.dataset.daily_account_summary`
AS
SELECT
  transaction_date,
  account_id,
  currency,
  SUM(amount) AS total_amount,
  COUNT(*) AS transaction_count
FROM `project.dataset.transactions`
GROUP BY transaction_date, account_id, currency;

How this saves money:

• Queries matching the materialized view pattern read from the view (smaller scan)
• BigQuery automatically routes queries to the view when it determines a match
• View refresh cost is incremental (only new data), not a full rescan

Typical savings: 50-80% on repeatable analytical queries.

BI Engine - In-Memory Cache for Dashboards

If your team uses Looker Studio (formerly Data Studio) or Tableau connected to BigQuery, BI Engine keeps data in RAM:

• Cost: from $2.50/GB/hour of reservation (minimum 1 GB)
• Effect: each dashboard query responds in milliseconds instead of seconds
• Savings: queries served from BI Engine consume neither slots nor on-demand bytes

For a team of 10-20 analysts refreshing dashboards dozens of times daily, BI Engine with a 5-10 GB reservation ($12-25/working hour) is cheaper than on-demand scanning of the same data hundreds of times.

Investment management teams in Leeds and Manchester - where daily NAV calculations and client reporting drive heavy dashboard usage - find BI Engine particularly cost-effective for their morning reporting cycles.

Editions Model (Slots) vs On-Demand - When to Switch

Calculating the break-even threshold

On-demand: $6.25/TB Standard Edition: $0.04/slot/hour (autoscaling, no commitment)

One slot processes data at a speed dependent on the query, but approximately:

• Simple aggregations: ~0.5-1 TB/slot/hour
• Complex JOINs: ~0.1-0.3 TB/slot/hour

Approximate thresholds:

• Processing <5 TB/month: on-demand is cheaper (~$31/month)
• Processing 5-20 TB/month: comparable - depends on pattern
• Processing >20 TB/month: slots are almost always cheaper

For financial pipelines (predictable queries, daily batch jobs): slots are typically more cost-effective from 10 TB/month upward because queries are regular and schedulable.

Autoscaling configuration

-- Reservation with autoscaling (Standard Edition)
-- Baseline: 100 slots, max: 400 slots
CREATE RESERVATION `project.region-europe-west2.prod_reservation`
OPTIONS (
  edition = 'STANDARD',
  slot_capacity = 100,
  autoscale_max_slots = 400
);

You pay for the baseline 24/7, and additional slots are charged per-second when the pipeline needs more capacity.

Cost Controls - Budgets and Alerts

Cloud Billing budgets

Set budgets at project level with alerts:

• 50% of budget: email notification (early warning)
• 80% of budget: Slack/PagerDuty alert (action required)
• 100% of budget: critical alert (escalation to manager)

Custom cost controls on BigQuery

-- Maximum query cost per-user (1 GB = ~$0.006)
-- Set to 10 GB so a single query cannot exceed $0.06
ALTER PROJECT `my-project`
SET OPTIONS (
  default_query_job_timeout_ms = 300000,  -- 5 minutes max
  maximum_bytes_billed = 10737418240      -- 10 GB max per query
);

Per-query override:

SELECT account_id, SUM(amount)
FROM `project.dataset.transactions`
WHERE transaction_date = '2026-07-01'
GROUP BY account_id
OPTIONS (maximum_bytes_billed = 1073741824);  -- 1 GB max

If the query needs to scan more - it fails with an error instead of generating unexpected cost.

Common Mistakes in Financial Pipelines

1. SELECT * in scheduled queries

A scheduled query that runs SELECT * on a historical table daily generates a constant, high cost. Solution: always select specific columns and add a partition filter.

2. JOINs on unpartitioned temporary tables

A pattern we see regularly:

-- BAD pattern: JOIN on full temp table
WITH temp AS (SELECT * FROM big_table)
SELECT * FROM temp JOIN other_table ON ...

BigQuery materialises temp as a temporary table WITHOUT partitioning. The JOIN scans it entirely. Solution: filter INSIDE the CTE.

3. No expire time on staging tables

ETL pipelines create temporary tables (_staging, _temp) and never delete them. They do not generate query costs but do generate storage costs ($0.02/GB/month for active storage).

-- Set automatic expiration on staging tables
ALTER TABLE `project.dataset.transactions_staging`
SET OPTIONS (expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY));

4. Views (VIEW) instead of materialized views

A standard VIEW does not cache results - every query executes the full base query. If a dashboard queries a VIEW 100 times daily, you pay 100x for the same scan. Materialized Views solve this.

5. Streaming inserts where batch load suffices

Streaming inserts cost $0.05/GB (in addition to storage). For a financial pipeline that loads data once daily (EOD batch), streaming is unnecessary - batch load is free.

Monitoring BigQuery Costs

INFORMATION_SCHEMA - usage analysis

-- Top 10 most expensive queries in the past week
SELECT
  user_email,
  query,
  total_bytes_processed / POW(1024, 4) AS tb_processed,
  total_bytes_processed / POW(1024, 4) * 6.25 AS estimated_cost_usd,
  creation_time
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_processed DESC
LIMIT 10;

-- Costs per-user in the past month
SELECT
  user_email,
  COUNT(*) AS query_count,
  SUM(total_bytes_processed) / POW(1024, 4) AS total_tb,
  SUM(total_bytes_processed) / POW(1024, 4) * 6.25 AS total_cost_usd
FROM `region-eu`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY total_cost_usd DESC;

These queries themselves cost very little (they scan metadata, not user data).

BigQuery Optimisation Checklist for Financial Services

Immediate (day 1)

• Enable require_partition_filter on all large tables
• Set maximum_bytes_billed to 10 GB as project default
• Review scheduled queries - remove SELECT * and add partition filters
• Configure budgets and alerts in Cloud Billing

Short-term (week 1-2)

• Add date partitioning to tables that lack it
• Add clustering on columns most frequently used in WHERE clauses
• Create materialized views for repeatable dashboard aggregations
• Set expire time on staging/temp tables

Medium-term (month 1-2)

• Evaluate slots vs on-demand break-even threshold
• Deploy BI Engine for Looker Studio dashboards
• Configure INFORMATION_SCHEMA monitoring (weekly cost report)
• Consider showback model per-team based on BigQuery usage

How We Can Help

At Devopsity, we optimise GCP infrastructure for financial services organisations across the UK and Europe. A typical BigQuery cost engagement:

• Audit of existing tables and queries (identify top-10 cost offenders) - 1 day
• Implementation of partitioning, clustering, and materialized views - 2-3 days
• Configuration of budgets, alerts, and monitoring - 1 day
• Slots vs on-demand break-even analysis and pricing model migration - 1-2 days

If your BigQuery bills are growing in proportion to analyst headcount rather than data volume - the problem is likely table structure, not business scale.

BigQuery bills out of control?

Book a free 30-minute call. No pitch - a technical conversation about your data pipelines.

Book a call

Frequently Asked Questions

How much does BigQuery on-demand cost?

$6.25 per terabyte of data processed. The first terabyte per month is free (Free Tier). Storage costs $0.02/GB/month (active data) or $0.01/GB/month (data unmodified for >90 days).

Is partitioning free?

Yes. Partitioning does not introduce additional costs - it simply changes the physical layout of data. Savings appear immediately because queries scan less data.

Slots or on-demand for a pipeline with 50 scheduled queries daily?

Depends on data volume. If your 50 queries collectively scan >500 GB/day (>15 TB/month), slots are cheaper. Calculate: 15 TB x $6.25 = $94/month on-demand vs 50 slots Standard Edition = $0.04 x 50 x 730h = $1,460/month. In this case, on-demand is cheaper. At 100 TB/month ($625 on-demand), slots begin to win.

Can I limit BigQuery budget per-team?

Yes. Use separate GCP projects per-team with individual Cloud Billing budgets. Alternatively, BigQuery Reservations let you assign slots to specific projects, preventing one team from consuming another’s resources.

How quickly will I see savings after implementing partitioning?

Immediately - from the first query on a partitioned table. If migrating an existing table, you need to recreate it with partitioning (CTAS - CREATE TABLE AS SELECT) or use bq cp with the time partitioning option.