ZenotiHelp

Understand the star schema and data model

ZTZenoti Docs Team Updated November 9, 2026 7 min readGuide

Analytics — Redshift Data Share delivers data in a star schema design. Understanding this structure helps you write efficient queries, choose the right tables, and interpret the data correctly.


On this page


What is a star schema

A star schema organizes data into two primary types of tables:

  • Fact tables sit at the center of the schema. They record measurable business events -- invoices, appointments, payments, membership charges, and other transactions. Fact tables contain numeric metrics, date references, and foreign key columns that link to dimension tables.
  • Dimension tables surround the fact tables and provide descriptive context -- who the guest was, which center processed the transaction, what service was performed, which employee was involved.

The resulting diagram resembles a star: one or more fact tables in the middle, with multiple dimension tables radiating outward. A third category, consolidated tables, provides pre-joined shortcuts for common reporting needs.

                    Bi_DimUser
                        |
                    (userwid)
                        |
Bi_DimCenter ----(centerwid)---- Bi_FactInvoiceItem ----(servicewid)---- Bi_DimService
                        |
             (invoicedateincenter = date)
                        |
                    Bi_DimDate

In this example, Bi_FactInvoiceItem is the central fact table. Each foreign key column (centerwid, userwid, servicewid) links to a surrounding dimension table, and the invoice date (invoicedateincenter) matches the calendar date in Bi_DimDate that provides the descriptive detail.

Table categories

The Redshift data warehouse contains three categories of tables:

Category Naming Purpose
Dimension tables Bi_Dim* prefix Descriptive attributes -- names, codes, categories, statuses, and configuration data
Fact tables Bi_Fact* prefix Measurable events and transactions -- amounts, counts, dates, durations
Consolidated tables Named by subject, for example Sales_Fact_Consolidated Pre-joined combinations of fact and dimension data for simplified querying

Every table, and every column in it, is documented once, in the Analytics — Amazon S3 table reference. Redshift Data Share uses the same tables, so that reference applies here unchanged:

Dimension tables

Dimension tables carry the Bi_Dim* prefix. They contain descriptive attributes that give context to the numeric data in fact tables.

What they contain:

  • Descriptive attributes -- names, codes, categories, statuses, and configuration data
  • A BIGINT surrogate key column (*WID) that serves as the primary key and join key
  • The original natural key (typically a GUID) preserved alongside the surrogate key for cross-referencing with other systems

Dimension tables you will join most often: Bi_DimUser, Bi_DimGuest, Bi_DimCenter, Bi_DimDate, Bi_DimService, Bi_DimProduct, Bi_DimEmployee and Bi_DimMembership. Each linked page lists that table's columns and what they hold.

How dimension updates work:

Dimension tables use an update-insert mechanism. When a record changes in the source system (for example, a guest updates their email address), the updated record replaces the existing row. New records are inserted as new rows. This keeps dimension data current with the latest values.

Dimension tables include a void column for soft-delete semantics. A value of 0 means the record is active; a non-zero value means the record has been voided or deleted in the source system. When querying dimension tables, you can optionally filter on void = 0 to exclude voided records.

Fact tables

Fact tables carry the Bi_Fact* prefix. They contain measurable business events and transactions.

What they contain:

  • Numeric metrics -- sale amounts, quantities, durations, counts
  • Date and time references
  • Foreign key WID columns that link to dimension tables
  • A void column indicating the current state of the record

How fact updates work -- the delete-insert pattern:

Fact tables use a delete-insert mechanism for incremental updates. When records are updated or deleted in the source system, the old versions are removed from the fact table and new versions are inserted.

void value Meaning
0 Current, active record -- include this in your queries
1 Soft-deleted record -- exclude this from your queries

WID -- the surrogate key

Always filter WHERE void = 0 when reading from fact tables for reporting. If you do not filter, your results will include stale and deleted records.

WID (Warehouse ID) is a BIGINT auto-incrementing surrogate key assigned to every row in every dimension table. All joins between fact and dimension tables use WID columns.

Naming convention:

  • In a dimension table, the primary key column is named <dimension>wid (for example, centerwid in Bi_DimCenter)
  • In a fact table, the foreign key column uses the same name: centerwid in Bi_FactInvoiceItem links to centerwid in Bi_DimCenter

This consistent naming makes it straightforward to identify join relationships -- if a fact table has a column called servicewid, it joins to Bi_DimService.servicewid.

