Analytics Metadata Descriptions

Note

This section is for Alation Analytics Version 1 (V1). Please see Alation Analytics V2 for information about Version 2 (available from release 2020.3).

Alation Analytics data model features one multi-fact star schema that includes 22 tables.

Alation Analytics Schematic

alation_visits

FACT table that registers User visits to Alation. The grain of this table is a Visit: the event of a User opening the designated catalog page of a specific Alation Object.

id	int	PK	Unique identifier of a particular Visit.
user_id	int	FK	Unique identifier of a specific User who visited the Object catalog page.
object_uuid	uuid	FK	UUID of the Object that is visited.
object_type_id	int	FK	Unique identifier of the Object Type.
time_period_id	bigint	FK	Unique identifier of the Time Period for a given Visit. Time Period can be used for grouping data by a specific period (day, week, month, quarter, year).
timestamp	timestamp with time zone		Timestamp when the object is visited.

alation_user

DIMENSION table that stores attributes of a User.

user_id	int	PK	Unique identifier of a specific User.
user_name	varchar		Username to log in to Alation.
email	varchar		Email address associated with the User.
is_active	boolean		Boolean indicating whether the User’s account is currently active in Alation (vs. suspended)
date_joined	timestamp with time zone		Date when the account of the User was created.
last_login	timestamp with time zone		Timestamp when the user last logged into Alation (does not imply that this user used Alation after login).
display_name	text		Display name of the user as it appears in Alation UI.
user_type	int		Code of the User’s role in Alation. Can assume values: 0 - RESERVED 1 - BUSINESS 2 - IT 3 - OFFICER 4 - ANALYST 5 - STEWARD
is_admin	boolean		Boolean value indicating whether the user is an Admin.

alation_object

DIMENSION table that stores attributes of Alation Objects.

Important

object_uuid is not unique across all Objects. It is unique in its Object Type, but Objects of different types may have the same object_uuid s.

id	int	PK	Unique identifier of a specific Object.
object_uuid	uuid		UUID of the Object.
object_type_id	int	FK	Unique identifier of the Object Type for the Object.
object_url	varchar		URL of the Object: Relative URI for the Object that can be used from Alation UI to visit this Object.

alation_object_type

DIMENSION table storing the attributes of the Object Type. Join this table if you want to use the name of the Object Type instead of the ID.

object_type_id	int	PK	Unique identifier of the Object Type.
object_type_name	varchar		Name of the Object Type.

time_period

DIMENSION table that stores details of a Time Period. Time Period is stored for several events that can be qualified by a timestamp. For example, the events of Visit or Query Execution. Time Period is the date of the event fragmented into time components, such as day, week, or year. This table exists to facilitate grouping by time in reports.

id	int	PK	Unique identifier of the Time Period.
date	date		Date of the event.
week	int		Week of the event. Can take values 1 to 52, representing each week in the year.
month	int		Month of the event. Can take values 1 to 12, representing Jan to Dec.
quarter	int		Quarter in which the event took place. Can take values from 1 to 4, where: 1 -  represents Jan - Mar 2 -  represents Apr - June 3 - represents July - Sept 4 -  represents Oct - Dec.
year	int		Year in which the event took place.
day_of_week	int		Day of the week on which the event took place. Can assume values 1 to 7, representing Monday (1)  to Sunday(7).

user_group_membership

FACT table that registers the fact of a User belonging to a Group. Each User-Group relationship existing in Alation is given an ID in this table. This table links User and Group dimensions. One User can belong to multiple Groups.

id	int	PK	Unique identifier of a specific User-Group relationship.
user_id	int	FK	Unique identifier of the User.
group_id	int	FK	Unique identifier of the Group.

alation_group

DIMENSION table that stores attributes of the Group.

group_id	int	PK	Unique identifier of a specific Group.
group_name	varchar		Name of the Group.
email	varchar		Email address associated with the Group.
display_name	text		Display name of the Group in Alation UI.

alation_object_popularity

FACT table that registers Object Popularity at the current time.  Each Object - Popularity association is given an ID in this table to link the Popularity value and the specific Object.

id	int	PK	Unique identifier of popularity for a specific Object.
object_uuid	uuid	FK	Unique identifier of the object.
object_type_id	int	FK	Unique identifier of the Object Type.
popularity	float		Actual current popularity value for the Object.

compose_query_log

