Warehouses — Snowflake, BigQuery, Databricks, Redshift

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.
• Separate warehouses — put workloads (ingestion vs analytics) on 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

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 and Redshift all support it.

✓ Check yourself

Answer from memory before opening each one.

1. The customer runs Snowflake, and security forbids you from writing into their account. Which delivery options remain, and what does each cost you?

   Show answer

   Two remain. A data share gives them read-only access to your tables with no copy and in real time, but it works only when both sides are on Snowflake. Staging Parquet in cloud storage lets them own the load step, but you add a second stage and some latency. See delivery mechanisms.

2. Dashboards on your BigQuery tables scan about 3 TB per query and the bill is climbing. What do you change?

   Show answer

   BigQuery charges for bytes scanned, so cut the scan. Partition by date, cluster on the columns people filter by, and use reservations if the workload is steady. Storage is cheap, so adding structure costs little. See BigQuery.

3. A dbt model written for Snowflake uses colon JSON paths and QUALIFY. The customer is on Redshift. Which parts port and which break?

   Show answer

   QUALIFY ports, because Redshift supports it. The JSON navigation does not: Redshift uses JSON_EXTRACT_PATH_TEXT where Snowflake uses the colon syntax. Date arithmetic can differ too, so test on the customer's warehouse. See dialect quirks.

4. Your Snowflake pipeline loads hourly and analysts query the same tables all day, on one warehouse. What do you change, and what trade-off remains?

   Show answer

   Give ingestion and analytics separate warehouses so each bills and scales on its own. Auto-suspend keeps idle cost down, but a suspended warehouse restarts cold and adds latency to the first query. See Snowflake.

5. The Redshift cluster is in a private network and the customer will not open a path to it. Which mechanism do you reach for?

   Show answer

   The Redshift Data API. It runs queries over HTTP and uses IAM auth, so you avoid network connectivity into the VPC. COPY from S3 is the usual batch load. See Redshift.

6. You know nothing yet about a new customer's data team. What delivery pattern do you default to, and when do you switch?

   Show answer

   Default to staging in S3 or GCS and let their pipeline load it. It is decoupled and the customer owns the last step. Switch to direct write when the customer trusts you and wants the simplest path, and to a data share when both sides are on Snowflake. See delivery mechanisms.

Takeaway

• Delivery is a choice of who owns the last step: direct write, staged files, a native data share, or reverse-ETL.
• For most customers, staging in S3 or GCS with their own load is the most defensible default. Data shares are cleanest when both sides run Snowflake.
• Cost levers differ by warehouse. Snowflake bills warehouse compute, BigQuery bills bytes scanned, and Redshift needs sort and distribution keys.
• Separate ingestion from analytics on different compute so each bills and scales independently.
• Dialects differ in JSON paths, date arithmetic and string functions, so test on the customer's warehouse and not on Postgres.
• Write the dbt models for the one warehouse the customer runs. Portability across warehouses costs more than it returns.

The warehouse holds the tables, and the BI tool is where business users see them. → BI tools

Further reading

• Introduction to Secure Data Sharing — Snowflake docs, accessed 2026. What a share is, and why it needs no copy.
• Snowpipe — Snowflake docs, accessed 2026. Continuous loading from staged files.
• Introduction to partitioned tables — Google Cloud docs, accessed 2026. How partitioning limits the bytes a query scans.
• Using the Amazon Redshift Data API — AWS docs, accessed 2026. Running SQL over HTTP without a database connection.
• QUALIFY clause — AWS docs, accessed 2026. Redshift's support for filtering window-function results.
• Upsert into a Delta Lake table using merge — Databricks docs, accessed 2026. The MERGE INTO pattern for idempotent loads.