Modeling Nested Data in Snowflake

  • 19 October 2017
  • 1 reply

  • Anonymous
  • 0 replies

Hi @brett_g,

I’m trying to follow your discourse. but I’m only getting nulls for my all my column names.

The JSON file I’m trying to parse is below. This is just one “cell” I have a column full of these json snippets - one for each order id.

{ “active”: false, “android”: [], “checkpoints”: [ { “checkpoint_time”: “2019-05-08T18:06:46”, “city”: null, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-10T02:23:06+00:00”, “location”: “UNITED STATES”, “message”: “ELECTRONIC NOTIFICATION RECEIVED: YOUR ORDER HAS BEEN PROCESSED AND TRACKING WILL BE UPDATED SOON”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InfoReceived_001”, “subtag_message”: “Info Received”, “tag”: “InfoReceived”, “zip”: null }, { “checkpoint_time”: “2019-05-08T18:21:07”, “city”: null, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-10T02:23:06+00:00”, “location”: “UNITED STATES”, “message”: “EN ROUTE TO DHL ECOMMERCE”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_001”, “subtag_message”: “In Transit”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-09T20:26:36”, “city”: “Avenel”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-10T20:36:19+00:00”, “location”: “Avenel, NJ, US”, “message”: “ARRIVAL AT DHL ECOMMERCE DISTRIBUTION CENTER”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_003”, “subtag_message”: “Arrival scan”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-10T13:21:44”, “city”: “Avenel”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-10T20:36:19+00:00”, “location”: “Avenel, NJ, US”, “message”: “PROCESSED”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_001”, “subtag_message”: “In Transit”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-10T14:00:17”, “city”: “Avenel”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-10T20:36:19+00:00”, “location”: “Avenel, NJ, US”, “message”: “DEPARTURE ORIGIN DHL ECOMMERCE FACILITY”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_007”, “subtag_message”: “Departure Scan”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-12T18:52:04”, “city”: “Orlando”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-13T03:09:49+00:00”, “location”: “Orlando, FL, US”, “message”: “ARRIVAL DESTINATION DHL ECOMMERCE FACILITY”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_003”, “subtag_message”: “Arrival scan”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-13T04:32:37”, “city”: “Orlando”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-13T09:09:50+00:00”, “location”: “Orlando, FL, US”, “message”: “TENDERED TO DELIVERY SERVICE PROVIDER”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_002”, “subtag_message”: “Acceptance scan”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-14T04:51:00”, “city”: “Miami Beach”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-14T09:22:58+00:00”, “location”: “Miami Beach, FL, US”, “message”: “ARRIVAL AT POST OFFICE”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_003”, “subtag_message”: “Arrival scan”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-14T03:36:00”, “city”: “Miami Beach”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-14T15:24:16+00:00”, “location”: “Miami Beach, FL, US”, “message”: “ARRIVED USPS SORT FACILITY”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_002”, “subtag_message”: “Acceptance scan”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-14T08:45:00”, “city”: “Miami Beach”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-14T15:24:16+00:00”, “location”: “Miami Beach, FL, US”, “message”: “SORTING COMPLETE”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “InTransit_001”, “subtag_message”: “In Transit”, “tag”: “InTransit”, “zip”: null }, { “checkpoint_time”: “2019-05-14T08:55:00”, “city”: “Miami Beach”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-14T15:24:16+00:00”, “location”: “Miami Beach, FL, US”, “message”: “OUT FOR DELIVERY”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “OutForDelivery_001”, “subtag_message”: “Out for Delivery”, “tag”: “OutForDelivery”, “zip”: null }, { “checkpoint_time”: “2019-05-14T13:44:00”, “city”: “Miami Beach”, “coordinates”: [], “country_iso3”: “USA”, “country_name”: “United States”, “created_at”: “2019-05-14T18:54:56+00:00”, “location”: “Miami Beach, FL, US”, “message”: “DELIVERED [IN/AT MAILBOX]”, “slug”: “dhl-global-mail”, “state”: null, “subtag”: “Delivered_001”, “subtag_message”: “Delivered”, “tag”: “Delivered”, “zip”: null } ], “courier_destination_country_iso3”: “USA”, “created_at”: “2019-05-10T02:19:28+00:00”, “custom_fields”: { “item_names”: “Compact plastic applicator tampons - Custom / Box of 18 x 1” }, “customer_name”: “Stephanie Denault”, “delivery_time”: 6, “delivery_type”: null, “destination_country_iso3”: “USA”, “emails”: [ “” ], “expected_delivery”: “2019-05-16”, “id”: “5cd4dfb04dfc673c10bea053”, “ios”: [], “language”: null, “last_mile_tracking_supported”: null, “last_updated_at”: “2019-05-14T18:54:56+00:00”, “note”: null, “order_id”: “1001004826670”, “order_id_path”: null, “order_promised_delivery_date”: null, “origin_country_iso3”: “USA”, “path”: “deprecated”, “pickup_location”: null, “pickup_note”: null, “return_to_sender”: false, “shipment_delivery_date”: “2019-05-14T13:44:00”, “shipment_package_count”: 1, “shipment_pickup_date”: “2019-05-08T18:06:46”, “shipment_type”: “DHL SM Parcel Ground”, “shipment_weight”: 0.408, “shipment_weight_unit”: “lb”, “signed_by”: null, “slug”: “dhl-global-mail”, “smses”: [], “source”: “shopify-private”, “subscribed_emails”: [], “subscribed_smses”: [], “subtag”: “Delivered_001”, “subtag_message”: “Delivered”, “tag”: “Delivered”, “title”: “L-2547090”, “tracked_count”: 22, “tracking_account_number”: null, “tracking_destination_country”: “USA”, “tracking_key”: null, “tracking_number”: “9274899999898086172176”, “tracking_origin_country”: null, “tracking_postal_code”: “33141”, “tracking_ship_date”: “20190510”, “tracking_state”: null, “unique_token”: “deprecated”, “updated_at”: “2019-05-14T18:54:56+00:00” }

