Query Redshift data with SQL
This article covers practical SQL query patterns for the Analytics -- Redshift Data Share data warehouse. You will learn how to connect a BI tool, query dimension and fact tables, use consolidated tables, and apply common filters for efficient reporting.
Prerequisite: You need an active Analytics Plus license with the Redshift Data Share add-on. Cluster access credentials are provisioned by Zenoti during onboarding.
On this page
- Connecting your BI tool
- Understanding the data model
- Basic dimension query
- Basic fact query
- Joining facts and dimensions via WID
- Using consolidated tables
- Using status dimension views
- Filtering by organization
- Filtering active records
- Performance tips
- Quick reference
Connecting your BI tool
Redshift Data Share provides direct SQL access to the data warehouse. You can connect using any SQL client or BI tool that supports Amazon Redshift (JDBC or ODBC connections). Common options include:
- Tableau -- Use the Amazon Redshift connector. Enter the cluster endpoint, database name, and credentials provided by Zenoti.
- Power BI -- Use the Amazon Redshift connector in the Get Data dialog. Provide the server endpoint and database name.
- DBeaver -- Add a new Redshift connection. Enter the host, port, database, and credentials.
- Amazon Redshift Query Editor -- Access the query editor directly from the AWS console if your organization has console access to the cluster.
In all cases, you will need the cluster endpoint, database name, port, and the username/password provided by Zenoti during onboarding. The connection is read-only -- you cannot modify the data warehouse.
Understanding the data model
The Redshift data warehouse uses a star schema design:
- Dimension tables (
Bi_Dim*prefix) contain descriptive attributes -- centers, employees, services, products, guests, and more. - Fact tables (
Bi_Fact*prefix) contain measurable events and transactions -- appointments, invoices, commissions, memberships, and more. - Consolidated tables are pre-joined tables that combine fact and dimension data into a single flat structure, removing the need for JOIN statements.
All tables use surrogate keys (columns ending in wid) as primary keys and join keys. These WID (Warehouse ID) columns are auto-generated integers. Natural business keys (typically GUIDs) are also preserved in each table for cross-referencing with the source Zenoti system.
Every table and its columns are documented once, in the Analytics — Amazon S3 table reference, which applies to Redshift Data Share as well. Start from Understand the star schema and data model to find the reference for the tables you want to query.
Basic dimension query
Dimension tables contain reference data. Query them to explore available centers, employees, services, and other entities.
SELECT
centerwid,
CenterName,
CenterCode
FROM Bi_DimCenter
WHERE void = 0;
This query reads Bi_DimCenter, the center dimension table. The void = 0 filter excludes soft-deleted records. Always apply this filter on dimension tables to return only active records.
Basic fact query
Fact tables contain transactional data. Use etlCreatedDate or date columns to scope your result set, and use LIMIT when exploring.
SELECT *
FROM Bi_FactAppointment
WHERE etlCreatedDate > '2024-01-01'
LIMIT 100;
Tip: Avoid
SELECT *in production queries. Specify only the columns you need to improve query performance.
Joining facts and dimensions via WID
The core query pattern in a star schema is joining a fact table to one or more dimension tables using WID columns. This enriches transactional records with descriptive context.
SELECT
f.factappointmentwid,
c.centername,
u.firstname,
u.lastname,
st.statusdisplayvalue AS appointment_status,
f.startdateincenter
FROM Bi_FactAppointment f
JOIN Bi_DimCenter c ON f.centerwid = c.centerwid
JOIN Bi_DimUser u ON f.userwid = u.userwid
JOIN Bi_vwDimStatus_AppointmentStatus st ON f.appstatuswid = st.dimstatuswid
WHERE c.void = 0
AND f.void = 0;
Key points:
- Join on the
widcolumn that both tables share (e.g.,centerwid,userwid,servicewid). - The names do not always match. For example, the
employeewidcolumn in the employee commission table joins touserwidinBi_DimUser. Check the table's column reference when a join is not obvious. - A status is stored as a key, such as
appstatuswid. Join the matching status view, shown below, to get its name. - Apply
void = 0on both fact and dimension tables. - Use table aliases (
f,c,u) for readability.
Using consolidated tables
Consolidated tables combine fact and dimension data into a single table, so no JOINs are required. This is ideal for simpler queries and ad-hoc analysis.
SELECT
"center name",
"service name",
"appointment status",
"appointment start date"
FROM appointment_fact_consolidated
LIMIT 100;
Consolidated tables name most of their columns in plain words with spaces, so wrap each column name in double quotes. Most of them, including this one, do not carry a void column.
Other commonly used consolidated tables include Sales_Fact_Consolidated, membership_fact_consoidated (spelled this way in the data warehouse), empcommission_fact_consolidated, inventory_fact_consolidated, and giftcards_fact_consolidated. The consolidated tables reference lists them all with their columns.
Consolidated tables contain the same data as the corresponding star schema tables -- they are a convenience layer, not a separate data set.
Using status dimension views
The data warehouse includes filtered views on the Bi_DimStatus table that provide pre-filtered status lookups by status type. These views follow the naming pattern Bi_vwDimStatus_<StatusType>.
Instead of filtering Bi_DimStatus manually for appointment statuses:
-- Use the pre-filtered view instead
SELECT *
FROM Bi_vwDimStatus_AppointmentStatus;
Other available views include Bi_vwDimStatus_MembershipStatus, Bi_vwDimStatus_PaymentType, and Bi_vwDimStatus_GuestStatus, with similar views for other status types. These views simplify queries by removing the need to filter by status type.
Filtering by organization
In a multi-tenant environment, filter by organizationid to isolate data for your organization:
SELECT
c.CenterName,
COUNT(f.factappointmentwid) AS total_appointments
FROM Bi_FactAppointment f
JOIN Bi_DimCenter c ON f.centerwid = c.centerwid
WHERE f.organizationid = 'your-org-guid'
AND f.void = 0
AND c.void = 0
GROUP BY c.CenterName
ORDER BY total_appointments DESC;
Replace 'your-org-guid' with your actual organization GUID. If your data share is scoped to a single organization, all records already belong to your organization, but the filter is still good practice.
Filtering active records
- Dimension tables: Use
WHERE void = 0to exclude soft-deleted records. - Fact tables: Use
WHERE void = 0to return only current, active records. Fact tables use a delete-insert pattern -- updated records replace the previous version. - ETL timestamps: Use
etlCreatedDateandEtlLastUpdatedOnto filter by when records were loaded or last updated. Fact tables usually carryetlCreatedDate, and dimension tables usually carryEtlLastUpdatedOn; check the table's column reference.
-- Find when the fact table last received new records
SELECT MAX(etlCreatedDate) AS last_load
FROM Bi_FactAppointment;
To see when each center was last refreshed, query Bi_DimETLRefreshLog. Data freshness and refresh cycles shows the query.
Performance tips
Select specific columns instead of
SELECT *. This reduces the amount of data scanned and returned.Filter early. Apply
WHEREconditions to narrow down rows before joining or aggregating.Use consolidated tables for simple queries. If you only need data from one subject area and do not need custom joins, consolidated tables avoid the overhead of multi-table joins.
Filter by date ranges. Narrow queries to the time period you need rather than scanning the entire table.
Use
LIMITwhen exploring. When you are exploring a new table or building a query, addLIMIT 100to avoid returning millions of rows.Join on WID columns. Always use the surrogate key WID columns for joins, not natural key GUIDs. WID columns are optimized for join performance.
Apply
void = 0consistently. Omitting this filter on fact tables leads to counting soft-deleted records, which inflates your results.
Quick reference
| Task | Pattern |
|---|---|
| Query a dimension | SELECT columns FROM Bi_Dim* WHERE void = 0 |
| Query a fact | SELECT columns FROM Bi_Fact* WHERE void = 0 |
| Join fact to dimension | JOIN Bi_Dim* d ON f.wid = d.wid |
| Use consolidated table | SELECT "column name" FROM *_consolidated |
| Filter by organization | WHERE organizationid = 'your-org-guid' |
| Check data freshness | SELECT MAX(etlCreatedDate) FROM fact_table_name |
| Use status view | SELECT * FROM Bi_vwDimStatus_<StatusType> |