Overview

Download the code assets for this quickstart:

• setup_snowfield_pro_search.sql — Full environment and SQL setup
• products.csv — Product catalog data (1,085 products)
• querying_with_python_sdk.ipynb - Querying Multi-index Cortex Search with Python SDK
• snow-sports-demo/ — Full-stack application source (backend, frontend, Dockerfile, product images, and SPCS spec)

What You'll Need

What You'll Learn

What You'll Build

The Retail Catalog Retrieval Problem

Query Type 1 — Brand Name Lookup

"ridgeline"
"black crows skis"
"marker kingpin"

Query Type 2 — Intent / Brand Voice

"warm waterproof jacket for off-piste skiing"
"protective gear for a beginner on groomed runs"
"lightweight boot for touring in variable snow"

Understanding Multi-Index Cortex Search

TEXT INDEXES

VECTOR INDEXES

Setting Up Your Snowflake Environment

Step 1 — Create the Database and Warehouse

-- Use a role with sufficient privileges
USE ROLE ACCOUNTADMIN;

-- Create the database and schemas
CREATE DATABASE IF NOT EXISTS CATALOG_SEARCH_DB;
CREATE SCHEMA IF NOT EXISTS CATALOG_SEARCH_DB.DATA;
CREATE SCHEMA IF NOT EXISTS CATALOG_SEARCH_DB.APP;

-- Create a dedicated warehouse for the search service
CREATE WAREHOUSE IF NOT EXISTS CATALOG_SEARCH_WH
    WAREHOUSE_SIZE = 'SMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    COMMENT = 'Warehouse for Cortex Search service and queries';

USE DATABASE CATALOG_SEARCH_DB;
USE SCHEMA DATA;
USE WAREHOUSE CATALOG_SEARCH_WH;

Step 2 — Create the PRODUCTS Table

CREATE OR REPLACE TABLE CATALOG_SEARCH_DB.DATA.PRODUCTS (
    PRODUCT_ID      NUMBER AUTOINCREMENT PRIMARY KEY,
    ITEM_NAME       VARCHAR(500),
    BRAND           VARCHAR(200),
    CATEGORY        VARCHAR(100),     -- Equipment | Apparel | Protection | Accessories
    SUBCATEGORY     VARCHAR(200),
    DISCIPLINE      VARCHAR(100),     -- Alpine | Freeride | Nordic | Touring | Park
    SKILL_LEVEL     VARCHAR(50),      -- Beginner | Intermediate | Advanced | Expert
    GENDER          VARCHAR(50),
    PRICE           NUMBER(10, 2),
    PRODUCT_TYPE    VARCHAR(100),
    FLEX_RATING     VARCHAR(50),
    DESCRIPTION     VARCHAR(2000),
    SEARCH_TEXT     VARCHAR(4000)
);

Step 3 — Load the Sample Catalog

-- Create a file format for the CSV
CREATE OR REPLACE FILE FORMAT CATALOG_SEARCH_DB.DATA.PRODUCTS_CSV_FORMAT
  TYPE = 'CSV'
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  SKIP_HEADER = 1
  NULL_IF = ('');

-- Create a stage to hold the CSV file
CREATE OR REPLACE STAGE CATALOG_SEARCH_DB.DATA.PRODUCTS_STAGE
  FILE_FORMAT = CATALOG_SEARCH_DB.DATA.PRODUCTS_CSV_FORMAT
  COMMENT = 'Stage for loading the products.csv catalog file';

PUT file:///path/to/products.csv @CATALOG_SEARCH_DB.DATA.PRODUCTS_STAGE AUTO_COMPRESS=FALSE;

-- Load the CSV data into the PRODUCTS table
COPY INTO CATALOG_SEARCH_DB.DATA.PRODUCTS
  (ITEM_NAME, BRAND, CATEGORY, SUBCATEGORY, DISCIPLINE, SKILL_LEVEL, GENDER, PRICE, PRODUCT_TYPE, FLEX_RATING, DESCRIPTION)
