Apache Superset + Redshift: A D23.io Reference Architecture

Table of Contents

1. Why Superset + Redshift Works
2. Architecture Overview
3. Connection Patterns and Configuration
4. Query Performance and Optimization
5. Caching Strategy for Production
6. Operational Considerations
7. Security and Compliance
8. Monitoring and Troubleshooting
9. Real-World Deployment Scenarios
10. Next Steps and Implementation

---

Why Superset + Redshift Works

Apache Superset paired with Amazon Redshift forms a powerful data analytics stack that has proven itself across dozens of customer deployments at D23.io. The combination delivers fast, cost-effective dashboarding without the per-seat licensing burden of traditional BI tools. Superset is open-source, self-hosted, and built on a modern architecture that scales. Redshift is AWS’s managed data warehouse—fast, resilient, and designed for analytical workloads at scale.

But the pairing isn’t automatic. Getting production-grade performance requires understanding how these systems communicate, where bottlenecks hide, and what operational patterns work in the real world.

This guide pulls directly from live customer architectures. We’ve deployed Superset + Redshift across financial services firms in New York, government agencies in Canberra, and scale-up tech companies in Sydney. We’ve seen what works, what breaks, and how to plan for the operational quirks that emerge once you move beyond the quick-start tutorial.

If you’re evaluating this stack or already running it, this reference architecture will save you months of trial-and-error.

The Business Case

Supersets’s primary advantage is cost and control. Per-seat BI tools (Tableau, Looker, Power BI) charge per user, which creates a hard ceiling on adoption. A team of 50 analysts can cost $500k–$1M annually in licensing alone. Superset removes that constraint: you pay for compute, not seats.

Redshift complements this by offering predictable, scalable warehouse capacity. Unlike some alternatives, Redshift integrates tightly with the AWS ecosystem, making it natural for teams already running data pipelines on AWS.

The combination is especially valuable for:

• Scale-ups needing to replace per-seat BI without ripping out infrastructure
• Regulated firms (financial services, government) wanting to self-host analytics
• Teams modernising legacy monoliths where analytics is embedded, not bolted-on
• Multi-tenant SaaS platforms where analytics is a product feature, not an afterthought

Our Platform Development in Sydney team has embedded Superset + ClickHouse analytics into financial services platforms; our Platform Development in New York partners have deployed SOC 2-ready Superset stacks for hedge funds and media companies; and our Platform Development in Canberra work includes Superset architectures aligned with IRAP and sovereign cloud requirements.

---

Architecture Overview

High-Level Topology

A production Superset + Redshift deployment has five main layers:

1. Data Ingestion – ETL/ELT pipelines (Airflow, dbt, Fivetran) loading data into Redshift
2. Redshift Cluster – Managed data warehouse handling queries and storage
3. Superset Backend – Flask application, metadata database, caching layer
4. Superset Frontend – React UI for dashboard creation and exploration
5. Access Layer – Network, authentication, and query governance

The critical path runs from user query → Superset backend → Redshift cluster → result set → browser. Each hop introduces latency, and production deployments must optimise all five.

Component Breakdown

Superset Backend consists of:

• A Flask application server (stateless, horizontally scalable)
• A metadata database (PostgreSQL or MySQL) storing dashboard definitions, user permissions, and query cache
• A message broker (Celery + Redis or RabbitMQ) for async query execution
• An optional caching layer (Redis) for query results

Redshift provides:

• Columnar storage optimised for analytical queries
• Massive parallel processing (MPP) across multiple nodes
• Integrated compression and encoding
• Native support for SQL and common data formats (Parquet, CSV, JSON via S3)

The connection between them is stateless and SQL-based. Superset translates user interactions into SQL, sends it to Redshift via JDBC or the native Redshift driver, and renders results.

Why This Matters for Production

Understanding this topology prevents common mistakes:

• Treating Superset as a query engine: It isn’t. It’s a UI layer. Query logic must live in Redshift (via views, materialized tables, or optimised schemas).
• Overloading the metadata database: If you cache query results in PostgreSQL, you’ll hit I/O limits. Use Redis instead.
• Running Superset on a single server: The frontend and backend are separate concerns. Scale them independently.
• Assuming Redshift scales linearly: Redshift performance depends on cluster configuration, data distribution, and query patterns. A 2-node cluster behaves very differently from an 8-node cluster.

