12.3 Analytics & BI Optimization

Optimizing Analytics and BI Workloads

The ultimate goal of the Lakehouse is to serve data to business users and analysts quickly and efficiently. The Gold layer is consumed primarily via Databricks SQL (DB SQL). Understanding how to configure compute, leverage Unity Catalog features, and utilize caching is critical for optimizing BI performance.

Databricks SQL Warehouses

DB SQL provides optimized compute environments specifically designed for executing SQL queries. There are distinct types of SQL Warehouses available:

Classic vs. Serverless SQL Warehouses

• Classic SQL Warehouses: These run in the customer's cloud account (data plane). The customer is responsible for network configuration, and the compute resources can take several minutes to start up.
• Serverless SQL Warehouses: The compute is managed entirely by Databricks in their serverless data plane. The defining feature is near-instant startup time. This eliminates the need to keep warehouses running idly just to avoid startup latency, resulting in significant cost savings.

Auto-Stop and Scaling

SQL Warehouses can be configured with an Auto-Stop duration (e.g., stop after 10 minutes of inactivity). With Serverless, this duration can be set very aggressively because the penalty for restarting is minimal.

For concurrency, SQL Warehouses use cluster scaling. You configure a minimum and maximum number of clusters. When concurrent queries exceed the capacity of the running clusters, Databricks automatically provisions additional clusters up to the maximum limit to handle the load.

Materialized Views vs. Streaming Tables

Delta Live Tables (DLT) and Databricks SQL introduce two declarative table types in Unity Catalog that simplify data engineering and accelerate BI.

Streaming Tables

A Streaming Table processes data incrementally. It is designed to ingest data from an append-only source (like Kafka or cloud storage) and compute the results exactly once per record. It is ideal for the Bronze-to-Silver transition.

Materialized Views (MVs)

A Materialized View is a precomputed result set of a query. MVs are designed to accelerate BI workloads. When the underlying data changes, the MV must be refreshed. Databricks automatically determines the most efficient way to refresh the MV—incrementally if possible, or via a full recompute if necessary. MVs are heavily utilized in the Gold layer to pre-calculate complex joins and aggregations for dashboards.

Primary Key and Foreign Key Constraints

Unity Catalog allows defining Primary Key (PK) and Foreign Key (FK) constraints on Delta tables.

Informational Constraints

Unlike traditional RDBMS systems, Databricks does not enforce these constraints during write operations. If you insert a duplicate primary key, the system will not throw an error. These are purely informational constraints designed to document relationships and, more importantly, to assist the Catalyst query optimizer.

The RELY Keyword

To extract the maximum performance benefit, engineers use the RELY keyword when defining constraints.

-- Adding a Primary Key constraint with RELY
ALTER TABLE gold.dim_customers ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id) RELY;

-- Adding a Foreign Key constraint with RELY
ALTER TABLE gold.fact_sales ADD CONSTRAINT fk_customer_id FOREIGN KEY (customer_id) REFERENCES gold.dim_customers(customer_id) RELY;

When the RELY keyword is present, you are promising the Catalyst optimizer that the data is guaranteed to obey the constraint (because you enforced it upstream during ETL). The optimizer can then use this information to perform Join Elimination. If a BI query joins a fact table to a dimension table but only selects columns from the fact table, the optimizer can completely skip the join operation, drastically reducing query execution time.

Query Caching Strategies

Databricks SQL employs multiple layers of caching to accelerate repeated queries, which is common in BI dashboard interactions.

1. Result Cache

The Result Cache stores the final output of a completed SQL query. If the exact same query is executed again, and the underlying data has not changed, Databricks returns the result directly from the Result Cache. This bypasses the SQL warehouse compute entirely, resulting in sub-second response times.

2. Disk Cache (formerly Delta Cache)

The Disk Cache stores copies of remote data (from cloud object storage) on the fast local NVMe SSDs of the SQL Warehouse compute nodes.

• When a query scans a Parquet file, the data is pulled from cloud storage and simultaneously written to the local Disk Cache.
• Subsequent queries that need to scan the same file blocks will read them from the local SSD, avoiding the network latency of querying cloud storage.
• The Disk Cache operates below the query level, meaning different queries scanning the same underlying data blocks will benefit from the cache.

By leveraging Serverless SQL, defining RELY constraints, and understanding caching mechanics, data engineers can ensure the Lakehouse delivers lightning-fast performance to end-users.