As companies grow, their data teams often prioritize the speed of development over efficiency, which can result in data sprawl over time. When companies then focus on optimizing their queries to reduce costs and simplify the complexity, it can take time to know where to start due to the thousands of datasets and various tools (like dbt models, Spark pipelines, and BI dashboards) generating queries. The first step towards effective optimization is understanding the costs and determining how the data team can optimize the underlying queries. This is where Single Origin's Insights page comes in handy.
Efficiency Insights in Single Origin
Single Origin's Insights page provides a comprehensive overview of all queries executed in your data warehouse, regardless of the tool used to generate them. This global view is generated by analyzing your data warehouse's query logs and breaking down the results by various factors such as dataset, formula, and common query patterns (defined as a specific set of datasets joined in one particular way). From here, you can learn exactly where to start your efficiency journey:
- What datasets are used in the most queries or generate the most cost? You could focus on optimizing the usage of these datasets to start.
- What specific query patterns can be optimized? How can they be optimized, and how much would you save?
We cover each of these two problems in more detail below.
The dataset view provides valuable insights into the most frequently used datasets and the cost attributed to them. Unlike other tools that show overall costs, Single Origin allocates costs from queries down to individual datasets. We start with the cost logged in your warehouse's query logs, and we then distribute the total query cost among the datasets referenced based on the size of each dataset.
By summarizing all queries against a dataset, Single Origin helps you identify common query patterns that can be optimized. Single Origin's unique feature is that it automatically parses all queries to uncover these patterns, eliminating the need to manually inspect hundreds or thousands of queries to optimize dataset usage.
Single Origin also suggests specific SQL logic that you can implement to optimize your use of datasets, as well as estimates how much you will save by implementing such logic. You can immediately go from insight to action, including calculating your ROI.
Query Pattern View
Similarly, starting at the query pattern view allows you to identify the most common patterns in your queries. If a pattern is costly, you can analyze the SQL logic that generates the same data from all your queries in a single, efficient query. For example, instead of generating metrics one by one across multiple queries, you can combine the SQL logic into a single query that produces all your metrics. You can save a significant amount by implementing such SQL logic.
If you are unsure about whether a particular pattern is worth addressing, then you can generate a cost-savings report with a single click.
Single Origin is a powerful tool that provides valuable insights into your company's usage patterns. By analyzing all your queries, Single Origin can make concrete suggestions that you can implement to optimize your operations. Additionally, Single Origin estimates the potential cost savings you can achieve by acting on the recommendations. With its comprehensive approach, Single Origin helps you identify and act on areas where optimization can be most valuable.
To learn more, check out our insights documentation here. To get insights for your queries, reach out to firstname.lastname@example.org today!