Deep Technical Series — Building a Modern Data Warehouse and Lakehouse

Part 7 — Implementing the Warehouse: Snowflake, Fabric, BigQuery, Synapse, and Redshift

---

Parts 1 through 6 gave you the dimensional model, the medallion architecture, the ingestion pipelines, and the transformation logic. Now it is time to implement the physical warehouse — the compute and storage engine where your Gold layer actually lives and where BI tools connect.

This part covers the physical implementation details for each major cloud warehouse: table design decisions, storage organization, compute configuration, connection setup, and the platform-specific tuning that separates a warehouse that performs well from one that is slow and expensive. Every platform has different physical mechanisms — clustering keys, distribution strategies, columnstore indexes, partitioning — and choosing the wrong one for your workload has a direct, measurable impact on query time and cost.

1 Physical Warehouse Design Principles Beginner

Before diving into platform-specific implementation, three universal principles apply regardless of which warehouse you choose. Getting these right at design time prevents expensive redesigns later.

Principle 1: Design for the Query, Not the Load

Warehouse physical design decisions — clustering keys, distribution keys, sort keys, partition columns — should be driven by how the data will be queried, not how it will be loaded. The most common analytical query pattern on a fact table (filter by date, group by customer or product) should inform every physical design decision. Design for reads first because reads are what BI tools and analysts do constantly. Writes happen once per pipeline run.

Principle 2: Small Dimensions, Large Facts

Dimension tables are typically small — thousands to millions of rows. Fact tables are large — hundreds of millions to billions of rows. Physical optimization effort should be concentrated on fact tables. A poorly designed dimension table rarely causes performance problems. A poorly designed fact table with no partitioning or clustering causes every dashboard query to be slow and expensive.

Principle 3: Storage and Compute Are Separate on Modern Warehouses

Snowflake, BigQuery, Fabric, Redshift RA3, and Synapse all separate storage from compute. Storage is cheap and scales independently. Compute is what costs money and what you tune for performance. Scaling up compute (larger warehouse, more slots, more DWUs) is the fastest way to improve query performance when physical design alone is not enough — but it is not a substitute for correct physical design.

2 Columnstore vs Rowstore — What Every Warehouse Uses Beginner

All modern analytical warehouses store data in columnar format — data is organized by column rather than by row. This is fundamental to why warehouses perform well for analytics and why OLTP databases (row-oriented) are not appropriate for analytical workloads.

In a columnar store, a query that selects 3 columns from a 100-column table reads only those 3 columns from disk. In a row store, the same query reads all 100 columns for every row and discards 97 of them. At billion-row scale, this difference is the difference between a 2-second query and a 20-minute query.

Characteristic	Columnstore (Analytical)	Rowstore (Transactional)
Storage layout	Data organized by column	Data organized by row
Compression	Excellent — similar values compress together	Moderate — mixed values per row block
Aggregation speed	Very fast — scans one column in memory	Slow — reads full rows to extract one column
Point lookup speed	Slower — must read column files	Fast — row is co-located
Best for	Analytics, BI, aggregations, wide scans	OLTP, point lookups, updates, deletes
Used by	Snowflake, BigQuery, Redshift, Synapse, Fabric Warehouse	SQL Server OLTP, MySQL, PostgreSQL

Snowflake uses a unique micro-partition architecture. Data is automatically divided into compressed micro-partitions of 50–500 MB each when loaded. Each micro-partition stores metadata about the min and max values of every column — enabling the query engine to skip entire micro-partitions that cannot contain rows matching a WHERE clause. This automatic pruning happens without any explicit partitioning configuration on small to medium tables.

Virtual Warehouses — Compute Configuration

A Virtual Warehouse is a named cluster of compute resources. You create separate warehouses for different workloads — ETL loading, BI queries, data science — and size each independently. Warehouses auto-suspend when idle (billing stops) and auto-resume when a query arrives (billing resumes within seconds).

