+1 (726) 227-3497
RougeWarehouse

Redshift ML: Train and Score Models in SQL

Redshift ML lets you train a model, and then score rows with it, using nothing but SQL. Behind CREATE MODEL sits Amazon SageMaker: Redshift exports your training set to S3, runs SageMaker Autopilot on it, picks an algorithm and hyperparameters, and then compiles the winning model back into the cluster as a SQL function. From then on prediction is a local function call inside your query — no endpoint, no network hop, no per-invocation charge.

That last part is what makes it interesting for a data warehouse team. Churn scores, lead scores, fraud flags and demand forecasts that used to live in a separate Python service can live in the same table as the data they score, refreshed by the same ELT job.

This tutorial covers the setup, a supervised model end to end, the cheaper "you already know the algorithm" path, bring-your-own-model, and the LLM-backed functions that arrived with Amazon Bedrock integration. Everything works on RA3 provisioned clusters and on Redshift Serverless.

Prerequisites: the IAM role

Redshift ML needs a role that can (a) read and write an S3 bucket used for training artifacts, and (b) call SageMaker. Attach the role to the cluster or Serverless namespace, then either set it as the default or name it in every CREATE MODEL.

The policy needs, at minimum, s3:GetObject, s3:PutObject, s3:DeleteObject and s3:ListBucket on your artifact bucket, plus sagemaker:CreateAutoMLJob, sagemaker:DescribeAutoMLJob, sagemaker:CreateModel, sagemaker:CreateEndpoint*, sagemaker:InvokeEndpoint and iam:PassRole. AWS publishes a managed policy for the common case; if your security team prefers least privilege, scope the S3 statements to one prefix.

Then grant the ability to create models to the people who should have it:

GRANT CREATE MODEL TO ROLE data_science;
GRANT EXECUTE ON MODEL churn_model TO ROLE analysts;

CREATE MODEL is a privileged operation: it moves data out of the warehouse to S3. Treat the grant like CREATE EXTERNAL SCHEMA, not like SELECT.

Build a training set

Models are only as good as the flattening you do first. Use a view so that training and scoring share exactly the same feature logic — the single most common cause of a model that looks good in training and bad in production is feature drift between the two queries.

CREATE OR REPLACE VIEW ml.customer_features AS
SELECT
  c.customer_id,
  c.plan_tier,
  c.region,
  DATEDIFF(day, c.signed_up_on, CURRENT_DATE)              AS tenure_days,
  COALESCE(u.sessions_30d, 0)                              AS sessions_30d,
  COALESCE(u.active_days_30d, 0)                           AS active_days_30d,
  COALESCE(u.seats_used, 0)                                AS seats_used,
  COALESCE(s.tickets_90d, 0)                               AS tickets_90d,
  COALESCE(b.mrr, 0)                                       AS mrr,
  COALESCE(b.failed_payments_90d, 0)                       AS failed_payments_90d
FROM analytics.dim_customer c
LEFT JOIN analytics.agg_usage_30d   u ON u.customer_id = c.customer_id
LEFT JOIN analytics.agg_support_90d s ON s.customer_id = c.customer_id
LEFT JOIN analytics.agg_billing     b ON b.customer_id = c.customer_id;

Two rules worth enforcing in review:

  • No leakage. A cancellation_reason column, or any aggregate computed after the churn event, will give you 0.99 accuracy and a useless model.
  • Point-in-time correctness. For a model that predicts churn 90 days out, the features must be as of 90 days before the label, not as of today. If your warehouse has history tables, join to them as-of; if it does not, that is a data modeling problem to fix before it is a modeling problem.

Train: the AUTO path

CREATE MODEL churn_model
FROM (
  SELECT f.plan_tier,
         f.region,
         f.tenure_days,
         f.sessions_30d,
         f.active_days_30d,
         f.seats_used,
         f.tickets_90d,
         f.mrr,
         f.failed_payments_90d,
         l.churned_within_90d
  FROM ml.customer_features_asof f
  JOIN ml.churn_labels l USING (customer_id, asof_date)
  WHERE l.asof_date BETWEEN '2025-01-01' AND '2026-03-31'
)
TARGET churned_within_90d
FUNCTION predict_churn
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftML'
PROBLEM_TYPE BINARY_CLASSIFICATION
OBJECTIVE 'f1'
SETTINGS (
  S3_BUCKET 'my-redshift-ml-artifacts',
  S3_GARBAGE_COLLECT ON,
  MAX_RUNTIME 5400
);

