Suggest an Idea

[JorgeUriarte]

Customers want to use Snowflake Variant fields to be able to use JSON content using standard snowflake functionality.

We need to support in Compose whether Replicate supports or not.   Even if Replicate doesn’t – having in Compose we can convert large varchars to variants.   Variant then allows a schema on read functionality for the dw.

Additional support (other than loading) would be through json / xml parsing to the mart layer.    So there may be some limitations on what we do today if we can’t apply flatten constructs to the variant.  (But this is likely a bigger ask related to increasing DM functionality).

A lot of DWH customers are now starting to use JSON inside of LOB or Variant fields to extend the functionality and classifications of data without having to change the table structures in their system.

[Tzachi_Nissim]

Hi Jorge,

Can you maybe expand on this a little? Is the following what you had in mind?

1. The source has some columns that include JSON/XML data.
2. Regardless of the data type landed by Replicate, you are asking that Compose have a new data type that will translate to a physical "Variant" data type on Snowflake.
3. The user will be able to load the JSON/XML data into the DWH.
4. The user will be able to use Snowflake functions to extract some attributes from the variant into the data marts.

I believe the above will not allow the use of any "array" data in the JSON/XML. For that we would actually want to break down the variant into a normalized form in the DWH, which is a different approach.

Regards,

Tzachi

[TimGarrod]

Delivered in August 2021 release.