Auditing SQL queries from CARTO
When you create a connection in CARTO, it will be used to push down SQL queries, which ultimately power your maps, workflows, applications and all the other data interactions in the platforms.
Each of these queries contains a fingerprint in the form of SQL comments that gives information about the connection, user, and feature that triggered such query. This information can be used to perform audits on performance, data governance, or other use cases.
In short, all SQL queries executed by CARTO contain auditing information within the query about the user, connection, map, component, and/or workflow that triggered it.
CARTO SQL Audit Trail structure
Here's an example of a BigQuery SQL query with its audit trail comments:
/* CARTO/3.0 (GPN:CARTODB_Inc; ACCID:ac_bj3xw0in; USERID:auth0|685ab6bef3767efff1a98e98; CM:maps_api_compute_builder; connectionId:154438ab-84ed-41cb-b833-6b6de56c2a54; mapId:d4596571-611f-4a0a-a0ab-81dc2b27e9f5) */
SELECT s.*
FROM `my-project.my_dataset.osm_pois_usa` s
WHERE geom IS NOT NULL
LIMIT 1
If we break down the SQL Audit Trail, we can find the following information:
CARTO identifier (
CARTO/3.0
): Initial identifier to start the audit trail as generated by CARTOCARTO partner name (
GPN:CARTODB_Inc
): Partner name tag that tells Google/Snowflake/etc the partner, using their partner program structure, that was responsible for this query.Organization ID (
ACCID:ac_bj3xw0in
): Your own organization ID, as found in Settings.User ID (
USERID:auth0|685ab6bef3767efff1a98e98
): The ID of the user that triggered the query. For a map, this would be the viewer of the map, not necessarily the map's owner. You can find the list of user IDs in Activity Data.Metric (
CM:maps_api_compute_builder
): A composed string that describes the component or metric that triggered this query. Its structure is similar to API + method + feature.Connection ID (
connectionId:154438ab-84ed-41cb-b833-6b6de56c2a54
): The ID of the connection that was used to execute the query.Map ID (
mapId:d4596571-611f-4a0a-a0ab-81dc2b27e9f5
): The ID of the Builder map that was used to execute the query.Workflow ID (
workflowId:d4596571-611f-4a0a-a0ab-81dc2b27e9f59
): The ID of the Workflow that was used to execute the query.
The structure could have slight differences depending on your data warehouse provider. Please check manually the audit trails in your logs, and refer to your data warehouse's SQL syntax for precision.
Audit examples
The following examples assume BigQuery syntax and structure, please modify accordingly depending on your data warehouse.
List all queries performed by CARTO
Find out which workflows scan more data
Understand which connections are being used in which maps
Last updated
Was this helpful?