bigquery-sql

name	bigquery-sql
description	BigQuery-specific SQL patterns: UNNEST for array expansion, STRUCT, ARRAY_AGG, DATE_DIFF/DATE_ADD, backtick-quoted table references, EXCEPT/REPLACE in SELECT, approximate aggregation, partitioned and wildcard tables.
type	skill

BigQuery SQL Skill

1. Table References - Always Backtick-Quote

-- Full table reference
SELECT * FROM `project.dataset.table`;

-- Can omit project if using the default project
SELECT * FROM `dataset.table`;

2. Array Expansion - Use UNNEST

-- Explode an array column to rows
SELECT id, item
FROM `project.dataset.table`,
UNNEST(array_col) AS item;

-- UNNEST with offset (position)
SELECT id, item, pos
FROM `project.dataset.table`,
UNNEST(array_col) AS item WITH OFFSET AS pos;

-- UNNEST a literal array
SELECT * FROM UNNEST([1, 2, 3]) AS num;

3. Date Functions

-- Add/subtract time
DATE_ADD(order_date, INTERVAL 7 DAY)
DATE_ADD(CURRENT_DATE(), INTERVAL -1 MONTH)

-- Difference between dates
DATE_DIFF(end_date, start_date, DAY)
DATE_DIFF(end_date, start_date, MONTH)

-- Truncate to period
DATE_TRUNC(event_date, MONTH)
TIMESTAMP_TRUNC(event_ts, HOUR)

-- Current date/time
CURRENT_DATE()
CURRENT_TIMESTAMP()

4. SELECT EXCEPT and REPLACE

-- All columns except one
SELECT * EXCEPT (col_to_remove) FROM `dataset.table`;

-- Replace a column value inline
SELECT * REPLACE (UPPER(name) AS name) FROM `dataset.table`;

5. STRUCT and ARRAY_AGG

-- Create a STRUCT
SELECT STRUCT(id, name) AS person FROM `dataset.table`;

-- Aggregate rows into an array
SELECT department, ARRAY_AGG(employee_name) AS employees
FROM `dataset.employees`
GROUP BY department;

-- Aggregate into array of structs
SELECT ARRAY_AGG(STRUCT(id, name)) AS records FROM `dataset.table`;

6. Approximate Aggregation (for large tables)

-- Approximate distinct count (faster for large tables)
APPROX_COUNT_DISTINCT(user_id)

-- Approximate quantiles
APPROX_QUANTILES(value, 100)[OFFSET(50)]  -- median

7. Partitioned Tables

When querying partitioned tables, always filter on the partition column to avoid full-table scans:

-- Partition on _PARTITIONDATE (pseudo-column)
WHERE _PARTITIONDATE >= '2024-01-01'

-- Partition on a date column
WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31'

8. Wildcard Tables (date-sharded)

-- Query all date-sharded tables matching a prefix
SELECT * FROM `project.dataset.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240101' AND '20241231';

9. String Functions

REGEXP_EXTRACT(col, r'pattern')          -- extract first match
REGEXP_REPLACE(col, r'pattern', 'repl')  -- replace matches
SPLIT(col, ',')[SAFE_OFFSET(0)]          -- split, access by index
TRIM(col) / LTRIM(col) / RTRIM(col)
FORMAT('%s-%d', str_col, int_col)        -- printf-style formatting

10. Common Anti-Patterns to Avoid

• Do NOT use = NULL - use IS NULL
• Do NOT forget to filter partitioned tables - costs money
• Do NOT use COUNT(DISTINCT ...) on huge tables - use APPROX_COUNT_DISTINCT
• Always backtick-quote table names with dots in them

11. Benchmark Patterns

• STRING_AGG: Use STRING_AGG(col, ',' ORDER BY col) for string aggregation (not GROUP_CONCAT).
• SAFE_DIVIDE / SAFE_CAST: Use to avoid division-by-zero errors and cast failures.
• IF / IIF: BigQuery supports IF(condition, true_val, false_val) - often cleaner than CASE WHEN for simple conditions.
• GENERATE_DATE_ARRAY / GENERATE_TIMESTAMP_ARRAY: For date spine generation.
• Numeric precision: BigQuery's FLOAT64 can lose precision. Use NUMERIC type or ROUND() only when the question asks for it.
• INFORMATION_SCHEMA: SELECT * FROM dataset.INFORMATION_SCHEMA.COLUMNS for metadata queries - useful when schema_overview is insufficient.

12. Public dataset and large-table patterns

• Cross-project references: Public datasets live under their own project. Fully qualify table references as `project.dataset.table` (backtick-quoted).
• Pipe-delimited string columns: Some sources store lists as delimited strings (e.g., |python|python-2.7|). Match a value while excluding a near-match with two REGEXP_CONTAINS predicates:

  WHERE REGEXP_CONTAINS(tags, r'python-2') AND NOT REGEXP_CONTAINS(tags, r'python-3')
  

• Date columns: Many BQ tables store dates as TIMESTAMP or DATE. Always check the actual type with describe_table.
• Large tables: Use partition filters and LIMIT during exploration. Avoid SELECT * on tables with >1M rows.