Worksheet profiling and analytics

Four routes read a worksheet table without you writing SQL: enriched schema, sampled rows, a per-column profile, and distinct values with counts. All four are GET, all four operate on a table that already exists in the worksheet, and none of them go through the entity engine — there is no access rule, no row-level security and no field protection on a worksheet table. The only boundary is the tenant that created the worksheet.

Method	Path	Returns
GET	/data/worksheets/{worksheet_id}/tables/{table_name}/schema	Per-column type, nullability, null count, distinct count, sample values, min/max
GET	/data/worksheets/{worksheet_id}/tables/{table_name}/sample	Rows, with a selection strategy
GET	/data/worksheets/{worksheet_id}/tables/{table_name}/analytics/{column_name}	One column’s statistics, dispatched by inferred kind
GET	/data/worksheets/{worksheet_id}/tables/{table_name}/columns/{column_name}/distinct	Distinct values with occurrence counts

data.svc registers these at /worksheets/…; the paths above are the public form, with the /data/ prefix the gateway strips before forwarding.

What applies to all four

• Authentication is a JWT, and the tenant key is enforced on every call. A worksheet belonging to another tenant answers 403 worksheet.cpet_violation, not 404.
• Nothing here is rate limited. The per-tenant limiter covers worksheet creation, file upload and POST .../query; the four introspection routes are not counted.
• {column_name} matches case-insensitively on the analytics and distinct routes, and the response echoes the table’s own casing. Table names in the path match exactly.
• type is a raw DuckDB type string (BIGINT, VARCHAR, TIMESTAMP, DECIMAL(10,2)), not a field type from the entity model. The two vocabularies are unrelated.
• Statistics are computed on every request against live table data. Nothing is cached, and a wide table costs several queries per column.

Caution

XLSX ingestion reads every cell as text, so a table built from a spreadsheet has every column typed VARCHAR and profiles as text — no quantiles, no histogram, no min/max on the schema route. CSV ingestion infers real types. To profile spreadsheet columns numerically, CAST them in a POST .../query instead.

Distinct counts: exact below the threshold, estimated above it

Three of the four routes report a distinct count, and all three compute it the same way:

1. Run DuckDB’s APPROX_COUNT_DISTINCT on the column.
2. If the estimate is greater than 10,000, keep it and set the approximate flag to true.
3. If it is between 1 and 10,000, re-run an exact COUNT(DISTINCT …), replace the value, and set the flag to false.
4. If it is 0, the count is 0 and the flag is false.

NULL is excluded from both the estimate and the exact count.

Route	Count field	Flag
…/schema	columns[].distinct_count	columns[].distinct_count_is_approx
…/analytics/{column}	distinct_count	distinct_count_is_approx
…/columns/{column}/distinct	total_distinct	total_distinct_is_approx

Caution

When the flag is true the number is a sketch estimate, not a count. Do not compare it for equality, do not derive a page count from it, and do not use it to decide that a column is a usable grouping key. Every other count on these routes — row_count, null_count, count, true_count/false_count, each values[].count, each histogram and distribution bucket count — is exact.

Table schema

GET /data/worksheets/{worksheet_id}/tables/{table_name}/schema

No parameters.

{
  "table": "orders",
  "row_count": 48213,
  "columns": [
    {
      "name": "order_id",
      "type": "BIGINT",
      "nullable": true,
      "null_count": 0,
      "distinct_count": 48213,
      "distinct_count_is_approx": true,
      "sample_values": ["1001", "1002", "1003", "1004", "1005"],
      "min": "1001",
      "max": "49213"
    },
    {
      "name": "region",
      "type": "VARCHAR",
      "nullable": true,
      "null_count": 12,
      "distinct_count": 4,
      "distinct_count_is_approx": false,
      "sample_values": ["EMEA", "APAC", "AMER", "LATAM"]
    }
  ]
}

