Deploying Apache Superset at Scale: Production-Ready BI Platform with AWS CDK and ECS Fargate

 1// RDS Configuration for Superset Metadata
 2const database = new rds.DatabaseInstance(this, 'SupersetDB', {
 3  engine: rds.DatabaseInstanceEngine.postgres({
 4    version: rds.PostgresEngineVersion.VER_15,
 5  }),
 6  instanceType: ec2.InstanceType.of(
 7    ec2.InstanceClass.R6G,  // Memory-optimized for caching
 8    ec2.InstanceSize.LARGE   // 2 vCPU, 16GB RAM
 9  ),
10  multiAz: true,  // Critical for HA
11  allocatedStorage: 100,  // Start with 100GB, auto-scale
12  maxAllocatedStorage: 500,  // Auto-scale up to 500GB
13  storageType: rds.StorageType.GP3,  // Modern SSD with better IOPS
14  backupRetention: Duration.days(7),
15  deleteProtection: true,  // Prevent accidental deletion
16  cloudwatchLogsExports: ['postgresql'],  // Export logs for analysis
17});

 1// Essential VPC setup with proper network isolation
 2const vpc = new ec2.Vpc(this, 'SupersetVpc', {
 3  maxAzs: 2,  // Multi-AZ for high availability
 4  natGateways: 1,  // Cost optimization: single NAT for non-prod
 5  subnetConfiguration: [
 6    {
 7      name: 'Public',
 8      subnetType: ec2.SubnetType.PUBLIC,
 9      cidrMask: 24,
10    },
11    {
12      name: 'Private',
13      subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS,
14      cidrMask: 24,
15    },
16    {
17      name: 'Isolated',
18      subnetType: ec2.SubnetType.PRIVATE_ISOLATED,
19      cidrMask: 24,
20    },
21  ],
22});
23
24// RDS PostgreSQL for metadata storage
25const dbSecurityGroup = new ec2.SecurityGroup(this, 'DBSecurityGroup', {
26  vpc: vpc,
27  description: 'Security group for Superset RDS instance',
28  allowAllOutbound: false,  // Least privilege
29});
30
31const database = new rds.DatabaseInstance(this, 'SupersetDatabase', {
32  engine: rds.DatabaseInstanceEngine.postgres({
33    version: rds.PostgresEngineVersion.VER_15,
34  }),
35  instanceType: ec2.InstanceType.of(
36    ec2.InstanceClass.R6G,
37    ec2.InstanceSize.LARGE
38  ),
39  vpc: vpc,
40  vpcSubnets: {
41    subnetType: ec2.SubnetType.PRIVATE_ISOLATED,  // Maximum isolation
42  },
43  multiAz: true,
44  allocatedStorage: 100,
45  maxAllocatedStorage: 500,
46  storageType: rds.StorageType.GP3,
47  storageEncrypted: true,
48  backupRetention: Duration.days(7),
49  deleteProtection: true,
50  securityGroups: [dbSecurityGroup],
51  credentials: rds.Credentials.fromGeneratedSecret('superset_admin', {
52    secretName: 'superset/db-credentials',
53  }),
54  cloudwatchLogsExports: ['postgresql'],
55  enablePerformanceInsights: true,
56  performanceInsightRetention: rds.PerformanceInsightRetention.DEFAULT,
57});

  1// ECS cluster for Superset application
  2const cluster = new ecs.Cluster(this, 'SupersetCluster', {
  3  vpc: vpc,
  4  clusterName: 'superset-production',
  5  containerInsights: true,  // Enhanced CloudWatch monitoring
  6});
  7
  8// Task definition for Superset
  9const taskDefinition = new ecs.FargateTaskDefinition(this, 'SupersetTask', {
 10  cpu: 1024,  // 1 vCPU
 11  memoryLimitMiB: 2048,  // 2GB RAM
 12  family: 'superset-app',
 13});
 14
 15// Superset container configuration
 16const supersetContainer = taskDefinition.addContainer('SupersetContainer', {
 17  image: ecs.ContainerImage.fromRegistry('apache/superset:latest'),
 18  logging: ecs.LogDrivers.awsLogs({
 19    streamPrefix: 'superset',
 20    logRetention: logs.RetentionDays.ONE_WEEK,
 21  }),
 22  environment: {
 23    SUPERSET_ENV: 'production',
 24    REDIS_HOST: redisCluster.attrRedisEndpointAddress,
 25    REDIS_PORT: redisCluster.attrRedisEndpointPort,
 26  },
 27  secrets: {
 28    DATABASE_URL: ecs.Secret.fromSecretsManager(database.secret!),
 29    SECRET_KEY: ecs.Secret.fromSecretsManager(supersetSecretKey),
 30  },
 31  healthCheck: {
 32    command: ['CMD-SHELL', 'curl -f http://localhost:8088/health || exit 1'],
 33    interval: Duration.seconds(30),
 34    timeout: Duration.seconds(5),
 35    retries: 3,
 36    startPeriod: Duration.seconds(60),
 37  },
 38  portMappings: [{
 39    containerPort: 8088,
 40    protocol: ecs.Protocol.TCP,
 41  }],
 42});
 43
 44// Application Load Balancer
 45const alb = new elbv2.ApplicationLoadBalancer(this, 'SupersetALB', {
 46  vpc: vpc,
 47  internetFacing: true,
 48  vpcSubnets: {
 49    subnetType: ec2.SubnetType.PUBLIC,
 50  },
 51  securityGroup: albSecurityGroup,
 52});
 53
 54// HTTPS listener with ACM certificate
 55const httpsListener = alb.addListener('HttpsListener', {
 56  port: 443,
 57  protocol: elbv2.ApplicationProtocol.HTTPS,
 58  certificates: [certificate],
 59  defaultAction: elbv2.ListenerAction.fixedResponse(404),
 60});
 61
 62// ECS Fargate service with load balancing
 63const service = new ecs.FargateService(this, 'SupersetService', {
 64  cluster: cluster,
 65  taskDefinition: taskDefinition,
 66  desiredCount: 3,  // Minimum 3 instances for HA
 67  minHealthyPercent: 50,  // Allow rolling updates
 68  maxHealthyPercent: 200,  // Can temporarily double capacity during deployments
 69  assignPublicIp: false,  // Private subnets only
 70  vpcSubnets: {
 71    subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS,
 72  },
 73  securityGroups: [appSecurityGroup],
 74  cloudMapOptions: {
 75    name: 'superset',
 76    dnsRecordType: servicediscovery.DnsRecordType.A,
 77  },
 78  enableExecuteCommand: true,  // Enable ECS Exec for debugging
 79});
 80
 81// Register service with load balancer
 82const targetGroup = httpsListener.addTargets('SupersetTargets', {
 83  targets: [service],
 84  port: 8088,
 85  protocol: elbv2.ApplicationProtocol.HTTP,
 86  healthCheck: {
 87    path: '/health',
 88    interval: Duration.seconds(30),
 89    timeout: Duration.seconds(5),
 90    healthyThresholdCount: 2,
 91    unhealthyThresholdCount: 3,
 92  },
 93  deregistrationDelay: Duration.seconds(30),  // Graceful shutdown
 94  stickinessCookieDuration: Duration.hours(1),  // Session affinity
 95});
 96
 97// Auto-scaling configuration
 98const scaling = service.autoScaleTaskCount({
 99  minCapacity: 3,
100  maxCapacity: 10,
101});
102
103scaling.scaleOnCpuUtilization('CpuScaling', {
104  targetUtilizationPercent: 70,
105  scaleInCooldown: Duration.seconds(300),
106  scaleOutCooldown: Duration.seconds(60),
107});
108
109scaling.scaleOnMemoryUtilization('MemoryScaling', {
110  targetUtilizationPercent: 80,
111  scaleInCooldown: Duration.seconds(300),
112  scaleOutCooldown: Duration.seconds(60),
113});

 1// One-time initialization task
 2const initTaskDefinition = new ecs.FargateTaskDefinition(this, 'InitTask', {
 3  cpu: 512,
 4  memoryLimitMiB: 1024,
 5});
 6
 7const initContainer = initTaskDefinition.addContainer('InitContainer', {
 8  image: ecs.ContainerImage.fromRegistry('apache/superset:latest'),
 9  command: [
10    '/bin/bash',
11    '-c',
12    `
13    # Initialize Superset database schema
14    superset db upgrade
15
16    # Create admin user
17    superset fab create-admin \
18      --username admin \
19      --firstname Admin \
20      --lastname User \
21      --email admin@example.com \
22      --password ${ADMIN_PASSWORD}
23
24    # Initialize Superset
25    superset init
26
27    # Load example dashboards (optional)
28    superset load_examples
29    `
30  ],
31  logging: ecs.LogDrivers.awsLogs({
32    streamPrefix: 'superset-init',
33  }),
34  secrets: {
35    DATABASE_URL: ecs.Secret.fromSecretsManager(database.secret!),
36    ADMIN_PASSWORD: ecs.Secret.fromSecretsManager(adminPasswordSecret),
37  },
38});
39
40// Run initialization via Lambda custom resource
41new cr.AwsCustomResource(this, 'SupersetInit', {
42  onCreate: {
43    service: 'ECS',
44    action: 'runTask',
45    parameters: {
46      cluster: cluster.clusterName,
47      taskDefinition: initTaskDefinition.taskDefinitionArn,
48      launchType: 'FARGATE',
49      networkConfiguration: {
50        awsvpcConfiguration: {
51          subnets: vpc.privateSubnets.map(s => s.subnetId),
52          securityGroups: [appSecurityGroup.securityGroupId],
53        },
54      },
55    },
56    physicalResourceId: cr.PhysicalResourceId.of('superset-init'),
57  },
58  policy: cr.AwsCustomResourcePolicy.fromSdkCalls({
59    resources: cr.AwsCustomResourcePolicy.ANY_RESOURCE,
60  }),
61});

 1// ElastiCache Redis for query caching
 2const cacheSubnetGroup = new elasticache.CfnSubnetGroup(this, 'CacheSubnetGroup', {
 3  description: 'Subnet group for Superset Redis cache',
 4  subnetIds: vpc.privateSubnets.map(s => s.subnetId),
 5});
 6
 7const cacheSecurityGroup = new ec2.SecurityGroup(this, 'CacheSecurityGroup', {
 8  vpc: vpc,
 9  description: 'Security group for Redis cache',
10});
11
12cacheSecurityGroup.addIngressRule(
13  appSecurityGroup,
14  ec2.Port.tcp(6379),
15  'Allow Redis access from Superset'
16);
17
18const redisCluster = new elasticache.CfnReplicationGroup(this, 'RedisCluster', {
19  replicationGroupDescription: 'Superset query cache',
20  engine: 'redis',
21  engineVersion: '7.0',
22  cacheNodeType: 'cache.r6g.large',
23  numCacheClusters: 2,  // Primary + replica
24  automaticFailoverEnabled: true,
25  multiAzEnabled: true,
26  cacheSubnetGroupName: cacheSubnetGroup.ref,
27  securityGroupIds: [cacheSecurityGroup.securityGroupId],
28  atRestEncryptionEnabled: true,
29  transitEncryptionEnabled: true,
30});

 1// ECS task role with minimal permissions
 2const taskRole = new iam.Role(this, 'SupersetTaskRole', {
 3  assumedBy: new iam.ServicePrincipal('ecs-tasks.amazonaws.com'),
 4  inlinePolicies: {
 5    'SecretsAccess': new iam.PolicyDocument({
 6      statements: [
 7        new iam.PolicyStatement({
 8          actions: ['secretsmanager:GetSecretValue'],
 9          resources: [
10            database.secret!.secretArn,
11            supersetSecretKey.secretArn,
12          ],
13        }),
14      ],
15    }),
16    'CloudWatchLogs': new iam.PolicyDocument({
17      statements: [
18        new iam.PolicyStatement({
19          actions: [
20            'logs:CreateLogStream',
21            'logs:PutLogEvents',
22          ],
23          resources: ['*'],
24        }),
25      ],
26    }),
27  },
28});
29
30// Execution role for pulling container images
31const executionRole = new iam.Role(this, 'SupersetExecutionRole', {
32  assumedBy: new iam.ServicePrincipal('ecs-tasks.amazonaws.com'),
33  managedPolicies: [
34    iam.ManagedPolicy.fromAwsManagedPolicyName(
35      'service-role/AmazonECSTaskExecutionRolePolicy'
36    ),
37  ],
38});

 1# superset_config.py - Production security settings
 2
 3# Secret key for session encryption
 4SECRET_KEY = os.environ.get('SECRET_KEY')
 5
 6# CSRF protection
 7WTF_CSRF_ENABLED = True
 8WTF_CSRF_TIME_LIMIT = None
 9
