How to Organize Your Data So Queries Stop Costing You a Fortune

If you've ever opened a query in BigQuery and gasped at the cost preview before even hitting run, or received a monthly bill that was way higher than expected, you know the price of poor data modeling. Partitioning, clustering, views, and materialized views aren't just technical details: they are the difference between pipelines that cost pennies and those that silently drain your budget.

In this guide, we'll explore how each of these techniques works, when to apply them, and how to combine them to build a data warehouse that delivers real performance, without blowing up the company credit card.

Partitioning: Divide and Conquer

Partitioning is an optimization technique that involves physically dividing a large table into smaller, more manageable segments called partitions. This division is based on the values of one or more columns, typically date/time columns or integer IDs [1].

How It Works

When a table is partitioned, the data is organized into separate storage blocks, with each block corresponding to a specific partition. For example, a sales table can be partitioned by date, with each day, month, or year stored in a distinct partition. When a query runs with a filter on the partition column, the database engine scans only the relevant partitions, ignoring the rest. This process, known as pruning, significantly reduces the amount of data scanned, resulting in faster queries and lower compute costs [1].

Benefits of Partitioning

• Improved Query Performance: By reducing the volume of data scanned, queries that filter on partition columns execute much faster.
• Cost Reduction: Many cloud data warehouses, like Google BigQuery, charge based on the amount of data processed. Partitioning minimizes this footprint, directly lowering your bill [1].
• Simplified Maintenance: It eases table maintenance, allowing operations like data deletion or expiration at the partition level without affecting the rest of the table. This is highly useful for data retention policies [1].
• Query Cost Estimation: In systems like BigQuery, partitioning allows for a more accurate cost estimate before execution, as the query planner can determine exactly which partitions will be scanned [1].

Common Partitioning Types

While partitioning types vary across platforms, the most common include:

• Time-unit Column Partitioning: Based on DATE, TIMESTAMP, or DATETIME columns. Data is automatically allocated into hourly, daily, monthly, or yearly partitions. Example: PARTITION BY DATE(timestamp_col).
• Ingestion Time Partitioning: The database automatically assigns data to partitions based on when it was ingested. A pseudo-column (e.g., _PARTITIONTIME in BigQuery) is used for this purpose.
• Integer Range Partitioning: Based on an INTEGER column where partitions are defined by ranges of values. Example: PARTITION BY RANGE_BUCKET(customer_id, GENERATE_ARRAY(0, 1000000, 10000)).

Code Example (BigQuery SQL)

To create a date-partitioned table in BigQuery, you can use the following syntax:

CREATE TABLE `your_project.your_dataset.partitioned_sales_table`
(
id STRING,
product STRING,
value NUMERIC,
sale_date DATE
)
PARTITION BY sale_date
OPTIONS(
description="Sales table partitioned by date"
);

To query data from a specific partition, use the WHERE clause to filter by the partition column:

SELECT
product,
SUM(value) as total_sales
FROM
`your_project.your_dataset.partitioned_sales_table`
WHERE
sale_date = '2023-01-15'
GROUP BY
product;

This example demonstrates how partitioning allows BigQuery to scan only the partition corresponding to '2023-01-15', optimizing the query. [1]

Clustering: Organizing Data to Speed Up Queries

While partitioning splits a table into physical segments, clustering organizes the data within those partitions (or the entire table, if unpartitioned) based on specific user-defined columns [2]. Think of partitioning as creating drawers in a cabinet, and clustering as sorting the items inside each drawer in a logical order.

How It Works

Clustering works by sorting the storage blocks of data based on the values of the clustered columns. When a query filters or aggregates data by these columns, the engine only scans the relevant blocks instead of the entire partition or table. This is highly effective for high-cardinality columns (columns with many unique values) [2].

For instance, if a transactions table is clustered by customer_id, all transactions for a single customer will be physically stored next to each other. A query looking for transactions of a specific customer_id benefits immensely because the system only reads a small portion of the data.

Benefits of Clustering

• Performance Boost for Filters and Aggregations: Speeds up queries that filter or group by clustered columns, especially high-cardinality ones.
• Reduced Data Scanned: Similar to partitioning, clustering allows the engine to skip irrelevant blocks of data, lowering the amount of processed data and reducing costs [2].
• Multi-column Optimization: You can cluster by multiple columns, and the order of these columns matters. The query planner optimizes searches from left to right, prioritizing the first clustered column [2].

When to Use Clustering

• Fine-grained Control: When partitioning alone doesn't provide the granularity needed to optimize specific queries.
• Filters on High-Cardinality Columns: Ideal for columns with many distinct values, where partitioning would be impractical or inefficient.
• Queries with Multiple Filters or Aggregations: When queries frequently filter or group by multiple columns [2].
• Large Tables or Partitions: Tables or partitions larger than 64 MB generally benefit from clustering [2].

