{"id":4708,"date":"2025-08-21T10:55:46","date_gmt":"2025-08-21T10:55:46","guid":{"rendered":"https:\/\/uplatz.com\/blog\/?p=4708"},"modified":"2025-08-30T11:46:03","modified_gmt":"2025-08-30T11:46:03","slug":"bigquery-pocket-book","status":"publish","type":"post","link":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/","title":{"rendered":"BigQuery Pocket Book"},"content":{"rendered":"<p><!-- ############################################################ --><br \/>\n<!-- BigQuery Pocket Book \u2014 Uplatz (Single Column, ~60 Cards) --><\/p>\n<div style=\"margin: 16px 0;\">\n<style>\n  \/* Scope *\/<br \/>\n  .wp-bq-pb{font-family:Arial,Helvetica,sans-serif;max-width:980px;margin:0 auto;}<\/p>\n<p>  \/* Gradient header (balanced typography) *\/<br \/>\n  .wp-bq-pb .heading{<br \/>\n    background:linear-gradient(135deg,#0ea5e9,#6366f1);<br \/>\n    color:#ffffff;padding:22px;border-radius:18px;text-align:center;<br \/>\n    margin-bottom:26px;box-shadow:0 10px 24px rgba(0,0,0,.10);border:1px solid rgba(255,255,255,.22)<br \/>\n  }<br \/>\n  .wp-bq-pb .heading h2{margin:0;font-size:1.82rem;font-weight:800;letter-spacing:.2px;line-height:1.15}<br \/>\n  .wp-bq-pb .heading p{margin:8px 0 0;font-size:.96rem;opacity:.95}<\/p>\n<p>  \/* Section titles *\/<br \/>\n  .wp-bq-pb .section-title{<br \/>\n    margin:26px 0 14px;padding:12px 16px;background:#f8fafc;border-left:8px solid #2563eb;<br \/>\n    border-radius:12px;font-weight:800;color:#0f172a;font-size:1.02rem;<br \/>\n    box-shadow:0 2px 8px rgba(0,0,0,.05);border:1px solid #e2e8f0<br \/>\n  }<br \/>\n  \/* Colored first section (per request) *\/<br \/>\n  .wp-bq-pb .section-title.color-primary{<br \/>\n    background:linear-gradient(135deg,#e0f2fe,#ede9fe); \/* sky -> indigo *\/<br \/>\n    border-left-color:#0ea5e9;color:#0f172a<br \/>\n  }<\/p>\n<p>  \/* Cards (single column) *\/<br \/>\n  .wp-bq-pb .card{<br \/>\n    background:#fff;border-left:6px solid #2563eb;padding:16px;border-radius:14px;<br \/>\n    box-shadow:0 6px 14px rgba(0,0,0,.06);border:1px solid #e5e7eb;margin-bottom:16px;<br \/>\n    transition:transform .12s ease,box-shadow .12s ease<br \/>\n  }<br \/>\n  .wp-bq-pb .card:hover{transform:translateY(-2px);box-shadow:0 12px 22px rgba(0,0,0,.08)}<br \/>\n  .wp-bq-pb .card h3{margin:0 0 10px;font-size:1.08rem;color:#0f172a}<br \/>\n  .wp-bq-pb .card p{margin:0;font-size:.96rem;color:#334155;line-height:1.62}<\/p>\n<p>  \/* Color helpers *\/<br \/>\n  .bg-blue{border-left-color:#2563eb;background:#f0f9ff}<br \/>\n  .bg-green{border-left-color:#16a34a;background:#f0fdf4}<br \/>\n  .bg-amber{border-left-color:#f59e0b;background:#fffbeb}<br \/>\n  .bg-violet{border-left-color:#7c3aed;background:#f5f3ff}<br \/>\n  .bg-rose{border-left-color:#e11d48;background:#fff1f2}<br \/>\n  .bg-cyan{border-left-color:#0891b2;background:#ecfeff}<br \/>\n  .bg-indigo{border-left-color:#4f46e5;background:#eef2ff}<br \/>\n  .bg-emerald{border-left-color:#059669;background:#ecfdf5}<br \/>\n  .bg-slate{border-left-color:#334155;background:#f8fafc}<\/p>\n<p>  \/* Mono + code *\/<br \/>\n  .mono{font-family:ui-monospace,SFMono-Regular,Menlo,Consolas,monospace}<br \/>\n  .wp-bq-pb code{background:#f1f5f9;padding:0 4px;border-radius:4px;border:1px solid #e2e8f0}<br \/>\n  .wp-bq-pb pre{background:#f5f5f5;color:#111827;border:1px solid #e5e7eb;padding:12px;border-radius:10px;overflow:auto;font-size:.92rem;line-height:1.55}<\/p>\n<p>  .muted{color:#64748b}<br \/>\n  .tight ul{margin:0;padding-left:18px}<br \/>\n  .tight li{margin:4px 0}<br \/>\n  .q{font-weight:700}<br \/>\n<\/style>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-5064\" src=\"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery-1024x576.jpg\" alt=\"\" width=\"840\" height=\"473\" srcset=\"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery-1024x576.jpg 1024w, https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery-300x169.jpg 300w, https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery-768x432.jpg 768w, https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery.jpg 1280w\" sizes=\"auto, (max-width: 840px) 100vw, 840px\" \/><\/p>\n<div class=\"wp-bq-pb\">\n<div class=\"heading\">\n<h2>BigQuery Pocket Book \u2014 Uplatz<\/h2>\n<p>~60 deep-dive flashcards \u2022 Single column \u2022 Storage &amp; Tables \u2022 SQL &amp; Arrays \u2022 Performance &amp; Cost \u2022 Security \u2022 Ingestion \u2022 Ops \u2022 Interview Q&amp;A<\/p>\n<p class=\"muted\">Cheat-friendly explanations \u2022 Readable SQL\/CLI snippets \u2022 Production-oriented tips<\/p>\n<\/div>\n<p><!-- ===================== SECTION 1 ===================== --><\/p>\n<div class=\"section-title color-primary\">Section 1 \u2014 Fundamentals<\/div>\n<div class=\"card bg-blue\">\n<h3>1) What is BigQuery?<\/h3>\n<p>Serverless, highly parallel data warehouse on Google Cloud. Separate storage\/compute, ANSI SQL, petabyte scale, pay per data processed or via slot capacity.<\/p>\n<pre><code class=\"mono\"># CLI auth &amp; default project\r\ngcloud auth login\r\ngcloud config set project &lt;PROJECT_ID&gt;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-green\">\n<h3>2) Core Objects<\/h3>\n<p><b>Project<\/b> \u2192 <b>Dataset (schema)<\/b> \u2192 <b>Table\/View<\/b>. Regions: multi\/regions; keep data close to compute and sources.<\/p>\n<\/div>\n<div class=\"card bg-amber\">\n<h3>3) Pricing Basics<\/h3>\n<p>Storage charged by TB\/month; queries charged by TB scanned (on-demand) or covered by slot reservations (capacity). Use <code>DRY RUN<\/code> to estimate bytes.<\/p>\n<pre><code class=\"mono\">-- Estimate cost before running\r\nEXPLAIN SELECT * FROM `proj.ds.table`;\r\n-- or\r\nDECLARE _DRY BOOL DEFAULT TRUE;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-violet\">\n<h3>4) SQL Dialect<\/h3>\n<p>Standard SQL (default). Supports arrays, structs, JSON type, window functions, geospatial, ML, and procedural scripting.<\/p>\n<\/div>\n<div class=\"card bg-rose\">\n<h3>5) When to Use BigQuery?<\/h3>\n<p>Analytics and BI on large datasets, ELT on lake data, semi-structured JSON logs, joins across massive tables, and ML close to data.<\/p>\n<\/div>\n<div class=\"card bg-cyan\">\n<h3>6) Regions &amp; Storage<\/h3>\n<p>Pick a single region or multi-region (e.g., US\/EU). Data residency matters for compliance and latency to GCS\/GA4\/Dataflow.<\/p>\n<\/div>\n<div class=\"card bg-indigo\">\n<h3>7) Projects &amp; Datasets<\/h3>\n<p>One dataset per domain or environment. Name with clarity: <code>analytics_raw<\/code>, <code>analytics_curated<\/code>, <code>bi<\/code>.<\/p>\n<pre><code class=\"mono\">bq mk --dataset --location=US myproj:analytics_raw<\/code><\/pre>\n<\/div>\n<div class=\"card bg-emerald\">\n<h3>8) Time Travel &amp; Snapshots<\/h3>\n<p>Query previous table versions (system time) for accidental deletes\/overwrites.<\/p>\n<pre><code class=\"mono\">SELECT * FROM `proj.ds.sales`\r\nFOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 24 HOUR);<\/code><\/pre>\n<\/div>\n<div class=\"card bg-slate\">\n<h3>9) Query Caching<\/h3>\n<p>Identical queries over same data may return from cache at no cost. Disable if you need fresh reads.<\/p>\n<\/div>\n<div class=\"card bg-blue\">\n<h3>10) Console &amp; CLI<\/h3>\n<p>Use BigQuery Studio (web) or <code>bq<\/code> CLI and client libraries. Prefer versioned SQL files in Git for repeatability.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 2 ===================== --><\/p>\n<div class=\"section-title\">Section 2 \u2014 Storage, Tables &amp; Layout<\/div>\n<div class=\"card bg-green\">\n<h3>11) Create Dataset\/Table<\/h3>\n<pre><code class=\"mono\">-- DDL\r\nCREATE SCHEMA IF NOT EXISTS `proj.analytics`;\r\nCREATE TABLE `proj.analytics.events`(\r\n  event_time TIMESTAMP,\r\n  user_id STRING,\r\n  attrs JSON\r\n);<\/code><\/pre>\n<\/div>\n<div class=\"card bg-amber\">\n<h3>12) Partitioning<\/h3>\n<p>Partition by DATE\/TIMESTAMP column or by ingestion time. Prunes bytes scanned and speeds queries.<\/p>\n<pre><code class=\"mono\">CREATE TABLE `proj.analytics.events_p`\r\nPARTITION BY DATE(event_time) AS SELECT * FROM `proj.analytics.events`;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-violet\">\n<h3>13) Clustering<\/h3>\n<p>Cluster up to 4 columns (e.g., <code>user_id<\/code>, <code>country<\/code>) to improve predicate\/aggregation performance.<\/p>\n<pre><code class=\"mono\">CREATE TABLE `proj.analytics.events_c`\r\nPARTITION BY DATE(event_time)\r\nCLUSTER BY user_id, country AS SELECT * FROM `proj.analytics.events`;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-rose\">\n<h3>14) Ingestion-Time Partitioning<\/h3>\n<p>Ideal for logs; use pseudo column for decorators and cost pruning.<\/p>\n<pre><code class=\"mono\">CREATE TABLE `proj.logs.app`\r\nPARTITION BY _PARTITIONDATE AS SELECT * FROM `proj.tmp.src`;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-cyan\">\n<h3>15) Decorators<\/h3>\n<p>Access a partition or snapshot with decorators.<\/p>\n<pre><code class=\"mono\">SELECT COUNT(*) FROM `proj.ds.tbl$20250820`;\r\nSELECT * FROM `proj.ds.tbl@-3600000`; -- 1 hour ago<\/code><\/pre>\n<\/div>\n<div class=\"card bg-indigo\">\n<h3>16) External Tables &amp; BigLake<\/h3>\n<p>Query Parquet\/CSV\/JSON in GCS without loading; add governance via BigLake for consistent security across lake+warehouse.<\/p>\n<pre><code class=\"mono\">CREATE EXTERNAL TABLE `proj.ext.web`\r\nWITH CONNECTION `proj.us.gcs`\r\nOPTIONS ( uris=['gs:\/\/bucket\/curated\/web\/**\/*.parquet'] );<\/code><\/pre>\n<\/div>\n<div class=\"card bg-emerald\">\n<h3>17) Schemas &amp; Types<\/h3>\n<p>Prefer typed columns over <code>STRING<\/code>; use <code>NUMERIC\/BIGNUMERIC<\/code> for money; <code>JSON<\/code> for semi-structured with functions.<\/p>\n<\/div>\n<div class=\"card bg-slate\">\n<h3>18) Views &amp; Materialized Views<\/h3>\n<p>Views = virtual SQL. Materialized views = precomputed and auto-refreshed for speed\/cost reduction.<\/p>\n<\/div>\n<div class=\"card bg-blue\">\n<h3>19) Table Lifecycle<\/h3>\n<p>Set default expiration per dataset\/table to control storage cost; use labels for ownership and cleanup.<\/p>\n<\/div>\n<div class=\"card bg-green\">\n<h3>20) Sharded Tables (legacy)<\/h3>\n<p>Prefer partitioned tables over sharded names like <code>events_20250820<\/code>. If you inherit sharded, migrate to partitioned for performance.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 3 ===================== --><\/p>\n<div class=\"section-title\">Section 3 \u2014 Querying, Arrays\/Structs &amp; SQL Features<\/div>\n<div class=\"card bg-amber\">\n<h3>21) Arrays &amp; UNNEST<\/h3>\n<p>Arrays require <code>UNNEST<\/code> to flatten; re-aggregate with <code>ARRAY_AGG<\/code>.<\/p>\n<pre><code class=\"mono\">SELECT user_id, item FROM `proj.ds.cart`,\r\nUNNEST(items) AS item;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-violet\">\n<h3>22) Structs<\/h3>\n<p>Nested records model JSON-like objects with types.<\/p>\n<pre><code class=\"mono\">SELECT address.city, address.zip FROM `proj.ds.customers`;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-rose\">\n<h3>23) JSON Type<\/h3>\n<p>Use <code>JSON_VALUE<\/code>\/<code>JSON_QUERY<\/code>\/<code>JSON_EXTRACT<\/code> to access fields.<\/p>\n<pre><code class=\"mono\">SELECT JSON_VALUE(attrs,'$.device.os') AS os FROM `proj.analytics.events`;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-cyan\">\n<h3>24) Window Functions<\/h3>\n<p>Ranking, moving averages, percentiles.<\/p>\n<pre><code class=\"mono\">SELECT user_id, amount,\r\nAVG(amount) OVER (PARTITION BY user_id ORDER BY ts ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg7\r\nFROM `proj.sales.txn`;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-indigo\">\n<h3>25) Geography<\/h3>\n<p>Spatial analysis with <code>GEOGRAPHY<\/code> and <code>ST_*<\/code> functions.<\/p>\n<pre><code class=\"mono\">SELECT ST_DISTANCE(h.geom, a.geom) AS m FROM homes h, amenities a;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-emerald\">\n<h3>26) Time &amp; Date<\/h3>\n<p>Use <code>DATE<\/code>\/<code>TIMESTAMP<\/code>\/<code>DATETIME<\/code> types and functions; convert time zones explicitly.<\/p>\n<\/div>\n<div class=\"card bg-slate\">\n<h3>27) DDL\/DML<\/h3>\n<pre><code class=\"mono\">ALTER TABLE `proj.ds.t` ADD COLUMN country STRING;\r\nMERGE `proj.ds.t` T\r\nUSING `proj.ds.stage` S ON S.id=T.id\r\nWHEN MATCHED THEN UPDATE SET amount=S.amount\r\nWHEN NOT MATCHED THEN INSERT (id,amount) VALUES(S.id,S.amount);<\/code><\/pre>\n<\/div>\n<div class=\"card bg-blue\">\n<h3>28) Scripting<\/h3>\n<p>Multi-statement scripts with variables and control flow.<\/p>\n<pre><code class=\"mono\">DECLARE d DATE DEFAULT CURRENT_DATE();\r\nCREATE TEMP TABLE tmp AS SELECT * FROM `proj.ds.t` WHERE date = d;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-green\">\n<h3>29) UDFs<\/h3>\n<p>SQL or JavaScript UDFs for reusable logic.<\/p>\n<pre><code class=\"mono\">CREATE TEMP FUNCTION doubleIt(x INT64) AS (x*2);\r\nSELECT doubleIt(21);<\/code><\/pre>\n<\/div>\n<div class=\"card bg-amber\">\n<h3>30) Approximate Functions<\/h3>\n<p><code>APPROX_COUNT_DISTINCT<\/code>, <code>APPROX_QUANTILES<\/code> reduce cost\/latency for large scans.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 4 ===================== --><\/p>\n<div class=\"section-title\">Section 4 \u2014 Performance, Bytes Scanned &amp; Cost Control<\/div>\n<div class=\"card bg-violet\">\n<h3>31) Prune Early<\/h3>\n<p>Always filter on partition column (<code>WHERE date BETWEEN ...<\/code>) and clustered columns; select only needed fields.<\/p>\n<\/div>\n<div class=\"card bg-rose\">\n<h3>32) Dry Runs<\/h3>\n<p>Estimate bytes scanned to avoid surprises.<\/p>\n<pre><code class=\"mono\">-- In UI: \"Query settings\" &gt; \"Dry run\"\r\n-- CLI: bq query --use_legacy_sql=false --dry_run --format=prettyjson \"$(cat query.sql)\"<\/code><\/pre>\n<\/div>\n<div class=\"card bg-cyan\">\n<h3>33) Materialized Views &amp; Caching<\/h3>\n<p>Precompute heavy joins\/aggregations; BigQuery rewrites queries to use MVs automatically.<\/p>\n<\/div>\n<div class=\"card bg-indigo\">\n<h3>34) Result Cache vs Temp Tables<\/h3>\n<p>Cache = free if available; otherwise <code>CREATE TEMP TABLE<\/code> to reuse intermediary results across steps.<\/p>\n<\/div>\n<div class=\"card bg-emerald\">\n<h3>35) Denormalize (carefully)<\/h3>\n<p>Store nested, repeated fields (arrays\/structs) to reduce joins; keep file\/table granularity manageable.<\/p>\n<\/div>\n<div class=\"card bg-slate\">\n<h3>36) Slot Reservations (Capacity)<\/h3>\n<p>Commit slots for predictable performance; flex\/short-term options exist. Assign reservations to projects\/folders.<\/p>\n<\/div>\n<div class=\"card bg-blue\">\n<h3>37) On-Demand Guardrails<\/h3>\n<p>Set maximum bytes billed per query to cap costs.<\/p>\n<pre><code class=\"mono\">-- UI setting or:\r\nDECLARE _MAX_BYTES_BILLED INT64 DEFAULT 1e12; -- 1 TB<\/code><\/pre>\n<\/div>\n<div class=\"card bg-green\">\n<h3>38) Partition Evolution &amp; Clustering<\/h3>\n<p>Recluster and optimize partitions automatically over time; monitor with <code>INFORMATION_SCHEMA.PARTITIONS<\/code>.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 5 ===================== --><\/p>\n<div class=\"section-title\">Section 5 \u2014 Ingestion, ETL\/ELT &amp; Streaming<\/div>\n<div class=\"card bg-amber\">\n<h3>39) Batch Load from GCS<\/h3>\n<p>Fastest\/cheapest for large files.<\/p>\n<pre><code class=\"mono\">bq load --source_format=PARQUET proj:analytics.events gs:\/\/bucket\/curated\/events\/*.parquet<\/code><\/pre>\n<\/div>\n<div class=\"card bg-violet\">\n<h3>40) Storage Write API (Streaming)<\/h3>\n<p>High-throughput, low-latency inserts with exactly-once semantics and schema evolution.<\/p>\n<\/div>\n<div class=\"card bg-rose\">\n<h3>41) Pub\/Sub \u2192 Dataflow \u2192 BigQuery<\/h3>\n<p>Managed streaming pipeline with windowing, late data handling, and dead-letter queues.<\/p>\n<\/div>\n<div class=\"card bg-cyan\">\n<h3>42) Dataproc\/Spark<\/h3>\n<p>Use Spark BigQuery connector to write Parquet\/ORC results directly into BigQuery tables.<\/p>\n<\/div>\n<div class=\"card bg-indigo\">\n<h3>43) Dataform \/ Cloud Composer<\/h3>\n<p>SQL-first transformations (Dataform) and Airflow orchestration (Composer) for ELT DAGs with tests and dependencies.<\/p>\n<\/div>\n<div class=\"card bg-emerald\">\n<h3>44) MERGE &amp; Change Data<\/h3>\n<p>Upsert CDC streams into partitioned\/clustering tables to keep dashboards current at lower cost.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 6 ===================== --><\/p>\n<div class=\"section-title\">Section 6 \u2014 Security, Governance &amp; Compliance<\/div>\n<div class=\"card bg-slate\">\n<h3>45) IAM Roles<\/h3>\n<p>Assign least privilege: <code>bigquery.dataViewer<\/code>, <code>dataEditor<\/code>, <code>dataOwner<\/code>, <code>jobUser<\/code>. Prefer groups over individuals.<\/p>\n<\/div>\n<div class=\"card bg-blue\">\n<h3>46) Row-Level Security<\/h3>\n<p>Policies filter rows based on predicates.<\/p>\n<pre><code class=\"mono\">CREATE ROW ACCESS POLICY region_filter\r\nON `proj.bi.customers`\r\nGRANT TO (\"group:emea-analysts@company.com\")\r\nFILTER USING (region = \"EMEA\");<\/code><\/pre>\n<\/div>\n<div class=\"card bg-green\">\n<h3>47) Column-Level Security<\/h3>\n<p>Policy tags via Data Catalog; grant access to tags rather than columns directly (mask PII).<\/p>\n<\/div>\n<div class=\"card bg-amber\">\n<h3>48) Encryption<\/h3>\n<p>AES-256 at rest by default; use CMEK for customer-managed keys on sensitive datasets; enforce with org policies.<\/p>\n<\/div>\n<div class=\"card bg-violet\">\n<h3>49) Audit &amp; Access Logs<\/h3>\n<p>Route Admin\/Data Access logs to Log Analytics\/BigQuery for inspections. Monitor for exfiltration patterns.<\/p>\n<\/div>\n<div class=\"card bg-rose\">\n<h3>50) Data Quality &amp; Contracts<\/h3>\n<p>Validate schemas, nullability, and ranges using Dataform tests or custom INFORMATION_SCHEMA checks before publishing to BI.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 7 ===================== --><\/p>\n<div class=\"section-title\">Section 7 \u2014 BI, ML &amp; Ecosystem<\/div>\n<div class=\"card bg-cyan\">\n<h3>51) BI Engine<\/h3>\n<p>In-memory acceleration for BI tools (Looker\/Looker Studio). Cache hot aggregates for sub-second dashboards.<\/p>\n<\/div>\n<div class=\"card bg-indigo\">\n<h3>52) BigQuery ML (BQML)<\/h3>\n<p>Train models in SQL (logistic\/linear reg, kmeans, time series, XGBoost, some deep models); predictions via <code>ML.PREDICT<\/code>.<\/p>\n<pre><code class=\"mono\">CREATE OR REPLACE MODEL `proj.ml.churn`\r\nOPTIONS(model_type='logistic_reg') AS\r\nSELECT label, f1,f2,f3 FROM `proj.ds.train`;<\/code><\/pre>\n<\/div>\n<div class=\"card bg-emerald\">\n<h3>53) Federated Queries<\/h3>\n<p>External connections to Cloud SQL\/Spanner and others for near-real-time analytics without full ingestion (mind quotas\/latency).<\/p>\n<\/div>\n<div class=\"card bg-slate\">\n<h3>54) Exports &amp; Interop<\/h3>\n<p>Export tables\/partitions to GCS (Parquet\/Avro\/CSV) for sharing with Spark\/Dataproc\/Vertex AI.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 8 ===================== --><\/p>\n<div class=\"section-title\">Section 8 \u2014 Operations, Monitoring &amp; Reliability<\/div>\n<div class=\"card bg-blue\">\n<h3>55) INFORMATION_SCHEMA<\/h3>\n<p>Introspect tables, columns, partitions, slots, and query history for optimization.<\/p>\n<pre><code class=\"mono\">SELECT * FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT\r\nWHERE creation_time &gt; TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);<\/code><\/pre>\n<\/div>\n<div class=\"card bg-green\">\n<h3>56) Alerts &amp; Quotas<\/h3>\n<p>Set scan\/slot usage alerts; protect against runaway queries; set org policies for location, CMEK, and cross-region transfers.<\/p>\n<\/div>\n<div class=\"card bg-amber\">\n<h3>57) Reliability &amp; SLIs<\/h3>\n<p>Track p95 query latency, bytes scanned\/user, storage growth, MV staleness, and failed jobs. Keep runbooks for hot paths.<\/p>\n<\/div>\n<p><!-- ===================== SECTION 9 ===================== --><\/p>\n<div class=\"section-title\">Section 9 \u2014 Patterns &amp; Interview Q&amp;A<\/div>\n<div class=\"card bg-violet\">\n<h3>58) Pattern \u2014 Medallion Lakehouse<\/h3>\n<p>Land raw (Bronze) in GCS \u2192 curate (Silver) with ELT in BQ \u2192 gold marts with MVs\/partitioned tables for BI.<\/p>\n<\/div>\n<div class=\"card bg-rose\">\n<h3>59) Pattern \u2014 Cost-Aware BI<\/h3>\n<p>Partition + cluster fact tables, MVs for heavy joins, BI Engine cache, guardrails on bytes billed, and query templates with required date filters.<\/p>\n<\/div>\n<div class=\"card bg-cyan\">\n<h3>60) Interview Q&amp;A \u2014 Practical<\/h3>\n<p><b>Partition vs Cluster?<\/b> Partition reduces scanned bytes by date\/ingestion; clustering sorts data within partitions for predicates\/aggregations.<\/p>\n<p><b>On-demand vs Slots?<\/b> On-demand = per TB scanned; Slots = reserved capacity for predictable performance.<\/p>\n<p><b>Speed up a slow query?<\/b> Add partition filters, cluster keys, avoid SELECT *, precompute with MVs, and check skew in <code>INFORMATION_SCHEMA<\/code>.<\/p>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>BigQuery Pocket Book \u2014 Uplatz ~60 deep-dive flashcards \u2022 Single column \u2022 Storage &amp; Tables \u2022 SQL &amp; Arrays \u2022 Performance &amp; Cost \u2022 Security \u2022 Ingestion \u2022 Ops \u2022 <span class=\"readmore\"><a href=\"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/\">Read More &#8230;<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2530,2462],"tags":[],"class_list":["post-4708","post","type-post","status-publish","format-standard","hentry","category-bigquery","category-pocket-book"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>BigQuery Pocket Book | Uplatz Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"BigQuery Pocket Book | Uplatz Blog\" \/>\n<meta property=\"og:description\" content=\"BigQuery Pocket Book \u2014 Uplatz ~60 deep-dive flashcards \u2022 Single column \u2022 Storage &amp; Tables \u2022 SQL &amp; Arrays \u2022 Performance &amp; Cost \u2022 Security \u2022 Ingestion \u2022 Ops \u2022 Read More ...\" \/>\n<meta property=\"og:url\" content=\"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/\" \/>\n<meta property=\"og:site_name\" content=\"Uplatz Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/Uplatz-1077816825610769\/\" \/>\n<meta property=\"article:published_time\" content=\"2025-08-21T10:55:46+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-08-30T11:46:03+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"720\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"uplatzblog\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@uplatz_global\" \/>\n<meta name=\"twitter:site\" content=\"@uplatz_global\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"uplatzblog\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/\"},\"author\":{\"name\":\"uplatzblog\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#\\\/schema\\\/person\\\/8ecae69a21d0757bdb2f776e67d2645e\"},\"headline\":\"BigQuery Pocket Book\",\"datePublished\":\"2025-08-21T10:55:46+00:00\",\"dateModified\":\"2025-08-30T11:46:03+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/\"},\"wordCount\":1014,\"publisher\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/08\\\/BigQuery-1024x576.jpg\",\"articleSection\":[\"BigQuery\",\"Pocket Book\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/\",\"url\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/\",\"name\":\"BigQuery Pocket Book | Uplatz Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/08\\\/BigQuery-1024x576.jpg\",\"datePublished\":\"2025-08-21T10:55:46+00:00\",\"dateModified\":\"2025-08-30T11:46:03+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/#primaryimage\",\"url\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/08\\\/BigQuery.jpg\",\"contentUrl\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/wp-content\\\/uploads\\\/2025\\\/08\\\/BigQuery.jpg\",\"width\":1280,\"height\":720},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/bigquery-pocket-book\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"BigQuery Pocket Book\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/\",\"name\":\"Uplatz Blog\",\"description\":\"Uplatz is a global IT Training &amp; Consulting company\",\"publisher\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#organization\",\"name\":\"uplatz.com\",\"url\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/11\\\/Uplatz-Logo-Copy-2.png\",\"contentUrl\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/wp-content\\\/uploads\\\/2016\\\/11\\\/Uplatz-Logo-Copy-2.png\",\"width\":1280,\"height\":800,\"caption\":\"uplatz.com\"},\"image\":{\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/Uplatz-1077816825610769\\\/\",\"https:\\\/\\\/x.com\\\/uplatz_global\",\"https:\\\/\\\/www.instagram.com\\\/\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/7956715?trk=tyah&amp;amp;amp;amp;trkInfo=clickedVertical:company,clickedEntityId:7956715,idx:1-1-1,tarId:1464353969447,tas:uplatz\"]},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/uplatz.com\\\/blog\\\/#\\\/schema\\\/person\\\/8ecae69a21d0757bdb2f776e67d2645e\",\"name\":\"uplatzblog\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/7f814c72279199f59ded4418a8653ad15f5f8904ac75e025a4e2abe24d58fa5d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/7f814c72279199f59ded4418a8653ad15f5f8904ac75e025a4e2abe24d58fa5d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/7f814c72279199f59ded4418a8653ad15f5f8904ac75e025a4e2abe24d58fa5d?s=96&d=mm&r=g\",\"caption\":\"uplatzblog\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"BigQuery Pocket Book | Uplatz Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/","og_locale":"en_US","og_type":"article","og_title":"BigQuery Pocket Book | Uplatz Blog","og_description":"BigQuery Pocket Book \u2014 Uplatz ~60 deep-dive flashcards \u2022 Single column \u2022 Storage &amp; Tables \u2022 SQL &amp; Arrays \u2022 Performance &amp; Cost \u2022 Security \u2022 Ingestion \u2022 Ops \u2022 Read More ...","og_url":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/","og_site_name":"Uplatz Blog","article_publisher":"https:\/\/www.facebook.com\/Uplatz-1077816825610769\/","article_published_time":"2025-08-21T10:55:46+00:00","article_modified_time":"2025-08-30T11:46:03+00:00","og_image":[{"width":1280,"height":720,"url":"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery.jpg","type":"image\/jpeg"}],"author":"uplatzblog","twitter_card":"summary_large_image","twitter_creator":"@uplatz_global","twitter_site":"@uplatz_global","twitter_misc":{"Written by":"uplatzblog","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/#article","isPartOf":{"@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/"},"author":{"name":"uplatzblog","@id":"https:\/\/uplatz.com\/blog\/#\/schema\/person\/8ecae69a21d0757bdb2f776e67d2645e"},"headline":"BigQuery Pocket Book","datePublished":"2025-08-21T10:55:46+00:00","dateModified":"2025-08-30T11:46:03+00:00","mainEntityOfPage":{"@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/"},"wordCount":1014,"publisher":{"@id":"https:\/\/uplatz.com\/blog\/#organization"},"image":{"@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/#primaryimage"},"thumbnailUrl":"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery-1024x576.jpg","articleSection":["BigQuery","Pocket Book"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/","url":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/","name":"BigQuery Pocket Book | Uplatz Blog","isPartOf":{"@id":"https:\/\/uplatz.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/#primaryimage"},"image":{"@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/#primaryimage"},"thumbnailUrl":"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery-1024x576.jpg","datePublished":"2025-08-21T10:55:46+00:00","dateModified":"2025-08-30T11:46:03+00:00","breadcrumb":{"@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/#primaryimage","url":"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery.jpg","contentUrl":"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2025\/08\/BigQuery.jpg","width":1280,"height":720},{"@type":"BreadcrumbList","@id":"https:\/\/uplatz.com\/blog\/bigquery-pocket-book\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/uplatz.com\/blog\/"},{"@type":"ListItem","position":2,"name":"BigQuery Pocket Book"}]},{"@type":"WebSite","@id":"https:\/\/uplatz.com\/blog\/#website","url":"https:\/\/uplatz.com\/blog\/","name":"Uplatz Blog","description":"Uplatz is a global IT Training &amp; Consulting company","publisher":{"@id":"https:\/\/uplatz.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/uplatz.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/uplatz.com\/blog\/#organization","name":"uplatz.com","url":"https:\/\/uplatz.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/uplatz.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2016\/11\/Uplatz-Logo-Copy-2.png","contentUrl":"https:\/\/uplatz.com\/blog\/wp-content\/uploads\/2016\/11\/Uplatz-Logo-Copy-2.png","width":1280,"height":800,"caption":"uplatz.com"},"image":{"@id":"https:\/\/uplatz.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/Uplatz-1077816825610769\/","https:\/\/x.com\/uplatz_global","https:\/\/www.instagram.com\/","https:\/\/www.linkedin.com\/company\/7956715?trk=tyah&amp;amp;amp;amp;trkInfo=clickedVertical:company,clickedEntityId:7956715,idx:1-1-1,tarId:1464353969447,tas:uplatz"]},{"@type":"Person","@id":"https:\/\/uplatz.com\/blog\/#\/schema\/person\/8ecae69a21d0757bdb2f776e67d2645e","name":"uplatzblog","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/7f814c72279199f59ded4418a8653ad15f5f8904ac75e025a4e2abe24d58fa5d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/7f814c72279199f59ded4418a8653ad15f5f8904ac75e025a4e2abe24d58fa5d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/7f814c72279199f59ded4418a8653ad15f5f8904ac75e025a4e2abe24d58fa5d?s=96&d=mm&r=g","caption":"uplatzblog"}}]}},"_links":{"self":[{"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/posts\/4708","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/comments?post=4708"}],"version-history":[{"count":2,"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/posts\/4708\/revisions"}],"predecessor-version":[{"id":5065,"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/posts\/4708\/revisions\/5065"}],"wp:attachment":[{"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/media?parent=4708"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/categories?post=4708"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/uplatz.com\/blog\/wp-json\/wp\/v2\/tags?post=4708"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}