Working with JSON Snowflake Variants in FME

Introduction

Snowflake’s data platform brings together all your structured, semi-structured, and unstructured data in one place. One of the tools Snowflake offers to support semi-structured data, like JSON or XML, is the Variant data type. This article discusses using Snowflake Variants with FME and why you might want to. We’ll focus on working with JSON in Variants, since it’s a common semi-structured data type.

What is a Variant?

A Snowflake Variant lets you store different kinds of semi-structured data in a single Snowflake column. For example, you can store a nested JSON structure related to a snowflake record in a single Variant column. Snowflake has tools to load and query data within a Variant, and FME extends that tool set, allowing you to read, transform, and write any Variant data. 

Variant Advantages

Variants are great for storing hierarchical data structures where schema flexibility is needed. Examples include API responses, vendor data streams, and BIM (Building Information Modeling) element properties. Enforcing schemas involves extra work and can be particularly challenging for complex, varied schemas that you don’t control. You may also want to enforce the schema down to a certain level. For example, a customers table in Snowflake will be mostly static, with columns such as ID, Name, and Street Address. However, there may be hundreds of customer preferences which different based on the type of customer. A Variant could store only the preferences that apply to each customer, as shown below.

The step-by-step instructions below walk through an example of storing BIM element properties from an IFC file in Snowflake. An IFC (Industry Foundation Classes) file is an open format for storing and exchanging rich building data. It contains elements for all of the different parts that make up a built structure, like walls, windows, and doors. All elements will be stored in a Snowflake table, and each element’s unique properties will be managed in a Variant. 

This could be part of a larger use case to aggregate BIM data across multiple buildings and projects into Snowflake for portfolio-level asset management, reporting, and auditing. For simplicity, we’ll work with a single building. 

Requirements

1. FME Form 2026.1 or later. Note: FME has supported Snowflake variants since 2020. This article was written in 2026.1 and uses transformers, such as the JSONUpdater, that weren’t introduced until later.
2. A working Snowflake database connection. See Connecting to Snowflake.
3. Access to your Snowflake account to view results.
4. The sample IFC file ARK_NordicLCA_Housing_Timber_As-built_Archicad.ifc, which is available in the Files section of this article.

Step-by-Step Instructions

Part 1: Dynamically Create JSON and Write it as a Snowflake Variant

In this part, we’ll look at how to dynamically create JSON and write it to a Snowflake Variant using FME.

To keep things simple and focus on Variants, we’ll store the building’s floors and elements in a single table. If we were building a full solution, we would likely keep separate tables for projects, sites, and buildings that are also included in the IFC file. 

Before beginning, download the IFC file ARK_NordicLCA_Housing_Timber_As-built_Archicad.ifc from the Files section of this article, which contains data for a small apartment building.

1. Start a New Workspace

Open FME Workbench and create a new workspace.

2. Add an IFCReader to the Canvas

Drag the ARK_NordicLCA_Housing_Timber_As-built_Archicad.ifc file onto the canvas. 

On the Add Reader dialog, choose Single Mergerd Feature Type

Click OK to finish adding the reader. Run the workspace with Data Caching enabled. 

There are over 5000 features representing both physical (e.g., walls, doors, windows) and non-physical (e.g., project, owner) parts of the model.

We’ll use Data Preview in this article to review results as we go. To avoid lag, disable Graphics View, as shown below.

3. Filter Sample Records

For this example, we'll write only the physical elements contained in the building to Snowflake.

Add a TestFilter to the canvas and connect it to the IFCReader. Double-click the TestFilter to open the parameters. Create the following two tests and output the results to the ports indicated:

1. Click If, then Edit... 

ifc_element_type = BuildingStorey
Output Port: Storey

Click OK.

2. Click Else If, then Edit... 

Ifc_contained_in_structure = Attribute Has a Value AND ifc_context_identifier = Body
Output Port: Contained in Structure

Click OK.

4. Thin the Storey Attributes

IFC files have a massive amount of attributes. Add an AttributeRenamer and connect it to the TestFilter’s Storey Output port. Open the parameters. Rename the following:

