Skip to main content

AutessaDB Analytics Tables

An analytics table is an AutessaDB table stored in columnar analytics storage and queried with SQL. It is built for the workloads a row table handles poorly: aggregations, group-bys, window functions, and joins over millions to billions of rows. You load data in bulk from files or other tables, ask questions in the SQL Workbench or through agents, and share it with the same access levels as every other table.

What it's for​

  • Reporting over large data. Sales history, event logs, sensor readings, call records, and exports from other systems that are too large or too analytical for row-by-row access.
  • Agents that answer data questions. An agent can look up what a table contains, write a SQL query, run it, and page through the result. A semantic layer (column descriptions, named measures, and verified example queries) makes those answers consistent.
  • Dashboards in custom apps. Apps run the same read-only queries and render the results.

Use a regular table when people edit individual rows, when triggers should fire per row, or when you need semantic search over text. Use an analytics table when data arrives in batches and is mostly read in aggregate.

Open AutessaDB → Tables, click New, and choose Analytics table to create one. Existing analytics tables appear in the tables list with an Analytics badge; filter the list by type to see only them. AutessaDB → SQL Workbench opens the SQL Workbench for querying one or several tables.

A table's detail page stacks its information top to bottom:

  • Header with Open workbench, Load data, Ask, Share, and Edit.
  • Summary with row count, storage size, and when data was last loaded.
  • Schema, including partition columns.
  • Semantic layer: description, measures, joins, and verified queries.
  • Preview of the first 50 rows.
  • Loads and Recent queries.

Key concepts​

Analytics tables are read-only to SQL​

Queries are a single read-only SELECT statement (common table expressions with WITH, subqueries, joins, unions, and window functions are all fine), written in ANSI SQL. Standard SQL works as written; for date and approximate functions use the analytical names, such as date_trunc('month', order_date), approx_distinct(customer_id), and format_datetime(order_ts, 'yyyy-MM'). You change data by loading or appending, never with INSERT, UPDATE, or DELETE in a query. Analytics tables do not support row-by-row edits, triggers, semantic search, or the row grid.

Column types​

When you create a table you choose an analytics type for each column. If you start from a file, types are inferred for you and you can adjust them.

TypeHolds
STRINGText
INT / LONG32-bit / 64-bit whole numbers
DOUBLEFloating-point numbers
DECIMAL(p,s)Exact decimals, for money and quantities
BOOLEANTrue/false
DATECalendar dates
TIMESTAMP / TIMESTAMPTZDate and time, without or with a time zone (stored in UTC)
STRING_LISTA list of text values
JSONNested JSON values

Column names are lowercase snake_case (letters, digits, and underscores, starting with a letter or underscore, up to 63 characters). Every table also gets two system columns: _loaded_at (when the row was loaded) and _load_id (which load added it). There is no built-in id column.

Large integers and DECIMAL values are returned as strings in JSON results so no precision is lost; timestamps are returned as ISO-8601 UTC strings.

When types are inferred from a file, numbers with at most two decimal places (money amounts) are suggested as DECIMAL(18,2) so totals add up exactly. Pick DOUBLE instead if the column is a measurement rather than an amount.

Partitioning​

A partition spec tells the storage how to group rows on disk, so a query that filters on the partition column reads only the matching groups. Choose up to two partition fields, each a column plus a transform:

TransformGroups rows by
YEAR, MONTH, DAY, HOURThe date or timestamp truncated to that unit
IDENTITYThe exact value (good for low-cardinality columns such as region)
BUCKET(n)A hash of the value into n buckets
TRUNCATE(w)A text prefix or number range of width w

The most common choice is the month or day of the main date column. Queries should then filter on that column: WHERE order_date >= DATE '2026-01-01' scans a fraction of what an unfiltered query scans.

SQL name​

Each analytics table has a SQL name, unique within your company, that queries use in FROM and JOIN. It defaults to the table name in snake_case (a table called "Order Lines" becomes order_lines). Queries can reference any analytics tables you have READ access to by their SQL names.

Semantic layer​

The semantic layer is optional metadata that helps people and agents query a table correctly:

  • Description and grain. What one row represents, for example "one row per order line".
  • Column descriptions and synonyms. "net_amount is revenue after discounts; also called net sales".
  • Measures. Named SQL expressions such as revenue := SUM(net_amount), offered as completions in the workbench and listed for agents.
  • Joins. How this table relates to others, for example order_lines.customer_id = customers.id, many-to-one.
  • Verified queries. A question paired with SQL someone has checked. Agents prefer a verified query when a question matches one. Saved queries are re-validated when the schema changes.

Draft it with AI​

