EDGE Bet Model dashboard

NRL Bucket-First Local Architecture

Summary

  • Use Scrapy -> DigitalOcean Spaces -> DuckDB -> DBeaver for the next 2 weeks.
  • Do not introduce dbt in v1. Keep transforms as plain DuckDB SQL so you can observe feed behavior with minimal moving parts, then promote to dbt-duckdb later if the schemas settle.
  • Treat the four workbook-backed datasets as the initial contract: draw, ladder, players, stats.
  • Use a bucket GUI for convenience, not as a dependency. Recommended pairing: DBeaver for SQL and Cyberduck or the DigitalOcean Spaces web console for blob inspection.

Architecture

flowchart LR
    A["Scrapy spiders<br/>draw / ladder / players / stats"] --> B["Shared pipeline sink"]
    B --> C["Local debug exports<br/>CSV / logs"]
    B --> D["Raw bucket zone<br/>html/json/ndjson.gz + manifest"]
    D --> E["DuckDB local stage job"]
    E --> F["Stage bucket zone<br/>partitioned parquet"]
    E --> G["Local DuckDB catalog<br/>views + inventory"]
    G --> H["DBeaver<br/>SQL exploration"]
    D --> I["Bucket GUI<br/>Spaces console / Cyberduck"]

flowchart TD
    A["Now: observe real feed behavior"] --> B["Immutable raw files in bucket"]
    B --> C["DuckDB SQL staging"]
    C --> D["Manual/local runs + run inventory"]
    D --> E["Later, if stable"]
    E --> F["dbt-duckdb for tests, docs, lineage, marts"]

Key Changes

  • In nrl_scraper/pipelines.py, replace the current CSV-only outcome with a shared sink that writes three artifacts for every spider run:
  • raw payload objects: original HTML or source JSON where available
  • canonical extracted records: records.ndjson.gz
  • run manifest: _run.json
  • Keep Output_Sheets/*.csv only as a developer/debug convenience. They should no longer be the system-of-record input to analytics.
  • Add a single local post-scrape stage job, invoked after run_spiders.py, that reads raw records from Spaces and writes Parquet into a stage zone.
  • Add a local DuckDB catalog file and SQL scripts for:
  • stg_nrl_draw
  • stg_nrl_ladder
  • stg_nrl_players
  • stg_nrl_stats
  • ops_nrl_run_inventory
  • ops_nrl_file_inventory
  • Use an immutable object layout:
  • raw/nrl/dataset=<dataset>/load_type=<historical|weekly>/season=<yyyy|unknown>/round=<rr|unknown>/run_date=<yyyy-mm-dd>/run_id=<utc_ts>/
  • stage/nrl/dataset=<dataset>/season=<yyyy|unknown>/round=<rr|unknown>/snapshot_date=<yyyy-mm-dd>/
  • Standardize every extracted row with a metadata envelope: run_id, dataset, scraped_at_utc, source_url, load_type, season, round, schema_version.
  • Standardize every manifest with: run_id, spider_name, dataset, started_at_utc, finished_at_utc, status, record_count, raw_object_keys, stage_object_keys, content_hash, schema_version, error.
  • Add bucket config as explicit env vars:
  • NRL_BUCKET_ENDPOINT
  • NRL_BUCKET_REGION
  • NRL_BUCKET_NAME
  • NRL_BUCKET_ACCESS_KEY
  • NRL_BUCKET_SECRET_KEY
  • NRL_UPLOAD_ENABLED
  • Support exactly two run labels in the runner:
  • historical
  • weekly
  • If season or round cannot be derived, write unknown; never infer values in the pipeline.

Tooling Decisions

  • Recommended now: DuckDB + SQL scripts.
  • Lowest setup cost
  • Excellent fit for Parquet in S3-compatible storage
  • Easy to inspect from DBeaver
  • Defer dbt-duckdb until after the observation period.
  • Add it later if you want model docs, tests, lineage graph, and repeatable marts
  • Keep SQL files one-model-per-file so migration to dbt is straightforward
  • Open-source alternatives worth considering later:
  • SQLMesh: strongest dbt-like alternative if you want SQL modeling without adopting dbt
  • dlt: good if ingestion complexity grows and you want stronger loading abstractions
  • Dagster or Prefect: orchestration only, useful later, unnecessary for local observation mode
  • Bucket GUI recommendation:
  • Not required for correctness
  • Helpful for debugging raw payloads, checking manifests, and downloading spot samples
  • Use Cyberduck or the DigitalOcean Spaces web console rather than building a custom UI

Access Pattern

flowchart LR
    A["Spaces raw objects"] --> B["Cyberduck / Spaces console"]
    C["Spaces staged parquet"] --> D["DuckDB local catalog"]
    D --> E["DBeaver SQL editor"]
    D --> F["Saved SQL views<br/>latest ladder / latest draw / inventory"]
  • Primary analysis path: DBeaver connected to a local .duckdb file that reads stage Parquet directly from Spaces.
  • Primary blob path: Cyberduck or Spaces console for raw HTML/JSON/NDJSON review.
  • Do not query raw blobs directly unless debugging; query staged Parquet through DuckDB.
  • Preserve raw and staged history, but use stg_nrl_*_latest views or ops_nrl_run_inventory.is_latest_run = true when you want deduped current-state analysis.

Test Plan

  • Verify each spider writes raw payloads, canonical ndjson.gz, and a manifest for one run.
  • Verify manifests and raw files share the same run_id and correct dataset labels.
  • Verify the stage job writes partitioned Parquet for each dataset and preserves row counts from manifests.
  • Verify DuckDB can read every staged dataset and expose stg_* views without manual file path edits.
  • Verify schema drift handling:
  • new nullable column is accepted and recorded in manifest schema_version
  • missing required business fields fail the stage job with a clear error
  • Verify DBeaver can query the local DuckDB catalog and that bucket browsing works in the chosen blob UI.

Assumptions

  • The tree you shared is the current repo shape, with spiders in nrl_scraper/spiders and a root-level test_spaces.py indicating DigitalOcean Spaces connectivity.
  • The workbook at C:/Users/mclar/OneDrive/Documents/Personal_Projects/NRL-scrapper-reqs/NRL-requirement-data-structures.xlsx is the initial data contract for Draw, Ladder, Players, and STATS.
  • The goal of this phase is raw + stage first, not a full warehouse or full dbt project.
  • Local execution remains the operating mode during the observation period; no Airflow, Kubernetes, or managed scheduler is introduced yet.