Challenges/Arrivia/Data Engineer/Voyagr Travel Club: Explain and Improve the Points Pipeline

Voyagr Travel Club: Explain and Improve the Points Pipeline

Microsoft Fabric
PySpark
Spark SQL
Incremental Load
Streaming
Lakehouse
Loyalty Data
Reconciliation
Data Engineering
AI-Assisted Development
Estimated Time:
30 minutes
Difficulty:Intermediate
Status:Not started
Start this challenge

Create a free account to upload your work. Your progress saves as a draft until you submit.

What You'll Be Doing

The Challenge

Voyagr Travel Club is a fictional membership travel company. Members earn and redeem airline miles with about 40 partner airlines, and Finance closes each month on the club's outstanding miles liability.

You just joined the data team as a Fabric Data Engineer II. The diagram below is the miles pipeline as it runs today. The engineer who built it has left, and you now own it.

Your manager's ask:

"Walk me through how this works and what's wrong with it. Then tell me the three things you'd change first, in order, and why. Then tell me how you'd use AI to get it done faster."

No special tools are required. Any text editor works, and running code is optional. Plan on about 3 minutes to read, 7 minutes working with AI, and 20 minutes to plan and record your video.

The pipeline today

voyagr-points-pipeline-diagram.svg

Facts

  • Each partner drops one file a night in its own format (CSV, fixed-width, or JSON). Partners own their files and will not change formats or reissue behavior.
  • About 2 million rows land per night. The ingest job also copies the whole internal ledger every night, about 300 million rows. Your team does not control the ledger source: no CDC, triggers, or schema changes.
  • A file can arrive up to 48 hours late. When a partner corrects an error, it reissues the whole day's file under the same file date.
  • Each row is an EARN, REDEEM, or REVERSAL. Redemptions and reversals arrive as negative miles. txn_id is unique only within a partner.
  • Miles expire 24 months after they are earned.
  • Member operations wants the app balance to reflect a redemption within 5 minutes. Redemptions are also confirmed later in the partner files.
  • Member email and loyalty number are PII. Reporting analysts must not see them.
  • The pipeline runs at 02:00 and reports refresh at 06:00. The reporting team shares the same small capacity.

Sample rows

Raw rows landed so far. On 2026-11-03, partner P7 reissued its 2026-11-01 file with a correction and one reversal.

received_datepartner_codefile_datetxn_idtxn_typemember_idmiles
2026-11-02P72026-11-01T1EARNM-1005000
2026-11-02P72026-11-01T2EARNM-1013000
2026-11-02P72026-11-01T3REDEEMM-100-2000
2026-11-03P72026-11-01T1EARNM-1005000
2026-11-03P72026-11-01T2EARNM-1013500
2026-11-03P72026-11-01T3REDEEMM-100-2000
2026-11-03P72026-11-01T4REVERSALM-100-1000
2026-11-03P92026-11-01T1EARNM-1024000

The partner statements for file date 2026-11-01 give net miles of 5,500 for P7 and 4,000 for P9.

AI Usage Guidance

We expect you to use AI tools. We evaluate how you use them — not whether you use them. Evidence of iteration, redirection, and critical evaluation scores higher than a polished output with no process documentation.

The single highest-signal indicator: your video answer to the mandatory AI question. If you cannot name a specific moment where you redirected AI output, evaluators will assume you did not.

Mandatory AI question for your video: Walk me through one moment where you disagreed with, pushed back on, or redirected what the AI gave you — and what you did instead. Name the specific moment. Explain what the AI produced that didn’t meet the bar, what you did differently, and why.

Communication clarity matters for this role. We assess structure, stakeholder awareness, and ability to simplify complex ideas — not presentation style or accent.

Submission: Upload each deliverable as a separate file directly on the Provn platform.

Submission

Upload each deliverable as a separate file on the Provn platform: your optional working notes, your AI transcript, and your video (MP4 or MOV).

What You'll Accomplish

Explain a pipeline you did not build and find what is wrong with it.

Keep a load correct when files are reissued, arrive late, or carry reversals.

Rank what blocks a number Finance relies on ahead of what only costs money.

Decide where streaming is worth its cost, protect PII, and show how you would prove the numbers.

Direct, check, and correct AI tools, and verify before you trust the output.

How Your Work Will Be Scored

Incremental & Streaming Pipeline Design: Loads stay correct when files are reissued, arrive late, or carry reversals, without full reloads, and the streaming call fits who uses the data.Spark & SQL Performance and Capacity Cost: You find what is wrong or wasteful in the pipeline, rank what blocks Finance's number, and explain the cost effect of your fixes.Lakehouse Design & Production Readiness: Layers have clear jobs, redundant copies are gone, PII is protected, and you define how the numbers get proven to Finance.Design Ownership & Stakeholder Communication: You explain the pipeline clearly, recommend a ranked direction, say what you would not do, and defend it with specifics.AI Fluency: You direct AI beyond the pasted prompt, catch what it gets wrong, and verify before you keep it, scored mainly from your transcript.
Before you submit: challenge best practices →

What to Submit

Working Notes

DocumentOptional

Format: .pdf, .doc, .docx, .rtf, .txt, .md

Code that turns the sample rows into the correct current rows and checks each partner's net miles against its statement, in PySpark or Spark SQL, with one line on how you checked it. Skip it if you would rather explain your approach on camera.

Sign in to upload files

AI Transcript and Log

DocumentRequired

Format: .pdf, .doc, .docx, .rtf, .txt, .md

Your full AI conversation(s) for this challenge, with anything personal removed. If you used no AI, say so here instead. Start the file with a short AI Usage Log: three entries of one or two lines each. What you asked the AI to do, what you kept, changed, or rejected, and why. Point to the turn in the transcript.

Sign in to upload files

Video

Video · 5 to 7 minutesRequired

Format: .mp4, .mov, .webm

(1) The pipeline in plain language (about 1.5 minutes): what each stage does, who depends on it, and where you would worry first. (2) The sample rows (about 1.5 minutes): what a nightly load that simply adds each file to the table would show Finance, what Finance should see, and how you get there. (3) Your top three improvements (about 2 minutes): in order, with your reason. Name one thing you would deliberately not do. Say how you would prove each change worked, and which number the member app trusts and which Finance trusts when they disagree. (4) AI (about 1.5 to 2 minutes): how you would use AI to get your improvements done faster (one task, the context you would give it, how you would check its output), then the mandatory AI question, including where it sits in your transcript.

Sign in to upload files