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.
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?
Hey Jason - There very well could be but I'm not familiar off the top of my head. Seems like a common scenario so I wouldn't be surprised if it exists somewhere.
@@KahanDataSolutions Thanks for the kind response. Might I ask, for example, let's say birth_name is another JSON. And, I simply want to reuse the marco again. intermediate_model as ( {{ flatten_json( model_name = 'source_model', json_column = 'birth_name' )}} ), how can I do it here by passing the source_model into the macro again?
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!
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?
That really depends on the individual. Like anything else, the advanced components will take time/experience to fully learn. But if you know SQL you should be able to start contributing to dbt projects pretty quickly once you learn the basic concepts.
That is a bit more complex but it is doable. Here is an example function in Snowflake that you can use with JSON data to get you there - docs.snowflake.com/en/sql-reference/functions/typeof.html
Want to build a reliable, modern data architecture without the mess?
Here’s a free checklist to help you → bit.ly/kds-checklist
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.
Love to hear that!
I had to change a couple of things to get this to work with SQL Server but this was a lifesaver! Thank you!
Nice! you bet
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?
how do we perform lateral flatten in redshift?
Hello,
Is it the same if our data is in amazon redshift please?
Great video as always!
Thank you!
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 🙏
Great vid!
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!
Hey Jason - There very well could be but I'm not familiar off the top of my head. Seems like a common scenario so I wouldn't be surprised if it exists somewhere.
@@KahanDataSolutions Thanks for the kind response.
Might I ask, for example, let's say birth_name is another JSON. And, I simply want to reuse the marco again.
intermediate_model as (
{{ flatten_json(
model_name = 'source_model',
json_column = 'birth_name'
)}}
),
how can I do it here by passing the source_model into the macro again?
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!
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
Thanks Kanthi! You can use the TYPEOF function in Snowflake to determine the data type - docs.snowflake.com/en/sql-reference/functions/typeof.html
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?
That lateral keyword is really weird, the documentation doesn't mention it's use anywhere. Only when listing multiple it seems.
How long does it take to learn dbt for person knowing sql
That really depends on the individual. Like anything else, the advanced components will take time/experience to fully learn. But if you know SQL you should be able to start contributing to dbt projects pretty quickly once you learn the basic concepts.
@@KahanDataSolutions thanks bro yes I am good in sql
Could you talk about getting proper data types, rather than just using varchar for everything? Would be super useful!
That is a bit more complex but it is doable. Here is an example function in Snowflake that you can use with JSON data to get you there - docs.snowflake.com/en/sql-reference/functions/typeof.html
@@KahanDataSolutions thanks, didn’t know about typeof
TOP
OMG my problem in Bigquery. tks