General guidelines for performing the capstone project

Task 0 — Scope & KPIs (planning)

• ·       Pick a theme (mobility, tourism, retail, health, energy…).
• ·       Define 3–6 KPIs (Key Performance Indicators) you will compute (clear formulas).
• ·       List the providers (≥2) and releases (e.g., 2024-03 & 2024-06).

Deliverables

• ·       1-page brief (theme, KPIs, providers, releases, intended users).
• ·       Initial data dictionary (fields tied to KPIs).

Acceptance

• ·       KPIs are measurable and traceable to specific source fields.
• ·       At least two providers and two releases (or versioned snapshots).

Task 1 — Mini Data Lake (raw + standardised)

• Collect data (CSV/JSON/Parquet/API dumps) from the chosen providers.
• Lay out the lake (local folders / object storage):
• Catalogue (metadata)
  • dataset_id, provider, title, description, format, schema (fields/types), license, source_url, release_date, ingested_at, checksum, row_count, pii_flag, dq_checks.
• Exploration: A short notebook/script that lists datasets from the catalog, prints schema previews, row counts, date ranges, and a basic DQ profile (% nulls, duplicates).

Deliverables

• Lake folders (bronze/, silver/) + catalog.json + exploration report.

Acceptance

• Raw files preserved as-is in bronze/.
• silver/ contains standardised files (consistent headers, ISO 8601 dates, numeric types).
• A catalogue lets a user discover datasets by provider/release and view schema and counts.

Task 2 — Data Warehouse Design (star or snowflake)

• Conceptual model: identify fact table(s) and dimensions; state the grain explicitly.
• Logical model: relational tables, PK/FK, surrogate keys, constraints.
• Physical model: implement in PostgreSQL (preferred) or MySQL.

Deliverables

• Diagram (image/PDF).
• dw_schema.sql with CREATE TABLE, PK/FK, indexes.

Acceptance

• Clear, consistent grain for each fact.
• Dimensions modelled appropriately (star or limited snowflake).
• Referential integrity and surrogate keys defined.

Task 3 — Talend Pipelines (ODS → DW)

All loading and transformations must be implemented in Talend.

Architecture

• ODS (Operational Data Store)/Staging layer in the database for landing standardised silver/ files.
• DW layer (facts/dims) populated from ODS.

Build (at minimum) these Talend jobs:

1. Job 01 – Ingest to ODS (idempotent)
   1. Components: tFileInputDelimited / tFileInputJSON → tMap → tSchemaComplianceCheck (optional) → tPostgresqlOutput (or MySQL).
   • 1. Context variables: ctx.lake_path, ctx.release_date, ctx.db_url, ctx.db_user, ctx.db_password.
     • 1. Idempotency: use a business key or (source_file, row_hash) to avoid duplicates; consider tUniqRowbefore output.
       • Basic DQ: count nulls/invalids, send rejects to ods_rejects via tReplicate + tFilterRow.
2. Job 02 – Incremental Merge (from second release)
   1. Read the new release; compare with ODS/DW using keys and a hash of critical fields.
   1. Components: tMap + tHashOutput/tMD5 → tJoin → tPostgresqlOutput (action: update/insert).
   • Watermark: maintain last_loaded_release in a small control table; skip earlier files.
3. Job 03 – DW Build (facts & dims)
   1. Populate dimensions first (surrogate keys via sequences/identity).
   1. Populate fact tables with FK lookups (tMap + lookup model).
   • If you implement SCD2 on a dimension, use a slowly changing dimension pattern (valid_from/valid_to, is_current).
4. Job 04 – Orchestration
   1. One parent job calling 01 → 02 → 03 with proper onComponentOk triggers and error handling.
   1. Emit simple run logs (row counts in/out, rejects, duration) to a control table.

Deliverables

• Talend .item files (export your jobs) + a runbook describing context variables and job order.
• SQL views or scripts used by jobs (/sql/).

Acceptance

• Two releases processed; incremental job does not duplicate data.
• DQ rejections and basic metrics are logged.
• Re-running is safe (idempotent).

Task 4 — OLAP & Tableau Dashboard

• Create SQL views (or provide queries) in the DW for each KPI (rollups, window functions as needed).
• In Tableau:
  • Connect directly to your DW (PostgreSQL/MySQL).
  • Create a data source per mart or reuse a published one.
  • Prefer Extracts for performance; document refresh steps (manual is OK).
  • Build a dashboard with 5–7 tiles max: KPI cards, trend lines, breakdowns (by time, location, provider).

Must-haves in Tableau

• Filters (date/provider/location) that match your dimensions.
• A “Data Info” caption: source, last refresh date/time, caveats.
• Each KPI worksheet references a SQL view or a clearly documented query.

Deliverables

• olap_views.sql (or olap_queries.sql) used by Tableau.
• Tableau workbook (.twbx) or clear screenshots with captions if using Public.
• Short notes on refresh routine (how to update extract).

Acceptance

• Numbers match the DW queries (reproducible from SQL).
• Filters behave correctly; drill-downs are consistent with the dimensional keys.
• Dashboard is readable, minimal chartjunk, and includes freshness info.

Task 5 — Documentation & Handover

• README.md: architecture diagram; tools/versions; how to run Talend jobs (contexts); load sequence; how to open Tableau; known issues.
• Data dictionary: fact/dim fields, definitions, calculation notes, lineage.
• Ethics & License note: data licenses, PII minimisation, fairness caveats.

Deliverables

• README, data dictionary, diagram.
• Zip/repo with: lake/ structure, catalog.json, Talend exports, SQL, Tableau workbook (or screenshots).

Acceptance

• A new user can follow the README to reproduce your KPIs end-to-end.
•