What is ETL (Extract, Transform, Load)?

ETL stands for Extract, Transform, Load. It is a data integration process used to read data from one or more source systems, convert that data into a consistent and usable format, and load it into a target system such as a data warehouse, data mart, reporting database, or analytics platform.

In simple terms, ETL development is the work of designing a repeatable data pipeline. The developer decides where the data comes from, how it should be cleaned or transformed, and where it should be loaded for reporting or analysis.

etl - extract, transform, load

Extract, Transform, and Load phases in ETL

The ETL process has three main phases. Each phase has a clear purpose in moving business data from operational systems to a target warehouse or analytics system.

ETL phaseMeaningTypical developer work
ExtractRead data from source systems.Connect to databases, files, applications, or APIs and select the required records.
TransformConvert source data into the required target format.Clean data, convert data types, apply business rules, join data, derive columns, and validate records.
LoadWrite the transformed data into the target system.Insert, update, upsert, or bulk load records into staging tables, warehouse tables, or reporting tables.

Extraction : Extraction is the process of reading the data from source databases into staging areas.

Transformation : Transformation is the process of converting the source data into required warehouse format.

Loading : Loading is the process of writing converted data from staging area into target warehouse systems.

Five practical steps in the ETL process

Although ETL is commonly explained as three phases, real ETL development is easier to understand as five practical steps.

  1. Identify source data: decide which tables, files, or applications should be used.
  2. Extract required records: read the selected columns and rows from the source system.
  3. Clean and standardize data: handle null values, duplicates, invalid formats, and inconsistent values.
  4. Transform using business rules: apply filters, joins, lookups, calculations, aggregations, and data type conversions.
  5. Load and verify target data: write the final records into the target and check row counts, rejected rows, and session logs.

ETL development prerequisites before building mappings

Before designing mappings in an ETL tool, the source database, target database, ODBC connectivity, ETL services, and repository folder should be ready. The following setup sequence is used in this tutorial.

  1. Setup Source and Target database.
  2. Creation of ODBC connections.
    1. Creating source ODBC connection.
    2. Creating target ODBC connection.
  3. Starting Informatica PowerCenter service.
  4. Creating folder.

ETL development process from metadata to workflow monitoring

After the environment is ready, ETL development moves from metadata import to mapping design, session creation, workflow execution, and monitoring. In Informatica PowerCenter, these activities are handled using Designer, Workflow Manager, and Workflow Monitor.

  1. Creation of source metadata.
  2. Creation of target metadata.
  3. Design mapping without business rules.
  4. Creating session for each mapping.
    1. Create reader connection (source).
    2. Create writer connection (Target).
    3. Create workflow.
    4. Run workflow.
    5. Monitoring ETL process. (view state).

GUI based ETL tools for visual mapping design

GUI based ETL tools provide a visual development environment where source definitions, target definitions, transformations, mappings, sessions, and workflows can be created with less hand-written code.

  1. Informatica.
  2. DataStage.
  3. Data Junction.
  4. Oracle Warehouse Builder.
  5. Cognos Decision Stream.

An Informatica PowerCenter is a GUI based ETL  (Extract, Transform, Load) tool from Informatica Corporation. Informatica PowerCenter follows a client-server model and allows developers to design, run, monitor, and administer ETL applications known as mappings and workflows.

Programmatic ETL tools and script based data loading

Programmatic ETL work is performed using SQL, scripts, stored procedures, database utilities, or programming languages. This approach is common when teams need custom transformation logic, scheduled database jobs, or high-volume loading utilities.

  1. PL/SQL.
  2. SAS Base.
  3. Teradata SQL Assistant.
  4. Teradata Utilities.
    1. BTEQ.
    2. Fast Load.
    3. Multi Load.
    4. T(Trickle) Pump.

ETL client-server process in Informatica PowerCenter

A client is a graphical user interface that provides a design-time development environment where ETL applications are designed. In Informatica PowerCenter, developers use client tools to create metadata, mappings, sessions, and workflows. The server-side services execute those objects and maintain repository metadata.

ETL application components: source, business rules, and target

