Multi-active data as Document SATs using dbtvault

AutomateDV (formerly known as dbtvault)
1

Hello all,

@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…

Thanks,
Saqib

2

Hi Saqib!

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?

3

Hi @alex.higgs . Actually what I was looking for was how to accomplish the row condensation using DBT patterns?

Thanks,
Saqib

4

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

5

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!

6

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.