Cookbook

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

← Back to Documentation Hub

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

  1. 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 _ver versioning.
  2. 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 .parquet file (orders_snapshot.parquet).
  3. Direct HTTPS Consumption (Power Query M): Power BI authenticates against /api/v1/auth/validate using a stateful token handshake and parses the binary stream directly in memory via Parquet.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 Rest tap directly to a Parquet sink 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-Token to GET /api/v1/auth/validate.
  • If the token's tid is still active on the backend, the API validates the session and returns a refreshed token string (or 200 OK).
  • If an administrator revokes the token (tid invalidated on the backend), the endpoint returns 401 Unauthorized or 403 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

  1. Create a Parameter (AutomatBiToken): In Power Query, store your API token in a Text parameter so you can update or rotate it from the Power BI Service dataset settings without editing M code.
  2. 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 the X-Auth-Token header) and set the privacy level to Organizational.
  3. Align Refresh Schedules: If your Automat BI snapshot pipeline runs at 04:00, schedule your Power BI Service dataset refresh for 04:30 so every morning dashboard opens with fresh overnight data.
  4. 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 .parquet snapshots directly to your own S3, Azure Blob, or Google Cloud Storage bucket using your own IAM credentials.

← Back to Documentation Hub