Google BigQuery

A BigQuery load job reads files from Cloud Storage and commits them into a table atomically — all rows or none. It reads CSV, newline-delimited JSON, Avro, Parquet and ORC, and carries no per-byte ingestion charge.

BigQuery supports three partition types: ingestion-time (the _PARTITIONTIME pseudo-column), time-unit column (a DATE, TIMESTAMP or DATETIME column at hour, day, month or year granularity), and integer range via RANGE_BUCKET. All are declared with PARTITION BY in the DDL.

Under BigQuery on-demand pricing you pay per byte processed: the uncompressed size of the columns the query reads, after partition and cluster pruning. The number of rows returned is irrelevant, so LIMIT changes nothing.

A STRUCT is a record of named, typed fields you address with dot notation. An ARRAY is a repeated field holding an ordered list of values in one row; to treat its elements as rows you must UNNEST it.

BigQuery compiles SQL into a directed graph of stages executed by Dremel. Slots read only the referenced columns from Capacitor files on Colossus over the Jupiter network, pass intermediate rows through an in-memory shuffle tier, and a final stage writes the result.

Use the BigQuery Storage Write API when rows must be queryable within seconds or arrive one at a time from an application. Use batch load jobs otherwise — they carry no per-byte ingestion charge and commit atomically, while streamed bytes are billed.

An authorized view sits in a separate dataset that you authorize on the source dataset. Readers are granted access to the view's dataset only; the view reads the base tables under its own authorization and exposes just the columns and rows it selects.

BigQuery ML trains and serves models inside the warehouse with SQL: CREATE MODEL fits a model on a query's result set and ML.PREDICT scores rows. Use it when the data already lives in BigQuery and the model type is a standard one.

BigQuery prunes partitions only when it can resolve the predicate on the partitioning column before execution. Comparing that column to a subquery result, ORing it with a non-partition predicate, or wrapping it in an expression BigQuery cannot map back to partition boundaries all force a full scan.

Partitioning splits a BigQuery table into physical segments on one date or integer expression and is eliminated at planning time. Clustering sorts rows inside each partition by up to four columns so BigQuery can skip blocks at runtime. Use a date partition plus clustering on the high-cardinality filter columns.

Run it as a BigQuery dry run: the console query validator, bq query --dry_run, or dryRun: true in the API. It returns estimated bytes processed without executing the query and without charge.

The comma is a CROSS JOIN, and cross-joining a row against zero elements produces no output rows, so parents with empty arrays disappear. Use LEFT JOIN UNNEST(t.items) AS item to keep them, with the element columns NULL.

Keeping data in Colossus makes compute stateless: any worker can serve any query, capacity can change mid-query without rebalancing data, and storage persists and is billed independently. Google's Jupiter network plus columnar files make the remote reads fast enough.

They differ in when rows become visible and what guarantee you get. The default stream is at-least-once and always available; committed streams make rows visible on append and support exactly-once via offsets; buffered streams hold rows until you flush; pending streams hold everything until one atomic commit.

Keep data external when other engines own the files, the data is queried rarely, or you are still exploring it. Load it into BigQuery once queries repeat, because native storage gives clustering, DML and predictable performance that an external table cannot.

Use the native JSON type when the payload's shape varies but you query paths inside it: values are stored parsed, so path access is direct and only the referenced paths need reading. Keep STRING only for opaque payloads you never query into.

Read the job's per-stage statistics: the execution details in the console, or the job_stages array in INFORMATION_SCHEMA.JOBS_BY_PROJECT. Rank stages by slot_ms, then look at records read, shuffle bytes, and wait/read/compute/write timings to see why that stage was expensive.

BigQuery scales the number of workers, not the memory inside one worker. Operations that funnel all rows into a single worker — a global ORDER BY, a window function with no PARTITION BY, one enormous group — exceed that worker's memory no matter how many slots are free.

Duplicates usually mean at-least-once writes: the default stream or legacy insertAll retried an append whose acknowledgement was lost. Fix it upstream with application-created committed streams and per-request offsets, or downstream by deduplicating on a business key.

Gzip is not splittable, so BigQuery must read that file with a single worker — the load is serialised no matter how much capacity exists. Split the data into many files, or use Avro or Parquet, whose internal blocks can be read in parallel.

Every chart, filter and refresh is a separate BigQuery job scanning the source table. Attribute the spend from INFORMATION_SCHEMA.JOBS_BY_PROJECT, then point the dashboard at a partitioned, clustered rollup table, lengthen cache freshness, and consider a BI Engine reservation.

A BigQuery row access policy attaches a named filter and a grantee list to the table itself, so every query by those principals is filtered automatically. Views push the same logic into one object per audience, which duplicates and drifts.

A dry run reports what BigQuery can determine before execution. Partition elimination is decided then and is reflected, but clustering skips storage blocks during execution, so the estimate assumes no block skipping and is an upper bound on the bytes a clustered table actually bills.

Layer controls: maximum_bytes_billed fails an oversized job before it reads data, custom query-usage-per-day quotas cap a project or user for the day, require_partition_filter blocks unfiltered scans of big tables, and INFORMATION_SCHEMA attributes what still slips through.

Switch when spend is high and predictable: measure actual slot demand from INFORMATION_SCHEMA total_slot_ms, compare a reservation sized to that demand against your per-byte spend, and move when capacity is cheaper or you need predictable cost and performance.

Flattening repeats each parent column once per array element, so a parent-level measure is summed once per child and multiplied by the array length. Aggregate the array in a scalar subquery instead, keeping the query at one row per parent.

Pick the coarsest granularity that still prunes for the dominant queries: daily for five years of history keeps partition count and partition size sane, hourly usually does not. Then cluster on the most-filtered high-cardinality columns, leading with the one queries name most, and enforce a required partition filter plus expiration.

Buy capacity once in an admin project, then split it into a few reservations by workload class — production pipelines, BI, ad-hoc — bind projects or folders to them with assignments, commit only the confident floor, and let autoscaling and idle-slot sharing absorb the rest.

Nest when children are always read with their parent and never independently: the join disappears, the row stays atomic, and leaf columns are still pruned. Split them out when children are updated individually, queried on their own, or unbounded in number.