ETL Concepts, Architecture, and Trade Vocabulary

What Is ETL and Why Does It Matter?

Data rarely lives in one place. A hospital keeps patient records in one system, billing data in another, and lab results in a third. A retailer pulls sales from point-of-sale terminals, inventory from a warehouse system, and customer data from a CRM. Before any of that data can be analyzed or reported on, it must be moved, cleaned, and organized. That movement process is called ETL — Extract, Transform, Load.

ETL is one of the core responsibilities of a database administrator. Whether you are preparing data for a data warehouse, migrating a legacy system, or feeding a reporting dashboard, you will work with ETL pipelines throughout your career. DAMA International's DMBOK2 (Chapter 8) identifies data integration and interoperability as a formal knowledge area, and Microsoft's DP-300 exam includes implementing data storage solutions that depend directly on ETL concepts. Understanding ETL is therefore not optional — it is foundational.

The Three Stages of ETL

Stage 1 — Extract

Extraction is the process of pulling raw data from one or more source systems. Sources can include relational databases (PostgreSQL, Oracle, SQL Server), flat files (CSV, JSON, XML), APIs, mainframe exports, spreadsheets, or cloud storage buckets.

The extract stage must handle several real-world challenges:

• Full vs. incremental extraction — A full extract pulls every record every time. An incremental extract pulls only records that have changed since the last run, using a timestamp column or a change-data capture (CDC) mechanism. Incremental loads are faster and put less load on the source.
• Source system availability — Extracting during peak business hours can slow production systems. DBAs typically schedule extract jobs during low-traffic windows.
• Data integrity at the source — The extract stage should never modify source data. This is a firm professional standard. Source systems are read-only during extraction.

Stage 2 — Transform

Transformation is where raw data is shaped into the form the destination system requires. This is usually the most complex stage. Common transformation operations include:

• Cleansing — Removing duplicates, correcting misspellings, standardizing formats (e.g., phone numbers, dates).
• Conforming — Resolving differences between systems (e.g., one system uses "M/F" for gender; another uses "Male/Female").
• Aggregating — Summarizing rows (e.g., daily sales totals from individual transactions).
• Joining — Combining data from multiple sources into a single record.
• Applying business rules — Calculating derived fields such as profit margin or age from date of birth.

Transformations happen in a dedicated environment — often called a staging area — separate from both the source and the destination. This protects both systems and allows you to audit and rerun transformations without side effects.

Stage 3 — Load

Loading is the process of writing transformed data into the target system, which is commonly a data warehouse, data mart, or operational database. Two loading strategies are standard:

• Full load — Replaces all existing target data on each run. Simple but slow for large datasets.
• Incremental (delta) load — Inserts or updates only changed records. Faster, but requires careful handling of deletes and slowly changing dimensions (SCDs).

Load jobs must be monitored for constraint violations, duplicate keys, and referential integrity errors. A failed load should not leave the target in a partial or inconsistent state — transactions and rollback strategies are essential here.

ETL vs. ELT

A related pattern is ELT — Extract, Load, Transform. The order of the last two steps is reversed: raw data is loaded directly into the target system first, and transformations happen inside that system afterward using its own compute power.

ELT became practical as cloud data warehouses (such as those running on large cloud platforms) gained the processing power to handle massive transformation workloads. Key differences:

| Feature | ETL | ELT |

|---|---|---|

| Where transformation happens | Staging/middleware server | Inside the target system |

| Best for | Sensitive data requiring masking before load | Large-scale analytics on powerful cloud platforms |

| Latency | Slightly higher (transform first) | Can be lower (load first, transform on demand) |

| Tool maturity | Long-established | Newer, cloud-centric |

A DBA must choose the right pattern for the workload, the platform, and the organization's data governance requirements.

Pipeline Architecture and Components

A typical ETL pipeline consists of these components:

1. Source connectors — Drivers or APIs that connect to source systems.

2. Extraction engine — Executes queries or file reads against sources.

3. Staging area — Temporary storage (database schema or file system) for raw extracted data.

4. Transformation engine — Applies business rules, cleansing, and restructuring logic.

5. Target connectors — Write mechanisms for the destination database.

6. Orchestration layer — Schedules jobs, manages dependencies, handles retries, and logs results.

7. Monitoring and alerting — Tracks row counts, error rates, and job duration; sends alerts on failure.

ETL Tool Categories

Three broad categories of tools exist:

• Hand-coded scripts — Python, shell scripts, or SQL procedures written by the DBA. Maximum flexibility; high maintenance burden.
• Open-source orchestrators — Tools such as Apache Airflow manage job scheduling and dependencies without vendor lock-in.
• Cloud-managed services — Fully managed pipeline services provided by major cloud vendors. Lower operational overhead; potential cost and lock-in considerations.

Accuracy and Data Stewardship

ETL work touches sensitive data — personally identifiable information (PII), financial records, and protected health information. A DBA must apply appropriate access controls at every stage, mask or encrypt sensitive fields before they leave secure environments, and maintain audit logs of all pipeline activity. These are not suggestions — they are professional and often legal obligations. DAMA's data governance framework and your organization's data policies define the standards you must follow.