For teams building multi-tenant SaaS platforms or embedded analytics, this separation is critical. Our Platform Development in Toronto team has architected Superset for PIPEDA-compliant, multi-tenant deployments where each customer’s data is logically isolated but physically co-located. The architecture supports this because Superset’s permissions model and Redshift’s row-level security (RLS) can work together.

---

Connection Patterns and Configuration

Setting Up the Redshift Connection

Superset connects to Redshift via SQLAlchemy, a Python ORM that abstracts database dialects. The connection string follows this pattern:

redshift+psycopg2://username:password@cluster-endpoint:5439/database

The key components:

• Dialect: redshift+psycopg2 (or redshift:// if using the newer Redshift driver)
• Credentials: IAM-based auth is preferred over static passwords
• Endpoint: The cluster’s DNS name (e.g., my-cluster.c9akciq32.us-east-1.redshift.amazonaws.com)
• Port: 5439 (Redshift’s default)
• Database: The target database within the cluster

For secure, production-grade deployments, use SQLAlchemy Engines with IAM authentication. This requires the sqlalchemy-redshift dialect and AWS credentials (either instance profiles or temporary tokens).

IAM Authentication Best Practice

Static passwords are a compliance risk. Instead, configure Superset to assume an IAM role and generate temporary credentials:

1. Create an IAM role with redshift-data:ExecuteStatement and redshift-data:DescribeStatement permissions
2. Attach the role to the EC2 instance (or ECS task) running Superset
3. Update the connection string to use the IAM endpoint and temporary tokens
4. Credentials rotate automatically every 15 minutes

This approach aligns with SOC 2 and ISO 27001 audit requirements—no long-lived secrets in configuration files. If you’re pursuing Security Audit (SOC 2 / ISO 27001) compliance, this is table-stakes.

Connection Pooling and Concurrency

Superset uses SQLAlchemy’s connection pooling to reuse database connections. The default pool size is 10 connections per backend instance. In production, tune this based on:

• Number of concurrent users: Each dashboard load may open 2–5 connections
• Query execution time: Long-running queries hold connections
• Redshift slot count: Redshift has a maximum query concurrency limit (typically 15–50 queries per cluster, depending on configuration)

A common configuration for mid-scale deployments (100–500 daily users):

SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_size": 20,
    "max_overflow": 10,
    "pool_recycle": 3600,
    "pool_pre_ping": True,
}

This allows 20 persistent connections plus up to 10 overflow connections, recycles connections every hour (preventing stale connections), and validates connections before use.

Network Configuration

Superset and Redshift must communicate over the network. In AWS, this typically means:

1. Same VPC: Place Superset (on EC2 or ECS) and Redshift in the same VPC. Intra-VPC communication is free and low-latency.
2. Security groups: Open Redshift’s security group to allow inbound traffic on port 5439 from Superset’s security group.
3. Subnet routing: Ensure both are in subnets with proper routing (NAT gateways if Redshift is in a private subnet).
4. Enhanced VPC routing: Enable this on the Redshift cluster to force all traffic through VPC endpoints, improving security and performance.

For teams in regulated industries, this matters. Our Platform Development in Washington, D.C. work includes FedRAMP-aware architectures where Superset and Redshift sit in isolated subnets with strict network policies. The Platform Development in Ottawa team has built ITSG-33-aligned architectures for Canadian government clients, where data residency and network segmentation are non-negotiable.

Testing the Connection

Before deploying dashboards, validate the connection:

sqlalchemy-redshift --uri "redshift+psycopg2://user:pass@cluster:5439/db"

Or from the Superset UI:

1. Settings → Data Sources → Add Database
2. Paste the connection string
3. Click “Test Connection”
4. If it fails, check:
   • Cluster is running (not paused)
   • Security group allows port 5439
   • IAM role has redshift-data:GetClusterCredentials permission
   • Network can reach the endpoint (run nc -zv cluster-endpoint 5439 from the Superset server)

---

Query Performance and Optimization

Understanding Redshift Query Execution

Redshift is a columnar, MPP data warehouse. Queries execute in parallel across all nodes. Performance depends on:

1. Data distribution: How rows are spread across nodes
2. Sort keys: How data is physically ordered on disk
3. Compression: Whether columns are encoded
4. Query plan: How the query optimizer chooses to scan and join tables

