ZenotiHelp

Stage Table Schema Upgrades β€” Forms Metadata, Membership and Membership User

ZTZenoti Docs Team Updated November 11, 2026 6 min readGuide

This article describes three dimension table schema additions in Analytics β€” Amazon S3. After Zenoti applies these schema upgrades to your data pipeline, the Bi_DimFormsMetaData, Bi_DimMembership, and Bi_DimMembershipUser dimension tables each receive a new column. You must update your downstream queries and data warehouse tables before consuming data from the updated tables.

Audience: Data engineers and BI developers who maintain the downstream Redshift or external data warehouse schema and query layer for Analytics β€” Amazon S3.


On this page


Before you begin

  • Your organization must be onboarded to Analytics β€” Amazon S3. Contact your Zenoti account representative if you are unsure whether this delivery mode is enabled for your organization.
  • You need read access to your organization's S3 bucket to retrieve upgrade scripts from the SchemaUpgrades folder.
  • You need write access to your downstream data warehouse tables to execute the schema changes.
  • Pause any automated pipelines that consume Bi_DimFormsMetaData, Bi_DimMembership, or Bi_DimMembershipUser CSV files until all schema changes in this upgrade are applied. Consuming new CSV data before the schema is updated will cause column misalignment because Analytics β€” Amazon S3 CSV files do not include column headers β€” column order is defined by the SchemaFull files.
  • Confirm that Zenoti has notified you that the upgrade scripts for these tables are available in your SchemaUpgrades folder. Schema upgrade scripts appear in that folder only after Zenoti has applied the corresponding data pipeline changes.

Important: Apply all three schema changes in this upgrade before re-enabling data consumption for any of the three affected tables. Partially applied patches can cause query failures if your queries join across the affected dimension tables.

Steps

Retrieve the schema upgrade scripts from your S3 bucket

  1. Open your S3 bucket in the AWS Management Console or using your preferred S3 client.
  2. Navigate to the SchemaUpgrades folder within your bucket path. The folder path follows the pattern <PODName>/<GroupID>/SchemaUpgrades/.
  3. Locate the upgrade scripts for these three tables. Each affected table has its own upgrade script file in this folder.
  4. Download or open each script file locally so you can review and execute the ALTER TABLE statements it contains.

Note: The SchemaUpgrades folder contains cumulative upgrade scripts. If your team has not applied previous upgrade batches, apply them in sequence before applying these scripts. Check the SchemaFull folder for the current baseline schema of each table if you need to reconcile your starting state.

Apply the Bi_DimFormsMetaData schema change

This step adds the UILabel column to the Bi_DimFormsMetaData dimension table. This column carries the UI display label for each form metadata field, enabling reports that surface form field labels in a human-readable format.

  1. In your data warehouse query editor, connect to the database or schema that hosts your Bi_DimFormsMetaData table.
  2. Execute the following change to add the new column:
  • Column name: UILabel
  • Data type: NVARCHAR(2048)
  • Nullable: Yes

The upgrade script in your SchemaUpgrades folder contains the complete ALTER TABLE statement for your warehouse dialect. Execute that statement rather than writing your own to ensure the column definition matches the delivered data exactly.

  1. Confirm the column was added successfully by inspecting the table schema in your warehouse.

Worked example: If your team maintains a Redshift table named analytics.Bi_DimFormsMetaData, you would execute the ALTER TABLE analytics.Bi_DimFormsMetaData ADD COLUMN UILabel NVARCHAR(2048) statement from the upgrade script. After the statement completes, run SELECT UILabel FROM analytics.Bi_DimFormsMetaData LIMIT 1; to verify the column is present. It will be NULL for existing rows until the next data refresh populates it.

Apply the Bi_DimMembership schema change

This step adds the MembershipCreditBenefitsType column to the Bi_DimMembership dimension table. This column indicates the type of credit benefits associated with the membership definition, enabling analysis of membership benefit structures across your organization.

  1. Connect to the database or schema that hosts your Bi_DimMembership table.
  2. Execute the change to add the new column:
  • Column name: MembershipCreditBenefitsType
  • Data type: INT
  • Nullable: Yes

Use the upgrade script from the SchemaUpgrades folder for the complete statement.

  1. Confirm the column was added by inspecting the Bi_DimMembership table schema.

