Zero-DWH Power BI Integration with Automat BI Parquet Snapshots
materialize pre-deduplicated, typed Apache Parquet snapshots directly from your managed Delta tables (or HTTP sources) on a schedule and load them natively into Power BI over HTTPS
Cookbook: Zero-DWH Power BI Integration with Automat BI Parquet Snapshots
Modern BI stacks often force mid-sized teams to stand up and pay for an external cloud data warehouse (Snowflake, Azure SQL, or Synapse) and an On-Premises Data Gateway just to feed Power BI cleanly. With Automat BI, you can materialize pre-deduplicated, typed Apache Parquet snapshots directly from your managed Delta tables (or REST sources) on a schedule and load them natively into Power BI over HTTPS—with zero external warehouse infrastructure.
Architecture Overview
- Incremental Storage (Delta Table): Your daily ingestion pipeline writes raw transactional records into an append-only Delta table (e.g.,
orders_history.delta) with monotonic_verversioning. - Scheduled Snapshot Pipeline (LOB Parquet): A lightweight scheduled pipeline runs right after overnight ingestion, deduplicates records (
QUALIFY row_number()), flattens nested JSON fields, and writes a clean, flat.parquetfile (orders_snapshot.parquet). - Direct HTTPS Consumption (Power Query M): Power BI authenticates against
/api/v1/auth/validateusing a stateful token handshake and parses the binary stream directly in memory viaParquet.Document().
Step 1: Materialize a Clean LOB Parquet Snapshot
Configure a scheduled transformation pipeline (e.g., set to run at 04:00 right after your overnight ERP/API ingestion finishes) to read from your Delta table and overwrite a clean Parquet snapshot.
Pipeline YAML (orders-snapshot.yaml)
- name: ReadOrdersDelta
kind: tap
show: -1
connection:
kind: Deltatable
locator: orders_history.delta
query: |
SELECT
order_id,
quote_id,
order_no,
sum,
vat,
vat_sum,
company_id,
company_name,
IFNULL(TRY_CAST(company_address AS JSON), '{}'::JSON)->>'$.country' AS country,
project_id,
project_name,
owner_id,
owner_email,
TRY_CAST("date" AS DATE) AS "date",
year_month,
lines,
status
FROM Deltatable
QUALIFY row_number() OVER (
PARTITION BY order_id
ORDER BY modified_date DESC
) = 1
- name: SaveOrdersSnapshot
kind: sink
connection:
kind: Parquet
locator: orders_snapshot.parquet
Why This Step Matters
- Heavy lifting happens once in DuckDB: Window deduplication (
QUALIFY row_number()), date casting, and JSON path extraction (->>'$.country') execute inside Automat BI rather than slowing down Power Query M on every user refresh. - Typed & compressed: Parquet preserves column data types (
DATE,DOUBLE,TEXT) and columnar compression natively, keeping network payloads small and Power BI model refreshes fast. - Lookup tables work the same way: For small reference tables that don't require historical versioning, you can write a
Resttap directly to aParquetsink on the same schedule.
Step 2: How Authentication & Token Refresh Work
Automat BI uses stateful session tokens tracked on the backend by Token ID (tid):
- Before downloading data, Power Query sends the configured
X-Auth-TokentoGET /api/v1/auth/validate. - If the token's
tidis still active on the backend, the API validates the session and returns a refreshed token string (or200 OK). - If an administrator revokes the token (
tidinvalidated on the backend), the endpoint returns401 Unauthorizedor403 Forbidden, immediately cutting off access without requiring you to rotate shared database passwords.
Step 3: Connect Power BI via Power Query (M)
In Power BI Desktop, open Home → Get Data → Blank Query → Advanced Editor and paste the following M script.
Single-Table Query (Orders)
let
// 1. Configuration
BaseUrl = "https://eu.automatbi.com",
DataPath = "api/v1/data/download/data/orders_snapshot.parquet",
InitialToken = "eyJ0...your_api_token",
// 2. Step 1: Validate & Refresh Session Token
AuthCall = Web.Contents(BaseUrl, [
RelativePath = "api/v1/auth/validate",
Headers = [
#"accept" = "text/plain",
#"X-Auth-Token" = InitialToken
],
Timeout = #duration(0, 0, 10, 0),
ManualStatusHandling = {401, 403}
]),
AuthMetadata = Value.Metadata(AuthCall),
AuthStatus = AuthMetadata[Response.Status],
// 3. Token Resolution Logic
EffectiveToken =
if AuthStatus = 401 then
error "Authentication Failed: Session revoked in API."
else if AuthStatus = 403 then
error "Access Denied: You do not have permission for this resource."
else
let
NewTokenText = Text.FromBinary(AuthCall)
in
if Text.Length(NewTokenText) > 0 then NewTokenText else InitialToken,
// 4. Step 2: Download Parquet Snapshot
DataResponse = Web.Contents(BaseUrl, [
RelativePath = DataPath,
Headers = [
#"accept" = "application/octet-stream",
#"X-Auth-Token" = EffectiveToken
],
Timeout = #duration(0, 0, 10, 0)
]),
// 5. Parse Native Parquet Binary
DataTable = Parquet.Document(DataResponse)
in
DataTable
Crucial Detail: Why RelativePath Is Used
Notice that Web.Contents keeps BaseUrl static ("[https://eu.automatbi.com](https://eu.automatbi.com)") and passes the endpoint via RelativePath. Do not replace this with string concatenation (BaseUrl & "/" & DataPath). Power BI Service blocks scheduled cloud refreshes for queries that use dynamic string-concatenated URLs, whereas BaseUrl + RelativePath passes static lineage validation cleanly.
Step 4: Scale to Multiple Tables with a Reusable M Function
If your Power BI semantic model loads 5–10 Parquet snapshots (e.g., orders_snapshot.parquet, customers_snapshot.parquet, products_snapshot.parquet), wrap the handshake into a reusable Power Query function GetAutomatBiParquet:
(FileName as text) as table =>
let
BaseUrl = "https://eu.automatbi.com",
DataPath = "api/v1/data/download/data/" & FileName,
InitialToken = AutomatBiToken, // Store token in a Power BI Parameter
AuthCall = Web.Contents(BaseUrl, [
RelativePath = "api/v1/auth/validate",
Headers = [
#"accept" = "text/plain",
#"X-Auth-Token" = InitialToken
],
Timeout = #duration(0, 0, 10, 0),
ManualStatusHandling = {401, 403}
]),
AuthStatus = Value.Metadata(AuthCall)[Response.Status],
EffectiveToken =
if AuthStatus = 401 then
error "Authentication Failed: Session revoked in API."
else if AuthStatus = 403 then
error "Access Denied: You do not have permission for this resource."
else
let
NewTokenText = Text.FromBinary(AuthCall)
in
if Text.Length(NewTokenText) > 0 then NewTokenText else InitialToken,
DataResponse = Web.Contents(BaseUrl, [
RelativePath = DataPath,
Headers = [
#"accept" = "application/octet-stream",
#"X-Auth-Token" = EffectiveToken
],
Timeout = #duration(0, 0, 10, 0)
]),
DataTable = Parquet.Document(DataResponse)
in
DataTable
Then each table in your model becomes a clean one-liner:
let
Source = GetAutomatBiParquet("orders_snapshot.parquet")
in
Source
Step 5: Publishing & Scheduling Refresh in Power BI Service
- Create a Parameter (
AutomatBiToken): In Power Query, store your API token in aTextparameter so you can update or rotate it from the Power BI Service dataset settings without editing M code. - Set Privacy / Authentication Method: When Power BI prompts for credentials for
[https://<region>.automatbi.com](https://<region>u.automatbi.com), select Anonymous (authentication is handled explicitly via theX-Auth-Tokenheader) and set the privacy level to Organizational. - Align Refresh Schedules: If your Automat BI snapshot pipeline runs at
04:00, schedule your Power BI Service dataset refresh for04:30so every morning dashboard opens with fresh overnight data. - Alternative — Push to Customer Object Storage: If your organization prefers native cloud-storage connectors (or uses Tableau/Qlik alongside Power BI) without custom HTTP token scripts, configure an Automat BI sink to push
.parquetsnapshots directly to your own S3, Azure Blob, or Google Cloud Storage bucket using your own IAM credentials.