The Data Normalization Engine lets you load data into Clarity from any system that can export a file. You upload the file, write a short SQL SELECT query that maps its columns to the columns Clarity expects, and save the query. Every later upload of the same report is processed with the saved query automatically.
Use it when Clarity has no connector for a system, but the system can export a CSV, spreadsheet, JSON or XML file.
Before You Begin
You will need:
An Automated CSV application in Clarity for the system the data comes from, and permission to configure that application.
A file exported from the source system, in one of these formats: CSV, TSV, XLSX, JSON or XML.
Files of 10MB or smaller. You can upload up to 5 files at a time.
Note: The legacy .xls Excel format is not supported. Save the file as .xlsx first. For Excel files, Clarity reads the first worksheet only.
Note: If the application is set up to use a custom transformer, the Configure Queries page is not available for it. An application uses either saved queries or a custom transformer, not both.
Open the Data Normalization Engine
Go to Applications.
Open the application you are loading data for.
Click Configure Queries.
Step-by-Step Guide
Step 1: Upload your files
Drag your files onto the upload area, or click Browse to choose them.
Check the files listed under Selected Files.
Click Upload & Parse Files.
Clarity reads each file and lists it under Parsed Source Files. To work with a file you uploaded earlier, choose it under Or Select a previous upload and click Load Temp Table Preview.
A few things to know about how files are read:
Every column is stored as text. Convert values in your query when you need a number or a date.
A column with no header is given a placeholder name based on its position:
COLUMN_A,COLUMN_B, and so on.Characters that are not valid in a column name, such as periods and spaces, are replaced automatically. Use the column names shown in Clarity, not the ones in your file, when you write your query.
XML files are flattened into rows and columns. Nested element names are joined with underscores.
Step 2: Preview your data
On a parsed file, click Show preview to see its rows, or open its schema to see the column names Clarity detected. Confirm the columns and values look the way you expect before you write a query.
Step 3: Continue to the Query Builder
Click Continue to Queries. The Query Builder opens below the upload area.
Step 4: Choose a query type
Open Query Type and choose the kind of record you are building:
Query type | What it produces |
|---|---|
Users | The accounts that exist in the source system |
Entitlements | The roles, groups, permissions or other access the source system tracks |
User-Entitlements | Which user has which entitlement |
Entitlement-Entitlements | Relationships between entitlements, such as a role that contains permissions |
Entitlement-Owners | Who owns each entitlement |
The note under the picker, and the information icon next to it, list the columns that query type must return. A check mark next to a query type means a query is already saved for it.
Step 5: Map your columns
Under Available Tables, open the file you want to query. The editor loads a starter query for the query type you chose.
Edit the starter query so that each line reads from one of your file's columns and uses
ASto give it the name Clarity expects. For example:employee_number AS employee_id.Click a column name under the open table to insert it into the query at the cursor.
Note: The output columns are fixed for each query type. Your query must return every column in the starter query, with the same names, in the same order. If your file has no value for a column, return NULL for it. See Output columns by query type.
Not sure where to start? Click Suggest SQL. Clarity reads the columns in your file and proposes a query for the selected query type. Review and test the suggestion before you save it.
Step 6: Test the query
Click Sample to run the query and see the rows Clarity would receive. Use Preview Limit to choose how many rows are returned (10 to 500). The limit applies to the preview only.
Step 7: Validate
Click Validate. Clarity checks that the query is allowed, and that it returns exactly the columns the query type requires, in the required order. If something is wrong, the message under the editor says what was expected.
Step 8: Save
Click Save. The query is saved for this application and this query type, and is used the next time the application syncs.
Each application has one saved query per query type. Saving again replaces the previous query for that type.
Repeat Steps 4 to 8 for each query type your data supports. Most applications need Users, Entitlements and User-Entitlements.
Output columns by query type
Each query type has a fixed list of output columns. The starter queries below are the ones the Query Builder loads. Replace the source column names with the columns from your own file, and keep every output name and its position.
Users
18 columns, in this order:
Column | Description |
|---|---|
user_identifier | Unique identifier for the user in the source system |
user_name | Username or login |
fullname | Full name |
Email address | |
employee_id | Employee or personnel number |
active_status |
|
status | The status exactly as the source system reports it |
attributes | Additional attributes as a JSON object. The starter query also sets four status keys inside it: |
attributes_hash |
|
permissions | User-level permissions, if any |
permissions_hash |
|
user_data, user_data_hash | Set to |
external_created_at | When the account was created in the source system |
external_deactivated_at | When the account was deactivated |
external_created_by | Who created the account |
external_deactivated_by | Who deactivated the account |
external_last_logon | Last login time |
Starter query:
SELECT
identity_service_identifier AS user_identifier,
user_name,
fullname,
email,
employee_id,
CASE WHEN active IN (1, '1', 'yes', 'true', 'active') THEN 'active' ELSE 'inactive' END AS active_status,
status,
JSON_SET(COALESCE(NULLIF(TRIM(attributes), ''), JSON_OBJECT()), '$.status', status, '$.status_derived_from', NULL, '$.termination_date', NULL, '$.status_changed_at', NULL) AS attributes,
MD5(JSON_SET(COALESCE(NULLIF(TRIM(attributes), ''), JSON_OBJECT()), '$.status', status, '$.status_derived_from', NULL, '$.termination_date', NULL, '$.status_changed_at', NULL)) AS attributes_hash,
permissions,
MD5(permissions) AS permissions_hash,
NULL AS user_data,
NULL AS user_data_hash,
external_created_at,
external_deactivated_at,
external_created_by,
external_deactivated_by,
external_last_logon
FROM {table_name}Entitlements
12 columns, in this order:
Column | Description |
|---|---|
entitlement_name | Name of the entitlement |
entitlement_type | Type of entitlement, such as role or group |
entitlement_service_identifier | Unique identifier for the entitlement in the source system |
entitlement_scope | Scope or context, if the source system has one |
extra_params | Additional details about the entitlement |
extra_params_hash |
|
can_provision | 1 if the entitlement can be granted through Clarity |
can_deprovision | 1 if the entitlement can be removed through Clarity |
can_be_deleted | 1 if the entitlement can be deleted |
can_be_default | 1 if the entitlement can be a default entitlement |
dto_friendly_name | Friendly display name |
dto_description | Description |
Starter query:
SELECT
entitlement_name,
entitlement_type,
entitlement_service_identifier,
entitlement_scope,
extra_params,
MD5(extra_params) AS extra_params_hash,
COALESCE(can_provision, 1) AS can_provision,
COALESCE(can_deprovision, 1) AS can_deprovision,
COALESCE(can_be_deleted, 1) AS can_be_deleted,
COALESCE(can_be_default, 1) AS can_be_default,
dto_friendly_name,
dto_description
FROM {table_name}User-Entitlements
7 columns, in this order: user_identifier, entitlement_application_id, entitlement_type, entitlement_service_identifier, entitlement_scope, meta, meta_hash.
The user identifier, entitlement type, entitlement identifier and scope must match the values your Users and Entitlements queries return, so Clarity can connect each row to the right user and entitlement.
Starter query:
SELECT
identity_service_identifier AS user_identifier,
{application_id} AS entitlement_application_id,
entitlement_type,
entitlement_service_identifier,
entitlement_scope,
NULL AS meta,
NULL AS meta_hash
FROM {table_name}Entitlement-Entitlements
10 columns, in this order: parent_entitlement_type, parent_entitlement_service_identifier, parent_entitlement_scope, child_application_id, child_entitlement_type, child_entitlement_service_identifier, child_entitlement_scope, meta, meta_hash, no_inheritance.
Use this query type when one entitlement contains another, for example a role that grants a set of permissions.
Starter query:
SELECT
parent_type AS parent_entitlement_type,
parent_identifier AS parent_entitlement_service_identifier,
parent_scope AS parent_entitlement_scope,
{child_application_id} AS child_application_id,
child_type AS child_entitlement_type,
child_identifier AS child_entitlement_service_identifier,
child_scope AS child_entitlement_scope,
NULL AS meta,
NULL AS meta_hash,
COALESCE(no_inheritance, 0) AS no_inheritance
FROM {table_name}Entitlement-Owners
4 columns, in this order: entitlement_type, entitlement_service_identifier, entitlement_scope, owner_user_identifier.
Use this query type when your file names an owner for each entitlement, for example in a Reviewer column. Clarity then sets entitlement owners on every sync.
owner_user_identifiermust be the same value your Users query returns asuser_identifierfor that person. The match is exact and case-sensitive.Return one row per owner. An entitlement with two owners needs two rows.
An owner that does not match an active user linked to an identity is skipped without an error.
If you do not save an Entitlement-Owners query, syncs leave existing entitlement owners as they are. Once you use one, an owner that no longer appears in a later full sync is removed.
Starter query: replace {users_table_name} with the table your Users query reads from, and Reviewer with your owner column.
SELECT
e.entitlement_type AS entitlement_type,
e.entitlement_service_identifier AS entitlement_service_identifier,
e.entitlement_scope AS entitlement_scope,
u.identity_service_identifier AS owner_user_identifier
FROM {table_name} e
INNER JOIN {users_table_name} u ON u.user_name = e.ReviewerWhat queries can do
Queries must be read-only. Only
SELECTstatements are allowed, and they may begin with aWITHclause.A query can read only the files uploaded to the same application. You can join two or more of them, or combine them with
UNION.Standard SQL functions such as
CASE,COALESCE,CONCAT,TRIMandSTR_TO_DATEcan be used to clean and reshape values.To split a column that holds several values into one row per value, use
JSON_TABLE.
How Clarity recognizes your files
A saved query is tied to the file it was written against. Clarity recognizes a later upload as the same report when three things match: the file name (ignoring dates, timestamps and the file extension), the column headers, and the application.
This means a daily export such as Users_2026-03-01.csv followed by Users_2026-03-02.csv is treated as the same report, and the new data replaces the old. A renamed file, or a file with an added, removed or renamed column, is treated as a different report.
For the full list of what is ignored and what must stay the same, see Data Normalization Engine Naming Rules.
When an uploaded file does not match
When you upload files to run a sync, Clarity checks each file against the files your saved queries read. If a file does not match, the sync fails right away instead of running on older data. The message names the file that was not processed, the column headers Clarity received, and the file name and column headers each saved query expects. The same message is saved in the application's Sync Log.
To fix it, do one of the following:
Correct the file. Rename it or adjust its column headers to match what the saved query expects, then upload it again.
Update the saved query. If the new layout is intentional, open Configure Queries, upload the new file there, and save your query again against it.
On the Configure Queries page, uploading a file that none of your saved queries read shows a warning naming the new file and the file your query still reads. The warning does not block you. If the new file is meant to replace the old one, save your query against the new file before your next sync.
Note: You do not need to upload every file on every sync. Upload only the files that changed, as long as each file you upload is one your saved queries read.
Troubleshooting
Validate says the column count is wrong.
Each query type requires an exact list of output columns in a fixed order. Start again from the starter query and keep every output column. The message lists the columns Clarity expected.
Validate shows a SQL error.
Check that the query is a single read-only SELECT, that it reads from a table listed under Available Tables, and that the column names match the names shown there.
The buttons under the editor are disabled.
Open a table under Available Tables and choose a Query Type. The line under the buttons shows which of the two is missing.
The export put everything into one column.
Some systems export a single column with the real fields packed inside, separated by a character such as |. Either split the file into separate columns before uploading, or split the values in your query, for example with SUBSTRING_INDEX(your_column, '|', 1).
A sync upload failed with a message that a file was not processed.
The file name or column headers no longer match what your saved queries read. See When an uploaded file does not match and Data Normalization Engine Naming Rules.
An Excel file will not upload, or data is missing.
Save .xls files as .xlsx. Only the first worksheet is read, so move the data you need to the first sheet.
Frequently Asked Questions
What happens if I upload the same file twice?
A file with the same name (ignoring dates) and the same column headers replaces the earlier upload. Nothing is duplicated. See Data Normalization Engine Naming Rules.
Can I upload a file larger than 10MB?
No. Split the file into smaller files, or remove columns you do not need.
Can a query read data from another application?
No. A query can read only the files uploaded to its own application.
Who can see the uploaded data?
Only users who are allowed to configure the application.
Can I have more than one saved query of the same type?
No. Each application has one saved query per query type. To read from several files in one query type, join them or combine them with UNION in a single query.
Need Help?
If you have any problems, contact your customer success team. You can also get in touch with our general support via email, open a support ticket. Our general support team is available Monday - Friday from 8:00 AM - 6:30 PM CST.