Alation Analytics V2 Database Model

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Applies from version 2020.3

Schema and Tables Explained

The Alation Analytics V2 package includes:

• A data dictionary with descriptions of all tables and recommendations on how to do JOINs.

• Sample queries — to be found in the Description field of the schema Public

• Documentation in the form of Alation Documents that explains the Alation Analytics V2 schema and some of the best practices for writing queries:

  • Introduction to Alation Analytics: Describes the Alation Analytics V2 schema and the relationships between its tables.

  • Alation Analytics ERD: Provides the entity–relationship diagram (ERD) for the Alation Analytics V2 database.

The data dictionary and the Documents are available in Alation after the Alation Analytics V2 database has been initialized and this data source becomes available in the Alation catalog. Use Search to find the Documents by their titles.

Note

Creating custom tables in the Alation Analytics V2 database is not recommended. Currently, users can create tables and views directly in the Alation Analytics V2 PostgreSQL database. This is technically feasible due to a PostgreSQL limitation as it does not differentiate between tables and views when granting permissions. Alation is not responsible for any data loss that may occur in user-created tables.

The qli_events Table

The table qli_events is intended to provide a comprehensive view of data consumption at the data source, query, and user levels. Users need to be aware of these limitations:

• The qli_events table is activated on demand. Please reach out to your account executive or Alation Support for activation. After activation, the data will become available after an ETL run.

• In versions 2024.1.3 to 2024.3.1, the qli_events table was limited to supporting one month of historical data upon activation, capped at 30 million rows. Starting with version 2024.3.2, enhancements to the pipeline have increased the capacity to approximately 120 million records per month, allowing for extracting four times more data than the previous limit for the same duration.

• After activation, the table supports up to 90 days of additional data over the historical data populated at the time of activation.

Alation Analytics V2 ERD

These images can be opened in a new browser tab for a detailed view (right-click on the image > open in a new tab).

Version 2026.2.1.0

The following new tables in Alation Analytics track data from Data Quality features:

• alation_dq_monitors: Stores monitor data. The primary key is monitor_id.

Column	Column type	Non-null Constraints	Description
monitor_id	bigint	NOT NULL	The ID of the monitor.
title	varchar		The title of the monitor.
description	text		A description of the monitor.
creation_type	varchar		A string representing how the monitor was created. Acceptable Values: UI, API, SDK, Standards
priority	varchar		The priority level of the monitor, such as low or high.
created_by_user	int		The ID of the user who created the monitor.
deleted	bool		A boolean value indicating whether the monitor has been deleted.
ts_created	timestamp		The timestamp when the monitor was created.
ts_updated	timestamp		The timestamp when the monitor was last updated.
ts_deleted	timestamp		The timestamp when the monitor was deleted.

• alation_dq_checks: Stores checks in monitors and their configurations. The primary key is check_uuid.

Column	Column Type	Non-null Constraints	Description
monitor_id	bigint	NOT NULL	The ID of the monitor.
check_uuid	uuid	NOT NULL	The unique metric UUID for the check.
asset_otype	varchar	NOT NULL	The object type for the asset on which the data quality check is defined.
asset_id	varchar	NOT NULL	The object identifier for the asset on which the data quality check is defined.
ds_id	int		The datasource identifier for the table or column on which the data quality check is defined.
schema_id	int		The schema identifier for the table or column on which the data quality check is defined.
table_id	int		The table identifier for the column on which the data quality check is defined.
column_id	int		The column identifier for the column on which the data quality check is defined.
object_name	varchar		The name of the table or column on which the data quality check is defined.
category	text		The category of the data quality check. Acceptable values include custom, consistency, timeliness, uniqueness, accuracy, completeness, and validity.
metric_name	varchar	NOT NULL	The name of the metric.
operator	varchar	NOT NULL	The operator defined for the metric.
argument	varchar	NOT NULL	The table or column name on which the metric is defined.
threshold	text[]	NOT NULL	The threshold defined for the metric.
configuration	json	NOT NULL	Additional filter checks applied to the metric.
description	text		A description of the metric.
sample_failed_query	text		The SQL query used to identify failed rows.
deleted	bool		A boolean value indicating whether the check has been deleted.
ts_created	timestamp		The timestamp when the check was created.
ts_updated	timestamp		The timestamp when the check was last updated.
ts_deleted	timestamp		The timestamp when the check was deleted.
version	bigint		Version number of the check configuration used for this result. Links to the specific check version in alation_dq_checks_version_history.

