# fakebaconpancakes/instacart-sql-analysis architecture

> Architecture diagram of the GitHub repository fakebaconpancakes/instacart-sql-analysis, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-09-28). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/fakebaconpancakes/instacart-sql-analysis
- Repository: https://github.com/fakebaconpancakes/instacart-sql-analysis

## Overview

Instacart E-Commerce Analytics turns order-history data into retention, merchandising, and fulfillment insights. Its workflow prepares local data, publishes it to MotherDuck, then serves SQL analyses and interactive dashboard results. The graph includes the documented analysis areas; source excerpts confirm the connection helpers and several dashboard interactions, but not every query implementation or dashboard section.

Main entry points: [`src/database.py`](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/src/database.py) (Connection and SQL Helpers), [`app.py`](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/app.py) (Executive Dashboard), [`upload_to_motherduck.py`](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/upload_to_motherduck.py) (MotherDuck Publisher), [`db_setup.py`](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/db_setup.py) (CSV-to-SQLite ETL)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_data_prep["Data Preparation"]
  node_etl["CSV-to-SQLite ETL<br/>[db_setup.py]"]
  node_sqlite[("Local SQLite")]
  node_publisher["MotherDuck Publisher"]
  node_motherduck[("MotherDuck Database")]
end

subgraph group_analytics["Business Analytics"]
  node_segmentation["Customer Segmentation"]
  node_top_products["Top Products<br/>[top_products.sql]"]
  node_product_pairs["Product Pairs<br/>[product_pairs.sql]"]
  node_product_affinity["Product Affinity"]
  node_hourly_demand["Hourly Demand<br/>[hourly_demand.sql]"]
  node_daily_demand["Daily Demand<br/>[daily_demand.sql]"]
end

subgraph group_dashboard["Dashboard Delivery"]
  node_dashboard["Executive Dashboard<br/>[app.py]"]
  node_database_helpers["Connection and SQL Helpers<br/>[database.py]"]
  node_dashboard_results["Tables and Charts<br/>[app.py]"]
end

node_csv_data["Instacart CSVs"]
node_dashboard_user(("Dashboard User"))

node_csv_data -->|"supplies CSVs"| node_etl
node_etl -->|"loads tables"| node_sqlite
node_publisher -->|"reads tables"| node_sqlite
node_publisher -->|"uploads tables"| node_motherduck
node_dashboard_user -->|"uses dashboard"| node_dashboard
node_dashboard -->|"requests SQL and queries"| node_database_helpers
node_database_helpers -->|"runs SQL"| node_motherduck
node_database_helpers -->|"reads SQL"| node_segmentation
node_database_helpers -->|"reads SQL"| node_top_products
node_database_helpers -->|"reads SQL"| node_product_pairs
node_database_helpers -->|"reads SQL"| node_product_affinity
node_database_helpers -->|"reads SQL"| node_hourly_demand
node_database_helpers -->|"reads SQL"| node_daily_demand
node_dashboard -->|"renders results"| node_dashboard_results

click node_etl "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/db_setup.py"
click node_publisher "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/upload_to_motherduck.py"
click node_segmentation "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/customer_segmentation.sql"
click node_top_products "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/top_products.sql"
click node_product_pairs "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/product_pairs.sql"
click node_product_affinity "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/product_affinity.sql"
click node_hourly_demand "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/hourly_demand.sql"
click node_daily_demand "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/daily_demand.sql"
click node_dashboard "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/app.py"
click node_database_helpers "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/src/database.py"
click node_dashboard_results "https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/app.py"

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_etl,node_sqlite,node_publisher,node_motherduck,node_dashboard_user toneBlue
class node_segmentation,node_top_products,node_product_pairs,node_product_affinity,node_hourly_demand,node_daily_demand toneAmber
class node_dashboard,node_database_helpers,node_dashboard_results toneMint
class node_csv_data toneIndigo
```

## Components

### Data Preparation

- [CSV-to-SQLite ETL](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/db_setup.py): `db_setup.py`
- **Local SQLite**
- [MotherDuck Publisher](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/upload_to_motherduck.py): `upload_to_motherduck.py`
- **MotherDuck Database**

### Business Analytics

- [Customer Segmentation](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/customer_segmentation.sql): `queries/customer_segmentation.sql`
- [Top Products](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/top_products.sql): `queries/top_products.sql`
- [Product Pairs](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/product_pairs.sql): `queries/product_pairs.sql`
- [Product Affinity](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/product_affinity.sql): `queries/product_affinity.sql`
- [Hourly Demand](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/hourly_demand.sql): `queries/hourly_demand.sql`
- [Daily Demand](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/queries/daily_demand.sql): `queries/daily_demand.sql`

### Dashboard Delivery

- [Executive Dashboard](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/app.py): `app.py`
- [Connection and SQL Helpers](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/src/database.py): `src/database.py`
- [Tables and Charts](https://github.com/fakebaconpancakes/instacart-sql-analysis/blob/main/app.py): `app.py`

### Other components

- **Instacart CSVs**
- **Dashboard User**

## Connections

- Instacart CSVs → CSV-to-SQLite ETL: supplies CSVs
- CSV-to-SQLite ETL → Local SQLite: loads tables
- MotherDuck Publisher → Local SQLite: reads tables
- MotherDuck Publisher → MotherDuck Database: uploads tables
- Dashboard User → Executive Dashboard: uses dashboard
- Executive Dashboard → Connection and SQL Helpers: requests SQL and queries
- Connection and SQL Helpers → MotherDuck Database: runs SQL
- Connection and SQL Helpers → Customer Segmentation: reads SQL
- Connection and SQL Helpers → Top Products: reads SQL
- Connection and SQL Helpers → Product Pairs: reads SQL
- Connection and SQL Helpers → Product Affinity: reads SQL
- Connection and SQL Helpers → Hourly Demand: reads SQL
- Connection and SQL Helpers → Daily Demand: reads SQL
- Executive Dashboard → Tables and Charts: renders results