Building IDR FX Advisor: An Automated FX Signal Pipeline with Airflow, dbt, and BigQuery

The Problem

If you're Indonesian and saving or investing in foreign currency, timing your IDR-to-foreign-currency exchanges can make a meaningful difference. But manually tracking exchange rates every day is tedious, and it's hard to tell whether today's rate is genuinely good or bad relative to recent history.

That's where IDR FX Advisor comes in — a fully automated ELT pipeline that sends a daily Discord alert telling you whether today is a good day to buy.

The Architecture

The entire pipeline runs on the modern data stack:

Frankfurter API → Airflow → BigQuery → dbt → Discord alert

Here's how each piece fits together:

1. Data Ingestion (Airflow)

Every day, an Airflow DAG (running on CeleryExecutor with Redis) fetches the latest IDR exchange rates from the Frankfurter API. It loads the raw data directly into a BigQuery table — no transformation at this stage, just raw ingestion.

2. Warehousing (BigQuery)

BigQuery stores all historical daily rates, backfilled from January 2021 to provide a solid baseline for statistical analysis. Using serverless BigQuery means zero infrastructure to manage for storage and querying.

3. Transformation (dbt)

This is where the magic happens. A dbt model computes three key metrics for each currency:

• Percentile rank — where does today's rate sit relative to its entire history?
• Multi-window z-scores — z-scores over 30, 90, and 365-day rolling windows
• Final signal — a 5-level recommendation mapped from the combination of percentile rank and 30-day z-score

The signal logic:

The z-score approach is key here — it detects whether today's rate is statistically unusual compared to recent history, regardless of IDR's long-term weakening trend. Combined with percentile rank against the full history, it gives a robust signal that works across different market conditions.

4. Alerting (Airflow → Discord)

The final Airflow task reads the dbt mart and sends a formatted Discord message with the signal, making it dead simple to check every morning.

Infrastructure as Code

The entire cloud infrastructure is defined with Terraform:

• BigQuery dataset and tables
• GCP service accounts and IAM permissions
• Terraform backend state management

Running terraform apply provisions everything. Running docker compose up starts the local Airflow + dbt environment. No clicking around in cloud consoles.

Why This Stack?

What I Learned

Building this project reinforced several important data engineering principles:

1. Start with a real problem — I wanted this tool myself. Solving a genuine need keeps motivation high and leads to a better product.
2. Statistical methods beat heuristics — Instead of arbitrary thresholds, using z-scores and percentile rank gives mathematically sound signals that adapt to market conditions.
3. Infrastructure as code pays for itself — Being able to terraform destroy and rebuild from scratch gives confidence that everything is documented and reproducible.
4. Keep it simple — The pipeline is surprisingly straightforward: fetch → store → transform → alert. Each piece does one thing well.

Try It Yourself

The project is fully open source on GitHub: github.com/rndrpp/idr-fx-advisor

To run it locally, you need:

• A GCP project with BigQuery enabled
• Docker and Docker Compose
• Terraform (for infrastructure provisioning)
• A Discord webhook URL

The README has detailed setup instructions. If you're Indonesian and investing in foreign currency, give it a try — and if you find it useful, I'd love to hear about it!

Built with Python, Apache Airflow (CeleryExecutor), dbt, BigQuery, Terraform, and Docker.