M. ENGINEERING SYSTEMS
PORTFOLIO / 2026
INITIALIZING 00%
M.
A LITTLE SPACE TO BUILD BIG THINGS

WELCOME.

TO MOMNA'S ENGINEERING WORLD

CLOUD✳ DEVOPS✳ AUTOMATION✳ RELIABILITY✳ KUBERNETES✳ CLOUD✳ DEVOPS✳ AUTOMATION✳ RELIABILITY✳ KUBERNETES✳
SYSTEMS WITH PURPOSE
01 — 06
CASE STUDY 06 / DATA ENGINEERING
ETL PIPELINE AUTOMATION

From API
to database.

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.

APACHE AIRFLOW PYTHON NASA APOD API POSTGRESQL DOCKER
ETL DATA PIPELINE 01 — 04
01
NASA APOD API Retrieve astronomy data
↘
02
Apache Airflow Orchestrate the ETL tasks
↘
03
Transform Data Select fields from the API response
↘
04
PostgreSQL Insert records into apod_data
TARGET
API INGESTION AIRFLOW → POSTGRESQL
PROJECT TYPE ETL Pipeline
DATA SOURCE NASA APOD API
ORCHESTRATION Apache Airflow
DATA STORAGE PostgreSQL
PIPELINE REQUIREMENTS ETL-06
R01

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 API
R02

Consistent data transformation

Map 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 QUALITY
R03

Scheduled orchestration

Define 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 AIRFLOW
R04

Structured PostgreSQL storage

Create the target apod_data table and map the transformed API fields to appropriate PostgreSQL columns. The schema should support reliable storage and later querying.

POSTGRESQL
R05

Failure recovery & safe reruns

Evaluate 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 REVIEW
R06

Verifiable execution

Support the technical explanation with the actual DAG view and evidence of a successful run. These screenshots must come from the real Airflow environment.

EXECUTION EVIDENCE
REQUIREMENTS & REVIEW POINTS R01 — R06
02 / PROBLEM & REQUIREMENTS
THE ENGINEERING CHALLENGE

API data is only useful when the pipeline is reliable.

Collecting 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.

01 PROJECT OBJECTIVE

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.

SOLUTION SCOPE
01 NASA APOD API → Data extraction
02 Airflow TaskFlow → Transformation
03 PostgreSQL → Structured persistence
04 Scheduling → Reliability → Verification

03 / ARCHITECTURE & SYSTEM DESIGN

DATA FLOW DESIGN

From astronomy API to structured data.

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.

CORE TECHNOLOGIES
Apache Airflow Python NASA APOD API PostgreSQL Docker Astro CLI

Two configured connections

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.

ETL architecture

nasa_apod_postgres
01

NASA APOD API

Provides the Astronomy Picture of the Day response over HTTP.

SOURCE
02

Prepare the destination

The create_table task creates apod_data if the table does not exist.

POSTGRES HOOK
03

Extract the API response

HttpOperator sends a GET request to the planetary/apod endpoint and converts the response to JSON.

HTTP OPERATOR
04

Transform selected fields

TaskFlow maps title, explanation, URL, date and media_type into a smaller data object.

PYTHON TASK
05

Load into PostgreSQL

The load task inserts the transformed values into the apod_data table.

DATABASE

Destination data contract

apod_data
id · SERIAL PK title · VARCHAR(255) explanation · TEXT url · TEXT date · DATE media_type · VARCHAR(50)

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.

↳ Architecture is based on the supplied repository. The DAG defines a daily schedule with catchup disabled. A live Astro Cloud or AWS deployment is not verified by these files, so it is not represented here as a confirmed runtime component.

04 / IMPLEMENTATION & CORE COMPONENTS

Each task owns one part of the pipeline.

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.

TECHNOLOGY STACK
AF Apache Airflow Py Python API NASA APOD PG PostgreSQL DK Docker AS Astro CLI

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.

COMPONENT / 01 Orchestration
DAG

Airflow DAG

The nasa_apod_postgres DAG defines the daily workflow and coordinates the order in which table preparation, extraction, transformation and loading are performed.

Source: dags/etl.py
COMPONENT / 02 Extraction
GET

NASA APOD HTTP request

Airflow's HttpOperator sends a GET request to planetary/apod using the nasa_api connection. The response is converted to JSON for downstream use.

Operator: HttpOperator
COMPONENT / 03 Transformation
Py

