{"id":71,"date":"2025-10-29T11:03:41","date_gmt":"2025-10-29T11:03:41","guid":{"rendered":"http:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/?page_id=71"},"modified":"2025-10-29T11:03:41","modified_gmt":"2025-10-29T11:03:41","slug":"capstone-roadmap","status":"publish","type":"page","link":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/capstone-roadmap\/","title":{"rendered":"Capstone Roadmap"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">General guidelines for performing the capstone project<\/h2>\n\n\n\n<h4 class=\"wp-block-heading\">Task 0 \u2014 Scope &amp; KPIs (planning)<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u00b7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Pick a theme (mobility, tourism, retail, health, energy\u2026).<\/li>\n\n\n\n<li>\u00b7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Define&nbsp;<strong>3\u20136 KPIs<\/strong>&nbsp;(Key Performance Indicators) you will compute (clear formulas).<\/li>\n\n\n\n<li>\u00b7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;List the&nbsp;<strong>providers<\/strong>&nbsp;(\u22652) and&nbsp;<strong>releases<\/strong>&nbsp;(e.g., 2024-03 &amp; 2024-06).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Deliverables<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u00b7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1-page brief (theme, KPIs, providers, releases, intended users).<\/li>\n\n\n\n<li>\u00b7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Initial&nbsp;<strong>data dictionary<\/strong>&nbsp;(fields tied to KPIs).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Acceptance<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>\u00b7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;KPIs are measurable and traceable to specific source fields.<\/li>\n\n\n\n<li>\u00b7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;At least two providers and two releases (or versioned snapshots).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Task 1 \u2014 Mini Data Lake (raw + standardised)<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Collect data<\/strong>\u00a0(CSV\/JSON\/Parquet\/API dumps) from the chosen providers.<\/li>\n\n\n\n<li><strong>Lay out the lake<\/strong>\u00a0(local folders \/ object storage):<img loading=\"lazy\" decoding=\"async\" width=\"454\" height=\"197\" src=\"blob:http:\/\/vargas-solar.com\/d9c71c83-b3f7-442e-b32c-59274800b8b9\" alt=\"Une image contenant texte, re\u00e7u, Police, capture d\u2019\u00e9cran\n\nLe contenu g\u00e9n\u00e9r\u00e9 par l\u2019IA peut \u00eatre incorrect.\"><\/li>\n\n\n\n<li><strong>Catalogue (metadata)<\/strong>\n<ul class=\"wp-block-list\">\n<li>dataset_id, provider, title, description, format, schema (fields\/types), license, source_url, release_date, ingested_at, checksum, row_count, pii_flag, dq_checks.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Exploration:\u00a0<\/strong>A short\u00a0<strong>notebook\/script<\/strong>\u00a0that lists datasets from the catalog, prints schema previews, row counts, date ranges, and a basic DQ profile (% nulls, duplicates).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Deliverables<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Lake folders (bronze\/, silver\/)\u00a0+\u00a0catalog.json\u00a0+ exploration report.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Acceptance<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Raw files preserved\u00a0<strong>as-is<\/strong>\u00a0in\u00a0bronze\/.<\/li>\n\n\n\n<li>silver\/\u00a0contains standardised files (consistent headers, ISO 8601 dates, numeric types).<\/li>\n\n\n\n<li>A catalogue lets a user discover datasets by provider\/release and view schema and counts.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Task 2 \u2014 Data Warehouse Design (star or snowflake)<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Conceptual model<\/strong>: identify\u00a0<strong>fact<\/strong>\u00a0table(s) and\u00a0<strong>dimensions<\/strong>; state the\u00a0<strong>grain<\/strong>\u00a0explicitly.<\/li>\n\n\n\n<li><strong>Logical model<\/strong>: relational tables, PK\/FK, surrogate keys, constraints.<\/li>\n\n\n\n<li><strong>Physical model<\/strong>: implement in\u00a0<strong>PostgreSQL<\/strong>\u00a0(preferred) or\u00a0<strong>MySQL<\/strong>.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Deliverables<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Diagram (image\/PDF).<\/li>\n\n\n\n<li><code>dw_schema.sql<\/code>\u00a0with\u00a0<code>CREATE TABLE<\/code>, PK\/FK, indexes.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Acceptance<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Clear, consistent grain for each fact.<\/li>\n\n\n\n<li>Dimensions modelled appropriately (star or limited snowflake).<\/li>\n\n\n\n<li>Referential integrity and surrogate keys defined.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Task 3 \u2014 Talend Pipelines (ODS \u2192 DW)<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">All loading and transformations must be implemented in&nbsp;<strong>Talend<\/strong>.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Architecture<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>ODS (Operational Data Store)\/Staging layer<\/strong>\u00a0in the database for landing standardised\u00a0silver<code>\/<\/code>\u00a0files.<\/li>\n\n\n\n<li><strong>DW layer<\/strong>\u00a0(facts\/dims) populated from ODS.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Build (at minimum) these Talend jobs:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Job 01 \u2013 Ingest to ODS (idempotent)<\/strong><ol><li>Components:\u00a0<code>tFileInputDelimited<\/code>\u00a0\/\u00a0<code>tFileInputJSON<\/code>\u00a0\u2192\u00a0<code>tMap<\/code>\u00a0\u2192\u00a0<code>tSchemaComplianceCheck<\/code>\u00a0(optional) \u2192\u00a0<code>tPostgresqlOutput<\/code>\u00a0(or MySQL).<\/li><\/ol>\n<ul class=\"wp-block-list\">\n<li><ol><li><strong>Context variables<\/strong>:\u00a0<code>ctx.lake_path<\/code>,\u00a0<code>ctx.release_date<\/code>,\u00a0<code>ctx.db_url<\/code>,\u00a0<code>ctx.db_user<\/code>,\u00a0<code>ctx.db_password<\/code>.<\/li><\/ol>\n<ul class=\"wp-block-list\">\n<li><ol><li><strong>Idempotency<\/strong>: use a\u00a0<strong>business key<\/strong>\u00a0or\u00a0<code>(source_file, row_hash)<\/code>\u00a0to avoid duplicates; consider\u00a0<code>tUniqRow<\/code>before output.<\/li><\/ol>\n<ul class=\"wp-block-list\">\n<li><strong>Basic DQ<\/strong>: count nulls\/invalids, send rejects to\u00a0<code>ods_rejects<\/code>\u00a0via\u00a0<code>tReplicate<\/code>\u00a0+\u00a0<code>tFilterRow<\/code>.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Job 02 \u2013 Incremental Merge (from second release)<\/strong><ol><li>Read the new release;\u00a0<strong>compare<\/strong>\u00a0with ODS\/DW using keys and a\u00a0<strong>hash of critical fields<\/strong>.<\/li><\/ol><ol><li>Components:\u00a0<code>tMap<\/code>\u00a0+\u00a0<code>tHashOutput<\/code>\/<code>tMD5<\/code>\u00a0\u2192\u00a0<code>tJoin<\/code>\u00a0\u2192\u00a0<code>tPostgresqlOutput<\/code>\u00a0(action: update\/insert).<\/li><\/ol>\n<ul class=\"wp-block-list\">\n<li><strong>Watermark<\/strong>: maintain\u00a0<code>last_loaded_release<\/code>\u00a0in a small control table; skip earlier files.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Job 03 \u2013 DW Build (facts &amp; dims)<\/strong><ol><li>Populate\u00a0<strong>dimensions<\/strong>\u00a0first (surrogate keys via sequences\/identity).<\/li><\/ol><ol><li>Populate\u00a0<strong>fact<\/strong>\u00a0tables with FK lookups (<code>tMap<\/code>\u00a0+ lookup model).<\/li><\/ol>\n<ul class=\"wp-block-list\">\n<li>If you implement\u00a0<strong>SCD2<\/strong>\u00a0on a dimension, use a slowly changing dimension pattern (valid_from\/valid_to, is_current).<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Job 04 \u2013 Orchestration<\/strong><ol><li>One parent job calling 01 \u2192 02 \u2192 03 with proper\u00a0<strong>onComponentOk<\/strong>\u00a0triggers and error handling.<\/li><\/ol>\n<ol class=\"wp-block-list\">\n<li>Emit simple\u00a0<strong>run logs<\/strong>\u00a0(row counts in\/out, rejects, duration) to a control table.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Deliverables<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Talend\u00a0<strong>.item<\/strong>\u00a0files (export your jobs) + a\u00a0<strong>runbook<\/strong>\u00a0describing context variables and job order.<\/li>\n\n\n\n<li>SQL views or scripts used by jobs (<code>\/sql\/<\/code>).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Acceptance<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Two releases processed;\u00a0<strong>incremental<\/strong>\u00a0job does\u00a0<strong>not<\/strong>\u00a0duplicate data.<\/li>\n\n\n\n<li>DQ rejections and basic metrics are logged.<\/li>\n\n\n\n<li>Re-running is\u00a0<strong>safe<\/strong>\u00a0(idempotent).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Task 4 \u2014 OLAP &amp; Tableau Dashboard<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Create\u00a0<strong>SQL views<\/strong>\u00a0(or provide queries) in the DW for each\u00a0<strong>KPI<\/strong>\u00a0(rollups, window functions as needed).<\/li>\n\n\n\n<li>In\u00a0<strong>Tableau<\/strong>:<ul><li>Connect\u00a0<strong>directly<\/strong>\u00a0to your DW (PostgreSQL\/MySQL).<\/li><\/ul><ul><li>Create a\u00a0<strong>data source<\/strong>\u00a0per mart or reuse a published one.<\/li><\/ul><ul><li>Prefer\u00a0<strong>Extracts<\/strong>\u00a0for performance; document\u00a0<strong>refresh<\/strong>\u00a0steps (manual is OK).<\/li><\/ul>\n<ul class=\"wp-block-list\">\n<li>Build a dashboard with 5\u20137 tiles max: KPI cards, trend lines, breakdowns (by time, location, provider).<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Must-haves in Tableau<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Filters (date\/provider\/location) that match your\u00a0<strong>dimensions<\/strong>.<\/li>\n\n\n\n<li>A \u201c<strong>Data Info<\/strong>\u201d caption: source, last refresh date\/time, caveats.<\/li>\n\n\n\n<li>Each KPI worksheet references a\u00a0<strong>SQL view<\/strong>\u00a0or a clearly documented query.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Deliverables<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>olap_views.sql<\/code>\u00a0(or\u00a0<code>olap_queries.sql<\/code>) used by\u00a0<strong>Tableau<\/strong>.<\/li>\n\n\n\n<li>Tableau workbook\u00a0(<code>.twbx<\/code>)\u00a0<strong>or<\/strong>\u00a0clear screenshots with captions if using Public.<\/li>\n\n\n\n<li>Short\u00a0<strong>notes<\/strong>\u00a0on refresh routine (how to update extract).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Acceptance<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Numbers match the DW queries (reproducible from SQL).<\/li>\n\n\n\n<li>Filters behave correctly; drill-downs are consistent with the dimensional keys.<\/li>\n\n\n\n<li>Dashboard is readable, minimal chartjunk, and includes freshness info.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Task 5 \u2014 Documentation &amp; Handover<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>README.md<\/strong>: architecture diagram; tools\/versions; how to run Talend jobs (contexts); load sequence; how to open Tableau; known issues.<\/li>\n\n\n\n<li><strong>Data dictionary<\/strong>: fact\/dim fields, definitions, calculation notes, lineage.<\/li>\n\n\n\n<li><strong>Ethics &amp; License note<\/strong>: data licenses, PII minimisation, fairness caveats.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Deliverables<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>README, data dictionary, diagram.<\/li>\n\n\n\n<li>Zip\/repo with:\u00a0<code>lake\/<\/code>\u00a0structure,\u00a0<code>catalog.json<\/code>, Talend exports, SQL, Tableau workbook (or screenshots).<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Acceptance<\/strong><strong><\/strong><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A new user can follow the README to\u00a0<strong>reproduce<\/strong>\u00a0your KPIs end-to-end.<\/li>\n\n\n\n<li>\u00a0<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>General guidelines for performing the capstone project Task 0 \u2014 Scope &amp; KPIs (planning) Deliverables Acceptance Task 1 \u2014 Mini Data Lake (raw + standardised) Deliverables Acceptance Task 2 \u2014 Data Warehouse Design (star or snowflake) Deliverables Acceptance Task 3 &hellip; <a href=\"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/capstone-roadmap\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":11,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-71","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/71","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/comments?post=71"}],"version-history":[{"count":1,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/71\/revisions"}],"predecessor-version":[{"id":73,"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/pages\/71\/revisions\/73"}],"wp:attachment":[{"href":"https:\/\/vargas-solar.com\/data-ware-lake-houses-bi\/wp-json\/wp\/v2\/media?parent=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}