10# Authentication method
11AUTH_TYPE = AUTH_DB  # Database authentication
12# AUTH_TYPE = AUTH_OAUTH  # Or OAuth for enterprise SSO
13
14# Row-level security
15ROW_LEVEL_SECURITY = True
16
17# SQL Lab settings
18SQLLAB_ASYNC_TIME_LIMIT_SEC = 300  # 5 minute query timeout
19SQLLAB_QUERY_COST_ESTIMATE_TIMEOUT = 10
20
21# Rate limiting
22RATELIMIT_ENABLED = True
23RATELIMIT_APPLICATION = "10 per second"
24
25# Data source connection encryption
26SQLALCHEMY_DATABASE_URI_REQUIRE_SSL = True

 1// CloudWatch dashboard for Superset operations
 2const dashboard = new cloudwatch.Dashboard(this, 'SupersetDashboard', {
 3  dashboardName: 'Superset-Production-Metrics',
 4});
 5
 6// ECS service metrics
 7dashboard.addWidgets(
 8  new cloudwatch.GraphWidget({
 9    title: 'ECS Service Health',
10    left: [
11      service.metricCpuUtilization(),
12      service.metricMemoryUtilization(),
13    ],
14    right: [
15      service.metricRunningTaskCount(),
16    ],
17  }),
18  new cloudwatch.GraphWidget({
19    title: 'ALB Performance',
20    left: [
21      alb.metricRequestCount(),
22      alb.metricTargetResponseTime(),
23    ],
24    right: [
25      alb.metricHttpCodeTarget(elbv2.HttpCodeTarget.TARGET_2XX_COUNT),
26      alb.metricHttpCodeTarget(elbv2.HttpCodeTarget.TARGET_5XX_COUNT),
27    ],
28  })
29);
30
31// RDS database metrics
32dashboard.addWidgets(
33  new cloudwatch.GraphWidget({
34    title: 'Database Performance',
35    left: [
36      database.metricCPUUtilization(),
37      database.metricDatabaseConnections(),
38    ],
39    right: [
40      database.metricReadLatency(),
41      database.metricWriteLatency(),
42    ],
43  })
44);
45
46// Custom application metrics
47const dashboardLoadTime = new cloudwatch.Metric({
48  namespace: 'Superset/Application',
49  metricName: 'DashboardLoadTime',
50  statistic: 'Average',
51  period: Duration.minutes(5),
52});
53
54const queryExecutionTime = new cloudwatch.Metric({
55  namespace: 'Superset/Application',
56  metricName: 'QueryExecutionTime',
57  statistic: 'Average',
58  period: Duration.minutes(5),
59});

 1// SNS topic for operational alerts
 2const alertTopic = new sns.Topic(this, 'SupersetAlerts', {
 3  displayName: 'Superset Production Alerts',
 4});
 5
 6alertTopic.addSubscription(
 7  new subscriptions.EmailSubscription('ops-team@example.com')
 8);
 9
