How scmosoeu/database-design works
Project Type and Purpose
• This is a Python‐based ETL/data-engineering utility (not a user‐facing web service) that uses Flask + Flask-SQLAlchemy purely to define, normalize, and load data into a relational database in a star/snowflake schema. Its goal is to:
• Read from a source database or files
• Normalize raw data via SQL scripts or ORM models
• Generate a dimensional model (fact & dimension tables) in a target RDBMS
File Structure
• Top level:
• app.py – the orchestrator that runs ingestion jobs
• dbconnector.py / utils.py – handle connections to source/target databases
• models.py – ORM class definitions (fact & dimension tables)
• resources/extensions.py – Flask app & SQLAlchemy initialization
• sql_scripts/ – pure-SQL “initial_ingestion.sql” & “delta_ingestion.sql”
• Patterns:
• Layered code: connection layer (dbconnector/utils), ORM/model layer (resources+models), orchestration layer (app.py), SQL‐script layer.
• Reliance on Flask-SQLAlchemy for metadata management despite no HTTP endpoints.
README for Insights
• Confirms use of Flask/SQLAlchemy for DDL & DML
• Describes fact table (product_sales) and its foreign key links to dimension tables (products, containers, combinations)
• Highlights two ingestion modes: full (“initial_ingestion.sql”) vs. incremental (“delta_ingestion.sql”)
Main Components & Relationships
a. ETL Orchestrator (app.py)
– Loads configuration, picks ingestion mode, calls SQL scripts or ORM sessions.
b. Connection Layer (dbconnector.py, utils.py)
– Establishes connections to source systems and the target RDBMS.
c. ORM Layer (resources/extensions.py + models.py)
– Defines ORM classes, metadata, relationships; creates tables if needed.
d. SQL Script Layer (sql_scripts/)
– Contains pure-SQL routines for initial or delta normalization.
e. Relational Database (external service)
– Stores dimension tables (products, containers, combinations) and the central fact table (product_sales).
f. Documentation/Artifacts (imgs/)
Architectural Patterns & Design Principles
• Layered Architecture: separation of concerns between connection, modeling, orchestration, and raw SQL.
• Star/Snowflake Schema: central fact table with FK links to dimensions.
• Dual Ingestion Paths: full vs. incremental.