ELT pipeline for Mongo

as you state, Fivetran will load your data into Redshift putting individual fields in columns where it can and putting everything else into varchar columns as JSON. So at that point you basically have a Data Lake - all your data in an analytical platform but basically still in source format and available for you to do whatever you want with it.

Initially, if you don't know much about your data and just want to investigate it, you can probably leave it as it is. Redshift has SQL functions that allow you to query the elements of a JSON structure so there is no need to build additional tables and more ETL just to allow you to investigate your data - especially as these tables may get thrown away once you understand your data and decide what you want to do with it.

If you have proper reporting requirements then that is the point where you can start to design a schema that will support these requirements (I'm not sure why you suggested a MYSQL schema as MYSQL is a database vendor?). Traditionally an analytical schema would be designed as a Kimball Dimensional model (facts and dimensions) but the type of schema you decide to design will depend on:

1. The database platform you are using (in your case, Redshift) and the type of structures it works best with e.g. star schema or "flat" tables
2. The BI tool you are using and how it expects to have data presented to it

For example (and I'm not saying this is a real world example), if Redshift works ok with star schemas but better with flat tables and Looker has to have a star schema then it probably makes more sense to build star schemas in Redshift as this is a single modelling exercise - rather than model flat tables in Redshift and then have to model star schemas in Looker.

Hope this helps?

It depends on how you need the final stage of your data analysis presented, and what the purpose of your data analysis is. As stated by NickW, assuming you need to integrate your data into a BI tool the schema should be adapted according to the tool's data format requirements.

a mongodb ETL/ELT process might looks like this:

Select Connection: Select the set connection Collection Name:Choose the collection by using the [database].[collection] format. If you pulling data from your authentication database, only the [collection] name can be determined. Examples: ea sample.products east .

Extract Method:

All: pull the entire data in the table.

Incremental: pull data by incremental value.

Incremental Attributes: Set the name of the incremental attribute to run by. I.e: UpdateTime .

Incremental Type: Timestamp | Epoch. Choose the type of incremental attribute.

Choose Range:

In Timestamp, choose your date increment range to run by. In Epoch, choose the value increment range to run by. If no End Date/Value entered, the default is the last date/value in the table. The increment will be managed automatically Include End Value: Should the increment process take the end value or not

Interval Chunks: On what chunks the data will be pulled by. Split the data by minutes, hours, days, months or years.

Filter: Filter the data to pull. The filter format will be a MongoDB Extended JSON.

Limit: Limit the rows to pull.

Auto Mapping: You can choose the set of columns you want to bring, add a new column or leave it as it is.

Converting Entire Key Data As a STRING In cases the data is not as expected by a target, like key names started with numbers, or flexible and inconsistent object data, You can convert attributes to a STRING format by setting their data types in the mapping section as STRING

Conversion exists for any value under that key. Arrays and objects will be converted to JSON strings.

Use cases: Here are few filtering examples:

{"account":{"$oid":"1234567890abcde"}, "datasource": "google", "is_deleted": {"$ne": true}} 

date(MODIFY_DATE_START_COLUMN) >=date("2020-08-01")