-- Create separate virtual warehouses for different workload types
-- ETL warehouse: larger, runs during pipeline windows only
CREATE WAREHOUSE etl_warehouse
    WAREHOUSE_SIZE   = 'LARGE'
    AUTO_SUSPEND     = 120        -- suspend after 2 minutes idle
    AUTO_RESUME      = TRUE
    INITIALLY_SUSPENDED = TRUE;

-- BI warehouse: smaller, runs during business hours
CREATE WAREHOUSE bi_warehouse
    WAREHOUSE_SIZE   = 'SMALL'
    AUTO_SUSPEND     = 60
    AUTO_RESUME      = TRUE
    MIN_CLUSTER_COUNT = 1
    MAX_CLUSTER_COUNT = 3         -- multi-cluster: scales for concurrent BI users
    SCALING_POLICY   = 'ECONOMY';

-- Data science warehouse: medium, on-demand
CREATE WAREHOUSE ds_warehouse
    WAREHOUSE_SIZE   = 'MEDIUM'
    AUTO_SUSPEND     = 300
    AUTO_RESUME      = TRUE;

Clustering Keys — For Large Fact Tables

-- Snowflake automatically handles physical organization on small/medium tables
-- For large fact tables (100M+ rows) add explicit clustering keys
-- to improve micro-partition pruning on common filter columns

-- Add clustering key on the most common filter columns
ALTER TABLE gold.fct_order_line
CLUSTER BY (order_date_key, customer_key);

-- Check if clustering is providing benefit
SELECT SYSTEM$CLUSTERING_INFORMATION('gold.fct_order_line');
-- Look for: average_overlaps close to 0 = good clustering
-- average_depth close to 1 = excellent
-- average_depth > 6 = consider re-clustering

-- Automatic clustering: Snowflake re-clusters as new data arrives
ALTER TABLE gold.fct_order_line
CLUSTER BY (order_date_key, customer_key)
  COMMENT = 'Automatic re-clustering enabled';

