Skip to main content

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 TRUE for 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.


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

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

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​

AspectJSON
Flexibilityβœ… High
Schema evolutionβœ… Easy
Performance❌ Slower
Storage efficiency❌ Low
Analytics readiness❌ Poor
Best useIngestion

🎯 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​

FieldTypeDescription
idNumberEmployee ID
nameStringEmployee name
departmentStringDepartment
salaryNumberSalary
locationObjectNested city & state
is_activeBooleanActive 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), FLATTEN is 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​

FeatureJSONParquetAvro
File TypeTextColumnarBinary
CompressionβŒβœ…βœ…
Schema EvolutionβŒβŒβœ…
Analytics Performanceβš οΈπŸš€βœ…
Streaming Friendlyβš οΈβŒπŸš€

⚑ Key Takeaways​

  • Snowflake natively supports JSON, Parquet, Avro
  • Same VARIANT table 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 😊