Forem: Arunkumar Amaran

Beyond Monitoring: Building AI-Powered Predictive Observability for Retail Data Pipelines published

Arunkumar Amaran — Thu, 07 May 2026 11:39:01 +0000

Three numbers before we start:

Average detection time with traditional monitoring: 4.2 hours

Average detection time with predictive observability: 11 minutes

False-positive alert reduction after ML tuning: 73%

There is a specific moment every data engineer knows. It is 6:47 AM. Your phone goes off. A VP of Merchandising is asking why the overnight inventory report is blank. You pull up the dashboard. Everything is green. Every pipeline shows "success." Every SLA is marked as met.

The pipeline ran. It just ran on three hours of missing upstream data, produced a table with 94% fewer rows than expected, and nobody — no alert, no monitor, no threshold — caught it. The pipeline was technically healthy. The data inside it was quietly wrong.

That is the gap between monitoring and observability. It is the gap I spent two years closing at enterprise scale in retail. This post is about how we did it, what we got wrong first, and what actually works in production.

Why traditional monitoring fails retail specifically

Traditional pipeline monitoring is threshold-based. You write a rule: alert if the orders table has fewer than 50,000 rows. That works fine until Black Friday, when you have 400,000 rows and the threshold no longer means anything. Or until a regional distribution center goes offline and your count drops to 49,500 — technically below threshold, but completely expected given what just happened upstream.

Rule-based monitoring has three structural problems in retail that don't go away with better tooling or tighter thresholds.

The first is seasonality. Retail data swings by orders of magnitude across a calendar year. A row count that signals catastrophe in July is unremarkable the week after Christmas returns. Static thresholds fire alerts during sales events — when volume is high and normal — and go silent during actual failures that happen in quieter periods.

The second is upstream dependency complexity. A typical enterprise retail pipeline touches supplier EDI feeds, POS systems across hundreds of stores, e-commerce platforms, and ERP systems. Each has its own failure characteristics. A CDC feed going silent in one region can cascade silently into a dozen downstream tables before anyone runs a query that exposes it.

The third, and the one that costs the most, is silent corruption. The pipeline runs. The job succeeds. The data is subtly wrong. A join dropped 12% of rows because a vendor changed a surrogate key format. A currency conversion ran against yesterday's exchange rate because the FX feed was stale. No rule catches this. No threshold fires. The business finds it at quarter-end review.

The failures that cost the most are the ones that don't look like failures. The pipeline ran. The job succeeded. The data is quietly wrong. Rule-based monitoring has no way to catch these — it doesn't understand what "correct" looks like for your business.

The architecture: four layers that each solve a different problem

What we built is not a single observability product. It is four layers, each with a distinct responsibility.

┌──────────────────────────────────────────────────────────────────┐
│          Retail Predictive Observability Platform                │
│                                                                  │
│  Layer 1 — Signal Collection                                     │
│  pipeline telemetry · data profiles · lineage events            │
│  POS · ERP · CDC · EDI feeds · e-commerce · Kafka               │
│                          │                                       │
│  Layer 2 — Feature Engineering                                   │
│  seasonal decomposition · z-scores · lag features               │
│  rolling baselines · promo calendar join · upstream state        │
│                          │                                       │
│  Layer 3 — ML Anomaly Detection                                  │
│  Prophet · Isolation Forest · LSTM autoencoder · XGBoost        │
│  per-pipeline per-metric models · confidence scoring             │
│                          │                                       │
│  Layer 4 — Intelligent Alerting + Auto-Remediation              │
│  PagerDuty · Slack · root cause trace · runbook links           │
│  circuit breakers · data quarantine · replay triggers            │
└──────────────────────────────────────────────────────────────────┘

Understanding why each layer exists matters more than implementation details, because the implementation looks different in every organization. The problem structure is universal.

Layer 1 — Signal collection: what to collect and why it matters

Most teams collect pipeline job metrics: run time, rows written, bytes processed. That is a starting point, not a solution. The signals with the highest predictive value are usually the ones nobody is collecting.

In retail at scale, three signal types stand out:

Row-count velocity — not just the count, but how fast it's changing relative to the rolling 30-day baseline for that specific day of week within the promotional calendar
Null rate trajectories on key join columns — a null rate on customer_id drifting from 0.2% to 1.8% over six hours is the canary for upstream schema drift
Upstream heartbeat freshness — if a supplier EDI feed goes quiet and you're not watching for the absence of data, you won't know until the inventory table starts drifting. Silence is a signal.

