Best Practices for Aggregating Data in Summary Tables

How to design high-performance summary tables for real-time metrics on high volumes of incoming data.

Summary tables are a major reason that Hydrolix has been so successful at monitoring huge events (and providing subsecond query response times for most queries).

During the 2025 Super Bowl, Hydrolix ingested more than 200 terabytes of data, with data ready for querying in seconds.

Learn how Hydrolix helped the FOX network ingest, manage, and analyze petabytes of CDN log data in real time for one of the biggest televised events in history.

If you’re aiming for real‑time metrics at scale, it’s worth exploring how well‑designed summary tables can accelerate query performance, reduce computational overhead, and keep your analytics responsive. By adopting a smarter aggregation strategy early on, you set the foundation for faster insights, more efficient resource usage, and a more resilient analytics pipeline.

TL;DR

• Summary tables store aggregated states for fast real‑time metrics.
• Query summary tables for real-time analytics, not raw data.
• Retain raw data for root cause analysis.
• Aim for 98 percent data reduction.
• Avoid monolithic tables; build custom ones per dashboard.
• Monitor cardinality to prevent data explosions.

What Are Summary Tables?

Summary tables, also commonly known as aggregate tables, store aggregate states that can quickly be computed for real-time metrics, which makes them ideal for dashboards and many user queries.

Summary tables are much smaller than the underlying raw data tables, making them much more efficient in terms of compute usage and overall performance. And they’re especially important when it comes to working with big data, where performance and resource usage are critical.

Using SQL to Generate a Summary Table

To generate a summary table, it’s typical to use a SQL statement with a GROUP BY that returns a specific aggregation. A summary table can be built on a SQL statement that has multiple GROUP BY clauses.

Some summary tables work by querying the underlying data and generating aggregations. This is a typical approach for reports that can be generated on a daily or weekly basis, but it’s not effective for real-time analytics.

Efficiency in Real-Time Aggregation

In the case of real-time analytics, summary tables need to reflect incoming data and remain highly accurate. A summary table that regularly queries the underlying data (such as on a five-second or one-minute interval) will be very inefficient, especially when the underlying raw data tables are very large. This can lead to high compute costs and subpar performance.

In the case of Hydrolix, summary tables are updated at ingest time (alongside raw data tables) and hold intermediate states which can be recalculated on the fly. Because summary tables are generated and updated separately from raw data tables, they remain highly accurate and never need to query the underlying tables.

Summary Table Optimization Techniques

Here are some best practices for working with summary tables and aggregate tables in general. The goal is to achieve as much reduction as possible for maximum efficiency by customizing summary tables for specific use cases and using techniques that reduce data cardinality.

Keep All the Raw Data—but Prioritize Querying Summary Tables.

Many solutions, including time series databases, aggregate data and then discard the underlying data. While this approach does reduce the amount of data that needs to be stored and queried, it’s not a great approach for use cases like root cause analysis and fraud detection. Aggregate data can show anomalies like high request latency, but the underlying raw data is required for actually pinpointing the source of issues.

Aggregate data is also usually “sliced” based on specific use cases—but no amount of designing and planning can guarantee that you’ll be able to capture every use case you’ll need to cover six months or a year from now. What if you realize that you need different aggregations later? If the underlying raw data is gone, it’s very likely that you won’t have all the data you need to perform fresh aggregations that are accurate. In other words, discarding the underlying data doesn’t just lead to gaps in the overall granularity of your data—it also puts the design of future summary tables at risk.

So you should keep all the underlying raw data—but that doesn’t mean the raw data should be the main target for querying, especially if you’re working with big datasets.

For big data use cases, querying the underlying raw data will lead to less efficient queries and more expensive compute—an unnecessary double whammy that can be avoided by querying summary tables where possible. As an example, a dashboard that refreshes every minute will make nearly 1500 queries per day—and if it’s querying raw data, it will also need to aggregate that data each time.

This can be a big issue regardless of what kind of storage you use, including cloud object storage, which is becoming increasingly popular not just for data lakes but for fast analytics using solutions like Apache Iceberg, Delta Live Tables and Hydrolix. In the case of object storage, efficient querying is absolutely essential due to its distributed nature. Scanning large volumes of data in object storage will be much slower, and the cost for making many HTTP requests quickly adds up.

As a best practice, dashboards should query smaller summary tables that hold precomputed aggregates. It’s important to keep the raw data for a wide range of use cases and to query it when needed, but summary table queries should be prioritized over querying raw data wherever possible.

Aim for Huge Reductions in Summary Tables: at Least 97-98%.

