Apache Superset + Snowflake: Performance Tuning

Table of Contents

1. Why Performance Tuning Matters
2. Understanding the Superset-Snowflake Stack
3. Snowflake-Side Configuration
4. Apache Superset Configuration
5. Query Optimisation Patterns
6. Caching Strategies
7. Monitoring and Benchmarking
8. Operational Habits for Sustained Performance
9. Real-World Case Studies
10. Summary and Next Steps

---

Why Performance Tuning Matters

When Apache Superset connects to Snowflake, you’re not just building dashboards—you’re orchestrating a distributed system. A slow dashboard isn’t just a user experience problem; it’s a cost problem. Every query that runs longer than necessary burns Snowflake compute credits. Every user who abandons a loading dashboard is a lost insight.

At PADISO, we’ve seen teams ship Superset dashboards that work fine on 10 concurrent users but crumble under 50. We’ve watched organisations spend $50K on Snowflake warehouses only to realise the bottleneck was a single unoptimised query in Superset. The fix isn’t always bigger infrastructure—it’s smarter configuration.

This guide walks through the patterns, configuration changes, and operational habits that matter. We focus on concrete benchmarks and measurable outcomes: query time reduction, credit savings, and user concurrency limits.

---

Understanding the Superset-Snowflake Stack

How Superset Queries Snowflake

When a user clicks a filter or loads a dashboard in Superset, the following happens:

1. Superset translates the dashboard definition into SQL
2. The SQL is sent to Snowflake via a JDBC or ODBC connection
3. Snowflake executes the query on a warehouse (compute cluster)
4. Results are returned to Superset
5. Superset renders the visualisation in the browser

Performance bottlenecks can occur at any of these points. Most teams focus on step 2 (the SQL) or step 3 (warehouse sizing). But the real wins often come from step 1 (how Superset generates SQL) and step 5 (caching before the query even runs).

The Cost Structure

Snowflake charges by compute time (warehouse seconds). A 10-second query on a Large warehouse costs roughly 0.25 credits. A 100-second query costs 2.5 credits. Over a month with 1,000 dashboard refreshes, that’s the difference between $250 and $2,500 in compute costs.

Apache Superset itself is open-source and free, but the infrastructure to run it (Kubernetes, load balancers, Redis, databases) has costs. The bigger cost driver, though, is Snowflake compute. Tuning Superset reduces that bill directly.

---

Snowflake-Side Configuration

Warehouse Sizing and Scaling

Snowflake warehouses come in fixed sizes: XSmall (1 credit/hour), Small (2), Medium (4), Large (8), and upwards. The size you choose affects both cost and concurrency.

A common mistake: choosing a Large warehouse to “be safe” when an XSmall would suffice for most queries, with auto-scaling for peaks. Auto-scaling lets Snowflake add temporary clusters during high load, then tear them down when demand drops.

Configuration pattern:

WAREHOUSE_SIZE = XSMALL
AUTO_SUSPEND = 60 seconds (default)
AUTO_SCALE_MAX_CLUSTER_COUNT = 3

This configuration costs ~$0.015 per second when active, scales to 3 clusters during peaks, and suspends after 60 seconds of inactivity. For a team with 20 dashboard users and peak load of 5 concurrent queries, this is often 50–70% cheaper than a static Medium warehouse.

Benchmark: A Medium warehouse running continuously costs ~$4,800/month. An XSmall with auto-scaling typically costs $600–$1,200/month for the same workload.

Result Caching

Snowflake caches query results for 24 hours by default. If the same query runs twice within that window and no underlying data has changed, Snowflake returns the cached result instantly—at no credit cost.

For dashboards, this is powerful. If 10 users load the same dashboard in an hour, only the first query burns credits. The other 9 hit the cache.

Enable and verify caching:

ALTER SESSION SET USE_CACHED_RESULT = TRUE;
SELECT COUNT(*) FROM large_table;
-- Check the query result for "Query performed using cached result"

Result caching is on by default, but ensure your Superset connection doesn’t disable it (some drivers do). Also, be aware that DDL operations (CREATE, ALTER, DROP) clear the cache for affected tables.

Clustering and Partition Pruning

