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!
You'll be receiving a confirmation email

Follow us on twitter :)

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.