FROM @CATALOG_SEARCH_DB.DATA.PRODUCTS_STAGE/products.csv
FILE_FORMAT = CATALOG_SEARCH_DB.DATA.PRODUCTS_CSV_FORMAT
ON_ERROR = 'ABORT_STATEMENT';

-- Verify row count (expected: 1,085)
SELECT COUNT(*) AS total_products FROM CATALOG_SEARCH_DB.DATA.PRODUCTS;

NOTE: In production, this is your existing catalog table. You do not need to load sample data — just point the search service at your real PRODUCTS table.

Step 4 — Build the SEARCH_TEXT Column

UPDATE CATALOG_SEARCH_DB.DATA.PRODUCTS
SET SEARCH_TEXT = TRIM(
    COALESCE(BRAND,        '') || ' ' ||
    COALESCE(ITEM_NAME,    '') || ' ' ||
    COALESCE(SUBCATEGORY,  '') || ' ' ||
    COALESCE(DISCIPLINE,   '') || ' ' ||
    COALESCE(SKILL_LEVEL,  '') || ' ' ||
    COALESCE(GENDER,       '') || ' ' ||
    COALESCE(DESCRIPTION,  '')
);

NOTE: SEARCH_TEXT is the column that gets the vector index. It should be as descriptive as possible. If you run the optional AI enrichment section next, it will rewrite DESCRIPTION before you build this column — giving the vector index richer, more searchable content.

Step 5 — Verify the Data

-- Check row count and sample SEARCH_TEXT content
SELECT COUNT(*) AS total_products FROM CATALOG_SEARCH_DB.DATA.PRODUCTS;

SELECT PRODUCT_ID, BRAND, ITEM_NAME, CATEGORY,
       LEFT(SEARCH_TEXT, 200) AS search_text_preview
FROM CATALOG_SEARCH_DB.DATA.PRODUCTS
LIMIT 5;

(Optional) Enrich Product Descriptions with Cortex AI

Why Enrich Before Indexing?

Step 1 — Preview Enrichment on 3 Rows

SELECT
    PRODUCT_ID,
    ITEM_NAME,
    BRAND,
    LEFT(DESCRIPTION, 100)                     AS original_description,
    AI_COMPLETE(
        'mistral-large2',
        CONCAT(
            'Write a rich, engaging product description for an online ski shop catalog. ',
            'Use natural language a customer would search for. Mention key use cases, ',
            'target skill level, and standout features. Keep it under 150 words.\n\n',
            'Product: ',     ITEM_NAME,    '\n',
            'Brand: ',       BRAND,        '\n',
            'Category: ',    SUBCATEGORY,  '\n',
            'Discipline: ',  DISCIPLINE,   '\n',
            'Skill Level: ', SKILL_LEVEL,  '\n',
            'Gender: ',      GENDER,       '\n',
            'Original description: ', DESCRIPTION
        )
    )                                           AS enriched_description
FROM CATALOG_SEARCH_DB.DATA.PRODUCTS
LIMIT 3;

Step 2 — Preserve Originals (Recommended)

-- Add a backup column before overwriting
ALTER TABLE CATALOG_SEARCH_DB.DATA.PRODUCTS
    ADD COLUMN DESCRIPTION_ORIGINAL VARCHAR(2000);

UPDATE CATALOG_SEARCH_DB.DATA.PRODUCTS
    SET DESCRIPTION_ORIGINAL = DESCRIPTION;

Step 3 — Enrich All Product Descriptions