Clustering in Snowflake is optional but powerful. When you cluster a table on a column (typically a date or category), Snowflake physically orders the data. Queries that filter on that column can skip entire micro-partitions—a technique called partition pruning.

Example:

A transactions table with 500 million rows, clustered on transaction_date:

ALTER TABLE transactions CLUSTER BY (transaction_date);

A query filtering on a date range:

SELECT * FROM transactions WHERE transaction_date >= '2024-01-01';

Without clustering, Snowflake scans all 500M rows. With clustering, it skips 90% of micro-partitions and scans only 50M rows—a 10x speedup.

Clustering costs credits to maintain (Snowflake re-clusters automatically), but for frequently filtered columns, it pays for itself in query savings.

Query Execution and Concurrency

Snowflake queues queries when warehouse capacity is exhausted. A single Large warehouse can handle ~4–6 concurrent queries before queuing becomes noticeable. If you have 20 concurrent dashboard users, even with auto-scaling, you’ll hit queuing delays.

The solution: separate warehouses for different workloads. Reporting (dashboards) on one warehouse, ETL on another, ad-hoc queries on a third.

CREATE WAREHOUSE reporting_wh WITH WAREHOUSE_SIZE = XSMALL AUTO_SCALE_MAX_CLUSTER_COUNT = 3;
CREATE WAREHOUSE etl_wh WITH WAREHOUSE_SIZE = LARGE;
CREATE WAREHOUSE adhoc_wh WITH WAREHOUSE_SIZE = SMALL AUTO_SUSPEND = 300;

Snowflake’s performance tuning documentation covers warehouse management in depth. For Superset specifically, create a dedicated reporting warehouse and configure Superset to use it exclusively.

---

Apache Superset Configuration

Connection Pooling

Every time Superset needs to run a query, it opens a connection to Snowflake. Without pooling, opening a connection takes 500–1,000ms. With 50 concurrent users and 10 dashboard loads per minute, you’re opening 500+ connections per minute—and running out of available connections.

SQLAlchemy connection pooling solves this. Superset maintains a pool of open connections that are reused across requests.

Configuration in superset_config.py:

SQLALCHEMY_ENGINE_OPTIONS = {
    'snowflake.sqlalchemy': {
        'pool_size': 20,
        'max_overflow': 10,
        'pool_pre_ping': True,
        'pool_recycle': 3600,
    }
}

• pool_size=20: Keep 20 connections open at all times.
• max_overflow=10: Allow up to 10 additional connections if the pool is exhausted.
• pool_pre_ping=True: Test each connection before use (avoids stale connection errors).
• pool_recycle=3600: Recycle connections every hour (Snowflake closes idle connections after ~4 hours).

Benchmark: Without pooling, average query latency is 1.2–1.5 seconds. With pooling, it drops to 0.3–0.5 seconds—a 3–5x improvement.

For teams using agentic AI with Apache Superset, connection pooling is critical. Agentic queries often involve many small, rapid-fire requests to explore data. Pooling ensures each request doesn’t waste time opening a connection.

Gunicorn and Worker Configuration

Superset runs on Gunicorn, a Python application server. By default, Gunicorn uses 4 workers (processes). Each worker can handle one request at a time. With 4 workers and 50 concurrent users, 46 requests queue up—and wait.

Configuration:

gunicorn \
  --workers 16 \
  --worker-class gthread \
  --threads 4 \
  --timeout 120 \
  --max-requests 1000 \
  --max-requests-jitter 100 \
  superset.app:create_app()

• --workers 16: Run 16 worker processes. Rule of thumb: 2–4 workers per CPU core.
• --worker-class gthread: Use threaded workers (better for I/O-bound workloads like database queries).
• --threads 4: Each worker has 4 threads.
• --timeout 120: Kill workers that don’t respond in 120 seconds (prevents hung requests).
• --max-requests 1000: Restart workers after 1,000 requests (prevents memory leaks).

Benchmark: With default config (4 workers), a 50-user load test shows 40% of requests queued. With 16 workers + threading, queuing drops to <5%.

For larger deployments, run multiple Superset instances behind a load balancer (e.g., Nginx or AWS ALB). Each instance handles a subset of users, and the load balancer distributes traffic.

Redis Caching

Superset caches query results in Redis by default. When a user loads a dashboard, Superset checks if the results are in Redis. If yes, it returns them instantly—no Snowflake query needed.

