Integrate Amazon Redshift with Optimizely Feature Experimentation

Amazon Redshift is a columnar data warehouse: you load event data into it and answer questions with SQL that no dashboard was built to answer. Optimizely Feature Experimentation decides which variation of a flag each user receives. Landing those decisions in Redshift lets you join the variation a user was served to whatever else your warehouse already knows about that user — orders, subscriptions, support tickets, lifetime value, churn — and compute the metric your business actually cares about rather than the one an analytics tool happens to offer.

This is a different job from forwarding decisions to a product analytics tool. Analytics tools answer "how did the variation affect the funnel we instrumented". A warehouse answers "how did the variation affect revenue ninety days later, for customers on the annual plan, excluding refunds". If your finance team disputes an experiment result, the reconciliation happens in the warehouse.

Feature Experimentation has no Custom Analytics Integration UI and no native Redshift connector, so the bridge is a decision notification listener plus a load path. The listener is the same callback used for every other Feature Experimentation integration; the interesting engineering is what happens between the callback firing and a row appearing in a table.

How the Integration Works

The listener fires on every decide(), hands the decision to a buffer, and the buffer delivers batches to Redshift through an AWS ingest path. Nothing writes to Redshift row by row — that is the one design decision this article exists to argue for.

flowchart LR
    A["user.decide('checkout_redesign')"] --> B[DECISION notification fires]
    B --> C[In-process buffer]
    C --> D{Ingest path}
    D -->|Streaming| E[Kinesis Data Firehose]
    D -->|Batch| F[Newline-delimited JSON on S3]
    E --> G[(Redshift table)]
    F --> H[COPY command]
    H --> G
    G --> I[Join to orders, subscriptions, LTV]

Decision Notification Data

The listener's decision info object carries these fields for a flag-type decision. The Node SDK exposes camelCase keys; the Python SDK exposes snake_case keys.

Choosing an Ingest Path

There are two supported ways to get rows into Redshift, and they suit different volumes. Choose one; running both doubles your deduplication work for no benefit.

Whichever you pick, the rule that matters is the same: never INSERT one decision at a time. Redshift is a columnar store optimized for bulk loads; single-row inserts create tiny blocks, force vacuum work, and will hold a connection open on the request path of your application. A flag evaluated a million times a day is a million inserts you do not want.

Prerequisites

• Optimizely Feature Experimentation SDK for your server platform — the Node.js SDK or the Python SDK. Browser decisions need a collector endpoint; that is covered below.

• A Redshift cluster or Serverless workgroup you can create a table in, and credentials for a role that can COPY from S3.

• An S3 bucket in the same region as the warehouse, and an IAM role attached to Redshift that can read it.

• AWS SDK installed where the listener runs: @aws-sdk/client-firehose for Node, or boto3 for Python.

• A decision on identity. The user_id you write must be the same key your orders and users tables use, or the join this integration exists for cannot be made. If your application decides with an anonymous ID before login, you also need the mapping table described at the end.

The Redshift Table

Model decisions as an append-only fact table. Decisions are immutable events: a user was served a variation at a point in time, and nothing that happens later changes that.

CREATE TABLE experiment_decisions (
    decision_id       VARCHAR(64)   NOT NULL,
    decided_at        TIMESTAMP     NOT NULL,
    user_id           VARCHAR(128)  NOT NULL,
    flag_key          VARCHAR(128)  NOT NULL,
    enabled           BOOLEAN       NOT NULL,
    variation_key     VARCHAR(128),
    rule_key          VARCHAR(128),
    event_dispatched  BOOLEAN       NOT NULL,
    environment       VARCHAR(32)   NOT NULL
)
DISTSTYLE KEY
DISTKEY (user_id)
SORTKEY (flag_key, decided_at);

The DISTKEY on user_id is the point of the whole design: it colocates decision rows with the user rows they will be joined to, so the join that produces your experiment result does not redistribute the table across the cluster. The SORTKEY on (flag_key, decided_at) means a query scoped to one flag and one date range reads a fraction of the blocks.

decision_id is a deterministic hash of user, flag, rule and a time bucket rather than a random value. Every at-least-once delivery path will eventually deliver the same decision twice, and a deterministic ID is what lets you deduplicate afterwards without guessing.

Server-Side Node.js Implementation

The listener does one thing: turn a decision into a row and put it in a buffer. Delivery is somebody else's problem, on somebody else's timer.

const crypto = require('crypto');
const optimizelySdk = require('@optimizely/optimizely-sdk');
const { enums } = require('@optimizely/optimizely-sdk');
const { FirehoseClient, PutRecordBatchCommand } = require('@aws-sdk/client-firehose');

const firehose = new FirehoseClient({ region: process.env.AWS_REGION });
const DELIVERY_STREAM = process.env.DECISIONS_STREAM;
const MAX_BATCH = 500; // Firehose accepts at most 500 records per call

const buffer = [];

