Snowflake Cortex Search: Type Casting Fix

The Issue

When using Snowflake Cortex Search with numeric ATTRIBUTES and filter comparisons (@gte, @lte, @eq), queries fail unless columns are explicitly cast to their proper types.

---

❌ Query That Errors

CREATE OR REPLACE CORTEX SEARCH SERVICE application_logs_search
  ON MESSAGE
  ATTRIBUTES LEVEL, SERVICE, ERROR, STATUS_CODE, MEMORY_PERCENT, DISK_USAGE_PERCENT, REGION
  WAREHOUSE = QUICKSTART_KAFKA_CONNECTOR_WH
  TARGET_LAG = '1 minute'
  AS (
    SELECT 
      MESSAGE,
      LEVEL,
      SERVICE,
      ERROR,
      STATUS_CODE,
      TIMESTAMP,
      REQUEST_ID,
      HOST,
      USER_ID,
      MEMORY_PERCENT,          -- ❌ No type casting
      AVAILABLE_MB,            -- ❌ No type casting
      DISK_USAGE_PERCENT,      -- ❌ No type casting
      AVAILABLE_GB,            -- ❌ No type casting
      REGION
    FROM "APPLICATION-LOGS"
  );

SELECT
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'QUICKSTART_KAFKA_CONNECTOR_DB.PUBLIC.application_logs_search',
    '{
      "query": "memory usage warning system resource",
      "columns": ["MESSAGE", "LEVEL", "SERVICE", "MEMORY_PERCENT", "AVAILABLE_MB", "REGION", "TIMESTAMP"],
      "filter": {
        "@and": [
          {"@eq": {"LEVEL": "WARN"}},
          {"@gte": {"MEMORY_PERCENT": 80}}  -- ❌ Fails on uncast ATTRIBUTE
        ]
      },
      "limit": 15
    }'
  ) AS search_results;

Error: Numeric filter operations fail because ATTRIBUTES are not properly typed.

---

✅ Fixed Query with Type Casting

CREATE OR REPLACE CORTEX SEARCH SERVICE application_logs_search
  ON MESSAGE
  ATTRIBUTES LEVEL, SERVICE, ERROR, STATUS_CODE, MEMORY_PERCENT, DISK_USAGE_PERCENT, REGION
  WAREHOUSE = QUICKSTART_KAFKA_CONNECTOR_WH
  TARGET_LAG = '1 minute'
  AS (
    SELECT 
      MESSAGE,
      LEVEL,
      SERVICE,
      ERROR,
      STATUS_CODE,
      TIMESTAMP,
      REQUEST_ID,
      HOST,
      USER_ID,
      MEMORY_PERCENT::NUMBER as MEMORY_PERCENT,              -- ✅ Explicit cast
      AVAILABLE_MB::NUMBER as AVAILABLE_MB,                  -- ✅ Explicit cast
      DISK_USAGE_PERCENT::NUMBER as DISK_USAGE_PERCENT,      -- ✅ Explicit cast
      AVAILABLE_GB::NUMBER as AVAILABLE_GB,                  -- ✅ Explicit cast
      REGION
    FROM "APPLICATION-LOGS"
  );

SELECT
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    'QUICKSTART_KAFKA_CONNECTOR_DB.PUBLIC.application_logs_search',
    '{
      "query": "memory usage warning system resource",
      "columns": ["MESSAGE", "LEVEL", "SERVICE", "MEMORY_PERCENT", "AVAILABLE_MB", "REGION", "TIMESTAMP"],
      "filter": {
        "@and": [
          {"@eq": {"LEVEL": "WARN"}},
          {"@gte": {"MEMORY_PERCENT": 80}}  -- ✅ Works with cast ATTRIBUTE
        ]
      },
      "limit": 15
    }'
  ) AS search_results;

Result: Query executes successfully with proper numeric filtering.

---

Key Takeaway

When using ATTRIBUTES for filtering in Snowflake Cortex Search:

• Always explicitly cast numeric columns to NUMBER type
• Cast string columns to VARCHAR if needed
• This ensures proper indexing and filter execution
• Prevents cryptic runtime errors during query execution

Common Types to Use

Data Type	Cast Syntax
Numbers	COLUMN_NAME::NUMBER
Strings	COLUMN_NAME::VARCHAR
Timestamps	COLUMN_NAME::TIMESTAMP
Booleans	COLUMN_NAME::BOOLEAN

---

💡 Pro Tip: Apply type casting during the search service creation, not in the query itself. This ensures consistent behavior across all searches using that service.