One Bill Across Three Clouds

A medium Pipeline Design interview practice problem on DataDriven. Design the pipeline on the canvas: sources, transforms, storage and checks, then get feedback on it.

Domain

Pipeline Design

Difficulty

medium

Seniority

L5

Problem

For thousands of enterprise customers, AWS sends hourly CSV cost reports, Azure a daily JSON export, and GCP a near-real-time stream, each in its own schema. How would you design a pipeline that unifies these into a consistent cost analytics layer and alerts our account managers when any managed account is trending toward a budget breach?

AWS, Azure, GCP. Three bills. One truth.

Business requirements

• Customers want one view of cloud spend across providers; today they get three separate bills with three formats they have to reconcile themselves.
• Cloud providers re-deliver billing files with corrections under the same path; the raw payloads have to be retained so the unified view can be rebuilt from them.
• Customers don't want to discover they breached their budget after the month closes; the alert has to come early enough to act on.

Worked solution and explanation

Normalize early, project daily

This is a schema-reconciliation problem dressed up as a billing dashboard. Anyone can land three feeds and draw a chart. What separates candidates is where the three meanings of 'cost' get merged: before query time, in one canonical record, or never. Union three provider tables in BI and the total will not match the invoices. Append a re-delivered corrected file and that period's line items double. Check budgets at month close and the customer learns about the breach from the bill.

Walk the requirements

Step 1: Land every provider raw before touching it

All three feeds, the hourly CSVs, the daily JSON export and the streaming feed, write unchanged into one retained lake. That is what lets you rebuild the unified view after a mapping bug or a provider correction, and the streaming feed gets the same replay story as the files.

Step 2: Normalize once into one canonical record

A single Spark job reads the lake and maps each provider's columns into one schema: account, resource, period, original amount, original currency, unified amount, FX rate used. 'Total spend across clouds' becomes one sum that reconciles, because the differences in what each cost column means were resolved here and not in a dashboard.

Step 3: Overwrite the period on load

The write into the warehouse replaces the partition keyed on provider, account and period. Re-running the same file is harmless and a corrected file swaps in atomically, so the load is idempotent and corrections never leave duplicates.

Step 4: Project against the budget every day

A dbt model reads canonical spend, computes month-to-date and extrapolates the run rate to month end. When the projection crosses the account's budget, an alert goes out with days left to act. It sits on its own branch, separate from the dashboard, so reporting stays a read path and detection stays a job.

Union in BI

Three tables with columns that only look alike. The total drifts from the invoices, every new provider means new dashboard logic, and a correction needs a manual cleanup.

Canonical first

One schema with original and unified amounts on every row. The dashboard and the projection read the same numbers, and a new provider only adds a mapping inside the normalizer.

Common follow-up questions

• A fourth provider is added with its own schema. What changes and what stays put? (Tests whether the candidate sees the normalizer mapping as the only extension point, with the warehouse, projection and dashboard untouched.)
• An account's billing currency changes mid-month. What does the canonical record store and what does the projection sum? (Tests original amount, original currency and FX rate per row, with the projection summing the unified amount.)