-- Cost: ~1-2 credits per 1,000 rows with mistral-large2
-- Runtime: ~30-60 sec for 30 rows | ~3-5 min for 1,040 rows
UPDATE CATALOG_SEARCH_DB.DATA.PRODUCTS
SET DESCRIPTION = AI_COMPLETE(
    'mistral-large2',
    CONCAT(
        'Write a rich, engaging product description for an online ski shop catalog. ',
        'Use natural language a customer would search for. Mention key use cases, ',
        'target skill level, and standout features. Keep it under 150 words.\n\n',
        'Product: ',     ITEM_NAME,    '\n',
        'Brand: ',       BRAND,        '\n',
        'Category: ',    SUBCATEGORY,  '\n',
        'Discipline: ',  DISCIPLINE,   '\n',
        'Skill Level: ', SKILL_LEVEL,  '\n',
        'Gender: ',      GENDER,       '\n',
        'Original description: ', DESCRIPTION
    )
);

Step 4 — Rebuild SEARCH_TEXT with Enriched Descriptions

UPDATE CATALOG_SEARCH_DB.DATA.PRODUCTS
SET SEARCH_TEXT = TRIM(
    COALESCE(BRAND,        '') || ' ' ||
    COALESCE(ITEM_NAME,    '') || ' ' ||
    COALESCE(SUBCATEGORY,  '') || ' ' ||
    COALESCE(DISCIPLINE,   '') || ' ' ||
    COALESCE(SKILL_LEVEL,  '') || ' ' ||
    COALESCE(GENDER,       '') || ' ' ||
    COALESCE(DESCRIPTION,  '')
);

-- Verify the enriched SEARCH_TEXT preview
SELECT PRODUCT_ID, BRAND, ITEM_NAME,
       LEFT(SEARCH_TEXT, 250) AS search_text_preview
FROM CATALOG_SEARCH_DB.DATA.PRODUCTS
LIMIT 5;

NOTE: After completing this section, proceed to "Creating the Multi-Index Search Service". The service will index your enriched SEARCH_TEXT automatically.

Creating the Multi-Index Search Service

The Service DDL

CREATE OR REPLACE CORTEX SEARCH SERVICE CATALOG_SEARCH_DB.APP.PRODUCT_SEARCH
    TEXT INDEXES   BRAND, ITEM_NAME, SUBCATEGORY
    VECTOR INDEXES SEARCH_TEXT(model='snowflake-arctic-embed-l-v2.0')
    ATTRIBUTES     PRODUCT_ID, ITEM_NAME, BRAND, CATEGORY, SUBCATEGORY,
                   SKILL_LEVEL, DISCIPLINE, GENDER, PRICE, PRODUCT_TYPE, FLEX_RATING
    WAREHOUSE  = CATALOG_SEARCH_WH
    TARGET_LAG = '1 hour'
    AS (
        SELECT
            PRODUCT_ID,
            SEARCH_TEXT,
            ITEM_NAME,
            BRAND,
            CATEGORY,
            SUBCATEGORY,
            SKILL_LEVEL,
            DISCIPLINE,
            GENDER,
            PRICE,
            PRODUCT_TYPE,
            FLEX_RATING
        FROM CATALOG_SEARCH_DB.DATA.PRODUCTS
    );

What Each Clause Does

Verify Service Status

-- Check that the service has indexed your rows
SHOW CORTEX SEARCH SERVICES IN SCHEMA CATALOG_SEARCH_DB.APP;

-- Detailed status
DESCRIBE CORTEX SEARCH SERVICE CATALOG_SEARCH_DB.APP.PRODUCT_SEARCH;

NOTE: The TARGET_LAG setting controls how frequently the service checks for new or updated rows. For a demo/dev catalog that rarely changes, '1 hour' is appropriate. For a live production catalog, use '1 minute' or different frequency.

Querying with SEARCH_PREVIEW (SQL)

NOTE: SEARCH_PREVIEW is the easiest path to testing Multi-Index Cortex Search from SQL. The SEARCH_PREVIEW function is provided for testing and validation only. It is not intended for serving search queries in an end-user application. The function has higher latency than the REST and Python APIs. For production applications use REST or the Python SDK's svc.search() method with multi_index_query returns structured Python objects and gives you full multi-index control. See the optional SDK section below.

