Reliable API ingestion
Retrieve Astronomy Picture of the Day records from NASA's APOD API through the configured Airflow HTTP connection. The workflow should make the source and extraction step clear.
NASA APOD APITO MOMNA'S ENGINEERING WORLD
Airflow ETL Pipeline with PostgreSQL and NASA APOD API
Orchestrating a Python-based ETL workflow with Apache Airflow to extract astronomy data from NASA's APOD API, transform selected fields, and load them into PostgreSQL.
Retrieve Astronomy Picture of the Day records from NASA's APOD API through the configured Airflow HTTP connection. The workflow should make the source and extraction step clear.
NASA APOD APIMap the API response into the fields used by the pipeline: title, explanation, URL, date and media type. Required-field validation and handling of incomplete responses remain important quality considerations.
DATA QUALITYDefine the workflow as an Airflow DAG with a daily schedule and an explicit task order, so table preparation, extraction, transformation and loading can be orchestrated.
APACHE AIRFLOWCreate the target apod_data table and map the transformed API fields to appropriate PostgreSQL columns. The schema should support reliable storage and later querying.
POSTGRESQLEvaluate retries, task failures and repeated execution. The current DAG has no explicit retry configuration, and its plain INSERT statement does not prevent duplicate APOD dates.
RELIABILITY REVIEWSupport the technical explanation with the actual DAG view and evidence of a successful run. These screenshots must come from the real Airflow environment.
EXECUTION EVIDENCECollecting data from an external API is only one part of an ETL workflow. The pipeline must also transform the response into a consistent structure, coordinate task execution, store records in PostgreSQL and make failures or repeated runs manageable.
Build and document an Apache Airflow workflow that extracts NASA APOD data, transforms selected fields and loads them into PostgreSQL. Assess the scheduling, data schema, failure recovery, duplicate prevention and execution evidence needed to evaluate the pipeline as an engineering solution.
03 / ARCHITECTURE & SYSTEM DESIGN
The pipeline connects NASA's Astronomy Picture of the Day (APOD) API to a PostgreSQL destination through an Apache Airflow DAG. Each stage has a defined responsibility: prepare the destination, extract the response, transform selected fields and load the resulting record.
The DAG references nasa_api for the HTTP extraction
and my_postgres_connection for database operations.
These are Airflow connection IDs; the environment must have the
corresponding connection settings configured before execution.
Provides the Astronomy Picture of the Day response over HTTP.
The create_table task creates apod_data if the table does not exist.
HttpOperator sends a GET request to the planetary/apod endpoint and converts the response to JSON.
TaskFlow maps title, explanation, URL, date and media_type into a smaller data object.
The load task inserts the transformed values into the apod_data table.
Schema shown from the CREATE TABLE statement in dags/etl.py. The table uses a generated primary key; the APOD date is not currently declared UNIQUE.
04 / IMPLEMENTATION & CORE COMPONENTS
The implementation uses an Airflow DAG to coordinate API extraction, Python-based transformation and PostgreSQL loading. Rather than treating ETL as one large operation, the repository separates the workflow into tasks with distinct responsibilities.
IMPLEMENTATION PRINCIPLE
Separate extraction, transformation and persistence so each stage can be inspected in the DAG. The current code provides the core ETL path; explicit retries, duplicate-safe loading and stronger data validation still need to be addressed in the reliability review.
The nasa_apod_postgres DAG defines the daily workflow
and coordinates the order in which table preparation, extraction,
transformation and loading are performed.
dags/etl.py Airflow's HttpOperator sends a GET request to planetary/apod using the nasa_api connection. The response is converted to JSON for downstream use.
HttpOperator transform_apod_data() selects the title, explanation,
URL, date and media type from the response. The current function
uses empty-string fallbacks for missing keys.
@task create_table() uses PostgresHook to
create apod_data if it does not already exist.
The schema includes a serial primary key and five APOD data fields.
my_postgres_connection load_data_to_postgres() inserts the transformed
values into the destination table using parameterized SQL.
The current insert is not protected by a unique APOD-date constraint.
INSERTThe repository includes an Astro runtime Dockerfile and Docker Compose configuration for local development, including a PostgreSQL service and persistent database volume.
Sources:Dockerfile, docker-compose.yml The nasa_apod_postgres DAG coordinates table preparation,
API extraction, data transformation and database loading. Airflow
manages the dependencies between these operations, while the API
response becomes the input for the transformation and loading tasks.
Values from dags/etl.py
@dailyFalsenasa_apod_postgres2026-06-22EXECUTION PRINCIPLE
The schedule defines the intended cadence; dependencies define
task order. catchup=False disables automatic creation
of historical scheduled runs when the DAG is activated. Actual
execution status must be checked in Airflow.
Prepare apod_data in PostgreSQL
HTTP GET → NASA APOD JSON response
Select five fields from the API response
Parameterized INSERT → apod_data
This is a conceptual rendering of the dependencies in the supplied DAG—not a screenshot of the live Airflow interface.
create_table() uses PostgresHook to create apod_data if it does not exist.
The DAG sets this task upstream of extraction.
HttpOperator calls planetary/apod through the nasa_api connection and converts
the HTTP response to JSON.
transform_apod_data() maps title, explanation,
URL, date and media type into the object consumed by the loader.
load_data_to_postgres() uses PostgresHook and parameterized SQL to insert
the transformed values into apod_data.
The extraction output is passed to the transformation task, and the transformed output is passed to the loader. This describes the configured data flow; it does not assert that a run has succeeded.
PostgreSQL · apod_data
The nasa_apod_postgres DAG defines daily
scheduling and disables historical catchup.
An Airflow HttpOperator sends a GET request
to NASA's APOD endpoint using the nasa_api connection.
planetary/apodThe TaskFlow function selects the required fields from the API response before passing the resulting object to the loading task.
PostgresHook creates the table if needed
and inserts transformed values into apod_data using SQL parameters.
Implementation flow derived from the repository source. This is a code-based illustration, not a screenshot of a live Airflow DAG run.
A data pipeline must do more than define a sequence of tasks. Its configuration, transformation logic and database operations need to be reviewable. This section examines what the supplied repository establishes and separates source-level findings from runtime behaviour that still needs verification.
The DAG uses @daily and catchup=False. Its dependencies connect
table creation, API extraction, transformation and loading.
The transform selects five fields. The destination table defines an ID, title, explanation, URL, date and media type.
Explicit retries are not configured in the DAG, and the plain INSERT does not guarantee idempotency. These are implementation limitations, not completed features.
Source reviewed. Runtime evidence not supplied. The ZIP contains no DAG screenshot or recorded successful run. The actual execution status and database output must be verified separately; no successful run is claimed here.
A scheduled ETL workflow must do more than move data. It should handle API and database failures predictably, protect connection credentials, and avoid creating duplicate records when a run is repeated. This review distinguishes the safeguards present in the repository from the improvements still needed.
A successful task should mean that the intended data was stored correctly—not merely that the workflow reached its final step.
The current DAG does not explicitly configure a retry policy or task-level retry settings. A temporary API outage or database connection failure may therefore need manual investigation. Configure appropriate retries and retry delays, then verify their behavior through actual task runs.
The current loader uses a standard INSERT statement, while the table's primary key is a generated SERIAL ID. The APOD date has no unique constraint, and the insert has no conflict-handling logic. Reprocessing the same API response can therefore create duplicate records. A unique date key and an appropriate UPSERT strategy should be considered.
The extraction task reads its NASA API key from the
Airflow connection named nasa_api, rather
than embedding the key in the active request template.
However, the source file also contains a commented-out
API URL with a key-like value. Remove that line and
rotate the key if it is genuine. Keep database
credentials out of committed configuration as well.
The transformation selects title, explanation, URL, date, and media type using dictionary defaults. This helps handle missing keys, but missing values can become empty strings rather than meaningful validation failures. Add checks for required fields, valid dates, API error responses, and database outcomes before treating a run as trustworthy.
First, remove any real secret from source history and rotate it if exposed. Next, configure and test task retries, validate the API response, and make loading safe to repeat. Finally, verify the behavior with Airflow task logs and PostgreSQL queries. These are recommendations—not claims that the changes have already been applied or tested.
Docker Compose and the Astro CLI support the project's local development workflow. Cloud deployment is described in the README, but a deployed environment is not verified by the supplied repository files.
Schedules tasks and makes dependencies explicit in a DAG.
Requires orchestration configuration, monitoring and recovery policies.
Provides structured astronomy-picture metadata through an HTTP endpoint.
Depends on API availability, response validity and credential configuration.
Stores extracted fields in a relational table with typed columns.
Needs connection management, integrity rules and duplicate-safe loading.
Provides a repeatable local Airflow development environment.
Adds container configuration and environment-specific setup requirements.
Orchestration, transformation and storage have separate responsibilities. That separation makes the pipeline easier to reason about, but dependable delivery still requires explicit retry policies, data validation and duplicate-safe database writes.
This project connects an external API, an orchestrated extraction workflow, a Python transformation step and a relational database. The important engineering decision is not simply which tools to use, but how their boundaries affect repeatability, failure recovery and data integrity.
Airflow represents task dependencies and a daily schedule explicitly. The trade-off is additional infrastructure and operational configuration compared with a simple standalone script.
The transformation selects five APOD fields before loading them into typed PostgreSQL columns. Missing keys currently fall back to empty strings, so stronger validation is needed before treating every response as trustworthy.
Task orchestration can make execution manageable, but it does not make database writes idempotent. A unique business key and conflict-aware loading should be considered before relying on safe reruns.
This project brings together API integration, workflow orchestration, Python transformation and relational storage. The repository documents the pipeline design and its intended workflow. Runtime success, data quality and production reliability still require execution evidence and testing.
Engineering takeaway A defined ETL workflow is a foundation. Reliable data delivery also requires validation, safe reruns, recovery policies and evidence from real executions.
nasa_apod_postgres defines a daily
schedule with catchup=False and an
explicit dependency between table creation and extraction.
The transformation selects title, explanation, URL, date and media type before passing the resulting dictionary to the database-loading task.
The DAG creates apod_data if it does
not exist and inserts the selected APOD fields using
parameterized SQL.
The supplied ZIP contains no Airflow DAG screenshot, successful task-run screenshot or database query output proving that records were loaded.
Check required APOD fields, date values and API error responses before loading.
Consider a unique date constraint and conflict-aware inserts to prevent duplicate records.
Configure retry settings and test temporary API and PostgreSQL failures.
Capture the DAG graph, successful task states, logs and a database query showing loaded records.
An Apache Airflow pipeline that extracts NASA Astronomy Picture of the Day data, transforms selected fields, and loads them into PostgreSQL.
Explore source repository ↗Selected paths from the supplied repository. Open GitHub for the complete tree and the current contents of each file.
The repository brings together the Airflow DAG, PostgreSQL integration, container configuration and setup documentation. Start with the workflow definition to trace how NASA APOD data moves from an HTTP request into a relational table.
Open dags/etl.py to inspect nasa_apod_postgres, its daily schedule,
task dependencies and TaskFlow transformations.
Review the apod_data table definition and
parameterized PostgreSQL insert statement in the DAG source.
Use the README, Dockerfile, Compose configuration and dependency files to understand the development environment.
Make reruns safe. Define a reliable uniqueness rule and conflict-handling strategy so repeated runs do not create duplicate APOD records.
Strengthen recovery. Configure and test task retries, timeouts and failure handling.
Validate incoming data. Check required fields, date values, API responses and database constraints before loading records.
Document runtime proof. Add a real DAG graph screenshot and a successful Airflow run showing completed task states and the execution date.
These are proposed improvements, not claims that they are already implemented. The supplied source does not configure explicit task retries or guarantee idempotent inserts. Cloud deployment and successful-run evidence must be verified separately.