1. Manual ways to parse JSON in Snowflake (the traditional route)

Before we bring in automation or agents, it helps to master the core primitives Snowflake gives you for JSON. Think of these as the building blocks your team – and your AI agent – will reuse.

‍

1.1 Store JSON as VARIANT and parse with PARSE_JSON

1. Land your raw JSON as text in a VARCHAR column (often via COPY INTO or a staged file).
2. Create a table with a VARIANT column:CREATE OR REPLACE TABLE events_raw (
 id        STRING,
 payload   VARIANT
);

3. When loading, call PARSE_JSON on the raw string:INSERT INTO events_raw (id, payload)
SELECT
 $1 AS id,
 PARSE_JSON($2) AS payload
FROM @my_stage/raw_events;

4. Now payload is a JSON document in VARIANT form. You can query it with payload:customer.id or payload['event_type'].

Official docs: PARSE_JSON.

‍

1.2 Safely handle bad JSON with TRY_PARSE_JSON

Bad rows from webhooks or malformed logs can crash a whole load.

1. Swap PARSE_JSON for TRY_PARSE_JSON:INSERT INTO events_raw (id, payload)
SELECT
 $1,
 TRY_PARSE_JSON($2)
FROM @my_stage/raw_events;

2. Any invalid JSON returns NULL instead of an error.
3. Add a downstream quality check:SELECT COUNT(*) AS bad_rows
FROM events_raw
WHERE payload IS NULL;


Docs: TRY_PARSE_JSON.

‍

1.3 Projecting fields with dot and bracket notation

Once JSON is in VARIANT:

1. Use dot notation for standard keys:SELECT
 payload:customer.id::STRING       AS customer_id,
 payload:event_type::STRING        AS event_type,
 payload:properties.revenue::NUMERIC(10,2) AS revenue
FROM events_raw;

2. Use bracket notation if keys contain spaces or non-standard characters:SELECT
 payload['campaign name']::STRING AS campaign_name
FROM events_raw;


Snowflake JSON navigation: Semi-structured data.

‍

1.4 Flattening arrays with LATERAL FLATTEN

Marketing and product events often store arrays: line_items, impressions, touchpoints.

1. Assume payload:line_items is an array.
2. Use LATERAL FLATTEN:SELECT
 e.id,
 li.value:sku::STRING    AS sku,
 li.value:price::NUMBER  AS price
FROM events_raw e,
LATERAL FLATTEN(input => e.payload:line_items) li;

3. Each array element becomes a row.

Docs: FLATTEN and LATERAL JOIN.

‍

1.5 Handling nulls and duplicate keys

Snowflake treats empty strings and JSON null differently.

1. PARSE_JSON('') returns SQL NULL.
2. PARSE_JSON('null') returns a JSON null inside VARIANT.
3. For rare cases with duplicate keys, use the d parameter:SELECT PARSE_JSON('{"a":1,"a":2}', 'd');
Snowflake keeps the last value.

‍

2. No-code and low-code methods with automation tools

If you run an agency or revenue team, you might not want your marketers writing SQL. You can still harness Snowflake’s JSON power via no-code tooling.

‍

2.1 ELT tools that auto-generate PARSE_JSON logic

Modern ELT platforms (Fivetran, Airbyte, Stitch and others) automatically ingest JSON APIs and write PARSE_JSON under the hood.

Typical setup:

1. Connect your source (Meta Ads, Google Ads, HubSpot, Shopify) and Snowflake.
2. Choose tables or endpoints; the tool infers schemas from sample JSON.
3. Behind the scenes it loads raw JSON and builds views that:
   • Parse VARCHAR with PARSE_JSON.
   • Use FLATTEN to explode arrays.
   • Cast to proper Snowflake types.

‍

Pros:

• Little to no SQL.
• Schemas auto-update as APIs evolve.

‍

Cons:

• Less control over naming and modeling.
• Harder to encode campaign-specific business rules.

‍