On a table's Edit page, click Suggest with AI. Autessa reads the table's columns, statistics, and a 50-row sample, as you are allowed to see them, and drafts a description, what one row represents, column descriptions and other names, measures, example verified queries, and joins. Suggestions are drafts: each one sits next to the current value with a checkbox. Verified queries show whether they passed their check, and you can Test them. Apply selected fills the form; nothing is saved until you click Save semantic layer. By default only empty fields get suggestions. When creating a table from a file, Suggest descriptions with AI drafts the table and column descriptions before you create it. Suggestions do not count toward AI usage.

Queries run as jobs​

Every query is a job that runs asynchronously on the analytics engine. Most queries take a few seconds; large scans take longer. While a query runs, you (or an agent) can wait for it or check back for the result later. Results are paged and can be downloaded as CSV or Parquet. A job can be cancelled while it runs. Results are kept for 7 days.

Before running a heavy query you can estimate it, which reports roughly how much data it would scan without running it.

Access and security​

Analytics tables use the same sharing and access levels as other tables:

LevelOn an analytics table
READDescribe the table, preview it, and run queries
WRITELoad and append data
ADMINChange the schema and semantic layer, share, and delete

A query that joins several tables needs READ on every one of them. A table you cannot read behaves as if it does not exist: the error says "unknown table or no access". Row-level and field-level security apply to queries too: hidden columns cannot be selected, masked columns return masked values, and row rules filter what each person's queries see.

Masked columns​

Mark a text column Masked when you create an analytics table (or add the column later) to store its values encrypted. Masked columns still work in SQL for exact matches and counting: =, <>, IN, IS NULL, GROUP BY, DISTINCT, COUNT(DISTINCT ...), MIN/MAX, joins with other masked columns, and ORDER BY. Sorting follows an internal code rather than the alphabet. Searching inside values (LIKE), ranges, and other functions are not available on masked columns. You cannot partition or sort a table by a masked column, and you cannot turn masking on or off for an existing column.

People see masked values as •••••• unless a database role says otherwise:

  • Visible: the person can reveal values one at a time (eye button) or all on a page (Show values). Every reveal is recorded in the access log.
  • Masked: the person always sees the mask you choose: fully redacted, the last four characters, or a code that looks the same for equal values.
  • Hidden: the column is not there for that person at all.

Table owners and admins can always reveal values. Downloads contain mask text unless you choose with values shown. Masked columns must be enabled for your company.

Limits​

Each plan sets limits on analytics storage, data scanned per query and per day, concurrent queries, query run time (up to 15 minutes), result rows, and data loaded per day. A query whose estimate exceeds the per-query scan limit is refused with a hint naming the partition columns to filter on. See Usage Limits.

How to create an analytics table​

  1. Go to AutessaDB → Tables, click New, and choose Analytics table.
  2. Enter a name and description.
  3. Choose how to define the columns:
    • Start from a file: drop a sample CSV or Parquet file. Columns and types are inferred, a sample of rows is shown, and any problems (mixed types, unparseable dates) are listed as warnings.
    • Define columns: add each column with a name, type, whether it may be empty, and a description.
    • Copy from an AutessaDB table: start from an existing table's columns.
  4. Optionally open Partitioning. The editor suggests a partition, such as "Partition by month of order_date", when your columns include a date.
  5. If you started from a file, leave Load this file after creating checked to load it right away.
  6. Click Create. You land on the table's detail page.

How to load data​

  1. On the table's detail page, click Load data.
  2. Choose a source:
    • Upload file: a CSV or Parquet file from your computer. Large files upload in parts.
    • From AutessaDB table: copy rows from a regular table, mapping its columns to this table's columns.
  3. Choose Append (add the rows) or Replace (swap the table's contents for the new rows).
  4. Click Load. Progress appears in the Loads card; rows that cannot be parsed are reported rather than silently dropped.

Scripts and apps can also append small batches of rows (up to 10,000 rows or 5 MB per call). Appends that arrive close together are committed together every few seconds.

How to change the schema​

Open Edit on the detail page. You can add columns (they are empty for existing rows) and widen a column's type safely, for example INT to LONG. Renaming, removing, and narrowing columns are not supported; add a new column and load into it instead.

How to describe a table for agents​

  1. Open Edit → Semantic layer, or the Semantic layer card on the detail page.
  2. Write the table's description and grain, and describe each column. Add synonyms for the words people actually use.
  3. Add measures for the numbers people ask about, such as revenue, order count, or average handle time.
  4. Add joins to related analytics tables.
  5. Save good queries as verified queries, either here or from the workbench with Save as verified query.

How to let an agent query analytics tables​

  1. Create three tools for the tables the agent should use: ANALYTICS_DESCRIBE, ANALYTICS_QUERY, and ANALYTICS_GET_RESULT. The Create agent tools action on the detail page creates all three, bound to the tables you pick.
  2. Attach them to an agent. The agent describes the tables first, prefers verified queries, filters on partition columns, and pages through long results.

Agents never receive download links; they read results through the tools.