How to Automate Reports in Snowflake

Why automate reports in Snowflake?

Automation guarantees fresh metrics without copy-paste work, reduces human error, and frees engineering hours. By combining CREATE TASK with cron-like schedules, Snowflake executes your SQL at precise intervals, logs history, and retries on failure.

What is the syntax for CREATE TASK?

CREATE [ OR REPLACE ] TASK task_name
WAREHOUSE = warehouse_name
SCHEDULE = 'USING CRON cron_expr timezone' | 'interval'
COMMENT = 'optional text'
AS
statement;

You can chain tasks with AFTER, enable with ALTER TASK … RESUME, and inspect runs via SHOW TASKS or SHOW TASK HISTORY.

How do I schedule a daily sales report?

Create a summary table first:

CREATE OR REPLACE TABLE DailySales (
report_date DATE,
total_sales NUMBER(12,2)
);

Then create the task:

CREATE OR REPLACE TASK daily_sales_report
WAREHOUSE = analytics_wh
SCHEDULE = 'USING CRON 0 6 * * * America/Los_Angeles'
AS
INSERT INTO DailySales
SELECT CURRENT_DATE, SUM(total_amount)
FROM Orders
WHERE order_date = CURRENT_DATE;

Resume the task so it starts tomorrow:

ALTER TASK daily_sales_report RESUME;

How can I debug failed tasks?

Query SNOWFLAKE.ACCOUNT_USAGE.TASK_HISTORY for the last run’s error_message. Check warehouse credit quota, SQL compilation errors, and privileges on referenced objects. Use EXECUTE TASK daily_sales_report for an ad-hoc run.

What are best practices for automated reports?

Use dedicated warehouses sized for report workloads. Filter data incrementally with order_date >= DATEADD(day,-1,CURRENT_DATE) to minimize compute. Store results in partitioned tables and add quality checks inside tasks or downstream alerts.