Audits
Query Audits allow Admins to gain insight into query sets outside the Single Origin platform, and to identify consolidation opportunities in a user’s external system. Query Audits follow a similar flow to Bulk Imports, but they do not create drafts or entities within the Single Origin platform.
Navigate to the Query Audits page by selecting Manage in the navigation bar and then clicking Audits. From here, administrators can create new query audits and view a history of previously run query audits. Click on a query audit to see more details about that audit.
Prerequisites for a new Query Audit
- Queries must be in an existing collection. To create a collection, see Query Collections.
- Your personal connector must have permission to access all referenced tables
Running a Query Audit
To start a Query Audit, click the "Run Query Audit" button at the top right corner of the Audits tab: https://{tenant}.singleorigin.tech/manage?tab=audits. Give your audit a name, and then select the collection you would like to audit. Once you click Confirm, the audit will start.
The time to process a set of queries varies depending on complexity. Expect an audit of 100 SQL queries to take 1 minute.
Audit Results
Uploaded queries are grouped based on their source table, then by dimension and metric expressions. The results of the query audit include:
- One row per group, where every query in the group references the same common set of tables
- Common formulas in the group & how frequently each formula appears
View the results for a query audit by selecting the desired run in the history table. From here, you can navigate the results in Single Origin or export the results CSV (using the "Actions" button) to explore locally.
Results CSV
The results CSV is returned in pipe form. To view in an app like Google Sheets, use a custom separator on import.
| Column | Description |
|---|---|
| group | INTEGER - each row should have a unique group number, starting at 0 and increasing by 1. |
| queries | ARRAY OF INTEGERS - when an import is processed, a row number is appended to the import starting at 1. If the audit finds similarities between queries, then queriesInGroup represents the row numbers of similar queries. For example, if you upload a CSV and the first and third rows are the same, then queriesInGroup = impor. |
| commonDatasets | ARRAY OF STRINGS - For the queries in the group, what are common datasets? A dataset must appear in all queries in the group to be included in commonDatasets. |
| commonFormulaCount | ARRAY OF STRINGS - For the queries in the group, what are common dimensions and data entities? And how often does the common expression occur? An expression must appear in at least 2 queries in the group to be included in commonFormulaCount. |
| similarity | STRING - EXACT or SIMILAR. Are expressions from commonDatasets and commonFormulaCount exactly the same, or not exactly the same but similar? |
| tags | ARRAY OF STRINGS - For the group queries, what is the unique list of tags associated with the queries? Note that if your source is a Query History Table, then we use the insertId column from BigQuery as the tag for the query. This way, you can find the compiled SQL queries in your logs using a WHERE clause (insertId = 'tag'). |
| sqlMaxLinesInGroup | The longest query in the group (number of lines). |
| totalRowsInCommonDatasets | The number of rows in the list of commonDatasets. To compute this, must have permissions to profile table. |
Currently, Single Origin outputs groups of queries that are exact matches, as well as some metadata on the groups.
Group Results
When looking into an individual group in an audit, you can see:
- common tables referenced by queries in the group
- each SQL query in the group
- common computation logic for the group
For more on common computation logic, see Common Computation.
Bulk Import
After running a query audit, you can directly import queries from the underlying collection into Single Origin. To start a Bulk Import from an audit's detail page, click the "Actions" button at the top right corner of the page and then select "Import Queries."
FAQ
- Why did a query fail to process?
- There are several potential reasons:
- The SQL query is invalid.
- The SQL query is valid, but it references a table in a different project/warehouse than your Single Origin Connector. In this case, we do not have schema metadata for the table.
- Your Single Origin connector has not finished syncing. If there is a sync in process, then we may only have partial schema metadata.
- There are several potential reasons:
- Why does a query in my source not show up in the results?
- In the results table, we only display queries that are similar or exact matches to other queries in the set. If a query is unique (e.g., references a table that no other queries reference), it will not appear in the results table.
- Why do I see "No Results Found" as a result of my audit?
- This can happen for one of two reasons:
- There is no duplication between queries in your set.
- We failed to process some (or all) queries, which led to the appearance of no duplication between queries in your set.
- This can happen for one of two reasons:
- Can I combine the results of multiple query audits?
- At this time, each audit is specific to the source queries provided for that audit. You cannot combine audits from different sets of queries - you would have to provide the entire set of queries into a single audit.
Updated 2 months ago