Field	Type	Notes
table	string	Echoes the path parameter.
row_count	int	Exact COUNT(*). -1 if the count query fails.
columns[].name	string	Ordinal order, as the table declares it.
columns[].type	string	DuckDB type name.
columns[].nullable	bool	Declared nullability from the information schema.
columns[].null_count	int	Exact count of NULL values.
columns[].distinct_count	int	See the threshold rule above. Excludes NULL.
columns[].distinct_count_is_approx	bool	true only above 10,000.
columns[].sample_values	array	Up to 5 distinct non-null values, unordered. Omitted when empty.
columns[].min / max	any	Numeric and temporal columns only. Omitted otherwise.

Note

nullable is read from information_schema.columns. Worksheet tables are created with CREATE TABLE … AS SELECT, which carries no NOT NULL constraints, so nullable is true for every ingested column regardless of the data. null_count is the field that tells you anything.

Caution

Per-column statistics are computed with one query each and a failure is swallowed, not reported. A column whose statistics query errors comes back with null_count, distinct_count and distinct_count_is_approx at their zero values, and no min, max or sample_values — which is indistinguishable from an empty column. This affects nested types (STRUCT, MAP, LIST) most often.

Sample rows

GET /data/worksheets/{worksheet_id}/tables/{table_name}/sample

Parameter	Values	Default	Effect
n	integer	50	Rows to return. Clamped to 10,000. A value ≤ 0, or one that does not parse, falls back to 50.
strategy	head, random, tail	head	Row selection.
seed	integer	0	Seeds random.
columns	comma-separated names	all columns	Projection.

{
  "table": "orders",
  "strategy": "random",
  "rows": [
    { "order_id": "1042", "region": "EMEA", "amount": 199.5 },
    { "order_id": "2298", "region": "APAC", "amount": 84.0 }
  ],
  "schema": [
    { "name": "order_id", "type": "BIGINT" },
    { "name": "region", "type": "VARCHAR" },
    { "name": "amount", "type": "DOUBLE" }
  ],
  "row_count_returned": 2
}

The schema block here carries only name and type — it is the result-set schema of the sample query, not the enriched schema above.

Strategy semantics:

• head takes the first n rows in storage order.
• tail takes the last n rows and returns them in storage order, not reversed.
• random orders by a hash of the row id combined with seed. It is deterministic: the same seed returns the same rows, and because seed defaults to 0, repeated calls without a seed return an identical sample. Vary seed to get a different draw.

Caution

An unrecognised strategy is not rejected. The sample falls through to head behaviour, but the response echoes the value you sent, so ?strategy=stratified returns head rows labelled "strategy": "stratified".

A columns value naming a column that does not exist fails the underlying query and returns 400 query.execution_error, not column.not_found.

Column analytics

GET /data/worksheets/{worksheet_id}/tables/{table_name}/analytics/{column_name}

Parameter	Values	Default	Effect
histogram_buckets	integer	20	Requested histogram buckets. Numeric columns only. A negative value suppresses the histogram.
top_k	integer	20	Number of most-frequent values. Text columns only. A negative value suppresses the top-values query.
include_quantiles	false	quantiles included	Only the exact string false disables quantiles. Numeric columns only.

Note

histogram_buckets=0 and top_k=0 do not turn the section off — zero is treated as “unset” and reverts to the default of 20. A value that does not parse as an integer is also treated as zero, so top_k=all gives you 20. Only a negative value suppresses: histogram_buckets=-1 drops the histogram key entirely, while top_k=-1 returns "top_values": null — the key stays.

Kind is inferred, not declared

The column’s DuckDB type name decides which statistics block you get. Matching is a case-insensitive substring test in this order:

Kind	Matches when the type name	Examples
boolean	contains BOOL	BOOLEAN
numeric	contains INT, FLOAT, DOUBLE, DECIMAL, NUMERIC or REAL	BIGINT, DECIMAL(10,2), DOUBLE
temporal	contains DATE, TIME or TIMESTAMP	DATE, TIMESTAMP, TIME
text	anything else	VARCHAR, UUID, BLOB, JSON, LIST, STRUCT