Test 1 — Query all indexes for search term: warm waterproof jacket for off-piste skiing"

SELECT PARSE_JSON(
    SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
        'CATALOG_SEARCH_DB.APP.PRODUCT_SEARCH',
        '{
            "query": "warm waterproof jacket for off-piste skiing",
            "columns": ["ITEM_NAME", "BRAND", "CATEGORY", "PRICE"],
            "limit": 10
        }'
    )
) AS search_results;

Test 2 — Query using multi-index query syntax, search multiple columns indepedently. It searches the TEXT INDEX on the BRAND column for the search term "ridgeline" and SEARCH_TEXT column for "warm waterproof jacket off-piste".

SELECT PARSE_JSON(
    SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
        'CATALOG_SEARCH_DB.APP.PRODUCT_SEARCH',
        '{
            "columns": ["PRODUCT_ID", "ITEM_NAME", "BRAND", "CATEGORY", "PRICE"],
            "multi_index_query": {
                "BRAND": [{"text": "ridgeline"}],
                "SEARCH_TEXT": [{"text": "warm waterproof jacket for off-piste skiing"}]
            },
            "limit": 10
        }'
    )
) AS search_results;

Test 3 — Attribute Filter (server-side filtering by SKILL_LEVEL)

SELECT PARSE_JSON(
    SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
        'CATALOG_SEARCH_DB.APP.PRODUCT_SEARCH',
        '{
            "query": "protective gear",
            "columns": ["PRODUCT_ID", "ITEM_NAME", "BRAND", "CATEGORY", "SKILL_LEVEL", "PRICE"],
            "filter": {"@eq": {"SKILL_LEVEL": "Beginner"}},
            "limit": 10
        }'
    )
) AS search_results;

NOTE: The filter clause is applied server-side before ranking — only products matching the filter are scored and returned. This is more efficient than fetching all results and filtering in your application.

Test 4 — Control Relevance with text_boosts and vector_boosts per column

SELECT PARSE_JSON(
    SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
        'CATALOG_SEARCH_DB.APP.PRODUCT_SEARCH',
        '{
            "multi_index_query": {
                "BRAND": [{"text": "ridgeline"}],
                "SEARCH_TEXT": [{"text": "warm waterproof jacket for off-piste skiing"}]
            },
            "columns": ["PRODUCT_ID", "ITEM_NAME", "BRAND", "CATEGORY", "PRICE"],
            "limit": 10,
            "scoring_config": {
                "weights": {
                    "texts": 0.3,
                    "vectors": 0.6,
                    "reranker": 0.1
                },
                "functions": {
                    "text_boosts": [
                        {"weight": 1.0, "column": "BRAND"}
                    ],
                    "vector_boosts": [
                        {"weight": 2.0, "column": "SEARCH_TEXT"}
                    ]
                }
            }
        }'
    )
) AS search_results;

TIP: Start with the defaults and adjust incrementally. A higher text_boosts weight on BRAND makes brand-name queries more precise; a higher vector_boosts weight on SEARCH_TEXT improves recall for natural-language queries.

Tuning and Optimization

1. Enrich SEARCH_TEXT for Better Vector Coverage

-- Enrich with brand taglines and synonyms if available
UPDATE CATALOG_SEARCH_DB.DATA.PRODUCTS
SET SEARCH_TEXT = TRIM(
    COALESCE(BRAND, '') || ' ' ||
    COALESCE(ITEM_NAME, '') || ' ' ||
    COALESCE(SUBCATEGORY, '') || ' ' ||
    COALESCE(DISCIPLINE, '') || ' ' ||
    COALESCE(SKILL_LEVEL, '') || ' ' ||
    COALESCE(GENDER, '') || ' ' ||
    COALESCE(DESCRIPTION, '') || ' ' ||
    -- Add brand tagline / voice if you have it
    COALESCE(BRAND_TAGLINE, '') || ' ' ||
    COALESCE(MATERIAL_TAGS, '')
);

