Troubleshoot Redshift data issues
Use this article to diagnose and resolve common issues when working with Analytics — Redshift Data Share.
On this page
- Data appears stale or has not refreshed
- Missing records
- Table missing or empty
- Connection issues
- Query performance is slow
- Schema changes broke my queries
- Data inconsistency between tables
- When to contact Zenoti Support
Before you begin
- You must have Redshift Data Share access provisioned for your organization. Provisioning is performed by Zenoti.
- You need your Redshift cluster credentials (endpoint, database name, port, username, and password).
- For data freshness questions, access to the
Bi_DimETLRefreshLogtable is required.
Data appears stale or has not refreshed
Symptom: Your reports show outdated information, or recent transactions from Zenoti are not visible in the data warehouse.
What to check:
Query the last load timestamp. Check the
etlCreatedDatecolumn on your primary fact table to see when it last received new records:SELECT MAX(etlCreatedDate) AS last_load FROM Bi_FactAppointment;Check
Bi_DimETLRefreshLog. This table records when data was last refreshed for each center and organization:SELECT * FROM Bi_DimETLRefreshLog ORDER BY EtlLastUpdatedOn DESC LIMIT 10;Allow normal processing time. Data is typically refreshed every 1 to 2 hours. During periods of high data volume, refresh cycles may take longer. A gap of up to 2 hours between updates is within normal operating range.
Action: If data has not been refreshed for 6 or more hours, contact Zenoti Support with the latest timestamp you can see and the name of the table you checked. Zenoti monitors for processing issues and is automatically notified of failures, but contacting support ensures your specific case is prioritized.
Note: You do not need to trigger, schedule, or monitor data refreshes. Zenoti operates the refresh pipeline automatically and handles failure recovery without requiring action on your part.
Missing records
Symptom: Expected records are absent from query results. Transactions, appointments, or other data that exists in Zenoti is not appearing in the data warehouse.
What to check:
Check the void column. Records may be soft-deleted. Ensure your query includes
WHERE void = 0for dimension tables and fact tables. If you are querying without this filter, you may see both active and voided records — but if you are filteringvoid = 0and the record hasvoid = 1, it will be excluded.Check your organizationid filter. If your query filters by
organizationid, verify you are using the correct GUID. An incorrect GUID will return zero results.Check the date range. If you are filtering by
etlCreatedDateor another date column, ensure the date range covers the period when the record was created. Records created very recently may not yet be present if the latest data refresh cycle has not completed.Check whether the center is in scope. If your data share is scoped to a specific center or zone, records from other centers will not be included. Contact Zenoti Support to verify your subscription scope.
Action: If records are missing and none of the above explains the absence, contact Zenoti Support with the specific record identifiers (for example, appointment ID or invoice ID) and the table you expected them in.
Table missing or empty
Symptom: A table you expect to see does not exist in the data warehouse, or it exists but contains no rows.
What to check:
New tables are added by Zenoti engineering. If you are looking for a table that was recently announced in release notes, it may not yet be available in your cluster. New tables are deployed during a processing cycle and may take one or more refresh cycles to populate.
Some tables may not be populated for your organization. Certain tables correspond to specific Zenoti features. If your organization does not use that feature, the table may exist but contain no data.
Tables may be temporarily excluded from processing during maintenance or when Zenoti engineering is resolving a data issue. During this period, the table retains its previous data but does not receive new updates. Processing resumes automatically and the table catches up once it is included in the refresh pipeline again.
Action: If a table is missing or persistently empty and you believe it should contain data for your organization, contact Zenoti Support.
Connection issues
Symptom: Your SQL client or BI tool cannot connect to the Redshift cluster, or you receive authentication errors.
What to check:
Verify your credentials. Confirm the cluster endpoint, database name, port, username, and password are entered correctly. Credentials are case-sensitive.
Check network and firewall rules. Ensure your network allows outbound connections to the Redshift cluster endpoint on the configured port (typically 5439). Corporate firewalls, VPNs, or IP restrictions may block the connection.
Confirm your Redshift Data Share access is active. If your access has been deactivated, connection attempts will fail. Contact Zenoti Support to verify your access status.
Check for expired credentials. If your password was recently rotated or your access was reprovisioned, update your client configuration with the new credentials.
Action: If credentials have expired or you need new access credentials, contact Zenoti Support to request a credential refresh.
Query performance is slow
Symptom: Queries take a long time to return results or time out.
What to check:
Use consolidated tables for simple queries. If you only need data from one subject area and do not require custom joins, consolidated tables avoid the overhead of multi-table joins and may return results faster.
Select specific columns instead of
SELECT *. Returning only the columns you need reduces the amount of data scanned.Filter early. Apply
WHEREconditions to narrow down rows before joining or aggregating. Filter by date range, center, or organization to reduce the working data set.Avoid full table scans on large fact tables. Always include date or center filters when querying large fact tables like
Bi_FactInvoiceItemorBi_FactAppointment.Use
LIMITwhen exploring. If you are building a query interactively, addLIMIT 100to avoid returning millions of rows during development.
Schema changes broke my queries
Symptom: After a Zenoti release, your queries or reports return errors or unexpected results.
What to check:
Review what changed. Schema changes are applied automatically by Zenoti. Check Zenoti release notes for details on new columns, new tables, or column type adjustments.
Update column references. If your query references a column that was renamed (rare) or if a new column has the same intent as an existing one, update your query to use the correct column name.
Check for new columns in
SELECT *queries. If your downstream systems process query results by column position rather than column name, new columns added to a table may shift expected positions. Use explicit column lists instead ofSELECT *to avoid this issue.
Note: Zenoti does not remove or rename existing columns during normal upgrades. If your query uses explicit column names and those columns still exist, the query should continue to work after an upgrade.
Data inconsistency between tables
Symptom: Counts or totals do not match between a fact table and its corresponding consolidated table, or between two related fact tables.
Explanation: Data is processed incrementally. During an active data refresh cycle, some tables may be updated before others. This creates a brief window where table data reflects slightly different points in time. This is normal and resolves automatically once the refresh cycle completes.
What to check:
Check the load or update timestamp on both tables —
etlCreatedDateon a fact table,EtlLastUpdatedOnon a dimension table. If the timestamps differ, the tables are at different refresh points. Wait for the next complete refresh cycle.Verify void filtering. Ensure you are filtering
void = 0consistently on all tables in your query. Inconsistent void filtering is the most common cause of count mismatches.Consolidated tables reflect the latest completed batch. If you are comparing a star schema query to a consolidated table query, the consolidated table may lag slightly behind the individual fact and dimension tables during an active refresh.
When to contact Zenoti Support
Contact Zenoti Support if you experience any of the following:
- Data has not refreshed for 6 or more hours (check
Bi_DimETLRefreshLogand the table's load timestamp first) - A table is persistently missing or empty and you believe it should contain data
- You receive persistent access denied errors after verifying your credentials
- Multiple tables show stale data simultaneously
- You suspect data corruption (for example, negative values where only positive values are expected, or records with impossible dates)
When contacting support, include:
- The table name and the latest timestamp you can see
- Your organization name or
organizationid - A description of the issue and any SQL queries you ran to diagnose it