Skip to main content

๐Ÿ“ฆ Batch Loading with Task


๐Ÿš€ What is Batch Loading in Snowflake?โ€‹

Batch Loading refers to loading structured or semi-structured files in bulk from external storage (like S3, Azure Blob, or GCS) into Snowflake tables at scheduled intervals.

๐ŸŽฏ Typical Use Casesโ€‹

  • ๐ŸŒ™ Nightly job loads
  • โฑ๏ธ Hourly log updates
  • ๐Ÿ” Periodic ETL ingestion
  • ๐Ÿ›๏ธ Initial data migration from legacy systems

๐Ÿ”ง Introduction to Snowflake Tasksโ€‹

๐Ÿง  Think of Snowflake Tasks as an internal scheduler built into Snowflake.

Tasks automate SQL workflows such as:

  • COPY INTO
  • MERGE
  • INSERT / UPDATE
  • Stored procedures

๐Ÿ‘‰ No external cron, no manual execution.


1๏ธโƒฃ What is a Snowflake Task?โ€‹

A Snowflake Task is a database object that automatically runs SQL statements on a schedule or after another task completes.

โŒ Without Tasksโ€‹

  • Someone must run COPY INTO manually
  • Risk of forgetting daily/hourly loads
  • No consistency or automation

โœ… With Tasksโ€‹

  • Fully automated pipelines
  • Reliable execution
  • Supports dependency-based workflows (DAGs)

๐Ÿ“Œ Simple definition for juniors:

A Task is a scheduled SQL job inside Snowflake.


2๏ธโƒฃ Key Features of Tasksโ€‹

FeatureDescription
โฒ๏ธ SchedulingRun SQL using CRON
๐Ÿ”— Task ChainsParent โ†’ Child dependencies
โš ๏ธ Error HandlingAuto-suspend on failure
๐Ÿ“ก Event DrivenTriggered using Streams
โš™๏ธ Resource ControlUses assigned warehouse

3๏ธโƒฃ Common Use Casesโ€‹

  • ๐Ÿ”„ Automated ETL / ELT pipelines
  • โœ… Data validation workflows
  • ๐Ÿงน Cleanup & archival jobs
  • ๐Ÿ“ฉ Post-load notifications

4๏ธโƒฃ Task Components (Think Like Building Blocks ๐Ÿงฑ)โ€‹

ComponentMeaning
๐Ÿ“œ Task BodySQL logic to run
๐Ÿ•’ ScheduleWHEN it runs (CRON)
๐Ÿ—๏ธ WarehouseCompute power
๐ŸŒณ DependenciesAFTER another task

5๏ธโƒฃ Creating a Basic Taskโ€‹

๐Ÿง  First define WHAT the task should do

Example: Run a stored procedure every day at 6 AM (Los Angeles time)

CREATE OR REPLACE TASK daily_sales_summary
WAREHOUSE = my_wh
SCHEDULE = 'USING CRON 0 6 * * * America/Los_Angeles'
AS
CALL process_daily_sales();

๐Ÿ“Œ At this stage:

  • โœ… Task is created
  • โŒ Task is NOT running

Start it explicitly:

ALTER TASK daily_sales_summary RESUME;

๐Ÿค” Step 2: Understand the CRON Schedule (How Often It Runs)โ€‹

โฐ Click to Expand: CRON Scheduling in Snowflake

โฐ Snowflake Tasks use a 5-field CRON expression to decide when and how often a task should execute.

Note: Snowflake does not support 6-field CRON schedules like 0 */5 * * * * UTC.


๐Ÿงพ Snowflake CRON Fieldsโ€‹

PositionFieldValue ExampleMeaning
1Minute0Minute of the hour
2Hour6Hour of the day
3Day-of-month*Day of month
4Month*Month of year
5Day-of-week*Day of week

Snowflake syntax:

USING CRON <expression> <timezone>

๐Ÿ“Œ Timezone is mandatory in Snowflake. Common values: UTC, America/Los_Angeles, Europe/London.


๐Ÿ“Œ Example: Run Every Hourโ€‹

USING CRON 0 * * * * UTC

๐Ÿง  Plain Englishโ€‹

  • 0 โ†’ run at minute 0
  • * โ†’ every hour

