Blogs / Snowflake / Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

Dec 7, 2019

SHARE

In this Blooming era of data, we need to make sure we gain as many insights at the same time properly utilize the space and storage of data. The data from IOT/Web-services will be streaming in continuously but it might not have a proper structure and it would be hard most of the times to segregate into distinct columns. With dbt and Jinja, we can overcome this scenario where we dynamically transpose the rows into columns so that the data can be analyzed more efficiently. Let’s see how we can do dynamic transpose in snowflake for IoT.

Let’s look at this IOT/Web service data:

Here the ‘reportId’ attribute is populated in the first element but failed to turn up in the second element. Similarly, we can have dynamic fields popping in one element which might be unique to it or present in others.

When we have similar data pulled into Snowflake, we can dynamically transpose it with dbt with the help of integrated Jinja and then use it for our analysis.

dbt is a transformation tool executed using a command-line interface once the data is loaded into the data warehouse. Instead of the traditional way of ETL, using dbt we approach with ELT (i.e.) after the data is extracted and loaded into the data warehouse the transformation is carried out. Jinja, a python template language is integrated with dbt that helps us to loop around the table instead of writing repeated SQL statements, thereby making our lives easier.

We are going to take this sample data in order to explain what we have achieved.

Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

The picture left denotes the sample JSON data that we have received, we then flatten this data and store it into the table as below.

Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

We could see that for the same Order_Id, we have multiple rows with each row specifying an attribute and its value. It would be much better if the same data was instead like this:

Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

This can be achieved in Snowflake with the help of dbt, by using Macro’s and for loops in Jinja where we could dynamically transpose the rows into columns so that we have data in a much better format for querying purposes.

What did we do in dbt?

Once we have the original source table, we used Macros in order to fetch the distinct column values of the attribute field and then looped each row with Jinja checking whether the given row has corresponding value for the column, if yes we are inserting the value for the column. This will fill in the values for columns automatically present and have null for those where no such attribute exists for it.  Once we have transposed columns, we then pick a max of all the columns so that various rows are brought as a single row. We need to group by other columns as we have used an aggregate function. This view, the output comes exactly the way we wanted as mentioned above.

Source table in snowflake:

Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

Then we need to execute the SQL file which contains the logics as explained above.

Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

After execution, we can get the result table as follows.

Achieve Dynamic Transpose in Snowflake for IOT/Web Service Data through dbt and Jinja

Read more about similar Self Service BI topics here and learn more about Visual BI Solutions Microsoft Power BI offerings here. 


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!