Superset has no control over these—they’re Redshift schema design decisions. But Superset can influence query writing, which affects execution time by 10x or more.

Query Writing Patterns

Avoid SELECT * on large tables. Superset’s exploration mode tempts users to browse all columns. Instead:

• Create views that project only necessary columns
• Use column-level permissions to hide sensitive fields
• Materialize common aggregations (e.g., daily sales by region) as separate tables

Push filters down to Redshift. Superset can apply filters client-side (after fetching rows) or server-side (in the SQL WHERE clause). Always use server-side filters. This is automatic if you configure dashboards correctly, but custom SQL queries can violate this.

Example: Bad (client-side filter)

SELECT * FROM orders

Then filter to 2024 in Superset’s UI. This fetches millions of rows, then discards most of them.

Example: Good (server-side filter)

SELECT order_id, customer_id, amount, order_date
FROM orders
WHERE order_date >= '2024-01-01'

Redshift filters before returning rows.

Leveraging Redshift’s Strengths

Redshift excels at:

• Aggregations: GROUP BY, SUM, COUNT, AVG across millions of rows
• Joins: Joining large fact tables with dimension tables
• Time-series queries: Windowing functions, cumulative sums
• Full-table scans: Columnar storage means scanning one column is fast

It struggles with:

• Single-row lookups: Use a relational database (PostgreSQL) if you need row-level access
• Frequent updates: Redshift is append-optimised; UPDATE and DELETE are slow
• Complex procedural logic: Use a data pipeline tool (dbt, Airflow) instead

Query Monitoring in Redshift

Superset executes queries, but Redshift logs execution details. Monitor performance via:

1. Redshift Query Editor (AWS Console): See query history, runtime, rows scanned
2. SVL_QUERY_SUMMARY: System view showing query execution statistics
3. Redshift Advisor: AWS’s built-in tool suggesting optimisations

If a Superset dashboard is slow, check Redshift’s query logs:

SELECT query, starttime, endtime, (endtime - starttime) as duration_seconds
FROM svl_query_summary
WHERE query_type = 'SELECT'
ORDER BY starttime DESC
LIMIT 50;

Look for queries taking >10 seconds. These are candidates for optimisation: add indexes, materialise intermediate results, or redesign the schema.

Schema Design for Superset

Superset works best with denormalised, star-schema designs:

• Fact tables: Contain metrics (sales, clicks, events) and foreign keys to dimensions
• Dimension tables: Contain attributes (customer, product, date)

Example:

fact_sales
  - sale_id (PK)
  - customer_id (FK)
  - product_id (FK)
  - date_id (FK)
  - amount
  - quantity

dim_customer
  - customer_id (PK)
  - name
  - segment
  - country

dim_product
  - product_id (PK)
  - name
  - category
  - price

dim_date
  - date_id (PK)
  - date
  - month
  - quarter
  - year

This design allows Superset to build charts quickly without complex joins. For teams using dbt to manage data pipelines, this is the standard approach—dbt’s macro library includes star-schema templates.

Our Platform Development in Melbourne team has helped insurance and retail companies modernise legacy monoliths by extracting analytics into a Superset + Redshift layer. The schema design is critical: it decouples analytics from operational systems and allows independent scaling.

Query Caching at the Redshift Level

Redshift caches query results in memory. Identical queries execute faster on subsequent runs. Superset can leverage this by:

1. Writing queries that are deterministic (same input → same output)
2. Avoiding random functions or current timestamps in WHERE clauses
3. Scheduling dashboard refreshes during off-peak hours

But Redshift’s cache is cluster-wide, not per-user. If 100 users run the same query, they all benefit from the cache. This is powerful for dashboards with fixed time ranges (e.g., “sales this month”).

---

Caching Strategy for Production

The Caching Pyramid

Production deployments use multiple caching layers:

1. Redshift query cache (automatic, cluster-level)
2. Superset result cache (Redis, configurable TTL)
3. Frontend cache (browser, HTTP headers)
4. Materialized tables (Redshift, pre-computed aggregations)

Each layer serves a different purpose. Understanding when to use each prevents over-caching (stale data) and under-caching (slow dashboards).

Superset Result Caching with Redis

Superset can cache query results in Redis, avoiding repeated Redshift queries. Configuration:

