Deep Technical Series — Building a Modern Data Warehouse and Lakehouse

Part 12 — Performance Engineering and Optimization

---

Once your warehouse or lakehouse architecture is in place and data is flowing reliably, performance engineering becomes the difference between a platform that users trust and one they avoid. Slow dashboards erode confidence. Expensive queries inflate costs without delivering value. Pipelines that take four hours when they should take forty minutes delay decisions and miss SLAs.

Performance in modern data platforms is impacted by six interdependent areas: data modeling, storage format and physical organization, partitioning strategy, file management and compaction, compute configuration, and query patterns. This part covers all six with working examples across Snowflake, BigQuery, Databricks, Fabric, Synapse, and Redshift.

1 Performance Starts With the Model Beginner

The single most impactful performance decision you make is the dimensional model design from Part 1. A well-designed star schema — narrow fact tables, clean dimension tables, integer surrogate keys, no unnecessary joins — is the foundation that all other performance techniques build on. Physical tuning can recover some performance from a bad model, but it cannot fully compensate for a poorly designed one.

Modeling Choices That Directly Impact Performance

• Integer surrogate keys over string or UUID keys. Joining two BIGINT columns is dramatically faster than joining two VARCHAR or UUID columns at billion-row scale. Columnar engines compress integer columns far more efficiently than string columns, and join operations on integers require less memory and fewer CPU cycles.
• Narrow fact tables. A fact table with 8–12 columns scans in a fraction of the time of one with 50+ columns. Every descriptive attribute on the fact table that belongs in a dimension adds unnecessary scan overhead to every query.
• Conformed dimensions over denormalized wide tables. A single wide table with all dimension attributes pre-joined to the fact seems convenient but creates a massive, poorly-compressing table that scans slowly. Star schema joins in columnar engines are extremely fast — keep dimensions separate.
• Correct grain definition. A fact table at the wrong grain (too detailed or too aggregated) forces every query to work harder than necessary. Define grain precisely in Part 1 and maintain it through all transformations.

References: Power BI Modeling Best Practices · dbt Modeling Guide

2 Storage Format and Columnar Compression Beginner

Modern warehouses and lakehouses store data in columnar format with compression applied per column. Because similar values are stored together in columnar format, compression ratios are dramatically better than row-oriented storage. A fact table column containing order amounts compresses to a fraction of its uncompressed size because sequential similar numbers compress extremely well.

Format	Compression	ACID	Time Travel	Best For
Parquet	Snappy / ZSTD / GZIP	No	No	Bronze, universal compatibility
Delta Lake	ZSTD (default)	Full ACID	Version + timestamp	Silver and Gold on Databricks/Fabric
Apache Iceberg	ZSTD / Snappy	Snapshot isolation	Snapshot + timestamp	Multi-engine lakehouses, Snowflake
Snowflake internal	Proprietary columnar	Full ACID	Up to 90 days	Snowflake managed tables
BigQuery Capacitor	Proprietary columnar	DML support	7 days snapshots	BigQuery native tables

3 Partitioning Strategy — Reduce What You Scan Intermediate

Partitioning divides a large table into smaller physical segments based on column values. When a query filters on a partitioned column, the engine reads only the relevant partitions — skipping everything else. At billion-row scale, good partitioning is the difference between a 2-second query and a 20-minute one. It also directly reduces cost on byte-scanned platforms like BigQuery.

Partition Column Selection Rules

• Partition by date on fact tables. Order date, event date, and ingestion date are the most commonly filtered columns in analytical queries. Date partitioning gives the largest pruning benefit on large fact tables.
• Target 128 MB to 1 GB per partition. Partitions smaller than 128 MB create excessive metadata overhead. Partitions larger than 1 GB reduce parallelism. Monthly partitions on most fact tables hit this sweet spot naturally.
• Never partition on high-cardinality columns. Partitioning on customer ID or product ID creates millions of tiny partitions. Metadata management becomes the bottleneck and query performance degrades rather than improves.
• Never partition on Boolean or low-cardinality columns. A two-value partition (is_active = TRUE / FALSE) provides almost no pruning benefit and adds overhead for no gain.

