I am currently preparing for the SnowPro Core certification which is the standard technical Snowflake certifications. More specialized certifications can be gained after this one is achieved. More details can be found on the Snowflake website.
The whole series: Part 1 - Warehouses Part 2 - Storage Part 3 - Account and security Part 4 - Data movement Part 5 - Overview and architecture Part 6 - Performance tuning Part 7 - Semi-structured data
So, let’s jump right into the seventh topic, which is semi-structured data:
- Snowflake supports JSON, AVRO, ORC, Parquet, XML
- Types:
- Variant: standard SQL types, array, objects
- Object: key-value pairs, value is VARIANT
- Array: value is VARIANT
- Access value with k:v or k[‘v’] syntax
- Cast with double colon (::) syntax
- Flatten, lateral join often used for flattening a json
Loading and unloading:
- Very similar to structured data
- Create file format, use it in COPY INTO
- Unloading supports JSON and parquet
