Create data service layer connecting dbt marts to dashboard #69

New Issue

lmiranda · 2026-01-17T16:09:01Z

lmiranda commented

2026-01-17 16:09:01 +00:00

Summary

Create a service layer that queries the dbt mart tables and provides data to the dashboard callbacks. This abstracts database queries from the UI layer.

Files to Create

File	Purpose
`portfolio_app/toronto/services/__init__.py`	Package init
`portfolio_app/toronto/services/neighbourhood_service.py`	Query functions for neighbourhood data
`portfolio_app/toronto/services/geometry_service.py`	GeoJSON generation for choropleth

Service Functions

NeighbourhoodService

def get_overview_data(year: int) -> pd.DataFrame
def get_housing_data(year: int) -> pd.DataFrame
def get_safety_data(year: int) -> pd.DataFrame
def get_demographics_data(year: int) -> pd.DataFrame
def get_amenities_data(year: int) -> pd.DataFrame
def get_neighbourhood_details(neighbourhood_id: int, year: int) -> dict
def get_rankings(metric: str, year: int, top_n: int = 10) -> pd.DataFrame

GeometryService

def get_neighbourhoods_geojson() -> dict
def get_cmhc_zones_geojson() -> dict

Data Flow

dbt marts (PostgreSQL)
    ↓
services/*.py (SQL queries via SQLAlchemy)
    ↓
callbacks/*.py (transform for Plotly)
    ↓
figures/*.py (render charts)

Acceptance Criteria

Services use SQLAlchemy ORM for queries
Results cached with TTL (avoid repeated DB hits)
All functions return pandas DataFrames or dicts
GeoJSON generation handles PostGIS geometry columns
Error handling for missing data
Unit tests with mock database

Technical Notes

Use geopandas for geometry handling
Consider functools.lru_cache or cachetools for caching
Keep services stateless (no instance state)
Queries should use the mart tables, not raw fact/dim tables

Labels: type:feature, component:backend, priority:high, tech:python, tech:postgresql

## Summary Create a service layer that queries the dbt mart tables and provides data to the dashboard callbacks. This abstracts database queries from the UI layer. ## Files to Create | File | Purpose | |------|---------| | `portfolio_app/toronto/services/__init__.py` | Package init | | `portfolio_app/toronto/services/neighbourhood_service.py` | Query functions for neighbourhood data | | `portfolio_app/toronto/services/geometry_service.py` | GeoJSON generation for choropleth | ## Service Functions ### NeighbourhoodService ```python def get_overview_data(year: int) -> pd.DataFrame def get_housing_data(year: int) -> pd.DataFrame def get_safety_data(year: int) -> pd.DataFrame def get_demographics_data(year: int) -> pd.DataFrame def get_amenities_data(year: int) -> pd.DataFrame def get_neighbourhood_details(neighbourhood_id: int, year: int) -> dict def get_rankings(metric: str, year: int, top_n: int = 10) -> pd.DataFrame ``` ### GeometryService ```python def get_neighbourhoods_geojson() -> dict def get_cmhc_zones_geojson() -> dict ``` ## Data Flow ``` dbt marts (PostgreSQL) ↓ services/*.py (SQL queries via SQLAlchemy) ↓ callbacks/*.py (transform for Plotly) ↓ figures/*.py (render charts) ``` ## Acceptance Criteria - [ ] Services use SQLAlchemy ORM for queries - [ ] Results cached with TTL (avoid repeated DB hits) - [ ] All functions return pandas DataFrames or dicts - [ ] GeoJSON generation handles PostGIS geometry columns - [ ] Error handling for missing data - [ ] Unit tests with mock database ## Technical Notes - Use `geopandas` for geometry handling - Consider `functools.lru_cache` or `cachetools` for caching - Keep services stateless (no instance state) - Queries should use the mart tables, not raw fact/dim tables **Labels:** type:feature, component:backend, priority:high, tech:python, tech:postgresql

lmiranda closed this issue

2026-01-17 16:27:09 +00:00

lmiranda referenced this issue from a commit

2026-01-17 16:49:28 +00:00

feat: Complete Phase 5 dashboard implementation

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: personal-projects/personal-portfolio#69