-- Partitioning examples across platforms

-- Databricks: partition fact table by month at creation
CREATE TABLE prod.gold.fct_order_line (
    order_line_key  BIGINT NOT NULL,
    order_date      DATE   NOT NULL,
    customer_key    BIGINT NOT NULL,
    extended_amount DECIMAL(18,2) NOT NULL
)
USING DELTA
PARTITIONED BY (YEAR(order_date), MONTH(order_date))
-- Or use Liquid Clustering instead (recommended for new Databricks tables):
-- CLUSTER BY (order_date, customer_key)

-- BigQuery: partition by DATE column with clustering
CREATE TABLE `project.gold.fct_order_line`
(
    order_line_key  INT64,
    order_date      DATE   NOT NULL,
    customer_key    INT64  NOT NULL,
    extended_amount NUMERIC
)
PARTITION BY order_date
CLUSTER BY customer_key, product_key;

-- Snowflake: automatic micro-partitioning -- add cluster key for large tables
ALTER TABLE gold.fct_order_line
CLUSTER BY (order_date_key, customer_key);

-- Check Snowflake clustering depth (lower = better)
SELECT SYSTEM$CLUSTERING_INFORMATION('gold.fct_order_line');

-- Redshift: sort key for range scan optimization
CREATE TABLE gold.fct_order_line (
    order_date_key  INT    NOT NULL,
    customer_key    BIGINT NOT NULL,
    extended_amount DECIMAL(18,2) NOT NULL
)
DISTKEY (customer_key)
COMPOUND SORTKEY (order_date_key, customer_key);  -- sort by date first, then customer

Verifying Partition Pruning Is Working

-- BigQuery: verify partition pruning with dry run
-- Run this in BigQuery CLI before executing expensive queries
bq query --dry_run --use_legacy_sql=false \
  "SELECT SUM(extended_amount) FROM project.gold.fct_order_line
   WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31'"
-- Output: "This query will process 2.1 GB"
-- Without partitioning: "This query will process 847 GB"

-- Databricks: check partition pruning in query explain plan
EXPLAIN FORMATTED
SELECT SUM(extended_amount)
FROM prod.gold.fct_order_line
WHERE order_date >= '2025-01-01'
AND   order_date <  '2025-04-01';
-- Look for: PartitionFilters in the explain output
-- PartitionFilters: [(order_date >= 2025-01-01) AND (order_date < 2025-04-01)]
-- Files read: 92 out of 1,460 total -- pruning eliminated 94% of files

-- Snowflake: check bytes scanned vs bytes partitioned
SELECT
    query_id,
    bytes_scanned,
    bytes_partitions_scanned,
    ROUND(100.0 * bytes_partitions_scanned / NULLIF(bytes_scanned, 0), 1) AS pct_partitions
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_text ILIKE '%fct_order_line%'
ORDER BY start_time DESC
LIMIT 10;

4 Clustering, Sorting, and Z-Order — Optimize Within Partitions Intermediate

Partitioning determines which partitions to skip. Clustering determines which files within a partition to skip. Together they enable the engine to find the relevant data with minimal I/O at both levels. On large tables that receive millions of rows daily, clustering is as important as partitioning.

Databricks Z-ORDER and Liquid Clustering

-- Z-ORDER: co-locates related data in fewer files based on multiple columns
-- Improves file skipping for queries that filter on those columns
-- Run after bulk loads or weekly maintenance

OPTIMIZE prod.gold.fct_order_line
ZORDER BY (customer_key, order_date_key);
-- After Z-ORDER: queries filtering on customer_key or order_date_key
-- read far fewer files because related rows are co-located

-- Liquid Clustering: the modern replacement for Z-ORDER on new tables (DBR 13.3+)
-- Auto-incremental -- no manual OPTIMIZE needed
-- Enable at CREATE TABLE time:
CREATE TABLE prod.gold.fct_order_line (...)
USING DELTA
CLUSTER BY (order_date, customer_key);
-- Databricks automatically re-clusters as new data arrives

