Authored by – Pavan Kumar Undamattla and Panga Karthik
Welcome to the documentation for the Kipi Data Utils Dynamic JSON Flattener app for Snowflake! This guide will walk you through the installation process, required privileges, and key features of the app.
What is the app about?
Kipi Data Utils Dynamic JSON Flattener Application parses JSON data from the user’s raw table and creates a table with user-selected columns. It stores a SELECT statement in the master table, facilitating flattening and scheduling as needed, and allows for easy rollback to earlier versions.
Why do we need the app?
Parsing JSON data typically demands data analysis and manual effort to create a structured table. This process becomes even more challenging with constant schema drift. However, our application is adept at handling such scenarios with ease. It efficiently manages schema drift and creates structured tables.
With our application, data engineers can flatten JSON data and create tables much more easily and quickly, reducing their effort and time consumption.
Installation and Prerequisites
Before you can start using the app, ensure the following prerequisites are met:
1. Grant Read Access: Grant read access on the source database, schema, and table for the app to access the JSON data.
2. Grant Write Access: Grant write access on the target schema and table to allow the app to create the flattened table.
3. Prerequisite: The raw table should include a “LOAD_DATE” column with a Timestamp data type to facilitate the identification and management of delta records. This column helps track when each record was loaded into the table, aiding in the detection of changes or updates over time.
Required Privileges
Certain privileges need to be granted to enable the smooth operation of the app. These privileges include:
- Sharing the event table with the provider for logging and tracing purposes.
- Granting select privileges on relevant tables from the security section.
Introduction
The Kipi Data Utils Dynamic JSON Flattener app simplifies the process of flattening nested JSON data in your Snowflake database. Whether you’re dealing with complex nested structures or managing schema changes, our intuitive interface streamlines the entire flattening process.
Key Features
1. JSON Structure Analysis: This helps us understand JSON data structure, its data types, and the level of nested JSON data.
2. Schema Adaptability: The app dynamically adjusts to schema changes within JSON data, ensuring seamless flattening processes regardless of evolving data structures. It also maintains data integrity and reliability throughout schema modifications.
3. Customized Data Extraction: Users have the flexibility to extract specific fields from the JSON data during the flattening process, catering to diverse analysis or reporting requirements.
4. Version Control and Rollback Capability: The app facilitates version maintenance and maintains a master table with versioned select query statements for the tables. This allows users to attach tasks to these statements for daily data processing and enables easy rollback to older versions if necessary, ensuring data consistency and reliability throughout the flattening process.
Step 1: Grant Access
1. Grant Read Access: Grant the app read access to the source database, schema, and table so that it can access the JSON data, as shown in the readme.
2. Grant Write Access: Grant write access on the target schema and table to allow the app to create the flattened table as shown in readme.
Step 2: Select Source Table
Under the security section, select the database, schema, and source table.
Step 3: Initiate Flattening Process
After initiating the flattening process, we can analyze the JSON structure, including the nested levels and data types. Additionally, we have the capability to edit the data types as needed.
Step 4: Choose Relevant Fields as Table
Here, we can choose relevant or all fields for the table and preview it before creating it.
Step 5: Choose Target Schema for Table Creation
Here, we can select the source database and target schema and provide the target table name to create the flattened table.
Step 6: VersionControl and RollBack
The application creates a master table to store the select query, which aids in flattening the captured JSON data. It captures every change and maintains versions, facilitating easy rollback when needed.
Step 7: Daily Data Processing
For incremental loads, we can attach an ‘insert into’ and Task to the select query in the master table, which runs daily or as needed to process the data as shown in readme.
Conclusion
With the Kipi Data Utils Dynamic JSON Flattener app, you can efficiently flatten nested JSON data, handle schema changes, and preview the results in a readable format. Empower your data processing capabilities and make working with JSON data in Snowflake a seamless experience.
Thank you for choosing our Kipi Data Utils Dynamic JSON Flattener application for Snowflake!
