Dataset Reference

This reference shows practical ways to inspect a DataCat SQLite dataset without reproducing the complete canonical schema specification.

Open a dataset

Open the .db file with a SQLite-compatible client. With the SQLite command-line tool:

sqlite3 -readonly /path/to/datacat_dataset.db

Keep the working dataset read-only. Treat the DataCat-managed source as read-only in every external tool. You can read it while the Job is active and write results to another file or database; stopping the Job is not required for that workflow. Use a SQLite-aware online backup for a live snapshot, or stop the Job before making a normal file-level copy of the complete dataset folder. Never copy only the active .db file because committed data can still reside in its WAL. See Datasets for the full rule.

Then list available tables:

.tables

Indicator-related tables appear only when the Job uses indicators.

Inspect provenance and coverage

SELECT
    dataset_id,
    job_id,
    schema_version,
    provider,
    venue,
    symbol,
    timeframe,
    mode,
    requested_start_utc,
    requested_end_utc,
    history_written_start_utc,
    history_written_end_utc
FROM meta;

Use canonical IDs for matching. Job names and ShortIDs are presentation aids, not durable unique keys.

For a continued dataset:

SELECT
    parent_dataset_id,
    parent_job_id,
    parent_job_name
FROM meta;

The parent columns identify the immediate source, so nested continuation forms a chain.

Read bars in time order

SELECT
    open_time_utc,
    close_time_utc,
    price_open,
    price_high,
    price_low,
    price_close,
    volume_traded,
    trades_count,
    provider_data_status
FROM bars
ORDER BY open_time_ms;

Do not filter out NULL raw values without first checking provider_data_status. A NULL can preserve an expected period whose provider data is pending, omitted, or unavailable because of an error.

Summarize provider-data quality

SELECT provider_data_status, COUNT(*) AS bar_count
FROM bars
GROUP BY provider_data_status
ORDER BY provider_data_status;

To select only durable provider rows:

SELECT *
FROM bars
WHERE provider_data_status = 'final'
ORDER BY open_time_ms;

This is stricter than selecting every row with non-NULL OHLCV because a returned recent bar can still be pending finalization.

Inspect gaps and recovery state

SELECT
    gap_start_open_time_utc,
    gap_end_close_time_utc,
    missing_bars_count,
    reason,
    status,
    next_retry_at_utc,
    attempts
FROM gaps
ORDER BY gap_start_open_time_ms, gap_id;

To find unresolved rows:

SELECT *
FROM gaps
WHERE status <> 'resolved'
ORDER BY gap_start_open_time_ms, gap_id;

meta.gaps_remaining counts persisted non-resolved gap rows. It is a dataset-quality count and can differ from the provider-policy-filtered Open Gaps value shown in the app.

Discover indicator outputs

Do not guess output column names from display labels. Read their deterministic mapping:

SELECT
    indicator_instance_id,
    indicator_id,
    instance_ordinal,
    output_id,
    table_name,
    column_name,
    display_name,
    params_json,
    warmup_bars
FROM indicator_definitions
ORDER BY instance_ordinal, output_ordinal;

One Indicator Instance can have multiple outputs, but all of its outputs remain in the same indicator table.

To list columns in the first output table:

PRAGMA table_info(indicators);

The stable generated pattern is visible in indicator_definitions; always use the persisted table_name and column_name mapping rather than rebuilding it in external code.

Read an indicator value and row state

After locating a column, quote its identifier in SQL:

SELECT
    open_time_utc,
    "i0001_sma__sma" AS value,
    indicator_output_states_json
FROM indicators
ORDER BY open_time_ms;

The state JSON uses one default for most output columns and exception arrays for outputs with another state. Resolve the effective state by checking whether the column appears in an exception; otherwise use default.

When JSON functions are available, inspect the row default directly:

SELECT
    open_time_utc,
    json_extract(indicator_output_states_json, '$.default') AS default_state
FROM indicators
ORDER BY open_time_ms;

Understand NULL indicator values

A NULL is not automatically a calculation error. Use the effective output state:

1. warmup means the instance needs more usable history.
2. awaiting_input means leading pending input cannot yet be normalized.
3. unavailable means required input is unusable.

If a transaction encounters a real calculation or persistence failure, DataCat rolls back the candidate batch rather than committing newer bars with silently stale indicator outputs.

Milliseconds and UTC text

Integer *_ms timestamps are canonical for computation and joins. Human-readable *_utc columns make manual inspection easier. Use integer timestamps for precise programmatic boundaries and UTC text for display or diagnostics.

Reproducible exports

For deterministic exports:

1. Check meta.schema_version.
2. Select explicit columns instead of SELECT * in long-lived integrations.
3. Add an explicit ORDER BY.
4. Filter provider-data and indicator-output states according to your quality policy.
5. Record the Dataset ID and relevant Registry/engine versions with the export.
6. Treat future schema versions as a compatibility event, not an invisible extension of schema 8.