ETL processing β facts
Fact table processing delivers transactional and event data from your Zenoti system to your S3 bucket through the Analytics β Amazon S3 data pipeline.
Audience: Data analysts and BI engineers who consume fact table data from their organization's S3 bucket.
On this page
Before you begin
- Your organization must have an active Analytics β Amazon S3 subscription, enabled by Zenoti.
- The initial full load must be completed before incremental fact processing begins.
- You need access to your organization's S3 bucket to verify fact table deliveries.
Steps
Understand fact table processing
Analytics β Amazon S3 processes fact tables during each data refresh. These tables contain transactional data organized by business domain:
- Appointments β
Bi_FactAppointment,Bi_FactAppointmentMultipleEmp,Bi_FactAppointmentRedo,Bi_FactDeletedAppointment - Invoicing and collections β
Bi_FactInvoiceItem,Bi_FactCollections,Bi_FactCashRegister,Bi_FactCashRegisterv2,Bi_FactInvoiceitemTaxes,Bi_FactEmployeeInvoiceItemRevenue,Bi_FactPettyCashDetails,Bi_FactScheduledCollections,Bi_FactInvoiceDonations - Employee and payroll β
Bi_FactEmployeePay,Bi_FactEmpSalary,Bi_FactEmployeeSalary,Bi_FactEmployeeSchedule,Bi_FactEmployeeCommission,Bi_FactEmployeeDeclareTips,Bi_FactEmployeeItemLevelCommissions,Bi_FactEmployeeMembershipCommissions,Bi_FactItemCommissionDeductions,Bi_FactMultipleEmployeeCommission,Bi_FactTipsPayout,Bi_FactEmployeeTipsAndSSGDetails,Bi_FactEmployeeworktasksPayroll,Bi_FactRecurringPaymentAmountLog - Memberships β
Bi_FactMembershipDunning,Bi_FactMembershipStatusLog,Bi_FactMembershipStatusLogDeleted,Bi_FactMembershipTransfers,Bi_FactMembershipUserRedemptions,Bi_FactRecurringStatusLog,Bi_FactRecurringStatusLogDeleted - Packages β
Bi_FactPackageUserRedemptions,Bi_FactPackageValue - Gift cards and loyalty points β
Bi_FactGiftCards,Bi_FactLoyaltyPoints - Campaigns and discounts β
Bi_FactCampaignDiscount,Bi_FactCampaignSummary,Bi_FactChallenges,Bi_FactMilestones - Guest activity β
Bi_FactGuestCheckIn,Bi_FactGuestMergeLog,Bi_FactGuestPersonas,Bi_FactGuestTierChanges,Bi_FactTurnAways,Bi_FactUpcomingGuestPersona,Bi_FactUserGuestPass - Feedback and forms β
Bi_FactForms,Bi_FactFeedBackV2 - Training and registration β
Bi_FactRegistration,Bi_FactInstructorClass - Inventory β
Bi_FactInventory - Issues β
Bi_FactIssues - Reference and system β
Bi_FactAdoptionSummary,Bi_FactOpportunityCustomFieldValues
Note: The number of fact tables in your bucket depends on the domains active for your organization. See the per-table reference articles listed in Related articles for column-level details for each table.
Understand the delete-insert mechanism
Fact tables use a delete-insert mechanism for incremental updates:
- Records with
void = 1indicate deletions or updates to be removed from your downstream data. - Records with
void = 0are current data to be inserted.
When consuming a new incremental GroupID:
- Remove records where
void = 1from your target tables (match on the natural key). - Insert records where
void = 0as new or updated data.
Important: The
voidflag controls ETL processing behavior β it is not a soft-delete marker. Always processvoid = 1removals before insertingvoid = 0records to maintain consistent data in your warehouse.
Worked example: Your
Bi_FactInvoiceItemtable receives an incremental load containing 500 records. Of these, 12 havevoid = 1β these correspond to invoice line items that were voided or corrected in Zenoti since the last refresh. Remove those 12 rows from your target by matching on the natural key, then insert the remaining 488 records withvoid = 0as current data.
Work with fact table join keys
Each fact table contains surrogate key (WID) columns that link to dimension tables, enabling star schema joins:
centerwidβ Links toBi_DimCenterfor center-based analysis.userwidβ Links toBi_DimUserfor guest-specific metrics.- Date WID columns β Link to
Bi_DimDatefor time-based reporting.
The centerwid and userwid columns are the most common join keys across fact tables. Other WID columns vary by domain β for example, therapistwid appears in appointment fact tables, and servicewid appears in invoice and appointment fact tables.
Use ETL metadata columns
Every fact table includes standard metadata columns:
| Column | Description |
|---|---|
etlcreateddate |
When the data refresh created the record |
EtlLastUpdatedOn |
Last data refresh update timestamp |
etlcreatedby |
Identifier for the data refresh process |
PodName |
Your organization's pod identifier |
organizationid |
Your organization ID for multi-tenant filtering |
void |
Record state: 0 = current, 1 = deleted or updated |
groupid |
The load cycle identifier for the incremental batch that delivered this record |
Handle incremental fact updates
- When a new GroupID folder appears in your S3 bucket, confirm the folder name does not end with
_processingβ a folder still ending in_processingindicates the load cycle has not completed yet. Wait until the folder is renamed before consuming its contents. - Process dimension updates before applying fact table changes to maintain referential integrity across your star schema joins.
- Apply the delete-insert mechanism: remove all
void = 1records from your target, then insert allvoid = 0records. - To identify records added since your last load, filter on
etlcreateddategreater than your previous load timestamp.
Note: Data refreshes run approximately every 1β2 hours. Actual timing varies with data volume, so do not rely on a fixed cadence in automated pipelines β check for a new completed GroupID folder instead.
Tips
- Filter out voided records in queries: When building reports, always add a
void = 0filter to exclude deleted or updated records from analysis results. If you omit this filter, voided records appear alongside current data. - Use
centerwidas your primary join key for location-based reports: Most revenue and appointment fact tables distribute data by center. Joining oncenterwidtoBi_DimCenteris the fastest path to center-level breakdowns. - Use
etlcreateddatefor incremental pipeline watermarks: Rather than tracking GroupID values manually, store the maximumetlcreateddatefrom each load as your pipeline watermark. On the next run, select only records whereetlcreateddateis greater than that value. - Check dimension tables first when a join returns no rows: Fact table WID columns reference dimension table rows. If a join returns unexpectedly empty results, verify the corresponding dimension table has been refreshed and the WID exists. See ETL processing β dimensions for dimension refresh details.
- Large tables deliver one or more CSV files per table folder: When a table contains a large volume of changed records in a single load cycle, the system writes multiple CSV files into the table's folder within the GroupID. Process all CSV files in the folder before moving to the next table.
Troubleshooting
A GroupID folder with _processing in the name has been in my bucket for an extended time.
The data refresh pipeline creates a temporary <groupid_processing> folder during a load cycle and renames it to <groupid> when the cycle completes. If the _processing folder persists beyond the typical 1β2 hour refresh cadence, the load cycle may have encountered an error. Do not consume data from a _processing folder. Contact your Zenoti account representative and provide the folder name and timestamp so the pipeline state can be investigated.
Joining a fact table to a dimension table returns no matching rows for some records.
This usually means the dimension table has not yet received the corresponding record from the same or an earlier load cycle. Confirm that your pipeline processes dimension CSV files before fact CSV files within the same GroupID. If the dimension record is still absent after the next refresh cycle, contact your Zenoti account representative.
My target table contains duplicate rows after loading a new GroupID.
Duplicates typically occur when the delete-insert step is skipped or the void = 1 removal is not applied before inserting void = 0 records. Review your pipeline logic to ensure you remove all records matching void = 1 on the natural key before inserting new records. Re-running the load without the removal step will produce duplicates on each cycle.