-- Check clustering cost
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.AUTOMATIC_CLUSTERING_HISTORY
WHERE START_TIME >= DATEADD('day', -7, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;

Gold Layer DDL — Snowflake

-- Snowflake: complete Gold dimension and fact table setup
-- Using dedicated Gold schema and warehouse-optimized DDL

CREATE SCHEMA IF NOT EXISTS gold;

-- Dimension: no explicit clustering needed (small table, auto-pruning handles it)
CREATE TABLE gold.dim_customer (
    customer_key         BIGINT         AUTOINCREMENT PRIMARY KEY,
    customer_natural_key VARCHAR(100)   NOT NULL,
    customer_name        VARCHAR(200),
    email                VARCHAR(200),
    city                 VARCHAR(100),
    state_province       VARCHAR(100),
    country              VARCHAR(100),
    loyalty_tier         VARCHAR(50),
    effective_from       DATE           NOT NULL,
    effective_to         DATE           NOT NULL,
    is_current           TINYINT        NOT NULL,
    CONSTRAINT uq_dim_customer_active
        UNIQUE (customer_natural_key, is_current)
)
DATA_RETENTION_TIME_IN_DAYS = 7;    -- time travel for 7 days

-- Fact table: clustering on most common filter columns
CREATE TABLE gold.fct_order_line (
    order_line_key   BIGINT         AUTOINCREMENT PRIMARY KEY,
    order_natural_key VARCHAR(100)  NOT NULL,
    order_date_key   INT            NOT NULL,
    customer_key     BIGINT         NOT NULL,
    product_key      BIGINT         NOT NULL,
    store_key        BIGINT         NOT NULL,
    quantity         INT            NOT NULL,
    unit_price       DECIMAL(18,2)  NOT NULL,
    discount_amount  DECIMAL(18,2)  NOT NULL DEFAULT 0,
    extended_amount  DECIMAL(18,2)  NOT NULL
)
CLUSTER BY (order_date_key, customer_key)
DATA_RETENTION_TIME_IN_DAYS = 7;

Microsoft Fabric Warehouse is a fully managed T-SQL warehouse built on the same Synapse engine but deeply integrated with OneLake and Power BI. It stores data as Delta Parquet files in OneLake, exposes a T-SQL interface for querying and loading, and integrates natively with Power BI through Direct Lake mode — allowing Power BI reports to query the warehouse data directly from OneLake without importing it into a separate semantic model cache.

Fabric Warehouse vs Fabric Lakehouse

Fabric provides two distinct SQL endpoints for analytics, and choosing the right one matters for your architecture.

	Fabric Warehouse	Fabric Lakehouse SQL Endpoint
Write method	T-SQL INSERT, COPY INTO, pipelines	Spark (PySpark, SQL), notebooks
DDL support	Full T-SQL DDL — CREATE, ALTER, DROP	Read-only SQL — tables managed by Spark
Transaction support	Full ACID transactions	ACID via Delta, no explicit transactions
Best for	Gold dimensional models, SQL-first teams, BI workloads	Silver and Bronze, Spark-heavy pipelines, ML
Power BI Direct Lake	Supported	Supported

Gold Layer DDL — Fabric Warehouse

-- Fabric Warehouse: T-SQL Gold layer setup
-- Familiar SQL Server / Synapse syntax

-- Create Gold schema
CREATE SCHEMA gold;
GO

-- Dimension table: standard T-SQL with IDENTITY surrogate key
CREATE TABLE gold.DimCustomer (
    CustomerKey         BIGINT          IDENTITY(1,1)  NOT NULL,
    CustomerNaturalKey  VARCHAR(100)    NOT NULL,
    CustomerName        VARCHAR(200),
    Email               VARCHAR(200),
    City                VARCHAR(100),
    StateProvince       VARCHAR(100),
    Country             VARCHAR(100),
    LoyaltyTier         VARCHAR(50),
    EffectiveFrom       DATE            NOT NULL,
    EffectiveTo         DATE            NOT NULL,
    IsCurrent           TINYINT         NOT NULL,
    CONSTRAINT PK_DimCustomer PRIMARY KEY NONCLUSTERED (CustomerKey)
);

-- Fact table
CREATE TABLE gold.FctOrderLine (
    OrderLineKey     BIGINT          IDENTITY(1,1)  NOT NULL,
    OrderNaturalKey  VARCHAR(100)    NOT NULL,
    OrderDateKey     INT             NOT NULL,
    CustomerKey      BIGINT          NOT NULL,
    ProductKey       BIGINT          NOT NULL,
    StoreKey         BIGINT          NOT NULL,
    Quantity         INT             NOT NULL,
    UnitPrice        DECIMAL(18,2)   NOT NULL,
    DiscountAmount   DECIMAL(18,2)   NOT NULL  DEFAULT 0,
    ExtendedAmount   DECIMAL(18,2)   NOT NULL,
    CONSTRAINT PK_FctOrderLine PRIMARY KEY NONCLUSTERED (OrderLineKey)
);

Loading Data — COPY INTO

-- Fabric Warehouse: COPY INTO from OneLake files (fastest bulk load method)
COPY INTO gold.DimCustomer (
    CustomerNaturalKey, CustomerName, Email,
    City, StateProvince, Country, LoyaltyTier,
    EffectiveFrom, EffectiveTo, IsCurrent
)
FROM 'https://onelake.dfs.fabric.microsoft.com/workspace/lakehouse.Lakehouse/Tables/gold_dim_customer/'
WITH (
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Managed Identity')
);

-- Verify load
SELECT COUNT(*) AS RowCount FROM gold.DimCustomer;
SELECT TOP 10 * FROM gold.DimCustomer ORDER BY CustomerKey DESC;

Connecting Power BI — Direct Lake Mode

-- Fabric Warehouse: Power BI Direct Lake connection
-- In Power BI Desktop or Fabric Portal:
-- 1. New report → OneLake data hub → Select your Fabric Warehouse
-- 2. Choose tables: gold.DimCustomer, gold.DimDate, gold.FctOrderLine
-- 3. Build relationships in model view (CustomerKey, DateKey)
-- 4. Create DAX measures:

-- Revenue measure (DAX):
-- Revenue = SUM(FctOrderLine[ExtendedAmount])

-- Direct Lake mode: Power BI reads from OneLake Delta files directly
-- No data import, no scheduled refresh required
-- Report always reflects current Gold layer data

-- Check Direct Lake mode is active:
-- In Power BI service: dataset settings → Storage mode should show "Direct Lake"

BigQuery is a fully serverless columnar warehouse. There are no clusters, no virtual warehouses, and no infrastructure to configure. You create tables and run queries. BigQuery charges per query based on bytes scanned — making partitioning and clustering not just a performance optimization but a cost control mechanism. A query on an unpartitioned 10 TB table scans 10 TB and costs roughly $50. The same query on a date-partitioned table that filters to one day scans 28 GB and costs $0.14.

Gold Layer DDL — BigQuery

-- BigQuery: Gold layer with partitioning and clustering
-- Dataset (equivalent to schema) must be created first in the console or via API

-- Dimension table: no partitioning needed (small table)
CREATE TABLE `project.gold.dim_customer`
(
    customer_key         INT64,
    customer_natural_key STRING   NOT NULL,
    customer_name        STRING,
    email                STRING,
    city                 STRING,
    state_province       STRING,
    country              STRING,
    loyalty_tier         STRING,
    effective_from       DATE     NOT NULL,
    effective_to         DATE     NOT NULL,
    is_current           INT64    NOT NULL
)
OPTIONS (description = 'Customer dimension with SCD Type 2 history');

-- Fact table: partition by date, cluster by most common join columns
CREATE TABLE `project.gold.fct_order_line`
(
    order_line_key    INT64,
    order_natural_key STRING        NOT NULL,
    order_date        DATE          NOT NULL,
    order_date_key    INT64         NOT NULL,
    customer_key      INT64         NOT NULL,
    product_key       INT64         NOT NULL,
    store_key         INT64         NOT NULL,
    quantity          INT64         NOT NULL,
    unit_price        NUMERIC,
    discount_amount   NUMERIC,
    extended_amount   NUMERIC
)
PARTITION BY order_date                    -- partition pruning on date filters
CLUSTER BY customer_key, product_key       -- clustering reduces scan within partitions
OPTIONS (
    partition_expiration_days = NULL,      -- no expiration -- keep all history
    description = 'Order line fact table'
);

Cost Management — Query Validation

-- Always validate bytes scanned before running expensive queries
-- In BigQuery console: "This query will process X bytes" shown before execution
-- Use INFORMATION_SCHEMA to analyze table and partition sizes

-- Check partition sizes for the fact table
SELECT
    partition_id,
    total_rows,
    ROUND(total_logical_bytes / POW(1024,3), 2)   AS size_gb,
    last_modified_time
FROM `project.gold.INFORMATION_SCHEMA.PARTITIONS`
WHERE table_name = 'fct_order_line'
ORDER BY partition_id DESC
LIMIT 30;

-- Estimate query cost before running (use dry run)
-- In BigQuery CLI:
-- bq query --dry_run "SELECT SUM(extended_amount) FROM project.gold.fct_order_line
--   WHERE order_date BETWEEN '2025-01-01' AND '2025-12-31'"
-- Outputs: "Query successfully validated. Assuming the tables are not modified,
--   running this query will process 2.1 GB of data."

BigQuery BI Engine — Sub-Second BI Queries

-- BigQuery BI Engine caches frequently queried data in fast in-memory storage
-- Enables sub-second response times for Looker Studio and Looker dashboards
-- Enable via the BigQuery console: BI Engine → Create Reservation

-- After enabling BI Engine, queries from connected BI tools automatically
-- use the in-memory cache when the data fits
-- Monitor BI Engine usage:
SELECT
    job_id,
    bi_engine_statistics.bi_engine_mode,
    bi_engine_statistics.bi_engine_reasons,
    total_bytes_processed,
    total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
AND bi_engine_statistics.bi_engine_mode != 'DISABLED'
ORDER BY creation_time DESC
LIMIT 20;

Azure Synapse Dedicated SQL Pool is a massively parallel processing (MPP) warehouse that distributes data and computation across multiple compute nodes. Unlike Snowflake or BigQuery which handle physical distribution automatically, Synapse requires you to explicitly define a distribution strategy for every table. Getting distribution right is the single most important performance decision in a Synapse implementation.

Distribution Strategies

Strategy	How It Works	Best For	Avoid When
HASH(column)	Rows distributed by hash of column value — same column value always goes to same node	Large fact tables, large dimensions with frequent joins	Low-cardinality columns, columns with data skew
ROUND_ROBIN	Rows distributed evenly across all nodes regardless of value	Staging tables, tables with no obvious distribution key	Tables that join to other tables frequently — causes data movement
REPLICATE	Full copy of table on every compute node — no data movement on join	Small dimension tables (under 2 GB)	Large tables — replication overhead exceeds benefit

Gold Layer DDL — Synapse Dedicated SQL Pool

-- Synapse: Gold layer with optimal distribution and columnstore indexes
-- Distribution strategy is defined at CREATE TABLE time
-- Cannot be changed without dropping and recreating the table

-- Small dimension: REPLICATE for join-free dimension lookups
CREATE TABLE gold.DimCustomer
(
    CustomerKey         BIGINT          NOT NULL,
    CustomerNaturalKey  VARCHAR(100)    NOT NULL,
    CustomerName        VARCHAR(200),
    Email               VARCHAR(200),
    City                VARCHAR(100),
    StateProvince       VARCHAR(100),
    Country             VARCHAR(100),
    LoyaltyTier         VARCHAR(50),
    EffectiveFrom       DATE            NOT NULL,
    EffectiveTo         DATE            NOT NULL,
    IsCurrent           TINYINT         NOT NULL
)
WITH (
    DISTRIBUTION = REPLICATE,          -- full copy on every node -- fast dimension joins
    CLUSTERED COLUMNSTORE INDEX        -- columnar compression for analytics
);

-- Large fact table: HASH on most common join column
-- Match distribution key to the largest dimension you join to most frequently
CREATE TABLE gold.FctOrderLine
(
    OrderLineKey     BIGINT          NOT NULL,
    OrderNaturalKey  VARCHAR(100)    NOT NULL,
    OrderDateKey     INT             NOT NULL,
    CustomerKey      BIGINT          NOT NULL,     -- HASH on this column
    ProductKey       BIGINT          NOT NULL,
    StoreKey         BIGINT          NOT NULL,
    Quantity         INT             NOT NULL,
    UnitPrice        DECIMAL(18,2)   NOT NULL,
    DiscountAmount   DECIMAL(18,2)   NOT NULL,
    ExtendedAmount   DECIMAL(18,2)   NOT NULL
)
WITH (
    DISTRIBUTION = HASH(CustomerKey),  -- distribute by CustomerKey
    CLUSTERED COLUMNSTORE INDEX        -- columnar storage
);

Checking Distribution Quality

-- Verify distribution is even -- skew causes some nodes to do much more work
-- This query shows row count per distribution bucket (60 buckets in Synapse)
SELECT
    pdw_node_id,
    distribution_id,
    rows,
    data_space_used_mb
FROM sys.dm_pdw_nodes_db_partition_stats
WHERE object_id = OBJECT_ID('gold.FctOrderLine')
ORDER BY rows DESC;
-- Ideal: all distributions have similar row counts
-- Red flag: one distribution has 10x more rows than others = data skew
-- Fix: choose a different distribution key with better cardinality

-- Check for data movement in queries (data movement = performance problem)
EXPLAIN
SELECT d.LoyaltyTier, SUM(f.ExtendedAmount)
FROM gold.FctOrderLine f
JOIN gold.DimCustomer d ON f.CustomerKey = d.CustomerKey
GROUP BY d.LoyaltyTier;
-- Look for "BroadcastMoveOperation" or "ShuffleMoveOperation" in the plan
-- These indicate data is being moved between nodes -- expensive
-- Eliminate by ensuring joined tables share the same distribution key

Amazon Redshift is AWS’s MPP columnar warehouse. RA3 nodes separate compute and storage — compute nodes can be scaled independently of the managed storage in Amazon S3 (via Redshift Managed Storage). Redshift Spectrum extends the warehouse by enabling direct SQL queries against S3 data without loading it into Redshift, creating a natural hybrid warehouse-plus-lake architecture.

Distribution and Sort Keys

Like Synapse, Redshift requires explicit distribution and sort key decisions at table creation. Distribution style controls how rows are spread across compute nodes. Sort key controls the physical ordering of rows on disk, which enables efficient range scans.

Gold Layer DDL — Redshift

-- Redshift: Gold layer with distribution and sort keys

-- Small dimension: ALL distribution (equivalent to Synapse REPLICATE)
CREATE TABLE gold.dim_customer (
    customer_key         BIGINT          NOT NULL ENCODE AZ64,
    customer_natural_key VARCHAR(100)    NOT NULL ENCODE ZSTD,
    customer_name        VARCHAR(200)    ENCODE ZSTD,
    email                VARCHAR(200)    ENCODE ZSTD,
    city                 VARCHAR(100)    ENCODE ZSTD,
    state_province       VARCHAR(100)    ENCODE ZSTD,
    country              VARCHAR(100)    ENCODE ZSTD,
    loyalty_tier         VARCHAR(50)     ENCODE ZSTD,
    effective_from       DATE            NOT NULL ENCODE AZ64,
    effective_to         DATE            NOT NULL ENCODE AZ64,
    is_current           SMALLINT        NOT NULL ENCODE AZ64
)
DISTSTYLE ALL                    -- copy to all nodes for fast joins
SORTKEY (customer_natural_key);  -- sorted for efficient natural key lookups

-- Large fact table: DISTKEY on highest-cardinality join column
CREATE TABLE gold.fct_order_line (
    order_line_key   BIGINT          NOT NULL ENCODE AZ64,
    order_natural_key VARCHAR(100)   NOT NULL ENCODE ZSTD,
    order_date_key   INT             NOT NULL ENCODE AZ64,
    customer_key     BIGINT          NOT NULL ENCODE AZ64,
    product_key      BIGINT          NOT NULL ENCODE AZ64,
    store_key        BIGINT          NOT NULL ENCODE AZ64,
    quantity         INT             NOT NULL ENCODE AZ64,
    unit_price       DECIMAL(18,2)   NOT NULL ENCODE AZ64,
    discount_amount  DECIMAL(18,2)   NOT NULL ENCODE AZ64,
    extended_amount  DECIMAL(18,2)   NOT NULL ENCODE AZ64
)
DISTKEY (customer_key)                         -- co-locate with dim_customer ALL distribution
COMPOUND SORTKEY (order_date_key, customer_key); -- sort for date-range + customer queries

Redshift Spectrum — Query S3 Bronze Directly

-- Redshift Spectrum: query S3 Bronze/Silver data without loading it into Redshift
-- Create an external schema pointing to your S3 data

CREATE EXTERNAL SCHEMA bronze_s3
FROM DATA CATALOG
DATABASE 'bronze'
IAM_ROLE 'arn:aws:iam::account:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

-- Create external table mapping to Bronze Parquet files on S3
CREATE EXTERNAL TABLE bronze_s3.crm_customer (
    customer_id     VARCHAR(100),
    customer_name   VARCHAR(200),
    email           VARCHAR(200),
    city            VARCHAR(100),
    updated_at      TIMESTAMP
)
STORED AS PARQUET
LOCATION 's3://your-bucket/bronze/crm/customer/'
TABLE PROPERTIES ('has_encrypted_data' = 'false');

-- Now join Gold (in Redshift) to Bronze (in S3) in a single query
-- Useful for backfill, audit, and historical analysis without data movement
SELECT
    g.customer_name    AS current_name,
    b.customer_name    AS original_name,
    g.loyalty_tier
FROM gold.dim_customer g
JOIN bronze_s3.crm_customer b
    ON g.customer_natural_key = b.customer_id
WHERE g.is_current = 1
AND g.customer_name <> b.customer_name;  -- find customers whose name changed

8 Warehouse Sizing and Concurrency Intermediate

Warehouse size affects query execution time linearly on most platforms — doubling the compute roughly halves execution time for CPU-bound queries. But sizing decisions also affect cost. The goal is the smallest warehouse that meets your SLA requirements, not the largest warehouse that makes queries as fast as possible.

Platform	Compute Unit	Scaling Model	Concurrency Approach
Snowflake	Virtual Warehouse (XS → 6XL)	Manual or auto-scale per warehouse	Multi-cluster warehouse scales horizontally for concurrent users
Fabric Warehouse	Capacity Units (CUs)	Fabric capacity auto-scales within purchased limits	Workload groups manage resource allocation between pipelines and BI
BigQuery	Slots (100 per job default)	Serverless auto-scale (on-demand) or reserved slots	Slot reservations with workload management for enterprise
Synapse	Data Warehouse Units (DWUs)	Manual scaling (pause/resume)	Workload groups and classifiers manage concurrency
Redshift	Node type and count	Resize cluster (RA3 elastic resize)	Concurrency scaling adds clusters automatically for burst workloads

Sizing Guidance

• Start small and measure. Begin with the smallest size that completes your critical queries within SLA. Measure actual execution times with realistic data volumes before committing to a larger size.
• Separate ETL and BI compute. On Snowflake, use separate virtual warehouses for ETL and BI. This prevents a large pipeline load from degrading dashboard query response times for users. The same principle applies on BigQuery with reservation assignments and on Synapse with workload groups.
• Auto-suspend aggressively. On Snowflake, set auto-suspend to 60–120 seconds for most warehouses. A suspended warehouse resumes in 1–5 seconds. Running warehouses idle costs real money at no benefit.

9 Connecting BI Tools to the Warehouse Beginner

Every major warehouse exposes a standard ODBC/JDBC endpoint that all BI tools can connect to. The connection details vary by platform but the process is the same: install the driver, provide the endpoint, authenticate, and select tables.

Platform	Connection String Format	Authentication
Snowflake	account.snowflakecomputing.com	Username/password, SSO, key-pair
Fabric Warehouse	workspace.sql.fabric.microsoft.com	Azure Active Directory / Entra ID
BigQuery	Project ID via BigQuery connector	GCP Service Account or OAuth
Synapse	server.sql.azuresynapse.net	Azure Active Directory or SQL auth
Redshift	cluster.region.redshift.amazonaws.com:5439	IAM or database username/password

10 Platform Comparison Matrix Beginner

Feature	Snowflake	Fabric Warehouse	BigQuery	Synapse	Redshift
Storage model	Micro-partitions (auto)	Delta on OneLake	Colossus (auto)	Columnstore pages	Columnar slices
Physical design effort	Low — mostly automatic	Low — auto columnstore	Medium — partition/cluster required	High — distribution keys manual	High — dist + sort keys manual
Compute scaling	On-demand, per warehouse	Auto within capacity	Fully serverless auto	Manual pause/resume	Manual resize + concurrency scaling
Pricing model	Per compute-second + storage	Capacity units (CUs)	Per byte scanned or slots	Per DWU-hour	Per node-hour + managed storage
T-SQL compatibility	Snowflake SQL dialect	Full T-SQL	Standard SQL dialect	Full T-SQL	PostgreSQL dialect
Power BI integration	DirectQuery or import	Native Direct Lake	DirectQuery or import	DirectQuery or import	DirectQuery or import
Open format storage	Iceberg (optional)	Delta (native)	BigLake external tables	Proprietary	Spectrum + S3 (external)

11 Workshops

References

• Snowflake — Key Architecture Concepts
• Snowflake — Clustering Keys
• Snowflake — Micro-Partitions
• Snowflake — Virtual Warehouses
• Microsoft Fabric — Data Warehousing Overview
• Microsoft Fabric — COPY INTO
• Microsoft — Power BI Direct Lake Mode
• BigQuery — Partitioned Tables
• BigQuery — Clustered Tables
• BigQuery — BI Engine
• Azure Synapse — SQL Best Practices
• Azure Synapse — MPP Architecture
• Amazon Redshift — Distribution Key Best Practices
• Amazon Redshift — Sort Key Best Practices
• Amazon Redshift Spectrum — Querying S3