Snowflake & BigQuery

Snowflake is cloud-agnostic (AWS/Azure/GCP), uses virtual warehouses with independent compute scaling, offers time-travel (query historical snapshots), and dominates enterprise adoption ($50B valuation, 8000+ customers). Pricing: per-second compute + storage. BigQuery is Google-native, serverless (no warehouse management), uses slots for reserved compute, and excels in Google Cloud environments. Pricing: per-query with monthly commitments for high volume. Choose Snowflake for multi-cloud flexibility and team control over compute. Choose BigQuery for Google Cloud-first orgs wanting zero infrastructure management.

Snowflake: (1) Right-size virtual warehouses, don't run X-Large for small queries; (2) Use clustering on high-cardinality columns to skip full table scans; (3) Understand query impact, smaller warehouse = slower but cheaper; (4) Monitor per-user spend with WAREHOUSE_METERING_HISTORY; (5) Suspend warehouses when idle. BigQuery: (1) Use partitioned tables to scan fewer bytes (costs are per-byte scanned, not compute time); (2) Use clustering for high-cardinality columns; (3) Run BI Engine for cached results; (4) Estimate query cost before running with `--dry_run` flag; (5) Use monthly commitments if volume >100TB/month. Snowflake: fixed per-second billing, easier to budget. BigQuery: usage-based, harder to predict but cheaper for ad-hoc queries.

ELT (Extract-Load-Transform) is the modern default: load raw data into the warehouse first, then transform with SQL (dbt). Why: (1) Faster initial load (no pre-processing); (2) Flexible schema evolution (schema-on-read); (3) All transformations in SQL using warehouse compute; (4) Version-controlled dbt models; (5) Easy to backfill and test. ETL (Extract-Transform-Load) = transform before loading (old pattern, still used for legacy systems). Both Snowflake and BigQuery are optimized for ELT. Use dbt as your transformation framework, it generates SQL, runs on schedule, and integrates with both.

Partitioning: divide a table by a column (usually date) so queries only scan relevant partitions. Example: `PARTITION BY DATE(created_at)`, query '2026-01-01' only scans Jan 2026 data, not all years. Clustering: sort data within partitions by one or more columns (high-cardinality: user_id, product_id). Both reduce bytes scanned = lower cost + faster queries. Snowflake: clustering is automatic (Z-order) but you specify cluster key. BigQuery: partitioning is mandatory for large tables, clustering is optional. Use partitioning for temporal data (dates, timestamps). Use clustering for high-cardinality columns (user_id, account_id, product_id). Combine both: partition by date, cluster by user_id.

Postgres: transactional OLTP (inserts/updates/deletes), relational consistency (ACID), small-to-medium datasets (1-10TB), low latency (ms), complex JOINs. Snowflake/BigQuery: analytical OLAP (massive SELECTs), denormalized schemas (fact tables), 100TB-EB scale, batch queries (seconds to minutes OK), compressed storage, cheap retention. Example: use Postgres as your operational database (customer orders, inventory), stream/batch to Snowflake for analytics (revenue by region, cohort analysis). Postgres = source of truth for transactional data. Snowflake/BigQuery = analytics engine for historical analysis + ML.

Time travel = query a table as it was at a specific time in the past. Snowflake: `SELECT * FROM table_name AT (TIMESTAMP => '2026-01-01 10:00:00'::timestamp_tz)` or `BEFORE (STATEMENT => query_id)`. Retention: 90 days by default (configurable). Use cases: (1) accidental DELETE recovery; (2) auditing data changes; (3) A/B testing by querying different points in time; (4) debugging data pipeline bugs. Cost: minimal, just READS, no extra storage. BigQuery: no native time travel, but snapshots exist (table decorators) with cheaper storage: `SELECT * FROM table@1234567890000` (milliseconds since epoch). Snowflake time travel is a major advantage for audit/recovery workflows.

dbt (data build tool) = your transformation framework. Write SQL SELECT statements, dbt compiles to INSERT/CREATE TABLE/CREATE VIEW. Workflow: (1) raw data lands in Snowflake/BigQuery (Fivetran/Airbyte does EL); (2) dbt models transform raw → mart tables; (3) dbt manages dependencies (model A depends on model B); (4) dbt runs on schedule (daily) or on-demand; (5) dbt tracks lineage and tests data quality (e.g., `not_null`, `unique` checks). Both Snowflake and BigQuery have first-class dbt support, dbt adapters handle SQL dialect differences. Use dbt for: schema evolution, version control (git), documentation (auto-generated), testing, deployment CI/CD.