The explore in the model file :

explore: aftership_data {

join: msg {

sql: ,lateral flatten(input => msg) msg ;;

relationship: one_to_many


join: msg_checkpoints {

sql: ,lateral flatten(input => msg.value:checkpoints) msg_checkpoints ;;

relationship: one_to_many

required_joins: [msg]



Please find the views below:

view: aftership_data {

sql_table_name: AFTERSHIP.DATA ;;

dimension: __sdc_primary_key {

type: string

sql: ${TABLE}."__SDC_PRIMARY_KEY" ;;


view: msg {

dimension: order_id {

type: string

sql: ${TABLE}.value:order_id;;



view: msg_checkpoints {

dimension: checkpoints__message {

type: string

sql: ${TABLE}.value:checkpoints:message::string ;;



I’m only getting nulls for Order ID and Checkpoints Message. Can you tell me what I’m missing on?

Here’s the sql for the explore:


aftership_data."__SDC_PRIMARY_KEY" AS “aftership_data.__sdc_primary_key”,

msg.value:order_id AS “msg.order_id”,

msg_checkpoints.value:checkpoints:message::string AS “msg_checkpoints.checkpoints__message”


,lateral flatten(input => msg) msg

,lateral flatten(input => msg.value:checkpoints) msg_checkpoints

GROUP BY 1,2,3



1 reply

Hey, this is a pretty amazing walk through, thanks for that.

Though I ran into an issue and I thought it might be useful to others.

In your explore definition you say

join: hits_customdimensions {

sql: ,lateral flatten(input => hits.value:customDimensions) hits_customdimensions ;;

required_joins: [hits]

relationship: one_to_many


Though it came out that the comma created the following problem:

Any table joined to the base table (say the table hits in your example) after joining hits_customdimensions would trigger the following error:

Lateral view cannot be on the left side of JOIN

Solution for that is to use the notation CROSS JOIN instead of a comma