Local Acquisition Store & SQLite
Relevant source files
The following files were used as context for generating this wiki page:
The Local Acquisition Store & SQLite system serves as the primary persistence layer for data acquisition workflows within the regime-detection project. It provides a structured mechanism to track fetch runs, record raw artifacts (text or binary), manage derived outputs, and maintain a lineage of data transformations. By utilizing SQLite, the system ensures local, portable, and ACID-compliant storage for both metadata and actual data content (via blobs or local file paths).
This system is critical for ensuring reproducibility and auditability of the regime detection engine. It tracks the provenance of every data point from its original source (e.g., Federal Reserve minutes, market price feeds) through to the normalized Parquet files used by the engine.
Core Schema Architecture
The database schema is designed to handle a hierarchy of acquisition metadata, starting from high-level "runs" down to specific data "records" and their "lineage."
Entity Relationship Diagram
The following diagram illustrates the primary relationships between the core tables in the acquisition database.
Table Definitions
The schema consists of several key tables that track different aspects of the acquisition lifecycle:
| Table | Purpose | Key Fields |
|---|---|---|
fetch_runs | Tracks individual execution instances of fetchers. | run_id, fetch_type, status, params_json |
artifacts | Metadata for raw data downloaded during a run. | source_name, artifact_kind, content_sha256 |
artifact_blobs | Optional storage for actual binary content of an artifact. | artifact_id, content_bytes |
derived_outputs | Tracks files produced by processing artifacts (e.g., Parquet). | output_kind, path, row_count, min_date, max_date |
artifact_records | A ledger for tracking artifact location and integrity. | name, stage, uri, local_path, content_sha256 |
artifact_lineage | Maps relationships between input and output artifacts. | output_artifact_record_id, input_artifact_record_id, transform_name |
source_checkpoints | Persists cursors for incremental data fetching. | source_name, cursor_key, cursor_value |
Sources: src/regime_data_fetch/acquisition_schema.py:5-84, tests/test_acquisition_schema.py:9-106
Data Lifecycle & Acquisition Store
The AcquisitionStore class provides the API for interacting with the SQLite database. It manages the lifecycle of a fetch run, from initialization to recording artifacts and finishing the run.
Fetch Run Flow
A typical acquisition process follows a structured sequence to ensure data integrity and provenance tracking.
Artifact Storage Options
The system supports two methods for storing artifact content:
- In-Database Blobs: Raw binary data is stored directly in the
artifact_blobstable, linked byartifact_id. - External Artifact Store: Data is uploaded to an external storage provider (configured via
ArtifactStore, such as S3 or a local file tree) and referenced via a URI in theartifact_recordstable.
Sources: src/regime_data_fetch/acquisition_store.py:250-300, tests/test_acquisition_artifact_ledger.py:218-250
Acquisition Consolidation
The consolidation module allows merging multiple local acquisition databases into a single canonical source. This is used when data is fetched across different environments or timeframes and needs to be unified for engine consumption.
Consolidation Logic
When databases are merged, the following logic is applied:
- Run/Artifact Mapping:
fetch_runsandartifactsare imported, and a mapping of old IDs to new IDs is maintained to preserve relational integrity. - Parameter Augmentation: The
params_jsonfor consolidated runs is updated withconsolidated_from_labelandconsolidated_from_dbto maintain metadata about the merge. - Normalized Data Import: Specific data rows (like OHLCV, Event Calendar, or Macro data) are extracted from their specialized tables and inserted into the target database.
Normalized Data Tables
The consolidator explicitly handles a wide array of normalized data types used by the regime engine:
daily_ohlcv_rowsevent_calendar_rowsmacro_rowspmi_rowspit_constituent_rowsfomc_minutes_rowsaggregate_eps_snapshot_rows
Sources: src/regime_data_fetch/acquisition_consolidation.py:26-42
Local Daily OHLCV Storage
A specialized subset of the acquisition store handles Daily OHLCV (Open, High, Low, Close, Volume) data. This data is often stored in a dedicated daily_ohlcv_rows table within the SQLite database to facilitate fast access by the engine without needing to parse individual Parquet artifacts for every query.
Daily OHLCV Schema
| Field | Type | Description |
|---|---|---|
symbol | TEXT | Asset ticker (e.g., SPY) |
date | TEXT | ISO date string |
open / high / low / close | REAL | Price data |
volume | INTEGER | Trade volume |
adjusted_close | REAL | Price adjusted for corporate actions |
source_file | TEXT | Path to the original artifact file |
Sources: src/regime_data_fetch/acquisition_consolidation.py:206-220, src/regime_data_fetch/local_daily_ohlcv_sqlite.py:45-60
Summary
The Local Acquisition Store & SQLite system provides a robust foundation for data persistence and provenance. By combining a comprehensive metadata schema with flexible storage options (SQLite blobs or external artifact stores), the system ensures that every input to the regime detection engine is tracked, hashed, and reproducible. The inclusion of consolidation tools further enables the management of distributed data fetching workflows into a unified canonical dataset.
Sources: src/regime_data_fetch/acquisition_store.py:10-50, src/regime_data_fetch/acquisition_consolidation.py:10-30