Introduction

This pipeline automates the extraction of daily weather data from the Open-Meteo API, using Apache Airflow, DBT, and DuckDB, all running in Docker containers.

Diagram of ELT Pipeline

This pipeline automates the extraction, transformation, and loading (ELT) of daily climate data using Apache Airflow, DBT, and DuckDB in a Dockerized environment:

1. Run Apache Airflow in a Docker container to orchestrate the pipeline.
2. Airflow DAG triggers DBT by sending a command through a bind-mounted socket to the Docker Daemon.
3. Docker Daemon starts a new container that executes DBT transformations.
4. DBT accesses Open-Meteos API via DuckDB's HTTP extension.
5. Requested data is stored in a local DuckDB file through a bind mount, ensuring persistence.

For a detailed explanation of this workflow, check out my GitHub repository:
👉 ELT Pipeline to Extract Daily Climate

Figure 1: ELT Pipeline to Extract Daily Climate Data using Docker, Airflow, DBT and DuckDB.

Daily Climate Data Visualization

Interactive Figure 2 (below) visualizes daily climate data extracted from the Open-Meteo API through the ELT pipeline, which utilizes Apache Airflow, DBT, and DuckDB. The data represents historical weather conditions for each U.S. state capital, allowing users to explore different climate variables such as temperature, precipitation, wind speed, and soil moisture over a selected date range. Users can dynamically filter the data by choosing a specific city and climate variable through dropdown selectors, ensuring a customizable and detailed view of weather patterns. Note: You can access data dating back to 2025/01/01 with a delay of 2 days.

The pipeline ensures efficient data extraction, transformation, and storage, enabling seamless analysis and visualization of state-level climate trends over time. The dataset is updated daily by running the build evidence project after extracting data in the Airflow DAG, and the processed data is then pushed to an AWS S3 bucket for storage and accessibility.