An end-to-end ELT pipeline on AWS that turns iNaturalist bird observations into a Power BI dashboard every morning.
AWS Lambda S3 Athena dbt Apache Airflow Terraform Power BI GitHub Actions
Personal project · Design, development and deployment · Code on GitHub
Context and problem
Thousands of naturalists share their bird sightings on iNaturalist. But the raw data (large, nested JSON files) can’t easily answer questions like: which species are seen around Nantes? In which season? Where?
The goal was to build a reliable, automated and low-cost pipeline that collects this data every day, guarantees its quality and makes it available in an analytics tool, following modern data stack practices.
The solution
The pipeline follows an ELT architecture: data is loaded raw first, then transformed inside the data lake.
- Ingestion: an AWS Lambda function calls the iNaturalist REST API incrementally (only observations created since the last run) and lands the raw JSON in S3.
- Transformation: dbt, running on Amazon Athena, cleans and structures the data through a medallion architecture (raw → staging → marts) into a Parquet star schema.
- Orchestration: Apache Airflow 3 runs the ingestion every morning, then each dbt model and its tests, with automatic retries and email alerts.
- Analytics: Power BI connects to the final tables: density map, seasonality, rankings and species photos.
Stack and my role
| Layer | Tools |
|---|---|
| Ingestion | AWS Lambda (Python), SSM Parameter Store |
| Storage | Amazon S3 (data lake), AWS Glue Data Catalog |
| Transformation | dbt Core, Amazon Athena, Parquet |
| Orchestration | Apache Airflow 3, Astronomer Cosmos, EC2 |
| Infrastructure | Terraform, IAM (least privilege), Session Manager |
| Quality and CI | 77 dbt tests, pytest, GitHub Actions |
| Visualization | Power BI |
My role: end-to-end ownership: specification, data modeling, development, infrastructure as code, production deployment and documentation.
Technical highlights
Incremental, idempotent ingestion. A watermark stored in SSM records the last successful run and only moves forward after a successful write to S3. If a run fails, the next one resumes from the same point, with no data loss and no duplicates.
Data quality as a gate. Every dbt model is followed by its tests (uniqueness, nulls, geographic bounds, referential integrity). A failing test automatically blocks every downstream model, so invalid data never reaches the dashboard.
Fine-grained orchestration. With Cosmos, each dbt model becomes a pair of Airflow tasks (run + test): 13 visible tasks that can be retried one by one.
Security and cost. An EC2 instance with no open ports (access through Session Manager), least-privilege IAM roles, and compressed, partitioned Parquet. Total cost: about $20 per month.
Challenges and lessons learned
Deploying to a real environment surfaced problems no test environment would have shown. I fixed and documented twelve of them, including:
- A dangerous incremental table: the
insert_overwritestrategy would have wiped the current month’s data every day. With ~18,000 rows, a full daily rebuild proved simpler, always correct and just as cheap. - ISO 8601 timestamps with time zones rejected by Athena, fixed with
from_iso8601_timestamp. - An initial load too long for Lambda (3-minute limit): the history was loaded once outside Lambda, and from then on the pipeline only processes new data.
- Data quality decisions: 77 observations without a date are kept in staging with a warning but excluded from the analytical model.
What I take away: a pipeline isn’t finished until it runs on its own in production, and knowing when not to use an advanced technique is part of the job.
Results
- ✅ In production since October 2026, running automatically every morning
- 18,454 observations, 583 taxa, 1,874 observers (from 1970 to today)
- 77 data quality tests on every run
- ~$20/month of infrastructure
- Full documentation: step-by-step deployment guide, operations runbook and validation results
Next steps and links
Next steps: SCD Type 2 history with dbt snapshots, dbt tests against Athena in CI (OIDC), continuous deployment, and time-of-day analysis based on the actual observation time.
🔗 Code and documentation on GitHub · LinkedIn post
💼 I’m open to Data Engineering / Analytics Engineering opportunities.
Get in touch