โžก๏ธ This runs once every hour, at the top of the hour.


๐Ÿ” Common CRON Patternsโ€‹

RequirementCRON ExpressionMeaning
Every minute* * * * *Runs every minute
Every 5 minutes*/5 * * * *Runs every 5 minutes
Every hour0 * * * *Runs at minute 0 each hour
Every day at midnight0 0 * * *Runs daily at 00:00
Every day at 6 AM LA0 6 * * * America/Los_AngelesRuns daily at 6 AM LA time

๐Ÿ”„ Alternative Schedule Format: INTERVALโ€‹

For simple repeating jobs, Snowflake also supports INTERVAL schedules.

๐Ÿ“‹ Interval syntaxโ€‹

SCHEDULE = 'INTERVAL 5 MINUTES'
SCHEDULE = 'INTERVAL 1 HOUR'
SCHEDULE = 'INTERVAL 1 DAY'

๐Ÿ“Š CRON vs INTERVALโ€‹

FeatureCRONINTERVAL
SyntaxUSING CRON 0 6 * * * UTCINTERVAL 5 MINUTES
Use caseSpecific time windowsSimple repeated intervals
TimezoneRequiredNot applicable
Best forBusiness schedulesFrequent periodic jobs

๐ŸŽฏ When to use eachโ€‹

Use CRON when:

  • You need a specific clock time (e.g. 6 AM)
  • You need day-of-week or month logic
  • Timezone alignment matters

Use INTERVAL when:

  • You need a regular repeat interval
  • You want easier syntax
  • You do not care about a specific time-of-day

๐Ÿ“ Schedule examplesโ€‹

Every 5 minutes with INTERVAL

CREATE OR REPLACE TASK monitor_logs
WAREHOUSE = compute_wh
SCHEDULE = 'INTERVAL 5 MINUTES'
AS
COPY INTO logs_table FROM @s3_stage;

ALTER TASK monitor_logs RESUME;

Every day at 6 AM LA with CRON

CREATE OR REPLACE TASK daily_report
WAREHOUSE = compute_wh
SCHEDULE = 'USING CRON 0 6 * * * America/Los_Angeles'
AS
CALL generate_daily_report();

ALTER TASK daily_report RESUME;

โš ๏ธ Important Things to Think Aboutโ€‹

๐Ÿง  Before choosing a CRON frequency:

  • ๐Ÿ’ฐ Every run consumes warehouse credits
  • ๐Ÿ” Avoid duplicate inserts (make logic idempotent)
  • ๐Ÿš€ For near-real-time ingestion, prefer Streams + Tasks
  • โณ Hourly or 5-minute schedules are usually sufficient and cost-effective

โœ… Rule of Thumbโ€‹

Batch data? โ†’ Use CRON Incremental changes? โ†’ Use Streams + Tasks Too frequent? โ†’ Higher cost, little benefit ๐Ÿ’ธ


๐ŸŒ Creating a Task Hierarchy (DAG)โ€‹

๐Ÿง  Tasks can be chained together to create pipelines! Here's an example using both INTERVAL and CRON:

Option 1: Using INTERVAL (Simple & Fast)

CREATE OR REPLACE TASK ingest_data
SCHEDULE = 'INTERVAL 5 MINUTES'
WAREHOUSE = my_wh
AS
-- Ingest logic (runs every 5 minutes)
COPY INTO raw_table FROM @s3_stage;

CREATE OR REPLACE TASK transform_data
WAREHOUSE = my_wh
AFTER ingest_data
AS
-- Transform logic (runs after ingest completes)
MERGE INTO transformed_table ...;

CREATE OR REPLACE TASK report_data
WAREHOUSE = my_wh
AFTER transform_data
AS
-- Report logic (runs after transform completes)
CALL generate_report();

Option 2: Using CRON (Scheduled at Specific Times)

CREATE OR REPLACE TASK ingest_data
SCHEDULE = 'USING CRON 0 * * * * UTC'
WAREHOUSE = my_wh
AS
-- Ingest logic (runs every hour)
COPY INTO raw_table FROM @s3_stage;

CREATE OR REPLACE TASK transform_data
WAREHOUSE = my_wh
AFTER ingest_data
AS
-- Transform logic (runs after ingest completes)
MERGE INTO transformed_table ...;