• alation_dq_anomalies: Stores anomalies in monitors and their configurations. The primary key is anomaly_id.

Column	Column Type	Non-null Constraints	Description
monitor_id	bigint	NOT NULL	The ID of the monitor.
anomaly_id	uuid	NOT NULL	The ID of the anomaly.
asset_otype	varchar	NOT NULL	The object type of the asset on which the anomaly is defined.
asset_id	varchar	NOT NULL	The object identifier of the asset on which the anomaly is defined.
ds_id	int		The datasource identifier for the table or column on which the anomaly is defined.
schema_id	int		The schema identifier for the table or column on which the anomaly is defined.
table_id	int		The table identifier for the column on which the anomaly is defined.
column_id	int		The column identifier for the column on which the anomaly is defined.
object_name	varchar		The name of the table or column on which the anomaly is defined.
category	text	NOT NULL	Indicates that the category is custom for anomalies.
metric_name	varchar	NOT NULL	The metric name of the anomaly.
operator	varchar		This field is empty for anomalies.
argument	varchar		The table or column name on which the metric is defined.
threshold	array		This field is empty for anomalies.
configuration	json		This field is empty for anomalies.
deleted	bool		A boolean value indicating whether the anomaly has been deleted.
ts_created	timestamp		The timestamp when the anomaly was created.
ts_updated	timestamp		The timestamp when the anomaly was last updated.
ts_deleted	timestamp		The timestamp when the anomaly was deleted.

• alation_dq_check_results: Stores check results during monitor runs. The composite key: monitor_id:check_uuid:job_id.

Column	Column Type	Non-null Constraints	Description
monitor_id	bigint	NOT NULL	The ID of the monitor.
check_uuid	uuid	NOT NULL	The unique metric UUID for the check.
job_id	bigint	NOT NULL	The job execution ID for the monitor run.
asset_id	varchar		The ID of the BI asset if the RDBMS object is related to a BI source object; otherwise, the string is empty.
asset_otype	varchar		The object type of the BI asset if the RDBMS object is related to a BI source object; otherwise, the string is empty.
ds_id	bigint		The datasource identifier for the table or column on which the data quality check is defined.
schema_id	bigint		The schema identifier for the table or column on which the data quality check is defined.
table_id	bigint		The table identifier for the column on which the data quality check is defined.
column_id	bigint		The column identifier for the column on which the data quality check is defined.
check_name	varchar		The metric definition of the check.
metrics	varchar		The metrics ID of the check.
dimension	varchar		The category of the data quality check. Acceptable values include custom, consistency, timeliness, uniqueness, accuracy, completeness, and validity.
outcome	varchar		The status of the execution, which can be PASS, FAIL, or ERROR.
outcome_reason	varchar		The error reason, used when the outcome is ERROR.
value	double precision		The resulting value from the check.
ts_created	timestamp		The timestamp when the check result was created.
version	bigint		Version number of the check configuration used for this result. Links to the specific check version in alation_dq_checks_version_history.

• alation_dq_anomaly_results: Stores anomaly results during monitor runs. The composite key is monitor_id:anomaly_id:job_id.