• Ifc_global_id: floor_id
• Ifc_name: floor_name

Click Keep Renamed Attributed Only to remove the other attributes. Click OK. 

Run the workspace. There are 8 floors in the building that will later be joined to each building element.

5. Pivot Properties Into Features

Let’s get the properties for each building element. Each element type has its own property set (Pset), which makes them good candidates to store in a Snowflake Variant. 

All of the property sets are currently stored as attributes. We need to pivot the attributes to features so we can work with them in FME.

Add an AttributeExploder to the canvas and connect it to the TestFilter’s Contained in Structure Output port. In the AttributeExploder parameters, set Keep Attributes to Yes. Click OK.

Connect a Tester to the Output port of the AttributeExploder and open the parameters. Test for _attr_name Begins With Pset_. Set the Comparison Mode to Case Insensitive. Click OK

Run the workspace up to the Tester and inspect the Passed features in Data Preview. Given there are so many attributes, you may need to scroll far to the right in Data Preview until you see the attributes for the property sets.

6. Create the JSON Fragments

The _attr_name values will become JSON keys. Let’s clean them up a bit first. Add a StringReplacer to the canvas and connect it to the Tester’s Passed port. Open the parameters and add the following two string replacements for _attr_name:

1. Text to Replace: . Replacement Text: _
2. Text to Replace: Pset_ Replacement Text: (leave empty)

Click OK.

Add a JSONTemplater to the canvas and connect it to the StringReplacer’s Output port. Open the parameters. Click [...] to open the editor and then add the template expression below:

{
    fme:get-attribute("_attr_name"): fme:get-attribute("_attr_value")
}

Click OK to close the editor. 

Name the Output Attribute JSONFragment and click OK to close the JSONTemplater.

7. Create the JSON Property Set

All of the JSON fragments can now be merged into a single JSON object for each IFC building element. Add a JSONUpdater to the canvas. Connect the JSONTemplater Output port to the Update port. Connect the TestFilter’s Contained in Structure Output port to the Document port. The FME Workbench canvas should look like this:

Open the JSONUpdater parameters. Set them as follows:

1. Group Processing: Enabled. 
   Group Update Features by ifc_global_id
2. JSON Document: 
   JSON Input: Text or Attribute
   JSON Text: {} (empty object)
3. Updates:
   Update Type: Merge Objects
   JSON Path: json
   Value Type: JSON/XQuery
   Value: JSONFragment
4. Result Attribute: Pset

Click OK. 

Run the workspace to the JSONUpdater and inspect the Document features in Data Preview. Scroll to the right and look for the Pset attribute. Double-click a Pset value to view it. Click the Syntax Highlighting button and choose JSON to pretty-print the results. 

We now have a JSON object created from IFC data to write to Snowflake.

8. Merge Elements with Floors

Let’s add the floor information to each building element. Add a FeatureMerger to the canvas and connect the JSONUpdate’s Document port to the FeatureMerger’s Requestor port. Connect AttributeRenamer’s Output port to the FeatureMerger’s Supplier port. The connections will cross on the canvas - to fix this, right-click the Supplier port and choose Move Up

Open the FeatureMerger parameters. Join on Requestor (the building element) ifc_parent_id and Supplier (the floor) floor_id. Leave Comparison Mode as Automatic. 

Click OK.

Run the workspace up to the FeatureMerger. The UnmergedRequestor port shows there are building elements without a floor id. These are elements linked to IFC spaces, which we didn’t include in this example. Inspect the Merged features in Data Preview - the floors have been merged to the building elements.

9. Write to Snowflake

Before writing to Snowflake, let’s clean up the schema. Add an AttributeKeeper to the canvas. In the parameters, click [...] and then search for and select these attributes:

• floor_id
• floor_name
• ifc_element_type
• ifc_global_id
• ifc_name
• ifc_predefined_type
• Pset

Click OK to select the attributes

Click OK again. 

Run the workspace up to the AttributeKeeper and inspect the Output. We now have a much more manageable schema to write. 

Add a Snowflake Writer to the canvas, choose your Snowflake connection, and leave Table Definition as Automatic. Click OK. If you don’t have a Snowflake connection, please see Connecting to Snowflake.