2. Granular TEXT INDEXES for Niche Catalogs

-- Example: add SKU and MODEL_NUMBER as TEXT indexes
-- (requires recreating the service)
CREATE OR REPLACE CORTEX SEARCH SERVICE ...
    TEXT INDEXES BRAND, ITEM_NAME, SUBCATEGORY, SKU, MODEL_NUMBER
    VECTOR INDEXES SEARCH_TEXT(model='snowflake-arctic-embed-l-v2.0')
    ...

Building a Search Application

Querying with the Python SDK

Connect and Retrieve the Service

from snowflake.snowpark.context import get_active_session
from snowflake.core import Root
session = get_active_session()
root = Root(session)

svc = (
    root
    .databases["CATALOG_SEARCH_DB"]
    .schemas["APP"]
    .cortex_search_services["PRODUCT_SEARCH"]
)

The Query Function

### The Query Function
SEARCH_COLUMNS = [
    "PRODUCT_ID", "ITEM_NAME", "BRAND", "CATEGORY",
    "SUBCATEGORY", "SKILL_LEVEL", "DISCIPLINE",
    "GENDER", "PRICE", "PRODUCT_TYPE", "FLEX_RATING",
]

def multi_search(query: str, limit: int = 20) -> dict:
    """
    Query the PRODUCT_SEARCH service across all four indexes simultaneously.
    Returns a dict with 'results' (list) and category_breakdown (dict).
    """
    response = svc.search(
        multi_index_query={
            "BRAND":       [{"text": query}],   # TEXT INDEX — exact brand recall
            "ITEM_NAME":   [{"text": query}],   # TEXT INDEX — product name match
            "SUBCATEGORY": [{"text": query}],   # TEXT INDEX — category keyword
            "SEARCH_TEXT": [{"text": query}],   # VECTOR INDEX — semantic intent
        },
        columns=SEARCH_COLUMNS,
        limit=limit,
    )

    results = response.results

    # Derive category breakdown from the CATEGORY attribute on each result
    # (not from which service the result came from — there is only one service)
    category_counts = {}
    for r in results:
        cat = r.get("CATEGORY", "Unknown")
        category_counts[cat] = category_counts.get(cat, 0) + 1

    return {
        "results": results,
        "total": len(results),
        "category_breakdown": category_counts,
    }

Testing the Query

# Test 1 — brand name lookup (relies on TEXT INDEX: BRAND)
result = multi_search("ridgeline")
print(f"Total results: {result['total']}")
print(f"Category breakdown: {result['category_breakdown']}")
for r in result['results'][:5]:
    print(f"  {r['BRAND']} — {r['ITEM_NAME']} ({r['CATEGORY']})")

# Test 2 — intent query (relies on VECTOR INDEX: SEARCH_TEXT)
result = multi_search("warm waterproof jacket for off-piste skiing")
print(f"\nTotal results: {result['total']}")
for r in result['results'][:5]:
    print(f"  {r['BRAND']} — {r['ITEM_NAME']} ({r['SUBCATEGORY']})")

The Demo App: SNOWFIELD PRO

Option 1 - Running the App Locally

Prerequisites

Step 1 — Install Backend Dependencies

cd code/snow-sports-demo/backend
pip install -r requirements.txt

Step 2 — Install Frontend Dependencies

cd ../frontend && npm install

Step 3 — Start the Backend

cd ../backend
SNOWFLAKE_CONNECTION=your_connection_name uvicorn main:app --reload --port 8000

Step 4 — Start the Frontend

curl http://localhost:8000/api/health
# Expected: {"status":"ok","database":"CATALOG_SEARCH_DB"}

NOTE: The first search request takes 30–60 seconds while the Snowpark session is established. Subsequent requests are fast.

cd ../frontend
npm run dev

Step 5 — Test the App

Architecture