-- Check clustering effectiveness
DESCRIBE DETAIL prod.gold.fct_order_line;
-- clusteringColumns: if liquid clustering, shows which columns
-- numFiles: lower is better -- Z-ORDER reduces file count

Snowflake Clustering Keys

-- Snowflake: clustering key improves micro-partition pruning
-- Most effective on tables > 1 TB or queries with highly selective filters

ALTER TABLE gold.fct_order_line
CLUSTER BY (order_date_key, customer_key);

-- Verify clustering provides benefit
SELECT SYSTEM$CLUSTERING_INFORMATION(
    'gold.fct_order_line',
    '(order_date_key, customer_key)'
);
-- Key metrics:
-- average_overlaps: lower = better clustering (< 1.0 is excellent)
-- average_depth: lower = better (< 3 is good, > 6 means re-cluster needed)
-- notes: Snowflake recommendation on whether to cluster this table

Synapse Distribution Keys — Minimize Data Movement

-- Synapse: co-located joins eliminate expensive data movement between nodes
-- Fact and its most-joined dimension should share the same distribution key

-- If FctOrderLine and DimCustomer both distribute on customer_key,
-- joins between them happen locally on each node -- no shuffle needed

-- Verify no data movement in query plans
EXPLAIN
SELECT c.loyalty_tier, SUM(f.extended_amount)
FROM gold.FctOrderLine f
JOIN gold.DimCustomer  c ON f.customer_key = c.customer_key
GROUP BY c.loyalty_tier;
-- Good plan: "BroadcastMoveOperation" on the small DimCustomer (replicated)
-- Bad plan:  "ShuffleMoveOperation" on the large FctOrderLine

-- Check for data skew (uneven distribution = some nodes much slower than others)
SELECT
    distribution_id,
    rows
FROM sys.dm_pdw_nodes_db_partition_stats
WHERE object_id = OBJECT_ID('gold.FctOrderLine')
ORDER BY rows DESC;
-- All distributions should have similar row counts (+/- 20%)

5 File Engineering — Solving the Small File Problem Intermediate

The small file problem is the most common performance issue in lakehouses. Every incremental pipeline run appends a small batch of files. After weeks of daily loads, a Silver table that should have 100 files has 10,000. Planning overhead, metadata reads, and task scheduling overhead per file make queries dramatically slower than they should be.

-- Databricks: diagnose and fix the small file problem

-- Check current file count and average size
DESCRIBE DETAIL prod.gold.fct_order_line;
-- numFiles: 4,821
-- sizeInBytes: 48,210,000,000  (48 GB)
-- Average file size: 48GB / 4821 = ~10 MB -- small file problem

-- Fix: OPTIMIZE compacts small files into larger ones
OPTIMIZE prod.gold.fct_order_line;
-- After OPTIMIZE: numFiles: 48, average size: ~1 GB -- optimal

-- Enable auto-optimization to prevent small files from accumulating:
ALTER TABLE prod.gold.fct_order_line
SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',  -- coalesce small writes at write time
    'delta.autoOptimize.autoCompact'   = 'true'   -- background compaction after writes
);

-- VACUUM: remove old file versions to reclaim storage
-- Always run VACUUM after OPTIMIZE -- OPTIMIZE leaves old files for time travel
VACUUM prod.gold.fct_order_line RETAIN 168 HOURS;  -- keep 7 days for time travel

# AWS: compact small Parquet files using Spark on EMR or Glue
from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# Read all small files
df = spark.read.parquet("s3://lake/silver/crm/customer/")

# Calculate target file count (aim for ~512 MB per file)
total_bytes  = df.rdd.map(lambda x: len(str(x))).sum()
target_count = max(1, int(total_bytes / (512 * 1024 * 1024)))

# Compact and overwrite
(
    df.repartition(target_count)
    .write
    .mode("overwrite")
    .parquet("s3://lake/silver/crm/customer/")
)
print(f"Compacted to {target_count} files")

6 Warehouse Compute Tuning by Platform Intermediate

Snowflake Virtual Warehouse Sizing

