# lukebarousse/sql_data_engineering_course architecture

> Architecture diagram of the GitHub repository lukebarousse/sql_data_engineering_course, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-06-29). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/lukebarousse/sql_data_engineering_course
- Repository: https://github.com/lukebarousse/sql_data_engineering_course

## Overview

1. **Repo purpose**
   - This is a SQL training repository, not an application runtime. Its architecture is organized around three course projects that progressively move from analysis to warehouse modeling to ETL-style transformation.

2. **Execution environment**
   - The repo is designed to run against **DuckDB** locally or **MotherDuck** in the cloud.
   - The shared course dataset is attached as a remote DuckDB database, and the SQL files operate on that attached data rather than on a custom application backend.

3. **Top-level structure**
   - `Projects/1_EDA` contains analytical SQL queries.
   - `Projects/2_WH_Mart_Build` contains scripts to build a warehouse and several marts.
   - `Projects/3_Flat_to_WH_Build` contains scripts to transform flat job-posting data into a normalized warehouse schema.
   - `Resources/images` is documentation/supporting media only.

4. **Project 1: exploratory analytics layer**
   - The three SQL files in `1_EDA` represent focused job-market analyses:
     - top demanded skills
     - top paying skills
     - optimal skills
   - This project sits directly on top of the raw/course dataset and produces no persistent modeled layers; it is the “consumer” layer of the repo.

5. **Project 2: warehouse + mart build**
   - `2_WH_Mart_Build` is a staged build pipeline.
   - `01_create_tables_dw.sql` and `02_load_schema_dw.sql` define and populate the warehouse foundation.
   - `03_create_flat_mart.sql`, `04_create_skills_mart.sql`, `05_create_priority_mart.sql`, `06_update_priority_mart.sql`, and `07_create_company_mart.sql` build downstream reporting marts.
   - `build_dw_marts.sql` likely acts as the orchestration entry point for running the whole mart pipeline in sequence.

6. **Project 3: flat-to-warehouse transformation**
   - This project is the most ETL-like and shows the repository’s core data-modeling flow.
   - `00_load_data.sql` ingests the flat source.
   - `01_create_tables.sql` defines the target warehouse schema.
   - `02_populate_company_dim.sql`, `03_populate_skills_dim.sql`, `04_populate_fact_table.sql`, and `05_populate_bridge_table.sql` populate dimension, fact, and bridge tables.
   - `06_verify_schema.sql` validates the result.
   - `build_warehouse.sql` and `build_warehouse.sh` are the pipeline entry points for scripted execution.

7. **Core data flow**
   - The repo’s main architectural pattern is:
     - **raw/flat job data** → **warehouse schema** → **dimensional marts** → **analysis queries**
   - Project 3 focuses on normalizing flat data into a star-like warehouse.
   - Project 2 focuses on deriving mart tables from that warehouse.
   - Project 1 consumes the modeled data to answer business questions.

8. **Modeling boundaries**
   - The warehouse layer separates entities such as **company** and **skills** into dimensions.
   - A **fact table** captures the core job-posting events/attributes.
   - A **bridge table** is used where many-to-many relationships exist, especially around skills.
   - This indicates the repo is teaching dimensional modeling concepts, not just query writing.

9. **Automation and orchestration**
   - The presence of wrapper scripts like `build_dw_marts.sql`, `build_warehouse.sql`, and `build_warehouse.sh` suggests the pipeline can be run end-to-end rather than only script-by-script.
   - The shell script is the only non-SQL execution artifact and likely exists to batch-run the warehouse build locally.

10. **External dependencies and data source**
   - The course data comes from a shared MotherDuck database, and the README notes **SerpApi** as the upstream source for the job-posting dataset.
   - That means the repository’s architecture depends on an external managed data source plus DuckDB/MotherDuck as the execution engine, rather than on internal services.

11. **What to diagram**
   - A good architecture diagram should show:
     - the attached course database as the source
     - Project 3 as the warehouse build pipeline
     - Project 2 as the mart/reporting build pipeline
     - Project 1 as the analysis/consumption layer
     - DuckDB/MotherDuck as the runtime boundary around all SQL execution

Main entry points: [`Projects/3_Flat_to_WH_Build/build_warehouse.sql`](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/build_warehouse.sql) (Build WH), [`Projects/3_Flat_to_WH_Build/00_load_data.sql`](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/00_load_data.sql) (Load data), [`Projects/3_Flat_to_WH_Build/01_create_tables.sql`](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/01_create_tables.sql) (Create schema), [`Projects/2_WH_Mart_Build/build_dw_marts.sql`](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/build_dw_marts.sql) (Build marts)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_runtime["Runtime"]
  node_duckdb_motherduck[("DuckDB<br/>sql runtime")]
end

subgraph group_source["Source"]
  node_course_db[("Course DB<br/>attached data")]
  node_serpapi_source(("SerpApi<br/>upstream source"))
end

