Section B · Data systems

Warehouses — Snowflake, BigQuery, Databricks, Redshift

Where extracted data lands so the customer's analytics team can query it. Delivery mechanisms, SQL dialect quirks, cost-aware patterns, and the auth/network considerations for each.

Snowflake

What it is

The most common modern enterprise warehouse. Separation of storage and compute; multi-cloud (AWS, Azure, GCP). Strong data-sharing model.

How to deliver

  • Direct write: your pipeline writes to the customer's Snowflake via SQLAlchemy / snowflake-connector-python. Easiest setup.
  • Snowflake data shares: read-only access to your tables — no copy, real-time. Works when both sides are Snowflake (the most common case).
  • External tables on S3: write Parquet to S3; Snowflake's external-table reads it. Decoupled; customer owns the load step.
  • Snowpipe: continuous ingestion from S3 staging.

Auth

Service-account password (basic); OAuth2; key-pair auth (RSA). Network policies (IP allowlist) common.

Cost-aware patterns

  • Warehouse-suspend after inactivity is on by default; pay for what you use. But cold-starts add latency.
  • Use small warehouse sizes (XS, S) for small workloads; size up only when query volume justifies.
  • Materialized views and clustering keys for repeated query patterns.
  • Compute pools — separate workloads (ingestion vs analytics) to different warehouses; bill independently.

Snowflake-specific syntax

  • QUALIFY clause for filtering window-function results without a subquery.
  • VARIANT type for semi-structured (JSON-like) data with first-class indexing.
  • ARRAY_AGG, OBJECT_CONSTRUCT for nested structure construction.

BigQuery

What it is

Google Cloud's serverless warehouse. Pay-per-query model; effectively unlimited scale; tight integration with Google Cloud services.

How to deliver

  • Direct write: BigQuery client libraries; bq load for batch.
  • Cloud Storage staging: upload Parquet/CSV to GCS; load into BigQuery. Common pattern.
  • BigQuery Data Transfer Service: managed integrations for common sources.
  • External tables on GCS: query GCS-resident data without copying.

Auth

Google Cloud service accounts. Key-based auth (JSON key file); Workload Identity Federation for cross-cloud.

Cost-aware patterns

  • BigQuery pricing has two components: storage (cheap) and query (per-TB scanned). Optimizing for query cost is the lever.
  • Partitioning by date drastically reduces scan size.
  • Clustering on common filter columns.
  • Reservations for predictable workloads — flat-rate compute instead of on-demand.

BigQuery-specific syntax

  • Arrays and structs are first-class. UNNEST for flattening.
  • JSON_VALUE, JSON_EXTRACT_SCALAR for JSON navigation.
  • Approximate aggregations: APPROX_COUNT_DISTINCT, APPROX_QUANTILES — much faster on large data.

Databricks

What it is

Spark-based platform; data warehouse (Databricks SQL) plus data lakehouse (Delta Lake) plus ML/data-science workflows. Common at customers with heavy ML or Spark workloads.

How to deliver

  • Delta Lake on customer's cloud storage: write Delta-formatted Parquet; Databricks reads it.
  • Direct write via Databricks SQL Connector.
  • Databricks Connect: client-side Spark API.
  • External tables: register cloud-storage paths as tables in Unity Catalog.

Auth

Personal access tokens; OAuth2; Unity Catalog for fine-grained access control.

Cost-aware patterns

  • Cluster types (job clusters vs interactive); job clusters are cheaper per workload.
  • Photon (vectorized engine) for big queries.
  • Liquid clustering or Z-ordering on common filter columns.

Databricks-specific syntax

  • Spark SQL is largely ANSI-compliant with extensions.
  • Delta Lake's MERGE INTO for upserts.
  • VACUUM, OPTIMIZE, Z-ORDER BY for table maintenance.

Amazon Redshift

What it is

AWS's columnar warehouse. Older than Snowflake/BigQuery; common at long-standing AWS-shop enterprises that haven't migrated.

How to deliver

  • COPY from S3: the canonical batch-load pattern.
  • Redshift Data API: HTTP-based queries; avoids VPC connectivity.
  • Redshift Spectrum: query S3 data directly without loading.

Auth

Database users; IAM-based auth via Redshift Data API.

Cost-aware patterns

  • RA3 nodes (modern) separate storage and compute; DC2 (legacy) doesn't.
  • Distribution keys and sort keys matter materially.
  • Concurrency scaling is paid extra; tune queries first.

Redshift-specific gotchas

  • Older SQL dialect; some window functions are limited compared to Snowflake.
  • VACUUM still required to reclaim space after deletes/updates (newer versions have auto-vacuum).
  • Long-running queries can block other queries on the same cluster.

Delivery mechanisms summary

PatternProCon
Direct writeSimplest; one stepCustomer may forbid for security; less decoupled
S3/GCS staging + customer loadDecoupled; customer owns load stepTwo-stage; latency
Native data shares (Snowflake)Real-time; no copy; cleanest governanceOnly works same-platform
Reverse-ETL tools (Hightouch, Census)Customer-owned toolingExtra cost and complexity

For most customers, S3/GCS staging + their pipeline is the most defensible pattern. Direct write works for trusting customers. Data shares are excellent when both sides are Snowflake.

Dialect quirks worth pinning

  • Date arithmetic: differs subtly across warehouses. Use INTERVAL literals; test on the customer's warehouse, not Postgres.
  • JSON navigation: Snowflake :, BigQuery JSON_VALUE, Redshift JSON_EXTRACT_PATH_TEXT, Databricks get_json_object.
  • String concat: || works in most; CONCAT in BigQuery and others.
  • String length: LENGTH, LEN, CHAR_LENGTH — pick one per warehouse.
  • QUALIFY for window-filter without subquery: Snowflake, BigQuery, Databricks all support; Redshift doesn't.
The portability discipline

Write your dbt models for one warehouse target; don't try to be portable. Cross-warehouse abstractions add complexity for marginal benefit. The customer is on one warehouse; optimize for it.