Notes on the knobs:

  • PROBLEM_TYPE may be BINARY_CLASSIFICATION, MULTICLASS_CLASSIFICATION or REGRESSION. Omit it and Autopilot infers it from the target — fine for a quick test, but state it in anything you check into source control.
  • OBJECTIVE defaults to accuracy for classification, which is the wrong metric for an imbalanced target like churn or fraud. Use f1 (or f1macro for multiclass, mse/mae for regression).
  • MAX_RUNTIME is in seconds and caps how long Autopilot explores. 5400 (90 minutes) is a reasonable first pass; the default of 5400 can be raised for large training sets, and lowered to a few hundred seconds when you are just proving the plumbing.
  • S3_GARBAGE_COLLECT ON deletes the exported training data from S3 when training finishes. Leave it on unless you have a specific reason to keep the extract.

The statement returns immediately. Training runs asynchronously, typically 20 minutes to a couple of hours.

Watch the training job

SHOW MODEL churn_model;

The output gives model state (TRAINING, READY, FAILED), the inferred or chosen problem type, the objective, the selected algorithm (usually XGBoost, Linear Learner or MLP), the validation score, and the estimated cost so far. There is also a catalog view:

SELECT model_name, schema_name, model_state, function_name,
       target_column, model_type, train_job_name
FROM SVV_ML_MODEL_INFO
ORDER BY model_name;

Read the validation score before you use the model, not after. If f1 on a churn model comes back at 0.31, the answer is more or better features, not more Autopilot runtime.

Predict

Once the state is READY, the function exists in the database and behaves like any other scalar function:

SELECT customer_id,
       predict_churn(plan_tier, region, tenure_days, sessions_30d,
                     active_days_30d, seats_used, tickets_90d,
                     mrr, failed_payments_90d) AS churn_flag
FROM ml.customer_features
WHERE plan_tier <> 'free';

Argument order matters and must match the training query's column order. This is why the shared feature view matters: pass the same expression list in both places.

For probabilities rather than a hard label, train with OBJECTIVE 'f1' and use the probability variant Autopilot generates, or model the score as a regression. Materialize the results so BI tools read a table, not a function:

CREATE MATERIALIZED VIEW ml.mv_churn_scores AS
SELECT customer_id,
       predict_churn(plan_tier, region, tenure_days, sessions_30d,
                     active_days_30d, seats_used, tickets_90d,
                     mrr, failed_payments_90d) AS churn_flag,
       CURRENT_DATE AS scored_on
FROM ml.customer_features;

REFRESH MATERIALIZED VIEW ml.mv_churn_scores;

Scoring runs in parallel across slices, in-database, at no extra inference charge. Millions of rows per refresh is routine.

The cheaper path: AUTO OFF with XGBoost

Autopilot is a hyperparameter search, and you pay SageMaker for the search. When you already know XGBoost is the right model — which, for tabular warehouse data, it usually is — skip the search:

CREATE MODEL churn_xgb
FROM ml.training_set
TARGET churned_within_90d
FUNCTION predict_churn_xgb
IAM_ROLE default
AUTO OFF
MODEL_TYPE XGBOOST
OBJECTIVE 'binary:logistic'
PREPROCESSORS 'none'
HYPERPARAMETERS DEFAULT EXCEPT (
  NUM_ROUND '150',
  MAX_DEPTH '6',
  ETA '0.2',
  SCALE_POS_WEIGHT '8'
)
SETTINGS (S3_BUCKET 'my-redshift-ml-artifacts');

Training drops from hours to minutes and the cost drops by an order of magnitude. SCALE_POS_WEIGHT is how you handle class imbalance directly; set it near the ratio of negatives to positives. With PREPROCESSORS 'none' you are responsible for encoding categoricals yourself — do it in the feature view with CASE expressions or a join to a lookup table.