-- Snowflake: right-size virtual warehouses for different workload types
-- Larger warehouses run queries faster but cost more per query
-- The goal: smallest warehouse that meets SLA requirements

-- Check if queries are queuing (warehouse too small for concurrent load)
SELECT
    warehouse_name,
    queued_load,      -- queries waiting for warehouse capacity
    running,          -- queries currently executing
    blocked           -- queries blocked on locks
FROM TABLE(INFORMATION_SCHEMA.WAREHOUSE_LOAD_HISTORY(
    DATE_RANGE_START => DATEADD('hour', -1, CURRENT_TIMESTAMP())
))
ORDER BY start_time DESC;

-- If queued_load is consistently > 0: consider larger warehouse or multi-cluster
-- If running / warehouse_size ratio is low: consider smaller warehouse

-- Separate ETL and BI workloads to prevent interference
ALTER WAREHOUSE bi_warehouse SET
    WAREHOUSE_SIZE     = 'SMALL'
    MIN_CLUSTER_COUNT  = 1
    MAX_CLUSTER_COUNT  = 3      -- auto-scale for concurrent BI users
    SCALING_POLICY     = 'ECONOMY';

ALTER WAREHOUSE etl_warehouse SET
    WAREHOUSE_SIZE     = 'LARGE'    -- larger for heavy transformation runs
    MIN_CLUSTER_COUNT  = 1
    MAX_CLUSTER_COUNT  = 1          -- no multi-cluster needed for sequential ETL
    AUTO_SUSPEND       = 120;       -- suspend after 2 minutes idle

BigQuery: Slot Management

-- BigQuery: on-demand vs reserved slots
-- On-demand: charged per byte scanned -- best for intermittent workloads
-- Reservations: committed slot capacity -- best for consistent high-volume workloads

-- Monitor slot utilization to decide between on-demand and reserved
SELECT
    DATE(creation_time)        AS query_date,
    SUM(total_slot_ms) / 1000  AS total_slot_seconds,
    COUNT(*)                   AS query_count,
    SUM(total_bytes_processed) / POW(1024,4) AS total_tb_scanned
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
AND job_type = 'QUERY'
GROUP BY 1
ORDER BY 1 DESC;

-- If consistently using > 500 slot-hours per day, reservations may be cheaper
-- Reservation break-even: ~$5,000/month in on-demand vs flat rate for 500 slots

Databricks: Photon and SQL Warehouse

-- Databricks SQL Warehouse: Photon execution engine for fast analytics
-- Photon is a vectorized C++ execution engine -- 3-5x faster than JVM Spark for SQL

-- Create SQL Warehouse with Photon enabled (default for SQL Warehouses)
-- Via Databricks UI: SQL → SQL Warehouses → Create
-- Or via REST API:
POST /api/2.0/sql/warehouses
{
    "name":        "production_bi_warehouse",
    "cluster_size": "Medium",
    "auto_stop_mins": 30,
    "enable_photon":  true,   -- Photon enabled by default, confirm explicitly
    "warehouse_type": "PRO",  -- PRO tier required for Photon and serverless
    "min_num_clusters": 1,
    "max_num_clusters": 3     -- scale for concurrent users
}

-- Monitor Photon usage and savings
SELECT
    warehouse_id,
    SUM(photon_total_duration_ms) AS photon_ms,
    SUM(fallback_total_duration_ms) AS fallback_ms,
    ROUND(100.0 * SUM(photon_total_duration_ms) /
        (SUM(photon_total_duration_ms) + SUM(fallback_total_duration_ms)), 1)
    AS photon_pct
FROM system.compute.warehouse_query_history
WHERE usage_date >= CURRENT_DATE - 7
GROUP BY 1;

7 Materialized Views and Aggregation Tables Intermediate

Materialized views and aggregation tables pre-compute expensive aggregations and store the results physically. BI queries that would otherwise scan billions of fact rows instead read from a small, pre-aggregated table. This is the highest-impact performance optimization for BI workloads on large fact tables.

