Preparing and using data for analysis

Preparing data for visualisation

Connecting tools

• Looker (semantic LookML model, governed metrics, persistent derived tables, caching) and Looker Studio (free self-service dashboards) connect natively to BigQuery. Connected Sheets lets spreadsheet users pivot BigQuery data. Other BI tools (Tableau, Power BI) connect through JDBC/ODBC drivers or the BigQuery Storage Read API.
• Choose Looker for a governed, reusable semantic layer; Looker Studio for quick, light dashboards. Looker Studio reports can run with owner’s credentials (viewers need no dataset access) or viewer’s credentials (respects row and column security). Choose the one that matches the security requirement.
• BigQuery Studio has notebooks (Colab Enterprise), SQL, data canvas and Gemini assistance for analysts.

Precalculating and shaping data

• Pre-aggregate repeated heavy calculations into summary tables (Dataform incremental tables, scheduled queries) or materialized views. Dashboards hitting a small aggregate table scan fewer bytes and are faster than ones over raw events.
• Use star schema marts and a date dimension for BI. Derive fields once (buckets, flags, currency conversions) in the model, not in every dashboard.
• Partition and cluster marts on dashboard filters (date, region).

BigQuery features for BI

Troubleshooting slow or costly queries

Use the query execution graph and INFORMATION_SCHEMA.JOBS (look at total_bytes_billed, total_slot_ms, stage timing, bytes shuffled and spilled). Common fixes:

• Select only needed columns; avoid SELECT *. LIMIT doesn’t cut bytes scanned; filter on partitions instead.
• Filter early; filter on the partition column with constants (a filter that depends on a subquery may not prune partitions); filter on clustered columns.
• Join order and skew: put the largest table first, filter before the join, use GROUP BY before join when possible; skewed keys cause slot imbalance and spilling; fix with pre-aggregating, APPROX_COUNT_DISTINCT (HLL_COUNT) for large distinct counts, filtering outliers.
• Avoid cross joins and unintended many-to-many joins that explode rows; avoid ORDER BY on huge results without LIMIT; avoid JavaScript UDFs where SQL works; avoid self-joins where window functions do the job; use QUALIFY for dedup; avoid repeatedly transforming the same data (materialise).
• Anti-patterns: oversharded tables, many small DML statements (batch them with MERGE), WITH clauses used many times (re-evaluated; materialise to a temp table), SELECT DISTINCT everywhere.
• Check slot contention: queries queued or slow with a flat bytes count point to insufficient slots (reservation autoscaling, separate batch vs interactive). Dry run (--dry_run) estimates bytes without cost; maximum bytes billed blocks costly queries; custom quotas per user/project cap on-demand scan volume.
• Query insights/performance recommendations and Gemini suggestions highlight skew, spill and slot contention. Index and storage recommender (via Gemini/INFORMATION_SCHEMA) propose partitioning and clustering and materialised views.

Security for analysis: IAM, masking, DLP

• When a requirement says “analysts see all rows except restricted columns”, use policy tags. “Each region’s manager sees only its region” = row-level policy (or an authorized view with SESSION_USER()). “Share an aggregated result with another team” = authorized view. “Hide the value but keep the query working” = dynamic masking. “Find where PII is hiding” = DLP discovery.
• Data Access audit logs capture who queried what; combined with INFORMATION_SCHEMA.JOBS_BY_*.

Preparing data for AI and ML

Feature engineering and BigQuery ML

