4 Powerful Techniques to Optimize Data Warehouse Design (for Speed, Scale & Smarter Decisions)

Your data warehouse is the engine room of every dashboard, forecast, and executive decision your business makes. But as data volumes explode, a warehouse that was fast last year can grind to a crawl — queries time out, storage costs balloon, and analysts wait minutes for answers they need in seconds. The difference between a warehouse that scales and one that stalls almost always comes down to design.

The four key techniques for designing a data warehouse optimally are:

• Dimensional modeling using star and snowflake schemas
• Data partitioning
• Strategic indexing
• Materialized views with pre-aggregation

Combined, they can significantly reduce query response times and manage their costs.

Let’s take a look at what each does — and what teams do wrong.

1. Dimensional Modeling, including Star and Snowflake Schemas

Familiarize yourself with the model before tuning the first query. While a transactional database is designed to be normalized for minimizing data redundancy, an analytics workload demands a structure geared toward reading large amounts of data quickly. This is where dimensional modeling can help.

In a star schema the central fact table contains the events to be measured (e.g., sales, clicks, shipments), and the other dimension tables contain the context (e.g., customer, product, time, geography). Simple and predictable structure of the system, queries join a fact to a few dimensions and return in quick time. A snowflake schema further normalizes those dimensions into sub-tables, giving up storage for more joins.

Best practice: Most BI workloads should use a star schema — usually the simplicity of the query is worth more than the savings in storage space from snowflaking. Don’t use snowflaking for very large, hierarchical dimensions.

2. Data Partitioning

Partitioning divides up one very large table into smaller more manageable pieces of the table with a key, usually a date range. So when an analyst asks the question “last quarter’s revenue”, the warehouse will only scan the partitions that relate to the last quarter, rather than the full table. This is known as partition pruning, and is one of the most significant optimizations that can be implemented.

Common partitioning strategies:

• Range partitioning (most often used for time-series and warehouse data)
• List partitioning – by discrete values (region, business unit, etc.)
• To avoid hotspots, hash partitioning distributes data evenly across the partition.Hash partitioning distributes data evenly across the partition to avoid hotspots.

Best practice: Partition tables on a date column that is most often used in WHERE clauses. Combine partitioning with a lifecycle policy which allows the automatic archiving or compression of old partitions.

3. Strategic Indexing

Indexes enable the warehouse to jump to the rows it needs and not scan all the rows. Bitmap indexes are particularly useful in an analytics scenario on a low-cardinality column such as “status” or “region” where there are relatively few values in the column, which makes the indexes compact and helps to quickly filter and aggregate the data that is analyzed. B-tree indexes are still a good option for high cardinality columns and range queries.

Best Practice: Index only the columns used in joins and filters not all columns. Data loads are slowed down and storage is increased if you create too many indexes; check periodically on index usage and remove the ones that aren’t used by any query.

4. Materialized Views and Pre-Aggregation

If they are running the same “monthly revenue by region” rollup every morning, there’s no need to re-calculate it from raw rows again. Materialized views are pre-calculated results of expensive queries that the warehouse can transparently re-rewrite queries that come in.

Best practice: If you have more common and costly queries for your aggregates, back them up with summary tables or materialized views. Choose a suitable refresh period to ensure that the information generated remains up to date, but not too burdensome.

Bonus Levers: Compression and Parallelism

The four techniques are amplified by two more techniques. Columnar compression allows for storage savings and fewer disk reads to perform analytics, which is a huge benefit for those using the system for analytics that are based on scanning. Parallel query execution (and massively parallel processing on enterprise platforms) breaks down a single large query into smaller chunks that are executed on many CPUs or nodes, reducing the execution time of the query.

These are some of the most frequent data warehouse design mistakes made.

• Copying your OLTP schema directly into your warehouse – normalized transactional models are bad for analytical performance.
• Indexing everything which slows loads and wastes storage.
• Without data governance, so the data warehouse becomes filled with duplicate, inconsistent, and stale data.
• Creating for the current volume, not the tenfold increase in volume in 2 years.

Frequently Asked Questions

Which technique is most important to optimize a data warehouse?

The base is dimensional modeling, if the schema isn’t correct, then nothing else will work. Then, get the star schema correct—then add partitioning, indexing, and materialized views.

Is there any performance gain for partitioning?

Yes. Partitioning allows partition pruning, thus only the slices of data that are needed for a query are scanned, rather than the entire table, which is typically the most significant performance improvement for large fact tables.

Which is better, a star schema or snowflake schema?

Generally a star schema is easier and faster to query. A snowflake schema trades off dimensions for normalization in order to save storage. The typical BI team chooses to use star schemas.

Optimize Your Data Warehouse with RalanTech

A well-designed data warehouse turns scattered, complex data into fast, trustworthy business insight. RalanTech’s data warehouse consulting and data engineering teams design, tune, and modernize warehouses that scale with your business — and our database performance tuning experts keep them fast as your data grows. Talk to our data experts to build an analytics foundation that’s ready for what’s next.