๐ฆ 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 INTOMERGEINSERT / 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 INTOmanually - 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โ
| Feature | Description |
|---|---|
| โฒ๏ธ Scheduling | Run SQL using CRON |
| ๐ Task Chains | Parent โ Child dependencies |
| โ ๏ธ Error Handling | Auto-suspend on failure |
| ๐ก Event Driven | Triggered using Streams |
| โ๏ธ Resource Control | Uses 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 ๐งฑ)โ
| Component | Meaning |
|---|---|
| ๐ Task Body | SQL logic to run |
| ๐ Schedule | WHEN it runs (CRON) |
| ๐๏ธ Warehouse | Compute power |
| ๐ณ Dependencies | AFTER 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โ
| Position | Field | Value Example | Meaning |
|---|---|---|---|
| 1 | Minute | 0 | Minute of the hour |
| 2 | Hour | 6 | Hour of the day |
| 3 | Day-of-month | * | Day of month |
| 4 | Month | * | Month of year |
| 5 | Day-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โ
| Requirement | CRON Expression | Meaning |
|---|---|---|
| Every minute | * * * * * | Runs every minute |
| Every 5 minutes | */5 * * * * | Runs every 5 minutes |
| Every hour | 0 * * * * | Runs at minute 0 each hour |
| Every day at midnight | 0 0 * * * | Runs daily at 00:00 |
| Every day at 6 AM LA | 0 6 * * * America/Los_Angeles | Runs 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โ
| Feature | CRON | INTERVAL |
|---|---|---|
| Syntax | USING CRON 0 6 * * * UTC | INTERVAL 5 MINUTES |
| Use case | Specific time windows | Simple repeated intervals |
| Timezone | Required | Not applicable |
| Best for | Business schedules | Frequent 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
| View | Latency | Best For |
|---|---|---|
INFORMATION_SCHEMA.TASK_HISTORY | Real-time โ | Task execution status |
INFORMATION_SCHEMA.COPY_HISTORY | Real-time โ | What data was loaded |
VALIDATE() | Real-time โ | Data quality checks |
ACCOUNT_USAGE.LOAD_HISTORY | 45 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โ
| Area | Practice |
|---|---|
| ๐ Security | Use Storage Integration |
| ๐งช Testing | Test COPY before Task |
| ๐ฐ Cost | Auto-suspend warehouses |
| ๐ Monitoring | TASK_HISTORY |
๐ง Final Key Takeawaysโ
| Concept | Meaning |
|---|---|
| Task | Automated SQL job |
| CRON | Scheduling logic |
| COPY INTO | Data loading |
| Stage | Cloud storage pointer |
| Staging Table | Validation layer |
- โ Batch loading + Tasks = Reliable pipelines
- โ CRON controls when
- โ SQL controls what