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.
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?
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?
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!
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
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.
►► The Starter Guide for Modern Data (Free PDF) → www.kahandatasolutions.com/startermds
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
how do we perform lateral flatten in redshift?
Hello,
Is it the same if our data is in amazon redshift please?
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 🙏
TOP
Great video as always!
Thank you!
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?
Great vid!
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?
OMG my problem in Bigquery. tks
That lateral keyword is really weird, the documentation doesn't mention it's use anywhere. Only when listing multiple it seems.
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
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
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