--- title: "Data Normalization Engine" slug: "data-normalization-engine" description: "The Data Normalization Engine lets you load data into Clarity from any system that can export a file." tags: ["DNE", "Custom Data Ingestion", "CSV Import"] status: "update" updated: 2026-11-19T16:52:47Z published: 2026-11-19T16:52:47Z canonical: "help.claritysecurity.com/data-normalization-engine" --- > ## Documentation Index > Fetch the complete documentation index at: https://help.claritysecurity.com/llms.txt > Use this file to discover all available pages before exploring further. # Data Normalization Engine 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 1. Go to **Applications**. 2. Open the application you are loading data for. 3. Click **Configure Queries**. ## Step-by-Step Guide ### Step 1: Upload your files 1. Drag your files onto the upload area, or click **Browse** to choose them. 2. Check the files listed under **Selected Files**. 3. 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 1. Under **Available Tables**, open the file you want to query. The editor loads a starter query for the query type you chose. 2. Edit the starter query so that each line reads from one of your file's columns and uses `AS` to give it the name Clarity expects. For example: `employee_number AS employee_id`. 3. 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](/docs/data-normalization-engine#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 | Email address | | employee_id | Employee or personnel number | | active_status | `active` or `inactive` | | 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: `status`, `status_derived_from`, `termination_date` and `status_changed_at` | | attributes_hash | `MD5()` of the same expression used for attributes | | permissions | User-level permissions, if any | | permissions_hash | `MD5()` of permissions | | user_data, user_data_hash | Set to `NULL` in the starter query | | 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:** ```sql 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 | `MD5()` of extra_params | | 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:** ```sql 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:** ```sql 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:** ```sql 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_identifier` must be the same value your Users query returns as `user_identifier` for 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. ```sql 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.Reviewer ``` ## What queries can do - Queries must be read-only. Only `SELECT` statements are allowed, and they may begin with a `WITH` clause. - 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`, `TRIM` and `STR_TO_DATE` can 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](/docs/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](/docs/data-normalization-engine#when-an-uploaded-file-does-not-match) and [Data Normalization Engine Naming Rules](/docs/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](/docs/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](mailto:support@claritysecurity.io), open a [support ticket](https://support.claritysecurity.com/home). Our general support team is available Monday - Friday from 8:00 AM - 6:30 PM CST. ## Related - [Data Normalization Engine Naming Rules](/data-normalization-engine-naming-rules.md)