CACHE_CONFIG = {
    "CACHE_TYPE": "redis",
    "CACHE_REDIS_HOST": "redis-endpoint",
    "CACHE_REDIS_PORT": 6379,
    "CACHE_REDIS_DB": 0,
    "CACHE_DEFAULT_TIMEOUT": 3600,  # 1 hour
}

For dashboards, set cache TTL based on data freshness requirements:

• Real-time dashboards (trading, monitoring): 0 seconds (no cache)
• Hourly dashboards (operational metrics): 300–600 seconds
• Daily dashboards (reporting): 3600–86400 seconds

Superset allows per-chart cache configuration. A single dashboard can have a mix: real-time metrics cached for 0 seconds, trend charts cached for 1 hour.

Materialized Views for Heavy Aggregations

If a dashboard query aggregates millions of rows (e.g., “total sales by region for the past 5 years”), don’t run it on-demand. Instead, materialise it:

CREATE TABLE agg_sales_by_region_daily AS
SELECT
  date_trunc('day', order_date) as date,
  region,
  SUM(amount) as total_sales,
  COUNT(*) as order_count
FROM fact_sales
GROUP BY 1, 2;

Refresh this table nightly (or hourly, depending on freshness requirements). Superset queries this pre-aggregated table instead of the raw fact table. Query time drops from 30 seconds to 100ms.

For teams using dbt, this is a standard pattern: dbt manages the materialisation and refresh schedule. Our Products page includes D23.io, our data platform, which orchestrates exactly this kind of workflow.

Cache Invalidation Strategy

Caching introduces a classic problem: stale data. Superset doesn’t know when Redshift data changes. You must manually invalidate caches or set conservative TTLs.

Common patterns:

1. Time-based TTL: Cache for N seconds, then refresh. Simple but may serve stale data.
2. Event-based invalidation: When data is loaded into Redshift, trigger a cache clear via API.
3. Hybrid: Use short TTLs (5 minutes) for dashboards, long TTLs (1 hour) for static reports.

For production, we recommend:

• Dashboards: 5–10 minute TTL
• Reports: 1 hour TTL
• Ad-hoc queries: 0 TTL (no cache)

Superset’s API allows programmatic cache management:

curl -X DELETE "http://superset-api/api/v1/cache/?keys=chart_123"

Integrate this into your data pipeline: after Redshift loads fresh data, clear the relevant caches.

Redis Deployment Considerations

Redis is a dependency for caching. In production:

1. Deploy Redis as a managed service (AWS ElastiCache) rather than self-hosting
2. Enable persistence: RDB snapshots or AOF logging prevent data loss on restart
3. Set memory limits: Redis evicts old entries when memory is full. Configure an eviction policy (LRU is common).
4. Monitor memory usage: If Redis fills up, caching stops working (Superset falls back to no cache).

A typical configuration for 100–500 users:

• Redis instance: cache.t3.medium (ElastiCache)
• Memory: 3–6 GB
• Eviction policy: allkeys-lru
• Backup: Daily snapshots to S3

---

Operational Considerations

Deployment Architecture

Superset is stateless; you can run multiple instances behind a load balancer. A production deployment typically includes:

1. Load Balancer (ALB or NLB): Distributes traffic across Superset instances
2. Superset Backend Instances (2–4 in an auto-scaling group): Flask app servers
3. Superset Scheduler (1 instance): Runs scheduled queries and alerts
4. Metadata Database (RDS PostgreSQL): Stores dashboards, users, permissions
5. Redis Cluster (ElastiCache): Query result caching
6. Redshift Cluster: Data warehouse

This architecture supports:

• High availability: If one Superset instance fails, others handle traffic
• Horizontal scaling: Add more instances during peak usage
• Separation of concerns: Scheduler runs independently, preventing long queries from blocking the UI

For teams in regulated industries, this matters. Our Platform Development in United States team has deployed Superset across multiple AWS regions with cross-region failover. Our Platform Development in Australia work includes multi-AZ deployments for financial services firms requiring 99.99% uptime.

Monitoring and Alerting

Monitor these metrics:

1. Superset API latency: Time to respond to dashboard requests
2. Query execution time: Time Redshift takes to execute queries
3. Cache hit ratio: Percentage of queries served from cache
4. Metadata database connections: Ensure connection pool doesn’t saturate
5. Redis memory usage: Prevent cache eviction
6. Redshift query queue: Redshift has a limit on concurrent queries

