lnajdi/business-intelligence-labs

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.