Skip to Content

Datamart APIs

Create a datamart

Pick the tables, columns, joins and tenancy that your metrics query.

POST
/api/v2/data-app/datamarts
curl --request POST https://api.usedatabrain.com/api/v2/data-app/datamarts \  --header "Authorization: Bearer $DATABRAIN_SERVICE_TOKEN" \  --header "Content-Type: application/json" \  --data '{    "name": "Sales",    "datasourceName": "Sales warehouse",    "tableList": [      {        "name": "orders",        "schemaName": "public",        "clientColumn": "customer_id",        "columns": [          { "name": "id" },          { "name": "amount", "allowAggregate": true, "defaultAggregation": "SUM" },          { "name": "created_at", "isApplyTimezone": true }        ]      },      {        "name": "customers",        "schemaName": "public",        "columns": [{ "name": "id" }, { "name": "name" }]      }    ],    "relationships": [      {        "relationshipName": "orders_customers",        "parentTableName": "orders",        "parentColumnName": "customer_id",        "childTableName": "customers",        "childColumnName": "id",        "cardinality": "ManyToOne",        "join": "LEFT JOIN"      }    ],    "tenancySettings": {      "tenancyLevel": "TABLE",      "clientColumnType": "STRING",      "schemaName": "public",      "tableName": "customers",      "tableClientNameColumn": "name",      "tablePrimaryKeyColumn": "id"    }  }'

Auth

A service token. See Authentication.

Authorization

ApiToken
headerAuthorizationBearer <token>

A service token or a data app API token.

Body

Unknown fields are refused. Names are case-sensitive.

namestringrequired

Datamart name, unique in your organization. Other endpoints call it datamartName

datasourceNamestringrequired

Name of a connected datasource.

tableListobject[]required

At least one table.

7 fields in tableList
namestringrequired

Table name, as in the synced schema.

schemaNamestringrequired

Schema of the table. Can be database.schema

columnsobject[]required

At least one column.

11 fields in columns
namestringrequired

Column name, as in the schema. Any name if isCustomColumn is true

aliasstring

Alias.

labelstring

Display name.

isHideboolean

Hide the column. Default false

A calculated column. Default false

sqlstring

Required with isCustomColumn. SQL expression of the calculated column.

The expression is already an aggregate.

Convert this date column to the guest token's timezone. Default false

Allow a default aggregation. A JSON boolean, not a string.

Default: false
defaultAggregationstring | null

ALL, AVG, SUM, COUNT, COUNT_DISTINCT, MAX, MIN, MEDIAN, PERCENTILE_CONT or PERCENTILE_DISC

defaultSortstring | null

ASC or DESC

The table's tenant column, for TABLE tenancy.

labelstring

Display name.

isHideboolean

Hide the table from metric builders. Default false

wildcardstring

Stored with the table.

Joins between the tables.

7 fields in relationships
relationshipNamestringrequired

Name of the join.

parentTableNamestringrequired

One side of the join.

parentColumnNamestringrequired

One side of the join.

childTableNamestringrequired

The other side.

childColumnNamestringrequired

The other side.

cardinalitystring | null

OneToOne, OneToMany, ManyToOne or ManyToMany

joinstring | null

INNER JOIN, LEFT JOIN, RIGHT JOIN or FULL JOIN

How each customer's rows are scoped.

7 fields in tenancySettings
tenancyLevelstringrequired

TABLE (a tenant column), DATABASE (a schema or database per tenant) or MULTI_DATABASE

Required for TABLE. STRING or NUMBER

Default: STRING

Required for TABLE. The table that lists your tenants.

tableNamestring

Required for TABLE. The table that lists your tenants.

Required for TABLE. Column with the tenant name.

Required for TABLE. Column with the tenant id, the value you send as clientId

primaryDatabasestring | null

Default database for DATABASE and MULTI_DATABASE

tableList

The data type comes from the schema. You do not send it.

relationships

Relationships are not checked against tableList. Check the names yourself.

Response

{ "id": "Sales" }

id is the datamart name. To get its UUID, list datamarts.

Errors

Statuserror.codeWhy
400INVALID_REQUEST_BODYA field is missing, unknown or wrong. The name is taken. tableList or columns is empty
400INVALID_REQUEST_BODYA table is not in the schema. hint and details.reason: "SCHEMA_TABLE_NOT_FOUND" say which. Grant access to it, then sync
400INVALID_REQUEST_BODYA column is not in the table: Invalid column names in table. Please verify all column names match the schema.
400DATASOURCE_NAME_ERRORNo datasource has this name
500INTERNAL_SERVER_ERRORThe datamart could not be saved. Leaving out schemaName also ends here

For errors that every endpoint returns, see Authentication.

Next, add a semantic layer.

Example Request
curl -X POST 'https://api.usedatabrain.com/api/v2/data-app/datamarts' \  -H 'Content-Type: application/json' \  -d '{  "name": "Sales",  "datasourceName": "Sales warehouse",  "tableList": [    {      "name": "orders",      "schemaName": "public",      "clientColumn": "customer_id",      "columns": [        {          "name": "id"        },        {          "name": "amount",          "allowAggregate": true,          "defaultAggregation": "SUM"        },        {          "name": "created_at",          "isApplyTimezone": true        }      ]    },    {      "name": "customers",      "schemaName": "public",      "columns": [        {          "name": "id"        },        {          "name": "name"        }      ]    }  ],  "relationships": [    {      "relationshipName": "orders_customers",      "parentTableName": "orders",      "parentColumnName": "customer_id",      "childTableName": "customers",      "childColumnName": "id",      "cardinality": "ManyToOne",      "join": "LEFT JOIN"    }  ],  "tenancySettings": {    "tenancyLevel": "TABLE",    "clientColumnType": "STRING",    "schemaName": "public",    "tableName": "customers",    "tableClientNameColumn": "name",    "tablePrimaryKeyColumn": "id"  }}'
Example Response
{  "id": "Sales"}