ETL vs ELT Quiz

ETL Process Overview

• ETL stands for Extract, Transform, Load. This process extracts data from various sources, transforms it into a consistent format, and then loads it into a data warehouse.
• Sources include databases (relational or NoSQL), files (CSV, JSON, XML), SaaS applications, REST APIs, and application events (webhooks).
• The staging area temporarily stores extracted data, ensuring data quality and applying business rules.
• ETL is a backend process, not directly seen by end users.

Data Warehouse Architecture

• Data warehouses often use a three-layer architecture:
  • Warehouse server (data server)
  • OLAP server (HOLAP/MOLAP/ROLAP).
  • Customer server (tool for executing requests; tool for data analysis)

Data Cleaning

• Incomplete data: Data unavailable at time of collection; time difference between acquisition and analysis; human problems with software, hardware.
• Noisy or incorrect data: Errors in collection instruments; human errors; transmission errors; buffer overflow.
• Inconsistent data: Different data sources; violation of functional dependency rules.
• Duplicate data (synonyms, etc.) leads to poor quality data.

Extraction (1/2)

• Extraction collects data from multiple sources like SQL/NoSQL databases, cloud platforms, or XML files.
• It's the most complex ETL task due to varying data quality and quantities across sources.
• Determining data eligibility for extraction can be challenging.
• Extensive analysis is required during the research phase to understand data properly.
• Extraction is a continuous process.

Extraction (2/2)

• Data should be extracted not just once, but repeatedly in a periodic manner to capture any updates.
• Extracted data is often stored temporarily in a staging area within the database.
• Staging allows for confirming data integrity, and necessary business rules are applied.
• Two main types of extraction: logical and physical, each has sub-types.

Logical Extraction

• Full Extraction: Completely extracts all data from the source system reflecting the current state. Used when the system doesn't identify data updates.

• Incremental Extraction: Tracks updates since the last successful extraction. Only updated or newly added parts are loaded.

Load (1/2)

• Loading places (extracted) data into the target system (often a cloud data warehouse).
• The target system prepares the data for analysis by BI tools.
• The loading process must perform quickly to accommodate the large amount of data often loaded in short periods.
• If loading fails, a robust recovery mechanism must be in place to restart from the point of failure to avoid data integrity issues.

Load (2/2)

• Initial Load: Loads all data from the source system to the data warehouse once.
• Incremental Load: Loads only updated or newly added records from the source systems into the data warehouse periodically.
• Full Refresh: Deletes all existing data in the target system, then loads up-to-date data from the source.

Transform (1/3)

• Transformation is the second stage of the ETL process. Data changes to meet the schema and requirements of the destination system.
• This involves converting data from different structures or formats, making it consistent with the target system.
• Data mapping to link data sources, data conversion of formats, and data cleaning are crucial for successful transformation.

Transform (2/3)

• Transformation can require multiple conversion steps to ensure a consistent format (joining, looking up, filtering, expressions, and aggregation).
• Transformation often uses staging tables/areas to temporarily hold intermediate data.
• Data needs proper reformatting/unification into uniform formats (e.g., MM/DD/YY to DD/MM/YY).

Transform (3/3)

• Basic Transformations: Removing duplicates, mapping null values, format conversions, establishing key relations.
• Advanced Transformations: Splitting or joining columns from multiple sources, deriving new columns, aggregating data, and data validation.

ELT vs ETL

• ETL is a process that transforms data in a staging area before loading it into the target system.
• ELT places raw data directly into the target system for further transformations. This can speed up loading. Often used with large volumes of data.

Semantic Modeling (1/4)

• Semantic data models define the meaning and context of data elements and how they relate to each other for business use. Organizations often have specific terminology.
• Models are key to relating data in different systems with different naming standards.

Semantic Modeling (2/4)

• Semantic modeling provides an abstract view of data, allowing users to interact with the data and query it easier without needing to know underlying data structures.
• Columns and tables are renamed in a model to more intuitive and user-friendly names to make the structure and relations more obvious.

Semantic Modeling (3/4)

• Semantic models hide relationships, columns, and tables that aren't relevant for business users.
• Renaming for consistency but also to the business and organizational terminologies.
• Add hierarchies to dimensions (categorize data).
• Calculated measures are added for facts (add calculations to data).

Semantic Modeling (4/4)

• Two main types of semantic models.
  • Tabular: Uses relational model constructs
  • Multidimensional: Uses traditional OLAP constructs such as cubes, dimensions, measurements.

Pros of Semantic Modeling

• Reporting tools display aggregation data accurately.
• Data frequently comes from several sources.
• Data transformations and calculations are accurately defined.
• Data is time-oriented.
• Business models are easier to understand.
• Semantic models simplify data complexities and relationships.
• Easier for users to analyze data quickly.

OLTP vs OLAP

• OLTP (Online Transaction Processing) systems handle transaction-based operations rapidly and immediately.
• OLAP (Online Analytical Processing) systems deliver analysis of large quantities of data and are optimized for reading and querying.

Types of OLAP

• ROLAP (Relational OLAP) stores data in relational databases. This makes it less computationally intensive to implement.
• MOLAP (Multidimensional OLAP) uses specialized multidimensional, native structures for fast, direct data access.
• HOLAP (Hybrid OLAP) combines characteristics of both ROLAP and MOLAP to offer a balance of performance and cost.

Cube

• A data cube is a multidimensional array representing data from a data warehouse.
• Data from many dimensions is organized in a cube to expedite analysis.
• Various manipulations are performed when analyzing the cube including rotate, slice, dice and drill.

MDX (Multi Dimensional Expressions)

• MDX is a query language used with OLAP systems to query and analyze multidimensional data, particularly cube data.
• MDX acts like a SQL-like query language to retrieve and analyze data in the cube.

SQL vs MDX

• SQL is used for operations in a relational database environment.
• MDX targets data in cube structures from OLAP systems.

ETL (Phases)

• Extract: Collects data from sources.
• Transform: Converts data into a usable structure for the target.
• Load: Places transformed data into the target/destination.

ELT (Phases)

• Extract: Collects data from sources.
• Load: Loads raw data directly into target system.
• Transform: Data transformations are done after loading into the target system.

Advantages and Disadvantages of ELT

• Pros: Less time and resources as data transformations and loading happen concurrently.
• Cons: Limited tools and expertise to fully support ELT operations. Fewer tools compared to ETL.