Datasets
A dataset is one table of your business’s data: your orders, a rent roll, the output of a scraper. Each one has a short name (orders), a structure a person approved, and the data loaded into it, kept as numbered versions.
The structure
The structure says what a dataset holds:
- Columns, each with a name, the kind of value it holds and, if you like, what it means in your own words. The kinds are text, whole number, number (a measurement or a coordinate), exact amount (money, with a set number of decimal places), yes/no, date, date and time, and a list or nested value kept whole.
- What identifies each row, if anything: one column, such as an order number, or several together, such as a date and a web address.
- How new data is added by default (see Load modes).
- How often new data is expected, if it’s on a schedule.
- Checks every load must pass, and relationships to other datasets (both below).
- Whether to keep a copy of each original record next to the columns. It’s on unless you turn it off, and it means a field you didn’t make a column for isn’t lost: you can add the column later.
Fields nested inside a record ({"properties": {"mag": 0.7}}) become columns with dotted names (properties.mag). Lists stay whole in one column rather than being split into rows.
Names of datasets, metrics and saved views use lowercase letters, digits and underscores, start with a letter, and are unique across all three in your organization, so “by name” always means one thing.
Proposals and approvals
Your assistant proposes a structure, and Ontyx reads it back in plain words. Nothing is in force until an owner or admin approves it, in the conversation or on the dashboard’s Approvals page. Until a dataset’s first structure is approved, no data can be loaded into it.
Changes work the same way: the assistant proposes a change, Ontyx lists what it changes and anything to watch out for, and a person approves it. One change can wait for approval at a time. A person who says no can give a reason, and the reason is kept so the next proposal can address it.
Every approved structure is a numbered structure version. Each load records the structure it was written under, so old data is always read the way it was meant.
What a change can do
| Change | Allowed? |
|---|---|
| Add a column | Yes. Once data exists, a new column can’t be required, because the rows already loaded have no value for it. |
| Remove a column | Yes, unless it identifies rows. A column that a relationship, an approved metric or a dataset built from this one uses can’t go until that’s dealt with (below). |
| Widen a column’s kind | Whole number to number, exact amount or text; anything to text. |
| Narrow a column’s kind, or change an amount’s decimal places | No. |
| Allow blanks in a column | Yes. |
| Require a value in a column | Only before any data is loaded. |
| Edit descriptions, the refresh schedule, the time zone of a date and time | Yes. |
| Say what identifies each row, on a dataset that has nothing yet | Yes, once the rows already loaded are checked: every row must have a value and no two may repeat. |
| Change what identifies each row once it’s set | No. To identify rows another way, set up a new dataset. |
| Rename a column | Not as such: remove the old column and add the new one. Values don’t carry over, and the proposal warns you. |
| Add, change or remove checks and relationships | Yes, with the rules below. |
A change that would stop an approved metric from working is refused, and the refusal names each metric and why. If you agree to lose those metrics, the assistant proposes the change again naming them, and approving it retires them in the same step. See Metrics and saved views.
Three ways data arrives
All three paths check the data the same way and add it the same way.
- In the conversation. Rows your assistant made, or that you pasted, sent with the message: up to 5,000 rows, or about 2 MB of text, at a time. Bigger sets go in batches.
- A file uploaded through a link. For a file too big to paste, up to 100 MB, your assistant gives you a link to an upload page. Open it, choose the file, then tell the assistant it’s uploaded. The link works for about 30 minutes. CSV, JSON and JSON Lines files are accepted, and the tools reference lists every format.
- From a script. A script or scraper sends data with one of your organization’s push keys, without any conversation. See the Push API.
Every row is checked against the approved structure first: that each value fits its column, that required columns have a value, that what identifies each row is never repeated with different values, and every declared check. A load that breaks a rule is turned away whole, with the reasons in plain words, and nothing is written. (Relationships are the one exception: they’re reported, never enforced. See below.) Fix the rows, or propose a structure change and have it approved, then send the data again.
Values are converted only when nothing is lost: "1.0" in a whole-number column is 1, an amount with more decimal places than its column allows is refused rather than rounded, and a date and time with no time zone is read in the zone the column names (or as UTC).
Load modes
Each load says how it adds its rows, or uses the dataset’s own setting:
| Mode | What it does |
|---|---|
| Replace | The rows sent become the whole dataset. |
| Append | The rows are added to what’s there. On a dataset where something identifies each row, a row that’s already there exactly the same is skipped, so sending the same rows twice adds nothing; a row whose identifier is there with other values is refused, because adding never changes an existing row. On a dataset with nothing that identifies each row, every row is added, even one sent twice. |
| Upsert | A row whose identifier is already there replaces that row, new ones are added, and rows the load doesn’t mention stay. Needs something that identifies each row. |
The answer to every load counts what happened: rows added, rows updated, rows already there exactly the same, and the total.
When an append or upsert finds every row already there exactly the same, no new version is made: the dataset still holds the same data. The one exception is a load that gives a later as-of date, which is recorded so the data counts as up to date.
Versions, as-of dates and freshness
Every load that changes something makes a new data version, numbered one up from the last. Versions never change once written, so you can ask any earlier version again (“what did it say last week?”) and get the same answer it gave then.
A data version and a structure version are different numbers: the data version goes up with every load, the structure version only when a person approves a change. Every answer names which one it means.
Each version has an as-of date: when the data is true of the world. A load can give one (a scraper’s run time, say); otherwise it’s the moment the data was loaded.
If you say how often new data is expected (hourly, daily, weekly, monthly, quarterly or yearly), every answer says whether the data is up to date:
- Up to date while a refresh is on schedule.
- Out of date once the data is more than one and a half periods old (a daily dataset whose data is more than a day and a half old). Out-of-date data is still answered, with a note saying how old it is.
- Unknown for a dataset with no schedule, which is never late.
What identifies each row
A dataset can name the column, or columns together, that identify each row: an order number, a permit number, a date and a web address. This is what lets a load update rows in place, what other datasets point at, and what lets you follow one row over time.
It’s checked on every load: no blanks, and no two rows with the same identifier and different values. You can set it when the dataset is set up, or later, once the rows already loaded are checked to hold it (your assistant can test a choice without proposing anything). Once set, it never changes.
Relationships between datasets
A dataset’s rows can point at rows of another dataset, the way each order line points at its order or each reply at the thread it answers. A dataset can point at its own rows too (an employee at their manager). The pointing dataset declares which of its columns hold the other dataset’s identifiers.
Ontyx checks a relationship when it’s proposed and on every load of either dataset, and says how many rows match: “3 of 5 rows point at a row in “meetings”; 2 point at a row that isn’t there (for example meeting_id=97).” Rows with a blank in those columns aren’t checked, and the answer always says how many there were.
A relationship never blocks a load. Rows that point at a row that isn’t there are loaded and reported, and checked again on the next load. A new relationship is refused only when no row matches at all, which usually means the wrong columns were picked.
Once declared, a question can bring in columns of the dataset it points at, such as each order’s customer name. See Asking questions.
Checks
Checks are rules every load must pass. There are three kinds:
- Never blank: a column always has a value.
- Different on every row: no two rows share the same values in these columns.
- Only values from a list: a column holds only the values you list, such as open, shipped or cancelled. Text must match exactly, capitals included. Blanks pass.
Every check is a gate. A load that fails one is refused, naming the check and the rows, and nothing is written. Adding a check, or taking values off a list, is refused if the rows already loaded don’t pass it, so a check never contradicts the data that’s there. Removing a check, or allowing more values, needs no check of the data.
Every load that lands says what it verified: what identifies each row, the columns that need a value, each check, each relationship it touched, and whether the data is up to date.
Following one row over time
On a dataset where something identifies each row, you can follow one row through its versions: its values in each of the latest 20 data versions, who loaded each version and when, and whether the row was unchanged, changed (which columns, before and after), added or removed. See Lineage and history.
Datasets built from other datasets
A dataset can also be built by Ontyx from a saved question over other datasets, instead of loaded: revenue per month per region, or totals by customer, kept as a table so asking it is quick and every assistant gets the same rows.
- The question is fixed when the dataset is set up. Its columns come from the question’s answer, and a person approves it like any other structure. Approving it builds it.
- It’s rebuilt from the latest data the next time it’s asked after one of the datasets it’s built from has new data, or when you ask for a rebuild. Every answer says what it was built from and whether it’s behind.
- It can’t be loaded directly and its structure can’t be changed: to change the question, set up a new one. It holds up to 250,000 rows.
- The datasets it’s built from can’t be deleted, or changed in a way that would stop its question working, while it exists.
Deleting a dataset
Deleting works like a trash can. Marking a dataset for deletion hides it straight away: it leaves the dataset list and can’t be read, loaded or changed. It’s deleted for good after 7 days, or after the number of days you choose, from 1 to 30. Until then it can be restored exactly as it was. A dataset that’s marked can also be deleted for good right away, which can’t be undone.
Marking is refused while something depends on the dataset: another dataset that points at it, a dataset built from it, or an approved metric that reads it. The refusal names all of them at once and what to do about each.
Its name stays taken until it’s deleted for good.