Kipi Data Utils Dynamic JSON Flattener

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, the application efficiently manages schema drift and creates structured tables, reducing data engineer effort and time consumption.

Installation and Prerequisites

Before using the app, ensure:

1. Grant Read Access: Grant read access on the source database, schema, and table for the app to access JSON data.
2. Grant Write Access: Grant write access on the target schema and table to allow table creation.
3. Prerequisite: The raw table should include a "LOAD_DATE" column with Timestamp data type to facilitate identification and management of delta records.

Required Privileges

Certain privileges must be granted for smooth operation:

• Sharing the event table with the provider for logging and tracing purposes
• Granting select privileges on relevant tables from the security section

Key Features

1. JSON Structure Analysis: Helps understand JSON data structure, data types, and nested levels.
2. Schema Adaptability: The app dynamically adjusts to schema changes within JSON data, ensuring seamless flattening regardless of evolving structures while maintaining data integrity.
3. Customized Data Extraction: Users can extract specific fields from JSON data during flattening for diverse analysis or reporting requirements.
4. Version Control and Rollback Capability: The app maintains a master table with versioned select query statements, allowing attachment of tasks for daily data processing and easy rollback to older versions.

Step 1: Grant Access

Grant the app read access to source database, schema, and table to access JSON data. Grant write access on the target schema and table to allow flattened table creation.

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, analyze the JSON structure including nested levels and data types. You can edit data types as needed.

Step 4: Choose Relevant Fields as Table

Select relevant or all fields for the table and preview it before creation.

Step 5: Choose Target Schema for Table Creation

Select the source database and target schema, then provide the target table name to create the flattened table.

Step 6: Version Control and Rollback

The application creates a master table to store the select query, aiding in flattening JSON data. It captures every change and maintains versions, facilitating easy rollback when needed.

Step 7: Daily Data Processing

For incremental loads, attach an 'insert into' and Task to the select query in the master table, which runs daily or as needed to process data.

Conclusion

The Kipi Data Utils Dynamic JSON Flattener app enables efficient flattening of nested JSON data, schema change handling, and readable format previewing. It empowers data processing capabilities and makes working with JSON data in Snowflake seamless.