Natural keys are preserved:

Every dimension table also retains the original natural key (typically a GUID) from the Zenoti operational system. Use natural keys when you need to:

  • Cross-reference data with other Zenoti integrations or API responses
  • Match records against external systems that use the same identifiers
  • Trace a record back to its source in the Zenoti application

For example, Bi_DimCenter contains both centerwid (the surrogate key for joins) and centerid (the original GUID from the source system).

ETL metadata columns

Most tables carry some standard metadata columns that help you track where a record came from and when the data refresh last wrote it:

Column Description
podname Identifies the Zenoti data pod that sourced the record
organizationid Organization GUID for multi-tenant filtering
groupid Tenant/group identifier
etlCreatedDate Timestamp when the record was first written by the data refresh process
EtlLastUpdatedOn Timestamp when the record was last updated by the data refresh process

Which of these columns a table has varies from table to table. Dimension tables usually carry EtlLastUpdatedOn, and fact tables usually carry etlCreatedDate. Check the table's column reference, linked above, before you rely on one. See Data Freshness and Refresh Cycles for guidance on using these columns.

Consolidated tables

Consolidated tables are pre-joined combinations of fact and dimension data. They are named by subject -- for example Sales_Fact_Consolidated -- and the consolidated tables reference lists them all with their columns.

When to use consolidated tables:

  • When you want results without writing JOIN statements
  • When you are connecting a BI tool that works best with flat, wide tables
  • When you are exploring data and want to see all relevant attributes in a single table
  • When you want a quick view that combines transaction data with descriptive context

When to use dimension and fact tables directly:

  • When you need to build custom metrics or aggregations that the consolidated tables do not cover
  • When you need full control over filter conditions and join logic
  • When you are joining tables across different business domains in ways the consolidated tables do not combine

Consolidated tables are rebuilt during each data refresh cycle. They always reflect the latest available data.

Table optimization for query performance

Tables in the Redshift data warehouse are optimized for query performance. Dimension tables are structured to be efficiently accessible from any node in the cluster, which means joins between fact and dimension tables execute quickly even at large data volumes. Fact tables are organized to co-locate related records, reducing the amount of data that needs to be scanned for common query patterns.

Sort ordering on frequently filtered and joined columns further accelerates queries that use WHERE clauses and date-range filters. These optimizations are managed automatically by Zenoti -- you do not need to configure or tune them.

Writing a join query

To join a fact table to one or more dimension tables, match the WID column names:

SELECT
    c.centername,
    s.servicename,
    SUM(f.saleprice) AS total_sales,
    COUNT(*) AS transaction_count
FROM Bi_FactInvoiceItem f
JOIN Bi_DimCenter c
    ON f.centerwid = c.centerwid
JOIN Bi_DimService s
    ON f.servicewid = s.servicewid
WHERE f.void = 0
  AND f.invoicedateincenter >= '2025-01-01'
  AND f.invoicedateincenter <  '2026-01-01'
GROUP BY c.centername, s.servicename
ORDER BY total_sales DESC;

Key points from this example:

  • Each JOIN uses the shared WID column name (centerwid, servicewid)
  • WHERE f.void = 0 filters to active records only
  • Descriptive attributes (centername, servicename) come from the dimension tables
  • Numeric metrics (saleprice) and the invoice date (invoicedateincenter) come from the fact table

Reporting by month or quarter. Bi_DimDate holds calendar attributes such as month, quarter, and fiscal periods. Join it on the date rather than on a WID: its date column is a timestamp, so cast it to a date to match a fact table's date column -- JOIN Bi_DimDate d ON CAST(d."date" AS DATE) = f.invoicedateincenter. Some of its column names, such as "date" and "year", are reserved words, so wrap them in double quotes.

Alternatively, use a consolidated table

Much of the same analysis is available in a consolidated table without any joins. Consolidated tables flatten the star schema into a single wide table, so you query one table instead of joining several. Build common business reports pairs each star schema query with its consolidated equivalent, and the consolidated tables reference lists the columns each one offers.

Business domains

The data warehouse covers the major Zenoti business domains, including guests, centers, employees, services, products, memberships, packages, gift cards, campaigns, issues, loyalty, training, and inventory. The dimension, fact, and consolidated table references linked under Table categories list the tables in each domain and their columns.

Table schemas are identical between Redshift Data Share and S3 Data Share, so those references apply to both.

Was this article helpful?
Your feedback helps us improve our documentation.