4.4 Bulk Unloading and Semi-Structured Data

SnowPro Core Study Guide Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: SnowPro Core Orientation and Exam Strategy

1.1 Current SnowPro Core Exam Facts 1.2 Eligibility, Registration, and Scheduling 1.3 Blueprint Domains and Weighting 1.4 Editions, Question Style, and Score Report Thinking 1.5 Study Calendar and Hands-On Practice Plan

2 Chapter 2: Snowflake AI Data Cloud Features and Architecture

2.1 Snowflake AI Data Cloud Features and Architecture Overview 2.2 Core Workflows and Decision Points 2.3 Scenario Practice for Snowflake AI Data Cloud Features and Architecture 2.4 Common Traps in Snowflake AI Data Cloud Features and Architecture 2.5 Practice Drills and Readiness Markers

3 Chapter 3: Account Management and Data Governance

3.1 Account Management and Data Governance Overview 3.2 Core Workflows and Decision Points 3.3 Scenario Practice for Account Management and Data Governance 3.4 Common Traps in Account Management and Data Governance 3.5 Practice Drills and Readiness Markers

4 Chapter 4: Data Loading, Unloading, and Connectivity

4.1 Data Loading, Unloading, and Connectivity Overview 4.2 Core Workflows and Decision Points 4.3 Snowpipe, External Tables, and Storage Integrations 4.4 Bulk Unloading and Semi-Structured Data 4.5 Connectors, Drivers, and Snowsight Loading

5 Chapter 5: Performance Optimization, Querying, and Transformation

5.1 Performance Optimization, Querying, and Transformation Overview 5.2 Core Workflows and Decision Points 5.3 Micro-Partitions, Pruning, Clustering, and the Query Profile 5.4 Search Optimization, Materialized Views, and Query Best Practices 5.5 Transformations: ELT, Streams & Tasks, and Semi-Structured Data

6 Chapter 6: Data Collaboration

6.1 Secure Data Sharing Architecture 6.2 Creating Shares, Granting Objects, and Reader Accounts 6.3 Snowflake Marketplace, Listings, and Data Exchange 6.4 Secure Views, Cross-Region Sharing, and Governance 6.5 Choosing the Right Sharing Method and Readiness Drills

7 Chapter 7: Final Review and Test Day

7.1 Timed Practice Strategy 7.2 Last-Week Review Map 7.3 Exam-Day Checklist 7.4 After the Exam and Next Steps

4.4 Bulk Unloading and Semi-Structured Data

Key Takeaways Explain This Topic AI

Unloading uses COPY INTO <location> FROM <table-or-query> into a stage, then GET to download.

SINGLE = FALSE (default) splits output into multiple files; MAX_FILE_SIZE caps each file (default 16 MB, max 5 GB).

Unloaded files are GZIP-compressed by default; supported output formats are CSV, JSON, and Parquet.

Semi-structured data loads into a VARIANT column; FLATTEN explodes arrays/objects into rows.

Dot/bracket notation (col:path) and casts (::type) extract values from VARIANT.

Last updated: October 2026

Bulk Unloading with COPY INTO <location>

Unloading reverses the load: COPY INTO @stage FROM (SELECT ...) writes table or query results to files in an internal or external stage, after which you GET them to a local machine (for internal stages) or read them directly from the cloud bucket (external). The output format is set with FILE_FORMAT ; supported unload formats are CSV (delimited), JSON, and Parquet — note Avro, ORC, and XML can be loaded but not unloaded.

Key unload options:

Option Default Effect
SINGLE FALSE FALSE splits into multiple files (one per thread); TRUE writes one file
MAX_FILE_SIZE 16 MB Max bytes per output file; up to 5 GB
COMPRESSION GZIP Output compression (GZIP/BROTLI/ZSTD/NONE etc.)
OVERWRITE FALSE Replace existing files at the path
HEADER FALSE Write column headers (CSV)
INCLUDE_QUERY_ID FALSE Add query ID to filenames to avoid collisions

