Production System vs Data warehouse
Firstly lets understand types of schema:
1. Normalized: This is how we usually structure data for transactional systems such as an order entry system, an HR system and so on — in other words, traditional, operational databases.
2. Denormalized:
3. Dimensional:The fact table holds the measures (usually numerical) that we want to analyze and the dimension tables contain data/information about the ways in which we want to analyze the data.. The dimension tables in particular are often highly de-normalized.
Every technology company will have a production system like Netsuite, Salesforce, or other DBMS like Oracle, Postgres, etc.
In the Production system, the tables are in normalized form. Hence, there are many small tables, where each table is designed to represent a single concept.
In the Datawarehosue, we have a dimensional schema. It is a simplified view of all the data
Dimensional models combine normalized and denormalized table structures
In ELT: We extract data from Production system, normalize the data and feed it in data warehouse. Later we will transform normalized schema to dimensional schema by using SQL
EL is the same for all companies. It’s just replicating the same data. Secondly, we need fewer data engineers.