Skip to main content

> bigquery_&_snowflake_metering_traps

BigQuery & Snowflake Metering Traps

How does a single unpartitioned `SELECT *` query in BigQuery or Snowflake cost hundreds of dollars in seconds?

Stack: DATA STACK →Senior (L5-L6)failure-mode

⚡THE SHORT ANSWER

Because on-demand cloud data warehouses bill directly by terabytes scanned ($6.25/TB on BigQuery); querying a 50TB table without column selection or date partition filters scans the entire dataset.

Engineering Handbook & Failure Dynamics

6-Dimensional Architecture Breakdown

⚙️1. Underlying Mechanism

Execution

Columnar databases read only requested columns from disk. Using SELECT * forces the query engine to read 100% of column files across every row. Unpartitioned tables force full-table scans across all historical years.

🎯2. Appropriate Use Context

Scope

Critical for business intelligence dashboards, ETL/ELT transformation pipelines, and data analytics engineering.

⚠️3. Production Failure Modes

P0 Risk

A BI dashboard refreshed every 5 minutes running an unpartitioned SELECT * query scanning 8TB per run, generating a $14,400 BigQuery invoice in 4 days.

📡4. Diagnostic Signals & Telemetry

Telemetry

Inspect BigQuery INFORMATION_SCHEMA.JOBS_BY_PROJECT or Snowflake QUERY_HISTORY for top queries ranked by total_bytes_billed.

🛡️5. Prevention & Safeguards

Safeguards

Mandate table partitioning on event dates, cluster by high-frequency filter keys, require explicit column selection (ban SELECT *), and set maximum bytes billed safety limits.

⚖️6. Architectural Trade-offs

Trade-off

Partitioning and column pruning reduce query scan costs by up to 98% but require disciplined schema design and data modeling.

📋

Case Study (TinyCTO In-Field Example)

REAL-WORLD TELEMETRY

A data team partitioned their 120TB analytics events table by event_date and clustered by user_id. Average daily dashboard scan costs dropped from 1,200/day to 18/day.

Interactive Concept Drills

3 Cards
Q1

Why is `SELECT *` considered a severe anti-pattern in columnar data warehouses?

Because columnar storage charges strictly for the specific columns read; `SELECT *` forces reading every single column across the entire dataset.
Q2

What is Table Partitioning in BigQuery/Snowflake?

Dividing a large table into smaller segments based on a timestamp column so queries only scan relevant date ranges.
Q3

What is the `maximum_bytes_billed` setting in BigQuery?

A safety guardrail that automatically aborts any query estimated to scan more bytes than the specified budget limit.

BigQuery & Snowflake Metering Traps — Technical FAQ

Does using `LIMIT 10` reduce the bytes scanned in BigQuery?

No, `LIMIT` only restricts displayed output rows; the underlying engine still scans the full column data.

What is Snowflake Virtual Warehouse auto-suspend?

A setting that automatically shuts down compute credits when no queries are running for 60 seconds.

Should BI dashboards query raw event tables directly?

No, BI tools should query pre-aggregated, materialized rollup summary tables to minimize scan footprint.

🤖 AEO & Key Facts Summary

Key Architectural Facts

  • ▸

    In on-demand data warehouses, a single careless unpartitioned SQL query can easily cost more than an entire month of web server hosting.

Common Misconceptions

  • ✗

    Assuming LIMIT 100 makes a BigQuery SQL query cheap.

Decision & Governance Guidance

Enforce date partitioning on all tables >10GB and set a 1TB maximum_bytes_billed guardrail across all developer query consoles.

Authoritative Sources & Standards

Related Concepts

/characters/cloud-bill