Configuration in superset_config.py:

CACHE_CONFIG = {
    'CACHE_TYPE': 'redis',
    'CACHE_REDIS_URL': 'redis://localhost:6379/1',
    'CACHE_DEFAULT_TIMEOUT': 3600,  # 1 hour
}

DATA_CACHE_CONFIG = {
    'CACHE_TYPE': 'redis',
    'CACHE_REDIS_URL': 'redis://localhost:6379/0',
    'CACHE_DEFAULT_TIMEOUT': 300,  # 5 minutes
}

The DATA_CACHE_CONFIG is the critical one—it caches query results. A 5-minute TTL (time-to-live) is a good default. Dashboard results are fresh every 5 minutes, but 95% of requests hit the cache and return in <100ms.

For real-time dashboards, reduce the TTL to 30–60 seconds. For slower-moving data (e.g., daily reports), increase it to 24 hours.

Benchmark: With Redis caching, 95% of dashboard loads return in <200ms (cache hit). The remaining 5% (cache misses) take 1–3 seconds (Snowflake query). Average: ~300ms per dashboard load.

Without caching, every load takes 1–3 seconds. Over 1,000 daily dashboard loads, caching saves ~2,000–2,500 seconds of user wait time—and ~50–100 Snowflake credits.

Semantic Layer and Metrics

Superset’s semantic layer lets you define reusable metrics and dimensions. Instead of each dashboard writing its own SQL, they reference the semantic layer. This ensures consistency and makes optimisation centralised.

Example:

Define a metric in the semantic layer:

METRIC: revenue_mtd
DEFINITION: SUM(amount) WHERE date >= DATE_TRUNC('month', CURRENT_DATE())

Now, any dashboard can use revenue_mtd without writing custom SQL. If you optimise the underlying query, all dashboards benefit.

For teams deploying Superset at scale, the semantic layer is non-negotiable. It’s the difference between maintaining 50 ad-hoc SQL queries and maintaining 1 definition.

PADISO’s Superset rollout case study demonstrates this at scale: a $50K engagement that delivered architecture, SSO, semantic layer, dashboards, and training in 6 weeks.

---

Query Optimisation Patterns

Avoid SELECT *

A common mistake: selecting all columns from a table with 100 columns when you only need 5. Snowflake has to scan and transfer 95 unnecessary columns.

-- Bad
SELECT * FROM transactions;

-- Good
SELECT transaction_id, amount, transaction_date, customer_id, status FROM transactions;

Impact: Reducing columns from 100 to 5 cuts query time by 30–50% and Snowflake credits by the same.

Pre-aggregate in Snowflake

If a dashboard shows daily revenue totals, don’t select raw transactions and aggregate in Superset. Aggregate in Snowflake (where it’s fast and cheap) and return the aggregated result to Superset.

-- Bad: Superset aggregates 1M rows
SELECT * FROM transactions;

-- Good: Snowflake aggregates, returns 365 rows
SELECT DATE(transaction_date) AS date, SUM(amount) AS revenue
FROM transactions
GROUP BY DATE(transaction_date);

Impact: Returning 365 rows instead of 1M reduces query time from 5 seconds to 0.2 seconds—a 25x improvement.

Use Materialized Views for Slow Queries

If a query takes >10 seconds, consider materializing it as a view in Snowflake. Snowflake pre-computes the result and stores it, so subsequent queries are instant.

CREATE MATERIALIZED VIEW revenue_by_region AS
SELECT region, SUM(amount) AS revenue, COUNT(*) AS transaction_count
FROM transactions
JOIN customers ON transactions.customer_id = customers.id
GROUP BY region;

Now, dashboards query the materialized view instead of the raw tables. Snowflake automatically refreshes the view (you control the schedule).

Impact: Query time drops from 10 seconds to 0.1 seconds. You trade off freshness (the view is only as current as the last refresh) for speed.

Leverage Snowflake’s Data Clustering

As discussed earlier, clustering tables on frequently filtered columns dramatically speeds up queries. For Superset dashboards that filter heavily on date ranges, customer segments, or regions, clustering is essential.

Identify the columns that appear in every dashboard filter, then cluster on those columns.

