How does a typical ETL architecture look for a data warehouse?

A typical ETL (Extract, Transform, Load) architecture for a data warehouse consists of three primary stages: extracting data from source systems, transforming it into a usable format, and loading it into the warehouse. The process starts with extracting raw data from various sources such as databases, APIs, flat files, or external systems. This data is then cleaned, enriched, and restructured during the transformation phase to align with the warehouse schema and business rules. Finally, the processed data is loaded into target tables in the warehouse, where it becomes accessible for reporting and analytics. Tools like Apache NiFi, Informatica, or custom Python scripts often handle these stages, with orchestration frameworks like Apache Airflow managing workflow dependencies.

In the extraction phase, data is pulled from heterogeneous sources. For example, a retail company might extract sales transactions from a PostgreSQL database, customer interactions from a Salesforce API, and inventory logs from CSV files. Incremental extraction (capturing only new or changed data) is commonly used to reduce load on source systems, while full extraction might occur during initial loads. Data is temporarily stored in a staging area—such as cloud storage (e.g., Amazon S3) or a relational database—to isolate raw data before transformation. This staging layer ensures that source system performance isn’t impacted and provides a recovery point if errors occur downstream.

During transformation, business logic is applied to convert raw data into a consistent format. This includes tasks like deduplication, data type conversion (e.g., string-to-date), and aggregations (e.g., daily sales totals). For instance, a healthcare system might standardize patient records by merging data from EHR systems and normalizing diagnosis codes. Transformation often occurs in a dedicated processing layer using tools like dbt (data build tool) or Spark, which handle complex joins, validations, and error logging. Invalid records might be routed to a quarantine table for manual review. Finally, the cleaned data is loaded into the warehouse’s dimensional model (e.g., star schema) or a data lakehouse structure. Loading strategies include bulk inserts for historical data or upserts (update/insert) for incremental updates. Performance optimizations like partitioning tables in BigQuery or indexing in Snowflake are applied here to speed up queries. Monitoring tools track job success, latency, and data quality metrics to ensure reliability.