Browser (localhost:5173)
  │
  ├── React frontend (Vite dev server)
  │     └── POST /api/search/multi ──proxy──▶ localhost:8000
  │
  └── FastAPI backend (uvicorn)
        └── svc.search(multi_index_query={...})
              └── Cortex Search Service: CATALOG_SEARCH_DB.APP.PRODUCT_SEARCH
                    ├── TEXT INDEXES: BRAND, ITEM_NAME, SUBCATEGORY
                    └── VECTOR INDEXES: SEARCH_TEXT

Option 2 - Running the App on Snowpark Container Services (SPCS)

aside negative Trial Account Limitation: Snowpark Container Services (SPCS) is not available on Snowflake trial accounts. If you are using a trial account, you can skip this section and use the local deployment described above instead. The local deployment provides the same functionality for development and testing purposes.

Prerequisites

Step 1 — Create an Image Repository

CREATE IMAGE REPOSITORY IF NOT EXISTS CATALOG_SEARCH_DB.APP.APP_IMAGES;

-- Get the repository URL (you'll need this for docker push)
SHOW IMAGE REPOSITORIES IN SCHEMA CATALOG_SEARCH_DB.APP;
-- Note the repository_url column, e.g.: <account>.registry.snowflakecomputing.com/catalog_search_db/app/app_images

Step 2 — Build the Docker Image

docker build --platform linux/amd64 -t snow-sports-store:v1 .

NOTE: The --platform linux/amd64 flag is required even on Apple Silicon — SPCS runs on x86_64 nodes.

Step 3 — Login to the Snowflake Registry

# Using the Snowflake CLI
snow spcs image-registry login --connection your_connection_name

# Or manually with Docker
docker login <account>.registry.snowflakecomputing.com -u <username>

Step 4 — Tag and Push the Image

docker tag snow-sports-store:v1 <account>.registry.snowflakecomputing.com/catalog_search_db/app/app_images/snow-sports-store:v1

docker push <account>.registry.snowflakecomputing.com/catalog_search_db/app/app_images/snow-sports-store:v1

Step 5 — Create a Compute Pool

CREATE COMPUTE POOL IF NOT EXISTS CATALOG_SEARCH_POOL
  MIN_NODES = 1
  MAX_NODES = 1
  INSTANCE_FAMILY = CPU_X64_XS
  AUTO_SUSPEND_SECS = 300
  AUTO_RESUME = TRUE;

Step 6 — Create the SPCS Service

CREATE SERVICE CATALOG_SEARCH_DB.APP.SNOW_SPORTS_STORE
  IN COMPUTE POOL CATALOG_SEARCH_POOL
  FROM SPECIFICATION $$
spec:
  containers:
  - name: snow-sports-store
    image: /catalog_search_db/app/app_images/snow-sports-store:v1
    env:
      SNOWFLAKE_DATABASE: CATALOG_SEARCH_DB
      SNOWFLAKE_SCHEMA: APP
      SNOWFLAKE_WAREHOUSE: CATALOG_SEARCH_WH
    resources:
      requests:
        cpu: 0.5
        memory: 1Gi
      limits:
        cpu: 2
        memory: 4Gi
  endpoints:
  - name: store-ui
    port: 8000
    public: true
$$
  MIN_INSTANCES = 1
  MAX_INSTANCES = 1;

Step 7 — Verify Deployment

-- Check service status (wait for status = READY)
SELECT SYSTEM$GET_SERVICE_STATUS('CATALOG_SEARCH_DB.APP.SNOW_SPORTS_STORE');

-- Get the public endpoint URL
SHOW ENDPOINTS IN SERVICE CATALOG_SEARCH_DB.APP.SNOW_SPORTS_STORE;

Cleanup

-- Drop the database
DROP DATABASE IF EXISTS CATALOG_SEARCH_DB;

-- Drop the warehouse
DROP WAREHOUSE IF EXISTS CATALOG_SEARCH_WH;

Conclusion and Resources

What You Built

Key Takeaways

Resources