usmanskillsmd bigquery-setup
bigquery setup — production database configuration, optimization, and operations for bigquery.
Bigquery Setup
Run bigquery in production with confidence — from schema design to disaster recovery.
Overview
This skill covers bigquery setup for applications that need reliable, performant data storage. It handles the full lifecycle from initial setup through scaling, optimization, and operational management.
Database issues are among the most costly problems in production systems — slow queries cause user-facing latency, data loss destroys trust, and scaling bottlenecks limit growth. This skill addresses all three with proven patterns from high-traffic production systems.
The skill produces configurations, queries, and operational procedures that have been battle-tested at scale. It emphasizes data integrity, query performance, and operational simplicity.
When to Use
Activate this skill when the user needs to:
Set up a new bigquery instance or cluster
Optimize slow database queries or operations
Design schemas for new features or applications
Configure replication, backups, or disaster recovery
Migrate data between databases or versions
Debug production database performance issues
Core Capabilities
Schema Design
Creates normalized, efficient database schemas with proper indexing strategies, constraint definitions, and migration scripts. Balances normalization with query performance.
Query Optimization
Analyzes and rewrites slow queries using EXPLAIN plans, index recommendations, and query restructuring. Identifies N+1 problems, missing indexes, and suboptimal join patterns.
Operations & Monitoring
Configures monitoring dashboards, alerting thresholds, and automated health checks. Sets up backup schedules, retention policies, and recovery procedures.
Scaling Strategies
Implements read replicas, connection pooling, sharding strategies, and caching layers. Provides capacity planning guidance based on growth projections.
Example Prompts
Users might ask:
"Design a schema for the e-commerce product catalog"
"Optimize the slow queries in the analytics dashboard"
"Set up bigquery replication with automatic failover"
"Create a migration script to add the new user preferences table"
"Configure bigquery backup and point-in-time recovery"
Configuration
ParameterDefaultDescription
connectionPool20Maximum database connections
queryTimeout30sMaximum query execution time
backupSchedule0 2 * * *Cron schedule for automated backups
slowQueryLogtrueLog queries exceeding threshold
Best Practices
Always use migrations — Never modify production schemas manually — use versioned migration scripts that can be reviewed and rolled back
Index based on query patterns — Create indexes that match your actual query WHERE/ORDER BY/JOIN patterns, not arbitrary columns
Monitor connection usage — Connection exhaustion is a common cause of production outages — monitor and alert on pool usage
Test with production-like data — Performance characteristics change dramatically with data volume — always test with realistic dataset sizes
Plan for failure — Assume every component will fail — implement backups, replicas, and automated recovery
Common Pitfalls
⚠️ Adding indexes without analyzing query patterns — unused indexes waste storage and slow writes
⚠️ Not setting query timeouts — a single runaway query can take down the entire database
⚠️ Ignoring connection pool limits — leads to connection exhaustion under load
⚠️ Testing with small datasets — queries that are fast with 100 rows may be catastrophically slow with 10 million
Output Format
Generates SQL scripts, configuration files, migration files, and operational runbooks. Includes inline comments explaining design decisions and performance implications.
Prerequisites
bigquery server installed or cloud instance provisioned
Database client/driver for your application language