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
1. Underlying Mechanism
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
Critical for business intelligence dashboards, ETL/ELT transformation pipelines, and data analytics engineering.
3. Production Failure Modes
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
Inspect BigQuery `INFORMATION_SCHEMA.JOBS_BY_PROJECT` or Snowflake `QUERY_HISTORY` for top queries ranked by `total_bytes_billed`.
5. Prevention & 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
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)
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 CardsWhy is `SELECT *` considered a severe anti-pattern in columnar data warehouses?
What is Table Partitioning in BigQuery/Snowflake?
What is the `maximum_bytes_billed` setting in BigQuery?
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
- [DOC]Google BigQuery Best Practices: Controlling Costs— Google Cloud