Snowflake secondary indexes explained: CREATE INDEX, covering indexes and when to use them

For most of its history Snowflake had no indexes on ordinary tables. It relied on micro-partitions instead: small chunks of a table with the minimum and maximum value of every column recorded, so a query can skip chunks that cannot contain the rows it needs. That works brilliantly for big analytical queries, and less well when you want a handful of rows by customer ID. On 26 October 2026, secondary indexes became generally available for standard tables. This guide explains what they are, how to create one and when they are worth paying for.

How a secondary index works

A secondary index maps the values of its key columns to the micro-partitions that hold them. When a query filters on those columns, Snowflake looks up the matching partitions in the index and reads only those. If every column the query needs is stored in the index, Snowflake can answer from the index alone. Snowflake builds and maintains the index automatically as data changes.

Create your first index

The syntax will look familiar if you have used PostgreSQL or SQL Server. Key columns come first, in order of importance, and INCLUDE adds extra columns that are stored in the index but not used as the main search key.

-- Speed up lookups by customer and date, and cover the columns the app reads
CREATE INDEX idx_orders_customer
  ON orders (customer_id, order_date)
  INCLUDE (status, amount);

-- Queries like this can now skip most of the table
SELECT order_date, status, amount
FROM orders
WHERE customer_id = 42
  AND order_date >= '2026-10-01';

-- List and remove indexes
SHOW INDEXES IN TABLE orders;
DROP INDEX orders.idx_orders_customer;

Keys can also be deterministic expressions, such as a cast or a lower-case version of a column. Write your filters with the same expression so the optimizer can match them:

CREATE INDEX idx_customers_email ON customers (LOWER(email));

SELECT customer_id, name
FROM customers
WHERE LOWER(email) = 'ana@example.com';

Index, search optimization or clustering?

Snowflake now has three ways to make filters faster. They overlap, so it helps to know what each is for.

Limits to know before you start

Costs and permissions

To create an index you need OWNERSHIP of the table and the ADD SEARCH OPTIMIZATION privilege on its schema. Only the table owner's role can drop it. Maintenance credits appear in the search optimization history, where secondary indexes are labelled separately:

GRANT ADD SEARCH OPTIMIZATION ON SCHEMA sales.public TO ROLE app_owner;

-- Credits spent maintaining secondary indexes in the last 30 days
SELECT table_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.search_optimization_history
WHERE index_type = 'Secondary Index'
  AND start_time >= DATEADD(day, -30, CURRENT_TIMESTAMP())
GROUP BY table_name
ORDER BY credits DESC;

Tables that change constantly cost more to index, because every change has to be reflected in the index. Indexes pay off best on large tables that are read often by selective queries and updated in batches.

More on Snowflake: Snowflake October 2026 update, this week's data platform news, the Snowflake cheat sheet and Snowflake vs Databricks for AI agents. New to Snowflake? Start with Getting started with Snowflake.

Sources: Snowflake release note "Secondary indexes (General availability)" (26 October 2026) and the Snowflake documentation on secondary indexes, CREATE INDEX and search optimization. Features roll out by region and edition; check availability in your account.

Alessandro Ecclesie AgazziWritten by Alessandro Ecclesie Agazzi, analytics engineer in London. Updated 28 October 2026. About me