• BigQuery ML creates and runs models with SQL in place: CREATE MODEL for linear and logistic regression, boosted trees (XGBoost), random forest, deep neural networks, k-means, matrix factorisation, ARIMA_PLUS (time series), PCA, AutoML models, imported TensorFlow/ONNX, and remote models that call Vertex AI (Gemini, embeddings, hosted models, Natural Language, Vision). Functions: ML.EVALUATE, ML.PREDICT, ML.EXPLAIN_PREDICT, ML.FEATURE_IMPORTANCE, ML.TRAIN-time options such as data_split_method and hyperparameter tuning. TRANSFORM clause bakes preprocessing (scaling, bucketing, one-hot) into the model so serving applies the same logic: this avoids training-serving skew.
• Preparing training data: clean and deduplicate, handle nulls and outliers, encode categoricals, normalise, split train/validation/test without leakage (split by time for time series; by entity such as customer for grouped data; never random-split rows that share an entity), balance classes, document the data. Don’t use features not available at prediction time.
• Feature management: Vertex AI Feature Store (built on BigQuery tables; online serving with low latency, offline from BigQuery), BigQuery tables as an offline store with point-in-time correct joins. Dataflow or Dataproc for heavy preprocessing; TFX/TensorFlow Transform for consistent transforms; Vertex AI Pipelines orchestrates training.
• Serving data: batch prediction reads BigQuery and writes back; online prediction needs low-latency features (Bigtable, Feature Store, Memorystore). Make sure training data and serving data come from the same transformation code.
• Unstructured to ML: object tables let BigQuery models and remote functions process images, audio and PDFs stored in Cloud Storage; Document AI parses documents; ML.GENERATE_TEXT/AI.GENERATE extracts fields.

Embeddings and retrieval-augmented generation

1. Collect and parse documents (Cloud Storage, object tables, Document AI layout parser).
2. Chunk text into passages (size and overlap affect recall and cost), attach metadata (source, date, access labels).
3. Embed with ML.GENERATE_EMBEDDING (BigQuery, remote model to a Vertex AI embedding model) or the Vertex AI API; store vectors in BigQuery, AlloyDB (pgvector/ScaNN), Spanner, Cloud SQL, Firestore, or Vertex AI Vector Search.
4. Index: CREATE VECTOR INDEX (IVF or TreeAH) in BigQuery; query with VECTOR_SEARCH (distance types cosine, euclidean, dot product); use pre-filters with metadata and combine with search index text search for hybrid retrieval.
5. Generate: pass retrieved chunks to a model (ML.GENERATE_TEXT or Gemini API) to ground the answer; Vertex AI RAG Engine and Agentic/Grounding options manage this end to end.

• Keep access control consistent: retrieval must respect the same row-level security as the source data; refresh embeddings when documents change; evaluate retrieval quality (recall, relevance) and mask PII before embedding.

Sharing data

Rules and publishing datasets

• Define who can see what (IAM at dataset level, authorised views, row and column policies), how (copy vs live reference), how long (expiration, time-bound IAM Conditions) and under which terms (contracts, audit). Share live data without copying wherever possible: no duplicate storage, always current, revocable.
• Publishing options:

Analytics Hub (BigQuery sharing)

• A publisher creates a data exchange (container with permissions) and listings that reference a shared dataset (or a Pub/Sub topic, or a view). Subscribers subscribe and get a linked dataset in their project: a read-only live link, no data copy and no storage cost for the subscriber; they pay for their own query compute.
• Exchange types: private (limited to chosen users), public (listings may be discoverable by everyone, including Google-provided datasets), commercial listings via Google Cloud Marketplace. Data clean rooms let parties analyse combined data with analysis rules that block raw-row access (aggregation thresholds, join restrictions, privacy budgets).
• Providers can see usage metrics; they can remove a subscriber’s access. Linked datasets must be in the same region as the source; for cross-region you use dataset replication first. Authorised views are built into the shared dataset to restrict what is exposed.
• Choose Analytics Hub over copying (DTS, exports) or giving direct project access when sharing at scale, across organisations, with governance and without duplicating data.

Reports and visualisations

• Looker Studio sharing: link, group or domain sharing, scheduled email, embed, data credentials (owner vs viewer). Looker: content access via folders and roles, access filters and user attributes for row security, scheduled deliveries, embedding, API. Keep the source of truth in governed BigQuery models, not in per-user extracts.
• BigQuery sharing of results: save queries, share notebooks and data canvases; export results to Sheets; Connected Sheets with permissions of the viewer.