Combining Partitioning and Clustering

Combining partitioning and clustering is a best practice for performance tuning. First, the table is divided into partitions (e.g., by date), and then, within each partition, data is clustered by one or more columns (e.g., customer_id or product_category). This provides a two-layered optimization, leading to even faster queries [2].

Code Example (BigQuery SQL)

To create a table partitioned by date and clustered by product and id in BigQuery:

CREATE TABLE `your_project.your_dataset.partitioned_clustered_sales_table`
(
id STRING,
product STRING,
value NUMERIC,
sale_date DATE
)
PARTITION BY sale_date
CLUSTER BY product, id
OPTIONS(
description="Sales table partitioned by date and clustered by product and id"
);

In this example, data is first divided by sale_date. Within each date partition, it is sorted by product, and then by id. A query filtering by both sale_date and product will be highly optimized. [2]

Tables, Views, and Materialized Views: What's the Difference?

Beyond optimizing physical storage with partitioning and clustering, data modeling also involves choosing the right logical structure to expose and consume data. The three main options are Tables, Views, and Materialized Views.

1. Tables

Tables are the fundamental storage structure in any relational database or data warehouse. They physically store data on disk.

• Characteristics: Data is physically persisted. DML operations (Insert, Update, Delete) modify the data directly within the table.
• Performance: Read performance depends on how the table is structured (indexes, partitioning, clustering).
• Cost: You pay for both physical storage and the compute resources used to query the data.
• When to use: To store raw or processed data that forms the base layer of your data warehouse.

2. Views

A View is essentially a saved SQL query that acts as a virtual table. It does not store physical data; instead, the underlying query runs every time the View is queried [3].

• Characteristics: They do not consume storage space (other than the query definition itself). They always return the most up-to-date data from the underlying base tables.
• Performance: Performance depends entirely on the complexity of the underlying query and the volume of data in the base tables at execution time. Complex queries in Views can be slow.
• Cost: You only pay for query compute costs each time the View is accessed.
• When to use:
• • To simplify complex queries (encapsulating joins and aggregations).
  • To restrict access to specific columns or rows of a base table (security and governance).
  • To create a logical abstraction layer over the physical model.

Example of Creating a View:

CREATE VIEW `your_project.your_dataset.daily_sales_view` AS
SELECT
sale_date,
SUM(value) as total_sales,
COUNT(id) as transaction_count
FROM
`your_project.your_dataset.partitioned_sales_table`
GROUP BY
sale_date;

3. Materialized Views

Materialized Views combine features of both Tables and Views. They are defined by an SQL query (like a View), but the query results are precomputed and physically stored on disk (like a Table) [4].

• Characteristics: They store data physically. They need to be refreshed to reflect changes in the base tables. Depending on the database, this refresh can be manual, scheduled, or automatic (incremental).
• Performance: Extremely fast read performance because the data is precomputed (especially useful for heavy aggregations).
• Cost: You pay for storing the precomputed data, the compute needed to refresh the Materialized View, and the queries run against it (which are typically much cheaper than querying base tables).
• When to use:
• • For dashboards and reports requiring sub-second response times.
  • When the same complex aggregation is queried repeatedly by multiple users or processes.
  • When slight data latency (minor lag between refresh cycles) is acceptable [4].

Example of Creating a Materialized View (BigQuery):

CREATE MATERIALIZED VIEW `your_project.your_dataset.mv_monthly_sales` AS
SELECT
EXTRACT(MONTH FROM sale_date) as month,
EXTRACT(YEAR FROM sale_date) as year,
SUM(value) as total_sales
FROM
`your_project.your_dataset.partitioned_sales_table`
GROUP BY
month, year;

At-a-Glance Comparison

Conclusion

Modern data modeling requires a deep understanding of how data is stored and accessed. Partitioning and clustering are essential tools for physically organizing large datasets, reducing costs, and accelerating queries by minimizing unnecessary data scans.

On the other hand, choosing between Tables, Views, and Materialized Views defines the logical architecture of your data warehouse. While Tables store your ground truth, Views offer flexibility and security, and Materialized Views deliver the high-speed performance needed for large-scale analytics.

Mastering these techniques is only half the battle. The other half is ensuring your data reaches these structures reliably, without surprises, black boxes, or endless maintenance. That's exactly what Erathos solves.

Discover the platform →

References

[1] Google Cloud. "Introduction to partitioned tables". Available at: https://cloud.google.com/bigquery/docs/partitioned-tables

[2] Google Cloud. "Introduction to clustered tables". Available at: https://cloud.google.com/bigquery/docs/clustered-tables

[3] Databricks. "Tables and views in Databricks". Available at: https://docs.databricks.com/aws/en/data-engineering/tables-views

[4] Snowflake. "Working with Materialized Views". Available at: https://docs.snowflake.com/en/user-guide/views-materialized