Stop Using SELECT *: The Simple Fix That Cuts Query Costs

Stop Using SELECT *: The Simple Fix That Cuts Query Costs

Selecting only needed columns can reduce I/O by 80% or more on wide tables. Learn how to identify and fix SELECT * queries across your organization.

The Problem

When you use SELECT *, Snowflake must read all columns from all micro-partitions, even if you only use 2-3 columns. For wide tables (50+ columns), this wastes 80-95% of I/O.

Cost Impact

-- Table with 100 columns, 1TB per column = 100TB total
SELECT * FROM wide_table WHERE date = '2024-01-15';
-- Scans all 100TB

SELECT id, name, amount FROM wide_table WHERE date = '2024-01-15';
-- Scans only 3TB (97% reduction!)

Common Scenarios

1. BI Tool Queries

-- BI tool default
SELECT * FROM customers WHERE region = 'West';

-- Optimized
SELECT customer_id, name, email, region
FROM customers WHERE region = 'West';

2. Application Queries

-- ORM default
SELECT * FROM orders WHERE customer_id = 123;

-- Optimized for display page
SELECT order_id, order_date, total, status
FROM orders WHERE customer_id = 123;

Finding SELECT * Queries

SELECT
QUERY_TEXT,
USER_NAME,
WAREHOUSE_NAME,
BYTES_SCANNED/1024/1024/1024 as GB_SCANNED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT ILIKE '%SELECT *%'
AND START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY BYTES_SCANNED DESC
LIMIT 100;

Best Practices

1. Explicitly list columns needed for each query
2. Audit BI tool queries and add column selections
3. Update application code to select specific columns
4. Create views with commonly-needed columns
5. Educate users on the cost impact

Real-World Example

A data analytics team's dashboards used SELECT * on a 150-column customer table. Queries scanned 500GB-1TB each. After audit, dashboards needed only 12-15 columns. Updated queries to select specific columns, query scans dropped to 40-60GB (92% reduction), dashboard load times improved from 45s to 6s, and monthly compute costs dropped $8,000.

Key Takeaways

• SELECT * can waste 80-95% of I/O on wide tables
• Always specify columns you actually need
• Audit BI tool queries and application code
• Create views with common column sets
• Monitor and alert on excessive SELECT * usage