-- Snowflake: materialized view for monthly revenue aggregation
-- Automatically refreshed when base tables change
CREATE OR REPLACE MATERIALIZED VIEW gold.mv_revenue_monthly AS
SELECT
    DATE_TRUNC('month', d.full_date) AS revenue_month,
    c.loyalty_tier,
    p.category,
    s.region,
    SUM(f.extended_amount)           AS revenue,
    COUNT(DISTINCT f.customer_key)   AS unique_customers,
    COUNT(*)                         AS order_line_count
FROM gold.fct_order_line    f
JOIN gold.dim_date           d ON f.order_date_key = d.date_key
JOIN gold.dim_customer       c ON f.customer_key   = c.customer_key AND c.is_current = 1
JOIN gold.dim_product        p ON f.product_key    = p.product_key  AND p.is_current = 1
JOIN gold.dim_store          s ON f.store_key      = s.store_key    AND s.is_current = 1
GROUP BY 1, 2, 3, 4;

-- Queries hitting the MV are dramatically faster:
-- Without MV: 45 seconds scanning 2B fact rows
-- With MV:     0.3 seconds reading 8,760 aggregated rows (12 months × 730 combos)

-- BigQuery: materialized view with auto-refresh
CREATE MATERIALIZED VIEW `project.gold.mv_revenue_monthly`
OPTIONS (
    enable_refresh = TRUE,
    refresh_interval_minutes = 60   -- refresh hourly
)
AS
SELECT
    DATE_TRUNC(order_date, MONTH)   AS revenue_month,
    customer_key,
    SUM(extended_amount)            AS revenue,
    COUNT(*)                        AS order_count
FROM `project.gold.fct_order_line`
GROUP BY 1, 2;

-- BigQuery automatically uses the MV for matching queries
-- No query rewrite needed -- the optimizer detects MV applicability

-- Databricks: cached aggregation table (Delta table approach)
-- More control than MVs -- manually refresh on schedule

CREATE OR REPLACE TABLE prod.gold.agg_revenue_monthly AS
SELECT
    DATE_TRUNC('month', f.order_date) AS revenue_month,
    c.loyalty_tier,
    p.category,
    SUM(f.extended_amount)             AS revenue,
    COUNT(DISTINCT f.customer_key)     AS unique_customers
FROM prod.gold.fct_order_line    f
JOIN prod.gold.dim_customer       c ON f.customer_key = c.customer_key AND c.is_current = 1
JOIN prod.gold.dim_product        p ON f.product_key  = p.product_key  AND p.is_current = 1
GROUP BY 1, 2, 3;

-- Schedule refresh in Databricks Workflows after each Gold pipeline run
-- The aggregation table is always current within one pipeline cycle

8 Query Optimization Techniques Advanced

Physical design handles the storage layer. Query design handles how efficiently you retrieve data from that storage. These rules apply across every platform covered in the series.

What to Do

-- 1. Filter early -- push predicates as close to the source as possible
-- BAD: filter after joining (expensive -- joins all rows first)
SELECT c.customer_name, SUM(f.extended_amount)
FROM gold.fct_order_line f
JOIN gold.dim_customer   c ON f.customer_key = c.customer_key
WHERE c.loyalty_tier = 'Gold'  -- filters AFTER the join completes
GROUP BY c.customer_name;

-- GOOD: filter the dimension before joining (reduces join size)
SELECT c.customer_name, SUM(f.extended_amount)
FROM gold.fct_order_line f
JOIN (
    SELECT customer_key, customer_name
    FROM gold.dim_customer
    WHERE loyalty_tier = 'Gold'     -- filter BEFORE the join
    AND   is_current   = 1
) c ON f.customer_key = c.customer_key
GROUP BY c.customer_name;

-- 2. Select only needed columns -- never SELECT *
-- BAD (scans all columns -- defeats columnar storage benefit):
SELECT * FROM gold.fct_order_line WHERE order_date_key = 20250115;

-- GOOD (scans only 3 columns):
SELECT customer_key, product_key, extended_amount
FROM gold.fct_order_line
WHERE order_date_key = 20250115;

-- 3. Use surrogate integer keys for joins, not natural string keys
-- BAD (joins on string -- slower, worse compression):
SELECT * FROM fct_order_line f
JOIN dim_customer c ON f.customer_natural_key = c.customer_natural_key;

-- GOOD (joins on integer surrogate key -- faster, better compression):
SELECT * FROM fct_order_line f
JOIN dim_customer c ON f.customer_key = c.customer_key AND c.is_current = 1;

What to Avoid

-- 1. Avoid non-SARGable predicates -- they prevent index and partition use
-- BAD (function on column prevents partition pruning):
WHERE YEAR(order_date) = 2025 AND MONTH(order_date) = 1

-- GOOD (range predicate on the column directly -- enables pruning):
WHERE order_date >= '2025-01-01' AND order_date < '2025-02-01'

-- 2. Avoid implicit type conversions
-- BAD (comparing INT column to string literal -- forces conversion on every row):
WHERE order_date_key = '20250115'

-- GOOD (matching types -- no conversion overhead):
WHERE order_date_key = 20250115

-- 3. Avoid row-by-row operations -- use set-based SQL
-- BAD (cursor/loop pattern -- 1 million SQL calls for 1 million rows):
FOR each row in source_table:
    INSERT INTO target_table VALUES (row.col1, row.col2...)

-- GOOD (single set-based INSERT):
INSERT INTO target_table
SELECT col1, col2 FROM source_table;

-- 4. In BigQuery: avoid SELECT * and unnecessary CTEs
-- BigQuery charges per byte scanned -- SELECT * scans all columns
-- Each CTE reference re-scans the data -- use temp tables for reused CTEs

9 Cost Optimization Intermediate

On cloud platforms, performance and cost are directly linked — faster queries generally scan less data and therefore cost less. The most effective cost optimizations are also the most effective performance optimizations. But there are cost-specific patterns worth applying regardless of query performance.

Platform	Key Cost Driver	Primary Cost Optimization
Snowflake	Virtual warehouse compute-seconds	Auto-suspend, right-size warehouses, reduce MERGE input size
BigQuery	Bytes scanned per query	Partitioning + clustering, avoid SELECT *, use dry-run before executing
Databricks	DBU (Databricks Unit) consumption per hour	Photon SQL Warehouses, Liquid Clustering reduces OPTIMIZE cost, right-size clusters
Fabric	Capacity Units (CUs) consumed	Direct Lake eliminates import refresh cost, consolidate small pipelines
Synapse	DWU-hours (pause when not in use)	Pause dedicated pool overnight, use serverless SQL for ad-hoc queries
Redshift	Node-hours + Spectrum bytes scanned	RA3 Managed Storage for cold data, Spectrum for archive queries

-- Snowflake: identify expensive queries and optimize them
-- Top 10 most expensive queries in the last 7 days by credits consumed
SELECT
    query_id,
    query_text,
    database_name,
    ROUND(credits_used_cloud_services, 4)     AS credits,
    ROUND(bytes_scanned / POW(1024,3), 2)     AS gb_scanned,
    execution_time / 1000                     AS seconds,
    partitions_scanned,
    partitions_total
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
AND execution_status = 'SUCCESS'
ORDER BY credits DESC
LIMIT 10;

-- BigQuery: identify tables generating the most query cost
SELECT
    referenced_table.table_id           AS table_name,
    COUNT(*)                            AS query_count,
    SUM(total_bytes_processed) / POW(1024,4) AS total_tb_scanned,
    ROUND(SUM(total_bytes_processed) / POW(1024,4) * 5, 2) AS estimated_cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT j,
     UNNEST(referenced_tables) AS referenced_table
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY total_tb_scanned DESC
LIMIT 20;

10 Monitoring Performance — Query Profiles and Execution Plans Intermediate

Every major platform provides query profiling tools that show exactly where time and resources are being spent. Use these tools to diagnose slow queries before applying optimizations — guessing at the bottleneck without profiling often leads to optimizing the wrong thing.

-- Snowflake: Query Profile
-- In Snowflake UI: Activity → Query History → select query → Query Profile
-- Key metrics to look for:
-- Bytes scanned: high = missing clustering or partition pruning not working
-- Spillage to disk: warehouse too small for the operation -- increase size
-- Percentage scanned from cache: high = good (result cache or remote disk cache)
-- Rows produced by step vs rows consumed by next step: large differences = filter pushdown issue

