Risks and Costs of SELECT *
An ETL process that has worked consistently for two years with no issues has suddenly failed. After reviewing the logs and tracing the code, you learn it's failing loading a temporary table that has loaded thousands of times successfully — but it's loading a table with a SELECT *, and a new column has been added. It's now loading more columns than the temporary table definition.
The code is updated to explicitly return the required columns for the temp table, and the users — waiting on the reports that drive critical business decisions — finally get their data.
A harmless change that isn't
Adding a column to a table should be innocuous, but using SELECT * in certain situations exposes risk when a new column is added. A new column can cause unexpected errors that impact the business.
The cost of delay — and of moving data you don't need
There is a potential financial impact when the business is delayed in making important decisions that drive the business, but there can be more implicit costs — such as when more data than required is returned from a cloud platform and you are paying for egress costs. When dealing with millions of rows, the data egress costs add up.
The performance cost
There can be a performance cost when returning more data than necessary. Extra data can unnecessarily impact network speed, slowing down a query's response time. This can have a wider impact than the slowness of the immediate query.
Undoing the work someone already did
Another risk is that a database administrator could have carefully created a covering index meant for this query. They have built it specifically so the query only has to read the index-level data — but a SELECT * undoes that work by forcing a lookup on the table leaf-level data to retrieve data that isn't needed.
Be intentional
SELECT * doesn't seem like something with a high risk, but it can impact the business by delaying decision making if a downstream process fails, it can have other tangible costs retrieving data that is unneeded, diminish query performance, add clutter to a network, and kill performance that someone else has already accounted for. Planning and limiting SELECT statements to their required columns reduces risk and cost — saving time and money.
Originally published on Medium.