Ultimately, the goal should be at least a 97-98% reduction in data per summary table. Otherwise, a summary table built on a raw table that holds many terabytes of data could still be quite sizable. In the past, we might’ve considered an 80% reduction in size to be a great improvement. And while it certainly is a significant improvement, if the raw table holds ten terabytes of data, the summary table will still be quite huge (around 2 TB). With 97-98% reduction in size, the same summary table will be just 40GB—much more manageable and extremely efficient to query.

You may need to get creative to reach this level of size reduction, but the end result is very rewarding. In addition to improved query and compute efficiency, it’s an opportunity to really drill down into each use case and pinpoint the data you need. And as long as you’re keeping the underlying raw data, you don’t need to worry about capturing the wrong slices of your data because you can always explore other aggregations later.

Avoid Monolithic Summary Tables.

It can be tempting to build “one summary table to rule them all.” This is an approach we’ve used in the past because of its convenience. With this approach, a single large summary table can provide useful aggregations for many different dashboards. And it’s probably more tempting if you’re not exactly sure what you want to aggregate, or if you’re discarding the underlying data and want to aggregate everything just in case.

The next diagram shows this approach.s aggregate tables, store aggregate states that can quickly be computed for real-time metrics, which makes them ideal for dashboards and many user queries.

Summary tables are much smaller than the underlying raw data tables, making them much more efficient in terms of compute usage and overall performance. And they’re especially important when it comes to working with big data, where performance and resource usage are critical.

To generate a summary table, it’s typical to use a SQL statement with a GROUP BY that returns a specific aggregation. A summary table can be built on a SQL statement that has multiple GROUP BY clauses.

Some summary tables work by querying the underlying data and generating aggregations. This is a typical approach for reports that can be generated on a daily or weekly basis, but it’s not effective for real-time analytics.

In the case of real-time analytics, summary tables need to reflect incoming data and remain highly accurate. A summary table that regularly queries the underlying data (such as on a five-second or one-minute interval) will be very inefficient, especially when the underlying raw data tables are very large. This can lead to high compute costs and subpar performance.

In the case of Hydrolix, summary tables are updated at ingest time (alongside raw data tables) and hold intermediate states which can be recalculated on the fly. Because summary tables are generated and updated separately from raw data tables, they remain highly accurate and never need to query the underlying tables.

Best Practices for High-Efficiency Summary Tables

Here are some best practices for working with summary tables and aggregate tables in general. The goal is to achieve as much reduction in possible for maximum efficiency by customizing summary tables for specific use cases and using techniques that reduce data cardinality.

Keep All the Raw Data—but Prioritize Querying Summary Tables.

Many solutions, including time series databases, aggregate data and then discard the underlying data. While this approach does reduce the amount of data that needs to be stored and queried, it’s not a great approach for use cases like root cause analysis and fraud detection. Aggregate data can show anomalies like high request latency, but the underlying raw data is required for actually pinpointing the source of issues.

Aggregate data is also usually “sliced” based on specific use cases—but no amount of designing and planning can guarantee that you’ll be able to capture every use case you’ll need to cover six months or a year from now. What if you realize that you need different aggregations later? If the underlying raw data is gone, it’s very likely that you won’t have all the data you need to perform fresh aggregations that are accurate. In other words, discarding the underlying data doesn’t just lead to gaps in the overall granularity of your data—it also puts the design of future summary tables at risk.

So you should keep all the underlying raw data—but that doesn’t mean the raw data should be the main target for querying, especially if you’re working with big datasets.

For big data use cases, querying the underlying raw data will lead to less efficient queries and more expensive compute—an unnecessary double whammy that can be avoided by querying summary tables where possible. As an example, a dashboard that refreshes every minute will make nearly 1500 queries per day—and if it’s querying raw data, it will also need to aggregate that data each time.

This can be a big issue regardless of what kind of storage you use, including cloud object storage, which is becoming increasingly popular not just for data lakes but for fast analytics using solutions like Apache Iceberg, Delta Live Tables and Hydrolix. In the case of object storage, efficient querying is absolutely essential due to its distributed nature. Scanning large volumes of data in object storage will be much slower, and the cost for making many HTTP requests quickly adds up.

As a best practice, dashboards should query smaller summary tables that hold precomputed aggregates. It’s important to keep the raw data for a wide range of use cases and to query it when needed, but summary table queries should be prioritized over querying raw data wherever possible.

Aim for Huge Reductions in Summary Tables: at Least 97-98%.

Ultimately, the goal should be at least a 97-98% reduction in data per summary table. Otherwise, a summary table built on a raw table that holds many terabytes of data could still be quite sizable. In the past, we might’ve considered an 80% reduction in size to be a great improvement. And while it certainly is a significant improvement, if the raw table holds ten terabytes of data, the summary table will still be quite huge (around 2 TB). With 97-98% reduction in size, the same summary table will be just 40GB—much more manageable and extremely efficient to query.

You may need to get creative to reach this level of size reduction, but the end result is very rewarding. In addition to improved query and compute efficiency, it’s an opportunity to really drill down into each use case and pinpoint the data you need. And as long as you’re keeping the underlying raw data, you don’t need to worry about capturing the wrong slices of your data because you can always explore other aggregations later.

Avoid a Monolithic Approach to Summary Tables.

It can be tempting to build “one summary table to rule them all.” This is an approach we’ve used in the past because of its convenience. With this approach, a single large summary table can provide useful aggregations for many different dashboards. And it’s probably more tempting if you’re not exactly sure what you want to aggregate, or if you’re discarding the underlying data and want to aggregate everything just in case.

The next diagram shows this approach.

Beyond convenience, teams may be tempted to centralize aggregate data into a single table for other reasons. Many enterprises seek to centralize their data and prevent data silos, and from that perspective, it may seem like a good practice to have a single summary table that many teams can access.

However, when it comes to huge volumes of data, we’ve found that summary tables that have many fields can have slowdowns in performance, especially if there are many GROUP BY queries or any of the fields have higher cardinality. You can still centralize the underlying raw data, but the best practice for summary table performance is to create smaller, bespoke summary tables.

Build Custom Summary Tables for Each Dashboard.

The better approach is to build custom summary tables for each use case. Typically, that means designing a dashboard and then determining which aggregations and metrics the summary table should have. The next diagram illustrates this approach.

Once you know which aggregations and metrics you need, you can create a list of the columns that you’ll need groupings for. As an example, a summary table showing page latency might include columns for HTTP status code, latency, and page URL.

If you already have existing summary tables, you can first check to see if one of these summary tables satisfies the conditions necessary for new dashboards. Reusing is great, and it’s always nice to have less redundancy. However, we’ve found that some redundancy in smaller, bespoke summary tables is preferable to having larger summary tables that avoid redundancy. The performance improvements and increased compute efficiency can be significant for massive volumes of data. Meanwhile, the storage requirements of additional small summary tables are minimal.

What about consistency of data across summary tables and dashboards? In the case of Hydrolix, summary tables ingest data alongside raw tables, and never query the underlying raw data. They are designed to handle late-arriving data and recalculate aggregations on the fly using intermediate states. As a result, they remain highly accurate for use cases such as observability. For other solutions, you may need to take additional steps to ensure consistency.

Perform Cardinality Analysis to Prevent Cardinality Explosions.

By this point, you should have a list of columns to aggregate for each specific dashboard use case. The next step is to ensure that none of the columns are at risk for cardinality explosions (unexpectedly high cardinality data), which can severely impact summary table performance during periods of peak data ingest.

In our experience, it’s best to keep the maximum cardinality fairly low. You can run a query that returns the total unique values in a column over the last hour to get a general sense of a column’s cardinality. Using Clickhouse-flavored SQL, you could do a query like this:

SELECT
  column_name,
  formatReadableQuantity(uniq_val)
FROM (
  SELECT
    column_name,
    sum(column_unique_values) AS uniq_val
  FROM project.table#.metadata
  WHERE min_timestamp > (now() - toIntervalMinute(60))
  GROUP BY column_name
  ORDER BY uniq_val DESC
)

Ideally, this query should return a maximum of around 300 values. Columns that include HTTP request codes, booleans, or a limited number of string responses could all be good candidates. But a column that holds unique IP addresses or user IDs would be a terrible candidate for summary tables.

You can read this post from our engineering team on performing cardinality analysis to reduce costs to learn how we applied cardinality analysis to find (and then reduce the cardinality) of high-cardinality columns for greater cost efficiency and performance.

Apply Techniques for Reducing Column Cardinality.

What if you have high-cardinality columns that absolutely need to be aggregated? You can still take steps to reduce cardinality while ensuring that you have the data you need for the summary table. In the case of the Super Bowl, we applied multiple techniques to reduce column cardinality and fine-tune summary tables. These included:

• Transforming strings to avoid thousands of unique attributes. For example, we transformed  request_path into a short request_file_ext when we learned that FOX Sports most needed aggregated data about file extension type, not the full request path, so we extracted only the file extension for the summary table column.
• Normalizing data into known sets. For example, fields that contained potentially large or messy strings were normalized into categories or known sets.
• Capturing only necessary data for summary tables. For example, if only the top 50 user agents were needed, everything else became “other”. Binning data is another technique that can be effective as well.

