Preparing and Using Data for Analysis
Transform in the warehouse where possible
BigQuery SQL handles most preparation: ELT with scheduled queries, MERGE for upserts, and Dataform or dbt-style projects for versioned, testable transformations with assertions and dependency graphs. Materialized views accelerate repeated aggregations and refresh automatically, but they cannot read from the streaming buffer and have restricted SQL forms - they are not a substitute for a scheduled job when you need full transformation logic.
Serving and governing
Expose curated datasets through views rather than raw tables. Column-level security uses policy tags from a Dataplex taxonomy; row-level security uses row access policies; authorized views remain the pattern for sharing a filtered subset across datasets. Dataplex and Data Catalog provide discovery, lineage and quality checks. BI Engine gives sub-second dashboard latency inside BigQuery, and Looker Studio or Looker supplies the presentation layer. BigQuery ML keeps simple model training next to the data, and federated queries via BigQuery connections reach Cloud SQL or Spanner without copying.
Traps
- Views are not free: a view re-runs its SQL every time and inherits the cost of the underlying scan.
- A materialized view that is not used by a query does not refresh; staleness is expected.
- Policy tags require the taxonomy and fine-grained reader role, not just dataset access.
- Reserved slots give predictable cost; on-demand is per byte scanned, and SELECT * destroys it.
Rule of thumb
Model data in layers - raw, cleaned, curated - and let every consumer read only the curated layer, so schema changes never break dashboards.