2.2 Visual data modeling tools

Tools like dbt Cloud’s GUI or other visual modelers can sit on top of Snowflake and let non-engineers tweak JSON models.

Pattern:

1. Start from a raw VARIANT table.
2. Add a new model and visually pick fields via a tree of JSON keys.
3. The tool emits SQL using payload:field and FLATTEN, then compiles to Snowflake.

This lets a marketing ops manager extend models (add a new property from an ad platform payload) without hand-writing JSON navigation.

‍

2.3 Workflow tools triggering Snowflake tasks

Business owners often want: “When new JSON events arrive, refresh my dashboards automatically.”

You can:

1. Set up a Snowflake TASK to run parsing SQL on a schedule.
2. Trigger that task via an external orchestrator or webhook.
3. Orchestrate end-to-end: load JSON → parse with PARSE_JSON → aggregate KPIs → email a report.

Snowflake tasks: TASK docs.

‍

3. Scaling JSON parsing with AI agents

Manual SQL and no-code tools get you going, but they still rely on humans to maintain patterns. As your business adds more platforms and campaigns, JSON parsing becomes a repetitive, error-prone chore.

Simular’s AI computer agents are designed to automate exactly this kind of digital workflow: opening Snowflake worksheets, editing SQL, running queries, and integrating results with the rest of your stack.

‍

3.1 Agent-driven template generation for new JSON sources

Imagine you onboard a new ad network that delivers logs as JSON files.

An AI agent running on Simular Pro can:

1. Open your browser, log into the Snowflake UI, and navigate to Worksheets.
2. Paste in a proven parsing template that:
   • Uses TRY_PARSE_JSON to load raw events into VARIANT.
   • Applies LATERAL FLATTEN for key arrays (e.g., ad_groups, line_items).
   • Casts and renames fields into a clean reporting schema.
3. Replace table and key names by reading sample JSON files from your storage.
4. Execute the SQL, verify row counts and null rates, and save the worksheet.

‍

Pros:

• Eliminates copy-paste and boilerplate SQL.
• Ensures consistent field naming across many sources.

‍

Cons:

• Requires careful initial instructions and guardrails.

Learn more about Simular’s agents: Simular Pro.

‍

3.2 Continuous maintenance as schemas evolve

APIs and event schemas change quietly: a new field is added, data types shift, arrays become nested. Humans usually discover this only when dashboards break.

An AI agent can:

1. Periodically sample fresh JSON rows from your VARIANT tables.
2. Compare keys and structures to a stored baseline.
3. If it detects new fields, generate:
   • An updated parsing view that selects the new keys.
   • A short changelog in your team’s documentation or Slack.
4. Run test queries to confirm that PARSE_JSON and FLATTEN still succeed (no type errors).

This keeps your Snowflake environment aligned with real-world payloads without constant engineer attention.

‍

3.3 End-to-end orchestration for non-technical teams

For agencies and revenue teams, the ideal future state is simple: JSON in, dashboards out – with no one worrying about commas in SQL.

A Simular AI agent can orchestrate the full workflow across desktop, browser, and cloud:

1. Detect new JSON files or API loads.
2. Log into Snowflake, run the appropriate PARSE_JSON/TRY_PARSE_JSON + FLATTEN models.
3. Export aggregated tables to your BI tool or Google Sheets.
4. Post a short summary in Slack: “Yesterday’s campaigns processed; 3K events parsed; 5 malformed rows quarantined.”

‍

Pros:

• Frees analysts and marketers to focus on messaging and offers.
• Production-grade reliability for long multi-step workflows.

‍

Cons:

• Requires initial investment in designing the workflow the agent will follow.

‍

To see how these agents behave in real computer environments, explore the demos and docs at Simular Pro and learn about the research approach at Simular About.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5

Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

1. Item 1
2. Item 2
3. Item 3

Unordered list

• Item A
• Item B
• Item C

Text link

Bold text

Emphasis

^Superscript

_Subscript