[Feature] Add Subsidiary information to NetSuite BS model

[rishi-tripathy]

Is there an existing feature request for this?

• [X] I have searched the existing issues

Describe the Feature

In the same way that the Income Statement and Transaction Details models have a column persisted through the transform that marks "subsidiary_id", it would be super helpful to have the same on the balance sheet. I'm actually working with @bmayer99 to segment some NetSuite data by subsidiary but we're running into issues from the balance sheet (the IS is working well!)

Describe alternatives you've considered

We attempted to join the BS transform model with the accountsubsidiarymap table, but this ran into issues where we were unable to split out values from accounts that were shared by multiple subsidiaries, since the subsidiary info isn't present in the BS model.

I'd love to be able to use Fivetran quickstart transforms here as a way to reduce the surface area of maintenance on our end.

Are you interested in contributing this feature?

• [ ] Yes.
• [X] Yes, but I will need assistance and will schedule time during your office hours for guidance.
• [ ] No.

Anything else?

We're using quickstart transformations via Fivetran to populate this data in our destination (Snowflake), so if we are able to get the subsidary column added to the BS model, having Fivetran update the dbt model version for their netsuite quickstart transform would be super helpful!

[rishi-tripathy]

Also noted in bmayer99's comment here! https://github.com/fivetran/dbt_netsuite/issues/47#issuecomment-1762166783

[bmayer99]

based on the model definition, it appears the subsidiary information is in transactions_with_converted_amounts and it's be just a matter of adding a column to the model:

from https://github.com/fivetran/dbt_netsuite/blob/6e8f101cb4a7a522341d942958128dc65208ed21/models/netsuite2/netsuite2__balance_sheet.sql#L33-L35

to

    transactions_with_converted_amounts.transaction_id,
    transactions_with_converted_amounts.transaction_line_id,
    transactions_with_converted_amounts.subsidiary_id,
    reporting_accounting_periods.accounting_period_id as accounting_period_id,

@fivetran-joemarkiewicz you are most familiar with these, wdyt?

[rishi-tripathy]

@fivetran-joemarkiewicz — would love your thoughts here! Let me know if there's more context we can provide to get to a path forward on this. I think this would be a big win for the most involved + complex (multi-entity) customers using the dbt models to pull out their "three financial statements".

[fivetran-catfritz]

Hi @rishi-tripathy, your request comes at a great time! In connection with issue #93, we actually will be adding in subsidiary information in the next release. Stay tuned!

[rishi-tripathy]

Hey @fivetran-catfritz - excited to have seen this release go out: https://github.com/fivetran/dbt_netsuite/releases/tag/v0.12.0-b1

It looks like it's still in beta, and that the flag for using_to_subsidiary is off by default.

Could you enable this for all Netsuite connectors for the Quickstart transformations used by our instance? I don't see an option to set variables in the quickstart settings on my end.

This is a blocking issue for working with our customers. Let me know if it's helpful to chat more about the need here, and thanks again!

[fivetran-joemarkiewicz]

Hey @rishi-tripathy! Thanks for reaching out. We are planning to release this officially early next week! Once this is released on the dbt hub there will be about a few day delay until it is available on Quickstart. I will see what I can do to prioritize the deployment to quickstart as well.

[rishi-tripathy]

Hey Joe! Checking in here — any updates so far? Appreciate the responsiveness :)

[fivetran-catfritz]

Hi @rishi-tripathy we plan to release this to the dbt hub tomorrow!

[rishi-tripathy]

Great! Any timeline on getting this into the quickstart as well? Thanks!

[fivetran-joemarkiewicz]

Hi @rishi-tripathy we have shared the update with our engineering team to deploy the update to Quickstart. I imagine it will be live after the holidays.

[rishi-tripathy]

Is there a way to track or subscribe to updates to the quickstart transforms? Looking forward to using this one!

[dubdesigndesk]

Just wondering if it was a choice to only to do to_subsidiary and not the from_subsidiary also? Would it be a big issue to include this and does it apply to all final models or just the balance sheet?

[rishi-tripathy]

Following up here as it's been a few weeks. When should I expect to see the updated schema including subsidiary information hit the quickstart transform for Netsuite?

[fivetran-joemarkiewicz]

Hi @rishi-tripathy! Last I heard the team owning the deployment of the Quickstart transformations were prioritizing getting an automation in place to automatically deploy updates to Quickstart following my teams updates to the dbt packages.

Good news is that they just tested the automation and it looks ready to roll out to our packages! I am going to be adding some files next week to ensure the automatic deployment of the Quickstart transformations. I will add the file early next week and ask our engineering team to prioritize kicking off the automation for Netsuite.

[rishi-tripathy]

Hi Joe, could you connect me with that team responsible for deployment or mention them in this issue? I went to check my connector and saw this error:

There's also no option to re-run the transform from this screen.

In checking my destination, the schema I see being pushed (below) in is different than what's linked from the transform overview page.

Thanks again!

[fivetran-joemarkiewicz]

Hi @rishi-tripathy I am sorry to see that you are running into this issue. There must be an update to the Transformations page that is causing some inconsistent behavior. Unfortunately the engineering teams don't usually engage within these GitHub discussions. I would highly recommend opening a support ticket to raise this issue with our support team and they can ensure engineering takes a look at this and addresses the fix.

On the Quickstart deployment, I expect the update will be merged into main and deployed to Quickstart by this evening.