Assignment: Build a Scalable Data Pipeline
(Python + ClickHouse + SQL)
Design and implement a real‑world data pipeline using Python, ClickHouse, and SQL. The pipeline should
efficiently ingest, process, store, and analyze data from multiple sources while following best practices for
performance, reliability, observability, and maintainability. Treat this as a small, production‑like system.
Goal
• Deliver an end‑to‑end pipeline that captures real‑time and historical signals relevant to
cross‑border ice‑cream truck rentals in Europe, with curated models enabling ROI analysis and
routing decisions.
• Demonstrate solid engineering practices: idempotency, incremental loads, schema design, data
quality checks, monitoring, and documentation.
Business Scenario
A client plans to rent out ice‑cream trucks across Europe and needs to answer:
1. Weather → Sales Connection: How strongly do weather conditions correlate with ice‑cream sales,
and where is this relationship strongest?
2. Best Country for Main Location: Which country should host the main truck hub?
3. Revenue vs. Rent: Assume a truck should be rented in months where revenue ≥ 3× the monthly
rent.
4. ROI Analysis: Use each country’s average ice‑cream price; where unavailable, approximate using the
Big Mac Index to infer purchasing‑power‑adjusted pricing. Factor in fuel usage of 1 liter per 10 km
and assume no costs beyond diesel for cross‑country transport.
5. Optimal Country Pattern: Propose the most profitable country sequence/pattern for truck
deployment over time.
Trucks are Mercedes Sprinter (2017). Use average rental prices × 1.5 as the monthly rent baseline by
country. Track consistency/volatility of fuel prices, Big Mac Index, weather, and sales; higher volatility
reduces reliability and affects point (3).
What You Will Be Doing
1) Pull Data from Multiple Sources
• Live API (Weather): Use OpenWeatherMap — [Link] — to fetch current
and historical/forecast weather for relevant European locations.
1
• Capture temperature, humidity, precipitation, wind, and weather codes at hourly or 3‑hourly grain.
• Respect rate limits and record provider metadata (endpoint, response timestamps, status).
• Sales Data (Relational or Files): Provide mock ice‑cream sales per country/city at daily or
transactional grain. You may supply these as CSV/JSON files or a lightweight relational source you
control. Ensure keys and timestamps support incremental loads and late‑arrivals.
• Reference Files (Object Storage or Local Folder): Supply supplemental inputs such as Big Mac
Index, diesel prices by country, truck rental price by country, and country/city geographies
(ISO codes, lat/lon). Store as CSV/JSON/Parquet.
2) Process, Transform & Clean the Data
• Use Python for extraction and loading and ClickHouse SQL for transformations.
• Create staging (raw/landed) and curated (analytics‑ready) layers.
• Staging: Raw ingestions, minimal casting, full payload retention where sensible.
• Curated: Typed, deduplicated, unit‑normalized tables with business logic applied.
• Modeling guidelines (suggested):
• Dimensions: dim_country , dim_city , dim_route (truck path), dim_price_index ,
dim_fuel , dim_rent .
• Facts: fct_weather_hourly / fct_weather_daily , fct_sales_daily (or transactional),
fct_costs (fuel + rent), fct_roi , and (optional) fct_route_perf .
• Normalize units to SI; store time in UTC with derived local time for analytics.
• Data quality & error handling:
• Enforce basic contracts (non‑null keys, valid ranges); quarantine bad records in an error table with
reason and source file/reference.
• Track idempotency (stable keys) and incremental windows; support late arrivals.
3) Store the Processed Data Efficiently (ClickHouse)
• Use ClickHouse as the serving warehouse for both staging and curated layers.
• Apply appropriate engines (e.g., MergeTree family) with:
• Partitioning by month/day on event timestamps.
• ORDER BY keys aligned to query patterns (e.g., date, country, location).
• Optional materialized views for pre‑aggregations (hourly/daily metrics) to speed BI.
• Keep raw landed files in a structured folder/object layout (e.g., provider=.../country=.../
dt=YYYY‑MM‑DD/ ).
4) Build a Simple Analytics Dashboard
• Create a lightweight BI report connected to ClickHouse (e.g., Power BI, Metabase, or Apache
Superset).
• Must include at minimum:
• Weather trends over time by country/city.
• Sales performance by region and time.
• ROI (revenue − costs) vs. monthly rent with the 3× rent rule highlight.
• Suggested country pattern for truck movement and a view of parameter volatility (stability vs.
fluctuation).
2
5) Set Up Monitoring & Alerts
• Provide operational visibility for your pipeline:
• Structured logs from Python steps (ingest, transform, load) with run ids, counts, error summaries.
• Simple health checks (e.g., latest data timestamp per table) and row‑count deltas.
• Alerts to a channel of your choice (e.g., email/Slack) on failures or staleness thresholds.
What You Need to Submit
1. Architecture Diagram (Visio/PowerPoint/Excalidraw, etc.)
2. Show sources (OpenWeatherMap, sales, reference files), processing (Python), storage (ClickHouse
staging/curated), scheduling (e.g., cron or your choice), and BI.
3. Your Work Artifacts
4. Configuration and ClickHouse SQL (DDL for tables, transformations, and any views/materialized
views).
5. Any configuration files or manifests needed to reproduce the pipeline.
6. Analytics Dashboard
7. Power BI ( .pbix ) or exported definition from Metabase/Superset, with screenshots of key views.
8. Short Write‑Up
9. Tooling choices and trade‑offs.
10. Performance optimizations (partitioning, ORDER BY, pre‑aggregations, concurrency).
11. Error handling & data quality (contracts, quarantine strategy, retry logic, late arrivals).
12. (Bonus Points)
13. Incremental loading with change detection.
14. Country routing optimization logic (e.g., compare multiple patterns and justify selection).
15. Basic volatility scoring for fuel/weather/indexes and its impact on ROI.
How You’ll Be Graded
• Does it work? Pipeline runs smoothly end‑to‑end (30%).
• Is it fast & efficient? Good partitioning, ORDER BY, aggregations, and query latency (20%).
• Error handling & monitoring: Clear logs, staleness checks, alerts, and recoverability (20%).
• Scalable & maintainable: Sound schema design, modular structure, sensible configs (15%).
• Clear documentation: Easy to follow, with diagrams and rationale (15%).
Timeframe
[TBD] 7 - 8 Hours .
3
Tools You Will Use
• Data Orchestration: Python (baseline) with your choice of simple scheduler (cron/systemd).
Optional: Airflow if you prefer.
• Processing: Python for extraction/loading; ClickHouse SQL for transformations and modeling.
• Storage: ClickHouse (staging + curated). Raw files in a clearly structured local folder or object store.
• Analytics: Power BI, Metabase, or Apache Superset.
• Monitoring: Logs and health checks; optional metrics via a tool of your choice.
• CI/CD (Optional): GitHub Actions or similar for linting/tests and repeatable runs.
Constraints & Guidelines
• Use OpenWeatherMap as the weather source: [Link]
• Favor UTC for timestamps; derive local time only for presentation.
• Normalize units/currencies; document any assumptions for missing values.
• Design for idempotency and incremental operation; clearly explain your keys and windows.
• Keep secrets (API keys) out of the repo; document how to provide them securely at runtime.
• Ensure the pipeline is reproducible on a reviewer’s machine with clear steps.