CREATE OR REPLACE TASK report_data
WAREHOUSE = my_wh
AFTER transform_data
AS
-- Report logic (runs after transform completes)
CALL generate_report();

๐Ÿง  Think of DAG like a pipeline chain: Ingest โ†’ Transform โ†’ Report

๐Ÿ“Œ Key Point: Tasks AFTER a parent task will trigger immediately after the parent completes, regardless of whether the parent used INTERVAL or CRON!


๐Ÿ› ๏ธ Full Pipeline: Automated S3 Data Ingestion

1๏ธโƒฃ Create Storage Integrationโ€‹

CREATE OR REPLACE STORAGE INTEGRATION s3_demo_sf_secured_load_si
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::248864054957:role/s3-demo-sf-secured-load-role'
STORAGE_ALLOWED_LOCATIONS = ('s3://s3-demo-sf-secured-load/');

2๏ธโƒฃ Create File Format & Stageโ€‹

CREATE OR REPLACE FILE FORMAT csv_file_format
TYPE = 'CSV'
STRIP_OUTER_ARRAY = TRUE;
CREATE OR REPLACE STAGE stg_s3_demo_sf_secured_load_si
URL = 's3://s3-demo-sf-secured-load/'
STORAGE_INTEGRATION = s3_demo_sf_secured_load_si
FILE_FORMAT = csv_file_format;

3๏ธโƒฃ Create Staging Tableโ€‹

CREATE OR REPLACE TABLE employee_india (
employee_id STRING,
name STRING,
department STRING,
job_title STRING,
location STRING,
status STRING,
hire_date DATE,
last_updated STRING
);

4๏ธโƒฃ Optional Manual Loadโ€‹

COPY INTO employee_india
FROM @aws_s3_si_bucket_stage
PATTERN = 'employee_.*\\.csv'
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1)
ON_ERROR = 'SKIP_FILE';

5๏ธโƒฃ Automate with Tasks (Batch Loading)โ€‹

๐Ÿ” Load Staging Table (Every Minute)โ€‹

CREATE OR REPLACE TASK load_s3_stg_employee
WAREHOUSE = compute_wh
SCHEDULE = 'USING CRON * * * * * UTC'
AS
COPY INTO employee_india
FROM @aws_s3_si_bucket_stage
PATTERN = 'employee_.*\\.csv'
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 1)
ON_ERROR = 'SKIP_FILE';
ALTER TASK load_s3_stg_employee RESUME;

๐Ÿง  Snowflake automatically skips already loaded files


๐Ÿ”„ Merge into Master Table (Dependent Task)โ€‹

CREATE OR REPLACE TABLE employee_master (
employee_id STRING,
name STRING,
department STRING,
job_title STRING,
location STRING,
status STRING,
hire_date DATE,
last_updated STRING
);
CREATE OR REPLACE TASK merge_employee_task
WAREHOUSE = compute_wh
AFTER load_s3_stg_employee
AS
MERGE INTO employee_master tgt
USING (
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY last_updated DESC) rn
FROM stg_employee
)
WHERE rn = 1
) src
ON tgt.employee_id = src.employee_id
WHEN MATCHED THEN UPDATE SET
name = src.name,
department = src.department,
job_title = src.job_title,
location = src.location,
status = src.status,
hire_date = src.hire_date,
last_updated = src.last_updated
WHEN NOT MATCHED THEN INSERT (
employee_id, name, department, job_title, location, status, hire_date, last_updated
) VALUES (
src.employee_id, src.name, src.department, src.job_title, src.location, src.status, src.hire_date, src.last_updated
);
ALTER TASK merge_employee_task RESUME;

๐Ÿงญ Monitoring & Debuggingโ€‹

1๏ธโƒฃ Check Task Status & Execution Historyโ€‹

-- View all tasks matching a pattern
SHOW TASKS LIKE '%employee%';
-- See the last 10 executions of a specific task (Real-Time โœ…)
SELECT *
FROM TABLE(
INFORMATION_SCHEMA.TASK_HISTORY(
TASK_NAME => 'load_s3_stg_employee',
RESULT_LIMIT => 10
)
);

