ETL vs ELT

Reference: https://www.xplenty.com/blog/etl-vs-elt/#conclusion

The Biggest Advantages of ELT

The primary advantage of ELT over ETL relates to flexibility and ease of storing new, unstructured data. With ELT, you can save any type of information—even if you don’t have the time or ability to transform and structure it first—providing immediate access to all of your information whenever you want it. Furthermore, you don’t have to develop complex ETL processes before data ingests, and saves developers and BI analysts time when dealing with new information. 

The Biggest Advantages of ETL

One of the biggest advantages of ETL over ELT relates to the pre-structured nature of the OLAP data warehouse. After structuring/transforming the data, ETL allows for speedier, more efficient, more stable data analysis. In contrast, ELT isn't ideal when speedy analysis is desired.

ETL vs. ELT Comparison

	ETL	ELT
Adoption of the technology and availability of tools and experts	ETL is a well-developed process used for over 20 years, and ETL experts are readily available.	ELT is a new technology, so it can be difficult to locate experts and more challenging to develop an ELT pipeline compared to an ETL pipeline.
Availability of data in the system	ETL only transforms and loads the data that you decide is necessary when creating the data warehouse and ETL process. Therefore, only this information will be available.	ELT can load all data immediately, and users can determine later which data to transform and analyze.
Can you add calculations?	Calculations will either replace existing columns, or you can append the dataset to push the calculation result to the target data system.	ELT adds calculated columns directly to the existing dataset.
Compatible with data lakes?	ETL is not normally a solution for data lakes. It transforms data for integration with a structured relational data warehouse system.	ELT offers a pipeline for data lakes to ingest unstructured data. Then it transforms the data on an as-needed basis for analysis.
Compliance	ETL can redact and remove sensitive information before putting it into the data warehouse or cloud server. This makes it easier to satisfy GDPR, HIPAA, and CCPA compliance standards. It also protects data from hacks and inadvertent exposure.	ELT requires you to upload the data before redacting/removing sensitive information. This could violate GDPR, HIPAA, and CCPA standards. Sensitive information will be more vulnerable to hacks and inadvertent exposure. You could also violate some compliance standards if the cloud-server is in another country.
Data size vs. complexity of transformations	ETL is best suited for dealing with smaller data sets that require complex transformations.	ELT is best when dealing with massive amounts of structured and unstructured data.
Data warehousing support	ETL works with cloud-based and onsite data warehouses. It requires a relational or structured data format.	ELT works with cloud-based data warehousing solutions to support structured, unstructured, semi-structured, and raw data types.
Hardware requirements	Cloud-based ETL platforms (like Xplenty) don't require special hardware. Legacy, onsite ETL processes have extensive and costly hardware requirements, but they are not as popular today.	ELT processes are cloud-based and don't require special hardware.
How are aggregations different?	Aggregation becomes more complicated as the dataset increases in size.	As long as you have a powerful, cloud-based target data system, you can quickly process massive amounts of data.
Implementation Complexity	ETL experts are easy to procure when building an ETL pipeline. Highly evolved ETL tools are also available to facilitate this process.	As a new technology, the tools to implement an ELT solution are still evolving. Moreover, experts with the requisite ELT knowledge and skills can be difficult to find.
Maintenance requirement	Automated, cloud-based ETL solutions, like Xplenty, require little maintenance. However, an onsite ETL solution that uses a physical server will require frequent maintenance.	ELT is cloud-based and generally incorporates automated solutions, so very little maintenance is required.
Order of the extract, transform, load process	Data transformations happen immediately after extraction within a staging area. After transformation, the data is loaded into the data warehouse.	Data is extracted, then loaded into the target data system first. Only later is some of the data transformed on an “as-needed” basis for analytical purposes.
Costs	Cloud-based SaaS ETL platforms that bill with a pay-per-session pricing model (such as Xplenty) offer flexible plans that start at approximately $100 and go up from there, depending on usage requirements. Meanwhile, an enterprise-level onsite ETL solution like Informatica could cost over $1 million a year!	Cloud-based SaaS ELT platforms that bill with a pay-per-session pricing model offer flexible plans that start at approximately $100 and go up from there. One cost advantage of ELT is that you can load and save your data without incurring large fees, then apply transformations as needed. This can save money on initial costs if you just want to load and save information. However, financially strapped businesses may never be able to afford the processing power required to reap the full benefits of their data lake.
Transformation process	Transformations happen within a staging area outside the data warehouse.	Transformations happen inside the data system itself, and no staging area is required.
Unstructured data support	ETL can be used to structure unstructured data, but it can’t be used to pass unstructured data into the target system.	ELT is a solution for uploading unstructured data into a data lake and make unstructured data available to business intelligence systems.
Waiting time to load information	ETL load times are longer than ELT because it's a multi-stage process: (1) data loads into the staging area, (2) transformations take place, (3) data loads into the data warehouse. Once the data is loaded, analysis of the information is faster than ELT.	Data loading happens faster because there's no waiting for transformations and the data only loads one time into the target data system. However, analysis of the information is slower than ETL.
Waiting time to perform transformations	Data transformations take more time initially because every piece of data requires transformation before loading. Also, as the size of the data system increases, transformations take longer. However, once transformed and in the system, analysis happens quickly and efficiently.	Since transformations happen after loading, on an as-needed basis—and you transform only the data you need to analyze at the time—transformations happen a lot of faster. However, the need to continually transform data slows down the total time it takes for querying/analysis.

