Making sense of JSON semi-structured data and transforming to a relational view

This recipe walks you through the process of loading JSON data into a Snowflake table and transforming it into a relational form that can then be further processed.

Getting ready

Since our objective is to demonstrate the processing of data in JSON format, we will not describe the process of creating stages and external data from cloud storage as that has been covered in previous recipes. For simplicity, we have made available a sample JSON file in a public cloud bucket that we will read and process. The JSON sample data is shown in the screenshot that follows. The data consists of two fields called data_set and extract_date and an array of objects called credit_cards. We are going to convert this data into columns and rows so that it can be processed in a relational manner:

Figure 3.14 – Sample JSON data

The JSON sample file can be found at https://github.com/PacktPublishing...