Controlling depth at which Basic Normalization expands nested JSON

[sharatvisweswara]

Hi, is there a way to control how deeply basic normalization will expand nested JSON? In my case I'm seeing ~400 tables for each Facebook source, and that's with only a handful of streams selected. I'm looking into moving from a "one set of tables for all facebook accounts" to separate set of tables for each facebook account, but with 400 tables per account I'll run in to Redshift's limits on the number of tables per db with only a fraction of the accounts we're managing. Are there any other best practices around managing a large number of facebook sources, or in general sources of the same type?

[edgao]

check out this roadmap item! #26028

We're in the midst of a significant update to normalization, and one of the changes is that we'll stop unnesting json completely. The first destination to get this treatment will be Bigquery, but our goal is to have this change available in all destinations in the next few months.

[sharatvisweswara]

Thanks @edgao that is very interesting and I filled out the form to be on the Beta when it's ready.

Will it also handle the case of piping multiple sources to the same table? For e.g. one of the issues is that when you "reset" a stream it deletes all data in that table, including data that came from other sources. I also see jobs failing intermittently when too many jobs are trying to insert data into Redshift at the same time.

[edgao]

It won't change that behavior (and in fact we're planning to prevent users from configuring that at all #16173). Airbyte destinations generally assume that they're running in isolation, i.e. there aren't other processes modifying the destination tables. I'd recommend setting up a manual UNION if you need to do that (e.g. as a custom transformation).

The redshift thing sounds separate - can you post an example error? No promises on when we'll get to it (we're not focusing a ton on per-destination updates right now, given that we're in the midst of a big platform-wide destination update) but it's worth at least tracking for when we do have some more resources. (I might just turn it into a new issue, if we can't find a quick fix)

[sharatvisweswara]

Enforcing isolation combined with limited normalization is a good strategy and that's something I can work with. It sounds like the best course of action for us right now is to wait for Destinations V2. All the other work-arounds I can think of are a lot of work that will be wasted if Destinations V2 works out for us.

If I come across that Redshift error again I'll be sure to send over details, I spent 10 mins clicking around without success trying to find that error, but I have over 300 connections total so I barely scratched the surface. One suggestion I could make for AIrbyte UI is to enable sorting connections by "failing first", grouping them by source type, etc.