Best Practices for Storing Data in BigQuery
summary
Claim: changing table structure slashes query bytes without touching SQL — examples show a query going from 3.7 GB → 11 MB and another from 3.5 GB → 200 KB by adding partitioning and clustering, native JSON columns shrink storage and speed distinct counts, long‑term storage drops cost by 50% after 90 days, and large migrations handle 6 billion events/day; recommends time partitioning, partition+cluster ordering, automatic expiration, labels for billing and GDPR, and Terraform for reproducible BigQuery infra.
AI-written summary · about
chapters (13)
- 0:00Data organization in BigQuery
- 1:30Table structure for cost and performance
- 5:25Partitioning by date or numeric columns
- 7:50Partitioning by ingestion time vs column
- 12:08Clustering for faster queries
- 16:29Partitioning and clustering
- 17:11When to use sharding
- 18:39Native JSON columns
- 21:19Reducing storage costs
- 24:33Long-term storage pricing
- 25:35Labeling for monitoring and compliance
- 28:48Managing BigQuery with Terraform
- 31:38Best practices summary
related talks
09mStorage: managing billions of objects
27mStorage and analytics
26mBig Data Is Modern Data
30mPerformance: Tips & Tricks to Optimize the Speed of Your Mission-Critical Database
50mBeyond the How: Why Table Partitioning Truly Matters
38mShaving off bytes at any scale: Space savings in various subsystems of PostgreSQL
54mReducing Costs and Improving Performance With Data Modeling in Postgres
40mBest Practices and Optimization with the Spark Engine