import json
from google.cloud import pubsub_v1, bigquery
from datetime import datetime, timezone

def publish_pipeline_signal(pipeline_id: str, table_ref: str) -> None:
    bq    = bigquery.Client()
    pub   = pubsub_v1.PublisherClient()
    topic = pub.topic_path("retail-platform", "pipeline-signals")

    query = f"""
        SELECT
          COUNT(*)                                   AS row_count,
          COUNTIF(customer_id IS NULL) / COUNT(*)    AS null_rate_customer,
          COUNTIF(store_id    IS NULL) / COUNT(*)    AS null_rate_store,
          MIN(order_timestamp)                       AS earliest_event,
          MAX(order_timestamp)                       AS latest_event,
          TIMESTAMP_DIFF(
            MAX(order_timestamp), MIN(order_timestamp), SECOND
          )                                          AS event_window_secs
        FROM `{table_ref}`
        WHERE DATE(ingestion_ts) = CURRENT_DATE()
    """

    row    = list(bq.query(query).result())[0]
    signal = {
        "pipeline_id":    pipeline_id,
        "collected_at":   datetime.now(timezone.utc).isoformat(),
        "row_count":      row.row_count,
        "null_rate_cust": round(row.null_rate_customer, 6),
        "null_rate_store":round(row.null_rate_store, 6),
        "event_window_s": row.event_window_secs,
    }
    pub.publish(topic, json.dumps(signal).encode())

Layer 2 — Feature engineering: teaching the model what "normal" means in retail

This is the layer most teams underinvest in, and it is why their anomaly detectors produce so many false positives. A model with no awareness of the promotional calendar will flag every Black Friday as an anomaly. Context is everything.

CREATE OR REPLACE TABLE `obs.pipeline_features` AS
WITH history AS (
  SELECT
    pipeline_id,
    collected_date,
    row_count,
    EXTRACT(DAYOFWEEK FROM collected_date)          AS day_of_week,
    IF(promo.event_name IS NOT NULL, 1, 0)           AS is_promo_day,
    AVG(row_count)    OVER same_dow_window            AS baseline_row_count,
    STDDEV(row_count) OVER same_dow_window            AS baseline_stddev,
    LAG(row_count, 7) OVER (
      PARTITION BY pipeline_id ORDER BY collected_date
    )                                                 AS lag_7d_row_count
  FROM      `obs.pipeline_signals` s
  LEFT JOIN `retail.promo_calendar` promo USING (collected_date)
  WINDOW same_dow_window AS (
    PARTITION BY pipeline_id,
                 EXTRACT(DAYOFWEEK FROM collected_date)
    ORDER BY collected_date
    ROWS BETWEEN 28 PRECEDING AND 1 PRECEDING
  )
)
SELECT *,
  SAFE_DIVIDE(
    row_count - baseline_row_count,
    NULLIF(baseline_stddev, 0)
  ) AS z_score
FROM history

The promotional calendar join is the single most important feature in the entire model. Without it, every major sale event fires alerts. With it, the model stays sensitive to the anomalies that happen outside known business events. After adding this join, our false positive rate on volume signals dropped from ~31% to under 6%.

Layer 3 — ML anomaly detection: right model for the right signal

Signal type	Best model	Reason
Row count / volume	Prophet (Meta)	Native seasonality + holiday support
Null rate / schema drift	Isolation Forest	No seasonality, strong on point anomalies
Event timing / freshness	LSTM autoencoder	Learns sequence patterns across runs
Cascade failure prediction	XGBoost + lineage graph	Propagation paths from upstream signals

from prophet import Prophet
import pandas as pd

def train_volume_model(
    pipeline_id: str,
    history_df: pd.DataFrame,
    promo_calendar: pd.DataFrame,
) -> Prophet:
    # Prophet expects columns 'ds' (datetime) and 'y' (metric value)
    df = history_df.rename(
        columns={"collected_date": "ds", "row_count": "y"}
    )

    # Promotional events become "holidays" — model won't flag these
    holidays = promo_calendar.rename(
        columns={"event_name": "holiday", "event_date": "ds"}
    )[["holiday", "ds"]]

    model = Prophet(
        holidays=holidays,
        yearly_seasonality=True,
        weekly_seasonality=True,
        daily_seasonality=False,
        interval_width=0.95,           # flag outside 95% prediction interval
        changepoint_prior_scale=0.05   # conservative — retail is stable
    )
    model.fit(df)
    return model

