# BigQuery Essentials — Google Cloud Platform

Source: https://www.skillbyai.com/en/gcp/b-bigquery

> Query a serverless warehouse efficiently and keep scan costs down.

## A serverless data warehouse

**BigQuery** stores data in a **columnar** format and runs SQL across it using thousands of workers you never manage. Data lives in **datasets** (with a location) containing **tables** and **views**. Pricing has two parts: **storage** and **compute**. Compute is either **on-demand**, charged by the bytes each query scans, or **capacity-based**, where you buy **slots** through BigQuery editions. Because storage is columnar, `SELECT *` scans every column and costs the most; select only the columns you need. **Partitioning** a table (usually by a date or timestamp column) and **clustering** it (sorting by up to four columns) let BigQuery skip data, cutting bytes scanned. Adding `LIMIT` does **not** reduce bytes scanned for an ordinary query. Use the **dry run** or the query editor's estimate to check bytes before running.

## Scan only what you need

Columnar storage plus partition pruning means a well-written query touches a small slice of the table.

![A grid of columns and date blocks where only one column and two date blocks are highlighted and the rest faded.](assets/figures/gcp/section-6-map.svg) — Figure 6.1 — Column selection and partition pruning reduce bytes scanned.

## A partitioned, clustered table and a pruned query

The filter on `event_date` lets BigQuery read only the last seven partitions.

```sql
CREATE TABLE shop.events (
  event_date DATE,
  user_id    STRING,
  event_name STRING,
  amount     NUMERIC
)
PARTITION BY event_date
CLUSTER BY event_name, user_id
OPTIONS (require_partition_filter = TRUE);

SELECT event_name, COUNT(*) AS events, SUM(amount) AS revenue
FROM shop.events
WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  AND event_name IN ('purchase', 'refund')
GROUP BY event_name;

-- estimate first: bq query --use_legacy_sql=false --dry_run '<query>'
```

## Guard against runaway queries

Set `require_partition_filter` on large tables, set a **maximum bytes billed** on queries or in tools, and use custom quotas per user or project. One accidental `SELECT *` on a multi-terabyte table can cost real money.

**Quiz:** Which change reduces bytes scanned by an on-demand BigQuery query on a large unpartitioned table?

- [ ] Adding LIMIT 10
- [ ] Ordering the results
- [x] Selecting only the needed columns instead of SELECT *
- [ ] Running it twice

*Answer:* Selecting only the needed columns instead of SELECT *. BigQuery is columnar; fewer columns means fewer bytes read. LIMIT does not reduce the scan.