# The `.pumapack` file format (PumaQuery, schema 1)
This document describes PumaQuery's backup file in enough detail to **edit an
export** or **generate one from scratch** that restores cleanly. The app opens
the result with no warnings, no renamed tables and no lost rows. It is written
for a reader, human or AI, who has no access to the app's source.
PumaQuery's own file is the **full workspace backup**: every dataset with all
of its rows, every saved query, and a few preferences. It is a UTF-8 JSON file.
The app writes it in two places:
- the topbar **Export** button, as `pumaquery-backup-YYYY-MM-DD.json`;
- Cmd/Ctrl+S, as `pumaquery-backup.json`.
Unlike the other PumaWorx apps, PumaQuery does **not** wrap its backup in the
shared pack envelope. A single `format` key at the top marks the file, and the
workspace sits directly beside it. The file works under a `.json` or a
`.pumapack` extension.
PumaQuery reads it from the topbar **Import** button (or
Cmd/Ctrl+I).
Restoring a backup **replaces the whole workspace**. It never merges into what
is already there. Before it does anything, the app asks:
> This file is a PumaQuery backup containing 2 datasets and 2 saved queries.
>
> Restoring REPLACES your current workspace. Continue?
Answering Cancel leaves everything as it was, with no confirmation message. If you want to
**add** one table instead of replacing everything, see §8.
---
## 1. The short version
If you only read one section, read this one.
1. Write one JSON object with `"format": "pumaquery-backup"` at the top level.
Without that exact value the file is not restored; it is imported as one
ordinary table instead. See §2.
2. Put datasets in `datasets` and saved queries in `savedQueries`. Both are
**objects keyed by id**, not arrays. Each record's `id` equals its key.
3. List every dataset id in `datasetOrder` and every saved query id in
`queryOrder`. A record left out of its order list is not shown in the
sidebar.
4. A dataset is a table: `columns` is an array of `{name, type, nullable}`,
and `rows` is an array of **arrays**, one value per column, in column order.
Rows are never objects.
5. Give every value its real JSON type: numbers as numbers, `true`/`false` as
booleans, `null` for missing. `"42"` and `42` are different values and are
never treated as equal.
6. In a `date` column, write calendar days as `"YYYY-MM-DD"`. See §6.
7. Dataset `name` is the SQL table name. Make names unique (ignoring case) and
use letters, digits and `_`, not starting with a digit. Column names follow
the same rule within a dataset.
8. Set `rowCountFull` to the number of rows you wrote. Timestamps
(`createdAt`, `updatedAt`) are **numbers** of milliseconds since 1970.
9. Every saved query needs an `sql` string, or it is dropped.
10. Check the result against the checklist in §10.
§11 is a complete, valid example you can copy and adapt.
---
## 2. The top-level object
```json
{
"format": "pumaquery-backup",
"schemaVersion": 1,
"exportedAt": "2026-09-24T10:30:00.000Z",
"accent": null,
"datasets": { },
"datasetOrder": [ ],
"activeDatasetId": null,
"savedQueries": { },
"queryOrder": [ ],
"editorBuffer": "",
"activeQueryId": null,
"splitTopPct": 38,
"theme": "dark"
}
```
| Key | Value | Notes |
|---|---|---|
| `format` | `"pumaquery-backup"` | **Required, exactly this string.** It is the only thing that tells the app the file is a backup. |
| `schemaVersion` | `1` | The current schema. Not checked on import, but write it. |
| `exportedAt` | ISO 8601 datetime | Informational. Ignored on import. |
| `accent` | `null` or `"#rrggbb"` | The app's accent color. See below. |
| `datasets` | object | Dataset records keyed by id. See §4.1. |
| `datasetOrder` | array of dataset ids | Sidebar order. See §3. |
| `activeDatasetId` | dataset id or `null` | The dataset selected in the sidebar. |
| `savedQueries` | object | Saved query records keyed by id. See §4.4. |
| `queryOrder` | array of query ids | Sidebar order of saved queries. |
| `editorBuffer` | string | The text in the SQL editor. |
| `activeQueryId` | query id or `null` | The saved query currently loaded in the editor. |
| `splitTopPct` | number, 15 to 80 | Height of the editor pane, as a percentage. |
| `theme` | `"dark"` or `"light"` | Color theme. |
What the importer actually requires:
- **`format` is exactly `"pumaquery-backup"`.** If it is missing or different,
there is no error: the file is read as generic JSON and becomes a new
one-row table named after the file (see §9).
- **The file is valid JSON.** Invalid JSON is not reliably refused either. A
file on one line fails with *"Could not import "name.json": JSON: ..."*,
but a pretty-printed file with a syntax error (a trailing comma, a missing
closing brace) is re-read line by line and usually lands as a small
one-column table named after the file, with a column called `value`.
- **No column entry is `null`.** A `null` in a `columns` array aborts
the restore with *"Backup is not a valid PumaQuery snapshot."* and nothing
changes.
Everything else is repaired or dropped quietly rather than refused. §3 and §4
say what happens to each field.
On success the app says *"Restored — 2 datasets, 2 queries"* (the counts are
the lengths of `datasetOrder` and `queryOrder` after cleaning, with "1
dataset" and "1 query" in the singular).
Every key not listed in this document is dropped on restore, at the top level
and inside records alike.
### `accent`
`null` leaves the user's accent color alone. A hex color such as `"#5b8af0"`
becomes the user's accent. The app's own palette is `#4ab874` (the default
green), `#5b8af0`, `#5ecc94`, `#d4a464`, `#e05050`, `#a880e8`, `#4ec9b0` and
`#c8b830`; any other `#rgb` or `#rrggbb` value is accepted as a custom color.
When generating a file, write `null`.
---
## 3. Workspace keys
| Key | What the importer does |
|---|---|
| `datasetOrder` | Ids that match no dataset are removed. If it is not an array, every dataset is listed, in the order of the keys in `datasets`. |
| `activeDatasetId` | If it matches no dataset, the first dataset in `datasetOrder` is selected (or none). |
| `queryOrder` | Same rules as `datasetOrder`, against `savedQueries`. |
| `activeQueryId` | If it matches no saved query, it becomes `null`. |
| `editorBuffer` | Anything that is not a string becomes `""`. |
| `splitTopPct` | Anything that is not a number from 15 to 80 becomes `38`. |
| `theme` | `"light"` gives the light theme; any other value gives dark. If the user has already picked a theme in this browser with the theme button, **their choice wins** over the file. |
**A record missing from its order list is still restored, but hidden.** A
dataset that is in `datasets` but not in `datasetOrder` is not shown in the
sidebar and not counted in the success message, yet a query can still read
it by name. Always list every id.
---
## 4. Record shapes
### Conventions for every record
- **Ids** are any non-empty strings, unique within their object. The app
generates ids like `ds_k3j9x0a2m1qz` and `q_7fh2k9d0p4ab`. Short readable
ids (`ds_employees`, `q_headcount`) work just as well.
- **The `id` field equals the record's key.** The app addresses records by
key; keep the two identical.
- **Timestamps** (`createdAt`, `updatedAt`) are numbers: milliseconds since
1970-01-01 UTC, e.g. `1789981200000` for 2026-09-21 09:00 UTC. A string
here, even a valid ISO date, is replaced by the moment of the restore.
### 4.1 `datasets` entries
```json
"ds_employees": {
"id": "ds_employees",
"name": "employees",
"sourceFilename": "employees.json",
"sourceFormat": "json",
"columns": [ { "name": "id", "type": "number", "nullable": false } ],
"rows": [ [1001] ],
"sampleOnly": false,
"rowCountFull": 1,
"rowCountInMemory": 1,
"createdAt": 1789981260000
}
```
| Field | Type | Meaning |
|---|---|---|
| `id` | string | Equals the key. |
| `name` | string | **The SQL table name**, and the label in the sidebar. Matched without regard to case. See §5. Not a string: becomes `"untitled"`. |
| `sourceFilename` | string | The file the data originally came from. Shown in the sidebar tooltip. `""` for none. |
| `sourceFormat` | string | Where it came from. The app writes `csv`, `tsv`, `json`, `ndjson`, `xlsx`, `pumapack` or `pumaevd`. Shown in the tooltip; any string is kept. |
| `columns` | array of column objects | See §4.2. **Required.** |
| `rows` | array of arrays | See §4.3. **Required.** |
| `sampleOnly` | boolean | **Ignored on import**; the app works it out from `rowCountFull`. Write `false`. |
| `rowCountFull` | integer | How many rows the dataset really has. Write the length of `rows`. See §6. |
| `rowCountInMemory` | integer | **Ignored on import**; set to the length of `rows`. Write the same number. |
| `createdAt` | number | When the dataset was imported, in milliseconds. |
**A dataset whose `columns` or `rows` is missing, `null` or not an array is
dropped without a message.** The success count then comes out one lower than
you expect.
### 4.2 Column objects
```json
{ "name": "hire_date", "type": "date", "nullable": false }
```
| Field | Values |
|---|---|
| `name` | The column name used in SQL and in the table header. Unique within the dataset, ignoring case. A value that is not a string is converted to one. |
| `type` | `"string"`, `"number"`, `"boolean"`, `"date"`, `"null"` (every cell is `null`) or `"mixed"` (the column holds more than one kind of value). **Any other value becomes `"string"`.** |
| `nullable` | `true` if at least one cell is `null`, otherwise `false`. |
**The type is trusted, not checked.** The importer does not look at the cells
to confirm the type you wrote, and it does not convert cells to match it. The
type decides the column's type badge, how the header filter reads what is typed
into it (`>10` on a `number` column, `true` on a `boolean` one, a date prefix on
a `date` one), and the Pivot defaults. A `number` column full of strings is labelled
numeric, but a filter such as `>10` matches none of its cells. Make each type agree with its cells.
The one exception is `date`: see §6.
### 4.3 Rows and cell values
```json
"rows": [
[1001, "Dave Foster", 1, "Engineering Manager", 168000, "2019-05-24", false, null],
[1002, "Karen Quinn", 1, "Senior Engineer", 131000, "2021-12-28", true, 1001]
]
```
- Each row is an **array**, with one cell per column, in the order of
`columns`.
- Row order is the dataset's natural order, which is what a query without
`ORDER BY` returns.
- A row shorter than `columns` is kept, and its missing cells read as `NULL`.
- A **`null` row** breaks the dataset: every query against it fails with
*"Cannot read properties of null (reading 'slice')"*.
Cell values by column type:
| Column type | Write | Notes |
|---|---|---|
| `string` | JSON string, or `null` | Any text. |
| `number` | JSON number, or `null` | Never a numeric string. `"42"` stays a string and never equals `42`. |
| `boolean` | `true` / `false`, or `null` | Never `"true"`, `1` or `"yes"`. |
| `date` | `"YYYY-MM-DD"`, or a full ISO datetime, or `null` | See §6. |
| `null` | `null` | Only for a column with no values at all. |
| `mixed` | any of the above | For a column that genuinely holds more than one kind. |
Nested objects and arrays are not cell values. The app stores them as JSON
text in a `string` column (e.g. `"[\"a\",\"b\"]"`), and so should you.
### 4.4 `savedQueries` entries
```json
"q_headcount": {
"id": "q_headcount",
"name": "Headcount and payroll by department",
"sql": "SELECT d.name AS department, COUNT(*) AS headcount\nFROM employees e\nJOIN departments d ON e.department_id = d.id\nGROUP BY d.name;",
"createdAt": 1790087400000,
"updatedAt": 1790087400000
}
```
| Field | Type | Meaning |
|---|---|---|
| `id` | string | Equals the key. |
| `name` | string | Shown in the sidebar. Not a string: becomes `"untitled"`. |
| `sql` | string | The query text. Use `\n` for line breaks. **Required**: a saved query without a string `sql` is dropped, and the success count says one fewer query. |
| `createdAt`, `updatedAt` | number | Milliseconds since 1970. |
The SQL is stored as written and is not checked on import. A query that names
a table or column that does not exist restores fine, and reports the error
only when it is run. §6 summarizes the SQL the app understands.
---
## 5. Cross-references
| From | Field | To |
|---|---|---|
| top level | `datasetOrder[]` | keys of `datasets` |
| top level | `activeDatasetId` | a key of `datasets`, or `null` |
| top level | `queryOrder[]` | keys of `savedQueries` |
| top level | `activeQueryId` | a key of `savedQueries`, or `null` |
| dataset | `id` | its own key |
| saved query | `id` | its own key |
| saved query | `sql` (table names) | a dataset `name`, matched ignoring case |
| saved query | `sql` (column names) | a column `name` in that dataset, matched ignoring case |
Queries find tables **by name, not by id**. That has two consequences:
- **Two datasets with the same name (ignoring case) cannot both be queried.**
Both restore, both appear in the sidebar, but a query names only one of
them and the other is unreachable from SQL.
- Renaming a dataset in the file does not rewrite the saved queries that use
it. Change both.
Within one dataset, two columns whose names differ only in case also collide:
SQL reaches the first one only.
---
## 6. Values, dates, row counts and SQL
### Dates
In a column of type `date`, the importer turns each string cell into a date:
- `"YYYY-MM-DD"`, e.g. `"2024-08-15"`, is a **calendar day**, taken as
midnight in the time zone of whoever opens the file. Use this for anything
that is a day rather than a moment.
- A full ISO datetime with a zone, e.g. `"2024-08-15T10:23:00Z"` or
`"2024-08-15T10:23:00+02:00"`, is that exact instant. A datetime without a
zone is read as local time.
- `"MM/DD/YYYY"` is also read as a calendar day, but prefer `YYYY-MM-DD`.
- The day must exist. A string that cannot be read as a date, such as
`"24/05/2019"` or `"soon"`, is **kept as text** inside the date column.
- A number in a date column is kept as a number, not read as a timestamp.
The app **writes** every date cell as a UTC instant with milliseconds, because
that is how JSON stores a date. So the same calendar day looks different in
exports made in different places:
| Calendar day in the app | Exported from | Written in the file as |
|---|---|---|
| 24 May 2019 | New York or Chicago (summer) | `"2019-05-24T05:00:00.000Z"` |
| 24 May 2019 | Tokyo | `"2019-05-23T15:00:00.000Z"` |
Both mean "24 May 2019" to the person who exported them. In practice:
- **When editing an export, leave existing date values exactly as they are.**
Do not "correct" a value that seems to be a day early or to carry a time.
- **When adding or changing a calendar day, write `"YYYY-MM-DD"`.** A file
can mix both forms in one column.
- A file restored in a different time zone from the one it was exported in
shifts its calendar days: a day exported in Chicago as
`"2019-05-24T05:00:00.000Z"` shows as `2019-05-23 19:00:00` in Honolulu.
Writing `"YYYY-MM-DD"` avoids this.
### Row counts and samples
`rowCountFull` is the number of rows the dataset really has. After a restore,
a dataset whose `rows` holds fewer rows than `rowCountFull` is flagged as a
sample: the breadcrumb above the results reads **SAMPLE ONLY (4 of 40)** and every query result says
"sample only". A `rowCountFull` lower than the number of rows, or missing, is
raised to the number of rows. So write `rowCountFull` equal to the length of
`rows`, and after deleting rows, lower it to match.
The browser keeps only the **first 200 rows** of each dataset between visits.
A restored dataset is complete until the page is reloaded; after that, larger
datasets hold their first 200 rows and are labelled as samples until the
original file is imported again. The backup file itself always carries every
row. At present the app also labels small, complete datasets **SAMPLE ONLY**
after a reload (for example *SAMPLE ONLY (5 of 5)*); the data in them is
whole.
Files larger than 200 MB are refused with *"File too large"*.
### The SQL in saved queries
PumaQuery runs a single `SELECT` statement:
```
SELECT [DISTINCT] col1, expr AS alias, ...
FROM "table_name" [INNER|LEFT JOIN "other" ON ...]
[WHERE expr]
[GROUP BY expr [, ...]]
[HAVING expr]
[ORDER BY expr [ASC|DESC] [, ...]]
[LIMIT n [OFFSET m]];
```
- Table and column names are matched ignoring case. Quote a name with double
quotes (`"my_table"`); give a joined table an alias (`FROM employees e`).
- Text values take single quotes: `WHERE role = 'Engineer'`. `TRUE`, `FALSE`
and `NULL` are keywords.
- Operators: `=`, `!=`, `<`, `<=`, `>`, `>=`, `AND`, `OR`, `NOT`,
`IS [NOT] NULL`, `[NOT] LIKE`, `[NOT] IN (...)`, `[NOT] BETWEEN ... AND ...`,
`+ - * / %`, and `||` for joining text. `=` never converts types.
- Aggregates: `COUNT`, `SUM`, `AVG`, `MIN`, `MAX`.
- Functions: `lower`, `upper`, `length`, `trim`, `ltrim`, `rtrim`, `substr`
(or `substring`), `replace`, `concat`, `round`, `floor`, `ceil` (or
`ceiling`), `abs`, `coalesce` (or `ifnull`), `date`, `year`, `month`, `day`,
`hour`, `minute`, `second`, plus `CASE WHEN ... THEN ... [ELSE ...] END` and
`CAST(x AS type)`.
- There are no subqueries, window functions, or `RIGHT`/`FULL` joins.
---
## 7. Editing an existing export
An export is a complete description of the workspace, so the safest edit is a
small one.
**Preserve exactly:**
- every id, and every key in `datasets` and `savedQueries`;
- `createdAt` and `updatedAt` (numbers);
- existing date cells, even ones that look a day off (§6);
- `sourceFilename` and `sourceFormat`;
- the column order, and the position of each cell within its row.
**Keep in step when you change something:**
- Adding or removing a column: add or remove the column object **and** the
cell at the same position in every row.
- Adding or removing rows: set `rowCountFull` (and `rowCountInMemory`) to the
new number of rows.
- Adding a dataset or saved query: add its id to `datasetOrder` or
`queryOrder`.
- Renaming a dataset or column: update every saved query and the
`editorBuffer` that use the old name.
- Changing a saved query's `sql`: set its `updatedAt` to a later number.
**The app recomputes, so your value does not matter:** `sampleOnly`,
`rowCountInMemory`, `exportedAt`.
**Never do these:** change `format`; wrap the file in another object or in a
PumaWorx pack envelope; turn rows into objects; turn `datasets` or
`savedQueries` into arrays; write a number or boolean as a string.
When the file is restored, it **replaces** everything in the user's current
workspace, including anything they did after the export was made.
---
## 8. Other files PumaQuery reads
Import accepts more than backups. These are **not** restores: each one adds a
new dataset beside the existing ones, with no question asked.
- **A plain data file** (`.json`, `.jsonl`/`.ndjson`, `.csv`, `.tsv`,
`.xlsx`). The table is named after the file, and column types are worked out
from the values. For JSON, write an **array of objects**, one object per
row: `[{"id": 1, "name": "Ada", "joined": "2024-08-15"}]`. An object whose
only array-valued key holds the rows also works. Nested objects become
dotted columns (`team.name`); arrays become JSON text. The app says
*"Imported "people" — 2 rows × 6 cols"*. This is the simplest way to hand
PumaQuery one new table.
- **Other PumaWorx apps' `.pumapack` files.** These unpack into several
read-only tables, one per kind of record. PumaQuery never writes them back.
Their format is described by each app's own reference.
The file saved by **Export & close** when a dataset is deleted
(`{"schemaVersion": 1, "name": ..., "columns": [...], "rows": [...]}`) is
currently **not** read back as that dataset: it imports as a single row. To
move one dataset, put it in a backup, or write it as an array of objects.
---
## 9. Things that go wrong
| Mistake | What happens |
|---|---|
| `format` missing, misspelled, or not `"pumaquery-backup"` | No restore and no error. The whole file becomes a new one-row table named after the file. |
| The backup wrapped in a pack envelope (`{"puma": ..., "data": {...}}`) | Same: a one-row table, no restore. |
| Pretty-printed JSON with a syntax error | No restore. Usually a small one-column table (`value`) named after the file. |
| A `null` inside a `columns` array | *"Backup is not a valid PumaQuery snapshot."* Nothing changes. |
| `columns` or `rows` missing, `null` or not an array | That dataset is silently dropped. |
| A `null` row | Restores, but every query on that dataset fails. |
| A row with fewer cells than columns | The missing cells read as `NULL`. |
| A column `type` the app does not know, e.g. `"integer"` | Becomes `"string"`. The cells are unchanged. |
| A column type that disagrees with its cells | Kept. Header filters and type badges then mislead. |
| `"42"` in a `number` column, `"true"` in a `boolean` one | Kept as text. It never equals `42` or `true`. |
| A date as `"24/05/2019"`, or as a number | Kept as text or number, not a date. `year()` on the text returns `NULL`. |
| `createdAt` or `updatedAt` as a string | Replaced with the time of the restore. |
| A saved query without `sql` | Dropped. The success message counts one fewer. |
| An id missing from `datasetOrder` | The dataset is restored but hidden from the sidebar and the count. |
| Two datasets with the same name | Both restore; SQL can reach only one of them. |
| `rowCountFull` larger than the number of rows | Labelled **SAMPLE ONLY (n of m)**. |
| `id` different from the record's key | Kept as written. The key is what the app uses. |
| `activeDatasetId` / `activeQueryId` pointing nowhere | First dataset selected; no query loaded. |
| `splitTopPct` outside 15 to 80 | Reset to 38. |
| A dropped file (dragged onto the window) | Currently processed twice, so the "REPLACES your current workspace" question appears twice. Use the Import button instead. |
| Answering Cancel to the question | Nothing changes. No confirmation message. |
---
## 10. Checklist before handing a file over
A file that passes all of these restores with no warnings and nothing
repaired.
**Structure**
- [ ] One JSON object, valid JSON, with `"format": "pumaquery-backup"` and
`"schemaVersion": 1` at the top level.
- [ ] `datasets` and `savedQueries` are objects keyed by id; each record's
`id` equals its key.
- [ ] No `null` where an object or array belongs.
**Datasets**
- [ ] Every dataset has `columns` (array of `{name, type, nullable}`) and
`rows` (array of arrays).
- [ ] Every row has exactly one cell per column, in column order.
- [ ] Every `type` is one of `string`, `number`, `boolean`, `date`, `null`,
`mixed`, and agrees with the cells.
- [ ] Numbers and booleans are JSON numbers and booleans, not strings.
- [ ] New calendar days in `date` columns are `"YYYY-MM-DD"`; exported date
values were left as they were.
- [ ] `rowCountFull` and `rowCountInMemory` equal the number of rows.
- [ ] Dataset names are unique ignoring case, and are plain identifiers.
- [ ] Column names are unique within each dataset, ignoring case.
**References**
- [ ] `datasetOrder` lists every dataset id, and `queryOrder` every query id.
- [ ] `activeDatasetId` and `activeQueryId` are real ids or `null`.
- [ ] Every table and column named in a saved query exists.
**Values**
- [ ] `createdAt` and `updatedAt` are numbers of milliseconds.
- [ ] Every saved query has a string `sql`.
- [ ] `accent` is `null` unless the user asked for a color.
---
## 11. A complete example
A small workspace: a `departments` table and an `employees` table (with a
date, a boolean and a nullable column) that join on `department_id`, and two
saved queries, one of them loaded in the editor. It restores with no warnings
and reports *"Restored — 2 datasets, 2 queries"*.
```json
{
"format": "pumaquery-backup",
"schemaVersion": 1,
"exportedAt": "2026-09-24T10:30:00.000Z",
"accent": null,
"datasets": {
"ds_departments": {
"id": "ds_departments",
"name": "departments",
"sourceFilename": "departments.csv",
"sourceFormat": "csv",
"columns": [
{ "name": "id", "type": "number", "nullable": false },
{ "name": "name", "type": "string", "nullable": false },
{ "name": "location", "type": "string", "nullable": false },
{ "name": "budget", "type": "number", "nullable": false }
],
"rows": [
[1, "Engineering", "Berlin", 4200000],
[2, "Sales", "San Francisco", 2800000],
[3, "Customer Success", "Austin", 1400000],
[4, "Operations", "Berlin", 1200000]
],
"sampleOnly": false,
"rowCountFull": 4,
"rowCountInMemory": 4,
"createdAt": 1789981200000
},
"ds_employees": {
"id": "ds_employees",
"name": "employees",
"sourceFilename": "employees.json",
"sourceFormat": "json",
"columns": [
{ "name": "id", "type": "number", "nullable": false },
{ "name": "name", "type": "string", "nullable": false },
{ "name": "department_id", "type": "number", "nullable": false },
{ "name": "role", "type": "string", "nullable": false },
{ "name": "salary", "type": "number", "nullable": false },
{ "name": "hire_date", "type": "date", "nullable": false },
{ "name": "remote", "type": "boolean", "nullable": false },
{ "name": "manager_id", "type": "number", "nullable": true }
],
"rows": [
[1001, "Dave Foster", 1, "Engineering Manager", 168000, "2019-05-24", false, null],
[1002, "Karen Quinn", 1, "Senior Engineer", 131000, "2021-12-28", true, 1001],
[1003, "Yara Singh", 1, "Engineer", 104000, "2024-08-03", true, 1001],
[1004, "Luis Ortega", 2, "Account Executive", 97000, "2022-03-14", false, null],
[1005, "Mei Tanaka", 3, "Support Lead", 88000, "2023-01-09", true, null]
],
"sampleOnly": false,
"rowCountFull": 5,
"rowCountInMemory": 5,
"createdAt": 1789981260000
}
},
"datasetOrder": ["ds_departments", "ds_employees"],
"activeDatasetId": "ds_employees",
"savedQueries": {
"q_headcount": {
"id": "q_headcount",
"name": "Headcount and payroll by department",
"sql": "SELECT d.name AS department, COUNT(*) AS headcount, SUM(e.salary) AS payroll\nFROM employees e\nJOIN departments d ON e.department_id = d.id\nGROUP BY d.name\nORDER BY payroll DESC;",
"createdAt": 1790087400000,
"updatedAt": 1790087400000
},
"q_remote_recent": {
"id": "q_remote_recent",
"name": "Remote staff hired since 2022",
"sql": "SELECT name, role, hire_date\nFROM employees\nWHERE remote = true AND year(hire_date) >= 2022\nORDER BY hire_date;",
"createdAt": 1790244900000,
"updatedAt": 1790244900000
}
},
"queryOrder": ["q_headcount", "q_remote_recent"],
"editorBuffer": "SELECT d.name AS department, COUNT(*) AS headcount, SUM(e.salary) AS payroll\nFROM employees e\nJOIN departments d ON e.department_id = d.id\nGROUP BY d.name\nORDER BY payroll DESC;",
"activeQueryId": "q_headcount",
"splitTopPct": 38,
"theme": "dark"
}
```
What the app does with this, as a check on your own reasoning. These results
were produced by restoring this exact file in the app:
- Both datasets are complete: no **SAMPLE ONLY** label, 4 and 5 rows.
- `hire_date` holds real dates. Exporting straight back writes them as UTC
instants (in Chicago in summer, `"2019-05-24"` comes back as
`"2019-05-24T05:00:00.000Z"`); everything else apart from `exportedAt`
comes back unchanged.
- *Headcount and payroll by department* returns Engineering 3 / 403000,
Sales 1 / 97000, Customer Success 1 / 88000. Operations has no employees,
so an inner join leaves it out.
- *Remote staff hired since 2022* returns Mei Tanaka (2023-01-09) and Yara
Singh (2024-08-03). Karen Quinn is remote but was hired in 2021.