def score_run(model: Prophet, actual: float, run_date: str) -> dict:
    future   = model.make_future_dataframe(periods=0)
    forecast = model.predict(future)
    today    = forecast[forecast["ds"] == run_date].iloc[-1]

    anomaly   = (actual < today["yhat_lower"] or actual > today["yhat_upper"])
    deviation = abs(actual - today["yhat"]) / today["yhat"] * 100

    return {
        "is_anomaly":      anomaly,
        "expected":        round(today["yhat"]),
        "actual":          actual,
        "deviation_pct":   round(deviation, 1),
        "confidence_low":  round(today["yhat_lower"]),
        "confidence_high": round(today["yhat_upper"]),
    }

Layer 4 — Intelligent alerting: the alert itself needs to be intelligent

Every alert we send includes the anomaly score and confidence interval, a lineage trace showing which downstream tables and dashboards are in the blast radius, a ranked list of root cause hypotheses, and for known failure patterns a direct link to the remediation playbook or an auto-triggered recovery action.

def build_alert(pipeline_id: str, anomaly: dict, graph) -> dict:
    downstream  = graph.get_downstream(pipeline_id, depth=3)
    hypotheses  = graph.rank_upstream_suspects(
                      pipeline_id, anomaly["signal_type"])
    playbook    = get_playbook(pipeline_id, anomaly["anomaly_class"])

    return {
        "title":             f"[PREDICTIVE] {pipeline_id} — {anomaly['anomaly_class']}",
        "severity":           _severity(anomaly["deviation_pct"]),
        "anomaly_detail":     anomaly,
        "impacted_tables":   [t.ref  for t in downstream],
        "at_risk_dashboards":[d.name for d in downstream if d.type == "dashboard"],
        "root_cause_ranked":  hypotheses[:3],
        "auto_remediation":   playbook.can_auto_fix,
        "runbook_url":        f"https://runbooks.internal/{pipeline_id}",
    }

The failure patterns nobody warns you about

Alert fatigue from under-tuned models. If your anomaly detector fires 40 alerts per day and 35 are false positives, on-call engineers will mute it within two weeks. A model that catches 70% of real anomalies with 5% false positive rate is worth more than one catching 95% with 40% false positive rate. The first gets trusted. The second gets circumvented.

Skipping the promotional calendar integration. This sounds obvious in hindsight. It never is in the moment, because observability systems are usually built without a retail domain expert in the room. Do it on day one or spend three months wondering why your model fires on every promotional period.

Building without lineage integration. An anomaly detector that fires "pipeline X has a volume anomaly" is marginally better than a threshold alert. One that fires "pipeline X has a volume anomaly affecting inventory_fact, markdown_analysis_daily, and the replenishment dashboard — root cause: DC_NORTH_EAST CDC feed went silent 47 minutes ago" is a completely different product.

An alert that tells you something is wrong is a notification. An alert that tells you what's wrong, why it's probably wrong, what it's affecting downstream, and what to do in the next five minutes — that's intelligence. The gap between the two is the entire value of building this right.

What actually changes when it's working

The most visible change is not technical. It's the nature of the 6:47 AM phone call.

When the system is working, the on-call engineer gets paged at 4:23 AM — before anyone in the business has noticed — with an alert that reads: "Predicted volume shortfall in orders_daily_fact (62% of expected, outside 95% confidence interval for Tuesday non-promo). Upstream signal: DC_NORTH_EAST CDC feed went silent at 3:51 AM. Downstream impact: inventory_replenishment_pipeline (high risk), promo_attribution_daily (medium risk). Auto-remediation available: trigger last-known-good replay."

The engineer investigates, confirms the DC feed outage, triggers the replay. The inventory report runs correctly at 6 AM. Nobody in Merchandising ever knows there was a problem.

That is the goal. Not better dashboards. Not faster alerts. Invisible reliability — where failures are detected and resolved before they are visible to the business.

Implementation checklist

[ ] Signal collection covers row count, null rates, event timing, upstream heartbeat freshness
[ ] Promotional calendar integrated as feature context in all volume-based models
[ ] Separate models per signal type — Prophet for volume, Isolation Forest for drift
[ ] Lineage graph built and queryable for downstream blast-radius assessment
[ ] Alert payload includes ranked root cause hypotheses, not just an anomaly score
[ ] False positive rate validated at < 10% on held-out data before production rollout
[ ] Auto-remediation playbooks defined for top 5 known failure patterns
[ ] On-call runbooks updated to reference the observability system output

