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:

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:

# 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)

Use

COUNT(*)

to check table size without exposing data:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT COUNT(*) as row_count FROM \`monzo-analytics.DATASET.TABLE_NAME\`"

Example:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT COUNT(*) as row_count FROM \`monzo-analytics.dims.vulnerable_customer_logs_dim\`"

3. List All Tables in a Dataset

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT table_name, table_type
   FROM \`monzo-analytics.DATASET_NAME.INFORMATION_SCHEMA.TABLES\`
   ORDER BY table_name"

Example:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT table_name FROM \`monzo-analytics.dims.INFORMATION_SCHEMA.TABLES\`
   ORDER BY table_name"

4. Export Schema to File

Useful for programmatic processing of table schemas:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  --format=csv --quiet \
  "SELECT column_name FROM \`monzo-analytics.DATASET.INFORMATION_SCHEMA.COLUMNS\`
   WHERE table_name = 'TABLE_NAME' ORDER BY ordinal_position" \
  | tail -n +2 > /tmp/columns.txt

Example:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  --format=csv --quiet \
  "SELECT column_name FROM \`monzo-analytics.dims.INFORMATION_SCHEMA.COLUMNS\`
   WHERE table_name = 'vulnerable_customer_logs_dim' ORDER BY ordinal_position" \
  | tail -n +2 > /tmp/columns.txt

5. Check Table Metadata

Get table creation time, size, and other metadata:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT
     table_name,
     creation_time,
     ROUND(size_bytes/1024/1024/1024, 2) as size_gb,
     row_count
   FROM \`monzo-analytics.DATASET_NAME.INFORMATION_SCHEMA.TABLES\`
   WHERE table_name = 'TABLE_NAME'"

6. Find Tables by Pattern

Search for tables matching a naming pattern:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT table_name
   FROM \`monzo-analytics.DATASET_NAME.INFORMATION_SCHEMA.TABLES\`
   WHERE table_name LIKE '%PATTERN%'
   ORDER BY table_name"

Example:

# Find all customer-related tables
bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT table_name FROM \`monzo-analytics.dims.INFORMATION_SCHEMA.TABLES\`
   WHERE table_name LIKE '%customer%' ORDER BY table_name"

7. Get Detailed Column Information

Get comprehensive column metadata including descriptions:

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT
     column_name,
     data_type,
     is_nullable,
     is_partitioning_column
   FROM \`monzo-analytics.DATASET.INFORMATION_SCHEMA.COLUMNS\`
   WHERE table_name = 'TABLE_NAME'
   ORDER BY ordinal_position"

8. Sample Data (Non-Sensitive Tables Only)

⚠️ WARNING: Only use this on non-sensitive tables. Never query actual content from people/staff/PII tables.

bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT * FROM \`monzo-analytics.DATASET.TABLE_NAME\` LIMIT 10"

Output Formatting Options

Control how results are displayed:

# CSV format
--format=csv

# JSON format
--format=json

# Pretty table format (default)
--format=prettyjson

# Quiet mode (no status messages)
--quiet

# Maximum rows to return
--max_rows=100

Common Projects and Datasets

Main Analytics Projects

• monzo-analytics

  - Main analytics warehouse

• monzo-analytics-v2

  - New OOM architecture models

• monzo-analytics-pii

  - PII-containing data (use with caution)

• sanitized-events-prod

  - Sanitised event data

• raw-analytics-events-prod

  - Raw event data

Common Datasets

• dims

  - Dimension tables

• prod

  - Production tables

• lending

  - Lending-specific tables

• slurpee

  - Slurpee data

Data Sensitivity Guidelines

✅ SAFE Operations (Always Allowed)

1. INFORMATION_SCHEMA queries - These only return metadata, not actual data
2. COUNT(*) queries - These only return row counts
3. Schema inspection - Column names, types, table structure

⚠️ RESTRICTED Operations (Use with Caution)

1. Querying actual content from:

   • People/staff data tables
   • PII-containing tables
   • Customer financial data
   • Authentication/security tables

2. When in doubt:

   • Stick to INFORMATION_SCHEMA queries
   • Use COUNT(*) to verify table exists
   • Ask the user before querying actual content

🚫 NEVER Do This

• Query actual rows from

  people

  ,

  staff

  ,

  hibob

  tables
• Export PII data to local files
• Query authentication credentials or tokens
• Access customer financial details without explicit permission

Error Handling

Common Errors and Solutions

Error: "Not found: Table"

# Solution: Check the table exists first
bq query --project_id=monzo-analytics --use_legacy_sql=false \
  "SELECT table_name FROM \`monzo-analytics.DATASET.INFORMATION_SCHEMA.TABLES\`
   WHERE table_name LIKE '%SEARCH_TERM%'"

Error: "Access Denied"

# Solution: You may not have permissions for that project/dataset
# Try a different project or ask the user about access

Error: "Syntax error"

# Solution: Ensure you're using Standard SQL (--use_legacy_sql=false)
# Check backtick usage around project.dataset.table identifiers

Best Practices

1. Always use fully-qualified table names with backticks:

   `project-id.dataset.table`
   

2. Use LIMIT for exploratory queries to avoid large result sets:

   SELECT * FROM `project.dataset.table` LIMIT 10
   

3. Check row counts before running expensive queries:

   # First check size
   bq query --project_id=monzo-analytics --use_legacy_sql=false \
     "SELECT COUNT(*) FROM \`project.dataset.table\`"
   
   # Then run full query if reasonable
   

4. Use dry-run for cost estimation (for expensive queries):

   bq query --dry_run --use_legacy_sql=false "YOUR_QUERY_HERE"
   

5. Export large results to file:

   bq query --project_id=monzo-analytics --use_legacy_sql=false \
     --format=csv "YOUR_QUERY" > output.csv
   

Quick Reference Commands

# Schema check
bq query --project_id=PROJECT --use_legacy_sql=false \
  "SELECT column_name, data_type FROM \`PROJECT.DATASET.INFORMATION_SCHEMA.COLUMNS\`
   WHERE table_name = 'TABLE' ORDER BY ordinal_position"

# Row count
bq query --project_id=PROJECT --use_legacy_sql=false \
  "SELECT COUNT(*) FROM \`PROJECT.DATASET.TABLE\`"

# List tables
bq query --project_id=PROJECT --use_legacy_sql=false \
  "SELECT table_name FROM \`PROJECT.DATASET.INFORMATION_SCHEMA.TABLES\`
   ORDER BY table_name"

# Table metadata
bq query --project_id=PROJECT --use_legacy_sql=false \
  "SELECT table_name, row_count, size_bytes
   FROM \`PROJECT.DATASET.INFORMATION_SCHEMA.TABLES\`
   WHERE table_name = 'TABLE'"

When to Use This Skill

Invoke this skill when you need to:

• Query BigQuery tables or datasets
• Inspect table schemas or column types
• Count rows or check table existence
• Export table metadata
• Verify data before running dbt models
• Investigate data issues or table structures
• Find tables by naming patterns

Integration with dbt Workflow

When working on dbt models in the analytics repository:

1. Before creating import models - Use BigQuery CLI to inspect source schemas
2. Before running dbt - Verify source tables exist and have expected structure
3. Debugging dbt failures - Query actual tables to understand data issues
4. Validating generators - Check that column types match between source and generator

Remember: Always respect data sensitivity guidelines and use INFORMATION_SCHEMA when possible.