Warning

🚧 Work in Progress: This page is currently under construction. Content may be incomplete or subject to change. To contribute, see the contribution guide.

SQL Standards (BigQuery)

---

Formatting

• Keywords in UPPERCASE: SELECT, FROM, WHERE, JOIN, etc.
• Column and table names in snake_case: reference_date, gross_value
• One column per line in SELECTs with more than 3 columns
• CTEs preferred over nested subqueries

---

Standard query structure

-- Description of what this query does
-- Author: data-squad | Date: YYYY-MM-DD
 
WITH base AS (
  SELECT
    fund_id,
    reference_date,
    share_value,
    SUM(investment_amount) AS total_invested
  FROM `project.gold_investments.shares`
  WHERE reference_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  GROUP BY 1, 2, 3
),
 
result AS (
  SELECT
    b.*,
    f.fund_name
  FROM base b
  LEFT JOIN `project.gold_investments.funds` f USING (fund_id)
)
 
SELECT * FROM result
ORDER BY reference_date DESC

---

Table and column naming

See the data naming standard.

---

BigQuery performance

• Always use partition filter on date-partitioned tables
• Avoid SELECT * in production — list columns explicitly
• Use APPROX_COUNT_DISTINCT when exact counts are not needed
• Clustered tables for columns frequently used in WHERE/JOIN