๐Ÿ‘‰ For Students/Business Users: This shows you when the task ran, if it succeeded, and error messages if it failed. Check this first! โฑ๏ธ


2๏ธโƒฃ Check What Data Was Copied (Last Hour)โ€‹

-- See COPY operations that ran in the last 1 hour (Real-Time โœ…)
SELECT *
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'EMPLOYEE_INDIA',
START_TIME => DATEADD('HOUR', -1, CURRENT_TIMESTAMP())
));

What this shows:

  • ๐Ÿ“Š How many rows were copied
  • ๐Ÿ“ Which file was loaded
  • โฐ When the copy happened
  • โœ… Status (SUCCESS/FAILED)
  • ๐Ÿ“ˆ Data volume transferred

๐Ÿ‘‰ For Business Users: Use this to verify "Did my data load successfully today?" ๐Ÿ“‹


3๏ธโƒฃ Validate Data Quality After Loadโ€‹

-- Validate the last load job for data integrity
SELECT *
FROM TABLE(VALIDATE(
TABLE_NAME => 'EMPLOYEE_INDIA',
JOB_ID => '_last'
));

What this does:

  • โœ”๏ธ Checks for duplicate rows
  • โœ”๏ธ Detects corrupted data
  • โœ”๏ธ Validates constraints
  • โœ”๏ธ Reports any data quality issues

๐Ÿ‘‰ For Data Engineers: Always run this after batch loads to ensure data quality! ๐ŸŽฏ


4๏ธโƒฃ Historical Load Data (โš ๏ธ Has 45-min Delay)โ€‹

-- See all historical loads for a table
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.LOAD_HISTORY
WHERE TABLE_NAME = 'EMPLOYEE_INDIA'
ORDER BY LAST_LOAD_TIME DESC;

โš ๏ธ Note: This query shows historical data but can be delayed by up to 45 minutes. Use the real-time queries above for immediate results!โ€‹

โš ๏ธ Expected Issues / Known Issuesโ€‹

โณ Delay in Load History Visibilityโ€‹

Current load information in Snowflake's ACCOUNT_USAGE schema may take up to 45 minutes to be reflected.

Why does this happen?

  • Snowflake aggregates usage data across all accounts
  • Data is updated periodically (not real-time)
  • Designed for historical reporting, not real-time monitoring

Solution: Use Real-Time Monitoring Views Instead

ViewLatencyBest For
INFORMATION_SCHEMA.TASK_HISTORYReal-time โœ…Task execution status
INFORMATION_SCHEMA.COPY_HISTORYReal-time โœ…What data was loaded
VALIDATE()Real-time โœ…Data quality checks
ACCOUNT_USAGE.LOAD_HISTORY45 mins โš ๏ธHistorical trends & billing

Quick Reference - Check Immediately:

-- โœ… REAL-TIME: Did my task run?
SELECT * FROM TABLE(INFORMATION_SCHEMA.TASK_HISTORY(
TASK_NAME => 'load_s3_stg_employee',
RESULT_LIMIT => 5
));

-- โœ… REAL-TIME: How many rows were loaded?
SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
TABLE_NAME => 'EMPLOYEE_INDIA',
START_TIME => DATEADD('HOUR', -1, CURRENT_TIMESTAMP())
));

-- โœ… REAL-TIME: Is the data valid?
SELECT * FROM TABLE(VALIDATE(
TABLE_NAME => 'EMPLOYEE_INDIA',
JOB_ID => '_last'
));

๐Ÿ‘‰ For Troubleshooting: Always use the queries above first. Only use ACCOUNT_USAGE for historical analysis.


๐Ÿ’ก Best Practicesโ€‹

AreaPractice
๐Ÿ” SecurityUse Storage Integration
๐Ÿงช TestingTest COPY before Task
๐Ÿ’ฐ CostAuto-suspend warehouses
๐Ÿ“Š MonitoringTASK_HISTORY

๐Ÿง  Final Key Takeawaysโ€‹

ConceptMeaning
TaskAutomated SQL job
CRONScheduling logic
COPY INTOData loading
StageCloud storage pointer
Staging TableValidation layer
  • โœ… Batch loading + Tasks = Reliable pipelines
  • โœ… CRON controls when
  • โœ… SQL controls what