ETL application logically defines Extraction, Transformation and Loading. An ETL application is designed with the following components.

  • Source (Extraction): source tables, flat files, views, or other systems from which data is read.
  • Business Rules (Transformation): cleansing, lookup, expression, filter, join, aggregation, and validation logic.
  • Targets (Loading): warehouse tables, staging tables, reporting tables, or other destination structures.

Repository metadata in an ETL system

A repository is the metadata store of an ETL system. It stores information required to design, run, monitor, and administer ETL applications. In Informatica PowerCenter, repository metadata includes source definitions, target definitions, transformations, mappings, sessions, workflows, users, folders, and execution details.

ETL server and runtime execution engine

An ETL server is an engine that provides the runtime environment for executing ETL applications. It reads instructions from the repository, connects to the source and target systems, runs the mapping logic, handles session logs, and loads final records into the target.

PowerCenter Integration Service (PRIS) for ETL execution

Informatica PowerCenter Integration service is an ETL engine or ETL server that performs Extraction, Transformation and Loading.

  • It is a run time environment where ETL objects are executed.
  • Integration service  reads metadata such as sessions, mappings from repository through repository service.
  • It creates metadata such as log file, which is saved in Repository through Repository service.

Setup source and target database for ETL practice

To setup target database, navigate to Start | Programs | Oracle | Application development | SQL plus.

  • Logon to Oracle with the following user credentials.
    • username : system
    • password : Admin12345.
    • Host string : ORCL.

Click on OK button to login. Now we have to create target user in Oracle database, in this Informatica tutorial we are going to create user called ‘Batch10am‘. Follow the steps given below to create ‘Batch10am’ user.

</>
Copy
Enter user-name: system
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> Create user Batch10am identified by Target;
User created.

SQL> Grant DBA TO Batch10am;
Grant succeeded.

SQL> show user;
USER is "SYSTEM"

SQL> Conn Batch10am/ Target;
Connected.

SQL> Show user;
USER is "BATCH10AM"

As shown above, we have created new user in Oracle database. Now we have to create a table for the user ‘Batch10am’. Learn how to create a sample table in Oracle database.

</>
Copy
SQL> Create table T_employee (Empno number(5),
  2  Ename varchar2(10),
  3  Job varchar2(10),
  4  Sal number(7,2),
  5  Deptno number(2));

SQL> Desc T_employee;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------------

 EMPNO                                              NUMBER(5)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(10)
 SAL                                                NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

Creating ODBC connection for ETL source and target databases

ODBC means Open Database Connectivity. It is a middleware interface that allows ETL tools to connect to different databases through configured drivers and DSN entries.

ODBC connection for Source and Target can be made from System DSN tab, navigate to Start | Settings | Control panel | Administrative tools | Data sources (ODBC) | System DSN | Click on add for new connection.

Creating source ODBC connection for extraction

The source ODBC connection points to the database from which data will be extracted. In a PowerCenter mapping, this connection is used by the session reader to read records from source tables or views.

Creating target ODBC connection for loading

The target ODBC connection points to the warehouse or target database where transformed data will be loaded. In a PowerCenter session, this connection is used by the writer to insert or update target records.

How to start Informatica PowerCenter 10.1 service before ETL development

Before going to ETL development process, first we have to start Informatica PowerCenter 10.1.0. To start navigate to Start | Settings | Control Panel | Administrative Tools | Services | Select Informatica 10.1.0 | Start (right click).

We can also start Informatica PowerCenter 10.1.0 service from Start | Programs | Informatica 10.1.0 | Server | Start Informatica services.

How to create folder in Informatica PowerCenter repository?

– Learn how to create folder in Informatica PowerCenter.

ETL development process inside Informatica PowerCenter

Creation of source metadata in Source Analyzer

A source metadata is created using Source analyzer tool from PowerCenter designer client component. Source metadata tells PowerCenter the structure of the source table, including column names, data types, precision, and scale.

Creation of target metadata for warehouse loading

Target metadata defines the structure into which transformed data will be loaded. A target definition may be imported from the database or manually created in the Target Designer. The target columns should match the required warehouse design.

Design mapping without business rules

A simple first mapping can connect source columns directly to target columns without applying business rules. This helps confirm that source metadata, target metadata, and database connections are working before adding filters, expressions, lookups, aggregations, or other transformations.

What is a session in Informatica ETL?

