Analytical / warehouse

Table data lives as files in a shared object store that every node can read over the network, and compute clusters are stateless workers that read it. Either side can be resized, paused or duplicated without touching the other.

A data warehouse stores data in an engine-managed, usually proprietary format with an enforced schema and integrated compute. A data lake stores raw files in object storage that many engines can read, with structure applied only at query time.

APPROX_COUNT_DISTINCT returns an estimated number of distinct values computed from a small fixed-size sketch, rather than the exact number computed by tracking every value seen. It uses far less memory and time, at a few percent typical error.

Each column's values sit contiguously in their own chunks, and the file metadata records where every chunk starts. The scan reads only the byte ranges of the columns the query names; the other columns are never opened or decompressed.

An encoding rewrites one column's values in a type-aware way — dictionary codes, run lengths, deltas — and can often be queried directly. A codec such as ZSTD then compresses those encoded bytes opaquely, and the block must be decompressed before anything can read it.

Snowflake separates storage (compressed columnar data in cloud object storage), compute (virtual warehouses, independent clusters that run queries), and cloud services (metadata, query optimization, transactions, security). The three layers scale independently and are billed separately.

Credits meter compute: a virtual warehouse burns them per second while it is running, serverless features burn them with no warehouse, and cloud services burn them above a daily allowance. Storage and data egress are billed separately, not in credits.

A Snowflake stage is a named location that holds data files for loading. Internal stages live in Snowflake-managed storage and receive files through the PUT command; external stages point at a bucket you own in S3, Azure Blob or GCS.

Snowflake splits every table into immutable micro-partitions and keeps per-column min/max metadata for each one. A filter is compared against that metadata, so only micro-partitions whose value ranges could match are read. Skipping the rest replaces indexes.

A Snowflake virtual warehouse is a named cluster of compute nodes that runs queries, DML and loads; it stores no data. Each size step up doubles the compute in the cluster and doubles the credits billed per running hour.

BigQuery is serverless: table data lives in Google-managed storage, and compute comes from a shared pool that Google's scheduler assigns to your query as slots for the duration of the query. There is no cluster to create, size, or resume.

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.

The leader node is the only node clients connect to: it parses SQL, builds and compiles the execution plan, ships code to the compute nodes, and merges their partial results. Compute nodes store the data and execute plan segments in parallel.

DISTSTYLE decides how a Redshift table's rows are spread across compute slices. KEY hashes one column so equal values co-locate, ALL puts a full copy on every node, EVEN round-robins rows, and AUTO lets Redshift pick and change it.

COPY reads many S3 files at once across all node slices, applies compression, and commits once. Single-row INSERTs pay a commit each and leave 1 MB column blocks nearly empty, so the load crawls and storage balloons.

Redshift Spectrum runs ordinary SQL directly against files in S3 without loading them into the cluster. Before querying you create an external schema that points at a data catalog database and an IAM role, then define external tables over S3 prefixes.

A slice is a partition of a compute node's memory and disk that acts as an independent worker. Redshift spreads every table's rows across all slices and runs each plan segment once per slice, so total slices set the cluster's degree of parallelism.

Each inserted block becomes a brand-new immutable data part: a directory of sorted, compressed per-column files plus index files. Existing files are never modified; a background merge process later combines parts into fewer, larger ones.

MergeTree stores rows as sorted, compressed, immutable parts merged in the background, and alone supports the sparse index, partitions, TTL and replication. Log engines are index-free append-only files; Memory keeps rows in RAM and loses them on restart.

Because inserts are creating parts faster than background merges can combine them. ClickHouse first delays and then rejects inserts once a partition's active part count crosses the parts_to_delay_insert and parts_to_throw_insert thresholds, protecting query performance and the merge pool.

Only the rows in the block just inserted. A ClickHouse materialized view runs its SELECT as if that single block were the whole source table, so it never sees existing rows, and merges, mutations and TTL deletions do not trigger it at all.

Because the view aggregates only the block being inserted, its output is partial. The -State combinator stores a mergeable intermediate aggregate instead of a final number, so AggregatingMergeTree can combine partial results from many inserts; readers finish the job with uniqMerge.