SQLite dashboard: query an uploaded SQLite file

What this connector is

SQLite in arcodash is an upload, not a connection. In the connector form you pick a .sqlite, .db or .sqlite3 file of up to 25 MB. The upload is checked for the SQLite file signature and opened once to confirm it is readable, then stored. The form shows a preview of the first table.

Queries run in the SQLite dialect against that stored copy. The file is opened read-only and immutable. ATTACH, VACUUM INTO and four process-wide pragmas (the heap limits and the two directory settings) are refused with an error. Everything else works, including CTEs, window functions, json_extract and several ;-separated statements in one query. BLOB columns come back as NULL. There is no host, no credentials and no network path to configure. The general flow for testing and schema introspection is in Connectors overview.

Make a clean snapshot first

An immutable open does not read a write-ahead log. If your application runs SQLite in WAL mode, recent writes can sit in a -wal file beside the database, and a plain copy of the main file misses them. Let SQLite produce the copy:

-- in the sqlite3 shell, on the machine that has the live database
.backup snapshot.db

Upload snapshot.db. It is one consistent file with nothing pending beside it.

What there is to monitor

A SQLite upload is static. Between two uploads nothing about the data changes, so a schedule that runs every 15 minutes re-reads identical bytes and returns the same number. That is fine, but it changes what a check is for.

Use checks to validate each delivery. Someone exports a snapshot, you replace the file, and the next run tells you whether the new snapshot is plausible before anyone builds a report on it. A monitor fires when its state changes, so the alert arrives after the first run on the new file. How that works is in Alerts overview.

Freshness means something narrow here. arcodash does not expose the upload time to SQL, so the only freshness you can measure is the age of the newest row inside the file:

SELECT CAST(julianday('now') - julianday(max(created_at)) AS INTEGER)
         AS days_since_last_order
FROM orders;

Alert on days_since_last_order > 2. In practice this tells you the export you uploaded is old, or that the source stopped writing before it was exported. If the table is empty, max() is NULL, the query returns NULL, and the monitor goes to unknown instead of firing. A volume check next to it covers that case.

Volume and validity checks

The row count is the first check worth having, because a snapshot that exported half the data still looks like a valid database:

SELECT count(*) AS row_count FROM orders;

Monitor row_count with < and a floor just under the last good export. Set it from the last real upload, not a guess.

Orphaned foreign keys are the failure that SQLite itself allows quietly, because SQLite leaves foreign key enforcement off unless the application turns it on:

SELECT count(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

Monitor orphan_orders with > and 0. A plain join is deliberate: it works in any SQLite build and needs no pragma.

A duplicate key check and a sanity check on values round out the set. Both return a single number:

SELECT count(*) AS dup_ids
FROM (SELECT id FROM orders GROUP BY id HAVING count(*) > 1);

SELECT count(*) AS bad_rows FROM orders WHERE total < 0 OR customer_id IS NULL;

Each query reads one row and one numeric column, which is what a monitor needs. The recipes for null rate and duplicates are in Data observability monitors.

Replace the file, then run again

To load a new snapshot, open the connector, use Replace on the file field, upload the new file and save. Queries keep pointing at the same connector, so saved queries, dashboards and monitors carry over. Then run the check queries, or let their schedule pick it up. If you do this weekly, a weekly schedule is enough. Running more often only repeats the same answer.

Dashboard tiles

A snapshot dashboard is a report on one moment, and it is honest about that if the tiles say so. Three that work well:

-- orders and revenue per day
SELECT date(created_at) AS day, count(*) AS orders, round(sum(total), 2) AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

-- revenue by customer, including customers with no orders
SELECT c.name, count(o.id) AS orders, round(coalesce(sum(o.total), 0), 2) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY revenue DESC
LIMIT 10;

-- status mix
SELECT status, count(*) AS n FROM orders GROUP BY status ORDER BY n DESC;

Dates in SQLite are text or numbers, so date(), strftime() and julianday() do the work that date types do elsewhere. Store ISO 8601 text if you control the writer, because date('2026-10-12 08:00:00') parses and '10/12/2026' returns NULL.

To generate freshness, row count, null and duplicate checks for a SQLite table, use the SQLite data check generator.

When this is not the right connector

If the data lives in a server database, connect that database. A SQLite file is the right answer when the data really is a file: a mobile app export, a backup, a local tool’s database, a dataset someone shared. To combine it with another source, save a query on the SQLite connector, then join its cached result against another query through Query Results. arcodash is hosted, so there is no way to point the connector at a file on your own machine; it always works from the upload.

CSV files are the other way to check a local file.

docs

SQLite setup in the docs

Connection fields, an example configuration and caveats.