Have you built predictive observability for data pipelines? What signals have you found most predictive — or most misleading? Drop it in the comments. The edge cases are always where the real learning happens.

Arunkumar Amaran — Tech Manager, Data Engineering & Architecture at Macy's Systems & Technology. 20+ years building enterprise data platforms on GCP, AWS, and Azure. IEEE Senior Member. Writes about the things that don't make it into the official docs.

🔗 linkedin.com/in/arunkumar-amaran · 📧 arunkumar.amaran@ieee.org · ORCID: 0009-0007-8225-0132

Opinions are my own and do not represent my employer.

How to Optimize BigQuery Costs (Real Techniques That Work)

Arunkumar Amaran — Wed, 06 May 2026 00:40:36 +0000

I want to start with a number: $140,000.

That's how much a single misconfigured scheduled query cost one of the teams I worked with over a three-month period. Not a rogue machine learning job. Not a massive ETL pipeline. A scheduled query. Running every 15 minutes. Scanning a 4TB table every time. For 90 days before anyone looked at the bill closely enough to notice.

Nobody wrote that query with malicious intent. The engineer who wrote it was experienced. The table had been small when the query was first created. Then the table grew. The query didn't know that. The bill did.

I've spent the better part of the last decade building and optimizing data platforms on GCP — including BigQuery at enterprise scale at Macy's Systems & Technology. This post is what I wish someone had handed me early on. Not the official Google documentation version of cost optimization. The actual production version, including the mistakes that end up in the bill.

First: Understand What You're Actually Paying For

BigQuery has two pricing models and a lot of people mix them up or don't think about which one they're on.

On-demand pricing charges you per byte scanned. As of 2026, that's around $6.25 per TB in most regions. Every query you run, BigQuery measures how much data it touched and charges accordingly. Write a bad query that scans 10TB when it needed to scan 10GB and you've just paid 1,000x more than necessary.

Capacity pricing (flat-rate / editions) charges you for reserved compute slots — basically compute reservations — regardless of how much data you scan. If your workloads are predictable and heavy, this can be dramatically cheaper. If your workloads are bursty and light, on-demand is usually better.

Most teams start on on-demand and never revisit that decision. That's often a mistake. But before you switch, you need to understand your query patterns — and that's what the rest of this post is largely about.

The Single Biggest Lever: Stop Scanning Data You Don't Need

On-demand BigQuery charges by bytes scanned, not bytes returned. That means the columns you don't SELECT and the rows you don't filter still cost money if BigQuery has to read past them.

Never use `SELECT *`

I know you know this. I also know that half the scheduled queries in your project have SELECT * in them right now. Go check.

-- This scans every column in a 2TB table
SELECT * FROM `project.dataset.orders`
WHERE order_date = '2026-01-15'

-- This scans only what you need
SELECT order_id, customer_id, total_amount
FROM `project.dataset.orders`
WHERE order_date = '2026-01-15'

The second query might cost 10-20x less depending on how wide the table is. For a table with 60 columns where you need 4, the math is brutal.

Use partition pruning — and verify it's working

Partitioned tables are one of BigQuery's most powerful cost controls, but they only work if your query filter actually triggers partition elimination.

-- ✅ This WILL prune partitions (uses the partition column directly)
SELECT order_id, total_amount
FROM `project.dataset.orders`
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31'

-- ❌ This will NOT prune partitions (wraps the column in a function)
SELECT order_id, total_amount
FROM `project.dataset.orders`
WHERE DATE(order_timestamp) BETWEEN '2026-01-01' AND '2026-01-31'

The second query looks like it should be equivalent. It isn't — wrapping the partition column in a function forces a full table scan. This is one of the most common and expensive mistakes I see in production.

How to verify: Run the query and look at the execution details. If you see "Partitions scanned: 1 of 365" — great, it's working. If you see "Partitions scanned: 365 of 365" on a query filtering a single day — your filter isn't hitting the partition column the way you think it is.

Cluster your tables

Partitioning handles time-based filtering. Clustering handles everything else. If you're frequently filtering by customer_id, product_id, store_id, or region — those columns should be in your clustering key.

CREATE TABLE `project.dataset.orders`
PARTITION BY DATE(order_timestamp)
CLUSTER BY customer_id, store_id
OPTIONS (require_partition_filter = TRUE)
AS SELECT * FROM `project.dataset.orders_raw`