Data mart vs Data warehouse

A Data Mart is a specific, subject oriented, repository of data designed to answer specific questions for a specific set of users. So an organization could have multiple data marts serving the needs of marketing, sales, operations, collections, etc. A data mart usually is organized as one dimensional model as a star-schema (OLAP cube) made of a fact table and multiple dimension tables.

In contrast, a Data Warehouse (DW) is a single organizational repository of enterprise wide data across many or all subject areas. The Data Warehouse is the authoritative repository of all the fact and dimension data (that is also available in the data marts) at an atomic level.

Data warehouse or Data Mart?

Data Warehouse:

• Holds multiple subject areas
• Holds very detailed information
• Works to integrate all data sources
• Does not necessarily use a dimensional model but feeds dimensional models.

Data Mart

• Often holds only one subject area- for example, Finance, or Sales
• May hold more summarised data (although many hold full detail)
• Concentrates on integrating information from a given subject area or set of source systems
• Is built focused on a dimensional model using a star schema.

Also Refer:

 http://www.datamartist.com/data-warehouse-vs-data-mart

http://opensourceanalytics.com/2006/03/14/data-mart-vs-data-warehouse-the-great-debate/

Surrogate key Vs primary key

Surrogate Key is similar to Primary key interms of its characteristics like the value will be user generated or auto generated sequence having not null values. Surrogate Key is primary key for the Key columns defined in the Dimension tables in DWH. In OLTP system this will be achieved by introducing Composite Primary Keys. Suppose, if you want query a table with composite key, you have to define no of columns specified in the composite key in the where condition this will be overhead to database. Instead, we define a Surrogate key which is unique for the composite key in Dimension Table. Having a Surrogate Key will help the BI Team to fetch the dimension records faster than using Primary or composite primary key.

Star and SnowFlake Schema

Star Schema: 
Star schema architecture is the simplest data warehouse design. The main feature of a star schema is a table at the center, called thefact table and the dimension tables which allow browsing of specific categories, summarizing, drill-downs and specifying criteria.
Typically, most of the fact tables in a star schema are in database third normal form, while dimensional tables are de-normalized (second normal form).
Despite the fact that the star schema is the simpliest datawarehouse architecture, it is most commonly used in the datawarehouse implementations across the world today (about 90-95% cases)





Snowflake Schema

Snowflake schema architecture is a more complex variation of a star schema design. The main difference is that dimensional tables in a snowflake schema are normalized, so they have a typical relational database design.