FACT table tracking the events of Query Execution in Compose (event of a User running a Query).

query_execution_id	int	PK	Unique identifier of the event of Query Execution.
user_id	int	FK	Unique identifier of the User who executed this query. Can be joined with alation_user on alation_user.user_id field.
db_username	text		Username used for connection to the database for the given Query Execution. Null, if unavailable.
datasouce_uuid	uuid		Unique identifier of the Data Source. Can be joined with alation_object.object_uuid using object_type_id for Data Source.
mentioned_tables_uuids	uuid[]		Array of UUID fields to identify the Tables used in the current query. Can be joined with alation_object.object_uuid using object_type_id for Table.
executed_period_id	int		Unique identifier of the time period of the Query Execution.
executed_at_ts	timestamp with time zone		Timestamp value at which the query started to execute.
execution_duration	double-precision		Time taken, in seconds, for the query to finish execution.
is_query_published	boolean		Boolean indicator of whether this query text is published.
is_scheduled_execution	boolean		Boolean indicator of whether this Query Execution is as the result of automatic scheduling.
query_statement_fp	bigint	FK	Unique identifier for query_statement table. Can be joined with it for getting the  SQL statement of the Query.
client_ip_address	text		IP Address from which the execution originated.

query_statement

DIMENSION table storing Query Statements. Can be used to pull out the SQL body of the Query.

statement_fp	bigint	PK	Unique identifier for the Query Statement text.
statement_text	text		Plain text SQL statements.

object_field_value

FACT table that tracks setting Values to Object Fields and links a specific Object Field with a specific Value. Each Object Field - Value association is given an id in this table. This id can be used for finding specific values for specific Fields on specific Objects. Object Field is an element of the metadata of an Object that holds a property qualifying this Object.

This is one of the most important tables in Alation Analytics if you want to evaluate metadata.

id	int	PK	Unique identifier of the Object Field - Value association.
object_uuid	uuid	FK	UUID of the Object corresponding to the current row.
object_type_id	int	FK	Unique identifier of the Object Type.
field_id	int	FK	Unique identifier of the Object Field.
value_fp	int	FK	Unique identifier of field_value dimension table. Can be used to get the actual value of the field.
value_source	bigint		How the current value is applied to the field on the specific object: 1 - External Source 2 - Set explicitly on the object in Alation 3 - Applied based on the rules from a Catalog Set.
user_id	int	FK	Unique identifier of the User who set this value. Null, if not applicable.
timestamp	timestamp with time zone		Timestamp when the value was set to the given field
time_period_id	int	FK	Unique identifier of the time_period for the event.

field_value

DIMENSION table that stores specific Field Values. Field Value is a specific Value set to a Field.

Sometimes a field is a pointer to another object, in which case you need public.field_value.object_type_uuid_value and public.field_value.object_type_id_value to find it.

value_fp	int	PK	Unique identifier of the Field Value.
bigint_array_value	bigint[]		Value of the field as bigint array, if applicable. Otherwise, null
bigint_value	bigint		Value of the field as a bigint, if applicable. Otherwise, null
boolean_value	boolean		Value of the field as a boolean, if applicable. Otherwise, null
datetime_value	timestamp with time zone		Value of the field as datetime entry, if applicable. Otherwise, null
float_value	double-precision		Value of the field as decimal floating value, if applicable. Otherwise, null
hstore_value	hstore		Value of the field as hstore entry, if applicable. Otherwise, null
interger_array_value	int[]		Value of the field as integer array, if applicable. Otherwise, null
interger_value	int		Value of the field as integer value, if applicable. Otherwise, null
text_array_value	text[]		Value of the field as text array, if applicable. Otherwise, null
text_value	text		Value of the field as text value, if applicable. Otherwise, null
uuid_value	uuid		Value of the field as a UUID value, if applicable. Otherwise, null
object_type_id_value	int		Value of the field if its data type is Alation Object. Unique identifier of the Object Type, can be used to join with alation_object_type table.
object_type_uuid_value	uuid		Value of the field if its data type is Alation Object. Unique identifier of the Object. Can be used along with object_type_id_value to join on the alation_object table.

object_field

DIMENSION table that stores attributes that describe each Object Field.

This table is useful in queries if you want to use the field names instead of field IDs.