Apply the Bi_DimMembershipUser schema change

This step adds the MembershipCreditBenefitsType column to the Bi_DimMembershipUser dimension table. This column indicates the type of credit benefits on the individual guest membership record, enabling per-guest benefit analysis alongside the membership-level data in Bi_DimMembership.

  1. Connect to the database or schema that hosts your Bi_DimMembershipUser table.
  2. Execute the change to add the new column:
  • Column name: MembershipCreditBenefitsType
  • Data type: INT
  • Nullable: Yes

Use the upgrade script from the SchemaUpgrades folder for the complete statement.

  1. Confirm the column was added by inspecting the Bi_DimMembershipUser table schema.

Verify the schema changes and resume consumption

  1. After applying all three schema changes, run a validation query that selects the new columns from each affected table to confirm they exist and accept data correctly.
  2. Check the SchemaFull folder in your S3 bucket and compare the updated schema files for Bi_DimFormsMetaData, Bi_DimMembership, and Bi_DimMembershipUser against your warehouse table definitions to confirm alignment.
  3. Re-enable any automated pipelines or ingestion jobs that were paused before you began.
  4. After the next data refresh β€” which occurs approximately every 1–2 hours β€” verify that the new columns are populated with values in the incoming CSV files.

Note: The new columns will be NULL for rows that have no applicable data in the source system. This is expected behavior and does not indicate an ingestion error.

Tips

  • Apply schema upgrades before data arrives. Zenoti delivers updated CSV data after the pipeline change is applied on its end. If you delay executing the upgrade scripts, CSV files may arrive with additional column positions before your warehouse table is ready to receive them. Because CSV files contain no column headers, misaligned schemas cause silent data corruption rather than obvious errors.
  • Use the SchemaFull folder as your reference. After applying an upgrade, compare your warehouse table definition against the corresponding file in your SchemaFull folder to confirm column names, data types, nullability, and column order match exactly.
  • Keep your upgrade script archive. Store each patch's upgrade scripts alongside your data warehouse migration history so you can reconstruct the schema evolution for any table if needed.
  • Check both membership tables when writing benefit-type queries. The MembershipCreditBenefitsType column exists on both Bi_DimMembership (the membership definition) and Bi_DimMembershipUser (the guest's active membership record). Join them appropriately depending on whether your analysis is at the membership-catalog level or the per-guest level.
  • Test with a small query before re-enabling pipelines. After applying schema changes, run a targeted SELECT that includes the new columns and review a sample of rows before turning automated ingestion back on.

Troubleshooting

New columns are absent from CSV files after the next data refresh.

The data pipeline on Zenoti's end may not yet have propagated the change to your subscription's export. Wait for one additional refresh cycle (approximately every 1–2 hours) and check again. If new columns are still absent after two full refresh cycles, contact your Zenoti account representative and reference the schema upgrade for these tables.

Column count in the CSV file does not match the updated schema.

Verify that you have applied all three schema changes in this upgrade and not a subset. Also confirm that the SchemaFull file for the affected table reflects the new column count. If the SchemaFull file has not yet been updated in your bucket, the upgrade script delivery may still be in progress β€” wait for the next data refresh and re-check.

Queries against Bi_DimMembership and Bi_DimMembershipUser return unexpected NULL values for MembershipCreditBenefitsType.

NULL is the valid value for membership records where no credit benefit type is configured in the source system. If you expect non-NULL values for specific memberships, verify that those memberships have credit benefits configured in Zenoti. Contact your Zenoti account representative if you believe data that should be present is missing.

Automated ingestion job failed after schema change was applied.

Check whether your ingestion job infers the schema from the first CSV file it encounters. If the job cached the old schema before the patch was applied, clear the schema cache and re-run. Ensure the job reads column positions from the SchemaFull definition rather than from CSV headers, since Analytics β€” Amazon S3 CSV files do not include column headers.

The SchemaUpgrades folder does not contain scripts for these tables.

The folder is populated by Zenoti after the pipeline change is deployed to your POD. If the expected scripts are not present, the deployment may not yet have reached your POD. Contact your Zenoti account representative to confirm the deployment timeline for your organization.

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