Cortex AISQL queries running slow on mobile analytics dashboards

BA/BI Snowflake
, , , , , , , ,
1

We’ve built mobile analytics dashboards using Snowflake Cortex AISQL on snow-7.0 and experiencing significant performance issues. Dashboard load times are 15-20 seconds when users drill down into specific metrics.

Our setup uses Cortex AISQL to enable natural language queries over mobile app event data (500M+ rows). The semantic layer is defined but queries still scan large partitions. Here’s a typical slow query pattern:

SELECT date, user_segment, SUM(session_duration)
FROM mobile_events
WHERE date >= DATEADD(month, -3, CURRENT_DATE())
GROUP BY date, user_segment;

We have Dynamic Tables configured for incremental updates but they don’t seem to be helping with query performance. The dashboard becomes unusable during peak hours when multiple users are accessing it simultaneously.

Has anyone optimized Cortex AISQL queries for mobile analytics? Should we be pre-aggregating data differently or is there a better approach to the semantic layer configuration?

2

I’ve seen similar issues with Cortex AISQL on large datasets. The key is understanding that Cortex still needs to execute queries against your base tables unless you optimize the underlying data structure. Dynamic Tables help with freshness but not necessarily query performance if they’re just replicating the base table structure. Have you looked at your query execution plans? Check if partition pruning is happening effectively.

3

Thanks for the suggestions. I checked the execution plans and you’re right - full table scans are happening despite having date filters. We do have clustering on date but maybe not optimally configured. Could you share more details on how you structured your pre-aggregated Dynamic Tables? Did you create separate tables for different time granularities?

4

For mobile analytics with Cortex AISQL, you need a three-tier aggregation strategy. Raw events table with tight clustering, hourly aggregates for last 7 days, daily aggregates for historical data. Configure your semantic layer to route queries intelligently based on time range. Also enable result caching aggressively and consider materialized views for your most common dashboard queries. The semantic layer metadata should include hints about which aggregation level to use.

5

Let me provide a comprehensive solution addressing all three optimization areas for your Cortex AISQL mobile analytics dashboards.

Cortex AISQL Query Optimization: The core issue is that Cortex AISQL generates queries against your base tables without understanding your materialization strategy. You need to configure the semantic layer with explicit aggregation hints. Update your semantic model to include pre-computed metrics and dimensions that map to your aggregate tables. Use the CORTEX_ANALYST_CONFIG to specify query rewrite rules that redirect time-range queries to appropriate aggregation levels.

Dynamic Table Incremental Updates: Restructure your Dynamic Tables with proper incremental refresh and clustering:

CREATE DYNAMIC TABLE mobile_events_hourly
TARGET_LAG = '1 hour'
WAREHOUSE = analytics_wh
CLUSTER BY (DATE_TRUNC('hour', event_timestamp), user_segment)
AS SELECT
  DATE_TRUNC('hour', event_timestamp) as hour,
  user_segment,
  COUNT(*) as event_count,
  SUM(session_duration) as total_duration
FROM mobile_events
GROUP BY 1, 2;

Create similar daily aggregates with TARGET_LAG = ‘1 day’ for historical queries. This ensures incremental updates happen efficiently without full table scans.

Semantic Layer Pre-aggregation: Configure your semantic layer to route queries intelligently. Define measures that reference pre-aggregated tables based on query time range. For queries spanning more than 7 days, route to daily aggregates. For recent data (last 7 days), use hourly aggregates. Include metadata in your semantic definitions:

CREATE OR REPLACE SEMANTIC MODEL mobile_analytics_model
WITH (
  base_table = 'mobile_events',
  aggregation_policy = 'time_based',
  aggregate_tables = ['mobile_events_hourly', 'mobile_events_daily']
);

Additional Optimizations:

  • Enable search optimization on user_segment and other frequently filtered dimensions
  • Set up result caching with appropriate cache TTL for dashboard queries
  • Use a dedicated medium or large warehouse with auto-scaling for dashboard workloads
  • Configure automatic clustering maintenance on your aggregate tables
  • Implement query result persistence for common dashboard views

Monitoring: Set up query monitoring to track Cortex AISQL-generated queries. Use QUERY_HISTORY to identify which queries are still hitting base tables instead of aggregates. Adjust your semantic layer configuration based on actual query patterns.

This three-pronged approach should reduce your dashboard load times from 15-20 seconds to under 3 seconds. The key is ensuring Cortex AISQL understands and uses your materialization strategy through proper semantic layer configuration.