Column	Column Type	Non-null Constraints	Description
monitor_id	bigint	NOT NULL	The ID of the monitor.
anomaly_id	varchar	NOT NULL	The ID of the anomaly.
job_id	bigint	NOT NULL	The job execution ID for the monitor run.
asset_id	varchar		The ID of the BI asset if the RDBMS object is related to a BI source object; otherwise, the string is empty.
asset_otype	varchar		The object type of the BI asset if the RDBMS object is related to a BI source object; otherwise, the string is empty.
ds_id	bigint		The data source identifier for the table or column on which the anomaly is defined.
schema_id	bigint		The schema identifier for the table or column on which the anomaly is defined.
table_id	bigint		The table identifier for the column on which the anomaly is defined.
column_id	bigint		The column identifier for the column on which the anomaly is defined.
metric_name	varchar		The metric name of the anomaly.
anomaly_type	varchar		The type of anomaly, for example, schema_drift, value_anomaly, or outlier.
dimension	varchar		Indicates that the category is custom for anomalies.
outcome	varchar		The execution outcome: OK if an anomaly is not detected, or ANOMALY if one is detected.
outcome_reason	varchar		The reason for the anomaly if one is detected.
value	double precision		The value of the detected outlier.
acceptable_high	varchar		The higher acceptable range.
acceptable_low	varchar		The lower acceptable range.
is_anomaly	bool		A boolean value that is True if an anomaly is detected and False otherwise.
previous_version	varchar		The previous value of the outlier.
total_changes	varchar		The total number of changes.
ts_created	timestamp		The timestamp when the anomaly result was created.

• alation_dq_checks_version_history: Stores historical check results for the past year.

This table allows users to track how their check configurations have evolved over time. The version field in alation_dq_checks points to the current or latest version, while alation_dq_check_results.version indicates which version was used for each execution, enabling historical analysis of how check changes affected results. The composite key is check_uuid:version.

Column	Column Type	Non-null Constraints	Description
check_uuid	uuid	NOT NULL	The unique metric UUID for the check.
version	bigint	NOT NULL	Version number of the check configuration. Increments each time a user modifies the check.
monitor_id	bigint	NOT NULL	The ID of the monitor this check version belongs to.
asset_otype	varchar	NOT NULL	The object type for the asset on which the data quality check is defined.
asset_id	varchar	NOT NULL	The object identifier for the asset on which the data quality check is defined.
ds_id	int		The datasource identifier for the table or column on which the data quality check is defined.
schema_id	int		The schema identifier for the table or column on which the data quality check is defined.
table_id	int		The table identifier for the column on which the data quality check is defined.
column_id	int		The column identifier for the column on which the data quality check is defined.
object_name	varchar		The name of the table or column on which the data quality check is defined.
category	text		The category of the data quality check.
metric_name	varchar	NOT NULL	The name of the metric for this check version.
operator	varchar	NOT NULL	The operator defined for the metric in this version.
argument	varchar	NOT NULL	The table or column name on which the metric is defined.
threshold	text[]	NOT NULL	The threshold defined for the metric in this version.
configuration	json	NOT NULL	Additional filter checks applied to the metric in this version.
description	text		A description of the metric for this version.
sample_failed_query	text		The SQL query used to identify failed rows for this version.
ts_created	timestamp		The timestamp when this check version was created.
ts_updated	timestamp		The timestamp when this check version was last updated.

Note

• The ETL for these tables runs only if the Data Quality feature is enabled.

• If a monitor is deleted, all checks for that monitor from the alation_dq_monitors, alation_dq_checks, and alation_dq_anomalies tables are marked as soft deleted.

• If a check is deleted, its corresponding row from alation_dq_checks table is marked as soft deleted.

• If an anomaly is deleted, its corresponding row from the alation_dq_anomalies table is marked as soft deleted.

• Alation Analytics never deletes any data from the alation_dq_check_results or alation_dq_anomaly_results table. Instead, data is stored so that even if a check or anomaly is deleted, users can still join these tables with related tables to get more details and determine if a given monitor, check, or anomaly is actually deleted.

Version 2025.3.2

The Alation Analytics V2 dashboard have been updated to reflect the Document Hubs feature. In the UI, you will see the terms “Documents” and “Folders”. When querying the Alation Analytics database, these concepts map to the following tables:

• Documents in the UI correspond to data in the terms table.

• Folders in the UI correspond to data in the glossaries table.

Note

The underlying database table names remain unchanged to ensure backward compatibility for any existing custom reports or queries.

Version 2025.3.1