Since summary tables are meant to provide a high-level view, reducing data granularity can greatly improve performance with minimal compromises in terms of the overall usefulness and value of the summary table. And as long as you’re keeping the underlying data, it won’t lead to data gaps.

Here’s one useful query that Federico Rodriguez (principal architect at Hydrolix) has shared with our success team when it comes to optimizing summary tables. This query analyzes a column’s distribution of values to determine how “top heavy” they are. Specifically, you can determine the fraction of total rows that the top 10, 100, and top 500 values cover.

SELECT
  Sum(hits_top10) / Sum(total_hits) AS top_10_coverage,
  Sum(hits_top100) / Sum(total_hits) AS top_100_coverage,
  Sum(hits_top500) / Sum(total_hits) AS top_500_coverage
FROM (
  SELECT
    Sum(cnt) AS total_hits,
    Sumif(cnt, rank <= 10) AS hits_top10,
    Sumif(cnt, rank <= 100) AS hits_top100,
    Sumif(cnt, rank <= 500) AS hits_top500
  FROM (
    SELECT
      [name-of-column, such as request path] AS d,
      Count() AS cnt,
      Dense_rank() OVER (ORDER BY cnt DESC) AS rank
    FROM [name-of-table].logs
    WHERE reqtimesec > Now() - interval 1 hour
    GROUP BY d
    ORDER BY cnt DESC
  )
)

As an example, if the top 500 covers greater than 95% of the data in the column, you could safely use the top 1000 to get close to full coverage. And if the top 500 covers 99% of your data, you could safely use the top 500 values to get close to full coverage.

If the value needed for complete coverage is greater than 300 (the rule of thumb) maximum cardinality for a summary table, you can determine whether the tradeoff in coverage is worth it.

If you can’t successfully limit cardinality even with creative measures, you can isolate high-cardinality columns in their own summary tables. If you do run into performance issues with high-cardinality columns, it won’t limit access to other mission-critical data stored in other summary tables.

Test Summary Queries Against Raw Data and Aim for 95%+ Reduction.

By this point, you should have a list of attributes that will be aggregated in the summary table using queries with GROUP BY clauses. The next step is to test whether summarizing data will actually achieve at least a 95% reduction compared to querying the raw data.

To do so, you can run test queries against a small subset of the data (for example, a time-filtered query that covers one data of data) on a per column basis. In other words, first run a query that retrieves all the raw data from that column, and then run the aggregate query you plan to use against the same raw data. You should see at least a 95% reduction in size in the aggregate data query response compared to the raw data query response.

Why 95% and not 97-98%? In the case of Hydrolix, the Hydrolix merge service achieves greater compressibility over time. So a 95% reduction in a small sample size will typically result in at least a 97-98% reduction in summary table size from a much larger data set.

Limit the Number of GROUP by Clauses in Each Summary Table.

The next step is to limit the number of GROUP BY clauses aggregating data in each summary table. Aggregations can be compute-intensive and impact performance, especially for big data. There is no perfect rule of thumb here—there’s a fine balance between maximizing performance and ensuring that dashboards have all the data they need. But once again, avoid monoliths. As an example, one Hydrolix customer was using a summary table that had 26 GROUP BYs. We reduced that to 12 GROUP BYs, leading to dramatically improved performance—all while continuing to provide the customer with all the data they needed in their dashboards.

This doesn’t mean that 12 GROUP BYs is an ideal number, but it was the minimum number of GROUP BYs needed for this dashboard’s specific use case. And reducing the number of GROUP BYs by more than half really helped with efficiency.

If you’re using a storage solution that requires adding indexes manually, it may be helpful to consider whether indexing columns that use GROUP BY improves query performance. There can be potential tradeoffs with indexing for some solutions, especially for write-heavy workloads. Hydrolix is designed to index all columns by default without bloating write times or storage overhead. As a result, all columns in Hydrolix summary tables are indexed and there’s no need for additional manual configuration (unless for some reason, you want some columns to not be indexed).

Summary Tables Are Immensely Powerful. Use Them Wisely!

By combining these techniques, you can create summary tables that provide real-time, mission-critical data to teams. For the Super Bowl, the time to glass for data was about 5 to 10 seconds, with a p50 query response time of .481 seconds—and this was with nearly 200 terabytes of data ingested over the span of a few hours. Summary tables were a big reason for the stellar performance, providing an efficient and reliable source for dashboards and many user queries. Meanwhile, the teams at FOX Sports were still able to query the underlying raw data when needed.

If you’re working with huge volumes of data in real time (think terabytes or petabytes), efficient summary tables are a requirement for ensuring performant analytics and keeping compute costs down.

Next Steps

Contact us about a managed trial or demo to see summary tables in action.

FAQs About Aggregating Data in Summary Tables