# hansg931/ecommerce-dw architecture

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

- Interactive diagram: https://gitdiagram.com/hansg931/ecommerce-dw
- Repository: https://github.com/hansg931/ecommerce-dw

## Overview

1. **Repository purpose**
   - This is an analytics engineering project for MyAnimeList data, centered on a **local DuckDB + dbt warehouse**.
   - The repo is organized around four architectural phases: **EDA**, **3-layer data warehouse modeling**, **semantic layer definition**, and **LLM text-to-SQL evaluation**.

2. **Primary data sources**
   - The raw analytical inputs are Kaggle-derived CSVs: `anime.csv`, `rating_complete.csv`, `animelist.csv`, `anime_with_synopsis.csv`, and a small status mapping file.
   - These sources represent four main entity types: anime metadata, user ratings, user watch history, and synopsis text.
   - The raw data is not stored in dbt models; it is the external upstream boundary feeding the warehouse.

3. **Exploration layer**
   - The `notebooks/` folder contains three Jupyter notebooks used for exploratory analysis of anime metadata, ratings behavior, and review/synopsis text.
   - This layer exists to discover data quality issues, distribution bias, and modeling opportunities before formal warehouse design.
   - It is an analysis-only boundary and does not feed runtime application logic.

4. **Warehouse execution stack**
   - The warehouse is built with **dbt-core** and **dbt-duckdb**, using **DuckDB** as the local analytical engine.
   - `dbt_project/` contains the dbt project configuration, profiles, SQL models, source definitions, exposures, and tests.
   - This is a batch transformation architecture: source CSVs are modeled into reusable relational layers.

5. **Staging layer**
   - `dbt_project/models/staging/` contains four staging views: `stg_anime`, `stg_ratings`, `stg_animelist`, and `stg_anime_synopsis`.
   - The staging layer handles cleanup and normalization: type casting, source-specific validation, status mapping, and simple derived flags.
   - It is the first abstraction boundary between raw files and analytical modeling.

6. **Intermediate layer**
   - `dbt_project/models/intermediate/` contains three derived tables: `int_anime_stats`, `int_user_profiles`, and `int_genre_metrics`.
   - These models join staged sources and compute reusable analytical features such as completion/drop rates, user tiers, genre popularity, and time/recency-oriented metrics.
   - This layer separates reusable business logic from final reporting outputs.

7. **Mart layer**
   - `dbt_project/models/marts/` contains three business-facing tables: `mart_content_performance`, `mart_user_segments`, and `mart_genre_trends`.
   - These are the terminal consumption layer for analytics questions, aggregations, and downstream semantic definitions.
   - The marts are the main “answerable” datasets for reporting and LLM query generation.

8. **Data lineage and flow**
   - The canonical flow is: **raw CSVs → staging views → intermediate tables → marts**.
   - The same core sources can fan out into multiple intermediate marts: for example, anime stats influence both content performance and genre trends, while ratings and watch history inform user segmentation.
   - `docs/data_lineage.md` and `docs/dw_design.md` document this lineage explicitly.

9. **Quality and validation**
   - `dbt_project/tests/` contains custom SQL tests that enforce important business rules such as rating/list-count consistency and valid value ranges.
   - Model-level schema tests are defined alongside staging, intermediate, and mart YAML files.
   - The README indicates the project emphasizes test completeness and model correctness as part of the warehouse boundary.

10. **Semantic layer**
   - The `semantic_layer/` directory defines metadata for AI/analytics consumption: table definitions, a business glossary, metric definitions, and query patterns.
   - This layer adds a contract over the marts so that terms like metrics, columns, and query behavior are consistently described in Korean and English.
   - Architecturally, it sits above the warehouse and translates schema into a more human- and model-readable interface.

11. **Golden dataset and evaluation loop**
   - `golden_dataset/` contains 40 curated natural-language questions and matching validated SQL queries.
   - `golden_dataset/llm_evaluation/evaluate.py` runs Gemini-based evaluation to compare baseline schema-only prompting versus semantic-layer-enhanced prompting.
   - This is a closed-loop validation subsystem: semantic metadata is tested by how well an LLM can generate correct SQL against the marts.

