@alex.higgs , thanks for the excellent overview of the dbtvault yesterday.
A follow up on the Multi-active SATs. Are there any dbvault patterns/recipes for loading the multi-active data as Document SATs using JSON
(described here: On Multi-Active Satellites in Data Vault - Datavault Builder)
I am guessing we will need to condense the rows into a JSON object in the Staging layer…
In Snowflake, this would be a VARIANT column. You could provide the VARIANT column containing the JSON as a payload column whether you create the VARIANT yourself or if it’s provided from your source data.
Does that answer your question?
Hi @alex.higgs . Actually what I was looking for was how to accomplish the row condensation using DBT patterns?
If you are on Snowflake I would recommend using
TO_JSON see docs: here.
You should do this in your staging, and it could work as dbtvault
derived_column using the function syntax see docs: here
Alex is right. We have implemented similarly on our side where on the stage layer (view) we created a derived column specifically what we wanted to put in Satellite as variant using TO_JSON as mentioned by Alex. On our side, we were more of converting the columns into JSON structure and saving it but I would think it would be similar for rows as well.
Let me know if that helps!
Thanks @spachunuri and @alex.higgs . We will explore the Snowflake option using CTEs. Looks like there is no DBT recipe/pattern to condense the rows.