Caution

The test is a substring match on the type name, so INTERVAL classifies as numeric and gets numeric aggregates run against it. Types with no meaningful text statistics — STRUCT, MAP, JSON — fall into text and their length statistics are whatever DuckDB’s LENGTH returns for that type, or zeros if it errors.

Response envelope

Every response carries the base block; exactly one of numeric, text, temporal, boolean is present, matching kind.

Field	Type	Notes
column	string	The table’s exact casing.
type	string	DuckDB type name.
kind	string	numeric · text · temporal · boolean.
count	int	Exact COUNT(*) over the table, including nulls.
null_count	int	Exact.
null_percentage	float	null_count / count, a fraction in [0,1] — not a percentage — rounded to 4 decimal places. 0 when the table is empty.
distinct_count	int	See the threshold rule.
distinct_count_is_approx	bool

Caution

null_percentage is named as a percentage and returned as a fraction. A column that is 12.5% null reports 0.125.

Numeric

{
  "column": "amount",
  "type": "DOUBLE",
  "kind": "numeric",
  "count": 48213,
  "null_count": 31,
  "null_percentage": 0.0006,
  "distinct_count": 9840,
  "distinct_count_is_approx": false,
  "numeric": {
    "min": 0.5,
    "max": 10000.5,
    "mean": 213.4471,
    "median": 149.0,
    "stddev": 402.118,
    "quantiles": { "p25": 62.0, "p50": 149.0, "p75": 268.5, "p95": 812.0, "p99": 2140.0 },
    "histogram": {
      "bucket_count": 18,
      "buckets": [
        { "range_min": "0.5", "range_max": "500.5", "count": 44120 },
        { "range_min": "500.5", "range_max": "1000.5", "count": 2981 }
      ]
    }
  }
}

• quantiles keys are exactly p25, p50, p75, p95, p99. The block is omitted when include_quantiles=false.
• min and max carry the column’s own storage type. On BIGINT, HUGEINT and DECIMAL columns they come back as JSON strings, not numbers, to avoid precision loss. On DOUBLE, FLOAT and the smaller integer types they are JSON numbers.
• mean, median, stddev and every quantile are DOUBLE aggregates, so they are JSON numbers — including on a BIGINT or HUGEINT column, where min and max beside them are strings. Only a DECIMAL column renders them as strings.
• histogram.buckets[].range_min and range_max are always JSON strings, whatever the column type.
• Every bucket is (max - min) / histogram_buckets wide — the width comes from the requested bucket count, not from bucket_count.
• Empty buckets are absent. bucket_count is the number of buckets that contain at least one row, so it is usually smaller than histogram_buckets and the array is not a contiguous series.
• histogram is omitted entirely when the column has a single distinct value (min == max), and when histogram_buckets is negative.

Text

{
  "column": "region",
  "type": "VARCHAR",
  "kind": "text",
  "count": 48213,
  "null_count": 12,
  "null_percentage": 0.0002,
  "distinct_count": 4,
  "distinct_count_is_approx": false,
  "text": {
    "top_values": [
      { "value": "EMEA", "count": 21044 },
      { "value": "AMER", "count": 18110 }
    ],
    "avg_length": 4.0,
    "min_length": 4,
    "max_length": 5
  }
}

top_values is ordered by count descending, limited by top_k, and excludes NULL. Length statistics are computed over non-null values only; avg_length is rounded to 2 decimal places.

Temporal

{
  "column": "ordered_on",
  "type": "DATE",
  "kind": "temporal",
  "count": 48213,
  "null_count": 0,
  "null_percentage": 0,
  "distinct_count": 731,
  "distinct_count_is_approx": false,
  "temporal": {
    "min": "2024-01-01",
    "max": "2025-12-31",
    "distribution": {
      "granularity": "month",
      "buckets": [
        { "bucket": "2024-01-01", "count": 1980 },
        { "bucket": "2024-02-01", "count": 2104 }
      ]
    }
  }
}

