Query S3 data with external BI tools
After Zenoti writes data to your S3 bucket, you can load it into a data warehouse or query engine of your choice and build reports using standard SQL or BI tools.
This article explains how to set up your tables, load data from S3, keep data in sync incrementally, and run queries against both star schema and consolidated tables.
On this page
- Step 1: Create table structures from SchemaFull
- Step 2: Load data from CSV files
- Step 3: Set up incremental sync
- Fact tables β delete-insert pattern
- Dimension tables β update-insert (upsert) pattern
- Consolidated tables
- Tool-specific guidance
- Amazon Athena
- Snowflake
- Databricks
- Power BI and Tableau
- Python (pandas)
- Assign column names from SchemaFull definition
- Filter to current records only
- Sample queries
- Revenue by center (star schema)
- Revenue by center (consolidated table)
- Important reminders
Before you begin
- Your organization must have Analytics β Amazon S3 enabled with at least one completed full data load.
- You need read access credentials for your S3 bucket.
- You need a data warehouse or query engine (such as Snowflake, Amazon Athena, Databricks, or BigQuery) where you will load the data.
Step 1: Create table structures from SchemaFull
Before loading any data, create the destination tables in your data warehouse using the schema definitions in your S3 bucket.
Location: <your-bucket>/SchemaFull/
Each file in this folder defines the columns and data types for one table. Use these definitions to create the corresponding table structures in your warehouse (Snowflake, BigQuery, SQL Server, Amazon Redshift, etc.).
Tip: Apply schemas from SchemaFull only once during initial setup. For ongoing schema changes, use the SchemaUpgrade folder. See Handle schema changes and upgrades for details.
Step 2: Load data from CSV files
Data files are delivered as CSV with the following characteristics:
- No column headers β the first row is data, not a header row.
- Column order matches the schema file for that table.
- UTF-8 encoded, comma-delimited.
- Files are organized by GroupID folder.
When loading CSV files, configure your loader to skip headers (or set header rows to 0). Map columns by position using the schema definition.
Important: GroupID 1 is the initial full load. All higher GroupIDs are incremental updates. Always load GroupID 1 before loading subsequent incremental files.
Step 3: Set up incremental sync
After the initial full load, apply incremental GroupID files using the appropriate sync strategy for each table type.
Fact tables β delete-insert pattern
Fact tables (e.g., Bi_FactInvoiceItem, Bi_FactAppointment) include a void column:
| void value | Meaning |
|---|---|
0 |
Current, active record β insert or keep |
1 |
Soft-deleted record β remove from your warehouse |
Incremental sync steps for fact tables:
- Load the new GroupID file into a staging table.
- Delete all rows from your main table where the key matches a
void = 1row in the staging table. - Insert all rows from the staging table where
void = 0.
Dimension tables β update-insert (upsert) pattern
Dimension tables (e.g., Bi_DimCenter, Bi_DimEmployee) do not use a void column. Apply changes using an upsert:
- Load the new GroupID file into a staging table.
- For each row in staging, update the matching row in your main table (matched on the WID surrogate key), or insert if it does not exist.
Consolidated tables
Consolidated tables (e.g., Sales_Fact_Consolidated) are pre-joined and refreshed as a unit. Replace the entire table contents on each load, or apply upsert logic based on the primary key defined in the schema.
Tool-specific guidance
Amazon Athena
Create external tables in the AWS Glue Data Catalog pointing to the S3 folder paths for each table. Query directly with standard SQL without moving data.
CREATE EXTERNAL TABLE Bi_FactInvoiceItem (
invoiceitemwid BIGINT,
centerwid BIGINT,
finalsaleprice DECIMAL(18,4),
void INT
-- add remaining columns per SchemaFull definition
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION 's3://<your-bucket>/Bi_FactInvoiceItem/';
Athena reads files directly from S3. Filter
void = 0in all queries to exclude deleted records.
Snowflake
Create an external stage pointing to your S3 bucket, then use COPY INTO to load data into tables.
CREATE STAGE zenoti_stage
URL = 's3://<your-bucket>/'
CREDENTIALS = (AWS_KEY_ID='...' AWS_SECRET_KEY='...');
COPY INTO Bi_FactInvoiceItem
FROM @zenoti_stage/GroupID=2/Bi_FactInvoiceItem/
FILE_FORMAT = (TYPE = 'CSV' SKIP_HEADER = 0);
Databricks
Mount the S3 bucket or reference it directly using the S3A protocol, then read CSV files with Spark.
df = spark.read.option("header", "false").csv("s3a://<your-bucket>/GroupID=2/Bi_FactInvoiceItem/")
Assign column names from the SchemaFull definition before writing to a Delta table.
Power BI and Tableau
These tools connect most naturally to a data warehouse (Snowflake, Redshift, BigQuery, etc.) rather than directly to raw S3 CSV files.
Recommended approach:
- Load S3 data into your data warehouse using the steps above.
- Connect Power BI or Tableau to the data warehouse.
- Build your reports on top of the warehouse tables.
Both tools also support S3 connectors or Athena connections if you prefer to query S3 directly without a separate warehouse.
Python (pandas)
Use boto3 to download files from S3, then load with pd.read_csv with header=None.
import boto3
import pandas as pd
import io
s3 = boto3.client('s3')
obj = s3.get_object(Bucket='<your-bucket>', Key='GroupID=2/Bi_FactInvoiceItem/data.csv')
df = pd.read_csv(io.BytesIO(obj['Body'].read()), header=None)
## Assign column names from SchemaFull definition
df.columns = ['invoiceitemwid', 'centerwid', 'finalsaleprice', 'void', ...]
## Filter to current records only
df = df[df['void'] == 0]
Sample queries
Revenue by center (star schema)
SELECT
d.centername,
SUM(f.finalsaleprice) AS total_revenue
FROM Bi_FactInvoiceItem f
JOIN Bi_DimCenter d ON f.centerwid = d.centerwid
WHERE f.void = 0
GROUP BY d.centername
ORDER BY total_revenue DESC;
Revenue by center (consolidated table)
SELECT
centername,
SUM(finalsaleprice) AS total_revenue
FROM Sales_Fact_Consolidated
WHERE void = 0
GROUP BY centername
ORDER BY total_revenue DESC;
Important reminders
- CSV files have no headers. Always reference the SchemaFull file for the correct column order before loading.
- Filter
void = 0in all fact table queries to return only current, active records. - Do not query folders with a
_processingsuffix. These folders are still being written and may contain incomplete data. - GroupID 1 is always the full load. Incremental GroupIDs (2, 3,...) contain only changed records.