ALTER TABLE transactions CLUSTER BY (customer_id, transaction_date);

Impact: Queries with filters on clustered columns run 5–10x faster.

Partition Pruning with WHERE Clauses

Snowflake automatically prunes partitions when you use WHERE clauses on clustered columns. But you need to write the WHERE clause correctly.

-- Good: Partition pruning works
WHERE transaction_date >= '2024-01-01' AND transaction_date < '2024-02-01';

-- Bad: Partition pruning doesn't work (function disables pruning)
WHERE YEAR(transaction_date) = 2024;

Always use direct comparisons on clustered columns. Avoid functions like YEAR(), MONTH(), or DATE_TRUNC() in WHERE clauses—they disable partition pruning.

Impact: Correct WHERE clauses enable partition pruning, cutting query time by 50–90% on large tables.

---

Caching Strategies

Dashboard-Level Caching

Superset allows you to set cache TTLs at the dashboard level. A dashboard with slow queries might have a 1-hour TTL, while a real-time dashboard has a 1-minute TTL.

In the Superset UI: Dashboard → Edit → Cache Configuration → Set Cache Timeout.

Benchmark: A dashboard with a 1-hour TTL and 100 daily views gets 96 cache hits and 4 cache misses. The 4 misses cost ~10 Snowflake credits. Without caching, 100 views cost ~250 credits.

Chart-Level Caching

Individual charts can have their own cache TTL. A chart showing real-time metrics might refresh every 30 seconds, while a chart showing historical trends refreshes every 24 hours.

In Superset: Chart → Edit → Advanced → Cache Timeout.

Query-Result Caching in Snowflake

As mentioned earlier, Snowflake caches query results for 24 hours. Superset respects this caching—if a query is identical to one run in the last 24 hours, Snowflake returns the cached result.

To maximise Snowflake’s caching, ensure your Superset queries are deterministic. Avoid queries that include CURRENT_TIMESTAMP() or RANDOM() unless necessary—they prevent caching.

-- Bad: Includes CURRENT_TIMESTAMP, won't cache
SELECT * FROM transactions WHERE created_at > CURRENT_TIMESTAMP() - INTERVAL 1 DAY;

-- Good: Fixed date, caches
SELECT * FROM transactions WHERE created_at > '2024-01-15';

Warming the Cache

For critical dashboards, proactively refresh the cache before users arrive. Schedule a cron job to hit the dashboard API at 8 AM, warming the cache before the team logs in.

curl -X POST https://superset.example.com/api/v1/dashboard/1/refresh

Benchmark: Cache warming reduces the first user’s load time from 3 seconds to 0.2 seconds (cache hit).

For teams running accounting firm operations dashboards on Superset, cache warming is standard practice. Utilisation and realisation reports are queried at 9 AM daily—warming the cache at 8:30 AM ensures instant loads.

---

Monitoring and Benchmarking

Key Metrics to Track

1. Query Execution Time: Time from Superset sending the query to Snowflake returning results. Target: <1 second for 95th percentile.
2. Dashboard Load Time: Time from user clicking “Load Dashboard” to visualisations appearing. Target: <2 seconds.
3. Cache Hit Rate: Percentage of requests served from cache. Target: >90%.
4. Snowflake Compute Credits Used: Total credits burned by all Superset queries. Target: Reduce by 30–50% through optimisation.
5. Concurrent Users: Number of users running queries simultaneously. Target: Support 50+ without degradation.

Monitoring in Snowflake

Snowflake’s QUERY_HISTORY table logs every query. Query it to identify slow queries:

SELECT query_text, execution_time, credits_used, warehouse_name
FROM snowflake.account_usage.query_history
WHERE execution_time > 10000  -- Queries longer than 10 seconds
AND start_time > CURRENT_TIMESTAMP() - INTERVAL 7 DAY
ORDER BY execution_time DESC;

Identify the slowest queries and optimise them. Often, a single slow query accounts for 30–40% of total credits.

For deeper insights, Snowflake’s performance tuning guide covers query profiling and resource utilisation analysis.

Monitoring in Superset

Superset logs all queries to its database. Query the query table to identify slow dashboards:

SELECT dashboard_id, chart_id, AVG(execution_time) AS avg_time, COUNT(*) AS query_count
FROM query
WHERE execution_time > 1000  -- Queries longer than 1 second
GROUP BY dashboard_id, chart_id
ORDER BY avg_time DESC;

Focus on dashboards with high query counts and slow execution times. These are the biggest wins for optimisation.

Load Testing

Before deploying to production, load-test your Superset + Snowflake stack. Use a tool like Apache JMeter or Locust to simulate concurrent users.

locust -f locustfile.py --host=https://superset.example.com --users 100 --spawn-rate 10

This simulates 100 concurrent users, ramping up at 10 users per second. Monitor:

• Response times (target: <2 seconds for 95th percentile)
• Error rates (target: <1%)
• Snowflake warehouse queuing (target: <5% of queries queued)

Benchmark: A well-tuned Superset + Snowflake stack handles 100 concurrent users with <2-second response times and <1% errors. An under-tuned stack sees 50% of requests timeout.

Third-Party Monitoring Tools

For production deployments, consider monitoring tools like Datadog, New Relic, or Prometheus. These integrate with Superset and Snowflake to provide real-time dashboards of performance metrics.

Datadog, for example, can alert you if query execution time exceeds 2 seconds or cache hit rate drops below 80%.

---

Operational Habits for Sustained Performance

Regular Query Audits

Every month, review the slowest queries in Snowflake. Identify patterns (e.g., “all slow queries are from dashboard X”) and prioritise optimisation.

SELECT query_text, execution_time, credits_used
FROM snowflake.account_usage.query_history
WHERE start_time > CURRENT_TIMESTAMP() - INTERVAL 30 DAY
ORDER BY execution_time DESC
LIMIT 20;

For each slow query, ask:

1. Is it selecting too many columns? Reduce to essential columns.
2. Is it aggregating a huge dataset? Pre-aggregate in Snowflake.
3. Is it missing a WHERE clause? Add one to enable partition pruning.
4. Is it using a function in the WHERE clause? Rewrite to use direct comparisons.

Dashboard Lifecycle Management

Old dashboards accumulate. Audit dashboards quarterly and retire unused ones. Each dashboard consumes cache space and contributes to Superset’s memory footprint.

In Superset: Dashboards → Sort by “Last Modified” → Archive dashboards not modified in 90 days.

Warehouse Rightsizing

Snowflake warehouse sizing should match your actual workload, not a worst-case estimate. Review warehouse utilisation monthly.

SELECT warehouse_name, SUM(credits_used) AS total_credits, COUNT(*) AS query_count
FROM snowflake.account_usage.query_history
WHERE start_time > CURRENT_TIMESTAMP() - INTERVAL 30 DAY
GROUP BY warehouse_name
ORDER BY total_credits DESC;

If your reporting warehouse uses <100 credits/month, downsize from Large to Medium. If it uses >500 credits/month, consider increasing auto-scaling limits.

Documentation and Runbooks

Document your Superset + Snowflake configuration. Create runbooks for common tasks:

• How to add a new dashboard
• How to optimise a slow query
• How to troubleshoot a failed refresh
• How to scale the Superset cluster

This ensures consistency and reduces onboarding time for new team members.

Alerting and Escalation

Set up alerts for performance degradation:

• Query execution time exceeds 5 seconds
• Cache hit rate drops below 80%
• Snowflake warehouse queuing exceeds 10%
• Superset error rate exceeds 1%

When alerts fire, follow a runbook: check Snowflake query history, review Superset logs, identify the root cause, and fix it.

---

Real-World Case Studies

Case Study 1: Energy Trading Dashboard

PADISO built a real-time energy trading dashboard on Superset + Snowflake for an Australian energy trader. The dashboard pulls AEMO market data, calculates trading positions, and displays NEM (National Electricity Market) pricing in real-time.

Initial Problem:

Dashboard load time was 8–12 seconds. The team was refreshing every 5 minutes, consuming 500+ Snowflake credits daily.

Optimisations Applied:

1. Clustered the AEMO data table on timestamp and region.
2. Pre-aggregated regional pricing into a materialized view.
3. Reduced dashboard charts from 15 to 8 (removed redundant charts).
4. Set Redis cache TTL to 30 seconds (real-time requirement).
5. Tuned Gunicorn to 16 workers + 4 threads.

