Reading Parquet from Fabric

Modified on Fri, 21 Aug at 1:22 PM

This guide shows how to consume the ILAP Analytics Parquet export from Microsoft Fabric: shortcut the storage container into a Lakehouse, and turn the snapshot folders into Delta tables a semantic model can read. It is for data engineers and report developers, and assumes familiarity with Fabric workspaces, Lakehouses and PySpark notebooks, and with the export's layout — see the Parquet export article for the folder structure, the completeness marker and the manifest. It covers reading only; the export is a system of record and Fabric should never be able to write to it.

For pulling live data through the REST or GraphQL API instead, see the Microsoft Fabric guide.

Before you start

You need an F SKU capacity. Reaching a firewalled storage account from Fabric uses trusted workspace access, which requires an F SKU and is explicitly unsupported on trial capacities. F2 is enough to make the path work — the F64 threshold people remember is about free-licence report viewers, not about this.

The storage account can stay closed. Trusted workspace access works when the account restricts public access to selected networks and when public access is disabled outright. Nothing about the export requires opening the account to the internet.

Fabric needs read access and nothing more. The workspace identity should hold Storage Blob Data Reader on the container. The archive is the only surviving copy of purged data; a writable connection is a hazard with no upside.

Whoever operates your ILAP Analytics deployment sets up the network rule and the role assignment. What you need from them is the container URL and confirmation that both are in place.

Create the shortcut

In the Lakehouse, create an ADLS Gen2 shortcut to the export container, authenticated with the workspace identity.

Target the dfs endpoint, not blob:

https://<account>.dfs.core.windows.net/<container>

ADLS Gen2 answers on both hostnames, so this looks like a free choice and is not. Where the account is reached over private endpoints, one provisioned for blob alone leaves every dfs call resolving publicly — and, with the account firewalled, failing.

The shortcut goes in Files/, not Tables/. A shortcut in the Tables section must point at a Delta table, and the export is plain Parquet. This is why the notebook below exists at all rather than being a convenience: see Why the export is plain Parquet for the reasoning behind that.

Create the shortcut after the network rule and the role assignment are both in place. One created before either exists fails, and a failed shortcut has to be deleted before you can retry.

Managed private endpoints are not an alternative route. OneLake shortcuts cannot use them, so the two approaches are mutually exclusive rather than interchangeable.

Read through the shortcut, never `abfss://`

Trusted workspace access does not cover Fabric Spark. It covers shortcuts, pipelines, semantic models, COPY and AzCopy. A notebook issuing

spark.read.parquet("abfss://<container>@<account>.dfs.core.windows.net/rs=1042/...")

fails no matter how the firewall is configured. Reading the same bytes through the shortcut is supported:

spark.read.parquet("Files/ia-export/rs=1042/reporting/Live_20260601000000/reportActivity.parquet")

This is the single most common way to lose an afternoon on this path, because the failure looks like a permissions problem rather than an unsupported one.

What the notebook has to get right

Everything below fails silently if skipped — no exception, just a report that is wrong in a way nobody can see. Each rule follows from the export's design; the Parquet export article explains why each holds.

Read only through the shortcut. Never abfss:// to the storage account.
Gate on _complete.json. Enumerate the planning folders and skip any without the marker. A reporting folder carries no marker of its own — derive its readability from its planning sibling.
Split the folder name on the first underscore. Live_nocutoff_318 is revision Live with the token nocutoff_318 — one token meaning no cut-off, where 318 is the planning schedule id that separates coexisting no-cut-off snapshots, not a cut-off. Splitting on the last underscore invents a revision type of Live_nocutoff and reads 318 as a cut-off.
Rename metadata columns to ann_<IlapId> before any union. Read MetadataFieldsByFile from _manifest.json and map physical names to stable ones per file. Union first and Location_2 from one snapshot merges with a different field of the same physical name from another.
Stamp every row with a snapshot key — (reportScheduleId, revisionType, cutoffToken) — and never union snapshots into a table that lacks it.
Assert the key is unique and fail the run on a duplicate. Exactly one version of each revision type exists per cut-off, so two complete folders on one key mean corruption or an unannounced layout change.
Report what was skipped, and what has gone missing since the last run. A snapshot mid-rewrite is correctly skipped, but under an overwriting load it then disappears from the output rather than keeping its previous rows. That is expected, and it must be visible.
Resolve a reference revision as-of, never by cut-off equality. See below.
Re-run it on a schedule. A re-import of the same schedule, revision and cut-off overwrites the export in place, so this is not a one-shot load. Each run picks up corrections; GeneratedAtUtc on the snapshot dimension is what makes a correction visible afterwards.

You need a planning-only pass for rules 2, 3 and 8, but you may not need the planning data. If every quantity your model shows comes from reporting, enumerate the planning folders for their markers, manifests and cut-offs and leave their fourteen data files unread — it halves the schema-drift surface, since planning carries its own independent metadata columns.

Matching Live against a reference revision

Cut-off coverage is ragged. Only Live has a full set: a baseline is typically re-set about twice a year, while Live may be imported weekly. Live and Baseline may occasionally share a cut-off, but never assume it.

The relationship is always as-of — the most recent reference snapshot at or before the Live cut-off — and never an equi-join on cut-off. An equi-join yields missing rows, which Power BI renders as gaps or zeroes rather than as an error.

Two details decide whether the as-of resolution is right:

No-cut-off sorts first. It is the state before actuals were tracked, so it precedes every dated cut-off and is the reference in force for any Live cut-off earlier than the first dated one. Sorting the raw tokens gets this backwards.
"No reference in force" is a real answer. A Live cut-off before the first dated reference, with no no-cut-off reference to fall back on, has none. Render that as a label, never as a null that silently blanks a visual.