That require_partition_filter = TRUE option is worth calling out explicitly. It makes BigQuery reject any query that doesn't include a partition filter. Annoying to developers at first. Saves you from the $140,000 mistake I mentioned at the top.

Materialized Views: The Most Underused Feature in BigQuery

Most people know materialized views exist. Few people use them properly for cost optimization.

Here's the thing about BigQuery materialized views that isn't obvious: they can answer queries even when the query doesn't directly reference them. BigQuery's query optimizer will automatically rewrite a query against a base table to use a materialized view instead, if the view can satisfy the query and is more efficient.

-- Create the materialized view
CREATE MATERIALIZED VIEW `project.dataset.daily_sales_mv`
PARTITION BY sale_date
AS
SELECT
  DATE(order_timestamp)     AS sale_date,
  store_id,
  product_id,
  SUM(quantity)             AS total_units,
  SUM(total_amount)         AS total_revenue,
  COUNT(DISTINCT order_id)  AS order_count
FROM `project.dataset.orders`
GROUP BY 1, 2, 3;

Now here's where it gets interesting. A dashboard query that does:

SELECT store_id, SUM(total_amount) AS revenue
FROM `project.dataset.orders`
WHERE DATE(order_timestamp) = '2026-01-15'
GROUP BY store_id

...might get automatically rewritten to query the materialized view instead of the full orders table — even though the query never mentioned the materialized view. That's BigQuery doing the cost optimization for you, provided you've set things up right.

The maintenance overhead is minimal. The cost reduction on heavily queried aggregations can be 80-95%.

Query Cost Estimation Before You Run

This one sounds obvious but is skipped constantly: estimate cost before running unfamiliar queries, especially on large tables.

In BigQuery console, every query shows a bytes-to-be-processed estimate in the top right before execution. Pay attention to it.

In code, you can do a dry run:

from google.cloud import bigquery

client = bigquery.Client()

job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=True)

query = """
    SELECT *
    FROM `project.dataset.large_table`
    WHERE region = 'US'
"""

job = client.query(query, job_config=job_config)

# Bytes processed if this query ran
bytes_processed = job.total_bytes_processed
gb_processed = bytes_processed / (1024 ** 3)
estimated_cost = (bytes_processed / (1024 ** 4)) * 6.25  # $6.25 per TB

print(f"This query would scan: {gb_processed:.2f} GB")
print(f"Estimated cost: ${estimated_cost:.4f}")

Build this into your data pipeline development workflow. Make developers run a dry-run estimate before merging new queries into production. It takes 30 seconds. It occasionally saves you $10,000.

Slots, Reservations, and When Flat-Rate Actually Makes Sense

The on-demand vs. flat-rate decision is one that most teams make once and never revisit. That's a mistake because the right answer changes as your workload matures.

On-demand is usually right when:

Your query volume is unpredictable or spiky
You're early-stage and still figuring out your workload patterns
Your monthly BigQuery bill is under ~$2,000

Flat-rate (Editions) is usually right when:

You have consistent, heavy query workloads running throughout the day
You're running large-scale batch processing jobs on a schedule
Your on-demand bill has stabilized and is consistently above ~$3,000/month

The crossover point depends on your region and edition tier, but a rough rule: if you're consistently buying more than 500 slot-hours per month at on-demand prices, flat-rate reservations are worth modeling out.

One trap to avoid: Buying flat-rate capacity and then not using it. Idle reserved slots cost the same as busy ones. If you're going to commit to flat-rate, you need to actually fill those slots — which means scheduling your heavy batch jobs during off-peak hours to maximize slot utilization.

-- Check your slot utilization in INFORMATION_SCHEMA
SELECT
  TIMESTAMP_TRUNC(period_start, HOUR)     AS hour,
  project_id,
  SUM(period_slot_ms) / (1000 * 3600)    AS slot_hours_used
FROM `region-us`.INFORMATION_SCHEMA.TIMELINE_BY_PROJECT
WHERE period_start >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY 1, 2
ORDER BY 1 DESC

Run this before committing to any reservation purchase. If you're averaging 40 slot-hours per hour during business hours and near-zero overnight, a flat-rate reservation sized for your peak will be mostly idle.

INFORMATION_SCHEMA: Your Cost Debugging Best Friend

BigQuery's INFORMATION_SCHEMA views are where you find the expensive queries. Most people don't use them nearly enough.

Find your most expensive queries from the last 30 days

