1. ETL

2. Extract

3. Transform

4. Load

ETL

• • ETL stands for Extract, Transform and Load, a set of common processes for collecting, integrating, and distributing data to make it available for additional purposes, such as analytics, machine learning, reporting, or other business purposes. 

  • ETL enables an organization to carry out data-driven analysis and decision-making using operational data.

Steps

1. 1. Data is collected from one or more sources.

   2. Data is transformed to apply the required business logic to the data, including cleaning it, re-modeling it, and joining it to other data.

   3. Data is stored in its new destination (such as a data warehouse or non-relational database).

Extract Data

• • In this step, the focus is first to understand where useful original data is stored and what form or formats it’s in. Then, processes are implemented to access it, perhaps through recurring nightly batch processes, or in real-time or triggered on the occurrence of specific events or actions.

Transform Data

• • In this step, original data is cleaned, formats are changed, and data is aggregated so it's in the proper form to be stored in a data warehouse or other sources, so it can be used by reporting tools or other parts of the business. 

  • Techniques:

    1. Cleaning

    2. Filtering

    3. Joining

    4. Normalizing

    5. Data Structuring

    6. Feature Engineering

    7. Anonymizing and Encrypting

    8. Sorting

    9. Aggregating

  • Example transformations include:

    1. Deriving calculated values based on the raw data.

    2. Re-ordering or transposing the data.

    3. Adding metadata or associating key-value pairs to the data.

    4. Removing repetitive data or adding counts of occurrences of data.

    5. Encoding or decoding the data to match destination requirements.

    6. Validating the data.

    7. Performing search and replace functions on the data.

    8. Changing the schema of the data, for example from text to values or IDs.

  • Approaches:

    1. Schema-on-write.

    2. Schema-on-read.

Load Data into Storage

• • In this step, the transformed data is stored in one or more places where applications, reporting tools, and other business processes can access it, such as in unstructured object stores, text files, or complex data warehouses. 

  • The process varies widely depending on the nature of the business requirements and the applications and users the data serves.

  • Techniques:

    1. Full loading.

    2. Incremental loading.

    3. Scheduled loading.

    4. On-demand loading.

    5. Batch and stream.

    6. Push and pull.

    7. Prallel and serial.

ETL vs ELT