> For the complete documentation index, see [llms.txt](https://help.smartico.ai/welcome/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-system-views.md).

# 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](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach)
* user\_id → [dm\_ach\_task](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_task)
* user\_id → [dm\_ach\_activity](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_activity)
* user\_id → [dm\_avatar](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_avatar)
* user\_id → [dm\_saw\_template](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_saw_template)
* user\_id → [dm\_tournament](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_tournament)
* user\_id → [dm\_ach\_custom\_sections](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_custom_sections)
* user\_id → [dm\_bnr\_banner](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_bnr_banner)
* user\_id → [dm\_bnr\_placement](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_bnr_placement)
* user\_id → [dm\_shop\_item](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_shop_item)
* user\_id → [dm\_segment](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.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](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_points_change_log)
* crm\_brand\_id → [g\_shop\_transactions](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_shop_transactions)
* crm\_brand\_id → [g\_ach\_levels\_changed](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_levels_changed)
* crm\_brand\_id → [g\_minigames](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_minigames)
* crm\_brand\_id → [g\_gems\_diamonds\_change\_log](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_gems_diamonds_change_log)
* crm\_brand\_id → [g\_ach\_claimed](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_claimed)
* crm\_brand\_id → [g\_ach\_completed](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_completed)
* crm\_brand\_id → [g\_ach\_optins](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ach_optins)
* crm\_brand\_id → [g\_tournament\_analytics](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_tournament_analytics)
* crm\_brand\_id → [g\_tournament\_winners](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_tournament_winners)
* crm\_brand\_id → [g\_ux](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.g_ux)
* crm\_brand\_id → [jp\_bet](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.jp_bet)
* crm\_brand\_id → [j\_automation\_rule\_progress](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_automation_rule_progress)
* crm\_brand\_id → [j\_bonuses](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_bonuses)
* crm\_brand\_id → [j\_communication](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_communication)
* crm\_brand\_id → [j\_engagements](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_engagements)
* crm\_brand\_id → [j\_webhooks\_facts](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.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](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/PVn4StbDnSBjvWx8XTTk#dwh_ext_xxx.dm_ach_task)
* event\_type\_id → [dm\_automation\_rule](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_automation_rule)
* event\_type\_id → [dm\_audience](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.dm_audience)
* event\_type\_id → [j\_engagements](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_engagements)
* event\_type\_id → [j\_events\_stats\_daily](#dwh_ext_xxx.j_events_stats_daily)
* event\_type\_id → [j\_events\_stats\_hourly](#dwh_ext_xxx.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](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.j_bonuses)
* product\_id → [j\_webhooks\_facts](https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/pages/mcM2zhXel1UsJAGRV9lc#dwh_ext_xxx.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.r\_deposits

Row-level deposit events in label (operator) currency. Used by daily aggregates and financial analytics. Partitioned by event\_time; restrict queries with a date or timestamp predicate on event\_time.

Primary key: **smartico\_event\_id**

Partitioned by: **event\_time**

Data availability: **all time by event\_time**

**Joins:**

* crm\_brand\_id → [dm\_brand](#dwh_ext_xxx.dm_brand)
* user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)

| Column                                 | Type      | Description                                                                   |
| -------------------------------------- | --------- | ----------------------------------------------------------------------------- |
| smartico\_event\_id                    | STRING    | Unique Smartico event identifier for the deposit row                          |
| user\_id                               | INT64     | Smartico user ID                                                              |
| crm\_brand\_id                         | INT64     | Brand ID in the Smartico system                                               |
| event\_time                            | TIMESTAMP | Time of the deposit event                                                     |
| amount\_label\_currency                | FLOAT64   | Deposit amount in label default currency                                      |
| amount\_user\_currency                 | FLOAT64   | Deposit amount in the user wallet currency                                    |
| external\_transaction\_id              | STRING    | Deposit transaction identifier from the operator or payment system            |
| acc\_last\_deposit\_payment\_method    | STRING    | Payment method label for the account at deposit time (operator-specific)      |
| acc\_last\_deposit\_payment\_submethod | STRING    | Payment sub-method or variant (e.g. provider-specific detail) at deposit time |
| insert\_time                           | TIMESTAMP | When this row was loaded into the warehouse table                             |

### dwh\_ext\_xxx.r\_withdrawals

Row-level withdrawal events in label (operator) currency. Used by daily aggregates and financial analytics. Partitioned by event\_time; restrict queries with a date or timestamp predicate on event\_time.

Primary key: **smartico\_event\_id**

Partitioned by: **event\_time**

Data availability: **all time by event\_time**

**Joins:**

* crm\_brand\_id → [dm\_brand](#dwh_ext_xxx.dm_brand)
* user\_id → [j\_user\_no\_enums](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)

| Column                    | Type      | Description                                                           |
| ------------------------- | --------- | --------------------------------------------------------------------- |
| smartico\_event\_id       | STRING    | Unique Smartico event identifier for the withdrawal row               |
| user\_id                  | INT64     | Smartico user ID                                                      |
| crm\_brand\_id            | INT64     | Brand ID in the Smartico system                                       |
| event\_time               | TIMESTAMP | Time of the withdrawal event                                          |
| amount\_label\_currency   | FLOAT64   | Withdrawal amount in label default currency                           |
| amount\_user\_currency    | FLOAT64   | Withdrawal amount in the user wallet currency                         |
| external\_transaction\_id | STRING    | Withdrawal transaction identifier from the operator or payment system |
| insert\_time              | TIMESTAMP | When this row was loaded into the warehouse table                     |

### 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](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)
* event\_type\_id → [dm\_event\_type](#dwh_ext_xxx.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](/welcome/technical-guides/smartico-data-warehouse.md#query-for-user-profile-details)
* event\_type\_id → [dm\_event\_type](#dwh_ext_xxx.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 |

### dwh\_ext\_xxx.j\_llm\_tokens\_usage

AI assistant tokens usage and credit operations. token\_op\_type = 1 means usage, token\_op\_type = 2 means credit. 'tokens' is the cost-aligned BILLABLE total (what bills the label). The raw\_\* columns are the underlying provider token counts (for analytics / re-pricing); 'model' is the model id that produced the usage, or 'BigQuery' for data-scan rows.

Primary key: **label\_id, bo\_user\_id, dt, token\_op\_type**

Partitioned by: **dt**

**Joins:**

* bo\_user\_id → [dm\_bo\_users](#dwh_ext_xxx.dm_bo_users)

| Column          | Type      | Description                                     |
| --------------- | --------- | ----------------------------------------------- |
| label\_id       | INT64     | Label ID                                        |
| bo\_user\_id    | INT64     | BackOffice user ID (-1 if unknown)              |
| dt              | TIMESTAMP | Operation timestamp                             |
| tokens          | INT64     | Billable (cost-aligned) tokens in the operation |
| token\_op\_type | INT64     | Operation type: 1 usage, 2 credit               |


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://help.smartico.ai/welcome/technical-guides/smartico-data-warehouse/smartico-dwh-system-views.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.