field_id	int	PK	Unique identifier of the Object Field.
field_name	text		Title of the field or property of the object.
field_datatype	varchar(255)		Data type of the field.
added_in_alation	boolean		Boolean indicating whether this property is created in Alation.

object_type_field

FACT table that links Object Type and Field. For the Field Values, join to the table public.object_field_value.

id	int	PK	Unique identifier of each valid Object Type - Field combination when a Field, represented as a record in object_field table is supported for the objects of a given Object Type.
object_type_id	int	FK	Unique identifier of the Object Type. Can be used to join with alation_object_type table.
field_id	int	FK	Unique identifier of the field. Can be used to join with object_field table.

catalog_set_membership

FACT table that tracks the fact of an Object belonging to a Catalog Set. Each Object - Catalog Set relationship is given an identifier.

id	int	PK	Unique identifier of the Catalog Set Membership.
object_uuid	uuid	FK	UUID of the Object that is a member of the Catalog Set.
object_type_id	int	FK	Unique identifier of the Object Type of the Object which is a member of the Catalog Set.
catalog_set_id	int		Unique identifier of the Catalog Set.

object_parent_relationship

FACT table that registers Alation Objects that have Parent Objects. Parent Object is an Object nesting other Objects as children. For example, a Schema has Tables as children, and a Table has Columns.

If you search for a Column, this table will tell you not only the Table, but also the Schema and the Data Source.

id	int	PK	Unique identifier of the Object - Parent relationship.
object_uuid	int	FK	UUID of the Object.
object_type_id	int	FK	Unique identifier of the Object Type.
parent_object_uuid	uuid		UUID of the parent Object
parent_object_type_id	int		Unique identifier of the Object Type of the parent Object.

metadata_change

FACT table that tracks the events of Metadata Change.

id	int	PK	Unique identifier of the Metadata Change event.
object_uuid	uuid	FK	UUID of the Object that  affected by the Metadata Change.
object_type_id	int	FK	Unique identifier of the Object Type.
action_performed	int		Type of Metadata Change event that took place: 0 - UNKNOWN 1 - ADDED 2 - DELETED 3 - RESTORED 4 - MODIFIED 5 - REFRESHED 6 - NO_CHANGE
field_old_value	text		Old value of the field.
field_new_value	text		New value of the field.
time_period_id	int	FK	Unique identifier of the time_period.
timestamp	Timestamp with time zone		Timestamp when the Metadata Change event took place.

alation_object_lineage

FACT table that registers Object Lineage of Objects.

id	int	PK	Unique identifier of the Lineage.
source_uuid	uuid	FK	UUID of the source Object.
source_otype_id	int	FK	Object Type id of the source Object.
target_uuid	uuid		UUID of the target Object.
target_otype_id	int		Object type id of the target Object.

object_tags

FACT table that registers the fact of a Tag added to an Object. Each Object - Tag relationship is given an ID in this table.

id	int	PK	Unique identifier of Object - Tag relationship.
object_uuid	uuid	FK	Unique identifier of the tagged Object, can be used to join with alation_object table.
object_type_id	int	FK	Unique identifier of the Object_Type. Can be used to join on the alation_object_type table.
tag_uuid	uuid		Unique identifier of the Tag. Can be used to join on the alation_object table and alation_object_type table.
tagger_id	int		Unique identifier of the User who added this Tag to the Object.
time_period_id	int	FK	Unique identifier of the time period of tagging the Object.
ts_tagged	timestamp with time zone		Timestamp when the Object was tagged.

object_flags

FACT table that registers the fact of a Flag being applied to an Object.

id	int	PK	Unique identifier of the Flag applied to the given Object.
object_uuid	uuid	FK	Unique identifier of the Object; can be used to join with alation_object table.
object_type_id	int	FK	Unique identifier of the Object_Type. Can be used to join on the alation_object_type table.
value	text		Any additional information provided by the User when applying the Flag.
flag_type	int		Enum integer representing the type of the Flag: ENDORSEMENT = 1, WARNING = 2, DEPRECATION = 3
user_id	int		Unique identifier of the User who added this Flag to the object.
time_period_id	int		Unique identifier of the time period.
ts_created	timestamp with time zone		Timestamp when the Flag is applied to the object.
is_propagated	boolean		Boolean indicating if Flag is propagated to the children of the Object.

django_migrations

Internal table used by the system to maintain the database schema.

etl_checkpoint

Internal table used for tracking the ETL job progress.