Name the table BuildingElements and leave the other defaults. Click OK.

Connect the Snowflake Writer to the AttributeKeeper’s Output port. Since the writer’s Table Definition is Automatic, the schema is populated. 

Double-click the BuildingElements feature type to edit the parameters. 

1. Click the User Attributes tab
2. Change the Schema Definition to Manual so we can make some changes
3. Change the Pset type to variant. Change the other types to varchar (no length) to avoid any future write issues. 
4. If you’d like, rearrange the order of attributes using the up and down arrows. In the screenshot below, the floor_id and floor_name attributes are moved to the top. 
5. Disable the Geometry Definition.

Click OK. 

The workspace should look like this:

We’re now ready to write the data to Snowflake. Run the workspace. Check the Translation Log to make sure there were no errors and features were successfully written.

10. Review Results in Snowflake

Log into Snowflake and query the BuildingElements table:  

Select * from "BuildingElements"

Click on a value in the Pset column to view the JSON.

JSON has been created at run time using BIM data in the IFC format and written to a Snowflake Variant! The Variant can now be queried in Snowflake or FME, as shown in the next part.

Part 2: Query the Snowflake Variant

Let’s see how FME can query the Pset Snowflake Variant created in Part 1. 

1. Start a New Workspace

Open FME Workbench and create a new workspace.

2. Add a Snowflake Reader to the Canvas

Choose the same Snowflake connection used in Part 1. Click Parameters.

In the reader parameters, select the BuildingElements table created in Part 1. Click OK.

Click OK again to add the reader to the canvas. Run the workspace with Data Caching enabled. 

All records in the BuildingElements table are read.

3. Add a Where Clause to Filter by Variant Keys

The feature type WHERE Clause is one place you can query a variant. Let’s query walls with a fire rating of EI 90, meaning the wall will prevent the passage of flames for at least 90 minutes in the event of a fire. 

Double-click the BuildingElements feature type to edit the parameters. Click [...] next to WHERE Clause to open the editor.

The syntax we’ll use for querying a Snowflake Variant is “column_name”:”key_name”::datatype. The datatype is optional, but recommended to avoid the query failing silently (returning no results) due to a type mismatch. Enter the statement below:

"ifc_element_type" = 'Wall' and
"Pset":"WallCommon_FireRating"::varchar = 'EI 90'

Click OK.

Run the workspace again. This time, only records matching the where clause are returned. Inspect the reader’s output and double-click a Pset value in Data Preview to view it.

4. Extract Variant

Variant data can be extracted for further analysis or transformation within FME. Add a JSONFlattener to the canvas and connect it to the Snowflake Reader. Open the parameters and choose the Pset attribute as the JSON Document. For the Attributes to Expose, enter all JSON keys from the Pset and click OK.

Click OK to close the JSONFlattener. Run the workspace and inspect the JSONFlattener Output in Data Preview. The Pset JSON has been flattened into attributes.

5. Query Snowflake Variant Using SQL Transformers

FME’s SQL transformers also let you query Snowflake data, including variants. For example, the SQL statement below gets the count of interior doors per building floor. The IsExternal property is part of the DoorCommon property set variant.

SELECT BE."floor_id", BE."floor_name", COUNT(BE."ifc_global_id") AS "interior_door_count" from "BuildingElements" BE
WHERE BE."ifc_element_type" = 'Door' 
    AND BE."Pset":"DoorCommon_IsExternal"::boolean = 'false'
GROUP BY BE."floor_id", BE."floor_name"
ORDER BY BE."floor_name"

To run this query in FME, add a SQLExecutor to the canvas. In the parameters, set:

1. Format: Snowflake
2. Connection: Your Snowflake database connection
3. SQL Statement: The statement above
4. Attributes to Export: Click [...] and then click Populate from Query. Click OK.

Click OK. Run the SQLExector and inspect the results in Data Preview.

To learn more about querying semi-structured data, see Snowflake’s Documentation. Snowflake’s syntax can be used in the FME’s where clause. 

Part 3: Modify the Snowflake Variant

