ZenotiHelp

ETL processing β€” facts

ZTZenoti Docs Team Updated October 27, 2026 5 min readGuide

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 = 1 indicate deletions or updates to be removed from your downstream data.
  • Records with void = 0 are current data to be inserted.

When consuming a new incremental GroupID:

  1. Remove records where void = 1 from your target tables (match on the natural key).
  2. Insert records where void = 0 as new or updated data.

Important: The void flag controls ETL processing behavior β€” it is not a soft-delete marker. Always process void = 1 removals before inserting void = 0 records to maintain consistent data in your warehouse.

Worked example: Your Bi_FactInvoiceItem table receives an incremental load containing 500 records. Of these, 12 have void = 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 with void = 0 as 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 to Bi_DimCenter for center-based analysis.
  • userwid β€” Links to Bi_DimUser for guest-specific metrics.
  • Date WID columns β€” Link to Bi_DimDate for 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

  1. When a new GroupID folder appears in your S3 bucket, confirm the folder name does not end with _processing β€” a folder still ending in _processing indicates the load cycle has not completed yet. Wait until the folder is renamed before consuming its contents.
  2. Process dimension updates before applying fact table changes to maintain referential integrity across your star schema joins.
  3. Apply the delete-insert mechanism: remove all void = 1 records from your target, then insert all void = 0 records.
  4. To identify records added since your last load, filter on etlcreateddate greater 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 = 0 filter to exclude deleted or updated records from analysis results. If you omit this filter, voided records appear alongside current data.
  • Use centerwid as your primary join key for location-based reports: Most revenue and appointment fact tables distribute data by center. Joining on centerwid to Bi_DimCenter is the fastest path to center-level breakdowns.
  • Use etlcreateddate for incremental pipeline watermarks: Rather than tracking GroupID values manually, store the maximum etlcreateddate from each load as your pipeline watermark. On the next run, select only records where etlcreateddate is 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.

Was this article helpful?
Your feedback helps us improve our documentation.