The-Data-Guy: Data Munging

2020-12-22

JSON in Snowflake

Snowflake is an amazing database. It has auto-scaling capabilities, the ability to separate workloads, and automatically recover data using its time-travel capabilities.

However, one of the most amazing things that I like about Snowflake is its ability to directly query JSON data stored in a variant column Query JSON Data

Combine this with some other tools like Kakfa and Dbezium and you have some very rapid prototyping capabilities for doing analysis of your application database.

Dbezium monitors the transaction logs of your application database, and publishes change events to Kafka.

Using the Snowflake Kafka Connector, you can capture these events and store the data in what would otherwise be called staging tables.

The Kafka Connector stores the data as JSON, but Snowflake's ability to query JSON data is simple enough that any SQL developer can extract the pertinent data from the JSON Structure.

An example from their documentation is:

SELECT src:device_type
  FROM raw_source

The src column of the raw_source table is where the JSON is stored. Once you know the JSON structure you want to query the :COLUMNAME is all that is needed to query the data from the variant column where the JSON data is stored.

For more complicated JSON structures you may need to use some of the flattening technique in order to get all of the data from the JSON structure.

With tools like this rapid prototyping is a breeze, the more complicated ETL jobs may not be necessary if the goal is to rapidly create a dimensional data model from your source application, then expose that data model with your favorite reporting tool (like Tableau)

Combining data from multiple sources using this technique is also very straightforward so long as there are common keys in the systems that relates the data together.

This allows architects to rapidly play The Enrichment Game, by combining data from multiple sources, rationalizing the data, enriching one application with the data of another, then expose that data through a reporting tool.

There are a number of problems I had to solve in different ways without having this capability. Using this particular combination of tools to rapidly create reports and respond to business needs drives more conversations about using Data to solve problems.

These conversations are much more valuable rather than the conversations about how complicated a Data Ecosystem is, and the need to either write more Data Munging code in order to get data migrated from one system to another.

2016-01-15

Data-Munging, like ETL only better?

ETL or Munge?

Data Munging

Data munging or data wrangling is loosely the process of manually converting or mapping data from one "raw" form into another format that allows for more convenient consumption of the data with the help of semi-automated tools.(https://en.wikipedia.org/wiki/Data_wrangling

ETL

In computing, Extract, Transform and Load (ETL) refers to a process in database usage and especially in data warehousing that:

Figure 1: Simple schematic for a data warehouse. The ETL process extracts information from the source databases, transforms it and then loads it into the data warehouse. (Photo credit: Wikipedia)

    Extracts data from homogeneous or heterogeneous data sources

    Transforms the data for storing it in the proper format or structure for the purposes of querying and analysis

    Loads it into the final target (database, more specifically, operational data store, data mart, or data warehouse)

Usually all the three phases execute in parallel since the data extraction takes time, so while the data is being pulled another transformation process executes, processing the already received data and prepares the data for loading and as soon as there is some data ready to be loaded into the target, the data loading kicks off without waiting for the completion of the previous phases. (https://en.wikipedia.org/wiki/Extract,_transform,_load

There is also a design pattern where you to what is referred to as ELT, which is Extract, Load and Transform. In this case the Extract process simply copies source data to a "staging"table, then from the staging table you Transform the data and load it into the final target destination. This can be quite a bit more efficient when dealing with large datasets, even if you are moving the data more than one time.

When do you use one versus the other?

The mechanics of each of these processes are incredibly similar. Their use-cases are widely different.

ETL is a process to move data from one repository to another.

Data Munging is taking data from one use-case to another.

English: Plot of a simple Friedman's Multivariate Adaptive Splines Regression (MARS) model with one independent variable. (Photo credit: Wikipedia)

If you are doing a regression model, your data needs to reflect a single observation with as many factors as may be appropriate for your regression model for a given observation. While this may mean "Extracting", and "Transforming" data you are not really "Loading" the data anywhere.
You may store an aggregation of the data, like the coefficients, R-squared, or even P-values from your regression model, this is not truly the same thing as an ETL process.

The value of doing machine learning, data science, or statistical modeling on clean, quality data that is stored in a data warehouse, or data mart and leverages the investment the organization makes in using both of these architectural models to make tommorrows data products the most cost effective for the organization.

Data Munging from source data directly without leveraging a data warehouse infrastructure (if it is available) will require the people doing data munging to do some of the same type of work that the ETL team already does.

If there is no data warehouse, data quality, or data management initiative in place, the team doing data munging for data science will be doing some work that could be useful to the wider organization.

They will be creating Data Products that could be useful to other teams. These intermediate Data Products, that may not be their official production Data product should be shared with others.

Pages