A session is a task that executes a mapping by sending instructions to the server. The server follows the session instructions to read from sources, apply mapping logic, and load target data. Session can be created using Task Developer tool from Informatica PowerCenter Workflow Manager client component.

Create reader connection for the ETL source

The reader connection is assigned to the source side of the session. It tells PowerCenter which database connection should be used to extract records from the source table or source view.

Create writer connection for the ETL target

The writer connection is assigned to the target side of the session. It tells PowerCenter where to load records after the mapping logic has completed.

Create workflow for running the ETL session

A workflow groups one or more tasks, including sessions, commands, decisions, and email tasks. In a basic ETL example, a workflow may contain only one session task, but production workflows often contain multiple dependent tasks.

Run workflow and execute the ETL mapping

After the workflow is created and validated, run it from Workflow Manager or Workflow Monitor. The Integration Service then executes the session, reads the source records, processes the mapping, and loads the target table.

Monitor ETL process state and session logs

Monitoring is required to check whether the workflow succeeded, failed, or is still running. Review session logs for row counts, rejected rows, database errors, connection errors, transformation errors, and performance details.

ETL and SQL difference for beginners

ETL and SQL are related, but they are not the same. SQL is a language used to query and manipulate relational database data. ETL is a process for moving, transforming, validating, and loading data across systems. SQL is often used inside ETL jobs for extraction queries, transformations, data validation, and target loading.

Is Excel an ETL tool?

Excel can perform simple extract, clean, transform, and load tasks for small datasets, especially with formulas, filters, and Power Query. However, Excel is not normally used as a complete enterprise ETL platform because production ETL usually needs scheduling, logging, error handling, version control, metadata management, large-volume processing, and secure connectivity.

How to learn ETL development as a beginner

To learn ETL, start with the basic data flow: source, staging, transformation, target, and validation. Then practice with SQL, one relational database, sample source tables, and one ETL tool. For Informatica PowerCenter, learn source definitions, target definitions, mappings, transformations, sessions, workflows, and workflow monitoring in that order.

  • Learn SQL basics: SELECT, JOIN, WHERE, GROUP BY, INSERT, UPDATE, and data type conversion.
  • Understand source-to-target mapping documents and business rules.
  • Practice loading data from one table to another before adding transformations.
  • Add common ETL transformations such as filter, expression, lookup, aggregator, and sorter.
  • Review logs and reconcile source and target row counts after every run.

ETL development FAQ

What are the 5 steps of the ETL process?

The five practical ETL steps are source identification, extraction, cleaning or standardization, transformation using business rules, and loading with validation.

Is ETL similar to SQL?

ETL is a data movement and transformation process. SQL is a database query language. SQL is often used inside ETL jobs, but ETL also includes orchestration, scheduling, logging, source-to-target mapping, error handling, and workflow monitoring.

Is ETL hard to learn?

ETL is manageable for beginners who already understand basic SQL and database tables. The difficult part is learning data quality rules, source-to-target mapping, performance tuning, rejected rows, and production troubleshooting.

Is Excel an ETL tool?

Excel can be used for small data preparation tasks, but it is not a complete enterprise ETL tool. ETL platforms are designed for repeatable jobs, large datasets, logs, scheduling, monitoring, reusable metadata, and controlled production loading.

What is the difference between ETL and ELT?

In ETL, data is transformed before it is loaded into the target. In ELT, data is loaded first and transformed inside the target platform. ETL is common in traditional warehouse tools, while ELT is common in modern cloud data platforms.

ETL development editorial QA checklist

  • Does the page explain ETL as Extract, Transform, Load before discussing Informatica PowerCenter?
  • Are the extraction, transformation, and loading responsibilities separated clearly?
  • Does the Informatica section connect each setup step to a real ETL development task?
  • Are source metadata, target metadata, mapping, session, workflow, and monitoring described in the correct order?
  • Does the page answer beginner questions about SQL, Excel, and whether ETL is difficult to learn?

ETL development key points

ETL development is the process of building a controlled data pipeline from source systems to target systems. The developer defines source metadata, target metadata, transformation rules, sessions, workflows, and monitoring steps. In Informatica PowerCenter, the Integration Service executes the ETL workflow while the repository stores the metadata required for development and runtime execution.