How to Parse JSON and XML in Snowflake Using Coalesce

Why parse JSON and XML in Snowflake?

Working with semi-structured data formats like JSON and XML is common in modern data pipelines. But parsing these formats manually writing Snowflake SQL can be complex and error-prone—especially when dealing with nested arrays, variable data types, and evolving schemas. 

Coalesce streamlines this process by allowing you to visually map, flatten, and transform semi-structured data in just a few clicks. This step-by-step guide and video tutorial explores how to parse JSON in Snowflake using Coalesce, including how to flatten, map, and transform complex data structures without writing a line of SQL.

1: Prepare your Snowflake environment 

Before diving into Coalesce, start by loading sample JSON and XML data into Snowflake. In the video tutorial, we used two separate worksheets to load these files into dedicated tables.

Tip: Make sure you’ve connected your Snowflake account and have access to the appropriate database and schema where your sample data lives.

2: Set up storage locations in Coalesce

To begin parsing JSON or XML in Coalesce, open your development workspace and click the gear icon in the lower-left corner to access build settings.

1. Create two new storage locations and name them JSON and XML.
2. Go to workspace settings (pencil icon) and map these storage locations to the correct database and schema.
   • For JSON, map to weather schema.
   • For XML, map to xml schema.

This ensures Coalesce pulls in the right data from Snowflake during your transformation workflow.

3: Add your raw JSON and XML source nodes

Navigate to the Nodes section (left-hand sidebar) and:

1. Click the plus icon next to the search bar.
2. Add your raw JSON and XML sources to the workspace.
3. View your sources in the graph as individual nodes.

Click Fetch Data in the preview pane to confirm that Coalesce has access to the raw records.

How do I parse JSON in Snowflake using Coalesce?

To parse the JSON:

1. Right-click the JSON node and choose Create Stage.
2. In the Stage node, right-click the column containing the raw JSON string.
3. From the dropdown, select Derive Mappings > From JSON.

Coalesce will automatically:

• Identify all primitive fields (strings, numbers, booleans, nulls).
• Generate mapped columns with appropriate data types.
• Apply transforms needed to parse and convert values.
• Flatten JSON arrays using Snowflake’s LATERAL FLATTEN() via the Join tab.

Click Create to generate the stage node and Run to execute the transformation. The preview pane will display your parsed and flattened data.

How do I parse XML in Snowflake using Coalesce?

Parsing XML in Coalesce follows the same streamlined process as JSON:

1. Create a stage node on top of your XML source.
2. Right-click the relevant XML column and choose Derive Mappings > From XML.
3. Coalesce parses the XML structure, creates mapped columns, and applies transforms.

This process automatically extracts nested fields, handles hierarchical structures, and prepares the data for downstream transformations.

View transformation details and SQL output

After running each stage node, you can inspect the underlying SQL statements used for parsing:

• DDL/DML Tabs show the exact SQL Coalesce generates for Snowflake.
• Data Preview confirms that parsing and flattening were successful.

This is especially helpful for validating transformations and ensuring your parsed data meets business requirements.

Why use Coalesce to parse semi-structured data?

Parsing JSON and XML in raw SQL can involve multiple SELECT, FLATTEN, LATERAL, and TRY_CAST operations. Coalesce eliminates this complexity by:

• Automatically generating Snowflake SQL behind the scenes.
• Allowing you to visually inspect and adjust mappings.
• Making data pipelines more maintainable and collaborative.

Whether you’re working with log files, event payloads, or third-party data sources, Coalesce speeds up your ability to turn semi-structured data into analytics-ready models. To learn more, check out our interactive product tour, or explore our product documentation on parsing semi-structured data. 

Try it yourself: Start parsing JSON and XML in minutes

Ready to simplify JSON and XML parsing in Snowflake? Here are two great ways to get started:

• Try Coalesce for free via Snowflake Partner Connect
• Spin up your environment in minutes and start transforming real data—no credit card required.
• Join a hands-on virtual lab
• Walk through the entire Coalesce platform experience with guided exercises and best practices.