Stop Full Table Scans
Pruning, Indexing, and Clustering- A deep dive into the skills necessary to build strong foundational knowledge that AI can't replace
More people now than ever will be querying your data due to the availability of data via AI agents. It will no longer be just the data team accessing data in the warehouse, but marketing executives, sales teams, and product owners.
Because of this, query speed will matter more than ever. You are no longer optimizing your code for your own work, but for the work of those who may not even understand data that well.
This leaves more room for error, performance issues, and long-running queries.
As analytics engineers, we need to understand query optimization so we can write code and design systems that are stable and performant.
Once again, foundational knowledge is the thing that carries us through this era of AI to ensure we can enable AI in other areas of the business.
Everyone has that one table that is so large and painful to query, that you try to avoid using it at all costs. It can sometimes be hours just to get one simple SELECT query to run.
This is because the system is reading every single row in your table to locate the piece of data that you requested. If there is no data pruning, it has no idea where to start.
It’s like paying someone to search every corner, of every room in your house to find your car keys, with no hints as to where they were last seen. This is time-consuming and expensive.
You can think of data warehouse optimization techniques like indexing, partitioning, and pruning as pointers to tell the person where to start searching. If you know you lost your keys in the kitchen, that will be quicker (and therefore cheaper) to search than the entire home.
What causes full scans?
When a full scan occurs, the system is reading every single row in your table. This is both timely and expensive because of the massive amounts of compute that this takes, especially on large tables. Not to mention, most data warehouses charge you based on the amount of data scanned (or compute).
This means, if you want to save money, your goal should always be to scan as little data as possible.
Full scans are caused by:
no index on fields used in WHERE or JOIN clauses
filtering on date parts or columns with functions applied
comparing values of different data types
overusing SELECT *
inefficient joins
lack of proper data modeling
I see a lot of these mistakes in AI-generated SQL, which is why avoiding these patterns is a foundational skill every analytics engineer needs to know.
Techniques to avoid full scans
To optimize your SQL queries, save money, and increase performance, you can use the following techniques:
