Modern Data Warehousing: Snowflake, BigQuery, Redshift & DuckDB - Data Engineering Hub

← Back to Hub Modern Data Warehousing: Snowflake, BigQuery, Redshift & DuckDB Architecture patterns for cloud data warehouses. Covers columnar storage, partitioning, clustering, materialized views, zero-copy cloning, and cost optimization across Snowflake, BigQuery, Redshift, and DuckDB. Table of Contents Architecture Comparison Columnar Storage Fundamentals Partitioning & Clustering Materialized Views Zero-Copy Cloning Cost Optimization DuckDB for Local Analytics Migration Patterns Architecture Comparison Feature Snowflake BigQuery Redshift DuckDB Storage/Compute Separated Separated Coupled (RA3 separates) Embedded Scaling Instant (seconds) Instant (slots) Minutes (resize) N/A (local) Concurrency Multi-cluster WH Slot-based WLM queues Single-process Partitioning Micro-partitions (auto) Partitioned tables Dist/Sort keys Hive partitioning Clustering Clustering keys Clustering columns Sort keys N/A Semi-structured VARIANT (native) JSON/RECORD SUPER type JSON/STRUCT ML Integration Snowpark ML BigQuery ML Redshift ML Python UDFs Columnar Storage Fundamentals All modern warehouses use columnar storage with compression: How It Works Column chunks: Each column stored separately, enabling column pruning Encoding: Dictionary, RLE, Delta, Bit-packing per data type Compression: ZSTD, Snappy, LZ4 on encoded data Statistics: Min/max/null count per chunk for predicate pushdown Snowflake Micro-Partitions -- Auto-clustering: Snowflake manages micro-partitions (50-500MB) -- No manual partitioning needed, but clustering keys help large tables CREATE TABLE events ( event_id STRING, user_id STRING, event_time TIMESTAMP_NTZ, payload VARIANT ) CLUSTER BY (user_id, event_time); -- Check clustering depth (lower = better pruning) SELECT SYSTEM$CLUSTERING_DEPTH('EVENTS'); -- Recluster manually if needed ALTER TABLE EVENTS RECLUSTER; BigQuery Partitioned & Clustered Tables -- Partition by date (ingestion or column) CREATE TABLE `project.dataset.events` PARTITION BY DATE(event_time) CLUSTER BY user_id, event_type AS SELECT * FROM `project.dataset.raw_events`; -- Partition pruning happens automatically SELECT * FROM `project.dataset.events` WHERE DATE(event_time) = '2024-01-15' AND user_id = 'user_123'; -- Cluster pruning also applies -- Partition expiration for cost control ALTER TABLE `project.dataset.events` SET OPTIONS (partition_expiration_days = 365); Redshift Distribution & Sort Keys -- Distribution: EVEN (default), KEY (co-locate joins), ALL (small tables) -- Sort: COMPOUND (prefix) or INTERLEAVED (equal weight) CREATE TABLE events ( event_id BIGINT IDENTITY(1,1), user_id BIGINT, event_time TIMESTAMP, payload SUPER ) DISTKEY (user_id) -- Co-locate user events SORTKEY (event_time); -- Time-series queries -- ANALYZE for query optimizer statistics ANALYZE events; Partitioning & Clustering Strategies When to Partition vs Cluster Partition: High-cardinality filter columns (date, tenant_id) — reduces bytes scanned Cluster: Medium-cardinality columns used in joins/filters — co-locates related data Both: Partition by date, cluster by user_id within each partition Snowflake: Clustering Keys -- Add clustering to existing table ALTER TABLE events CLUSTER BY (user_id, event_time); -- Monitor clustering effectiveness SELECT * FROM TABLE(INFORMATION_SCHEMA.CLUSTERING_INFORMATION('EVENTS')); -- clustering_depth < 10 is good; > 100 needs reclustering -- Automatic clustering (Snowflake manages) ALTER TABLE events RESUME RECLUSTER; ALTER TABLE events SUSPEND RECLUSTER; BigQuery: Partition + Cluster -- Ingestion-time partitioning (cheaper, less flexible) CREATE TABLE `dataset.events` PARTITION BY _PARTITIONDATE CLUSTER BY user_id, event_type OPTIONS (partition_expiration_days = 730); -- Column partitioning (flexible, supports partition pruning on any DATE/TIMESTAMP) CREATE TABLE `dataset.events` PARTITION BY DATE(event_time) CLUSTER BY user_id, event_type; -- Require partition filter (prevents full scans) ALTER TABLE `dataset.events` SET OPTIONS (require_partition_filter = TRUE); Redshift: Distribution Styles -- Choose diststyle per table size and join patterns -- EVEN: Default, round-robin (good for uniform access) -- KEY: Hash on distkey (co-locate joined tables) -- ALL: Broadcast to all nodes (dimension tables < 10M rows) -- Star schema: fact table KEY on FK, dimension tables ALL CREATE TABLE fact_sales ( sale_id BIGINT, customer_id BIGINT, product_id BIGINT, amount DECIMAL(10,2) ) DISTKEY (customer_id) SORTKEY (sale_date); CREATE TABLE dim_customer ( customer_id BIGINT, name VARCHAR(100) ) DISTSTYLE ALL; -- Compound vs Interleaved sort keys -- COMPOUND: WHERE sale_date > '2024-01-01' AND customer_id = 123 -- INTERLEAVED: WHERE customer_id = 123 OR product_id = 456 Materialized Views Pre-computed results for expensive aggregations. Auto-refreshed on base table changes. Snowflake Materialized Views CREATE MATERIALIZED VIEW mv_daily_user_stats AS SELECT DATE(event_time) AS event_date, user_id, COUNT(*) AS event_count, COUNT(DISTINCT event_type) AS unique_events FROM events GROUP BY DATE(event_time), user_id; -- Query rewrite is automatic for compatible queries SELECT event_date, user_id, event_count FROM events WHERE event_date >= '2024-01-01' GROUP BY event_date, user_id; -- Rewrites to MV -- Suspend/resume for maintenance ALTER MATERIALIZED VIEW mv_daily_user_stats SUSPEND; ALTER MATERIALIZED VIEW mv_daily_user_stats RESUME; BigQuery Materialized Views CREATE MATERIALIZED VIEW `project.dataset.mv_daily_stats` PARTITION BY event_date CLUSTER BY user_id OPTIONS ( enable_refresh = TRUE, refresh_interval_minutes = 60 ) AS SELECT DATE(event_time) AS event_date, user_id, COUNT(*) AS event_count, COUNT(DISTINCT event_type) AS unique_events FROM `project.dataset.events` GROUP BY event_date, user_id; -- Manual refresh if needed CALL BQ.REFRESH_MATERIALIZED_VIEW('project.dataset.mv_daily_stats'); Redshift Materialized Views CREATE MATERIALIZED VIEW mv_daily_user_stats DISTKEY (user_id) SORTKEY (event_date) AS SELECT DATE(event_time) AS event_date, user_id, COUNT(*) AS event_count, COUNT(DISTINCT event_type) AS unique_events FROM events GROUP BY DATE(event_time), user_id; -- Auto-refresh on commit (default) or manual REFRESH MATERIALIZED VIEW mv_daily_user_stats; Zero-Copy Cloning Instant, metadata-only copies for dev/test/backup without storage duplication. Snowflake: CLONE -- Clone entire database (instant, zero storage) CREATE DATABASE dev_db CLONE prod_db; CREATE SCHEMA dev_schema CLONE prod_schema; CREATE TABLE dev_table CLONE prod_table; -- Time Travel: Clone at specific point CREATE TABLE events_yesterday CLONE events AT(TIMESTAMP => DATEADD(day, -1, CURRENT_TIMESTAMP())); -- Clone with data filtering (CREATE TABLE AS SELECT from clone) CREATE TABLE dev_sample AS SELECT * FROM prod_table CLONE TABLE prod_table TABLESAMPLE BERNOULLI (10); BigQuery: Table Clones (Preview) -- Create clone (metadata only) CREATE TABLE `project.dev.events` CLONE `project.prod.events`; -- Clone with time travel CREATE TABLE `project.dev.events_20240101` CLONE `project.prod.events` FOR SYSTEM_TIME AS OF '2024-01-01 00:00:00'; Redshift: Not Native (Use CTAS) -- No zero-copy clone. Use CTAS (copies data) or UNLOAD/LOAD CREATE TABLE dev_events AS SELECT * FROM prod_events WHERE 1=0; -- Schema only INSERT INTO dev_events SELECT * FROM prod_events TABLESAMPLE BERNOULLI (10); Cost Optimization Strategies Storage Costs Compression: All warehouses auto-compress; verify with SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS (Snowflake) Data types: Use smallest type (INT vs BIGINT, DATE vs TIMESTAMP) VARIANT/SUPER/JSON: Expensive to query; extract frequent fields to columns Retention: Set TTL on temp tables; use partitioning for time-based expiry Compute Costs Warehouse Lever Action Snowflake Warehouse size XS for dev, L/XL for prod; auto-suspend 60s Snowflake Multi-cluster Max clusters = peak concurrency / 8 BigQuery Slot reservations Commit 1yr/3yr for 40-60% discount BigQuery On-demand Partition + cluster to reduce bytes billed Redshift RA3 + Redshift Serverless Serverless for variable workloads Redshift Concurrency scaling Auto-add clusters for queue spikes Query Optimization for Cost -- Snowflake: Search optimization service (for point lookups on non-clustered cols) ALTER TABLE events ADD SEARCH OPTIMIZATION ON EQUALITY(user_id); -- BigQuery: Partition pruning verification EXPLAIN SELECT * FROM `project.dataset.events` WHERE DATE(event_time) = '2024-01-15'; -- Check "partitionId" in explain output -- Redshift: WLM queue configuration -- Short queries (dashboard): dedicated queue, high priority -- ETL: separate queue, lower priority -- Ad-hoc: default queue, timeout 30min DuckDB for Local Analytics Embedded analytical database for local development, testing, and edge analytics. Key Features Zero-dep: Single binary, no server, runs in-process SQL compatibility: PostgreSQL dialect, window functions, CTEs Columnar: Vectorized execution, parallel query processing Extensions: HTTPFS (S3), Parquet, Iceberg, Delta, Postgres scanner Local Development Workflow import duckdb # Connect to local file or in-memory con = duckdb.connect("local_warehouse.duckdb") # Read Parquet directly from S3 (httpfs extension) con.install_extension("httpfs") con.load_extension("httpfs") con.execute(""" CREATE VIEW events AS SELECT * FROM read_parquet('s3://bucket/events/year=2024/month=*/*.parquet') """) # Query with full SQL result = con.execute(""" SELECT DATE(event_time) as day, user_id, COUNT(*) FROM events WHERE event_time >= '2024-01-01' GROUP BY day, user_id ORDER BY day DESC """).fetchdf() # Export to Parquet for warehouse loading con.execute("COPY (SELECT * FROM events) TO 'local_events.parquet' (FORMAT PARQUET)") DuckDB + dbt for Local Modeling # profiles.yml duckdb: target: dev outputs: dev: type: duckdb path: local_warehouse.duckdb threads: 4 # dbt models run locally against DuckDB # dbt run --target dev # Then deploy same models to Snowflake/BigQuery by changing target Migration Patterns Snowflake → BigQuery -- 1. Extract DDL (Snowflake) SELECT GET_DDL('TABLE', 'EVENTS'); -- 2. Convert data types -- VARIANT → JSON/RECORD -- TIMESTAMP_NTZ → TIMESTAMP -- NUMBER(p,s) → NUMERIC(p,s) -- 3. Load via GCS (recommended for > TB) -- Snowflake: COPY INTO @stage FROM events; -- gsutil cp @stage/* gs://bucket/migration/ -- BigQuery: bq load --source_format=PARQUET dataset.events gs://bucket/migration/*.parquet -- 4. Validate row counts & checksums SELECT COUNT(*), SUM(HASH(*)), ... FROM both; Redshift → Snowflake -- Use AWS DMS or Snowflake's Redshift migration tool -- Key conversions: -- DISTKEY/SORTKEY → CLUSTER BY -- SUPER → VARIANT -- IDENTITY → AUTOINCREMENT -- INTERLEAVED SORTKEY → not directly supported (use clustering) -- Redshift UNLOAD to S3 → Snowflake COPY FROM S3 UNLOAD ('SELECT * FROM events') TO 's3://bucket/migration/events_' IAM_ROLE 'arn:aws:iam::...' PARQUET PARTITION BY (year, month); -- Snowflake COPY INTO events FROM @s3_stage/migration/ FILE_FORMAT = (TYPE = PARQUET); Related Guides Batch Processing at Scale Real-Time Streaming Data Quality Contracts Data Observability