How to schedule queries in Redshift

Galaxy Glossary

How do I schedule queries in Amazon Redshift?

CREATE SCHEDULE lets you run SQL automatically in Amazon Redshift on a cron-based timetable.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What problem does CREATE SCHEDULE solve?

Automates recurring SQL so you never log in to rerun analytics, roll-ups, or maintenance tasks.

How do I create a daily schedule?

Run the following SQL as a superuser or a user with sys:orchestration privileges.

CREATE SCHEDULE daily_midnight CRON ('0 0 * * ? *') ENABLE; -- ready to run

How do I attach a query to that schedule?

Wrap your SQL in CREATE SCHEDULED QUERY and reference daily_midnight.

CREATE SCHEDULED QUERY daily_revenue_refresh SCHEDULE daily_midnight RUN AS ROLE 'arn:aws:iam::123456789012:role/redshift-query-runner' AS INSERT INTO daily_revenue(date, revenue) SELECT order_date, SUM(total_amount) FROM Orders GROUP BY order_date;

How can I pause or delete a schedule?

Disable it temporarily or drop it permanently.

ALTER SCHEDULE daily_midnight DISABLE; DROP SCHEDULE daily_midnight;

How do I monitor scheduled executions?

Query SVV_SCHEDULED_QUERIES for status and SVL_SCHEDULED_QUERY_EXECUTION for run history and failure reasons.

Best practices for reliable automation

Keep SQL idempotent, add logging tables, specify RUN AS ROLE explicitly, and use ALLOW_CONCURRENT_EXECUTION only when duplicate runs are safe.

Why How to schedule queries in Redshift is important

How to schedule queries in Redshift Example Usage


-- Run at 01:00 UTC daily
CREATE SCHEDULE daily_1am
  CRON ('0 1 * * ? *')
  ENABLE;

-- Refresh out-of-stock report
CREATE SCHEDULED QUERY refresh_product_stock
  SCHEDULE daily_1am
  RUN AS ROLE 'arn:aws:iam::123456789012:role/redshift-query-runner'
AS
  INSERT INTO product_stock_summary (run_date, out_of_stock)
  SELECT CURRENT_DATE, COUNT(*)
  FROM Products
  WHERE stock = 0;

How to schedule queries in Redshift Syntax


-- Create a time-based schedule
CREATE SCHEDULE schedule_name
    { CRON ('cron_expression') | AT 'timestamp' }
    [ALLOW_CONCURRENT_EXECUTION]
    [ENABLE | DISABLE]
    [COMMENT 'text'];

-- Attach SQL to the schedule
CREATE SCHEDULED QUERY query_name
    SCHEDULE schedule_name
    RUN AS ROLE 'arn:aws:iam::<account>:role/<role_name>'
AS
    sql_statement;

-- Pause or remove
ALTER SCHEDULE schedule_name DISABLE;
DROP  SCHEDULE schedule_name;

Common Mistakes

Forgetting to add ENABLE. A schedule created without ENABLE never runs. Always end the CREATE SCHEDULE statement with ENABLE or run ALTER SCHEDULE … ENABLE later.
Writing non-idempotent INSERTs and enabling ALLOW_CONCURRENT_EXECUTION. If a run overlaps with the previous one, duplicate rows appear. Make the query idempotent or omit ALLOW_CONCURRENT_EXECUTION.