TaskFlow transformation

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.

Pattern: @task
COMPONENT / 04 Schema setup
SQL

PostgreSQL table creation

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.

Hook: my_postgres_connection
COMPONENT / 05 Persistence
DB

PostgreSQL loader

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.

Operation: INSERT
COMPONENT / 06 Runtime
DK

Docker & Astro environment

The 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
05 / WORKFLOW & EXECUTION
ETL LIFECYCLE

One scheduled run. Four connected stages.

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.

AF

DAG scheduling configuration

Values from dags/etl.py

SCHEDULE @daily
CATCHUP False
DAG ID nasa_apod_postgres
START DATE 2026-06-22

EXECUTION 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.

PIPELINE DEPENDENCIES 01 — 04

How the tasks connect

LOGICAL DAG FLOW
DB

01 · Create table

Prepare apod_data in PostgreSQL

UPSTREAM DEPENDENCY ↓
API

02 · Extract

HTTP GET → NASA APOD JSON response

RESPONSE OUTPUT ↓
Py

03 · Transform

Select five fields from the API response

TRANSFORMED OBJECT ↓
SQL

04 · Load

Parameterized INSERT → apod_data

This is a conceptual rendering of the dependencies in the supplied DAG—not a screenshot of the live Airflow interface.

Execution responsibilities

FROM dags/etl.py
01

Prepare PostgreSQL

INITIALIZE

create_table() uses PostgresHook to create apod_data if it does not exist. The DAG sets this task upstream of extraction.

02

Extract the API response

EXTRACT

HttpOperator calls planetary/apod through the nasa_api connection and converts the HTTP response to JSON.

task_id: extract_apod
03

Transform selected fields

TRANSFORM

transform_apod_data() maps title, explanation, URL, date and media type into the object consumed by the loader.

04

Insert the record

LOAD

load_data_to_postgres() uses PostgresHook and parameterized SQL to insert the transformed values into apod_data.

Task output hand-off

HTTP JSON → Transformed object → SQL insert

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.

DATA DESTINATION

PostgreSQL · apod_data

IMPLEMENTATION REVIEW

Inside the pipeline.

04 COMPONENTS
AF Airflow
Py Python
API NASA APOD
PG PostgreSQL
COMPONENT / 01 AF

Scheduled orchestration

The nasa_apod_postgres DAG defines daily scheduling and disables historical catchup.

Schedule @daily
Catchup False
Source file dags/etl.py
COMPONENT / 02 API

Extract

An Airflow HttpOperator sends a GET request to NASA's APOD endpoint using the nasa_api connection.

NASA APOD planetary/apod
COMPONENT / 03 Py

Transform

The TaskFlow function selects the required fields from the API response before passing the resulting object to the loading task.

title date url explanation media_type
COMPONENT / 04 PG

Load into PostgreSQL

PostgresHook creates the table if needed and inserts transformed values into apod_data using SQL parameters.

DB
apod_data PostgreSQL · Structured records
↗
DATA FLOW SOURCE → STORAGE
01 NASA API JSON response
02 Transform Selected fields
03 PostgreSQL SQL insert

Implementation flow derived from the repository source. This is a code-based illustration, not a screenshot of a live Airflow DAG run.

GH Explore the source code GitHub repository · dags/etl.py
INSPECT THE IMPLEMENTATION

From API data to structured storage.

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.

01

Scheduling and dependencies

The DAG uses @daily and catchup=False. Its dependencies connect table creation, API extraction, transformation and loading.

02

Transformation and schema

The transform selects five fields. The destination table defines an ID, title, explanation, URL, date and media type.

03

Reliability still needs testing

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.

DESIGNING FOR FAILURE

Reliable pipelines. Controlled data delivery.

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.

CORE PRINCIPLE

A successful task should mean that the intended data was stored correctly—not merely that the workflow reached its final step.

PIPELINE RISK REVIEW 04 / FOCUS AREAS
01
AIRFLOW / TASK RECOVERY

Retries & Recovery

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.

RETRY POLICY NOT CONFIGURED
02
POSTGRESQL / DATA INTEGRITY

Duplicate Prevention

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.

IDEMPOTENCY NOT GUARANTEED
03
CONNECTIONS / SECRET HANDLING

API Credentials

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.

REVIEW SOURCE FOR EXPOSED SECRETS
04
PYTHON / DATA QUALITY

