Load data into Snowflake and export data using various methods and file formats (12% of exam).
This domain covers utilizing various methods to load data into Snowflake including bulk loading with COPY INTO, continuous loading with Snowpipe, and data sharing. It also covers managing data unloading processes to export data, handling semi-structured data formats like JSON, Avro, Parquet, and ORC, working with stages (internal and external), and understanding file format options.
5 minutes
5 Questions
Data Loading and Unloading are essential operations in Snowflake for moving data between external sources and Snowflake tables. Understanding these concepts is crucial for the SnowPro Core Certification.
**Data Loading** refers to importing data into Snowflake tables from various sources. Snowflake supports multiple loading methods:
1. **COPY INTO Command**: The primary method for bulk loading data from staged files. It supports formats like CSV, JSON, Avro, Parquet, and ORC.
2. **Snowpipe**: Enables continuous, automated data loading as files arrive in a stage. It uses serverless compute resources and is ideal for streaming scenarios.
3. **Web Interface**: Allows manual file uploads through the Snowsight UI for smaller datasets.
**Stages** are storage locations where data files reside before loading. Snowflake offers three types:
- Internal stages (user, table, or named stages within Snowflake)
- External stages (referencing cloud storage like S3, Azure Blob, or GCS)
**File Formats** define how data is structured. You can create named file format objects or specify format options inline during load operations.
**Data Unloading** involves exporting data from Snowflake tables to external files using the COPY INTO <location> command. This exports query results to staged files in various formats.
Key considerations for both operations include:
- **Transformation during load**: Using SELECT statements to transform data while loading
- **Error handling**: ON_ERROR parameter controls behavior when errors occur
- **Compression**: Snowflake automatically compresses files and supports various compression types
- **Validation**: VALIDATION_MODE allows testing loads before execution
- **Metadata columns**: Special columns like METADATA$FILENAME provide information about source files
**Best Practices**:
- Use appropriately sized files (100-250 MB compressed)
- Leverage parallel processing by splitting large files
- Use COPY command history to track loaded files and prevent duplicates
Mastering these concepts ensures efficient data movement in your Snowflake environment.Data Loading and Unloading are essential operations in Snowflake for moving data between external sources and Snowflake tables. Understanding these concepts is crucial for the SnowPro Core Certification.
**Data Loading** refers to importing data into Snowflake tables from various sources. Snowflak…