subgraph group_warehouse["Warehouse build"]
  node_wh_entry["Build WH<br/>orchestration sql"]
  node_wh_shell["Build shell<br/>runner script<br/>[build_warehouse.sh]"]
  node_load_flat["Load data<br/>ingest sql<br/>[00_load_data.sql]"]
  node_create_wh_tables["Create schema<br/>ddl sql"]
  node_populate_dims["Populate dims<br/>dimension sql"]
  node_populate_fact_bridge["Load fact+bridge<br/>model sql"]
  node_verify_wh["Verify schema<br/>check sql"]
end

subgraph group_marts["Mart build"]
  node_mart_entry["Build marts<br/>orchestration sql<br/>[build_dw_marts.sql]"]
  node_mart_dw["Warehouse base<br/>dw sql"]
  node_flat_mart["Flat mart<br/>mart sql"]
  node_skills_mart["Skills mart<br/>mart sql"]
  node_priority_mart["Priority mart<br/>mart sql"]
  node_company_mart["Company mart<br/>mart sql"]
end

subgraph group_analysis["Analysis"]
  node_eda_demand["Demanded skills<br/>analysis sql"]
  node_eda_paying["Paying skills<br/>analysis sql"]
  node_eda_optimal["Optimal skills<br/>analysis sql"]
end

node_serpapi_source -.->|"origin"| node_course_db
node_duckdb_motherduck -->|"attach"| node_course_db
node_wh_shell -->|"runs"| node_wh_entry
node_duckdb_motherduck -->|"executes"| node_wh_entry
node_course_db -->|"source"| node_load_flat
node_load_flat -->|"feeds"| node_create_wh_tables
node_create_wh_tables -->|"enables"| node_populate_dims
node_populate_dims -->|"supports"| node_populate_fact_bridge
node_populate_fact_bridge -->|"checks"| node_verify_wh
node_wh_entry -.->|"orchestrates"| node_load_flat
node_wh_entry -.->|"orchestrates"| node_create_wh_tables
node_wh_entry -.->|"orchestrates"| node_populate_dims
node_wh_entry -.->|"orchestrates"| node_populate_fact_bridge
node_mart_entry -.->|"orchestrates"| node_mart_dw
node_mart_dw -->|"feeds"| node_flat_mart
node_flat_mart -->|"feeds"| node_skills_mart
node_skills_mart -->|"feeds"| node_priority_mart
node_priority_mart -->|"feeds"| node_company_mart
node_duckdb_motherduck -->|"executes"| node_mart_entry
node_duckdb_motherduck -->|"executes"| node_eda_demand
node_duckdb_motherduck -->|"executes"| node_eda_paying
node_duckdb_motherduck -->|"executes"| node_eda_optimal
node_course_db -->|"queries"| node_eda_demand
node_course_db -->|"queries"| node_eda_paying
node_course_db -->|"queries"| node_eda_optimal
node_course_db -->|"raw input"| node_wh_entry
node_course_db -->|"source"| node_mart_entry

