◾Smartico DWH \ System views

Dimensional tables

dwh_ext_xxx.dm_tag

List of tags used for organizing and categorizing entities.

Primary key: tag_id

Column	Type	Description
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the tag was created
update_date	TIMESTAMP	Date and time when the tag was last updated
tag_id	INT64	Unique identifier for the tag
tag_name	STRING	Name of the tag
is_deleted	BOOL	Indicates if the tag has been deleted

dwh_ext_xxx.dm_tag_entity

Association table linking tags to entities.

Primary key: id

Column	Type	Description
id	INT64	Unique identifier for the tag-entity association
tag_id	INT64	Reference to dm_tag
entity_id	INT64	ID of the associated entity
entity_type_id	INT64	Type of the associated entity
is_deleted	BOOL	Indicates if the association has been deleted
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the association was created
update_date	TIMESTAMP	Date and time when the association was last updated

dwh_ext_xxx.dm_casino_game_name

Primary key: smr_game_id

Column	Type	Description
smr_game_id	INT64
game_name	STRING
label_id	INT64
update_date	TIMESTAMP
create_date	TIMESTAMP

dwh_ext_xxx.dm_casino_provider_name

Contains information about casino game providers.

Primary key: smr_provider_id

Column	Type	Description
smr_provider_id	INT64
provider_name	STRING
label_id	INT64
update_date	TIMESTAMP
create_date	TIMESTAMP

dwh_ext_xxx.dm_casino_game_type

Contains information about casino game types.

Primary key: smr_game_type_id

Column	Type	Description
smr_game_type_id	INT64
game_type	STRING
label_id	INT64
update_date	TIMESTAMP
create_date	TIMESTAMP

dwh_ext_xxx.dm_sport_type

Primary key: smr_sport_type_id

Column	Type	Description
smr_sport_type_id	INT64
sport_type	STRING
label_id	INT64
update_date	TIMESTAMP
create_date	TIMESTAMP

dwh_ext_xxx.dm_sport_league

Primary key: smr_sport_league_id

Column	Type	Description
smr_sport_league_id	INT64
sport_league	STRING
label_id	INT64
update_date	TIMESTAMP
create_date	TIMESTAMP

dwh_ext_xxx.dm_bo_users

List of BackOffice users with their details.

Primary key: user_id

Joins:

• user_id → dm_ach

• user_id → dm_ach_task

• user_id → dm_ach_activity

• user_id → dm_avatar

• user_id → dm_saw_template

• user_id → dm_tournament

• user_id → dm_ach_custom_sections

• user_id → dm_bnr_banner

• user_id → dm_bnr_placement

• user_id → dm_shop_item

• user_id → dm_segment

Column	Type	Description
user_id	INT64	ID of the backoffice user
username	STRING	Username of the backoffice user
bo_user_email	STRING	Email of the backoffice user
is_active	BOOL	Indicates if the user is active (enabled for login and not deactivated)
home_label_id	INT64	Home label ID of the user
update_date	TIMESTAMP	Date and time when the user was last updated
create_date	TIMESTAMP	Date and time when the user was created

dwh_ext_xxx.dm_brand

Contains information about CRM brands.

Primary key: crm_brand_id

Joins:

• crm_brand_id → g_ach_points_change_log

• crm_brand_id → g_shop_transactions

• crm_brand_id → g_ach_levels_changed

• crm_brand_id → g_minigames

• crm_brand_id → g_gems_diamonds_change_log

• crm_brand_id → g_ach_claimed

• crm_brand_id → g_ach_completed

• crm_brand_id → g_ach_optins

• crm_brand_id → g_tournament_analytics

• crm_brand_id → g_tournament_winners

• crm_brand_id → g_ux

• crm_brand_id → jp_bet

• crm_brand_id → j_automation_rule_progress

• crm_brand_id → j_bonuses

• crm_brand_id → j_communication

• crm_brand_id → j_engagements

• crm_brand_id → j_webhooks_facts

Column	Type	Description
label_id	INT64	Label identifier
create_date	TIMESTAMP	Date and time when the brand was created
update_date	TIMESTAMP	Date and time when the brand was last updated
crm_brand_id	INT64	ID of the brand in the Smartico system
crm_brand_name	STRING	Name of the brand
ext_brand_id	STRING	External brand identifier
is_qa	BOOL	Indicates if this is a QA/test brand
is_deleted	BOOL	Indicates if the brand has been deleted

dwh_ext_xxx.dm_event_type

List of event types used for triggering real-time campaigns and tracking user activities.

Primary key: event_type_id

Partitioned by: create_date

Joins:

• event_type_id → dm_ach_task

• event_type_id → dm_automation_rule

• event_type_id → dm_audience

• event_type_id → j_engagements

• event_type_id → j_events_stats_daily

• event_type_id → j_events_stats_hourly

Column	Type	Description
event_type_id	INT64	Unique identifier for the event type
event_type_name	STRING	Internal name of the event type
event_type_uiname	STRING	UI display name of the event type
product_id	INT64	ID of the product this event type belongs to
tracks_mau	BOOL	Indicates if this event type tracks Monthly Active Users
has_tr_table	BOOL	Indicates if this event type has corresponding transactional table (with tr_ prefix)
create_date	TIMESTAMP	Date and time when the event type was created
update_date	TIMESTAMP	Date and time when the event type was last updated

dwh_ext_xxx.dm_product

Contains information about products and integrations setup on the label. E.g. label usually has one integration product and many Smartico specific products that are dependent on what is activated on the label. Example of integration product: Int: MyCasinoPlatform Example of Smartico specific product: Tournaments, Jackpots, Store, AI Churn, etc..

Primary key: product_id

Joins:

• product_id → j_bonuses

• product_id → j_webhooks_facts

Column	Type	Description
product_id	INT64	ID
product_name	STRING	Name
is_integration_product	BOOL	Indicator if the product of type integration or internal product of Smartico
integration_type	STRING	Type of integration, e.g. NodeJS, Kafka, RabbitMQ, API etc.
create_date	TIMESTAMP
update_date	TIMESTAMP

Fact tables

dwh_ext_xxx.j_events_stats_daily

Events stats daily, aggregated by event type and user. Updated every hour. Can be joined with dm_event_type table that contains event_type_name. Note that event_type_name from dm_event_type corresponds to the table with prefix 'tr_', for example 'tr_acc_deposit_approved'.

Primary key: event_date, event_type_id, user_id

Partitioned by: event_date

Joins:

• user_id → j_user_no_enums

• event_type_id → dm_event_type

Column	Type	Description
label_id	INT64	Label ID
event_date	TIMESTAMP	Event date, truncated to day
event_type_id	INT64	Event type ID
user_id	INT64	Smartico user ID
cnt	INT64	Count of events during the day

dwh_ext_xxx.j_events_stats_hourly

Events stats hourly, aggregated by event type and user. Updated every hour. Can be joined with dm_event_type table that contains event_type_name. Note that event_type_name from dm_event_type corresponds to the table with prefix 'tr_', for example 'tr_acc_deposit_approved'.

Primary key: event_date, event_type_id, user_id

Partitioned by: event_date

Joins:

• user_id → j_user_no_enums

• event_type_id → dm_event_type

Column	Type	Description
label_id	INT64	Operator/Label ID
event_date	TIMESTAMP	Event date, truncated to hour
event_type_id	INT64	Event type ID
user_id	INT64	Smartico user ID
cnt	INT64	Count of events during the hour