Redshift — Conversation Analytics in the AWS Data Stack

DISTSTYLE KEY on call_id for the conversation_signals table - this co-locates all signals for a conversation on the same node, making JOINs to the conversations table fast because both sides of the join are on the same slice. DISTSTYLE EVEN for the conversations table - uniform distribution across nodes. Wrong distribution keys cause expensive cross-node data movement (DS_DIST_ALL_NONE or DS_BCAST_INNER in your EXPLAIN plan) during every JOIN. Check SVV_TABLE_INFO.skew_rows after loading - values above 4.0 mean your distribution is uneven.

COMPOUND sort key on (call_timestamp) for the conversations table. Redshift stores data in sorted 1MB blocks - a sort key on timestamp gives excellent range query performance for time-filtered queries (WHERE call_timestamp BETWEEN ...). For the signals table, COMPOUND sort on (call_id, evaluated_at) - this means queries that filter on a specific conversation or a time range of evaluations skip entire blocks. Interleaved sort keys are rarely worth the VACUUM overhead for conversation data. Stick with COMPOUND.

Redshift does not auto-vacuum like PostgreSQL. Schedule VACUUM DELETE (reclaims space from deleted rows) and VACUUM SORT (re-sorts rows added since the last sort operation) weekly, or immediately after large loads that use the staging-table DELETE/INSERT pattern. Run ANALYZE on all tables after significant data changes - without it, the query planner uses stale statistics and may choose terrible execution plans. Use SVV_TABLE_INFO to monitor unsorted percentage and act when it exceeds 20%.

VACUUM and ANALYZE

Schedule weekly VACUUM DELETE and VACUUM SORT after bulk loads. Run ANALYZE on all tables after significant data changes. Use SVV_TABLE_INFO to check the unsorted column for sort degradation and tbl_skew_rows for distribution skew. Without VACUUM, deleted rows waste disk space and sort order degrades - queries scan more blocks than necessary. Without ANALYZE, the query planner uses stale statistics and produces bad execution plans.

Distribution skew monitoring

Check SVV_TABLE_INFO.skew_rows regularly. Values above 4.0 indicate data skew - one or more nodes hold significantly more data than others. If the signals table skews badly on call_id (some conversations produce hundreds of signals while others produce a handful), consider switching to DISTSTYLE EVEN and accepting the cross-node JOIN overhead. The trade-off is usually worth it when skew is severe.

WLM queue configuration

Separate queues for ETL loads (high memory allocation, low concurrency - 2 to 3 slots) and BI queries (lower memory per query, higher concurrency - 10 to 15 slots). Auto WLM works for most teams - Redshift dynamically allocates memory based on query needs. Switch to manual WLM only when you need guaranteed memory for specific workloads. Always set query timeouts (WLM_QUERY_SLOT_COUNT and max_execution_time) to prevent runaway queries from blocking the entire queue.

Concurrency scaling

Enable concurrency scaling for read-heavy BI workloads. Redshift spins up transient clusters to handle burst read traffic when your main cluster's WLM queues are full. The first hour per day of concurrency scaling is free. Set concurrency_scaling_mode to auto on the WLM queues that serve dashboards and analyst queries. ETL queues generally do not benefit from concurrency scaling.

Late-arriving data

Conversation data often arrives hours or days after the call - transcript processing takes time, metadata gets backfilled, and some platforms batch their API exports. Design your loading for idempotent upserts (the staging + DELETE/INSERT pattern) rather than append-only INSERTs. Late arrivals should update existing rows, not create duplicates. Use ingested_at as an audit column to track when data actually landed versus when the call happened.

Node type and cluster sizing

Start with 2x dc2.large for development - fast local SSD storage, enough compute for prototyping. Move to ra3.xlplus or ra3.4xlarge when you need managed storage (Redshift Managed Storage keeps hot data on local SSD and warm data on S3 transparently). Ra3 separates storage and compute - resize compute without re-loading data. Never start big and scale down - start small and add nodes when you see query queue times or COPY jobs exceeding your SLA.

Signal to win-rate correlation (QuickSight)

Amazon QuickSight connects natively to Redshift - no JDBC driver setup, no credential management outside of AWS. Build a dataset that joins conversation_signals to your CRM opportunity table on deal_id. Create a calculated field for win rate by signal quartile - deals where discovery_score was in the top 25% closed at what rate versus the bottom 25%? Publish the dashboard with filters for team, time period, and signal type. QuickSight's SPICE engine caches the data so dashboard refreshes do not hit the Redshift cluster on every page load.

Predictive deal scoring (Redshift ML)

Use CREATE MODEL to train a SageMaker model directly from a SQL query - SELECT signal features and deal outcomes from your tables, and Redshift handles the SageMaker integration automatically. Train on historical signals (discovery_score, risk_flag count, objection_count) plus deal outcomes (won/lost). Score active deals with a simple SELECT that includes the ML function as a column. No data movement out of Redshift, no separate ML infrastructure, no Python notebooks to maintain. Retrain monthly as your signal library grows.

Cross-source deal view (federated query)

Use federated queries to JOIN Redshift conversation_signals with Aurora PostgreSQL CRM data in a single SQL statement. No ETL pipeline to replicate CRM tables into Redshift. The query engine reads from both sources at execution time. Best for ad-hoc analysis where building a full replication pipeline is not justified - e.g. sales leadership wants a one-off report correlating conversation risk signals with pipeline stage and close date from Aurora. For regular dashboards, replicate the CRM data into Redshift for better performance.