Building a Complete Apache Airflow ETL Pipeline with Docker, Open-Meteo, and PostgreSQL 构建完整的 Apache Airflow ETL 管道：使用 Docker、Open-Meteo 和 PostgreSQL

TL;DR

• The article provides a comprehensive, theory-inclusive guide to building a complete Apache Airflow ETL pipeline using Docker, Open-Meteo API, and PostgreSQL.
• It addresses practical questions about Airflow connections, XCom usage, database setup, and task-to-task data passing that are often glossed over in introductory tutorials.
• The implementation demonstrates an ETL (Extract, Transform, Load) workflow where weather data is extracted from an HTTP API, transformed within Airflow, and loaded into a PostgreSQL database via parameterized SQL.
• The project structure is Git-ready and includes proper deployment with Docker Compose, featuring separate services for Airflow webserver, scheduler, worker, triggerer, Redis, and PostgreSQL.
• The guide clarifies important distinctions between databases, data warehouses, and data lakes while emphasizing that this specific implementation focuses on relational database loading rather than data lake architectures.

Why It Matters

This resource is highly relevant for AI practitioners and data engineers who need to move beyond basic Airflow examples and understand how to build production-grade ETL pipelines with proper architecture, connection management, and data flow patterns. By addressing common pain points like API hostname configuration, XCom usage for inter-task communication, and PostgreSQL table creation/loading, it bridges the gap between theoretical knowledge and practical implementation skills essential for real-world data engineering workflows.

Technical Details

• The pipeline uses Apache Airflow 2.10.4 deployed via Docker Compose with supporting services including PostgreSQL for both Airflow metadata and weather data storage, and Redis as the Celery message broker
• Three main tasks form the DAG: extract_weather (HTTP GET to Open-Meteo API), transform_weather (normalizes field names and structures data), and load_weather (executes parameterized SQL upsert into public.weather_data table)
• Airflow Connections are configured for open_meteo_api (HTTP connection) and postgres_default (PostgreSQL connection), solving the question of how components know which servers to contact
• XCom (Cross Communication) is used to pass small amounts of data between tasks, specifically transferring the extracted and transformed weather data from one task to the next
• The PostgreSQL connection uses the hostname "postgres" rather than "localhost" due to Docker networking configuration, where service names resolve to container IPs within the Docker network
• The implementation creates the target table automatically if it doesn't exist and performs upsert operations, though notes potential gaps in sequence IDs after successful upserts
• The project follows a standard distributed Airflow architecture with separate webserver (port 8080), scheduler, worker, and triggerer services

Industry Insight

For AI professionals and data engineers, this guide demonstrates best practices for creating maintainable, deployable ETL pipelines that can be version-controlled and scaled. The emphasis on proper connection management, clear separation of concerns between extraction, transformation, and loading stages, and use of Docker for consistent environments provides a template that can be adapted for various data sources and destinations. Understanding these fundamentals prepares practitioners to handle more complex scenarios involving cloud platforms, larger datasets, and advanced scheduling requirements while maintaining reliable, observable workflows.

TL;DR

• 本文通过构建一个完整的Apache Airflow ETL管道，详细解释了工作流编排、XCom通信、Docker Compose部署及PostgreSQL数据加载等核心实践。
• 项目基于Open-Meteo API提取天气数据，经转换后存入PostgreSQL的public.weather_data表，实现了从API到关系型数据库的端到端ETL流程。
• 文章深入剖析了Airflow架构组件（Webserver, Scheduler, Worker, Triggerer, Redis）的角色分工及其在分布式环境下的协作机制。
• 明确区分了ETL与ELT模式，并澄清了数据库、数据仓库与数据湖的概念差异，强调本案例为传统ETL而非云原生数据湖方案。
• 提供了可复现的Git友好型项目结构，涵盖Connection配置、参数化SQL插入、任务依赖定义及故障排查指南，适合初学者落地生产级调度系统。

为什么值得看

对于AI从业者或数据工程师而言，本文不仅展示了如何利用Airflow构建可扩展的数据管道，还揭示了实际工程中常被忽视的配置细节（如XCom传递、连接管理、序列ID间隙问题），有助于理解现代数据基础设施中的编排逻辑与运维挑战。其结合Docker容器化部署与开源API的实践路径，也为团队快速搭建原型系统提供了可迁移的技术参考。

技术解析

• ETL流程设计：采用三步式Extract-Transform-Load架构，先从Open-Meteo HTTP API获取JSON格式原始天气数据，再通过Python任务进行字段重命名、类型转换和缺失值处理，最后使用参数化SQL语句将结构化数据写入PostgreSQL表，确保数据一致性。
• Airflow DAG建模：定义名为etl_weather_pipeline的有向无环图（DAG），包含三个顺序执行的任务节点——extract_weather → transform_weather → load_weather，利用>>运算符显式声明任务依赖关系，保证执行时序正确性。
• XCom跨任务通信：在extract_weather任务中抓取API响应结果并通过XCom推送至后续任务；transform_weather读取该数据进行清洗后再将处理后的字典对象经由XCom传递给load_weather任务，实现轻量级状态共享。
• Docker Compose集成部署：使用docker-compose.yml文件统一编排Airflow Webserver、Scheduler、Worker、Triggerer四个核心服务以及作为消息队列的Redis和作为元数据存储的目标PostgreSQL实例，支持一键启动完整运行环境。
• Connection与权限隔离：创建两个关键Connection对象——open_meteo_api用于指定外部API访问凭证与主机地址，postgres_default则配置本地PostgreSQL连接字符串，使各任务无需硬编码敏感信息即可安全调用资源。

行业启示

• 标准化ETL模板价值凸显：随着企业数据源日益复杂，预定义好连接管理、错误重试、日志监控等通用模块的Airflow DAG模板能显著降低重复开发成本，提升交付效率与稳定性。
• 容器化+编排工具成主流选择：借助Docker Compose对多服务生命周期进行集中管控，配合Kubernetes进一步弹性扩缩容，已成为中小型团队构建敏捷数据平台的标准组合拳。
• 概念辨析驱动架构选型：清晰理解ETL/ELT差异及各类存储介质特性（如PG适用于事务性操作而S3更适合海量非结构化归档），有助于根据业务场景合理设计数据流动路径，避免过度工程化或能力不足。