Understand the star schema and data model
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
- Table categories
- Dimension tables
- Fact tables
- WID -- the surrogate key
- ETL metadata columns
- Consolidated tables
- Table optimization for query performance
- Writing a join query
- Alternatively, use a consolidated table
- Business domains
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: guests, centers, and appointments; services, products, and catalog; employees, memberships, and business
- Fact tables: appointments, sales, and financial; employees, memberships, and operations
- Consolidated tables: consolidated tables reference
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
voidcolumn 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 = 0when 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,centerwidinBi_DimCenter) - In a fact table, the foreign key column uses the same name:
centerwidinBi_FactInvoiceItemlinks tocenterwidinBi_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 = 0filters 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.