10// Critical alerts
11new cloudwatch.Alarm(this, 'HighErrorRate', {
12  metric: alb.metricHttpCodeTarget(
13    elbv2.HttpCodeTarget.TARGET_5XX_COUNT
14  ),
15  threshold: 10,
16  evaluationPeriods: 2,
17  alarmDescription: 'High 5XX error rate from Superset',
18  actionsEnabled: true,
19}).addAlarmAction(new cloudwatch_actions.SnsAction(alertTopic));
20
21new cloudwatch.Alarm(this, 'DatabaseHighCPU', {
22  metric: database.metricCPUUtilization(),
23  threshold: 80,
24  evaluationPeriods: 3,
25  alarmDescription: 'Database CPU usage above 80%',
26}).addAlarmAction(new cloudwatch_actions.SnsAction(alertTopic));
27
28new cloudwatch.Alarm(this, 'NoHealthyTasks', {
29  metric: service.metricRunningTaskCount(),
30  threshold: 2,
31  comparisonOperator: cloudwatch.ComparisonOperator.LESS_THAN_THRESHOLD,
32  evaluationPeriods: 2,
33  alarmDescription: 'Less than 2 healthy Superset tasks running',
34}).addAlarmAction(new cloudwatch_actions.SnsAction(alertTopic));
35
36// Performance degradation alerts
37new cloudwatch.Alarm(this, 'HighResponseTime', {
38  metric: alb.metricTargetResponseTime(),
39  threshold: 2,  // 2 second response time threshold
40  evaluationPeriods: 3,
41  alarmDescription: 'ALB response time exceeds 2 seconds',
42}).addAlarmAction(new cloudwatch_actions.SnsAction(alertTopic));

 1// For predictable workloads, use Fargate Spot for non-critical tasks
 2const service = new ecs.FargateService(this, 'SupersetService', {
 3  cluster: cluster,
 4  taskDefinition: taskDefinition,
 5  capacityProviderStrategies: [
 6    {
 7      capacityProvider: 'FARGATE_SPOT',
 8      weight: 2,  // 67% Spot
 9      base: 1,    // Always 1 on-demand
10    },
11    {
12      capacityProvider: 'FARGATE',
13      weight: 1,  // 33% On-demand
14    },
15  ],
16});
17// Potential savings: 50-70% on Fargate costs

 1// Auto-delete old CloudWatch logs
 2const logGroup = new logs.LogGroup(this, 'SupersetLogs', {
 3  retention: logs.RetentionDays.ONE_WEEK,  // Adjust based on compliance
 4});
 5
 6// RDS automated backups with lifecycle
 7const database = new rds.DatabaseInstance(this, 'SupersetDB', {
 8  backupRetention: Duration.days(7),  // Balance cost vs recovery needs
 9  preferredBackupWindow: '03:00-04:00',  // Off-peak hours
10});

 1# Lambda function to stop/start ECS services
 2# Save ~60% on dev/staging by running 12 hours/day instead of 24/7
 3
 4# Stop at 8 PM
 5aws ecs update-service --cluster superset-dev \
 6  --service superset-service --desired-count 0
 7
 8# Start at 8 AM
 9aws ecs update-service --cluster superset-dev \
