# lnajdi/business-intelligence-labs architecture
> Architecture diagram of the GitHub repository lnajdi/business-intelligence-labs, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-30). It is an AI-made overview: check details against the source.
- Interactive diagram: https://gitdiagram.com/lnajdi/business-intelligence-labs
- Repository: https://github.com/lnajdi/business-intelligence-labs
## Overview
1. **Repository purpose**
- This repo is a **BI lab workspace**, not an application codebase.
- The core architecture is a **small data warehouse pipeline** built around **CSV inputs**, **Apache Hop** for orchestration/ETL, and **DuckDB** as the analytical database.
2. **Primary working area**
- The main system lives in `labs/lab01_hop_duckdb/`.
- Everything else at the root is course scaffolding (`SETUP.md`, overall `README.md`).
- The lab is structured as a teaching environment: inputs, transformations, SQL scripts, Hop metadata, and documentation.
3. **Data sources**
- Source data is under `labs/lab01_hop_duckdb/data/raw/`.
- The raw layer contains multiple CSV feeds: `orders`, `order_items`, `payments`, `customers`, `products`, `categories`, `stock_movements`, and `sales_budget`.
- There are also `_april` files, which suggest **incremental / delta loads** are part of the exercise.
4. **Landing and storage zones**
- `data/processed/` is present as a placeholder for derived files.
- `duckdb/` is a placeholder for the local DuckDB database file or related artifacts.
- The repo intentionally does **not** store generated databases or personal outputs; these are meant to be created locally during the lab.
5. **Orchestration layer: Apache Hop**
- `hop/` contains the ETL orchestration assets.
- `hop/pipelines/` holds the pipeline definitions:
- `p01_csv_to_staging.hpl` for loading raw files into staging,
- `p02_dim_*` pipelines for building dimensions,
- `p03_build_facts.hpl` for facts,
- `p04_incremental_load.hpl` for delta processing,
- `p05_load_budget.hpl` for budget/control data.
- `hop/workflows/` contains higher-level execution flows:
- `wf_initial_load.hwf`,
- `wf_incremental_load.hwf`,
- `wf_checks.hwf`.
- In architecture terms, Hop is the **control plane** that sequences loading, transformation, and validation steps.
6. **Metadata and connection layer**
- `hop/metadata/rdbms/DuckDB_Lab1.json` stores the database connection metadata for Hop.
- This is the boundary between the ETL tool and the warehouse engine: Hop executes pipelines against DuckDB through this connection definition.
7. **SQL transformation layers**
- The `sql/` directory mirrors the warehouse lifecycle in script form.
- The scripts show a clear progression:
- `00_create_schema.sql` and `10_create_staging_schema.sql` for setup,
- `01_load_staging_tables.sql` and `11_staging_transformations.sql` for staging,
- `20_create_warehouse_schema.sql` plus `21_dim_*`, `30_fact_*` for dimensional modeling,
- `40_create_control_schema.sql` and `41_load_budget.sql` for control/budget data,
- `50_initial_full_load.sql` and `51_incremental_load.sql` for load execution,
- `52_actuals_vs_budget.sql` for analytical comparison.
- This suggests the same logical architecture is expressed both as Hop pipelines and as SQL scripts.
8. **Warehouse model**
- The warehouse is a **star schema** pattern, confirmed by docs and SQL filenames.
- Dimensions include `date`, `customer`, `product`, and `channel`.
- Facts include `sales` and `stock`.
- The architecture separates descriptive entities from transactional measures, enabling KPI analysis.
9. **Control and analytics layer**
- `sales_budget.csv` and `41_load_budget.sql` indicate a small **control schema** for planning vs actual analysis.
- `52_actuals_vs_budget.sql` is the downstream analytic comparison layer.
- This means the pipeline is not only loading operational data but also preparing **business performance reporting**.
10. **Validation and quality**
- `wf_checks.hwf`, `02_profile_tables.sql`, and `03_quality_checks.sql` indicate a dedicated **data profiling and quality gate**.
- Validation appears to run after loading, before analysis, which is a distinct boundary in the flow.
- The architecture therefore includes a quality-check subsystem, not just ingestion and modeling.
11. **Documentation and teaching assets**
- `docs/` contains the conceptual model: Apache Hop concepts, architecture notes, business questions, data dictionary, incremental load pattern, KPI examples, and star schema design.
- `hop/blueprints/` and `hop/pipeline_blueprint.md` suggest guided implementation patterns rather than production code.
- `deliverables/` contains templates for student outputs, reinforcing that this is a lab environment.
12. **End-to-end data flow**
- The main flow is: **raw CSVs → staging → dimensional warehouse → facts/control tables → KPI exploration and budget comparisons**.
- Hop workflows orchestrate the execution path, while SQL scripts define the warehouse and transformations.
- The architectural boundary to draw is a local analytics stack: file-based sources feeding DuckDB through Hop-managed ETL, with separate quality and incremental-load paths.
Main entry points: [`labs/lab01_hop_duckdb/hop`](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/hop) (Hop), [`labs/lab01_hop_duckdb`](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb) (Lab 01), [`labs/lab01_hop_duckdb/sql/00_create_schema.sql`](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/00_create_schema.sql) (SQL scripts), [`labs/lab01_hop_duckdb/sql/20_create_warehouse_schema.sql`](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/20_create_warehouse_schema.sql) (Star schema)
## Diagram (Mermaid)
```mermaid
flowchart TD
subgraph group_root_scaffold["Course scaffold"]
node_repo_readme["README
repo intro
[README.md]"]
node_setup_guide["SETUP
[SETUP.md]"]
end
subgraph group_lab_workspace["Lab workspace"]
node_lab01["Lab 01
bi lab"]
end
subgraph group_data_zone["Data zones"]
node_raw_data[("Raw CSVs
source files")]
node_processed_data[("Processed
derived files")]
node_duckdb_store[("DuckDB
warehouse store")]
end
subgraph group_hop_layer["Hop ETL"]
node_hop_root["Hop
etl control"]
node_hop_meta["DuckDB conn
hop metadata
[DuckDB_Lab1.json]"]
node_pipe_p01["CSV staging
pipeline"]
node_pipe_dims["Dimensions
pipeline set
[p02_dim_date.hpl]"]
node_pipe_facts["Facts
pipeline"]
node_pipe_incr["Incremental
pipeline"]
node_pipe_budget["Budget load
pipeline"]
node_wf_exec["Workflows"]
end
subgraph group_sql_layer["SQL lifecycle"]
node_sql_lifecycle["SQL scripts
warehouse sql"]
node_sql_quality["Quality gate
sql checks"]
node_sql_warehouse["Star schema
dw model"]
node_sql_control["Control data
control sql"]
node_sql_analytics["KPI analysis
analytics sql"]
end
subgraph group_docs_layer["Docs and deliverables"]
node_docs["Docs"]
node_deliverables["Deliverables
student outputs"]
end
node_repo_readme -->|"points to"| node_lab01
node_setup_guide -->|"sets up"| node_lab01
node_lab01 -->|"ingests"| node_raw_data
node_lab01 -->|"stores in"| node_duckdb_store
node_lab01 -->|"orchestrates with"| node_hop_root
node_hop_root -->|"connects"| node_hop_meta
node_hop_root -->|"runs"| node_pipe_p01
node_hop_root -->|"runs"| node_pipe_dims
node_hop_root -->|"runs"| node_pipe_facts
node_hop_root -->|"runs"| node_pipe_incr
node_hop_root -->|"runs"| node_pipe_budget
node_hop_root -->|"sequences"| node_wf_exec
node_raw_data -->|"feeds"| node_pipe_p01
node_pipe_p01 -.->|"mirrors"| node_sql_lifecycle
node_sql_lifecycle -->|"includes"| node_sql_quality
node_sql_lifecycle -->|"includes"| node_sql_warehouse
node_sql_lifecycle -->|"includes"| node_sql_control
node_sql_warehouse -->|"supports"| node_sql_analytics
node_pipe_dims -->|"builds"| node_sql_warehouse
node_pipe_facts -->|"builds"| node_sql_warehouse
node_pipe_budget -->|"loads"| node_sql_control
node_pipe_incr -->|"used by"| node_wf_exec
node_sql_quality -->|"gates"| node_wf_exec
node_docs -->|"documents"| node_lab01
node_deliverables -.->|"supports"| node_docs
node_processed_data -.->|"pairs with"| node_duckdb_store
click node_repo_readme "https://github.com/lnajdi/business-intelligence-labs/blob/main/README.md"
click node_setup_guide "https://github.com/lnajdi/business-intelligence-labs/blob/main/SETUP.md"
click node_lab01 "https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb"
click node_raw_data "https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/data/raw"
click node_processed_data "https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/data/processed"
click node_duckdb_store "https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/duckdb"
click node_hop_root "https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/hop"
click node_hop_meta "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/metadata/rdbms/DuckDB_Lab1.json"
click node_pipe_p01 "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p01_csv_to_staging.hpl"
click node_pipe_dims "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p02_dim_date.hpl"
click node_pipe_facts "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p03_build_facts.hpl"
click node_pipe_incr "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p04_incremental_load.hpl"
click node_pipe_budget "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p05_load_budget.hpl"
click node_wf_exec "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/workflows/wf_initial_load.hwf"
click node_sql_lifecycle "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/00_create_schema.sql"
click node_sql_quality "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/02_profile_tables.sql"
click node_sql_warehouse "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/20_create_warehouse_schema.sql"
click node_sql_control "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/40_create_control_schema.sql"
click node_sql_analytics "https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/52_actuals_vs_budget.sql"
click node_docs "https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/docs"
click node_deliverables "https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/deliverables"
classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_repo_readme,node_setup_guide toneBlue
class node_lab01 toneAmber
class node_raw_data,node_processed_data,node_duckdb_store toneMint
class node_hop_root,node_hop_meta,node_pipe_p01,node_pipe_dims,node_pipe_facts,node_pipe_incr,node_pipe_budget,node_wf_exec toneRose
class node_sql_lifecycle,node_sql_quality,node_sql_warehouse,node_sql_control,node_sql_analytics toneIndigo
class node_docs,node_deliverables toneTeal
```
## Components
### Course scaffold
Top-level repository guidance and setup files.
- [README](https://github.com/lnajdi/business-intelligence-labs/blob/main/README.md): repo intro · Repository-level overview. · `README.md`
- [SETUP](https://github.com/lnajdi/business-intelligence-labs/blob/main/SETUP.md): setup guide · Environment and lab setup instructions. · `SETUP.md`
### Lab workspace
Main BI lab area for the DuckDB and Apache Hop exercise.
- [Lab 01](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb): bi lab · Main workspace for the Hop + DuckDB lab. · `labs/lab01_hop_duckdb`
### Data zones
Raw inputs and local working storage.
- [Raw CSVs](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/data/raw): source files · Operational input feeds including incremental April files and budget data. · `labs/lab01_hop_duckdb/data/raw`
- [Processed](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/data/processed): derived files · Placeholder for local derived outputs. · `labs/lab01_hop_duckdb/data/processed`
- [DuckDB](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/duckdb): warehouse store · Local analytical database workspace. · `labs/lab01_hop_duckdb/duckdb`
### Hop ETL
Apache Hop pipelines, workflows, and connection metadata.
- [Hop](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/hop): etl control · Apache Hop orchestration layer. · `labs/lab01_hop_duckdb/hop`
- [DuckDB conn](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/metadata/rdbms/DuckDB_Lab1.json): hop metadata · DuckDB connection definition used by Hop pipelines. · `labs/lab01_hop_duckdb/hop/metadata/rdbms/DuckDB_Lab1.json`
- [CSV staging](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p01_csv_to_staging.hpl): pipeline · Loads raw CSVs into staging. · `labs/lab01_hop_duckdb/hop/pipelines/p01_csv_to_staging.hpl`
- [Dimensions](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p02_dim_date.hpl): pipeline set · Builds core dimensions: date, customer, product, channel. · `labs/lab01_hop_duckdb/hop/pipelines/p02_dim_date.hpl`
- [Facts](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p03_build_facts.hpl): pipeline · Builds sales and stock fact tables. · `labs/lab01_hop_duckdb/hop/pipelines/p03_build_facts.hpl`
- [Incremental](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p04_incremental_load.hpl): pipeline · Processes delta loads for April feeds. · `labs/lab01_hop_duckdb/hop/pipelines/p04_incremental_load.hpl`
- [Budget load](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/pipelines/p05_load_budget.hpl): pipeline · Loads control data for budget comparisons. · `labs/lab01_hop_duckdb/hop/pipelines/p05_load_budget.hpl`
- [Workflows](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/hop/workflows/wf_initial_load.hwf): workflow · Initial, incremental, and validation execution flows. · `labs/lab01_hop_duckdb/hop/workflows/wf_initial_load.hwf`
### SQL lifecycle
Scripted warehouse setup, transforms, checks, and analytics.
- [SQL scripts](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/00_create_schema.sql): warehouse sql · Scripted warehouse lifecycle mirroring the Hop pipelines. · `labs/lab01_hop_duckdb/sql/00_create_schema.sql`
- [Quality gate](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/02_profile_tables.sql): sql checks · Profiling and data-quality validation steps. · `labs/lab01_hop_duckdb/sql/02_profile_tables.sql`
- [Star schema](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/20_create_warehouse_schema.sql): dw model · Dimensions and facts for the analytical warehouse. · `labs/lab01_hop_duckdb/sql/20_create_warehouse_schema.sql`
- [Control data](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/40_create_control_schema.sql): control sql · Budget schema and load scripts for actual-vs-budget analysis. · `labs/lab01_hop_duckdb/sql/40_create_control_schema.sql`
- [KPI analysis](https://github.com/lnajdi/business-intelligence-labs/blob/main/labs/lab01_hop_duckdb/sql/52_actuals_vs_budget.sql): analytics sql · Exploration and actuals-vs-budget comparison queries. · `labs/lab01_hop_duckdb/sql/52_actuals_vs_budget.sql`
### Docs and deliverables
Teaching material, architecture notes, and student outputs.
- [Docs](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/docs): lab docs · Architecture notes, concepts, schema design, and business questions. · `labs/lab01_hop_duckdb/docs`
- [Deliverables](https://github.com/lnajdi/business-intelligence-labs/tree/main/labs/lab01_hop_duckdb/deliverables): student outputs · Templates for checklist, KPI list, and quality report. · `labs/lab01_hop_duckdb/deliverables`
## Connections
- README → Lab 01: points to · Repository overview leads into the lab workspace.
- SETUP → Lab 01: sets up · Setup instructions prepare the lab environment.
- Lab 01 → Raw CSVs: ingests · Lab consumes file-based source feeds.
- Lab 01 → DuckDB: stores in · Local warehouse artifacts are created in DuckDB workspace.
- Lab 01 → Hop: orchestrates with · Apache Hop manages execution in the lab.
- Hop → DuckDB conn: connects · Pipelines use DuckDB connection metadata.
- Hop → CSV staging: runs · Hop first loads CSVs into staging.
- Hop → Dimensions: runs · Hop builds the dimensional layer.
- Hop → Facts: runs · Hop builds fact tables.
- Hop → Incremental: runs · Hop executes delta processing.
- Hop → Budget load: runs · Hop loads budget/control data.
- Hop → Workflows: sequences · Workflows coordinate initial load, incremental load, and checks.
- Raw CSVs → CSV staging: feeds · Raw CSV inputs are source material for staging loads.
- CSV staging → SQL scripts: mirrors · Staging logic is also expressed in SQL scripts.
- SQL scripts → Quality gate: includes · Lifecycle scripts include profiling and quality checks.
- SQL scripts → Star schema: includes · Lifecycle scripts create the dimensional warehouse.
- SQL scripts → Control data: includes · Lifecycle scripts create and load control tables.
- Star schema → KPI analysis: supports · Warehouse outputs feed KPI and comparison queries.
- Dimensions → Star schema: builds · Dimension pipelines populate the star schema.
- Facts → Star schema: builds · Fact pipelines populate the star schema.
- Budget load → Control data: loads · Budget pipeline populates control schema data.
- Incremental → Workflows: used by · Incremental load is invoked through workflow execution.
- Quality gate → Workflows: gates · Quality checks sit before downstream analysis.
- Docs → Lab 01: documents · Teaching material explains the lab architecture and patterns.
- Deliverables → Docs: supports · Templates guide student outputs and reporting.
- Processed → DuckDB: pairs with · Processed artifacts and local database form the working storage layer.