Sentinel Fleet Operations

Overview

Most operational dashboards are built for one audience and bolted onto a second. The result is a business tab that's clean but lacks engineering context, and an engineering tab that's accurate but unreadable by anyone outside the data team.

Sentinel Fleet Operations was designed the other way around: define four distinct operational questions first, then build a data model that answers all four from the same fact layer. The domain simulates an Anduril Sentry surveillance fleet deployed across 10 North American sites in coastal, desert, tundra, plains, and forest environments. The pipeline generates 9 parquet sources (83K rows, deterministic seed) and transforms them through a dbt star schema on DuckDB — 9 staging models, 8 mart models, 66 tests — served through a four-tab Streamlit interface: Operations, Detection Analytics, Reliability, and Pipeline Health.

System Architecture

The build and serve phases are fully decoupled. dbt runs offline; the compiled DuckDB file and dbt artifacts ship with the repository. At runtime, Streamlit reads exclusively from the pre-built database — no source files, no dbt process, no build step in production.

flowchart LR
    A["Python Script\ngenerate_data.py"] -->|"9 parquet files\n83K rows, seed=42"| B[/"data/raw/*.parquet"/]
    B -->|"read_parquet()"| C["dbt-duckdb\n9 staging models"]
    C -->|"materialize"| D["dbt-duckdb\n8 mart models · 66 tests"]
    D -->|"ship as static artifact"| E[("data/sentinel.duckdb\n~14 MB")]
    D -.->|"run_results.json\nmanifest.json"| F[/"dbt artifacts"/]
    E -->|"read-only at runtime"| G["Streamlit App\nstreamlit_app.py"]
    F -->|"parsed at runtime"| G
    G --> H["Tab 1: Operations\nSite leads · mission commanders"]
    G --> I["Tab 2: Detection Analytics\nMission analysts · product engineers"]
    G --> J["Tab 3: Reliability\nReliability engineering"]
    G --> K["Tab 4: Pipeline Health\nData team"]

Star Schema

Two dimension tables anchor the schema. Four fact tables capture distinct operational realities: daily fleet telemetry, active deployments, component reliability against MTBF targets, and inventory stock levels. All four facts are queryable independently, and fct_fleet_health_daily drives both the Operations top-line KPIs and the Reliability uptime trend — numbers stay consistent across tabs without per-tab aggregation logic.

erDiagram
    DIM_SITE {
        string site_id PK
        string site_name
        string region
        string env
        int active_tower_count
        int total_tower_count
    }
    DIM_TOWER {
        string tower_id PK
        string site_id FK
        string status
        int age_days
    }
    FCT_FLEET_HEALTH_DAILY {
        string tower_id FK
        date telemetry_date
        float avg_uptime_pct
        float avg_sensor_health
        int incidents_opened
    }
    FCT_DEPLOYMENT_STATUS {
        string deployment_id PK
        string tower_id FK
        string site_id FK
        float duration_hours
        string status
    }
    FCT_COMPONENT_RELIABILITY {
        string component_id PK
        string component_type
        float observed_mtbf_hours
        float target_mtbf_hours
    }
    FCT_INVENTORY_STATUS {
        string part_number PK
        int available_stock
        string stock_status
        int lead_time_days
    }
    FCT_DETECTION_EVENTS {
        string detection_id PK
        string tower_id FK
        string site_id FK
        string target_class
        float confidence_score
        bool auto_resolved
        string time_of_day
    }
    FCT_ALERT_PIPELINE_LATENCY {
        string detection_id PK
        float detect_to_alert_sec
        float alert_to_notify_sec
        float notify_to_ack_sec
        float ack_to_resolve_sec
        float total_latency_min
    }
    DIM_SITE ||--o{ DIM_TOWER : "hosts"
    DIM_TOWER ||--o{ FCT_FLEET_HEALTH_DAILY : "tower_id"
    DIM_TOWER ||--o{ FCT_DEPLOYMENT_STATUS : "tower_id"
    DIM_TOWER ||--o{ FCT_DETECTION_EVENTS : "tower_id"
    FCT_DETECTION_EVENTS ||--o| FCT_ALERT_PIPELINE_LATENCY : "detection_id"

dbt Model Lineage

Nine staging models — one per parquet source — handle only type casting and column renaming. No business logic lives in staging. The eight mart models join across staging to produce analytics-ready fact and dimension tables. 66 dbt tests cover primary key uniqueness, foreign key not-null constraints, accepted_values on all enum columns (status, severity, stock_status, env, target_class, time_of_day), and referential integrity from staging back to upstream dimensions. Source freshness is configured on telemetry, detections, and alert_pipeline sources with 6-hour warn / 24-hour error thresholds.

flowchart TD
    SP[/"sites.parquet"/] --> SS["stg_sites"]
    TP[/"towers.parquet"/] --> ST["stg_towers"]
    TEL[/"telemetry.parquet"/] --> STEL["stg_telemetry"]
    DP[/"deployments.parquet"/] --> SD["stg_deployments"]
    IP[/"incidents.parquet"/] --> SI["stg_incidents"]
    CP[/"components.parquet"/] --> SC["stg_components"]
    INP[/"inventory.parquet"/] --> SIN["stg_inventory"]
    DETP[/"detections.parquet"/] --> SDET["stg_detections"]
    APP[/"alert_pipeline.parquet"/] --> SAP["stg_alert_pipeline"]
    SS --> DS["dim_site"]
    SS --> DT["dim_tower"]
    ST --> DT
    ST --> FH["fct_fleet_health_daily"]
    STEL --> FH
    SI --> FH
    ST --> FD["fct_deployment_status"]
    SD --> FD
    SC --> FR["fct_component_reliability"]
    SIN --> FIN["fct_inventory_status"]
    SDET --> FDET["fct_detection_events"]
    SDET --> FAP["fct_alert_pipeline_latency"]
    SAP --> FAP
    style FH fill:#2997ff,color:#fff,stroke:#0077ed
    style FD fill:#2997ff,color:#fff,stroke:#0077ed
    style FR fill:#2997ff,color:#fff,stroke:#0077ed
    style FIN fill:#2997ff,color:#fff,stroke:#0077ed
    style FDET fill:#2997ff,color:#fff,stroke:#0077ed
    style FAP fill:#2997ff,color:#fff,stroke:#0077ed

Key Engineering Decisions

Outcome & Reflection

The hardest design problem wasn't the data model — it was figuring out what each persona actually needs from the same underlying facts. Site leads want geographic distribution and open incidents sorted by severity. Reliability engineers want 60-day uptime trends and MTBF actuals versus targets. The data team wants build metadata and source freshness. Three very different views, one star schema.

The static artifact pattern — commit the compiled database, run nothing in production — is a discipline I'd carry into any Streamlit-based analytics application. It eliminates an entire class of deployment failure and makes the build reproducible by definition. The main limitation is refresh cadence: re-building and pushing is a manual step. The natural next iteration is a scheduled GitHub Action that runs the generator and dbt build nightly and commits the updated artifacts automatically.