How hansg931/ecommerce-dw works
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 Semantic layer · staging Staging · golden_dataset Golden set · int_anime_stats.sql Anime stats
Components 28
Raw inputs
Kaggle-derived CSV sources feeding the warehouse.
- Raw CSVs · Kaggle-derived anime, ratings, watch-history, and synopsis inputs.
EDA
Notebook-based exploration and data profiling.
- EDA notebooks · Exploratory notebooks for metadata, ratings, and review analysis.
notebooks
dbt warehouse
DuckDB-backed dbt transformation pipeline.
- DuckDB · Local analytical execution engine used by dbt.
- dbt project · Project configuration, profiles, models, sources, and tests.
dbt_project/dbt_project.yml - Staging · Cleanup and normalization boundary over raw sources.
dbt_project/models/staging - Intermediate · Reusable feature engineering and business logic layer.
dbt_project/models/intermediate - Marts · Consumption-ready analytical outputs.
dbt_project/models/marts - Anime stg · Normalize anime metadata and types.
dbt_project/models/staging/stg_anime.sql - Ratings stg · Clean rating records and validate values.
dbt_project/models/staging/stg_ratings.sql - List stg · Normalize user watch-history and status mapping.
dbt_project/models/staging/stg_animelist.sql - Synopsis stg · Prepare synopsis text for analysis.
dbt_project/models/staging/stg_anime_synopsis.sql - Anime stats · Reusable content metrics such as completion and drop rates.
dbt_project/models/intermediate/int_anime_stats.sql - User profiles · Derived user tiers and engagement features.
dbt_project/models/intermediate/int_user_profiles.sql - Genre metrics · Genre popularity and time-oriented aggregates.
dbt_project/models/intermediate/int_genre_metrics.sql - Content perf · Business-facing content performance dataset.
dbt_project/models/marts/mart_content_performance.sql - User segments · Business-facing audience segmentation dataset.
dbt_project/models/marts/mart_user_segments.sql - Genre trends · 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 · Human- and model-readable definitions over marts.
semantic_layer - Glossary · Bilingual business terms and definitions.
semantic_layer/business_glossary.yml - Metrics · Metric definitions for analytics consumption.
semantic_layer/metric_definitions.yml - Query patterns · Reusable query behavior and prompt guidance.
semantic_layer/query_patterns.yml - Table defs · Semantic descriptions of tables and columns.
semantic_layer/table_definitions.yml
LLM eval
Golden dataset and text-to-SQL validation loop.
- Golden set · Curated NL questions and validated SQL pairs.
golden_dataset - Questions · Curated natural-language benchmark questions.
golden_dataset/questions.yml - Golden SQL · Validated target SQL for evaluation.
golden_dataset/golden_queries.sql - Eval runner · Gemini-based schema-only vs semantic-layer evaluation.
golden_dataset/llm_evaluation/evaluate.py
Docs
Architecture and lineage documentation.
- Lineage docs · Documented warehouse flow and design.
docs/data_lineage.md - DW design · Warehouse design and layering notes.
docs/dw_design.md
Connections 31
- 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.