How to Automate Reports in Redshift

How do I automate reports in Amazon Redshift?

Automating reports in Redshift means using stored procedures plus AWS scheduling services to run SQL and persist or export results without manual effort.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why automate reports in Redshift?

Automated reports remove manual query runs, guarantee fresh metrics, and cut compute costs by materializing lightweight result tables that BI tools read instead of heavy joins.

Which objects should I use?

Use a PL/pgSQL stored procedure for the reporting logic, then trigger it with Amazon EventBridge + Redshift Data API for fully managed scheduling.

What is the exact procedure syntax?

CREATE OR REPLACE PROCEDURE, a LANGUAGE plpgsql block, and a CALL statement execute the logic. See the Syntax section below.

How do I schedule the run?

Create an EventBridge cron rule and add a Redshift Data API target that calls the stored procedure. No Lambda is required.

How can I export results to S3?

Add an UNLOAD command inside the procedure or a second procedure: UNLOAD 'SELECT * FROM reporting.daily_sales_report' TO 's3://company-reports/daily/' IAM_ROLE 'arn:aws:iam::123:role/redshift-s3' PARALLEL OFF CSV GZIP;

Practical example: daily sales summary

The example_query section shows a full stored procedure that builds a daily sales table from Orders, OrderItems, and Products, then schedules it for 06:00 UTC every day.

Best practices for automated reports

Keep procedures idempotent, store reports in a dedicated reporting schema, log runtimes with RAISE INFO, and grant SELECT-only permissions to analysts.

Common mistakes and fixes

See the Common Mistake fields below for details on permission issues and duplicate data.

Can I change the schedule without code?

Yes. Store cron expressions in a config table and update the EventBridge rule with aws events put-rule during deployments.

Do I need AWS Lambda?

No. Lambda is optional. EventBridge calling the Redshift Data API is enough unless you need preprocessing or cross-cluster orchestration.

Why How to Automate Reports in Redshift is important

How to Automate Reports in Redshift Example Usage


-- Build and schedule a daily sales report
CREATE OR REPLACE PROCEDURE reporting.generate_daily_sales_report()
LANGUAGE plpgsql
AS $$
BEGIN
  DROP TABLE IF EXISTS reporting.daily_sales_report;
  CREATE TABLE reporting.daily_sales_report AS
  SELECT
      o.order_date::date        AS report_date,
      SUM(oi.quantity*p.price)  AS total_revenue,
      COUNT(DISTINCT o.id)      AS orders_count
  FROM Orders o
  JOIN OrderItems oi ON oi.order_id = o.id
  JOIN Products p    ON p.id = oi.product_id
  WHERE o.order_date::date = current_date - 1
  GROUP BY 1;
END;
$$;

-- One-time call to verify
CALL reporting.generate_daily_sales_report();

-- Schedule at 06:00 UTC daily
aws events put-rule --name redshift-daily-sales-report --schedule-expression "cron(0 6 * * ? *)"

aws events put-targets --rule redshift-daily-sales-report --targets '[{"Id":"1","Arn":"arn:aws:redshift:us-east-1:123456789012:cluster:dev","RoleArn":"arn:aws:iam::123456789012:role/redshift-data-api","Input":"{\"Sql\":\"CALL reporting.generate_daily_sales_report();\"}"}]'

How to Automate Reports in Redshift Syntax


-- 1. Create reporting schema (optional)
CREATE SCHEMA IF NOT EXISTS reporting;

-- 2. Create or update stored procedure
CREATE OR REPLACE PROCEDURE reporting.generate_daily_sales_report()
LANGUAGE plpgsql
AS $$
BEGIN
    DROP TABLE IF EXISTS reporting.daily_sales_report;
    CREATE TABLE reporting.daily_sales_report AS
    SELECT
        o.order_date::date            AS report_date,
        SUM(oi.quantity * p.price)    AS revenue,
        COUNT(DISTINCT o.id)          AS orders_count,
        COUNT(DISTINCT o.customer_id) AS customers_count
    FROM Orders o
    JOIN OrderItems oi ON oi.order_id = o.id
    JOIN Products   p  ON p.id        = oi.product_id
    WHERE o.order_date::date = current_date - 1
    GROUP BY 1;
END;
$$;

-- 3. Manual execution
CALL reporting.generate_daily_sales_report();

-- 4. Schedule through EventBridge & Data API (CLI)
aws events put-rule \
  --name "redshift-daily-sales-report" \
  --schedule-expression "cron(0 6 * * ? *)"

aws events put-targets \
  --rule redshift-daily-sales-report \
  --targets '[
      {
        "Id":"1",
        "Arn":"arn:aws:redshift:us-east-1:123456789012:cluster:dev",
        "RoleArn":"arn:aws:iam::123456789012:role/redshift-data-api",
        "Input":"{\"Sql\":\"CALL reporting.generate_daily_sales_report();\"}"
      }
  ]'

Common Mistakes

Missing IAM permission for the Redshift Data API target. Without EXECUTE and database credentials, EventBridge fails silently. Fix by attaching the AmazonRedshiftDataFullAccess policy to the role and granting database user EXECUTE on the stored procedure.
Appending to the report table instead of recreating it causes duplicates. Fix by DROP TABLE IF EXISTS or TRUNCATE before INSERT/CREATE TABLE AS, or use an UPSERT pattern with a primary key on report_date.

Frequently Asked Questions (FAQs)

Can I automate multiple reports in one schedule?

Yes. Call several stored procedures in a wrapper procedure, then schedule the wrapper so you manage just one EventBridge rule.

Does automation increase Redshift cost?

Compute time is the same as running queries manually. Because procedures often materialize results into narrow tables, downstream queries run faster, lowering overall cost.

How do I monitor failures?

Enable EventBridge rule targets for dead-letter queues or CloudWatch alarms. Inside the procedure, RAISE EXCEPTION messages appear in STL_QUERY and can trigger SNS alerts.