Set up CloudWatch alarms:

• API latency > 5 seconds: Investigate slow queries or Superset overload
• Cache hit ratio < 30%: Adjust TTLs or add more Redis memory
• Redshift query queue > 10: Scale the cluster or optimise queries

Backup and Disaster Recovery

Superset stores critical data in two places:

1. Metadata database (PostgreSQL): Dashboard definitions, user permissions, query history
2. Redshift cluster: Actual data

For disaster recovery:

• Metadata: Enable automated backups on RDS (daily snapshots, 30-day retention). Test restore procedures quarterly.
• Redshift: Enable automated snapshots (daily by default). Keep 35-day retention.
• Configuration: Version-control Superset configuration (connection strings, caching settings) in a Git repository.

Restore procedure (if metadata database is lost):

1. Restore RDS from snapshot
2. Restart Superset instances
3. Dashboards and permissions are recovered

Restore procedure (if Redshift is lost):

1. Restore Redshift from snapshot
2. Redshift data is recovered
3. Superset dashboards continue working (they query Redshift, not store data)

For teams pursuing compliance, this is critical. Our Services include Security Audit (SOC 2 / ISO 27001) support, which includes backup and disaster recovery planning aligned with audit requirements.

Patching and Upgrades

Superset releases new versions regularly. Plan upgrades carefully:

1. Test in staging: Deploy the new version to a non-production environment first
2. Check for breaking changes: Review release notes for API or configuration changes
3. Plan downtime: Upgrades may require restarting Superset instances (a few minutes of unavailability)
4. Backup before upgrading: Snapshot the metadata database in case of issues

Redshift upgrades are handled by AWS. They typically occur during your maintenance window (configurable) and take 15–30 minutes.

---

Security and Compliance

Authentication and Authorization

Superset supports multiple authentication backends:

1. Database authentication: Built-in user/password (development only)
2. LDAP: Integrate with corporate directory (Active Directory, OpenLDAP)
3. OAuth2: Integrate with cloud identity providers (Okta, Azure AD, Google Workspace)
4. SAML: Enterprise SSO

For production, use OAuth2 or SAML. This allows:

• Centralized user management
• Single sign-on (users log in once, access multiple systems)
• Audit trails (identity provider logs who accessed what)

Authorization (what users can see):

Superset’s permission model is role-based:

• Admin: Full access to all dashboards, can modify configurations
• Alpha: Can create dashboards and explore data
• Gamma: Can only view dashboards (read-only)

For fine-grained access control, use Redshift’s row-level security (RLS) combined with Superset’s dataset permissions:

CREATE RLS POLICY sales_by_region ON fact_sales
USING (region = current_user_id);

Now, when a user in the “Americas” region queries fact_sales, they only see rows for their region. This is enforced at the database level, not the application level—more secure.

Encryption

Encrypt data in transit and at rest:

1. In transit: Use TLS 1.2+ for all connections

   • Superset to browser: Enable HTTPS (ALB terminates TLS)
   • Superset to Redshift: Enable SSL in the connection string
   • Superset to Redis: Enable TLS (ElastiCache supports this)

2. At rest: Enable encryption on storage

   • RDS metadata database: Enable encryption at rest (AWS KMS)
   • Redshift cluster: Enable encryption at rest (AWS KMS)
   • Redis: ElastiCache supports encryption at rest

Audit Logging

Superset logs user actions (dashboard views, query executions). Enable these logs and ship them to a central system (CloudWatch, Splunk, ELK):

LOGGING_CONFIG = {
    "version": 1,
    "handlers": {
        "cloudwatch": {
            "class": "watchtower.CloudWatchLogHandler",
            "log_group": "/aws/superset/actions",
        }
    },
}

For compliance (SOC 2, ISO 27001), audit logs are mandatory. They prove who accessed what data and when.

Network Security

Isolate Superset and Redshift from the public internet:

1. Private subnets: Place both in subnets without internet gateways
2. Security groups: Allow only necessary traffic (Superset → Redshift on port 5439)
3. VPC endpoints: Use AWS PrivateLink to access AWS services (S3, KMS) without traversing the internet
4. Bastion host: If you need to access Superset or Redshift for administration, use a bastion host (jump server) in a public subnet

This architecture prevents data exfiltration and reduces the attack surface.