click node_wh_entry "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/build_warehouse.sql"
click node_wh_shell "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/build_warehouse.sh"
click node_load_flat "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/00_load_data.sql"
click node_create_wh_tables "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/01_create_tables.sql"
click node_verify_wh "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/06_verify_schema.sql"
click node_mart_entry "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/build_dw_marts.sql"
click node_mart_dw "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/01_create_tables_dw.sql"
click node_flat_mart "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/03_create_flat_mart.sql"
click node_skills_mart "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/04_create_skills_mart.sql"
click node_priority_mart "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/05_create_priority_mart.sql"
click node_company_mart "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/07_create_company_mart.sql"
click node_eda_demand "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/1_EDA/01_top_demanded_skills.sql"
click node_eda_paying "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/1_EDA/02_top_paying_skills.sql"
click node_eda_optimal "https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/1_EDA/03_optimal_skills.sql"

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_duckdb_motherduck toneBlue
class node_course_db,node_serpapi_source toneAmber
class node_wh_entry,node_wh_shell,node_load_flat,node_create_wh_tables,node_populate_dims,node_populate_fact_bridge,node_verify_wh toneMint
class node_mart_entry,node_mart_dw,node_flat_mart,node_skills_mart,node_priority_mart,node_company_mart toneRose
class node_eda_demand,node_eda_paying,node_eda_optimal toneIndigo
```

## Components

### Runtime

SQL execution boundary

- **DuckDB**: sql runtime · Local DuckDB or cloud MotherDuck execution environment

### Source

External course data

- **Course DB**: attached data · Shared remote DuckDB dataset used by the SQL projects
- **SerpApi**: upstream source · External origin for the job-posting dataset

### Warehouse build

Flat-to-warehouse pipeline

- [Build WH](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/build_warehouse.sql): orchestration sql · Entry point for the warehouse build · `Projects/3_Flat_to_WH_Build/build_warehouse.sql`
- [Build shell](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/build_warehouse.sh): runner script · Local shell wrapper for warehouse execution · `Projects/3_Flat_to_WH_Build/build_warehouse.sh`
- [Load data](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/00_load_data.sql): ingest sql · Ingests flat source data into the warehouse flow · `Projects/3_Flat_to_WH_Build/00_load_data.sql`
- [Create schema](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/01_create_tables.sql): ddl sql · Defines the target warehouse tables · `Projects/3_Flat_to_WH_Build/01_create_tables.sql`
- **Populate dims**: dimension sql · Loads company and skills dimensions
- **Load fact+bridge**: model sql · Populates the fact table and many-to-many bridge
- [Verify schema](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/3_Flat_to_WH_Build/06_verify_schema.sql): check sql · Validates the warehouse result · `Projects/3_Flat_to_WH_Build/06_verify_schema.sql`

### Mart build

Warehouse-to-reporting pipeline

- [Build marts](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/build_dw_marts.sql): orchestration sql · Entry point for the mart build pipeline · `Projects/2_WH_Mart_Build/build_dw_marts.sql`
- [Warehouse base](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/01_create_tables_dw.sql): dw sql · Creates and loads the warehouse foundation for marts · `Projects/2_WH_Mart_Build/01_create_tables_dw.sql`
- [Flat mart](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/03_create_flat_mart.sql): mart sql · Reporting mart over the flat job data · `Projects/2_WH_Mart_Build/03_create_flat_mart.sql`
- [Skills mart](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/04_create_skills_mart.sql): mart sql · Reporting mart centered on skills · `Projects/2_WH_Mart_Build/04_create_skills_mart.sql`
- [Priority mart](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/05_create_priority_mart.sql): mart sql · Priority reporting mart and update step · `Projects/2_WH_Mart_Build/05_create_priority_mart.sql`
- [Company mart](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/2_WH_Mart_Build/07_create_company_mart.sql): mart sql · Reporting mart centered on companies · `Projects/2_WH_Mart_Build/07_create_company_mart.sql`

### Analysis

EDA queries

- [Demanded skills](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/1_EDA/01_top_demanded_skills.sql): analysis sql · EDA query for top demanded skills · `Projects/1_EDA/01_top_demanded_skills.sql`
- [Paying skills](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/1_EDA/02_top_paying_skills.sql): analysis sql · EDA query for top paying skills · `Projects/1_EDA/02_top_paying_skills.sql`
- [Optimal skills](https://github.com/lukebarousse/sql_data_engineering_course/blob/main/Projects/1_EDA/03_optimal_skills.sql): analysis sql · EDA query for optimal skills · `Projects/1_EDA/03_optimal_skills.sql`

## Connections

- SerpApi → Course DB: origin · Upstream job-posting data is sourced from SerpApi
- DuckDB → Course DB: attach · SQL runtime attaches the shared course database
- Build shell → Build WH: runs · Shell wrapper invokes the warehouse build script
- DuckDB → Build WH: executes · Warehouse SQL runs inside DuckDB/MotherDuck
- Course DB → Load data: source · Flat source data is read during ingestion
- Load data → Create schema: feeds · Loaded data prepares the warehouse schema build
- Create schema → Populate dims: enables · Dimensions are populated after schema creation
- Populate dims → Load fact+bridge: supports · Fact and bridge loads depend on the dimensions
- Load fact+bridge → Verify schema: checks · Verification validates the populated warehouse
- Build WH → Load data: orchestrates · Warehouse entry script sequences the build steps
- Build WH → Create schema: orchestrates · Warehouse entry script sequences the build steps
- Build WH → Populate dims: orchestrates · Warehouse entry script sequences the build steps
- Build WH → Load fact+bridge: orchestrates · Warehouse entry script sequences the build steps
- Build marts → Warehouse base: orchestrates · Mart entry script sequences the warehouse foundation build
- Warehouse base → Flat mart: feeds · Warehouse foundation supports the flat mart
- Flat mart → Skills mart: feeds · Flat mart output supports downstream marts
- Skills mart → Priority mart: feeds · Skills mart informs priority mart construction
- Priority mart → Company mart: feeds · Priority mart layer supports company reporting
- DuckDB → Build marts: executes · Mart SQL runs inside DuckDB/MotherDuck
- DuckDB → Demanded skills: executes · EDA queries run inside DuckDB/MotherDuck
- DuckDB → Paying skills: executes · EDA queries run inside DuckDB/MotherDuck
- DuckDB → Optimal skills: executes · EDA queries run inside DuckDB/MotherDuck
- Course DB → Demanded skills: queries · EDA reads the modeled/shared course data
- Course DB → Paying skills: queries · EDA reads the modeled/shared course data
- Course DB → Optimal skills: queries · EDA reads the modeled/shared course data
- Course DB → Build WH: raw input · Warehouse build starts from the flat course dataset
- Course DB → Build marts: source · Mart build operates on warehouse outputs derived from the course data