10  --service superset-service --desired-count 1

 1# 1. Install dependencies
 2npm install
 3
 4# 2. Configure deployment parameters
 5export AWS_REGION=us-east-1
 6export DOMAIN_NAME=analytics.company.com
 7export CERTIFICATE_ARN=arn:aws:acm:...
 8
 9# 3. Bootstrap CDK (first-time only)
10cdk bootstrap aws://ACCOUNT_ID/us-east-1
11
12# 4. Deploy infrastructure
13cdk deploy SupersetStack
14
15# 5. Get ALB DNS name
16aws elbv2 describe-load-balancers \
17  --query 'LoadBalancers[0].DNSName' \
18  --output text
19
20# 6. Configure DNS (Route53 or external)
21# Point analytics.company.com to ALB DNS
22
23# 7. Initialize Superset (automatic via custom resource)
24# Admin credentials stored in Secrets Manager
25
26# 8. Access Superset
27# https://analytics.company.com

 1// cdk.json
 2{
 3  "context": {
 4    "dev": {
 5      "instanceCount": 1,
 6      "instanceType": "t3.small",
 7      "dbInstanceType": "db.t4g.medium",
 8      "enableRedis": false,
 9      "domainName": "dev-analytics.company.com"
10    },
11    "prod": {
12      "instanceCount": 3,
13      "instanceType": "t3.medium",
14      "dbInstanceType": "db.r6g.large",
15      "enableRedis": true,
16      "multiAz": true,
17      "domainName": "analytics.company.com"
18    }
19  }
20}