function decisionId(userId, flagKey, ruleKey, decidedAt) {
  // Deterministic: the same decision delivered twice hashes to the same id,
  // so a duplicate can be removed later instead of being counted twice.
  const bucket = decidedAt.slice(0, 13); // hour precision
  return crypto
    .createHash('sha256')
    .update(`${userId}|${flagKey}|${ruleKey}|${bucket}`)
    .digest('hex')
    .slice(0, 32);
}

function record(decisionInfo, userId) {
  const decidedAt = new Date().toISOString();
  const { flagKey, enabled, variationKey, ruleKey, decisionEventDispatched } = decisionInfo;

  buffer.push({
    decision_id: decisionId(userId, flagKey, ruleKey, decidedAt),
    decided_at: decidedAt.replace('T', ' ').replace('Z', ''),
    user_id: userId,
    flag_key: flagKey,
    enabled,
    variation_key: variationKey,
    rule_key: ruleKey,
    event_dispatched: decisionEventDispatched,
    environment: process.env.APP_ENV || 'production',
  });

  if (buffer.length >= MAX_BATCH) flush();
}

async function flush() {
  if (buffer.length === 0) return;
  const batch = buffer.splice(0, MAX_BATCH);
  try {
    await firehose.send(
      new PutRecordBatchCommand({
        DeliveryStreamName: DELIVERY_STREAM,
        // Newline-delimited JSON: one object per line is what COPY ... FORMAT AS JSON expects
        Records: batch.map((row) => ({ Data: Buffer.from(`${JSON.stringify(row)}\n`) })),
      }),
    );
  } catch (error) {
    // Never let a warehouse problem break a flag decision
    console.error('firehose put failed', error);
  }
}

const optimizely = optimizelySdk.createInstance({ sdkKey: process.env.OPTIMIZELY_SDK_KEY });

optimizely.onReady().then(() => {
  optimizely.notificationCenter.addNotificationListener(
    enums.NOTIFICATION_TYPES.DECISION,
    ({ type, userId, decisionInfo }) => {
      if (type !== 'flag') return;
      record(decisionInfo, userId);
    },
  );
});

setInterval(flush, 10000);
process.on('SIGTERM', flush);

The SIGTERM handler is not decoration. A container that is scaled down mid-buffer loses every decision it was holding, and those losses are not random — they cluster on deploys, which is exactly when your traffic mix changes.

Python Implementation

The Python SDK's DECISION callback takes four positional arguments and exposes snake_case keys in decision_info. Reading decision_info.get('flagKey') returns None without raising, which produces a warehouse full of null flag keys and no error anywhere.

import atexit
import hashlib
import json
import os
import threading
from datetime import datetime, timezone

import boto3
from optimizely import optimizely
from optimizely.helpers import enums

firehose = boto3.client('firehose', region_name=os.environ['AWS_REGION'])
DELIVERY_STREAM = os.environ['DECISIONS_STREAM']
MAX_BATCH = 500

_buffer = []
_lock = threading.Lock()


def decision_id(user_id, flag_key, rule_key, decided_at):
    bucket = decided_at[:13]
    digest = hashlib.sha256(f'{user_id}|{flag_key}|{rule_key}|{bucket}'.encode()).hexdigest()
    return digest[:32]


def flush():
    with _lock:
        if not _buffer:
            return
        batch = _buffer[:MAX_BATCH]
        del _buffer[:MAX_BATCH]
    try:
        firehose.put_record_batch(
            DeliveryStreamName=DELIVERY_STREAM,
            Records=[{'Data': (json.dumps(row) + '\n').encode()} for row in batch],
        )
    except Exception as error:  # a warehouse problem must never break a decision
        print(f'firehose put failed: {error}')


def on_decision(decision_type, user_id, attributes, decision_info):
    if decision_type != 'flag':
        return

    decided_at = datetime.now(timezone.utc).isoformat()
    flag_key = decision_info.get('flag_key')
    rule_key = decision_info.get('rule_key')

    row = {
        'decision_id': decision_id(user_id, flag_key, rule_key, decided_at),
        'decided_at': decided_at[:19].replace('T', ' '),
        'user_id': user_id,
        'flag_key': flag_key,
        'enabled': decision_info.get('enabled'),
        'variation_key': decision_info.get('variation_key'),
        'rule_key': rule_key,
        'event_dispatched': decision_info.get('decision_event_dispatched'),
        'environment': os.environ.get('APP_ENV', 'production'),
    }

    with _lock:
        _buffer.append(row)
        should_flush = len(_buffer) >= MAX_BATCH

    if should_flush:
        flush()


optimizely_client = optimizely.Optimizely(sdk_key=os.environ['OPTIMIZELY_SDK_KEY'])
optimizely_client.notification_center.add_notification_listener(
    enums.NotificationTypes.DECISION, on_decision
)
atexit.register(flush)