Compliance Frameworks

Superset + Redshift can be deployed to meet various compliance requirements:

• SOC 2 Type II: Requires controls over access, encryption, and audit logging
• ISO 27001: Requires information security management system (ISMS)
• HIPAA: Requires encryption, access controls, and audit trails (healthcare)
• PCI-DSS: Requires network segmentation and encryption (payment processing)
• GDPR: Requires data minimization, consent, and right to deletion

For teams pursuing compliance, use Vanta to automate compliance monitoring. Vanta integrates with AWS, checks configurations, and generates audit reports. Our teams have implemented Superset + Vanta for clients pursuing SOC 2 certification.

---

Monitoring and Troubleshooting

Common Issues and Solutions

Slow dashboard loads

1. Check Superset API latency (CloudWatch metrics)
2. Identify the slowest chart (Superset UI shows query time per chart)
3. Get the SQL query and run it in Redshift Query Editor
4. Check Redshift query plan (EXPLAIN statement)
5. Optimise the query or materialise the result

Redshift connection errors

1. Verify cluster is running (not paused)
2. Check security group allows port 5439 from Superset
3. Verify IAM role has redshift-data:GetClusterCredentials permission
4. Test connectivity: nc -zv cluster-endpoint 5439
5. Check Superset logs: docker logs superset or CloudWatch logs

Out of memory errors in Superset

1. Increase container memory (if running in Docker/ECS)
2. Reduce connection pool size (fewer concurrent connections)
3. Enable query result caching (Redis) to avoid re-executing queries
4. Materialise heavy aggregations in Redshift

Cache not being used

1. Verify Redis is running and Superset can reach it
2. Check cache TTL is > 0 (no-cache disables caching)
3. Verify queries are deterministic (same input → same output)
4. Monitor Redis memory (if full, cache is evicted)

Observability Best Practices

Instrument Superset with observability tools:

1. Metrics: Prometheus scrapes Superset metrics (request latency, query count)
2. Logs: Ship logs to CloudWatch, Splunk, or ELK
3. Traces: Use distributed tracing (X-Ray, Jaeger) to track requests across services
4. Dashboards: Create dashboards in Grafana or CloudWatch to visualize metrics

Key metrics to track:

• superset_api_request_duration_seconds: API latency
• superset_query_execution_time_seconds: Query execution time
• superset_cache_hits_total: Cache hit count
• superset_cache_misses_total: Cache miss count
• redshift_query_queue_depth: Number of queries waiting to execute

---

Real-World Deployment Scenarios

Scenario 1: Financial Services (New York)

A hedge fund needs real-time analytics on trading positions. Requirements:

• Data freshness: 5-minute latency (trades settle quickly)
• Compliance: SOC 2 Type II, no data exfiltration
• Users: 50 traders and analysts
• Data volume: 100M trades/day, 5 years historical

Architecture:

• Redshift cluster: 8 RA3 nodes (columnar storage, managed storage)
• Superset: 4 instances behind ALB, 1 scheduler
• Metadata DB: RDS PostgreSQL Multi-AZ
• Redis: ElastiCache 6 GB, Multi-AZ
• Network: Private subnets, VPC endpoints for S3 and KMS
• Caching: 5-minute TTL for real-time dashboards, 1-hour TTL for historical reports

Data pipeline:

• Trades flow into Kafka
• Kafka → Lambda → S3 → Redshift (Spectrum external tables for real-time, COPY for batch)
• dbt materialises aggregations (trades by symbol, by strategy, by counterparty)

This architecture is typical for our Platform Development in New York engagements. The combination of Superset’s low-cost, multi-user access model and Redshift’s query performance is ideal for capital markets.

Scenario 2: Government (Canberra)

A defence agency needs analytics on procurement data. Requirements:

• Data residency: Australia only (IRAP-aligned)
• Compliance: IRAP Protected, SOC 2 equivalent
• Users: 200 analysts across multiple agencies
• Data volume: 10M procurement records, 20 years historical

Architecture:

• Redshift cluster: 4 DC2 nodes in ap-southeast-2 (Sydney region)
• Superset: 2 instances, scheduler, deployed on EC2 in private subnet
• Metadata DB: RDS PostgreSQL in private subnet, encrypted at rest with AWS KMS
• Redis: ElastiCache in private subnet
• Network: No internet gateway, all traffic through VPC endpoints
• Authentication: LDAP to government directory service
• Audit: All logs shipped to CloudWatch, retained for 7 years