1cdk deploy --context env=prod

 1# Update container image in task definition
 2aws ecs register-task-definition \
 3  --cli-input-json file://task-def.json
 4
 5# Update service with new task definition
 6aws ecs update-service \
 7  --cluster superset-production \
 8  --service superset-service \
 9  --task-definition superset-app:LATEST \
10  --force-new-deployment

 1# Create manual snapshot before major changes
 2aws rds create-db-snapshot \
 3  --db-instance-identifier superset-db \
 4  --db-snapshot-identifier superset-backup-$(date +%Y%m%d)
 5
 6# Scale database instance (minimal downtime)
 7aws rds modify-db-instance \
 8  --db-instance-identifier superset-db \
 9  --db-instance-class db.r6g.xlarge \
10  --apply-immediately

 1# List running tasks
 2TASK_ARN=$(aws ecs list-tasks \
 3  --cluster superset-production \
 4  --service-name superset-service \
 5  --query 'taskArns[0]' --output text)
 6
 7# Execute command in container
 8aws ecs execute-command \
 9  --cluster superset-production \
10  --task ${TASK_ARN} \
11  --container SupersetContainer \
12  --interactive \
13  --command "/bin/bash"

1# superset_config.py
2SQLALCHEMY_POOL_SIZE = 20  # Max connections per instance
3SQLALCHEMY_POOL_TIMEOUT = 300
4SQLALCHEMY_MAX_OVERFLOW = 40  # Additional connections under load
5SQLALCHEMY_POOL_RECYCLE = 3600  # Recycle connections hourly

 1# Cache configuration
 2CACHE_CONFIG = {
 3    'CACHE_TYPE': 'redis',
 4    'CACHE_REDIS_URL': f'redis://{REDIS_HOST}:{REDIS_PORT}/0',
 5    'CACHE_DEFAULT_TIMEOUT': 3600,  # 1 hour default
 6}
 7
 8# Per-dashboard cache timeout
 9SUPERSET_CACHE_TIMEOUT = {
10    'daily_metrics': 3600,  # 1 hour
11    'real_time_dashboard': 300,  # 5 minutes
12    'historical_reports': 86400,  # 24 hours
13}

 1# Instrument Superset with CloudWatch metrics
 2import boto3
 3cloudwatch = boto3.client('cloudwatch')
 4
 5def log_dashboard_load_time(dashboard_id, load_time_ms):
 6    cloudwatch.put_metric_data(
 7        Namespace='Superset/Application',
 8        MetricData=[{
 9            'MetricName': 'DashboardLoadTime',
10            'Value': load_time_ms,
11            'Unit': 'Milliseconds',
12            'Dimensions': [{
13                'Name': 'DashboardId',
14                'Value': str(dashboard_id)
15            }]
16        }]
17    )

 1// Primary region (us-east-1)
 2const primaryStack = new SupersetStack(app, 'SupersetPrimary', {
 3  env: { region: 'us-east-1' },
 4});
 5
 6// Secondary region (eu-west-1)
 7const secondaryStack = new SupersetStack(app, 'SupersetSecondary', {
 8  env: { region: 'eu-west-1' },
 9});
10
11// Route53 latency-based routing
12const hostedZone = route53.HostedZone.fromLookup(this, 'Zone', {
13  domainName: 'company.com',
14});
15
16new route53.ARecord(this, 'PrimaryRecord', {
17  zone: hostedZone,
18  recordName: 'analytics',
19  target: route53.RecordTarget.fromAlias(
20    new targets.LoadBalancerTarget(primaryStack.alb)
21  ),
22  region: 'us-east-1',
23});
24
25new route53.ARecord(this, 'SecondaryRecord', {
26  zone: hostedZone,
27  recordName: 'analytics',
28  target: route53.RecordTarget.fromAlias(
29    new targets.LoadBalancerTarget(secondaryStack.alb)
30  ),
31  region: 'eu-west-1',
32});