PraxisAI
Sign in
Data pipelinemedium

Bike-share trips ETL pipeline

Generate messy trip exports, clean them with documented rules, and load an idempotent SQLite warehouse with a daily summary.

Suggested effort
~4h focused work
Window
24 hours
Starts from
An empty repo
Stack
Your choice
Sign in to start this build →

Opens in a new tab. The clock starts when you press Start inside the environment, not before, and your workspace is kept while you step away.

Time window 24 hours. Suggested effort about 4 hours.

Context

A city bike-share scheme exports a CSV of trips every day. The export is messy: mixed date formats, duplicated rows, missing station names, trips with negative duration. Analysts want a clean, queryable table and a daily summary.

Core requirements

  • Write a seeded generator script that produces data/raw/trips_*.csv (at least 3 days, 5,000+ rows in total) with realistic mess: duplicates, two date formats, blank fields, out-of-range durations, stray whitespace. Columns: trip_id, started_at, ended_at, start_station_id, start_station_name, end_station_id, end_station_name, bike_type, member_type.
  • Extract: read every raw file. Transform: normalise timestamps to UTC ISO 8601, trim strings, drop exact duplicates, fill station names from other rows with the same id, compute duration_minutes.
  • Reject rows that cannot be fixed (end before start, duration over 24 hours, missing station id) into a rejects table or file with a reason per row.
  • Load into SQLite (warehouse.db): trips, stations, rejects, and a daily_summary view (trips, median duration, top 5 start stations, member share per day).
  • Idempotent: running the pipeline twice on the same input produces the same database, with no duplicated rows.

Acceptance criteria

  • One command runs the whole pipeline and prints a run report (rows read, loaded, rejected by reason).
  • Transform functions are pure and unit tested, including each kind of mess the generator creates.
  • An end-to-end test runs the pipeline on a small fixture and asserts the counts.
  • The README documents the schema and every cleaning rule.

Stretch goals (optional)

  • Incremental loads: only process files not seen before (track a manifest).
  • Data-quality assertions that fail the run when the reject rate exceeds a threshold.
  • Parquet output alongside SQLite.

Constraints

  • Any language. Suggested: Python (standard library, pandas or polars), or Node.
  • No external services; all data is generated locally.

Deliverables (every project)

  • Source code committed in this repository (the grader diffs against the first commit).
  • README.md that replaces the stub, with: how to install, run and test it (copy-pasteable commands); the decisions and trade-offs you made; what you would do next with more time; and a short note on how you used the AI agent (what you delegated, what you checked or rewrote).
  • Automated tests that run with a single command (npm test, pytest, go test ./... or cargo test).
  • No secrets in the repository. Anything configurable reads from environment variables with safe defaults.

Ground rules

  • The 24-hour clock is a window, not a workload. Stop at roughly the suggested effort, then write down what you would do next. A small, finished, tested core beats a large unfinished one.
  • Use the AI agent as much or as little as you like: every prompt is recorded and the report shows how it was used. You are judged on the result and on whether you understood and verified what the agent produced.
  • The work is yours. PraxisAI uses it only to produce your assessment report.