What if a JSON value within a Snowflake Variant needs to be updated? Let’s look at how to do this with FME by updating manufacturer information for the building’s pipe segments.

1. Start a New Workspace

Open FME Workbench and create a new workspace.

2. Add a Snowflake Reader to the Canvas

Choose the same Snowflake connection used in Part 1 and Part 2. Click Parameters.

In the reader parameters, select the BuildingElements table created in Part 1. Click OK.

In the WHERE Clause, add:

"ifc_element_type" = 'PipeSegment'

Click OK.

Click OK again to add the reader to the canvas. Run the workspace with Data Caching on. Only the building’s pipe segments should be returned.

Inspect the output and view a Pset value. The ManufacturerTypeInformation_Manufacturer property is missing. 

3. Add Values to Update

In a real-world solution, manufacturer values would likely be read from a database or other system, but for simplicity, we will manually add them based on each pipe segment’s type. 

Add an AttributeValueMapper to the canvas and connect it to the Snowflake Reader. Open the parameters and set them as shown below:

1. Input Attribute: ifc_predefined_type
2. Output Attribute: manufacturer
3. Input Value 1: GUTTER

Output Value1: Lizard Roofing

4. Input Value 2: RIGIDSEGEMENT

Output Value 2: Zipster Fittings

Click OK.

Run the workspace and inspect the AttributeValueMapper’s output. All features now have a manufacturer value that can be added to the associated Pset JSON.

4. Update the Propert Set JSON

Add a JSONUpdater to the canvas. Connect the AttributeValueMapper’s Output to both the Document and Update ports of the JSONUpdater. 

Open the JSONUpdater’s parameters and set them as shown below:

1. Group Processing: Enabled. 
   Group Update Features by: ifc_global_id
2. JSON Document: 
   JSON Input: Text or Attribute
   JSON Text: Pset
3. Updates:
   Update Type: Replace Object Value
   JSON Path: json
   Object/Array Index: ManufacturerTypeInformation_Manufacturer
   Value Type: Plain Text
   Value: @Value(manufacturer)
4. Result Attribute: Pset

Click OK. 

Run the workspace and inspect the JSONUpdater Output. The Pset JSON has been updated 

5. Remove Manufacturer Attribute

Now that the Pset JSON has been updated, we can remove the manufacturer attribute before writing to Snowflake. 

Add an AttributeRemover to the canvas and connect it to the JSONUpdater’s Document output port. Set Attributes to Remove to manufacturer.

Click OK.

6. Write to Snowflake

Add a Snowflake Writer to the canvas, choose your Snowflake connection, and leave Table Definition as Automatic. Click OK.

Enter the existing table name BuildingElements. Click OK.

Connect the AttributeRemover to the BuildingElements feature type.

Now that the Snowflake Writer is connected to a transformer providing a schema, it can be fully configured. Double-click the BuildingElements feature type to reopen the parameters. Change the Feature Operation to Update. For Table Handling, choose Use Existing. For the Match Columns, which is the update key, click [...] and choose ifc_global_id. 

Click OK. 

The completed workspace looks like this:

We’re now ready to update the pipe segment JSON variants in Snowflake. Run the workspace. Check the Translation Log to make sure there were no errors and features were successfully written.

7. Review Results in Snowflake

Log into Snowflake and query the BuildingElements table:  

SELECT BE."ifc_global_id", BE."ifc_element_type", BE."ifc_predefined_type", BE."Pset":"ManufacturerTypeInformation_Manufacturer"
FROM "BuildingElements" BE
WHERE BE."ifc_element_type" = 'PipeSegment'

This concludes the introduction to working with Snowflake Variants and JSON in FME. 

Additional Resources

Getting Started with Snowflake

IFC: Enriching and adding Property Sets

Data Attribution 

The IFC files in this tutorial are provided under the Nordic Sustainable Construction Programme. It is found in the Architectural models zip file: Download here. You can unzip the file and find it in the IFC folder as:  ARK_NordicLCA_Housing_Timber_As-built_Archicad.ifc. It is made available under the Creative Commons license (CC BY-SA 4.0).