This is our Platform Development in Canberra standard. The emphasis is on data residency, encryption, and audit trails—not performance (government systems are rarely latency-sensitive).

Scenario 3: SaaS (Sydney)

A logistics software company embeds analytics in their product. Requirements:

• Multi-tenancy: 500+ customers, each with isolated data
• Scalability: Support 10,000+ concurrent dashboard users
• Cost: Per-seat BI is unaffordable; embedded analytics must be low-cost
• Data volume: 1B shipment records, growing 20% YoY

Architecture:

• Redshift cluster: 16 RA3 nodes (managed storage scales independently of compute)
• Superset: 20 instances in auto-scaling group, 3 schedulers
• Metadata DB: RDS PostgreSQL with read replicas for scale-out
• Redis: ElastiCache 20 GB, cluster mode enabled (horizontal sharding)
• Caching: 1-hour TTL for most dashboards, 0 TTL for real-time operational dashboards
• Row-level security: Superset dataset filters + Redshift RLS ensure each customer sees only their data

Data pipeline:

• Customer shipment data → S3 → Redshift
• dbt materialises per-customer aggregations (total shipments, on-time rate, cost per shipment)
• Superset queries materialised tables (sub-second response time)

This is typical for our Platform Development in Sydney SaaS engagements. The challenge is scaling Superset to thousands of concurrent users while keeping costs low. The solution: multi-tenant architecture with aggressive caching and materialized views.

---

Next Steps and Implementation

Planning Your Deployment

If you’re evaluating Superset + Redshift or planning a deployment, follow this sequence:

1. Define requirements

   • How many users?
   • What data volume?
   • What latency requirements?
   • What compliance frameworks?

2. Size the infrastructure

   • Redshift cluster size (2–100+ nodes, depending on data volume and query complexity)
   • Superset instance count (1–20+, depending on concurrent users)
   • Redis size (1–100 GB, depending on cache needs)

3. Design the schema

   • Extract analytics data from operational systems
   • Build a star schema (fact and dimension tables)
   • Create materialized views for heavy aggregations
   • Use dbt to manage transformations

4. Build the data pipeline

   • Ingest data into Redshift (Fivetran, Airflow, dbt)
   • Schedule refreshes (hourly, daily, depending on freshness requirements)
   • Monitor data quality

5. Deploy Superset

   • Set up infrastructure (EC2, RDS, ElastiCache, ALB)
   • Configure authentication (OAuth2 or SAML)
   • Connect to Redshift
   • Build dashboards

6. Test and optimise

   • Load test with expected user count
   • Identify slow queries, optimise
   • Tune caching TTLs
   • Monitor in production

7. Secure and comply

   • Enable encryption (TLS, at-rest)
   • Set up audit logging
   • Implement access controls
   • Document for compliance audits

Getting Help

If you’re building this from scratch, consider partnering with a team that’s done it before. PADISO has deployed Superset + Redshift across dozens of customer environments. Our Services include Platform Design & Engineering, where we handle architecture, implementation, and operational handoff.

We also offer fractional CTO support for teams building analytics as a product feature. If you’re a founder or operator scaling a data-driven company, our CTO as a Service model provides on-demand technical leadership.

For teams in specific regions, we have dedicated practices:

• Platform Development in Australia: Financial services, retail, government
• Platform Development in United States: Hedge funds, media, SaaS
• Platform Development in Toronto: Financial services, telecom, media

Superset + Redshift: Production-Ready

Apache Superset paired with Amazon Redshift is a proven, production-grade analytics stack. It delivers:

• Cost efficiency: No per-seat licensing, pay only for compute
• Scalability: Horizontal scaling of Superset, vertical scaling of Redshift
• Performance: Columnar storage and MPP execution enable sub-second queries on billions of rows
• Compliance: Self-hosted, auditable, supports encryption and row-level security
• Flexibility: Open-source, extensible, integrates with modern data stacks

The key to success is understanding the architecture, optimising queries and caching, and planning for operational requirements from the start. This reference architecture captures lessons from dozens of deployments. Use it as a blueprint for your own implementation.

Ready to move forward? Book a call with our team to discuss your analytics architecture.