--- title: Create metric SQL model description: Datadog, the leading service for cloud-scale monitoring. breadcrumbs: Docs > API Reference > Experiments --- > For the complete documentation index, see [llms.txt](https://docs.datadoghq.com/llms.txt). {% callout %} # Important note for users on the following Datadog sites: app.ddog-gov.com, us2.ddog-gov.com {% alert level="danger" %} This product is not supported for your selected [Datadog site](https://docs.datadoghq.com/getting_started/site.md). ({% placeholder "user-datadog-site-name" /%}). {% /alert %} {% /callout %} # Create metric SQL model{% #create-metric-sql-model %} {% tab title="v2" %} | Datadog site | API endpoint | | ----------------- | ----------------------------------------------------------------------- | | ap1.datadoghq.com | POST https://api.ap1.datadoghq.com/api/v2/experiments/metric-sql-models | | ap2.datadoghq.com | POST https://api.ap2.datadoghq.com/api/v2/experiments/metric-sql-models | | app.datadoghq.eu | POST https://api.datadoghq.eu/api/v2/experiments/metric-sql-models | | app.ddog-gov.com | POST https://api.ddog-gov.com/api/v2/experiments/metric-sql-models | | us2.ddog-gov.com | POST https://api.us2.ddog-gov.com/api/v2/experiments/metric-sql-models | | uk1.datadoghq.com | POST https://api.uk1.datadoghq.com/api/v2/experiments/metric-sql-models | | app.datadoghq.com | POST https://api.datadoghq.com/api/v2/experiments/metric-sql-models | | us3.datadoghq.com | POST https://api.us3.datadoghq.com/api/v2/experiments/metric-sql-models | | us5.datadoghq.com | POST https://api.us5.datadoghq.com/api/v2/experiments/metric-sql-models | ### Overview Create a metric SQL model. Requires at least one subject type, whose subject_type_id must already exist for the organization (list them with GET /api/v2/experiments/subject-types). The model is created against the organization's warehouse connection, which is resolved server-side. Only customer-defined measures belong in measures. The response provides unique_subject_count_measure_id for each subject type and event_count_measure_id for use in metric aggregations. column_type is required for every measure and property. Certification is read-only and cannot be changed through this endpoint. This endpoint requires the `product_analytics_warehouse_model_write` permission. OAuth apps require the `product_analytics_warehouse_model_write` authorization [scope](https://docs.datadoghq.com/api/latest/scopes.md#experiments) to access this endpoint. ### Request #### Body Data (required) {% tab title="Model" %} | Parent field | Field | Type | Description | | ------------- | ---------------------------------- | -------- | ---------------------------------------------------------------------------------------------------------- | | | data [*required*] | object | Metric SQL model resource to create. | | data | attributes [*required*] | object | Complete column mappings and query used to replace the metric SQL model. | | attributes | date_partition_column | string | SQL column used to partition the source data by date. | | attributes | description | string | Text that explains the metric SQL model. | | attributes | measures | [object] | Measures available from the SQL model's result columns. | | measures | column_name [*required*] | string | SQL result column that contains the measure values. | | measures | column_type [*required*] | enum | Data type of a column in the SQL model. Allowed enum values: `STRING,INTEGER,FLOAT,BOOLEAN,DATE,TIMESTAMP` | | measures | description | string | Description of the measure. | | measures | migration_metadata | | Metadata associated with migration of this resource. | | measures | name | string | Display name of the measure. | | attributes | migration_metadata | | Metadata retained for resources imported from another system. | | attributes | name [*required*] | string | Display name of the metric SQL model. | | attributes | properties | [object] | Property columns exposed by the SQL model. | | properties | column_name [*required*] | string | Name of the SQL result column that supplies this property. | | properties | column_type [*required*] | enum | Data type of a column in the SQL model. Allowed enum values: `STRING,INTEGER,FLOAT,BOOLEAN,DATE,TIMESTAMP` | | properties | description | string | Optional text that explains what this property represents. | | properties | migration_metadata | | Opaque metadata preserved when this property is migrated. | | properties | name [*required*] | string | Name used to identify the property in the model. | | attributes | sql [*required*] | string | SQL query that produces the model's source data. | | attributes | subject_types [*required*] | [object] | Subject types mapped to columns in the SQL model. | | subject_types | column_name [*required*] | string | SQL result column that contains the subject identifier. | | subject_types | subject_type_id [*required*] | string | Identifier of the subject type mapped to this column. | | attributes | timestamp_column [*required*] | string | SQL column that supplies the event timestamp. | | data | id | string | Optional JSON:API resource identifier field. | | data | type [*required*] | enum | Metric SQL models resource type. Allowed enum values: `metric-sql-models` | {% /tab %} {% tab title="Example" %} ```json { "data": { "attributes": { "date_partition_column": null, "description": null, "measures": [ { "column_name": "revenue", "column_type": "STRING", "description": null, "migration_metadata": "undefined", "name": null } ], "migration_metadata": "undefined", "name": "Order facts", "properties": [ { "column_name": "item_type", "column_type": "STRING", "description": null, "migration_metadata": "undefined", "name": "item_type" } ], "sql": "SELECT user_id, order_id, item_type, revenue, created_at FROM analytics.orders", "subject_types": [ { "column_name": "user_id", "subject_type_id": "550e8400-e29b-41d4-a716-446655440010" } ], "timestamp_column": "created_at" }, "id": "string", "type": "metric-sql-models" } } ``` {% /tab %} ### Response {% tab title="201" %} Created {% tab title="Model" %} Response containing the metric SQL model. | Parent field | Field | Type | Description | | ------------- | ------------------------------- | --------- | ----------------------------------------------------------------------------------------------------------- | | | data [*required*] | object | JSON:API resource containing the metric SQL model identity and fields. | | data | attributes | object | Details of the metric SQL model. | | attributes | certified_at | date-time | Time when this resource was certified. | | attributes | created_at | date-time | Time when this resource was created. | | attributes | date_partition_column | string | SQL column used to partition the source data by date. | | attributes | description | string | Text that explains the metric SQL model. | | attributes | event_count_measure_id | string | Read-only measure ID. Pass it as warehouse_metric_measure.id when the metric operation is count. | | attributes | experiment_count | int64 | Number of experiments that reference this resource. | | attributes | is_certified | boolean | Whether this resource has been certified. | | attributes | measures | [object] | Measures available from the SQL model's result columns. | | measures | column_name | string | Name of the SQL result column that supplies this measure. | | measures | column_type | enum | Data type of a column in the SQL model. Allowed enum values: `STRING,INTEGER,FLOAT,BOOLEAN,DATE,TIMESTAMP` | | measures | description | string | Text that explains the measure. | | measures | id | string | ID of the measure. | | measures | migration_metadata | | Metadata retained for resources imported from another system. | | measures | name | string | Display name of the measure. | | attributes | metric_count | int64 | Number of metrics that use this SQL model. | | attributes | migration_metadata | | Metadata retained for resources imported from another system. | | attributes | name | string | Display name of the metric SQL model. | | attributes | properties | [object] | Property columns exposed by the SQL model. | | properties | column_name | string | Name of the SQL result column that supplies this measure. | | properties | column_type | enum | Data type of a column in the SQL model. Allowed enum values: `STRING,INTEGER,FLOAT,BOOLEAN,DATE,TIMESTAMP` | | properties | description | string | Text that explains the measure. | | properties | id | string | ID of the measure. | | properties | migration_metadata | | Metadata retained for resources imported from another system. | | properties | name | string | Display name of the measure. | | attributes | sql | string | SQL query that produces the model's source data. | | attributes | subject_types | [object] | Subject types mapped to columns in the SQL model. | | subject_types | column_name | string | Name of the SQL result column that identifies subjects of this type. | | subject_types | subject_type_id | string | ID of the subject type used by this configuration. | | subject_types | unique_subject_count_measure_id | string | Read-only measure ID. Pass it as warehouse_metric_measure.id when the metric operation is `uniqueSubjects`. | | attributes | timestamp_column | string | SQL column that supplies the event timestamp. | | attributes | updated_at | date-time | Time when this resource was last updated. | | data | id [*required*] | uuid | ID of the metric SQL model. | | data | type [*required*] | enum | Metric SQL models resource type. Allowed enum values: `metric-sql-models` | {% /tab %} {% tab title="Example" %} ```json { "data": { "attributes": { "created_at": "2024-01-01T12:00:00Z", "event_count_measure_id": "550e8400-e29b-41d4-a716-446655440024", "is_certified": false, "measures": [ { "column_name": "revenue", "column_type": "FLOAT", "id": "550e8400-e29b-41d4-a716-446655440021", "name": "revenue" } ], "name": "Order facts", "properties": [ { "column_name": "item_type", "column_type": "STRING", "id": "550e8400-e29b-41d4-a716-446655440023", "name": "item_type" } ], "sql": "SELECT user_id, order_id, item_type, revenue, created_at FROM analytics.orders", "subject_types": [ { "column_name": "user_id", "subject_type_id": "550e8400-e29b-41d4-a716-446655440010", "unique_subject_count_measure_id": "550e8400-e29b-41d4-a716-446655440022" } ], "timestamp_column": "created_at", "updated_at": "2024-01-01T12:00:00Z" }, "id": "550e8400-e29b-41d4-a716-446655440020", "type": "metric-sql-models" } } ``` {% /tab %} {% /tab %} {% tab title="400" %} Malformed body, validation failure, the SQL was rejected by the warehouse validator, or the organization has no warehouse connection. {% tab title="Model" %} API error response. | Parent field | Field | Type | Description | | ------------ | ------------------------ | -------- | ------------------------------------------------------------------------------- | | | errors [*required*] | [object] | A list of errors. | | errors | detail | string | A human-readable explanation specific to this occurrence of the error. | | errors | meta | object | Non-standard meta-information about the error | | errors | source | object | References to the source of the error. | | source | header | string | A string indicating the name of a single request header which caused the error. | | source | parameter | string | A string indicating which URI query parameter caused the error. | | source | pointer | string | A JSON pointer to the value in the request document that caused the error. | | errors | status | string | Status code of the response. | | errors | title | string | Short human-readable summary of the error. | {% /tab %} {% tab title="Example" %} ```json { "errors": [ { "detail": "Missing required attribute in body", "meta": {}, "source": { "header": "Authorization", "parameter": "limit", "pointer": "/data/attributes/title" }, "status": "400", "title": "Bad Request" } ] } ``` {% /tab %} {% /tab %} {% tab title="401" %} Missing or invalid authentication. {% tab title="Model" %} API error response. | Parent field | Field | Type | Description | | ------------ | ------------------------ | -------- | ------------------------------------------------------------------------------- | | | errors [*required*] | [object] | A list of errors. | | errors | detail | string | A human-readable explanation specific to this occurrence of the error. | | errors | meta | object | Non-standard meta-information about the error | | errors | source | object | References to the source of the error. | | source | header | string | A string indicating the name of a single request header which caused the error. | | source | parameter | string | A string indicating which URI query parameter caused the error. | | source | pointer | string | A JSON pointer to the value in the request document that caused the error. | | errors | status | string | Status code of the response. | | errors | title | string | Short human-readable summary of the error. | {% /tab %} {% tab title="Example" %} ```json { "errors": [ { "detail": "Missing required attribute in body", "meta": {}, "source": { "header": "Authorization", "parameter": "limit", "pointer": "/data/attributes/title" }, "status": "400", "title": "Bad Request" } ] } ``` {% /tab %} {% /tab %} {% tab title="403" %} Authenticated caller lacks the product_analytics_warehouse_model_write permission. {% tab title="Model" %} API error response. | Parent field | Field | Type | Description | | ------------ | ------------------------ | -------- | ------------------------------------------------------------------------------- | | | errors [*required*] | [object] | A list of errors. | | errors | detail | string | A human-readable explanation specific to this occurrence of the error. | | errors | meta | object | Non-standard meta-information about the error | | errors | source | object | References to the source of the error. | | source | header | string | A string indicating the name of a single request header which caused the error. | | source | parameter | string | A string indicating which URI query parameter caused the error. | | source | pointer | string | A JSON pointer to the value in the request document that caused the error. | | errors | status | string | Status code of the response. | | errors | title | string | Short human-readable summary of the error. | {% /tab %} {% tab title="Example" %} ```json { "errors": [ { "detail": "Missing required attribute in body", "meta": {}, "source": { "header": "Authorization", "parameter": "limit", "pointer": "/data/attributes/title" }, "status": "400", "title": "Bad Request" } ] } ``` {% /tab %} {% /tab %} {% tab title="409" %} Another metric SQL model in this organization already uses one of these values. {% tab title="Model" %} API error response. | Parent field | Field | Type | Description | | ------------ | ------------------------ | -------- | ------------------------------------------------------------------------------- | | | errors [*required*] | [object] | A list of errors. | | errors | detail | string | A human-readable explanation specific to this occurrence of the error. | | errors | meta | object | Non-standard meta-information about the error | | errors | source | object | References to the source of the error. | | source | header | string | A string indicating the name of a single request header which caused the error. | | source | parameter | string | A string indicating which URI query parameter caused the error. | | source | pointer | string | A JSON pointer to the value in the request document that caused the error. | | errors | status | string | Status code of the response. | | errors | title | string | Short human-readable summary of the error. | {% /tab %} {% tab title="Example" %} ```json { "errors": [ { "detail": "Missing required attribute in body", "meta": {}, "source": { "header": "Authorization", "parameter": "limit", "pointer": "/data/attributes/title" }, "status": "400", "title": "Bad Request" } ] } ``` {% /tab %} {% /tab %} {% tab title="429" %} Too many requests {% tab title="Model" %} API error response. | Field | Type | Description | | ------------------------ | -------- | ----------------- | | errors [*required*] | [string] | A list of errors. | {% /tab %} {% tab title="Example" %} ```json { "errors": [ "Bad Request" ] } ``` {% /tab %} {% /tab %} ### Code Example ##### \## default # \# Use a Personal Access Token or Service Access Token export DD_BEARER_TOKEN="" \# Curl command curl -X POST "https://api.datadoghq.com/api/v2/experiments/metric-sql-models" \ -H "Accept: application/json" \ -H "Content-Type: application/json" \ -H "Authorization: Bearer ${DD_BEARER_TOKEN}" \ -d @- << EOF { "data": { "attributes": { "date_partition_column": null, "description": null, "measures": [ { "column_name": "revenue", "column_type": "FLOAT", "description": null, "name": null } ], "name": "Order facts", "sql": "SELECT user_id, order_id, item_type, revenue, created_at FROM analytics.orders", "subject_types": [ { "column_name": "user_id", "subject_type_id": "550e8400-e29b-41d4-a716-446655440010" } ], "timestamp_column": "created_at" }, "type": "metric-sql-models" } } EOF {% /tab %}