Microsoft Fabric: bringing external company data into OneLake

This guide shows how to bring company data that arrives as files or API output into Microsoft Fabric: where it lands in OneLake, whether it belongs in a lakehouse or a warehouse, how to keep the original files, how to turn them into tables and how to run the load every day. The worked examples use the Fokals datasets Hiring Activity, Market Series and Job Postings, and the steps suit any licensed feed of files.

Fokals is delivered direct, by REST API and as bulk files in CSV, JSON Lines or JSON, which you load with Fabric's own pipelines, notebooks and COPY INTO. You fetch the files or call the API, and Fabric holds the result. See how Fokals is delivered.

What OneLake gives you to land data in

Microsoft describes Fabric as an analytics platform in which Data Engineering, Data Factory, Data Warehouse and other experiences work over one storage layer, OneLake. Every tenant has a single OneLake, built on Azure Data Lake Storage, and it stores tables in Delta Parquet or Iceberg format. You organise it into workspaces, and a workspace holds items such as lakehouses and warehouses. OneLake supports the ADLS Gen2 APIs and SDKs, so existing ADLS Gen2 applications can use it: a workspace appears as a container and each item as a folder.

A lakehouse has two top-level areas. Tables holds managed Delta tables. Files holds data that is not a Delta table, which is where a vendor's files belong until you load them.

Lakehouse or warehouse

Microsoft's decision guide compares the two. Both store data in Delta format in OneLake and share a SQL engine. They differ in how you develop, which data they hold and what SQL can write.

For company data the choice follows the team. Fokals files are CSV, JSON Lines or JSON, and in a CSV file lists and objects, such as open postings by job function, are held as JSON in a single cell. A lakehouse suits a team that parses those cells with Spark. A team that works in T-SQL can load the same files into a warehouse with COPY INTO, which Microsoft's ingestion page says reads CSV, JSONL and Parquet from ADLS Gen2, Blob Storage and OneLake. The two can be combined: Microsoft's lakehouse page describes landing and transforming data in a lakehouse and then exposing curated tables to a warehouse. A warehouse is the choice when you need multi-table transactions or when analysts must write with SQL.

Land the files before you load them

Keep what arrives. Microsoft's medallion guide calls the first layer bronze and describes it as data stored exactly as it arrives, with silver for corrected and standardised data and gold for curated tables. It recommends a separate workspace for each layer, which also gives you one place to apply access rules to the raw files. For a vendor feed that means one folder for each table and period in the Files area of a bronze lakehouse:

Files/fokals/company_hiring_daily/2026-10-03/
Files/fokals/company_hiring_daily/2026-10-04/
Files/fokals/market_series/2026-09-27/

Put the export's manifest in the same folder. A Fokals export comes with a manifest that names its period, label versions and licence, so anyone who opens the folder can see what it is and under which licence it was received.

Microsoft's page on ingestion options lists the ways in:

The REST connector in a copy activity reads only JSON response payloads, according to Microsoft's connector page, so a CSV or JSON Lines export is fetched in a notebook. Each Fokals client has one bearer key with scopes, and rate limits apply per key by minute and by day. Keep the key in Azure Key Vault: Microsoft's credentials page says never to hardcode secrets in a notebook and shows getSecret for reading one.

import os
import requests

vault = "https://<your-vault>.vault.azure.net/"
api_key = notebookutils.credentials.getSecret(vault, "fokals-api-key")

export_url = "<the export endpoint for the table, from the API reference>"
period = "2026-10-03"
folder = f"/lakehouse/default/Files/fokals/company_hiring_daily/{period}"
os.makedirs(folder, exist_ok=True)

with requests.get(
    export_url,
    headers={"Authorization": f"Bearer {api_key}"},
    stream=True,
    timeout=300,
) as response:
    response.raise_for_status()
    with open(f"{folder}/company_hiring_daily.csv", "wb") as out:
        for chunk in response.iter_content(chunk_size=1 << 20):
            out.write(chunk)

From files to Delta tables