For anything sourced from the reporting files this is only ever an annotation: both series come from columns of the same snapshot, so the export has already carried the applicable reference values into every cut-off and there is no join to get wrong. Use it to show which baseline is in force at each point — the reference series is a step function between re-baselines by construction, and a flat line invites being read as missing data.

Two kinds of step appear on such a chart and mean different things. A re-baseline step is real: the thing being measured against moved. An active-revision changeover step is an artefact of which snapshot owns reporting, not plan movement. Both are readable from the revision type on the snapshot dimension, and conflating them misleads.

A worked notebook

The shape below is deliberately thin. Every rule that can be silently wrong — path parsing, the completeness gate, column identity, key uniqueness, as-of resolution — is worth putting in plain functions you can unit-test on a laptop, rather than in notebook cells that need a running capacity to exercise.

import json, re
from pyspark.sql import functions as F
from notebookutils import mssparkutils

SHORTCUT = "Files/ia-export"   # the shortcut name, never a storage account URL

def list_paths():
    """Every path under the shortcut, relative to it."""
    found, pending = [], [SHORTCUT]
    while pending:
        for entry in mssparkutils.fs.ls(pending.pop()):
            if entry.isDir:
                pending.append(entry.path)
            else:
                found.append(entry.path.split(SHORTCUT + "/", 1)[-1])
    return found

def parse_snapshot_folder(path):
    """rs=<id>/<dataset>/<revisionType>_<cutoffToken>/... -> parts, or None."""
    segments = [s for s in path.strip("/").split("/") if s]
    if len(segments) < 3 or not segments[0].startswith("rs="):
        return None
    if segments[1] not in ("planning", "reporting"):
        return None
    # First underscore, not last: 'Live_nocutoff_318' is Live with the token
    # 'nocutoff_318' - no cut-off, 318 being the planning schedule id.
    index = segments[2].find("_")
    if index <= 0:
        return None
    return {
        "report_schedule_id": int(segments[0][3:]),
        "dataset": segments[1],
        "revision_type": segments[2][:index],
        "cutoff_token": segments[2][index + 1:],
        "path": "/".join(segments[:3]),
    }

def read_json(relative_path):
    return json.loads(mssparkutils.fs.head(f"{SHORTCUT}/{relative_path}", 1 << 22))

def rename_map(manifest, file_name, actual_columns):
    """Physical column name -> ann_<IlapId>, for the columns actually present."""
    by_file = manifest.get("MetadataFieldsByFile") or {}
    # Only fall back to the flat list when the per-file map is absent entirely.
    fields = by_file.get(file_name, []) if by_file else manifest.get("MetadataFields", [])
    present, mapping, claimed = set(actual_columns), {}, {}
    for field in fields:
        if field["ColumnName"] not in present:
            continue                                  # predates the field; null, not an error
        ilap_id = (field.get("IlapId") or "").strip()
        identity = ilap_id or f"id{field['MetadataFieldId']}"
        stable = "ann_" + re.sub(r"[^A-Za-z0-9_]", "_", identity)
        if stable in claimed:                         # fatal: one field's values under another's name
            raise ValueError(
                f"{claimed[stable]!r} and {field['ColumnName']!r} in {file_name!r} "
                f"both resolve to {stable!r}")
        claimed[stable] = field["ColumnName"]
        mapping[field["ColumnName"]] = stable
    return mapping

def load(snapshot, file_name, manifest):
    # 'reporting_path' comes from discovery pairing a complete planning folder with its
    # reporting sibling; remember that reporting keeps the bare 'nocutoff' token.
    frame = spark.read.parquet(f"{SHORTCUT}/{snapshot['reporting_path']}/{file_name}")
    # Rename BEFORE any union, never after.
    for physical, stable in rename_map(manifest, file_name, frame.columns).items():
        frame = frame.withColumnRenamed(physical, stable)
    key = "{report_schedule_id}|{revision_type}|{cutoff_token}".format(**snapshot)
    return (frame
            .withColumn("snapshot_key", F.lit(key))
            .withColumn("report_schedule_id", F.lit(snapshot["report_schedule_id"]))
            .withColumn("revision_type", F.lit(snapshot["revision_type"]))
            .withColumn("cutoff_token", F.lit(snapshot["cutoff_token"])))

Discovery then pairs each complete planning folder with its reporting sibling — remembering that a reporting folder keeps the bare nocutoff where planning appends its schedule id, and that a planning-only snapshot is normal rather than incomplete — and the union writes one Delta table per reporting file:

frames = [load(s, "reportActivity.parquet", manifests[s["key"]])
          for s in complete if s["reporting_path"]]

combined = frames[0]
for frame in frames[1:]:
    # allowMissingColumns: a metadata field absent from one snapshot reads as null,
    # which is safe only because the columns were resolved by IlapId first.
    combined = combined.unionByName(frame, allowMissingColumns=True)

combined.write.mode("overwrite").option("overwriteSchema", "true") \
        .saveAsTable("fact_report_activity")

Report on the resulting Delta tables from Power BI, with Direct Lake or import.

When the data looks stale

If a snapshot has demonstrably been re-imported but Fabric still shows the previous figures, suspect the OneLake shortcut cache before suspecting the export. The Fabric REST API exposes a Reset Shortcut Cache operation for exactly this.

If a snapshot has vanished from your tables since the last run, it was most likely mid-rewrite when the notebook ran, and skipped by the completeness gate. Re-run once the import has finished.