-- Via SQL:
SELECT
    query_id,
    total_elapsed_time,
    bytes_scanned,
    bytes_written,
    partitions_scanned,
    partitions_total,
    ROUND(100.0 * partitions_scanned / NULLIF(partitions_total, 0), 1) AS pct_scanned
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
WHERE query_text ILIKE '%fct_order_line%'
ORDER BY start_time DESC
LIMIT 20;

-- Databricks: explain formatted for query analysis
EXPLAIN FORMATTED
SELECT c.loyalty_tier, SUM(f.extended_amount)
FROM prod.gold.fct_order_line f
JOIN prod.gold.dim_customer   c ON f.customer_key = c.customer_key
WHERE f.order_date >= '2025-01-01'
GROUP BY c.loyalty_tier;

-- Key things to look for in the plan:
-- PartitionFilters: confirms partition pruning is applied
-- PushedFilters: confirms predicates pushed to storage layer
-- BroadcastHashJoin vs SortMergeJoin: Broadcast is faster for small dimensions
-- If SortMergeJoin on a small table: add spark.sql.autoBroadcastJoinThreshold hint

-- Force broadcast join for small dimension tables if auto-detection misses it:
SELECT /*+ BROADCAST(c) */
    c.loyalty_tier,
    SUM(f.extended_amount)
FROM prod.gold.fct_order_line f
JOIN prod.gold.dim_customer c ON f.customer_key = c.customer_key
GROUP BY c.loyalty_tier;

-- BigQuery: execution details for completed jobs
SELECT
    job_id,
    creation_time,
    total_bytes_processed / POW(1024,3)    AS gb_processed,
    total_slot_ms / 1000                   AS slot_seconds,
    total_bytes_billed / POW(1024,3)       AS gb_billed,
    ROUND(total_bytes_billed / POW(1024,4) * 5, 4) AS cost_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
AND job_type = 'QUERY'
ORDER BY total_bytes_processed DESC
LIMIT 20;

11 Performance Summary — The Optimization Checklist Beginner

Apply these in order. Each layer builds on the previous. Start with modeling — no amount of physical tuning recovers a bad model.

Area	Key Action	Impact
1. Modeling	Star schema, integer surrogate keys, narrow fact tables	Very High — foundation of all performance
2. Storage format	Delta or Iceberg for Silver/Gold, ZSTD compression	High — columnar + compression reduces I/O
3. Partitioning	Partition fact tables by date, target 128 MB–1 GB per partition	Very High — eliminates irrelevant partition scans
4. Clustering / Z-Order	Cluster on most common filter columns after partitioning	High — reduces files scanned within partitions
5. File compaction	OPTIMIZE weekly, auto-optimize on write for streaming tables	Medium-High — prevents metadata overhead growth
6. Compute sizing	Separate ETL and BI warehouses, right-size, auto-suspend	Medium — prevents workload interference and waste
7. Materialized views	Pre-aggregate top BI query patterns on large fact tables	Very High for BI — sub-second vs minutes
8. Query patterns	Filter early, no SELECT *, SARGable predicates, set-based SQL	Medium — prevents query-level inefficiency
9. Cost monitoring	Profile top queries weekly, eliminate unnecessary full-table scans	Medium — prevents cost surprises

12 Workshops

References

• Power BI Modeling Best Practices
• dbt — Model Design Guide
• Apache Parquet Documentation
• Databricks — Delta Lake Documentation
• Databricks — Delta File Management
• Databricks — Z-Order Optimization
• Databricks — Liquid Clustering
• Snowflake — Clustering Keys
• Snowflake — Micro-Partitions
• Snowflake — Materialized Views
• BigQuery — Partitioned Tables
• BigQuery — Performance Best Practices
• BigQuery — Materialized Views
• Azure Synapse — SQL Best Practices
• Amazon Redshift — Sort Key Best Practices
• Snowflake — Query Profile