Stop Copy-Pasting Exchange Rates: A Small Data Pipeline That Pays for Itself

Some automation projects are impressive.

Others are valuable because they eliminate a tiny task that happens every single day.

Currency conversion is a good example.

If a business operates across multiple markets, sooner or later someone needs a consistent exchange-rate table.

Without one, analysts start doing things like:

• copying rates into spreadsheets;
• using different conversion dates;
• converting in separate dashboards;
• hard-coding values into SQL;
• re-running historical calculations with today’s rate.

The technical problem is small.

The analytical consequences are not.

A simple automated exchange-rate pipeline can remove an entire category of avoidable inconsistency.

The business problem

Imagine revenue comes from several currencies.

A dashboard needs everything in USD.

A naive approach is:

local revenue × current exchange rate = USD revenue

That works until someone asks for last month.

If you use today’s exchange rate for historical revenue, the historical dashboard can change even when the underlying transactions did not.

That makes trend analysis confusing.

A better model stores a dated conversion rate.

date | from_currency | to_currency | rate

Then every transaction can join to the appropriate rate for its date.

This creates reproducibility.

The report you ran yesterday and the report you run tomorrow use the same historical rate table.

A lightweight architecture

The pipeline does not need to be complicated.

One practical pattern is:

Google Finance / spreadsheet
        ↓
Scheduled extraction
        ↓
Validation
        ↓
Warehouse table
        ↓
Analytics models
        ↓
Dashboards

The spreadsheet acts as a convenient source layer.

The warehouse becomes the durable analytical source of truth.

That distinction matters.

A live spreadsheet formula is useful for retrieval, but it should not be the only historical storage if downstream reporting depends on it.

What the rate table should contain

At minimum:

Depending on the business, you may also want:

• market/country;
• rate type;
• inverse rate;
• source timestamp;
• validation status.

The goal is not to maximize columns.

It is to make the table auditable.

Idempotency matters even for tiny pipelines

Scheduled jobs fail.

They also get rerun.

If a daily job inserts the same exchange-rate rows every time it is retried, you now have duplicate dates.

A simple unique key can prevent this:

(rate_date, base_currency, quote_currency)

Then the ingestion can use an upsert/merge pattern.

Conceptually:

MERGE target t
USING incoming s
ON  t.rate_date = s.rate_date
AND t.base_currency = s.base_currency
AND t.quote_currency = s.quote_currency
WHEN MATCHED THEN UPDATE SET
  rate = s.rate,
  loaded_at = s.loaded_at
WHEN NOT MATCHED THEN INSERT (...);

Now rerunning the job is safe.

That property is more important than the specific scheduler.

Validate before trusting

External values can fail in several ways.

A formula may return blank.

A source may be temporarily unavailable.

A parsing change may turn a numeric value into text.

A rate may be mathematically possible but obviously suspicious.

Useful checks include:

• rate is not null;
• rate is greater than zero;
• expected currencies are present;
• date is correct;
• day-over-day change is within a reasonable alert threshold.

The last check should not necessarily block the pipeline.

Currencies can move sharply.

But an unusual movement can trigger a warning for manual review.

Weekends and missing dates

Another subtle question:

What happens on days when the market/source does not publish a new value?

There are several legitimate choices:

1. store only published dates;
2. forward-fill the most recent rate;
3. join transactions to the latest available rate on or before the transaction date.

Which one is correct depends on the reporting requirement.

The important part is to define the policy once.

Otherwise each dashboard solves the same problem differently.

Conversion direction can create mistakes

If the source provides:

1 USD = 120 BDT

and the model expects:

1 BDT = ? USD

then the inverse is required.

That sounds obvious, but it is one of the easiest ways to create silent errors.

A robust pipeline should name fields clearly:

• base_currency
• quote_currency
• rate

and document the meaning:

1 unit of base currency equals X units of quote currency.

That sentence prevents a surprising amount of confusion.

Centralize conversion logic

The biggest benefit arrives downstream.

Without a shared table:

Dashboard A → own conversion
Dashboard B → own conversion
Notebook C  → own conversion
Finance file → own conversion

With a shared rate model:

                 ┌→ Dashboard A
Rate table → model ├→ Dashboard B
                 ├→ Notebook C
                 └→ Finance analysis

One definition.

One historical record.

One place to fix problems.

This is a small example of a larger analytics principle:

Repeated business logic should become shared data infrastructure.

Scheduling options

The specific scheduler is less important than reliability.

Possible choices include:

• GitHub Actions;
• cloud schedulers;
• warehouse-native scheduled queries;
• Airflow/Composer;
• serverless functions;
• a lightweight cron job.

For a simple daily rate table, the best solution is often the least complicated one that provides:

• scheduling;
• logs;
• retries;
• credentials/security;
• failure visibility.

A five-line cron that nobody monitors is not necessarily simpler than a managed scheduler.

Cost is usually negligible

This type of dataset is tiny.

A few currencies × 365 days produces almost nothing compared with normal warehouse volumes.

So optimization should focus on reliability and maintainability, not storage cost.

That is another useful engineering habit:

do not solve an imaginary scale problem while leaving the real operational problem unsolved.

How I would expose it to analysts

The final table should be boring.

That is a compliment.

An analyst should be able to write:

SELECT
  o.order_date,
  o.country,
  o.revenue_local,
  fx.rate,
  o.revenue_local * fx.rate AS revenue_usd
FROM orders o
LEFT JOIN fx_daily fx
  ON o.order_date = fx.rate_date
 AND o.currency = fx.base_currency
 AND fx.quote_currency = 'USD'

No web scraping in the dashboard.

No manually updated CTE.

No mysterious constant.

If the conversion logic is boring, the pipeline has done its job.

The bigger lesson

Automation does not have to be large to matter.

If a task is:

• repeated;
• deterministic;
• easy to validate;
• used by multiple analyses;

it is an excellent candidate for a small data pipeline.

The return is not only time saved.

It is consistency.

Everyone stops arguing about which exchange rate was used because the rate becomes a shared, dated, inspectable data asset.

And that is exactly what good analytics infrastructure should do:

remove avoidable ambiguity before it reaches the dashboard.