bigquerylisted

# BigQuery CLI Skill This skill provides comprehensive guidance on using the BigQuery CLI (`bq`) for querying and inspecting data in Monzo's BigQuery projects. ## Core Principles 1. **Always specify the project explicitly** using `--project_id=PROJECT_NAME` 2. **Always use Standard SQL** with `--use_legacy_sql=false` 3. **Respect data sensitivity** - avoid querying actual content from sensitive tables 4. **Use INFORMATION_SCHEMA** for metadata queries (schemas, columns, tables) ## Common Query Patterns ### 1. Check Table Schema (INFORMATION_SCHEMA) Use this to inspect column names, types, and structure **without accessing sensitive data**: ```bash bq query --project_id=monzo-analytics --use_legacy_sql=false \ "SELECT column_name, data_type, is_nullable FROM \`monzo-analytics.DATASET_NAME.INFORMATION_SCHEMA.COLUMNS\` WHERE table_name = 'TABLE_NAME' ORDER BY ordinal_position" ``` **Examples:** ```bash # Check dims dataset table schema bq query --project_id=monzo-analytics --use_legacy_sql=false \ "SELECT column_name, data_type FROM \`monzo-analytics.dims.INFORMATION_SCHEMA.COLUMNS\` WHERE table_name = 'vulnerable_customer_logs_dim' ORDER BY ordinal_position" # Check prod dataset table schema bq query --project_id=monzo-analytics --use_legacy_sql=false \ "SELECT column_name, data_type FROM \`monzo-analytics.prod.INFORMATION_SCHEMA.COLUMNS\` WHERE table_name = 'transactions' ORDER BY ordinal_position" ``` ### 2. Count Rows (Safe for Sensitive Tables