Bi_FactInvoiceItem β Resident Tax Identity Columns
The Bi_FactInvoiceItem fact table includes three columns that carry guest tax identity information for each invoice line item: ResidenceCountryName, ResidentRegistrationId, and ResidentTinNumber. This article explains what these columns contain, how they are populated during data refresh, and how to use them in your BI queries.
Audience: BI analysts and data engineers who query the Bi_FactInvoiceItem fact table in their S3 Data Share delivery.
On this page
- Before you begin
- Steps
- Column reference
- How these columns are populated
- Tips
- Troubleshooting
- Related articles
Before you begin
- Your organization must be subscribed to Analytics β Amazon S3 (S3 Data Share).
- Your S3 bucket must have received at least one incremental load after the schema update that introduced these columns. If your bucket was provisioned before this schema change, you must apply the corresponding schema upgrade scripts from the SchemaUpgrade folder in your S3 bucket before querying these columns. See Schema Versioning & Upgrades.
- You must have read access to your organization's S3 bucket to retrieve CSV files.
- Column order in CSV files is defined by the SchemaFull folder β there are no column headers in the delivered CSV files. Confirm the column positions for
ResidenceCountryName,ResidentRegistrationId, andResidentTinNumberfrom your SchemaFull definition before building queries.
Note: If your BI tool connects directly to the Redshift layer rather than consuming CSV files from S3, the same three columns are available in the
Bi_FactInvoiceItemtable with identical names and data types.
Steps
Step 1: Verify the schema upgrade has been applied
Before querying the new columns, confirm that your table definition includes them.
Navigate to the SchemaFull folder in your S3 bucket. The path follows the pattern:
<PODName>/<GroupID>/SchemaFull/Open the schema definition file for
Bi_FactInvoiceItem.Confirm that the following three columns appear in the column list:
ResidenceCountryNameResidentRegistrationIdResidentTinNumber
If these columns are absent, apply the schema upgrade script from the SchemaUpgrade folder before proceeding. Contact your Zenoti implementation team if you need assistance locating the correct upgrade script.
Important: Querying a CSV file that was generated after the schema change using a table definition that predates the change will produce column misalignment errors. Always apply schema upgrades before consuming new data.
Step 2: Identify the correct GroupID folder
The three columns are populated in both full load and incremental load CSV files.
In your S3 bucket, navigate to the folder for your organization, zone, or center scope:
<PODName>/<GroupID>/For the initial full load, use GroupID 1. For ongoing analysis, use the latest incremental GroupID.
Confirm that no folder named
<GroupID>_processingexists for the GroupID you intend to query β a_processingsuffix indicates the data refresh is still in progress. Wait until the folder is renamed to the plain GroupID before consuming the files.
Step 3: Locate the Bi_FactInvoiceItem CSV file
- Within the selected GroupID folder, locate the CSV file for
Bi_FactInvoiceItem. - Load the file into your BI tool or query engine using the column order defined in your SchemaFull definition.
- Map
ResidenceCountryName,ResidentRegistrationId, andResidentTinNumberto the correct positional columns.
Step 4: Query the resident tax identity columns
Use the three columns as follows in your queries:
ResidenceCountryNameβ Filter or group invoice line items by the guest's country of residence. Useful for regional tax reporting.ResidentRegistrationIdβ Join or filter on the guest's resident registration identifier where local regulations require it on invoices.ResidentTinNumberβ Include the guest's tax identification number in tax compliance reports or invoice exports.
Note: These columns may contain
NULLvalues. ANULLin any of these columns indicates that the corresponding tax identity information was not recorded on the guest profile at the time the invoice line item was created.
Worked example: A tax compliance analyst needs to produce a monthly report of all invoice line items for guests with a registered tax identification number in a specific country. They filter
Bi_FactInvoiceItemwhereResidenceCountryName = 'Brazil'andResidentTinNumber IS NOT NULL, then join toBi_DimUseron the guest surrogate key (WID) to include guest name and contact details. The resulting dataset lists every invoice line item with a verified Brazilian tax ID, ready for submission to the local tax authority.
Column reference
The following table describes the three resident tax identity columns in Bi_FactInvoiceItem.
| Column name | Data type | Description |
|---|---|---|
ResidenceCountryName |
VARCHAR | The name of the country where the guest is registered as a resident, as recorded on the guest profile at the time of the invoice. |
ResidentRegistrationId |
VARCHAR | The guest's resident registration identifier (for example, a national ID or residency permit number), as recorded on the guest profile. |
ResidentTinNumber |
VARCHAR | The guest's tax identification number (TIN), as recorded on the guest profile at the time of the invoice. |
Note: All three columns are VARCHAR. Leading zeros and special characters in registration identifiers and TIN values are preserved exactly as entered in Zenoti.
How these columns are populated
Data flows from Zenoti through processing stages to your S3 bucket approximately every 1β2 hours. The timing varies with data volume.
- Incremental loads β During each data refresh cycle, changed invoice line item records are selected and the three columns are read from the source data. If a guest's tax identity information has been updated in Zenoti since the last refresh, the updated values appear in the next incremental load.
- Full load β During the initial full load (GroupID 1), all historical invoice line items are exported with the values that were current at the time of export.
- Records with no tax identity data β When a guest profile does not include country of residence, a registration identifier, or a TIN, the corresponding column is delivered as
NULLin the CSV file. This applies to both full load and incremental load files.
Note: The values in these columns reflect the guest's profile data at the time the invoice line item was processed. Retroactive changes to a guest's tax identity in Zenoti do not update historical invoice line item records already delivered to S3.
Tips
- Apply schema upgrades promptly. When a new SchemaUpgrade script appears in your S3 bucket, apply it before your next scheduled query run. Delaying schema upgrades while continuing to consume new CSV files causes column misalignment.
- Handle NULLs explicitly in your BI tool. Because tax identity fields are optional in Zenoti, all three columns can be NULL. Use
COALESCEor equivalent functions to substitute a display value (for example,'Not provided') in reports intended for non-technical stakeholders. - Use
ResidenceCountryNamefor regional segmentation. Grouping invoice line items byResidenceCountryNamelets you produce country-level tax summaries without joining to a separate dimension table, since the country name is carried directly on the fact row. - Combine with
Bi_DimUserfor full guest context. JoinBi_FactInvoiceItemtoBi_DimUseron the guest surrogate key (WID) to enrich tax identity data with guest name, contact information, and membership status in a single query. - Validate TIN formats in your reporting layer. Zenoti stores TIN values as free-form text. If your compliance reports require a specific format (for example, a fixed digit count), apply format validation in your BI tool rather than relying on the source data.
Troubleshooting
The three columns do not appear in my query results.
Your table definition may predate the schema change that introduced these columns. Open the SchemaFull folder in your S3 bucket and confirm that ResidenceCountryName, ResidentRegistrationId, and ResidentTinNumber are listed. If they are absent from the SchemaFull definition, apply the schema upgrade script from the SchemaUpgrade folder. If the SchemaUpgrade folder does not contain a relevant script, contact your Zenoti implementation team.
All three columns contain only NULL values for recent invoice line items.
This is expected behavior when guest profiles in Zenoti do not include tax identity information. Confirm with your Zenoti administrator that the relevant fields (country of residence, registration ID, TIN) are being collected on guest profiles. If the fields are populated in Zenoti but still appear as NULL in S3, verify that the data refresh cycle has completed by checking that the GroupID folder does not have a _processing suffix.
Column values appear shifted β data from one column is appearing in another.
This indicates a column order mismatch between your table definition and the CSV file. CSV files delivered to S3 do not contain column headers. Re-read the SchemaFull definition for Bi_FactInvoiceItem and confirm that your BI tool or query engine is mapping columns by position using the current schema, not a cached or outdated definition. Re-apply the schema upgrade if necessary.
The ResidentTinNumber value for a guest has changed since the last report run.
The columns reflect the guest's profile data at the time each invoice line item was processed. If a guest's TIN was updated in Zenoti after an invoice was created, the updated value appears only on invoice line items created after the update β not on historical records. If you need the current TIN for all guests regardless of invoice date, join to Bi_DimUser for the current guest profile data.