Loading Semi-Structured Data (JSON, Parquet, Avro)
π Loading Semi-Structured Data from External Stagesβ
Letβs assume you already have:
- β
An external stage in Snowflake (e.g.,
@my_external_stage) - β
A Storage Integration (
my_s3_integration) configured securely
Snowflake supports JSON, Parquet, Avro, and other semi-structured formats using the VARIANT data type.
πΉ 1οΈβ£ Create a Table with VARIANT Columnβ
CREATE OR REPLACE TABLE semi_structured_data (
raw VARIANT
);
β VARIANT can store any semi-structured format
β Enables schema-on-read
πΉ 2οΈβ£ Create File Formats (Expandable by Type)β
Snowflake needs to know how to parse files from the external stage.
π JSON File Format
πΉ What is JSON File Format?β
JSON file format is used to load semi-structured, hierarchical, text-based data into Snowflake.
Snowflake stores JSON data in a VARIANT column and applies schema-on-read.
πΉ Basic Syntaxβ
CREATE OR REPLACE FILE FORMAT my_json_format
TYPE = 'JSON'
<options>;
πΉ Core JSON Options (Most Used)β
1οΈβ£ STRIP_OUTER_ARRAYβ
STRIP_OUTER_ARRAY = TRUE | FALSE
What it does
Removes the outer [ ] when JSON contains an array of objects.
Example input
[
{ "id": 1 },
{ "id": 2 }
]
β Each object becomes a separate row.
Best practice
- β
Use
TRUEfor most JSON files.
2οΈβ£ IGNORE_UTF8_ERRORSβ
IGNORE_UTF8_ERRORS = TRUE | FALSE
What it does Skips invalid UTF-8 characters instead of failing the load.
Use when
- Data comes from external systems
- Logs or third-party APIs
3οΈβ£ COMPRESSIONβ
COMPRESSION = AUTO | NONE | GZIP | BZIP2 | ZSTD | DEFLATE
Common values
AUTOβ Snowflake detects automatically (recommended)GZIPβ.json.gz
πΉ Error Handling Optionsβ
4οΈβ£ ALLOW_DUPLICATEβ
ALLOW_DUPLICATE = TRUE | FALSE
What it does Allows duplicate keys in JSON objects.
Example
{ "id": 1, "id": 2 }
β οΈ Last value wins.
5οΈβ£ REPLACE_INVALID_CHARACTERSβ
REPLACE_INVALID_CHARACTERS = TRUE | FALSE
What it does
Replaces invalid characters with οΏ½ instead of failing.
πΉ Metadata & Parsing Controlβ
6οΈβ£ ENABLE_OCTALβ
ENABLE_OCTAL = TRUE | FALSE
Allows parsing of octal numbers (non-standard JSON).
7οΈβ£ DATE_FORMATβ
DATE_FORMAT = 'AUTO' | 'YYYY-MM-DD'
Used when casting JSON strings to DATE.
8οΈβ£ TIME_FORMATβ
TIME_FORMAT = 'AUTO'
9οΈβ£ TIMESTAMP_FORMATβ
TIMESTAMP_FORMAT = 'AUTO'
Used when converting JSON timestamps.
πΉ Rare / Advanced Optionsβ
π BINARY_FORMATβ
BINARY_FORMAT = HEX | BASE64 | UTF8
Used when JSON contains binary data.
πΉ Where JSON File Format Is Expected / Recommendedβ
β Recommended Scenariosβ
Use JSON when:
- Data comes from REST APIs
- Application or microservice outputs
- Logs, events, clickstream data
- Schema is unknown or evolving
- Initial landing / raw ingestion layer
π Typical pipelines:
- API β S3 β Snowflake
- App logs β Snowflake
- Event streaming dumps
β Not Recommended Scenariosβ
Avoid JSON when:
- Data volume is very large
- Schema is fixed and relational
- Heavy analytics or BI reporting is required
Prefer instead:
- Parquet β analytics & reporting
- Avro β streaming & schema evolution
β οΈ Limitations, Disadvantages & Overheads (VERY IMPORTANT)β
π« 1οΈβ£ Performance Limitationsβ
Why
- JSON is text-based
- Needs parsing at query time
Impact
- Slower than Parquet
- Higher CPU usage
π JSON is great for ingestion, not analytics.
π« 2οΈβ£ Storage Overheadβ
Why
- Repeated field names
- No columnar compression
Impact
- Larger file size
- More I/O
π JSON can be 3β10Γ larger than Parquet.
π« 3οΈβ£ Schema-on-Read Overheadβ
What happens
- Schema interpreted during queries
Impact
- Higher compute usage
- Slower complex queries
Best practice:
Convert JSON to structured tables early.
π« 4οΈβ£ Limited Type Enforcementβ
- Data lands as
VARIANT - Type errors appear at query time
raw:salary::NUMBER
π« 5οΈβ£ Error Handling Complexityβ
Issues:
- Invalid characters
- Broken JSON
- Duplicate keys
Requires:
- Extra file format options
- Debugging effort
π« 6οΈβ£ Cost Overheadβ
Why
- Parsing cost
- Longer queries
Result
- Higher Snowflake credit usage
π« 7οΈβ£ Not BI-Friendlyβ
- BI tools prefer structured columns
- Requires flattening and transformations
β Recommended Production JSON File Formatβ
CREATE OR REPLACE FILE FORMAT json_prod_format
TYPE = 'JSON'
STRIP_OUTER_ARRAY = TRUE
IGNORE_UTF8_ERRORS = TRUE
COMPRESSION = AUTO;
β Safe β Flexible β Production-ready
π Summary Tableβ
| Aspect | JSON |
|---|---|
| Flexibility | β High |
| Schema evolution | β Easy |
| Performance | β Slower |
| Storage efficiency | β Low |
| Analytics readiness | β Poor |
| Best use | Ingestion |
π― Key Takeawayβ
Use JSON for ingestion and flexibility β not for analytics.
Golden Rule
- JSON β Landing layer
- Transform β Structured tables / Parquet
- Analyze β BI & reporting
π Think of JSON as a temporary format, not a final one.
π¦ Parquet File Format
Columnar, compressed format optimized for analytics.
CREATE OR REPLACE FILE FORMAT my_parquet_format
TYPE = 'PARQUET';
When to use
- Analytical workloads
- Large datasets
Benefits
- Fast queries
- Lower storage cost
𧬠Avro File Format
Binary, row-based format with schema evolution support.
CREATE OR REPLACE FILE FORMAT my_avro_format
TYPE = 'AVRO';
When to use
- Streaming pipelines (Kafka, CDC)
- Evolving schemas
πΉ 3οΈβ£ List Files in the External Stageβ
LIST @my_external_stage;
β Confirms file availability before loading
πΉ 4οΈβ£ Load Data (Expandable by Type)β
π Load JSON Files
Loading JSON from Amazon S3 into Snowflake
π Sample JSON File (Source Data)β
File name: employees.json
S3 location: s3://beems-rohit-vicky/employees.json
[
{
"id": 1,
"name": "Alice",
"department": "Sales",
"salary": 75000,
"location": {
"city": "Mumbai",
"state": "MH"
},
"is_active": true
},
{
"id": 2,
"name": "Bob",
"department": "Marketing",
"salary": 68000,
"location": {
"city": "Chennai",
"state": "TN"
},
"is_active": false
},
{
"id": 3,
"name": "Charlie",
"department": "Engineering",
"salary": 92000,
"location": {
"city": "Bangalore",
"state": "KA"
},
"is_active": true
},
{
"id": 4,
"name": "Diana",
"department": "HR",
"salary": 62000,
"location": {
"city": "Delhi",
"state": "DL"
},
"is_active": true
},
{
"id": 5,
"name": "Ethan",
"department": "Finance",
"salary": 80000,
"location": {
"city": "Hyderabad",
"state": "TS"
},
"is_active": false
}
]
π§ JSON Structure Overviewβ
| Field | Type | Description |
|---|---|---|
id | Number | Employee ID |
name | String | Employee name |
department | String | Department |
salary | Number | Salary |
location | Object | Nested city & state |
is_active | Boolean | Active employee flag |
β οΈ This is a JSON array, so we must use STRIP_OUTER_ARRAY = TRUE.
π§± Step 1: Create Storage Integration (Secure AWS Access)β
CREATE OR REPLACE STORAGE INTEGRATION s3_demo_sf_secured_load_rohit_vicky
TYPE = EXTERNAL_STAGE
STORAGE_PROVIDER = S3
ENABLED = TRUE
STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::248864054957:role/Vicky_Rohit_role'
STORAGE_ALLOWED_LOCATIONS = ('s3://beems-rohit-vicky/');
π Verify
DESC STORAGE INTEGRATION s3_demo_sf_secured_load_rohit_vicky;
β Uses IAM Role (no access keys) β Follows Snowflake security best practices
π Step 2: Create JSON File Formatβ
CREATE OR REPLACE FILE FORMAT json_file_format
TYPE = 'JSON'
STRIP_OUTER_ARRAY = TRUE;
Why this mattersβ
- Converts each JSON object into one Snowflake row
- Prevents entire file loading as a single record
π¦ Step 3: Create External Stage (S3 β Snowflake)β
CREATE OR REPLACE STAGE stg_s3_demo_sf_secured_load_rohit_vicky_json
URL = 's3://beems-rohit-vicky/'
STORAGE_INTEGRATION = s3_demo_sf_secured_load_rohit_vicky
FILE_FORMAT = json_file_format;
π List files
LIST @stg_s3_demo_sf_secured_load_rohit_vicky_json;
π§ Step 4: Create Target Table (VARIANT Column)β
CREATE OR REPLACE TABLE employees_json (
emp_raw VARIANT
);
β VARIANT stores the entire JSON object
β Enables schema-on-read
π₯ Step 5: Load JSON Dataβ
COPY INTO employees_json
FROM @stg_s3_demo_sf_secured_load_rohit_vicky_json;
β JSON parsed automatically β 5 objects β 5 rows loaded
π Step 6: View Raw Loaded Dataβ
SELECT * FROM employees_json;
Each row contains one full JSON object.
π§ Step 7: Query JSON Fields (Dot Notation)β
SELECT
emp_raw:id::INT AS id,
emp_raw:name::STRING AS name,
emp_raw:department::STRING AS department,
emp_raw:salary::NUMBER AS salary,
emp_raw:is_active::BOOLEAN AS is_active
FROM employees_json;
β Simple β SQL-friendly β No schema needed at load time
π Step 8: Working with Nested Data (FLATTEN)β
Use FLATTEN only when arrays are present.
SELECT
lf.value:location.city::STRING AS city
FROM employees_json,
LATERAL FLATTEN(INPUT => emp_raw) lf;
β Expands arrays β Enables deep JSON analytics
βΉοΈ For nested objects (not arrays),
FLATTENis not required
π Step 9: Monitor Load Historyβ
SELECT *
FROM INFORMATION_SCHEMA.LOAD_HISTORY
WHERE TABLE_NAME = 'EMPLOYEES_JSON'
AND LAST_LOAD_TIME >= DATEADD('hour', -24, CURRENT_TIMESTAMP());
π Current timestamp:
SELECT CURRENT_TIMESTAMP();
π§ͺ Step 10: Validate Files Without Loading (Optional)β
COPY INTO staging_orders
FROM @my_stage/orders.csv
FILE_FORMAT = (TYPE=CSV FIELD_DELIMITER=',' SKIP_HEADER=1)
VALIDATION_MODE = 'RETURN_ERRORS';
β Validates data β Loads nothing β Ideal for testing pipelines
π End-to-End Flow Summaryβ
Amazon S3
β
Storage Integration (IAM Role)
β
External Stage
β
JSON File Format
β
COPY INTO
β
VARIANT Table
β
Query (Dot notation / FLATTEN)
β οΈ Limitations & Overheads (Important for Juniors)β
Disadvantages of JSONβ
- Text parsing is slower than Parquet
- Large files increase load time
- No enforced schema β data quality checks needed
Best Practicesβ
- Use
STRIP_OUTER_ARRAY = TRUE - Batch small files
- Convert to Parquet for analytics
- Validate loads using
LOAD_HISTORY
π― Key Takeawaysβ
- Storage Integration β secure IAM-based access
- External Stage β pointer to S3
- VARIANT β schema-on-read for JSON
- COPY INTO β safest & fastest load
- Dot notation β easy querying
- FLATTEN β array expansion
- LOAD_HISTORY β auditing & monitoring
π¦ Load Parquet Files
COPY INTO semi_structured_data
FROM @my_external_stage/employees.parquet
FILE_FORMAT = my_parquet_format;
β Columnar + compressed β Best performance for analytics
𧬠Load Avro Files
COPY INTO semi_structured_data
FROM @my_external_stage/employees.avro
FILE_FORMAT = my_avro_format;
β Supports schema evolution
πΉ 5οΈβ£ Query Semi-Structured Dataβ
π Query VARIANT Data
SELECT
raw:id::INT AS id,
raw:name::STRING AS name,
raw:department::STRING AS department
FROM semi_structured_data;
Nested object example:
SELECT raw:employee.address.city::STRING AS city
FROM semi_structured_data;
πΉ 6οΈβ£ Flatten Nested Arraysβ
π Use FLATTEN for Arrays
SELECT
value:phone::STRING AS phone_number
FROM semi_structured_data,
LATERAL FLATTEN(input => raw:contacts);
β Expands arrays into rows β Works for JSON, Parquet, Avro
π Side-by-Side Comparisonβ
| Feature | JSON | Parquet | Avro |
|---|---|---|---|
| File Type | Text | Columnar | Binary |
| Compression | β | β | β |
| Schema Evolution | β | β | β |
| Analytics Performance | β οΈ | π | β |
| Streaming Friendly | β οΈ | β | π |
β‘ Key Takeawaysβ
- Snowflake natively supports JSON, Parquet, Avro
- Same
VARIANTtable can store all formats - Querying syntax is identical
- Choose format based on workload:
- β JSON β Flexibility π Parquet β Analytics 𧬠Avro β Streaming & schema evolution
π― Final Recommendationβ
Use VARIANT + External Stages + Storage Integrations for secure, scalable semi-structured ingestion.
This approach is production-ready, cloud-native, and enterprise compliant.
π Step 9: Load Monitoringβ
SELECT *
FROM INFORMATION_SCHEMA.LOAD_HISTORY
WHERE TABLE_NAME = 'EMPLOYEES_JSON'
AND LAST_LOAD_TIME >= DATEADD('hour', -24, CURRENT_TIMESTAMP());
β οΈ Limitations & Overheads (Important for Juniors)β
β JSON Disadvantagesβ
- Text parsing is slower than Parquet
- Large JSON files increase load time
- No enforced schema β data quality checks needed
β Best Practicesβ
- Use
STRIP_OUTER_ARRAY = TRUE - Batch small files
- Convert to Parquet for analytics
- Validate data after load
π― Key Takeawaysβ
This is the safest & simplest way to load JSON into Snowflake
β Secure IAM-based access (no access keys)
β Schema-on-read using VARIANT
β Easy querying with dot notation
β Ideal for APIs, logs, events, raw ingestion
If you want next:
- π Parquet vs JSON comparison
- π Flatten array example
- π Production checklist
- π Interview Q&A for juniors
Just say the word π