SELECT
  job_id,
  user_email,
  ROUND(total_bytes_processed / POW(1024, 4), 4)  AS tb_processed,
  ROUND((total_bytes_processed / POW(1024, 4)) * 6.25, 2) AS estimated_cost_usd,
  SUBSTR(query, 0, 200)                            AS query_preview,
  creation_time
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
  AND state = 'DONE'
ORDER BY total_bytes_processed DESC
LIMIT 25

Run this query once a week. The top 5 results are almost always where your money is going. In my experience, fixing the top 3 queries on this list typically cuts the bill by 30-50%.

Find queries run by service accounts that are scanning too much

SELECT
  user_email,
  COUNT(*) AS query_count,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4), 2) AS total_tb_processed,
  ROUND(SUM(total_bytes_processed) / POW(1024, 4) * 6.25, 2) AS total_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND job_type = 'QUERY'
GROUP BY user_email
ORDER BY total_tb_processed DESC

Service accounts running scheduled queries are frequently the biggest offenders because nobody's watching them. A dashboard refresh that runs every 15 minutes against an unpartitioned table is 2,880 queries per month. Do the math on what each of those costs and multiply.

Caching: Free Is Good, When It Works

BigQuery caches query results for 24 hours. Identical queries (same SQL, same referenced tables, no changes to underlying data) return cached results at zero cost.

This sounds great. It has a few gotchas:

Caching doesn't work if your query uses non-deterministic functions like CURRENT_TIMESTAMP(), NOW(), or RAND()
Caching doesn't work if you explicitly disable it
Caching doesn't work across projects by default

The biggest practical implication: don't put CURRENT_DATE() or CURRENT_TIMESTAMP() directly in scheduled queries if you can help it. Pass the date as a parameter instead. An identical parameterized query against a table that hasn't changed since the last run will hit the cache. A query with an inline CURRENT_DATE() won't.

-- ❌ This will never hit cache (current_date() makes it non-deterministic)
SELECT store_id, SUM(revenue)
FROM `project.dataset.daily_sales`
WHERE sale_date = CURRENT_DATE()
GROUP BY store_id

-- ✅ Pass the date as a scripting variable or scheduled query parameter
DECLARE run_date DATE DEFAULT DATE('2026-01-15');

SELECT store_id, SUM(revenue)
FROM `project.dataset.daily_sales`
WHERE sale_date = run_date
GROUP BY store_id

The Cost Optimization Mindset Shift

Here's the thing that took me the longest to internalize: BigQuery cost optimization is not a one-time project. It's a continuous practice.

Tables grow. Query patterns change. New analysts join the team and write queries the way they're used to writing queries in other systems. A dashboard that was cheap six months ago isn't necessarily cheap today because the underlying table is now 10x larger.

The teams that control their BigQuery costs over the long term are the ones that have made it a habit — weekly cost reviews, dry-run estimates as part of code review, INFORMATION_SCHEMA queries on a dashboard that someone actually looks at.

The teams that get surprised by the bill are the ones that set it up and assumed it would stay efficient on its own.

It won't. But with the right habits, it's absolutely manageable — and the savings compound faster than you'd expect.

Quick Reference Checklist

Before you ship any new BigQuery query or table to production, run through this:

[ ] No SELECT * — only columns you need
[ ] Table is partitioned on the most common filter column
[ ] Partition filter is on the raw column, not wrapped in a function
[ ] Clustering keys match your most common WHERE and JOIN conditions
[ ] require_partition_filter = TRUE for large tables
[ ] Dry-run estimate reviewed before merging
[ ] Materialized view considered for any aggregation queried more than daily
[ ] No CURRENT_TIMESTAMP() inline if caching matters
[ ] Scheduled query frequency matches actual business need (not "every 15 minutes because that was the default")

Wrapping Up

The $140,000 story at the top of this post isn't the worst one I've heard. It's not even close. BigQuery cost disasters tend to be quiet — they accumulate over weeks or months before anyone notices, and by then the money is gone.

The good news: almost every expensive pattern is avoidable, and most of them are avoidable with techniques that also make your queries faster. Partition pruning, clustering, materialized views, proper column selection — these aren't just cost controls. They're good engineering.

Start with INFORMATION_SCHEMA. Find your top 5 most expensive queries. Fix them. Repeat monthly. You'll be surprised how quickly the bill responds.

Have a BigQuery cost story of your own? Drop it in the comments — the good ones are always educational (and occasionally horrifying).

🔗 linkedin.com/in/arunkumar-amaran · 📧 arunkumar.amaran@ieee.org