Data Pipeline Flow Builder

A Directed Acyclic Graph (DAG) represents a pipeline where nodes are processing steps and edges are data flows between them. 'Acyclic' means no circular dependencies — data always moves forward. Tools like Apache Airflow, Prefect, and Dagster all model pipelines as DAGs. A DAG guarantees that tasks can be executed in a valid order (topological sort) without infinite loops.

ETL (Extract-Transform-Load) transforms data before loading into the destination. ELT (Extract-Load-Transform) loads raw data first, then transforms inside the data warehouse. ETL is better when the destination can't handle raw data or transformation is complex. ELT is preferred with modern cloud warehouses (Snowflake, BigQuery, Redshift) since they have abundant compute for in-warehouse SQL transformations. dbt is the leading ELT transformation tool.

Source: reads from databases, APIs, files, streams. Transform: reshapes, cleans, enriches data. Filter: removes rows that don't match criteria. Aggregate: groups and summarizes (SUM, COUNT, AVG). Join: combines two datasets by a key. Sink: writes to destination (database, data lake, API). A well-structured pipeline has clear separation between these concerns to make each step testable and replaceable.

Production pipelines need idempotent steps (running twice produces the same result), retry logic with exponential backoff, dead-letter queues for poison messages, and alerting on failures. Tools like Airflow support retry_delay and max_retries per task. For Kafka pipelines, use consumer group offsets to resume from the last committed position. Always design for at-least-once delivery and make sinks idempotent.

Batch pipelines process large data chunks on a schedule (hourly, daily) — simpler, easier to backfill, good for analytics. Streaming pipelines process events in real time (sub-second latency) — more complex, better for operational use cases like fraud detection. Tools: Airflow/dbt for batch, Apache Flink/Spark Streaming/Kafka Streams for streaming. Many modern platforms (Databricks, Snowflake) support both via micro-batching.