What are the key architectural patterns in ETL design?

ETL (Extract, Transform, Load) systems rely on several architectural patterns to handle data workflows efficiently. Three key patterns include batch processing, stream-based processing, and the hub-and-spoke (or “medallion”) architecture. Each addresses specific use cases, balancing trade-offs between latency, scalability, and complexity.

Batch Processing is the most common ETL pattern, designed for large-scale data ingestion at scheduled intervals. Data is extracted from sources in bulk, transformed in batches (e.g., cleansing or aggregating), and loaded into a destination system. This approach is ideal for scenarios where real-time data isn’t critical, such as daily sales reporting or monthly financial reconciliation. Tools like Apache Airflow or Informatica automate batch workflows, leveraging parallelism and fault tolerance. For example, a retail company might use batch ETL to process overnight sales data from hundreds of stores, ensuring reports are ready by morning. While efficient for high-volume workloads, batch processing introduces latency, making it unsuitable for real-time analytics.

Stream-Based ETL processes data continuously, often in near-real time, using technologies like Apache Kafka or Apache Flink. This pattern extracts data as it’s generated (e.g., IoT sensor feeds or clickstreams), applies transformations incrementally, and loads results into systems like data lakes or dashboards. A bank might use stream ETL to detect fraud by analyzing transactions as they occur, flagging anomalies within seconds. Unlike batch processing, stream-based systems handle low-latency requirements but add complexity in managing state, out-of-order data, and recovery. Tools like Kafka Streams or AWS Kinesis simplify these challenges with built-in windowing and exactly-once processing guarantees.

Hub-and-Spoke (Medallion Architecture) organizes data into layers (bronze, silver, gold) to enforce quality and structure. Raw data lands in a “bronze” layer (e.g., a data lake), undergoes cleaning in a “silver” layer, and is aggregated into a “gold” layer for consumption. For instance, a healthcare provider might ingest raw patient records into bronze, standardize formats in silver, and create aggregated views in gold for analytics. This pattern, popularized by Delta Lake and Databricks, ensures traceability and reduces redundancy. It scales well for large organizations but requires careful governance to avoid siloed transformations. Tools like Apache Spark or dbt often manage the transformations between layers.