12. **Project boundaries**
   - The repo is not an application serving end users; it is an **offline analytics engineering pipeline**.
   - The main runtime boundary is local Python execution with Poetry, dbt, DuckDB, notebooks, and optional Gemini API access for evaluation.
   - `docs/`, `tests/`, and `semantic_layer/` are supporting layers around the warehouse, not separate production services.

13. **What an architecture diagram should emphasize**
   - Show the external raw CSV inputs entering a DuckDB/dbt transformation pipeline.
   - Show the strict layer separation: staging for cleanup, intermediate for reusable feature engineering, marts for business-ready outputs.
   - Show the semantic layer and golden dataset as a parallel validation/consumption path above the marts, not as part of the core warehouse execution path.

Main entry points: [`semantic_layer`](https://github.com/hansg931/ecommerce-dw/tree/main/semantic_layer) (Semantic layer), [`dbt_project/models/staging`](https://github.com/hansg931/ecommerce-dw/tree/main/dbt_project/models/staging) (Staging), [`golden_dataset`](https://github.com/hansg931/ecommerce-dw/tree/main/golden_dataset) (Golden set), [`dbt_project/models/intermediate/int_anime_stats.sql`](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/intermediate/int_anime_stats.sql) (Anime stats)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_external["Raw inputs"]
  node_raw_csvs[("Raw CSVs<br/>source files")]
end

subgraph group_exploration["EDA"]
  node_eda_notebooks["EDA notebooks<br/>analysis notebooks"]
end

subgraph group_warehouse["dbt warehouse"]
  node_duckdb[("DuckDB<br/>local engine")]
  node_dbt_project["dbt project<br/>dbt core<br/>[dbt_project.yml]"]
  node_staging_models["Staging<br/>dbt views"]
  node_intermediate_models["Intermediate<br/>derived tables"]
  node_mart_models["Marts<br/>business tables"]
  node_stg_anime["Anime stg<br/>staging view<br/>[stg_anime.sql]"]
  node_stg_ratings["Ratings stg<br/>staging view<br/>[stg_ratings.sql]"]
  node_stg_animelist["List stg<br/>staging view<br/>[stg_animelist.sql]"]
  node_stg_synopsis["Synopsis stg<br/>staging view"]
  node_int_anime_stats["Anime stats<br/>intermediate table"]
  node_int_user_profiles["User profiles<br/>intermediate table"]
  node_int_genre_metrics["Genre metrics<br/>intermediate table"]
  node_mart_content_performance["Content perf<br/>mart table"]
  node_mart_user_segments["User segments<br/>mart table"]
  node_mart_genre_trends["Genre trends<br/>mart table"]
end

subgraph group_semantic["Semantic layer"]
  node_semantic_layer["Semantic layer<br/>metadata contract"]
  node_glossary["Glossary"]
  node_metrics["Metrics<br/>metric defs"]
  node_patterns["Query patterns<br/>query templates<br/>[query_patterns.yml]"]
  node_table_defs["Table defs<br/>table metadata"]
end

subgraph group_evaluation["LLM eval"]
  node_golden_dataset["Golden set<br/>eval dataset"]
  node_questions["Questions<br/>nl prompts<br/>[questions.yml]"]
  node_golden_sql["Golden SQL<br/>reference queries<br/>[golden_queries.sql]"]
  node_eval_script["Eval runner<br/>python script<br/>[evaluate.py]"]
end

subgraph group_docs["Docs"]
  node_docs_lineage["Lineage docs<br/>architecture docs<br/>[data_lineage.md]"]
  node_docs_design["DW design<br/>architecture docs<br/>[dw_design.md]"]
end

node_raw_csvs -->|"source feed"| node_staging_models
node_duckdb -->|"executes"| node_dbt_project
node_dbt_project -->|"defines"| node_staging_models
node_staging_models -->|"contains"| node_stg_anime
node_staging_models -->|"contains"| node_stg_ratings
node_staging_models -->|"contains"| node_stg_animelist
node_staging_models -->|"contains"| node_stg_synopsis
node_stg_anime -->|"feeds"| node_int_anime_stats
node_stg_anime -->|"feeds"| node_int_genre_metrics
node_stg_ratings -->|"feeds"| node_int_user_profiles
node_stg_animelist -->|"feeds"| node_int_user_profiles
node_stg_animelist -->|"feeds"| node_int_anime_stats
node_int_anime_stats -->|"feeds"| node_mart_content_performance
node_int_anime_stats -->|"feeds"| node_mart_genre_trends
node_int_user_profiles -->|"feeds"| node_mart_user_segments
node_int_genre_metrics -->|"feeds"| node_mart_genre_trends
node_mart_content_performance -->|"describes"| node_semantic_layer
node_mart_user_segments -->|"describes"| node_semantic_layer
node_mart_genre_trends -->|"describes"| node_semantic_layer
node_semantic_layer -->|"includes"| node_glossary
node_semantic_layer -->|"includes"| node_metrics
node_semantic_layer -->|"includes"| node_patterns
node_semantic_layer -->|"includes"| node_table_defs
node_golden_dataset -->|"contains"| node_questions
node_golden_dataset -->|"contains"| node_golden_sql
node_golden_dataset -->|"runs"| node_eval_script
node_semantic_layer -->|"improves prompt"| node_eval_script
node_golden_dataset -->|"benchmarks"| node_semantic_layer
node_docs_lineage -->|"documents"| node_staging_models
node_docs_design -->|"documents"| node_dbt_project
node_eda_notebooks -->|"explores"| node_raw_csvs

click node_eda_notebooks "https://github.com/hansg931/ecommerce-dw/tree/main/notebooks"
click node_dbt_project "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/dbt_project.yml"
click node_staging_models "https://github.com/hansg931/ecommerce-dw/tree/main/dbt_project/models/staging"
click node_intermediate_models "https://github.com/hansg931/ecommerce-dw/tree/main/dbt_project/models/intermediate"
click node_mart_models "https://github.com/hansg931/ecommerce-dw/tree/main/dbt_project/models/marts"
click node_stg_anime "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_anime.sql"
click node_stg_ratings "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_ratings.sql"
click node_stg_animelist "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_animelist.sql"
click node_stg_synopsis "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_anime_synopsis.sql"
click node_int_anime_stats "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/intermediate/int_anime_stats.sql"
click node_int_user_profiles "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/intermediate/int_user_profiles.sql"
click node_int_genre_metrics "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/intermediate/int_genre_metrics.sql"
click node_mart_content_performance "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/marts/mart_content_performance.sql"
click node_mart_user_segments "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/marts/mart_user_segments.sql"
click node_mart_genre_trends "https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/marts/mart_genre_trends.sql"
click node_semantic_layer "https://github.com/hansg931/ecommerce-dw/tree/main/semantic_layer"
click node_glossary "https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/business_glossary.yml"
click node_metrics "https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/metric_definitions.yml"
click node_patterns "https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/query_patterns.yml"
click node_table_defs "https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/table_definitions.yml"
click node_golden_dataset "https://github.com/hansg931/ecommerce-dw/tree/main/golden_dataset"
click node_questions "https://github.com/hansg931/ecommerce-dw/blob/main/golden_dataset/questions.yml"
click node_golden_sql "https://github.com/hansg931/ecommerce-dw/blob/main/golden_dataset/golden_queries.sql"
click node_eval_script "https://github.com/hansg931/ecommerce-dw/blob/main/golden_dataset/llm_evaluation/evaluate.py"
click node_docs_lineage "https://github.com/hansg931/ecommerce-dw/blob/main/docs/data_lineage.md"
click node_docs_design "https://github.com/hansg931/ecommerce-dw/blob/main/docs/dw_design.md"

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_raw_csvs toneBlue
class node_eda_notebooks toneAmber
class node_duckdb,node_dbt_project,node_staging_models,node_intermediate_models,node_mart_models,node_stg_anime,node_stg_ratings,node_stg_animelist,node_stg_synopsis,node_int_anime_stats,node_int_user_profiles,node_int_genre_metrics,node_mart_content_performance,node_mart_user_segments,node_mart_genre_trends toneMint
class node_semantic_layer,node_glossary,node_metrics,node_patterns,node_table_defs toneRose
class node_golden_dataset,node_questions,node_golden_sql,node_eval_script toneIndigo
class node_docs_lineage,node_docs_design toneTeal
```

## Components

### Raw inputs

Kaggle-derived CSV sources feeding the warehouse.

- **Raw CSVs**: source files · Kaggle-derived anime, ratings, watch-history, and synopsis inputs.

### EDA

Notebook-based exploration and data profiling.

- [EDA notebooks](https://github.com/hansg931/ecommerce-dw/tree/main/notebooks): analysis notebooks · Exploratory notebooks for metadata, ratings, and review analysis. · `notebooks`

### dbt warehouse

DuckDB-backed dbt transformation pipeline.

- **DuckDB**: local engine · Local analytical execution engine used by dbt.
- [dbt project](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/dbt_project.yml): dbt core · Project configuration, profiles, models, sources, and tests. · `dbt_project/dbt_project.yml`
- [Staging](https://github.com/hansg931/ecommerce-dw/tree/main/dbt_project/models/staging): dbt views · Cleanup and normalization boundary over raw sources. · `dbt_project/models/staging`
- [Intermediate](https://github.com/hansg931/ecommerce-dw/tree/main/dbt_project/models/intermediate): derived tables · Reusable feature engineering and business logic layer. · `dbt_project/models/intermediate`
- [Marts](https://github.com/hansg931/ecommerce-dw/tree/main/dbt_project/models/marts): business tables · Consumption-ready analytical outputs. · `dbt_project/models/marts`
- [Anime stg](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_anime.sql): staging view · Normalize anime metadata and types. · `dbt_project/models/staging/stg_anime.sql`
- [Ratings stg](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_ratings.sql): staging view · Clean rating records and validate values. · `dbt_project/models/staging/stg_ratings.sql`
- [List stg](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_animelist.sql): staging view · Normalize user watch-history and status mapping. · `dbt_project/models/staging/stg_animelist.sql`
- [Synopsis stg](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/staging/stg_anime_synopsis.sql): staging view · Prepare synopsis text for analysis. · `dbt_project/models/staging/stg_anime_synopsis.sql`
- [Anime stats](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/intermediate/int_anime_stats.sql): intermediate table · Reusable content metrics such as completion and drop rates. · `dbt_project/models/intermediate/int_anime_stats.sql`
- [User profiles](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/intermediate/int_user_profiles.sql): intermediate table · Derived user tiers and engagement features. · `dbt_project/models/intermediate/int_user_profiles.sql`
- [Genre metrics](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/intermediate/int_genre_metrics.sql): intermediate table · Genre popularity and time-oriented aggregates. · `dbt_project/models/intermediate/int_genre_metrics.sql`
- [Content perf](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/marts/mart_content_performance.sql): mart table · Business-facing content performance dataset. · `dbt_project/models/marts/mart_content_performance.sql`
- [User segments](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/marts/mart_user_segments.sql): mart table · Business-facing audience segmentation dataset. · `dbt_project/models/marts/mart_user_segments.sql`
- [Genre trends](https://github.com/hansg931/ecommerce-dw/blob/main/dbt_project/models/marts/mart_genre_trends.sql): mart table · Business-facing genre trend and popularity dataset. · `dbt_project/models/marts/mart_genre_trends.sql`

### Semantic layer

Metadata and metric contract above marts.

- [Semantic layer](https://github.com/hansg931/ecommerce-dw/tree/main/semantic_layer): metadata contract · Human- and model-readable definitions over marts. · `semantic_layer`
- [Glossary](https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/business_glossary.yml): business glossary · Bilingual business terms and definitions. · `semantic_layer/business_glossary.yml`
- [Metrics](https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/metric_definitions.yml): metric defs · Metric definitions for analytics consumption. · `semantic_layer/metric_definitions.yml`
- [Query patterns](https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/query_patterns.yml): query templates · Reusable query behavior and prompt guidance. · `semantic_layer/query_patterns.yml`
- [Table defs](https://github.com/hansg931/ecommerce-dw/blob/main/semantic_layer/table_definitions.yml): table metadata · Semantic descriptions of tables and columns. · `semantic_layer/table_definitions.yml`

### LLM eval

Golden dataset and text-to-SQL validation loop.

- [Golden set](https://github.com/hansg931/ecommerce-dw/tree/main/golden_dataset): eval dataset · Curated NL questions and validated SQL pairs. · `golden_dataset`
- [Questions](https://github.com/hansg931/ecommerce-dw/blob/main/golden_dataset/questions.yml): nl prompts · Curated natural-language benchmark questions. · `golden_dataset/questions.yml`
- [Golden SQL](https://github.com/hansg931/ecommerce-dw/blob/main/golden_dataset/golden_queries.sql): reference queries · Validated target SQL for evaluation. · `golden_dataset/golden_queries.sql`
- [Eval runner](https://github.com/hansg931/ecommerce-dw/blob/main/golden_dataset/llm_evaluation/evaluate.py): python script · Gemini-based schema-only vs semantic-layer evaluation. · `golden_dataset/llm_evaluation/evaluate.py`

### Docs

Architecture and lineage documentation.

- [Lineage docs](https://github.com/hansg931/ecommerce-dw/blob/main/docs/data_lineage.md): architecture docs · Documented warehouse flow and design. · `docs/data_lineage.md`
- [DW design](https://github.com/hansg931/ecommerce-dw/blob/main/docs/dw_design.md): architecture docs · Warehouse design and layering notes. · `docs/dw_design.md`

## Connections

- Raw CSVs → Staging: source feed · Raw analytical inputs enter the dbt staging boundary.
- DuckDB → dbt project: executes · DuckDB is the local engine used by the dbt project.
- dbt project → Staging: defines · Project config and source definitions drive staging models.
- Staging → Anime stg: contains · Anime metadata staging view.
- Staging → Ratings stg: contains · Ratings staging view.
- Staging → List stg: contains · Watch-history staging view.
- Staging → Synopsis stg: contains · Synopsis staging view.
- Anime stg → Anime stats: feeds · Anime staging contributes to content metrics.
- Anime stg → Genre metrics: feeds · Anime genre fields drive genre aggregates.
- Ratings stg → User profiles: feeds · Ratings inform user engagement features.
- List stg → User profiles: feeds · Watch history supports user segmentation.
- List stg → Anime stats: feeds · Consumption status contributes to anime stats.
- Anime stats → Content perf: feeds · Core content KPIs roll into the content mart.
- Anime stats → Genre trends: feeds · Anime stats power genre trend reporting.
- User profiles → User segments: feeds · User features roll into segmentation outputs.
- Genre metrics → Genre trends: feeds · Genre aggregates feed the genre trend mart.
- Content perf → Semantic layer: describes · Semantic definitions sit above the marts.
- User segments → Semantic layer: describes · Semantic contract references segmentation outputs.
- Genre trends → Semantic layer: describes · Semantic contract references genre trend outputs.
- Semantic layer → Glossary: includes · Business terminology and bilingual definitions.
- Semantic layer → Metrics: includes · Metric definitions for AI and analytics use.
- Semantic layer → Query patterns: includes · Query patterns and prompt guidance.
- Semantic layer → Table defs: includes · Table and column metadata definitions.
- Golden set → Questions: contains · Curated benchmark questions.
- Golden set → Golden SQL: contains · Validated SQL answers.
- Golden set → Eval runner: runs · Evaluation script measures text-to-SQL quality.
- Semantic layer → Eval runner: improves prompt · Semantic metadata is used to enhance SQL generation.
- Golden set → Semantic layer: benchmarks · The semantic layer is evaluated by the golden dataset loop.
- Lineage docs → Staging: documents · Lineage docs explain the core warehouse flow.
- DW design → dbt project: documents · Design docs describe the warehouse architecture.
- EDA notebooks → Raw CSVs: explores · Notebooks analyze the same upstream raw data.
