lukebarousse/sql_data_engineering_course

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