There are two routes to a table. The first is a table shortcut with a file transformation. Microsoft's page on it says it works with folders from any source that shortcuts support, converts CSV, Parquet or JSON files into a Delta table, checks the folder every two minutes and appends the rows of new files. It needs files with identical schemas, works in a lakehouse only and does not accept MERGE or DELETE on the target table. That fits tables Fokals writes once after the period closes, such as Hiring Activity, provided a file with a changed layout lands in a new folder. A breaking change to labels or scores ships as a new version with at least 90 days' notice, so the new folder can be planned.

The second route is a notebook, which gives you control of column types. Microsoft's Load to tables page says that screen takes CSV or Parquet and cannot set an explicit schema. The JSON cells contain quotes and commas, so read a sample file first and match the quote and escape options to how it is written. Keep the JSON cells as text in the main table and expand them into a second table with one row for each company, day and job function. Microsoft's Direct Lake page says Direct Lake tables do not support complex Delta column types, so a map column would rule out Direct Lake for Power BI later.

from datetime import datetime, timezone

from pyspark.sql import functions as F
from pyspark.sql.types import LongType, MapType, StringType

loaded_at = F.lit(datetime.now(timezone.utc))

raw = (
    spark.read
    .option("header", True)
    .option("inferSchema", True)
    .option("quote", '"')
    .option("escape", '"')  # match the quoting of your sample file
    .csv("Files/fokals/company_hiring_daily/2026-10-03/")
)

hiring = raw.withColumn("day", F.to_date("day")).withColumn("loaded_at", loaded_at)
hiring.write.mode("append").format("delta").saveAsTable("company_hiring_daily")

by_function = hiring.select(
    "company_id",
    "day",
    F.explode(
        F.from_json("by_function", MapType(StringType(), LongType()))
    ).alias("job_function", "open_postings"),
    "loaded_at",
)
by_function.write.mode("append").format("delta").saveAsTable("company_hiring_by_function")

The notebook stamps loaded_at, a column you own, which later tells you what arrived and when. Two rules in the data dictionary shape the load. Daily and weekly rows are written once and never changed, so append each period once and load only periods that are not yet in the table, because a rerun appends a period twice. A period written more than seven days after it closed carries reconstructed=true, so a period can arrive late. For tables whose rows describe a state that moves, such as Job Postings (job_postings) with its last_seen_at and closed_at, upsert on posting_id instead of appending. Microsoft's medallion guide names MERGE for upserts, and the warehouse supports MERGE as a generally available statement.

Schedule the load

A data pipeline can run the notebook on a schedule. A schedule takes a start and end date, a frequency and a time zone, has no open-ended option, and a pipeline can hold up to 20 of them. Scheduled runs can email a failure notice. Fokals times are UTC. Each daily table is written once after the UTC day closes and each weekly table after the Monday to Sunday week closes, so set the schedule's time zone to UTC and choose a time after the files have appeared in your sample.

On the API route, keep the cursor of the last page you loaded in a small table. Feeds run oldest first from a time you set, so the last cursor is the bookmark. The guide to keeping a warehouse in step with incremental API sync covers the pattern.

Join to your own data

Every Fokals row carries company_id, and for a listed company its isin, figi, lei, ticker and mic. Join on the identifier your own tables hold. The lakehouse's SQL analytics endpoint reads the Delta tables with T-SQL. The hiring dataset page says what each table holds.

SELECT p.portfolio, h.day, h.open_postings, h.new_postings
FROM dbo.company_hiring_daily AS h
JOIN dbo.positions AS p ON p.isin = h.isin
WHERE h.day >= '2026-09-01';

What OneLake adds

OneLake holds what you land and keeps it under the access rules of your workspace. Fokals delivers CSV, JSON Lines and JSON, and the conversion to Delta tables is your step, as above. A shortcut reaches data in place and points at the files once you have landed them. OneLake shortcuts and external data sharing covers what that means for a licence, and Power BI reports on company signals models these tables for reporting.

Frequently asked questions

The queries and code on this page are examples to adapt. Test them in your own environment before you rely on them.

What this page says about the products it names was checked against their public documentation on 4 October 2026. Product and company names are trademarks of their owners. Fokals is not affiliated with them or endorsed by them.