Data pipeline architecture
Understand the four-stage pipeline that processes your Zenoti data and delivers it to your S3 bucket.
Before you begin
- Your organization must have an active Analytics β Amazon S3 subscription.
- Your S3 Data Share must be configured and active.
Steps
Understand the pipeline stages
The Analytics β Amazon S3 pipeline processes your data through four sequential stages:
- SQL Server Step β Extracts core data from the Zenoti operational system and performs initial transformations.
- Redshift Dim/Fact Load β Builds dimension and fact tables in the Redshift data warehouse.
- Redshift Consolidated Tables β Creates denormalized tables by joining dimensions and facts.
- S3 Sync β Exports data as CSV files to your S3 bucket.
Each stage must complete successfully before the next stage begins. S3 Data Share is Stage 4 β it runs after Redshift processing, not independently.
Understand processing modes
The system operates in one of two modes based on infrastructure configuration:
- Regular Mode β Standard processing for most organizations. Reads from the RPT database and transforms data in the Analytics database.
- Failover/Hyperscale Mode β High-volume processing mode that reads from read-replica databases and uses distributed processing for faster throughput.
Both modes produce identical output in your S3 bucket. The system automatically selects the appropriate mode. These are infrastructure configurations, not user-selectable options.
Understand delivery modes
Zenoti offers two data delivery modes:
- Redshift Data Share β Provides dedicated or shared Redshift cluster access for direct SQL queries against the data warehouse.
- S3 Data Share β Delivers CSV files to customer-owned S3 buckets.
Customers subscribe to one or both modes. This article focuses on the S3 Data Share delivery path.
Review stage-specific processing
Each pipeline stage handles different aspects of your data:
SQL Server Step:
- Extracts appointment, guest, service, and transaction data from Zenoti.
- Creates two core dimension tables (
Bi_DimUserandBi_DimCenter) in the Analytics database. - Prepares data for warehouse loading.
Redshift Dim/Fact Load:
- Builds the remaining 95 dimension tables directly from source system data.
- Creates all 57 fact tables organized by business domain.
- Establishes primary and foreign key relationships between tables.
- Removes outdated records and inserts new data.
Redshift Consolidated Tables:
- Joins dimension and fact tables to create simplified reporting tables.
- Processes large fact tables in configurable batches (default: 5 million rows).
- Creates approximately 47 consolidated tables for easier querying.
- Small and custom consolidated tables run in full sync mode.
S3 Sync:
- Reads processed data and converts it to CSV format.
- Splits files by organization, zone, or center based on your subscription scope.
- Places CSV files in your S3 bucket using the standard folder structure.
Handle processing errors
If the pipeline encounters errors:
- Check your S3 bucket for a
_processingfolder that has not been renamed β this indicates the load did not complete. - Wait for the next scheduled refresh cycle, which typically processes any failed data.
- Contact your Zenoti Implementation team if errors persist beyond one data refresh cycle.
Tips
- Monitor data freshness regularly β Set up alerts if no new GroupID folder arrives beyond the typical 1β2 hour cadence, as this may indicate pipeline issues.
- Review consolidated tables first β These pre-joined tables are optimized for reporting and require less complex SQL queries than the raw dimension and fact tables.
- Plan for processing windows β The time a refresh cycle takes to complete depends on your data volume.