Snowflake schemas are generally used when a dimensional table becomes very big and when a star schema can’t represent the complexity of a data structure. For example if a PRODUCT dimension table contains millions of rows, the use of snowflake schemas should significantly improve performance by moving out some data to other table (with BRANDS for instance).


The problem is that the more normalized the dimension table is, the more complicated SQL joins must be issued to query them. This is because in order for a query to be answered, many tables need to be joined and aggregates generated. 

What is a dimension table?

A dimensional table is a collection of hierarchies and categories along which the user can drill down and drill up. It contains only the textual attributes

What is Fact table?

Fact Table contains the measurements or metrics or facts of business process. If your business process is "Sales”, then a measurement of this business process such as "monthly sales number" is captured in the Fact table. Fact table also contains the foreign keys for the dimension tables.

Ref:

http://dba.fyicenter.com/Interview-Questions/Data-Warehousing/Difference_between_Snow_flake_and_Star_Schema_W.html

More info:

Star schema architecture is the simplest data warehouse design. The main feature of a star schema is a table at the center, called the fact table and the dimension tables which allow browsing of specific categories, summarizing, drill-downs and specifying criteria.
Typically, most of the fact tables in a star schema are in database third normal form, while dimensional tables are de-normalized (second normal form).

Fact table

The fact table is not a typical relational database table as it is de-normalized on purpose - to enhance query response times. The fact table typically contains records that are ready to explore, usually with ad hoc queries. Records in the fact table are often referred to as events, due to the time-variant nature of a data warehouse environment.
The primary key for the fact table is a composite of all the columns except numeric values / scores (like QUANTITY, TURNOVER, exact invoice date and time).

Typical fact tables in a global enterprise data warehouse are (apart for those, there may be some company or business specific fact tables):

sales fact table - contains all details regarding sales
orders fact table - in some cases the table can be split into open orders and historical orders. Sometimes the values for historical orders are stored in a sales fact table.
budget fact table - usually grouped by month and loaded once at the end of a year.
forecast fact table - usually grouped by month and loaded daily, weekly or monthly.
inventory fact table - report stocks, usually refreshed daily

Dimension table

Nearly all of the information in a typical fact table is also present in one or more dimension tables. The main purpose of maintaining Dimension Tables is to allow browsing the categories quickly and easily.
The primary keys of each of the dimension tables are linked together to form the composite primary key of the fact table. In a star schema design, there is only one de-normalized table for a given dimension.

Typical dimension tables in a data warehouse are:

time dimension table
customers dimension table
products dimension table
key account managers (KAM) dimension table
sales office dimension table

Orcl: OLTP vs OLAP

OLTP vs. OLAP

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

• OLTP (On-line Transaction Processing) is involved in the operation of a particular system. OLTP is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF). It involves Queries accessing individual record like Update your Email in Company database.
• OLAP (On-line Analytical Processing) deals with Historical Data or Archival Data. OLAP is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema). Sometime query need to access large amount of data in Management records like what was the profit of your company in last year.

- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second.

- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques.

The following table summarizes the major differences between OLTP and OLAP system design.

	OLTP System Online Transaction Processing (Operational System)	OLAP System Online Analytical Processing (Data Warehouse)
Source of data	Operational data; OLTPs are the original source of the data.	Consolidation data; OLAP data comes from the various OLTP Databases
Purpose of data	To control and run fundamental business tasks	To help with planning, problem solving, and decision support
What the data	Reveals a snapshot of ongoing business processes	Multi-dimensional views of various kinds of business activities
Inserts and Updates	Short and fast inserts and updates initiated by end users	Periodic long-running batch jobs refresh the data
Queries	Relatively standardized and simple queries Returning relatively few records	Often complex queries involving aggregations
Processing Speed	Typically very fast	Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes
Space Requirements	Can be relatively small if historical data is archived	Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP
Database Design	Highly normalized with many tables	Typically de-normalized with fewer tables; use of star and/or snowflake schemas
Backup and Recovery	Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability	Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method