MongoDB - Support for user-defined schemas

[evantahler]

Airbyte is a schema-aware application, as are most of the destinations we sync to (e.g data warehouses with a fixed set of columns). For Airbyte's MongoDB source, we have 2 methods of discovering the schema:

1. schema enforced Sample a number of objects (configurable) and inspect the properties of those objects to build the schema
2. schema not enforced Disable schema enforcement, and move the objects as blobs, with only the primary key _id and the data in a data blob.

Option one (schema enforced syncs) work well for MongoDB collections with similar-ish properties on all objects. If you have a collection with differently shaped objects, sampling will likely miss some properties.

A third option was presented in #42862, which would be to allow users with varying objects in the collection to provide a schema they want to use for the sync (e.g. via a JSONSchema document).

Are you interested in Airbyte's MongoDB providing this new "describe your schema explicitly" option for schema-aware syncs?

[fabianboerner]

The main problem with mongodb, is historical data. As you applications evolve you have different needs so your schema evolves. In mongodb every attribute is actually stored on the disk other than relational databases that just have null as a pointer.

So to choose not to migrate millions of old documents has an impact on the used diskspace and memory.
This is especially important when you use cloud providers for mongodb where if you have reached a specific amount of diskspace, memory, iops you need to switch to the next higher tier with more monthly costs involved.

Also in a multi-tenant system some datasets can be empty. Multiple teams can write documents and only provide certain data, historically, maybe legacy systems and else.

So for us we see that it would be important to have at least the possibility to edit the schema.

We used other systems in the past before we switched to airbyte cloud, we needed to switch to self hosted due to budgeting changes.

[terencesolinum]

Stumbled upon that issue while trying to sync mongo to postgres, at the moment it requires us to cast types on column that would happen to become jsonb instead of his actual type. Being able to cast it on Airbyte directly sounds like a great idea

[fabianboerner]

Thats exactly our problem and we cant afford to migrate every document to have null or empty string values just for the sake of having no values.

[terencesolinum]

If data types were consistent, i would have corrected the issue later on, the main issue is that the data types offered by Airbyte is changing each time you reload the schema. As if it's not going through our mongodb from the same starting point nor iterating through anything actually. Parameter from source-mongodb : Document discovery sample size seems to not be working as intended.

That's how I found that discussion, and user-defined schemas sounds like a legit work around.

Same about migrating every document and add null values everywhere. Absence of values should be caught as null by Airbyte instead of generating "null" data types (Converted into Jsonb on a Postgres destination)

[fabianboerner]

That was my thought exactly because we have multiple documents and only a few of that are older have attributes missing, overall if you generate a schema it should have the correct data type.

But i have to rediscover the schema a couple of times to maybe get the right one. But my issue was converted into a discussion.