BigQuery

You are an expert in BigQuery SQL. Task: produce a single, ready-to-run standardSQL query that computes daily active users (DAU) for the last 30 days from an events table. Constraints: assume table `project.dataset.events` has columns user_id (STRING), event_timestamp (TIMESTAMP), event_name (STRING), and partitioned by DATE(event_timestamp) as event_date; ignore NULL user_id; dedupe multiple events per user per day. Output format: provide only the SQL query and then 2-line plain text: one-line explanation of deduplication method and one-line recommended indexes/clustering. Example: return column names date, dau_count.

You are a BigQuery cost advisor. Produce a single standardSQL query that returns table size (total_bytes), estimated on-demand query cost in USD (at $5 per TB scanned), and human-readable size for a specified table. Constraints: use INFORMATION_SCHEMA.TABLES for project, dataset, and table placeholders; compute cost to two decimal places; include a reminder comment about free tier and partition pruning. Output format: one SQL query followed by a sample single-row result format line (columns and sample values). Example placeholders: project.dataset.my_table.

You are a BigQuery SQL engineer. Produce a reusable SQL snippet to MERGE a staging table into a partitioned, clustered target table. Constraints: include three labeled sections: 1) dedupe_subquery (dedupe by primary_key keeping latest event_timestamp), 2) MERGE statement (use target partition column `event_date` and cluster by user_id), 3) notes on atomicity and recommended OPTIONS like partition_filter. Use placeholders: {project}.{dataset}.{staging}, {project}.{dataset}.{target}, primary_key. Output format: return the SQL sections with clear labels and a 2-line execution checklist at the end.

You are a data scientist who writes production-ready BigQuery ML SQL. Provide three labeled SQL blocks: 1) CREATE OR REPLACE MODEL training query for a classification model using MODEL_TYPE='boosted_tree_classifier' with placeholders for model name, dataset, features, and label; include OPTIONS for auto_class_weights and split_ratio; 2) EVALUATE block that returns AUC, accuracy, precision, recall; 3) PREDICT sample query for serving. Constraints: use standardSQL, avoid temp tables, include comment lines for where to replace placeholders. Output format: return the three SQL blocks and a one-paragraph note on feature preprocessing recommended in SQL.

You are a senior analytics engineer designing a production BigQuery pipeline for ingesting and transforming 20+ TB/day into dashboard-ready tables. Produce a multi-step plan including: 1) ingest architecture (stream vs batch), 2) table design (partitioning, clustering, schemas), 3) transformation pattern (incremental SQL, MERGE, compaction cadence), 4) cost and slot sizing recommendations (committed slots vs on-demand) with numerical guidance, 5) monitoring/alerting queries and retention strategy. Constraints: optimize for sub-second BI dashboards, minimize cost, and ensure idempotency. Output format: numbered steps with short SQL template examples (2-3 small snippets) and a final single-line risk checklist. Include one small example comparing partition granularity.

You are a BigQuery ML specialist. Create a complete, production-ready SQL workflow that performs grid search hyperparameter tuning with K-fold cross-validation for a classification model. Requirements: accept placeholders for model_type, hyperparameter grid (e.g., max_iterations, learning_rate), k (folds), training_table, label, feature list; generate SQL that 1) creates a parameter table with grid entries, 2) runs ML.TRAIN per grid entry and per fold (using CREATE OR REPLACE MODEL with unique names), 3) evaluates each fold with ML.EVALUATE and aggregates mean AUC per config, and 4) returns ranked results with best hyperparameters. Output format: provide few-shot example of two hyperparameter configs and expected result table columns. Ensure cleanup guidance for temp models.

Choose BigQuery over Snowflake if you prioritize deep Google Cloud integration and SQL-based in-warehouse ML.