There is also MODEL_TYPE KMEANS for clustering (AUTO OFF, K in the hyperparameters, no TARGET), useful for segmentation you want to keep entirely inside the warehouse.

Bring your own model

If the data science team trains in SageMaker with their own tooling, you can still call it from SQL. Two forms:

Local inference — the model artifact is compiled into Redshift, prediction is free and offline-capable:

CREATE MODEL fraud_byom
FROM 's3://my-models/fraud/xgb-2026-04/model.tar.gz'
FUNCTION predict_fraud (DECIMAL(12,2), INT, VARCHAR(2), INT)
RETURNS DECIMAL(8,6)
IAM_ROLE default
SETTINGS (S3_BUCKET 'my-redshift-ml-artifacts');

Remote inference — Redshift calls an existing SageMaker endpoint per batch of rows. Use this for models Redshift cannot compile locally (deep learning, custom containers), and budget for endpoint cost and latency:

CREATE MODEL demand_remote
FUNCTION predict_demand (INT, INT, DECIMAL(10,2))
RETURNS DECIMAL(10,2)
SAGEMAKER 'demand-forecast-prod'
IAM_ROLE default;

Remote inference makes a network call for every batch, so it belongs in a scheduled job over a bounded row set, not in an interactive dashboard query.

LLM functions with Bedrock

The newer half of Redshift ML is text. You can register an Amazon Bedrock foundation model as an external model and call it in SQL for classification, summarization and extraction over text columns already in the warehouse:

CREATE EXTERNAL MODEL ticket_sentiment
FUNCTION ticket_sentiment_fn
IAM_ROLE 'arn:aws:iam::111111111111:role/RedshiftBedrock'
MODEL_TYPE BEDROCK
SETTINGS (
  MODEL_ID 'anthropic.claude-3-5-haiku-20241022-v1:0',
  PROMPT 'Classify the sentiment of this support ticket as POSITIVE, NEUTRAL or NEGATIVE. Answer with one word only:',
  REQUEST_TYPE UNIFIED,
  RESPONSE_TYPE VARCHAR
);

SELECT ticket_id,
       ticket_sentiment_fn(body) AS sentiment
FROM support.tickets
WHERE created_at >= DATEADD(day, -1, GETDATE());

Three cautions from production use. First, this is a per-token API call per row: run it over a day's increment, not the full history, and store the result. Second, Bedrock has throughput limits — a query over a million rows will throttle, so batch it. Third, the model can return anything; constrain the prompt and validate the output before it lands in a column your dashboards trust.

Operating models over time

  • Retrain on a schedule. Redshift ML does not retrain automatically. Wrap CREATE MODEL in a scheduled query or your orchestrator, train into a new name (churn_model_v7), compare validation scores, then repoint the materialized view.
  • Track drift cheaply. Store the score distribution at each refresh. A sudden shift in the mean predicted probability, with no change in the business, means the input distribution moved.
  • Clean up. DROP MODEL churn_model; removes the model and its function. Old models keep occupying catalog space and confusing analysts.
  • Know the cost. Training is a SageMaker charge that shows up outside your Redshift bill; local inference is free; remote inference and Bedrock calls are per-call. SHOW MODEL reports an estimated training cost, and the Autopilot job is visible in the SageMaker console under its train_job_name.
  • Governance. Training exports data to S3. If the training set contains regulated fields, encrypt the bucket with a CMK, keep S3_GARBAGE_COLLECT ON, and check whether your obligations allow the export at all.

Where this fits

Redshift ML is not a replacement for a machine learning platform. It is a very good fit for tabular predictions over data that already lives in the warehouse, where the consumers are SQL users and dashboards, and where the alternative is a pipeline that exports to a service and imports the scores back. That covers a surprising share of the "we want ML" requests a data team receives.

If you want help deciding which of your candidate use cases are actually Redshift ML shaped — and building the point-in-time feature views that make them work — that is what our Redshift ML & Generative SQL and Data Modeling & Architecture services do. Get in touch with the use case and we will tell you honestly whether SQL is the right place for it.