Validation & Failure Signals

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.

VALIDATION NEEDS EXPANSION

Hardening priorities

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.

01 Protect credentials 02 Configure retries 03 Prevent duplicates 04 Verify stored data
SOURCE REVIEWED dags/etl.py Review repository
ENGINEERING ANALYSIS / 08

Technology decision matrix

04 COMPONENTS
01 API NASA APOD Data source
02 AF Airflow Extract & orchestrate
03 Py Transform Select APOD fields
04 PG PostgreSQL Store records
DEVELOPMENT ENVIRONMENT

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.

TECHNOLOGY ENGINEERING VALUE TRADE-OFF
01 Apache Airflow

Schedules tasks and makes dependencies explicit in a DAG.

Requires orchestration configuration, monitoring and recovery policies.

02 NASA APOD API

Provides structured astronomy-picture metadata through an HTTP endpoint.

Depends on API availability, response validity and credential configuration.

03 PostgreSQL

Stores extracted fields in a relational table with typed columns.

Needs connection management, integrity rules and duplicate-safe loading.

04 Docker + Astro

Provides a repeatable local Airflow development environment.

Adds container configuration and environment-specific setup requirements.

DESIGN IMPLICATION

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.

SYSTEM DESIGN TRADE-OFFS

Every pipeline choice has an operational cost.

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.

A

Orchestration vs. simplicity

Airflow represents task dependencies and a daily schedule explicitly. The trade-off is additional infrastructure and operational configuration compared with a simple standalone script.

B

Flexible API data vs. strict storage

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.

C

Repeatable runs vs. duplicate records

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.

SOURCE REPOSITORY Explore the implementation
09 / RESULTS & REFLECTION PIPELINE REVIEW

What the pipeline demonstrates.

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.

AIRFLOW NASA APOD PYTHON POSTGRESQL DOCKER
REPOSITORY EVIDENCE

What the source establishes

04 ITEMS
01
ORCHESTRATION

Daily Airflow DAG

nasa_apod_postgres defines a daily schedule with catchup=False and an explicit dependency between table creation and extraction.

IN SOURCE
02
API & TRANSFORMATION

NASA APOD field mapping

The transformation selects title, explanation, URL, date and media type before passing the resulting dictionary to the database-loading task.

IN SOURCE
03
DATABASE

PostgreSQL table and loader

The DAG creates apod_data if it does not exist and inserts the selected APOD fields using parameterized SQL.

IN SOURCE
04
EXECUTION EVIDENCE

Successful run not yet documented

The supplied ZIP contains no Airflow DAG screenshot, successful task-run screenshot or database query output proving that records were loaded.

NOT PROVIDED
ENGINEERING REVIEW / 01

Lessons & next improvements

L01

Validate incoming data

Check required APOD fields, date values and API error responses before loading.

L02

Make reruns safe

Consider a unique date constraint and conflict-aware inserts to prevent duplicate records.

L03

Test failure recovery

Configure retry settings and test temporary API and PostgreSQL failures.

L04

Prove execution

Capture the DAG graph, successful task states, logs and a database query showing loaded records.

SOURCE FILE dags/etl.py Review repository
PIPELINE REPOSITORY SOURCE AVAILABLE
MOMNA-SHAHID17 /

airflow-etl-pipeline-postgresql-astro

An Apache Airflow pipeline that extracts NASA Astronomy Picture of the Day data, transforms selected fields, and loads them into PostgreSQL.

Explore source repository
REPOSITORY STRUCTURE SOURCE MAP

Selected paths from the supplied repository. Open GitHub for the complete tree and the current contents of each file.

TECHNOLOGY INDEX
A Apache Airflow Py Python PG PostgreSQL D Docker N NASA APOD API As Astro CLI
Inspect the implementation Review source code, dependencies and documentation
ENGINEERING HANDOFF

Follow the pipeline. Explore the source.

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.

01

Start with the DAG

Open dags/etl.py to inspect nasa_apod_postgres, its daily schedule, task dependencies and TaskFlow transformations.

02

Trace the data model

Review the apod_data table definition and parameterized PostgreSQL insert statement in the DAG source.

03

Review the local setup

Use the README, Dockerfile, Compose configuration and dependency files to understand the development environment.

POTENTIAL NEXT ITERATIONS 01—04
+

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.

Scroll to Top