How to Schedule SQL Jobs in Amazon Redshift

How do I schedule recurring SQL jobs in Amazon Redshift?

The Redshift scheduler lets you run SQL statements or stored procedures automatically at defined intervals.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why schedule jobs in Amazon Redshift?

Automated jobs refresh materialized views, load data, and purge old records without manual effort. They keep analytics current and reduce operational toil.

How does Redshift job scheduling work internally?

Redshift provides built-in Scheduler objects: SCHEDULE and JOB. A SCHEDULE defines timing (CRON or rate), while a JOB binds SQL or a stored procedure to that schedule.

What is the basic workflow?

1) CREATE SCHEDULE with a cron expression. 2) CREATE JOB referencing the schedule and containing the SQL. 3) Monitor with system views like svl_scheduled_jobs.

What permissions are required?

Only superusers or users granted the pg_scheduler role can create, alter, or drop schedules and jobs.

How to create a schedule step-by-step?

Define a human-readable name and CRON string. Example: run nightly at 02:00 UTC.

Example

CREATE SCHEDULE nightly_2am ON CRON '0 2 * * *';

How to attach SQL to the schedule?

Create a job that references the schedule and executes either inline SQL or a stored procedure.

Example

CREATE JOB refresh_daily_sales ON SCHEDULE nightly_2am DO $$ REFRESH MATERIALIZED VIEW daily_sales; $$ LANGUAGE SQL;

How to check upcoming runs?

Query information_schema.scheduled_job_run_times to list the next run time and history.

How to disable or drop jobs?

Use ALTER JOB ... DISABLE to pause without losing definition, or DROP JOB to remove permanently.

Best practices for ecommerce workloads

Group related load, transform, and refresh steps under one schedule to minimize queue contention. Use descriptive names like orders_hourly_load.

Common mistakes and fixes

Wrong timezone: CRON is interpreted in UTC. Convert local time before defining schedules.

Long-running SQL: If job execution exceeds frequency, overlaps occur. Add LIMIT clauses or optimize queries.

Monitoring tips

Alert on failed runs by querying svl_scheduled_job_log for status <> 'SUCCEEDED'. Integrate with CloudWatch for notifications.

Why How to Schedule SQL Jobs in Amazon Redshift is important

How to Schedule SQL Jobs in Amazon Redshift Example Usage


-- Load yesterday's Orders and OrderItems every hour
CREATE SCHEDULE hourly_load ON RATE ( '1 hour' );

CREATE JOB load_new_orders
ON SCHEDULE hourly_load
DO $$
    INSERT INTO Orders (id, customer_id, order_date, total_amount)
    SELECT id, customer_id, order_date, total_amount
    FROM staging_orders
    WHERE order_date >= date_trunc('hour', current_timestamp) - interval '1 hour';
$$ LANGUAGE SQL;

How to Schedule SQL Jobs in Amazon Redshift Syntax


CREATE SCHEDULE schedule_name
ON { CRON 'cron_expression' | RATE ( interval ) };

CREATE JOB job_name
ON SCHEDULE schedule_name
DO $$
    -- Any SQL statement or CALL stored_procedure()
$$ LANGUAGE SQL
[OWNER TO user]
[COMMENT 'text'];

-- Ecommerce example
-- Refresh a customer lifetime value materialized view every Sunday at 03:30 UTC
CREATE SCHEDULE sunday_0330 ON CRON '30 3 * * 0';

CREATE JOB refresh_clv
ON SCHEDULE sunday_0330
DO $$
    REFRESH MATERIALIZED VIEW customer_lifetime_value;
$$ LANGUAGE SQL;

Common Mistakes

Using local time in CRON strings. Redshift interprets CRON in UTC, causing jobs to fire at unexpected hours. Convert local time to UTC before scheduling.
Forgetting to grant pg_scheduler role. Without it, job creation fails with permission errors. Grant the role or ask an admin to set up the job.