Handle JSON with dbt // jinja & macros

Get a FREE checklist to build simple, reliable data architectures (without the mess)

A common data type you'll see in a modern stack is JSON.

This is often used for data streams, software applications, etc.

The tricky part as an engineer is trying to combine it with structured, relational data.

Fortunately, most modern databases now have ways to store this data type.

Add in the dynamic features of dbt and you can really simplify this task.

In this video we will first look at how to flatten data in snowflake...

Then use the dbt features of jinja and macros to create a reusable component in our project.

Timestamps:
00:00 - Intro
00:38 - Review JSON Data
01:13 - Flatten JSON w/ Snowflake
02:35 - Flatten using Jinja & dbt
09:30 - Remove Trailing Comma
10:53 - Create a Reusable Macro

Title & Tags:
How to Flatten JSON Using Snowflake, DBT and Jinja | dbt tutorial
#kahandatasolutions #dataengineering #databuildtool

[KahanDataSolutions]

Get a FREE checklist to build simple, reliable data architectures (without the mess)

[randolphdeline]

I think you just saved me weeks worth of work. I was just put in charge of flattening dozens of tables with multiple levels totaling 100s of columns and that macro is perfect.

[peytonbadura]

I had to change a couple of things to get this to work with SQL Server but this was a lifesaver! Thank you!

[Mahsa_Faraji]

Thanks for your video. In your code, you've used flatten function. Is it possible to do like this in dbt with postgresql (I know that flatten doesn't exist in postgresql)? or it's better to use other method to flatten json?

[vlogwithkaran]

how do we perform lateral flatten in redshift?

[brodericksmith]

hey there! this video is awesome! do you have any tips on how to turn a variable into a string? I'm trying to take this concept and apply it to Postgres, but i need the column names to be strings to make it work. not sure how to get quotes around the variable name!

[kanthipavuluri]

Nice video! Can you also share how we can determine the data types of the json fields, in this video everything is considered to be a varchar

[wallyflops]

That lateral keyword is really weird, the documentation doesn't mention it's use anywhere. Only when listing multiple it seems.

[linaelyakhloufi]

Hello,
Is it the same if our data is in amazon redshift please?

[kirillmelnikov]

Hi 👋
How can I write if statement on dbt macros which will be go out loop, for example “if table exists - continue, else: break” ?
Thx to advanced 🙏

[summer_xo]

I started off by asking myself, "this is cool but how could I convince my team to use this vs flattening via ADF". By the end seeing how reusable it was totally sold me, so much quicker than spinning up a new ADF pipeline, creating source etc (assuming the extract isn't taking place in ADF). Im curious if you think there are any more benefits using DBT/Snowflake to flatten vs ADF?

[jasonyeung]

Might I ask if there was any package that could recursively do what you did in dbt to unwrap nested JSON and / or array? Thanks!

[yslx]

Could you talk about getting proper data types, rather than just using varchar for everything? Would be super useful!

[abdullahsiddique]

How long does it take to learn dbt for person knowing sql