user = optimizely_client.create_user_context('user_123', {'plan': 'pro'})
decision = user.decide('checkout_redesign')
print('Variation:', decision.variation_key)

Browser Decisions

A browser cannot write to Redshift, and it must not hold AWS credentials that would let it try. When decisions are made client-side, post them to a collector endpoint you own and run the buffering above on the server.

import { createInstance, enums } from '@optimizely/optimizely-sdk';

const optimizely = createInstance({ sdkKey: '<YOUR_SDK_KEY>' });

optimizely.onReady().then(() => {
  optimizely.notificationCenter.addNotificationListener(
    enums.NOTIFICATION_TYPES.DECISION,
    ({ type, userId, decisionInfo }) => {
      if (type !== 'flag') return;

      const payload = JSON.stringify({
        userId,
        flagKey: decisionInfo.flagKey,
        enabled: decisionInfo.enabled,
        variationKey: decisionInfo.variationKey,
        ruleKey: decisionInfo.ruleKey,
        decisionEventDispatched: decisionInfo.decisionEventDispatched,
      });

      // sendBeacon survives the page unloading; fetch does not
      navigator.sendBeacon('/collect/decision', new Blob([payload], { type: 'application/json' }));
    },
  );
});

Treat everything that arrives at /collect/decision as untrusted. The userId in that payload is whatever the browser sent, so authenticate the request and overwrite the user ID from the session server-side before it reaches the buffer.

Loading with COPY

If you chose the batch path instead of Firehose, write newline-delimited JSON to S3 and load it on a schedule:

COPY experiment_decisions
FROM 's3://your-bucket/decisions/2026/08/09/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftCopyRole'
FORMAT AS JSON 'auto'
TIMEFORMAT 'auto'
REGION 'us-east-1';

FORMAT AS JSON 'auto' matches object keys to column names, which is why the buffer emits snake_case keys that already match the DDL. Load into a staging table and merge into the fact table when you need deduplication:

DELETE FROM experiment_decisions
USING experiment_decisions_staging s
WHERE experiment_decisions.decision_id = s.decision_id;

INSERT INTO experiment_decisions
SELECT * FROM experiment_decisions_staging;

TRUNCATE experiment_decisions_staging;

Verifying the Integration

1. Trigger a decision for a known test user.

2. Wait for the delivery buffer to elapse — minutes for Firehose, one scheduled run for COPY.

3. Query for the row and check every column is populated:

SELECT flag_key, variation_key, rule_key, event_dispatched, decided_at
FROM experiment_decisions
WHERE user_id = 'user_123'
ORDER BY decided_at DESC
LIMIT 10;

A row with a null variation_key and enabled = false is correct — that is a user who did not get the flag. A row with a null flag_key is the camelCase bug. No row at all, with no error in your application logs, usually means the listener was registered after the first decide() call.

Then check for duplicates, because the ingest path is at-least-once:

SELECT decision_id, COUNT(*)
FROM experiment_decisions
GROUP BY decision_id
HAVING COUNT(*) > 1;

Analyzing Experiments in Redshift

The reason for all of this is one query shape: first exposure per user, joined to an outcome your warehouse already holds.

WITH first_exposure AS (
    SELECT user_id,
           MIN(decided_at) AS exposed_at,
           MIN(variation_key) AS variation_key
    FROM experiment_decisions
    WHERE flag_key = 'checkout_redesign'
      AND event_dispatched = TRUE
      AND environment = 'production'
    GROUP BY user_id
)
SELECT e.variation_key,
       COUNT(DISTINCT e.user_id)                          AS users,
       COUNT(DISTINCT o.order_id)                         AS orders,
       SUM(o.net_revenue)                                 AS net_revenue,
       SUM(o.net_revenue) / COUNT(DISTINCT e.user_id)     AS revenue_per_user
FROM first_exposure e
LEFT JOIN orders o
       ON o.user_id = e.user_id
      AND o.created_at >= e.exposed_at
      AND o.refunded_at IS NULL
GROUP BY e.variation_key
ORDER BY e.variation_key;

Three details make this trustworthy. Filtering on event_dispatched removes rollout decisions Optimizely never counted as impressions, so your denominator matches the one in Optimizely's own results. Taking the first exposure per user stops a returning visitor from being counted once per session. And joining on o.created_at >= e.exposed_at counts only orders placed after the user saw the variation — without it, revenue earned before the experiment started is attributed to it.

Treat the output as a description, not a verdict. A warehouse query reports a difference; it does not tell you whether the difference is distinguishable from noise. Take the counts to a significance test before acting on them.

Troubleshooting

Related Reading

• Integrate Snowflake and BigQuery with Optimizely Feature Experimentation — the same fact-table pattern on the other two major warehouses.

• Integrate Segment with Optimizely Feature Experimentation — if a CDP already delivers your events to Redshift, forward the decision there instead of building this pipeline.

• Integrate mParticle with Optimizely Feature Experimentation — another managed route into the warehouse.