With SINGLE = FALSE , Snowflake names files like data_0_0_0.csv.gz , splitting work across warehouse threads for parallel writes. To produce one output file, set SINGLE = TRUE , but be aware a single file is capped (you cannot exceed MAX_FILE_SIZE 's 5 GB hard limit). Because unloaded files default to GZIP , downstream consumers must decompress unless you set COMPRESSION = NONE . Like loading, 100-250 MB compressed files are the efficiency sweet spot for re-loading the data elsewhere.

Semi-Structured Data: VARIANT and FLATTEN

Snowflake stores JSON, Avro, ORC, Parquet, and XML in a VARIANT column (up to 128 MB of uncompressed data per value). You can load an entire document into one VARIANT and query it later without a fixed schema — a major Snowflake selling point.

Accessing VARIANT data

Path notation: src:owner.name (dot) or src['owner']['name'] (bracket) navigates nested objects.

Array indexing: src:items[0] reads the first element.

Casting: append ::type to coerce, e.g. src:price::number or src:dt::date . Without a cast, values return as VARIANT.

FLATTEN

The FLATTEN table function explodes an array or object into one row per element, used with a LATERAL join: SELECT f.value:sku::string AS sku

FROM orders,
LATERAL FLATTEN(input => orders.line_items) f;

FLATTEN returns columns including SEQ , KEY , PATH , INDEX , VALUE , and THIS . Set OUTER => TRUE to keep rows whose array is empty/null (like an outer join), and RECURSIVE => TRUE to expand all nested levels. This pairing — load to VARIANT, then FLATTEN into relational rows — is the canonical semi-structured workflow. You can also let COPY INTO transform during load (select specific columns, apply MATCH_BY_COLUMN_NAME , reorder, or cast) so semi-structured files land in a relational shape directly.

Unload Targets, Partitioning, and Functions
Where unloaded files go

COPY INTO <location> can target any of the four stage types. Unloading to an internal stage (named, table @%t , or user @~ ) keeps files inside Snowflake until you GET them; unloading to an external stage writes straight into your S3/Azure/GCS bucket, where downstream systems can pick them up without a Snowflake client. To organize output, the PARTITION BY clause writes files into subfolders by an expression (for example PARTITION BY (TO_VARCHAR(dt,'YYYY/MM')) ), which is handy for date-partitioned exports consumed by a data lake.

Helpful semi-structured functions

Beyond FLATTEN, several functions appear in loading/unloading questions:

Function Use
PARSE_JSON Convert a string to a VARIANT
TO_JSON / TO_VARIANT Serialize back to a JSON string / cast to VARIANT
OBJECT_CONSTRUCT Build a VARIANT object from key/value pairs (great for unloading to JSON)
ARRAY_AGG Aggregate rows into a VARIANT array
GET / GET_PATH Programmatic path access into a VARIANT
TYPEOF Inspect the data type stored in a VARIANT
A common unload pattern

To export relational rows as JSON, combine OBJECT_CONSTRUCT(*) with a JSON file format: COPY INTO @stage FROM (SELECT OBJECT_CONSTRUCT(*) FROM orders) FILE_FORMAT = (TYPE = JSON) SINGLE = FALSE MAX_FILE_SIZE = 268435456 . The result is GZIP-compressed JSON split into multiple ~256 MB files. Remember that unloaded JSON/CSV defaults to GZIP, so set COMPRESSION = NONE if the consumer cannot decompress, and use OVERWRITE = TRUE cautiously since it replaces existing files at the path.

Traps the exam likes to set

Several unloading and semi-structured details are recurring traps. First, you cannot unload to Avro, ORC, or XML even though you can load them — only CSV, JSON, and Parquet are unload targets. Second, SINGLE = FALSE is the default , so assuming one output file produces a wrong answer; the parallel split is intentional. Third, accessing a VARIANT value without a cast returns VARIANT, not the scalar type, which breaks downstream joins or comparisons — always append ::type .

Fourth, FLATTEN is a table function : it must appear in the FROM clause (typically with LATERAL) to expand an array into rows, so calling it like a scalar function in the SELECT list is a syntax error.

Fifth, a single VARIANT value is limited to 128 MB of uncompressed data , so an enormous JSON document may need to be split before loading. Keeping these five facts straight handles most of the unloading and semi-structured questions on the exam.