How sherman94062/retail-sage works
1. Overall shape
- Retail-SAGE is a local, offline-capable analytics system with three main planes: a data warehouse built in DuckDB, an AI agent that reasons over it, and a presentation/evaluation layer for chat, benchmarking, and tests.
2. Data source and storage boundary
- The underlying dataset is TPC-DS SF1 (and optionally SF100 for larger scale).
- Raw/generated data is created by scripts, stored as DuckDB-accessible data and/or exported Parquet, then queried locally by the rest of the stack. There is no separate server-side database service.
3. Data pipeline
- scripts/01_generate_data.py creates the TPC-DS dataset.
- scripts/02_export_parquet.py supports partitioned Parquet export for larger scale.
- scripts/03_verify_data.py validates the dataset against the TPC-DS benchmark expectations.
- This pipeline feeds the analytical model layer used by dbt and the agent.
4. dbt transformation layer
- The dbt/ project is the warehouse modeling boundary.
- models/staging/ contains 24 source-aligned staging models for the TPC-DS tables.
- models/intermediate/ normalizes and unifies business concepts like sales, returns, customer profile, and item performance.
- models/marts/ exposes query-friendly marts such as fct_sales, fct_returns, dim_customer, dim_item, dim_store, dim_date, plus derived metrics tables like daily_channel_summary and customer_ltv.
5. Semantic layer
- dbt/semantic_models/ defines MetricFlow semantic models for sales, customer, inventory, and channel metrics.
- This layer is the governed metric boundary: it standardizes business definitions so the agent can reason in analytics terms rather than raw table joins.
6. Agent core
- The agent/ package is the autonomous reasoning system.
- agent/agent.py runs the multi-turn Claude tool-use loop and returns a structured AgentResult.
- The agent does not answer from a single SQL generation step; it iterates through search, schema inspection, SQL execution, and refinement until it can synthesize a result.
7. Agent tools and execution
- agent/tools.py defines the tools the model can call, including SQL execution and schema/table inspection behaviors.
- The tools form the execution boundary between natural language reasoning and the local DuckDB warehouse.
- This is the core control loop: the model proposes a step, the tools ground it in data, and the agent continues or finalizes.
8. Semantic memory
- agent/memory.py uses ChromaDB to store and retrieve query history, table descriptions, and column glossary content.
- scripts/04_seed_memory.py populates that memory store.
- The memory layer improves table discovery and reduces cold-start hallucination by giving the agent prior knowledge from the repository’s own schema and past analyses.
9. Context and prompting
- agent/context.py builds schema context for the model from the warehouse and/or metadata.
- agent/prompts.py contains the system prompt and few-shot examples that shape tool use and analytical behavior.
- Together these define the agent’s operating policy: use memory, inspect schema, write SQL, verify results, then answer with specific numbers.
10. User interfaces
- ui/app.py is the Streamlit UI for interactive chat.
- The UI is not just a chat box; it also surfaces execution trace information like SQL history, token usage, cost, and progress messages, which makes the agent’s reasoning inspectable.
11. Benchmarking and quality control
- agent/benchmark.py and scripts/05_run_benchmark.py evaluate the agent against the full 99-query TPC-DS benchmark.
- This creates a closed-loop quality system: data generation and transformations are verified, then the agent is tested on real analytical questions against ground truth.
12. Tests and engineering boundaries
- tests/ covers tool behavior, memory CRUD/search, and prompt construction.
- The repository cleanly separates concerns into: data preparation, warehouse modeling, semantic memory, agent orchestration, and UI/evaluation.
- That separation is what you’d want to reflect in an architecture diagram: data flows left-to-right from generation to DuckDB/dbt/semantic models, then into memory and agent reasoning, then out through CLI/UI and benchmark/test harnesses.
Main entry points: agent.py Reasoning loop · 01_generate_data.py Generate data · tests Test suite · marts Retail marts
Components 18
Data pipeline
Generate, export, and verify the local TPC-DS dataset.
- Generate data · Creates the TPC-DS dataset locally.
scripts/01_generate_data.py - Export Parquet · Partitioned Parquet export for larger-scale local use.
scripts/02_export_parquet.py - Verify data · Validates generated data against TPC-DS expectations.
scripts/03_verify_data.py
Warehouse
dbt modeling and semantic metric definitions over DuckDB-accessible data.
- Staging models · Source-aligned models for TPC-DS tables.
dbt/models/staging - Intermediate models · Normalizes sales, returns, customer, and item concepts.
dbt/models/intermediate - Retail marts · Query-friendly facts, dimensions, and derived metrics.
dbt/models/marts - Semantic models · Governed metric definitions for analytics terms.
dbt/semantic_models - DuckDB warehouse · Local analytical storage and execution boundary.
Agent core
Reasoning loop, tools, memory, and prompt/context assembly.
- Context build · Builds schema context from warehouse metadata.
agent/context.py - Prompt policy · System prompt and few-shot examples for tool use.
agent/prompts.py - Semantic memory · Stores query history, table descriptions, and glossary terms.
agent/memory.py - Agent tools · Schema inspection and SQL execution boundary.
agent/tools.py - Reasoning loop · Multi-turn Claude tool-use orchestration that returns a structured result.
agent/agent.py - Seed memory · Populates semantic memory from repository knowledge.
scripts/04_seed_memory.py
UX and eval
Interactive app, benchmarking, and quality checks.
- Streamlit app · Interactive chat with execution trace and usage details.
ui/app.py - Benchmark run · Runs the 99-query TPC-DS benchmark.
scripts/05_run_benchmark.py - Agent benchmark · Benchmark logic and scoring for agent performance.
agent/benchmark.py - Test suite · Covers tools, memory, and prompt construction.
tests
Connections 22
- Generate data → DuckDB warehouse: load data · Creates the local analytical dataset consumed by the warehouse.
- Generate data → Export Parquet: optional export · Can be followed by Parquet export for larger-scale use.
- Export Parquet → DuckDB warehouse: serve files · Exports partitioned files that remain queryable by the local stack.
- Generate data → Verify data: validate · Checks generated data against benchmark expectations.
- DuckDB warehouse → Staging models: source refs · Staging models read from the local warehouse source boundary.
- Staging models → Intermediate models: normalize · Source-aligned tables are reshaped into shared business concepts.
- Intermediate models → Retail marts: publish marts · Intermediate logic feeds facts, dimensions, and derived metrics.
- Retail marts → Semantic models: define metrics · Marts underpin governed metric definitions.
- Semantic models → Context build: schema context · Semantic and schema metadata inform the agent's context building.
- Retail marts → Context build: table metadata · Warehouse structure is surfaced to the agent for reasoning.
- Context build → Prompt policy: assemble prompt · Context is combined with prompt policy before tool use.
- Prompt policy → Reasoning loop: guide behavior · Prompting shapes the multi-turn reasoning loop.
- Semantic memory → Reasoning loop: retrieve knowledge · Prior schema and query history are consulted during analysis.
- Seed memory → Semantic memory: populate · Seeds the memory store with repository-specific knowledge.
- Reasoning loop → Agent tools: call tools · The agent iterates through schema inspection and SQL execution.
- Agent tools → DuckDB warehouse: run sql · Tools execute against the local warehouse boundary.
- Reasoning loop → Streamlit app: respond · Structured results and traces are surfaced in the interactive app.
- Agent benchmark → Reasoning loop: score agent · Benchmarking exercises the agent on TPC-DS questions.
- Benchmark run → Agent benchmark: invoke · Script entrypoint runs the benchmark harness.
- Test suite → Agent tools: verify behavior · Tests cover tool execution and schema inspection.
- Test suite → Semantic memory: verify behavior · Tests cover memory CRUD and retrieval.
- Test suite → Prompt policy: verify behavior · Tests cover prompt construction and policy.