granularity is always month and is not a parameter. There is one bucket per month that contains at least one row — months with no rows are absent, so the series is not contiguous and a two-year column returns at most 24 buckets. distribution is omitted when the column is entirely null. Bucket labels and min/max are formatted from the column type: DATE as 2006-01-02, TIME as 15:04:05, and TIMESTAMP as RFC 3339 in UTC.

Boolean

{
  "column": "is_paid",
  "type": "BOOLEAN",
  "kind": "boolean",
  "count": 48213,
  "null_count": 40,
  "null_percentage": 0.0008,
  "distinct_count": 2,
  "distinct_count_is_approx": false,
  "boolean": { "true_count": 31900, "false_count": 16273 }
}

Both counts are exact and neither includes NULL; true_count + false_count + null_count == count. There is no histogram or top-k for a boolean column.

Distinct values

GET /data/worksheets/{worksheet_id}/tables/{table_name}/columns/{column_name}/distinct

Parameter	Values	Default	Effect
limit	integer	100	Values returned. Clamped to 10,000. A value ≤ 0, or one that does not parse, falls back to 100.
order	frequency, alpha, value	frequency	frequency is count descending; alpha casts the value to text and sorts lexically; value sorts by the column’s native type. An unrecognised value falls back to frequency.
with_counts	false	counts included	Only the exact string false drops the counts.
search	substring	none	Case-insensitive ILIKE filter on the value cast to text.

{
  "column": "region",
  "total_distinct": 4,
  "total_distinct_is_approx": false,
  "returned": 4,
  "truncated": false,
  "values": [
    { "value": "EMEA", "count": 21044 },
    { "value": "AMER", "count": 18110 },
    { "value": "APAC", "count": 6203 },
    { "value": "LATAM", "count": 844 }
  ]
}

Field	Type	Notes
column	string	The table’s exact casing.
total_distinct	int	Distinct values in the whole column. See the threshold rule.
total_distinct_is_approx	bool
returned	int	Length of values.
truncated	bool	true when more values existed than limit allowed.
values[].value	any	NULL is excluded.
values[].count	int	Exact occurrence count, or -1 when with_counts=false.

Caution

total_distinct ignores search. It is always the distinct count of the entire column, so with a filter applied returned and total_distinct describe different populations and their ratio is meaningless. There is no filtered total.

Note

with_counts=false does not remove the count key — it sets every count to -1. Treat -1 as “not computed”, not as a value.

Errors

All four routes return the standard worksheet error body: {"error": {"code", "message", "details", "request_id"}}.

Code	Status	Raised when
worksheet.not_found	404	Unknown worksheet id, or one more than 5 minutes past expiry — the lookup evicts it and reports it missing.
worksheet.expired	410	The worksheet’s TTL has passed but it is still inside the 5-minute grace window.
worksheet.cpet_violation	403	The worksheet belongs to a different tenant.
table.not_found	404	No such table in this worksheet.
column.not_found	404	No such column, on the analytics and distinct routes.
query.execution_error	400	The underlying statistics or sample query failed — including a bad columns projection.

Note

These four routes do not classify the failure they get back from the engine: every query failure becomes query.execution_error with a 400. A timeout, a syntax problem or an exhausted memory limit hit while profiling all surface under that one code — query.timeout, query.syntax_error and worksheet.memory_exceeded are raised only by POST .../query.

Worksheets live in the memory of one process. A profiling call that returns 404 after previously succeeding means the worksheet’s process restarted or the request reached a different replica — not that the table was dropped.

Continue with

• Worksheets — the create, ingest, define-table and query lifecycle these routes read from.
• Worksheet limits and quotas — the rate limits these routes are exempt from, and every other bound.
• Data API HTTP reference — every route, header and status code in one place.
• Query DSL — profiling and filtering entity data, which is a separate surface with access control applied.