This version of Alation Analytics includes the following updates:

• Tables files, filesystems, and directories related to file system objects will be empty by default. Data population for these tables requires enabling the feature. For more information, see File System Object Ingestion section.

• The terms table includes a new column term_state to indicate the state of a glossary term.

• The search_queries table includes two columns chat_id and duration.

• To enable deeper analysis of your Data Marketplace, four new tables have been added to the Alation Analytics database: data_products, data_products_searches, data_products_events, and data_products_listing_state.

• A new summary table asset_types has been added to the Alation Analytics database. This table provides a consolidated view of all asset types in your catalog, including custom asset types and other types like ELT sources.

Version 2025.1.5

This version of Alation Analytics includes the following updates:

• New columns name and description are added to the data_quality table.

• The column source_url in the table bi_datasource_columns from varchar(1000) to text.

• The column source_url in the table bi_datasources from varchar(1000) to text.

• The column row_count in the table rdbms_tables from int to bigint.

• The column name in the table rdbms_datasources from varchar(1000) to text.

• The column glossary_title in the table glossaries from varchar(220) to text.

Version 2025.1.4

This version of Alation Analytics includes the following updates:

• Data type varchar(100) is changed to varchar(150) for the column group_name in table alation_group

• Data type varchar(100) is changed to text for the column uri in table rdbms_datasources

Version 2025.1.3

This version of Alation Analytics includes the following updates:

• The deleted and ts_deleted columns are added to the query table.

Version 2025.1.2

This version of Alation Analytics enables joining migrated documents with the original articles and migrated folders with the original article groups. It also adds information about Document Hub hierarchies so you can link child and parent objects. There are new columns in the following tables:

Tables	New Columns
article	document_id (int4)
custom_glossary	folder_id (int4)
term	article_id (int4)
glossaries	article_group_id (int4)
terms	linked_glossaries (ARRAY of int4) parent_term_id (int4) parent_glossary_id (int4)
glossaries	child_terms (ARRAY of int4) linked_terms (ARRAY of int4) child_glossaries (ARRAY of int4) parent_glossary_id (int4)

Version 2025.1.0.1

Version 2024.3.2

Version 2024.1.3

Version 2024.1.1

Version 2024.1

Using Compose

Users with access to the Alation Analytics V2 database can query it in Compose after they connect with their individual account.

See User Access to Alation Analytics V2

Sample Queries to Retrieve Data from Data Quality

Available from version 2025.1

Alation Analytics v2 (AAv2) supports the inclusion of data quality or health data for reporting purposes.

With this integration, users can link the state of data quality directly to the Alation catalog, providing greater visibility and reporting capabilities. This enables organizations to incorporate data quality metrics into their governance workflows, ensuring compliance with mandatory reporting requirements.

Refer to the following sample queries for retrieving heath data:

Select by Quality and Value

Replace the value of <integer_value> with the actual value.

SELECT *
FROM public.data_quality
WHERE quality = 'GOOD'
 AND value::int > '<integer_value>';

Select by object_id

Replace the value of object_id with the actual value.

SELECT *
FROM public.data_quality
WHERE object_id = '<object_id>';

Select by object_type

Replace the value of object_type with the actual value.

SELECT *
FROM public.data_quality
WHERE object_type = '<object_type>';

Select by last_updated

This could be records updated after a certain date. Replace the value of last_updated with the actual value.

SELECT *
FROM public.data_quality
WHERE last_updated > '<YYYY-MM-DD HH:MM:SS.SSS>';

Combined Query (Filter by object_type and last_updated)

Replace the values of last_updated and object_type with the actual values.

SELECT *
FROM public.data_quality
WHERE object_type = '<object_type>'
AND last_updated > '<YYYY-MM-DD HH:MM:SS.SSS>';

Select Records Updated Within a Specific Range

Replace the value of last_updated timestamp with the actual value.

SELECT *
FROM public.data_quality
WHERE last_updated BETWEEN '<YYYY-MM-DD HH:MM:SS.SSS>' AND '<YYYY-MM-DD HH:MM:SS.SSS>';