Data Loading and Unloading

Data engineers often struggle with Snowflake's ingestion workflows: files sit in cloud storage, but there's no direct import button. Without understanding stages, the PUT command, and COPY INTO, teams waste time on manual uploads, face duplicate loads, and miss cost-saving opportunities.

Inefficient data loading drives up storage and compute costs. Loading into permanent tables instead of temporary ones means paying indefinitely for staging data. Running COPY INTO manually instead of automating with Snowpipe delays reports and keeps warehouses running longer than necessary.

This masterclass follows Vinay, a data engineer, and Rahul, a senior architect, through real-world scenarios at GlobalMart. You’ll work through interactive examples, hands-on SQL scripts, and multiple-choice questions.

What You'll Learn:

Stages & Data Ingestion

• Differentiate between internal stages (user, table, named) and external stages (S3, ADLS, GCS) and choose the right one for your data source
• Use the PUT command to upload files to internal stages and the GET command to download from them
• Configure file formats (CSV, JSON, Parquet, ORC, Avro, XML) at stage or command level

Loading, Transformations & Error Handling

• Load data from stages using COPY INTO with column reordering, omission, type casting, and truncation
• Apply COPY options like VALIDATION_MODE, ON_ERROR, PURGE, and FORCE to handle errors and validate files
• Understand 64-day load history tracking to prevent duplicate ingestion

Data Unloading & Export Control

• Unload data from tables to stages in CSV, JSON, or Parquet formats
• Control file splitting (default 16MB) with MAX_FILE_SIZE, SINGLE, and OVERWRITE options

Table Types & Cost Optimization

• Choose between permanent (90-day Time Travel, 7-day Fail-Safe), temporary (session-only), transient (cost-efficient), and external tables (query without ingestion)
• Reduce costs by using temporary tables for staging and transient tables for intermediate workflows

Automated Ingestion with Snowpipe

• Set up Snowpipe for serverless, event-driven ingestion with AUTO_INGEST=TRUE (cloud messaging) or FALSE (REST API)
• Manage pipes with SUSPEND/RESUME and understand 14-day load history retention and stale pipe risks

By the end, you'll understand Snowflake's complete data loading and unloading workflow—so you can automate ingestion with Snowpipe, choose cost-effective table types, and configure reliable data pipelines. Test your knowledge throughout with scenario-based questions.