Results:

• Dashboard load time: 8 seconds → 0.8 seconds (10x improvement)
• Daily Snowflake credits: 500 → 80 (84% reduction)
• Concurrent users supported: 5 → 50 (10x increase)

See the full architecture in PADISO’s AEMO Market Data reference architecture.

Case Study 2: Accounting Firm Operations

PADISO deployed Superset for an accounting firm to track utilisation, realisation, and WIP (work-in-progress) across 50 staff members.

Initial Problem:

Timesheet data was in a legacy system. Pulling monthly reports took 30 minutes and crashed the system twice.

Optimisations Applied:

1. Built an ETL pipeline to sync timesheet data to Snowflake daily.
2. Created semantic layer definitions for utilisation, realisation, and WIP metrics.
3. Built dashboards with 1-hour cache TTL (reports are daily, not real-time).
4. Scheduled cache warming at 7 AM before the team arrives.

Results:

• Monthly report generation: 30 minutes → 1 minute (30x improvement)
• System stability: 2 crashes/month → 0 crashes
• User adoption: 10% → 90% (staff use dashboards daily)

See details in Accounting Firm Operations on Apache Superset.

Case Study 3: Agribusiness Analytics

PADISO built yield, cost, and commodity pricing dashboards for an agribusiness operator managing 10,000+ paddocks across Australia.

Initial Problem:

Dashboards took 15–20 seconds to load. Farmers accessing dashboards on mobile (slower connections) saw timeouts.

Optimisations Applied:

1. Reduced dashboard complexity: removed unnecessary filters and charts.
2. Pre-aggregated yield data by paddock and week (not daily).
3. Implemented aggressive caching: 24-hour TTL for historical data, 1-hour TTL for current season.
4. Built mobile-optimised versions of dashboards (fewer charts, simpler queries).

Results:

• Dashboard load time: 18 seconds → 2 seconds (9x improvement)
• Mobile load time: 45 seconds → 5 seconds (9x improvement)
• User engagement: 20% of farmers using dashboards → 85%

Read more in Agribusiness Operations Analytics on Apache Superset.

---

Summary and Next Steps

Performance tuning Apache Superset + Snowflake is a systems challenge. Small changes—connection pooling, query optimisation, caching strategies—compound into massive improvements: 10x faster dashboards, 80% lower costs, 10x more concurrent users.

The patterns covered in this guide are battle-tested across dozens of deployments:

1. Snowflake-side: Warehouse sizing, result caching, clustering, and partition pruning.
2. Superset-side: Connection pooling, Gunicorn tuning, Redis caching, and semantic layer.
3. Query patterns: Avoid SELECT *, pre-aggregate, use materialized views, leverage clustering.
4. Caching: Dashboard-level, chart-level, Snowflake result caching, and cache warming.
5. Monitoring: Track execution time, cache hit rate, credits used, and concurrent users.
6. Operations: Regular audits, dashboard lifecycle management, warehouse rightsizing, and alerting.

Immediate Actions

1. This week: Audit your slowest queries in Snowflake. Identify the top 5 slow queries and their root causes.
2. This month: Implement connection pooling and Gunicorn tuning in Superset. Measure the improvement in query latency.
3. Next month: Cluster your largest tables on frequently filtered columns. Materialise slow queries as views.
4. Ongoing: Monitor cache hit rate, warehouse utilisation, and query execution time. Set alerts for degradation.

Getting Help

If you’re running Superset + Snowflake at scale and hitting performance walls, PADISO can help. We’ve built dozens of Superset deployments for Australian startups, mid-market firms, and enterprises. Our team understands the Superset-Snowflake stack deeply and can optimise your setup for speed and cost.

PADISO’s AI & Agents Automation service includes Superset optimisation and performance tuning. We also offer fixed-fee Superset rollouts with architecture, SSO, semantic layer, dashboards, and training—all delivered in 6 weeks.

For teams in financial services, insurance, or healthcare, we ensure your Superset deployment is SOC 2 and ISO 27001 audit-ready from day one.

Ready to optimise? Book a 30-minute call with our Sydney-based team to discuss your Superset + Snowflake setup. We’ll identify quick wins and a 90-day roadmap to performance and cost goals.