-- A partitioned, clustered table: BigQuery only reads the slice of data
-- your WHERE clause actually needs, instead of the whole table.
CREATE TABLE my_dataset.events
PARTITION BY DATE(event_timestamp)   -- one physical partition per day
CLUSTER BY user_id, event_type       -- data within each partition is
                                      -- sorted/grouped by these columns
OPTIONS (
  require_partition_filter = true    -- refuses to run a query that
                                      -- forgot a date filter, so nobody
                                      -- accidentally scans (and pays for)
                                      -- the entire table's history
)
AS SELECT * FROM raw_events;

-- This query only touches ONE day's partition, not the whole table:
SELECT user_id, COUNT(*) AS events
FROM my_dataset.events
WHERE DATE(event_timestamp) = '2024-01-15'   -- partition pruning
  AND event_type = 'purchase'                -- clustering narrows further
GROUP BY user_id;

-- Dry run: estimates bytes scanned without actually running the query
-- (in the console, this happens automatically as you type — watch the
-- "This query will process X MB/GB" indicator before clicking Run)
SELECT corpus, SUM(word_count) AS total_words
FROM `bigquery-public-data.samples.shakespeare`
GROUP BY corpus
ORDER BY total_words DESC;

-- Partitioned tables: Dramatically reduce bytes scanned
CREATE TABLE my_dataset.events
PARTITION BY DATE(event_timestamp)
CLUSTER BY user_id, event_type  -- Further reduce scan within partition
OPTIONS (
  require_partition_filter = true  -- Prevent expensive full-table scans
)
AS SELECT * FROM raw_events;

-- Query only the partition you need
SELECT user_id, COUNT(*) as events
FROM my_dataset.events
WHERE DATE(event_timestamp) = '2024-01-15'  -- Uses partition pruning
  AND event_type = 'purchase'  -- Uses clustering
GROUP BY user_id;

-- Avoid SELECT * (scans all columns — BigQuery is columnar)
-- Only select columns you need

-- Use APPROX_COUNT_DISTINCT instead of COUNT(DISTINCT x) for large tables
-- Much faster, 1% error rate acceptable for analytics
SELECT APPROX_COUNT_DISTINCT(user_id) as unique_users
FROM my_dataset.events
WHERE DATE(event_timestamp) = '2024-01-15';

-- Window functions for analytics
SELECT 
  user_id,
  event_timestamp,
  SUM(revenue) OVER (PARTITION BY user_id ORDER BY event_timestamp 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as lifetime_revenue,
  RANK() OVER (PARTITION BY DATE(event_timestamp) ORDER BY revenue DESC) as daily_rank
FROM my_dataset.purchases;

-- Train a logistic regression model in SQL
CREATE OR REPLACE MODEL my_dataset.churn_model
OPTIONS (
  model_type = 'LOGISTIC_REG',
  input_label_cols = ['churned']
) AS
SELECT
  user_age,
  days_since_last_purchase,
  total_purchases,
  avg_order_value,
  churned
FROM my_dataset.user_features
WHERE split = 'TRAIN';

-- Make predictions
SELECT user_id, predicted_churned, predicted_churned_probs
FROM ML.PREDICT(MODEL my_dataset.churn_model,
  SELECT * FROM my_dataset.user_features WHERE split = 'TEST'
);

Query pricing: on-demand pricing is per TB of bytes scanned *(the commonly-cited $5/TB figure needs verification against current official BigQuery pricing — it varies by region and Google has changed pricing tiers before)*
  Use partitioning and clustering to reduce bytes scanned
  
Storage pricing: active vs long-term (90+ days unmodified) storage are priced differently, with long-term being cheaper *(exact current $/GB/month figures need verification against the official pricing page)*

Cost controls:
  Custom quotas: Limit bytes per user/project per day
  BigQuery reservations: Flat-rate pricing for predictable workloads
  Dry run: Estimate bytes before running query

-- Check query cost before running (Python client)
job_config = bigquery.QueryJobConfig(dry_run=True, use_query_cache=False)
query_job = client.query(sql, job_config=job_config)
print(f"Will scan {query_job.total_bytes_processed / 1e9:.2f} GB")