Microsoft Fabric (via Collector Method)

Updated in Release 6.2

About Collectors

Collectors are extractors that are developed and managed by you (a customer of K).

KADA provides python libraries that customers can use to quickly deploy a Collector.

Why you should use a Collector

There are several reasons why you may use a collector vs the direct connect extractor:

  1. You are using the KADA SaaS offering and it cannot connect to your sources due to firewall restrictions

  2. You want to push metadata to KADA rather than allow it to pull data for security reasons

  3. You want to inspect the metadata before pushing it to K

Using a collector requires you to manage:

  1. Deploying and orchestrating the extract code

  2. Managing a high water mark so the extract only pulls the latest metadata

  3. Storing and pushing the extracts to your K instance


Integration details

Scope

Included

Comments

Metadata

YES

Warehouses and Lakehouse SQL analytics endpoints: schemas, tables, views, columns, procedures, functions

Lineage

YES

View / procedure / function → table, from sys.sql_expression_dependencies. Column level where Fabric records it. Lakehouse shortcuts. dbt jobs (optional)

Usage

YES

Fabric query insights. Fabric keeps only 30 days of history

Roles

YES

Workspace roles (Admin / Member / Contributor / Viewer). Group members are not expanded

Sensitive Data Scanner

NO


Notebooks, Data pipelines

Via Power BI collector

See "Fabric items collected by the Power BI collector" below

Semantic models, reports, dashboards

Via Power BI collector

Onboard the Power BI source as usual

Fabric items collected by the Power BI collector

This collector covers the Warehouses and Lakehouses of a workspace: their tables, views, routines, shortcuts, query history and dbt jobs. The other Fabric items are collected by the Power BI collector, so to get the full Fabric picture you need both collectors:

Fabric item

Collected by

How to enable

Warehouse, Lakehouse (SQL analytics endpoint), shortcuts, dbt jobs

Fabric collector (this page)

—

Semantic models, reports, dashboards, dataflows

Power BI collector

Standard Power BI onboarding

Notebooks (with the tables and files they read and write)

Power BI collector

include_fabric_notebooks: true

Data pipelines (with their activity lineage, and the notebooks / pipelines they run)

Power BI collector

include_fabric_pipelines: true

Notebook runtime lineage from Spark OpenLineage events

Power BI collector

include_fabric_openlineage: true (needs include_fabric_notebooks)

Notes:

  • The Power BI Fabric options need the following for the Power BI collector's service principal, on top of the standard Power BI onboarding:

    • Contributor on each Fabric workspace, because notebook and pipeline definitions are not covered by the Power BI admin scanner

    • ‘Service principals can call Fabric public APIs’ (‘Service principals can use Fabric APIs’ on older tenants) enabled in the Fabric Admin portal → Tenant settings

    • For include_fabric_openlineage only: ‘Users can access data stored in OneLake with apps external to Fabric’ enabled in the Fabric Admin portal → Tenant settings, and HTTPS (443) access from the collector server to onelake.dfs.fabric.microsoft.com

  • Runtime lineage only appears for notebooks that register Fabric's bundled OpenLineage Spark listener. The Power BI collector reads these events from each lakehouse's Files/Lineage/ folder in OneLake.

  • With include_fabric_openlineage, keep the Power BI collector's input_path on persistent storage. The OpenLineage history is built up there across runs, and files removed from OneLake can only be recovered from it.

  • Notebook and pipeline lineage points at the tables this collector extracts. For the two to join up in K, the Fabric source must be onboarded with the workspace's SQL connection string as its host (see Step 1).

  • Shortcut FILE objects are emitted by both collectors with the same ids. This collector places them on their lakehouse, so keep include_file_shortcuts on when the Power BI Fabric options are used.


Pre-requisites

Collector Server Minimum Requirements

For the collector to operate effectively, it will need to be deployed on a server with the below minimum specifications:

  • CPU: 2 vCPU

  • Memory: 8GB

  • Storage: 30GB (depends on historical data extracted)

  • OS: unix distro e.g. RHEL preferred but can also work with Windows Server

  • Python 3.10.x or later

  • Access to K landing directory

Microsoft Fabric Requirements

  • A Service Principal in Microsoft Entra ID (App registration) with a client secret

    • You will need for the setup

      • Application (client) ID

      • Directory (tenant) ID

      • Secret Value (the Value column, not the Secret ID)

  • ‘Service principals can call Fabric public APIs’ must be enabled in the Fabric Admin portal → Tenant settings

    • On older tenants this setting is labelled ‘Service principals can use Fabric APIs’

    • If the setting is limited to specific security groups, the Service Principal must be a member of one of them

  • The Service Principal must be added to the workspace (Workspace → Manage access) with the Contributor role

    • Contributor gives access to the warehouse and lakehouse metadata and to query insights (query history)

  • For each workspace to onboard

    • The Workspace ID: the guid in the workspace URL, https://app.fabric.microsoft.com/groups/<workspace id>/...

    • The SQL connection string: used as the Host name of the source in K, see Step 1 for where to find it

  • A lakehouse is skipped until its SQL analytics endpoint has finished provisioning

One source per workspace

  • Each Fabric workspace must be onboarded as its own source in K, with its own collector configuration and its own workspaces value

    • Every workspace has its own SQL endpoint host, shared by all the warehouses and lakehouses in it. K identifies a source's tables by that host, so tables from two workspaces cannot be loaded into one source

    • The collector stops with an error if its configuration covers more than one workspace

  • To onboard several workspaces (e.g. Dev, Test and Prod), repeat Step 1 and Step 4 for each workspace

    • Give each source a name that identifies the workspace, e.g. Fabric - Sales Prod

    • Each source uses its own workspace's SQL connection string as its Host name

  • Lineage between workspaces (e.g. a shortcut in one workspace pointing at a table in another) joins up automatically, as long as both workspaces are onboarded with their own SQL connection string as the host

Optional Requirements

  • To load workspace roles into K: add the Service Principal to the workspace with the Member role instead of Contributor

    • Without it the collector still runs, but no workspace roles are loaded

  • To collect Fabric dbt jobs (include_dbt_jobs)

    • ‘Users can access data stored in OneLake with apps external to Fabric’ must be enabled in the Fabric Admin portal → Tenant settings

    • HTTPS (443) access from the collector server to onelake.dfs.fabric.microsoft.com

  • To collect lineage for shortcuts that point at a lakehouse or warehouse in another workspace: the Service Principal must also have access to that workspace

    • Without it those shortcut links are skipped

  • To collect Fabric notebooks and Data pipelines: these come from the Power BI collector, see "Fabric items collected by the Power BI collector" above

Each Fabric workspace must be onboarded as its own source in K, using that workspace's SQL connection string as the Host name. The collector stops with an error if its configuration covers more than one workspace.


Step 1: Create the Source in K

Create a Fabric source in K, one per Fabric workspace (see "One source per workspace")

  • Go to Settings, Select Sources and click Add Source

  • Select “Load from File” option

  • Give the source a Name - e.g. Fabric - Sales Prod

  • Add the Host name: the SQL connection string of the workspace, e.g. abcd1234efgh5678.datawarehouse.fabric.microsoft.com

  • Click Finish Setup

Finding the SQL connection string in Fabric

  1. Open the workspace in Fabric

  2. In the workspace item list, find any Warehouse or Lakehouse SQL analytics endpoint, and select … (More options) → Copy SQL connection string

    • Alternatively, open the warehouse or SQL analytics endpoint and go to Settings → SQL endpoint, where the SQL connection string can be copied

  3. Paste it as the Host name, as it is copied: the host only, with no https:// and no port

Every warehouse and lakehouse in a workspace shares the same SQL connection string, so it does not matter which item it is copied from. A different workspace has a different one.

The Host name entered here must match the host the collector writes, which by default is this SQL connection string. If the source was already onboarded under a different Host name, set that name in the collector's host field instead.


Step 2: Getting Access to the Source Landing Directory

When using a Collector you will push metadata to a K landing directory.

To find your landing directory you will need to:

  1. Go to Platform Settings - Settings. Note down the value of this setting:

    • If using Azure: storage_azure_storage_account

    • If using AWS:

      • storage_root_folder - the AWS s3 bucket

      • storage_aws_region - the region where the AWS s3 bucket is hosted

  2. Go to Sources - Edit the Source you have configured. Note down the landing directory in the About this Source section.

To connect to the landing directory you will need:

  • If using Azure: a SAS token to push data to the landing directory. Request this from KADA Support (support@kada.ai)

  • If using AWS:

    • An Access key and Secret. Request this from KADA Support (support@kada.ai)

    • OR provide your IAM role to KADA Support to provision access.


Step 3: Install the Collector

It is recommended to use a python environment such as pyenv or pipenv if you are not intending to install this package at the system level.

Some python packages also have dependencies on the OS level packages, so you may be required to install additional OS packages if the below fails to install.

You can download the latest Core Library and Fabric whl via Platform Settings → Sources → Download Collectors

Run the following command to install the collector

pip install kada_collectors_extractors_fabric-1.0.0-py3-none-any.whl

You will also need to install the common library kada_collectors_lib for this collector to function properly.

pip install kada_collectors_lib-<version>-none-any.whl

Note that you will also need an ODBC driver manager installed at the OS level for pyodbc to use, as well as the Microsoft ODBC Driver 18 for SQL Server, see Pre-requisites.


Step 4: Configure the Collector

The collector requires a set of parameters to connect to and extract metadata from Microsoft Fabric.

FIELD

FIELD TYPE

DESCRIPTION

EXAMPLE

client

string

Application (client) id of the Entra application

"00000000-0000-0000-0000-000000000000"

secret

string

Secret value of the Entra application


tenant

string

Tenant id the Entra application is registered in

"00000000-0000-0000-0000-000000000000"

workspaces

list<string>

The id (guid) of the workspace to extract. Set exactly one, see "One source per workspace". If left empty every workspace the application can see is used, and the run fails if that is more than one

["11111111-1111-1111-1111-111111111111"]

item_types

list<string>

Which item types to extract, any of Warehouse, Lakehouse. Defaults to both

["Warehouse", "Lakehouse"]

items

list<string>

Optional list of warehouse / lakehouse names to extract. Empty extracts all of them

[]

host

string

Optional. Only needed if the source in K was onboarded under a Host name other than the workspace SQL connection string. Leave empty otherwise

""

driver

string

The ODBC driver name

"ODBC Driver 18 for SQL Server"

connection_timeout

integer

ODBC connection timeout in seconds

30

meta_only

boolean

Extract metadata only, skipping query history

false

events_only

boolean

Extract query history only, skipping metadata

false

chunk_hours

integer

Optional. Split the query history extraction into windows of this many hours, to limit memory use on busy workspaces. Leave null for a single window

null

hwm_safety_minutes

integer

How far behind the run's end time the next high water mark is set, so queries Fabric has not yet published are picked up on the next run. Default 60

60

include_file_shortcuts

boolean

Collect lakehouse shortcuts under Files as FILE objects on the lakehouse. When false only shortcuts backing a table are collected

true

include_dbt_jobs

boolean

Collect Fabric dbt jobs: models, tests and model lineage, plus run history when both metadata and query history are extracted (neither meta_only nor events_only set). See "Optional Requirements"

false

input_path

string

Folder where raw files read from OneLake (dbt manifests) are landed before they are processed. Replaced on every run

"/tmp/input"

output_path

string

Absolute path to the output location where files are to be written. Its contents are deleted at the start of every run

"/tmp/output"

mask

boolean

To enable masking or not

true

masking_workers

integer

Number of workers used for masking

1

compress

boolean

To gzip the output or not

true

These parameters can be added directly into the run or you can pass the parameters in via a JSON file. The following is an example you can use that is included in the example run code below.

kada_fabric_extractor_config.json

JSON
{
    "client": "",
    "secret": "",
    "tenant": "",
    "workspaces": [""],
    "item_types": ["Warehouse", "Lakehouse"],
    "items": [],
    "host": "",
    "driver": "ODBC Driver 18 for SQL Server",
    "connection_timeout": 30,
    "meta_only": false,
    "events_only": false,
    "chunk_hours": null,
    "hwm_safety_minutes": 60,
    "include_file_shortcuts": true,
    "include_dbt_jobs": false,
    "input_path": "/tmp/input",
    "output_path": "/tmp/output",
    "mask": true,
    "masking_workers": 1,
    "compress": true
}

Step 5: Run the Collector

The following code is an example of how to run the extractor. You may need to uplift this code to meet any code standards at your organisation.

This can be executed in any python environment where the whl has been installed.

This is the wrapper script: kada_fabric_extractor.py

Python
import os
import argparse
from kada_collectors.extractors.utils import load_config, get_hwm, publish_hwm, get_generic_logger
from kada_collectors.extractors.fabric import Extractor

get_generic_logger('root')  # Set to use the root logger, you can change the context accordingly or define your own logger

_type = 'fabric'
dirname = os.path.dirname(__file__)
filename = os.path.join(dirname, 'kada_{}_extractor_config.json'.format(_type))

parser = argparse.ArgumentParser(description='KADA Microsoft Fabric Extractor.')
parser.add_argument('--config', '-c', dest='config', default=filename, help='Location of the configuration json, default is the config json in the same directory as the script.')
parser.add_argument('--name', '-n', dest='name', default=_type, help='Name of the collector instance.')
args = parser.parse_args()

start_hwm, end_hwm = get_hwm(args.name)

ext = Extractor(**load_config(args.config))
ext.test_connection()
ext.run(**{"start_hwm": start_hwm, "end_hwm": end_hwm})

# Publish end_hwm less hwm_safety_minutes, not end_hwm. Fabric can lag a completed query
# by 15 minutes or more, so publishing end_hwm would skip that window.
publish_hwm(args.name, ext.next_hwm)

Publish ext.next_hwm, not end_hwm as the other collector wrappers do. Fabric can take 15 minutes or more to publish a completed query to query insights, so publishing end_hwm would permanently skip those queries. As a result consecutive runs overlap by hwm_safety_minutes, which K handles.

When onboarding several workspaces, run one collector per workspace, each with its own config file and its own --name, so each keeps its own high water mark file, e.g. python kada_fabric_extractor.py -c sales_prod.json -n fabric_sales_prod.

Advanced options:

If you wish to maintain your own high water mark files elsewhere you can use the above section’s script as a guide on how to call the extractor. The configuration file is simply the keyword arguments in JSON format. Refer to this document for more information Collector Integration General Notes | Storing High Water Marks (HWM)

If you are handling external arguments of the runner yourself, you’ll need to consider additional items for the run method. Refer to this document for more information Collector Integration General Notes | Extractor run method

Python
from kada_collectors.extractors.fabric import Extractor

kwargs = {}  # However you choose to construct your args
hwm_kwargs = {"start_hwm": "YYYY-MM-DD HH:MM:SS", "end_hwm": "YYYY-MM-DD HH:MM:SS"}  # UTC

ext = Extractor(**kwargs)
ext.run(**hwm_kwargs)
next_hwm = ext.next_hwm  # Store this as the start_hwm of the next run

class Extractor(client: str = None, secret: str = None, tenant: str = None, \
    workspaces: list = [], item_types: list = ['Warehouse', 'Lakehouse'], items: list = [], \
    host: str = None, driver: str = 'ODBC Driver 18 for SQL Server', connection_timeout: int = 30, \
    output_path: str = './output', mask: bool = False, masking_workers: int = 1, \
    compress: bool = False, meta_only: bool = False, events_only: bool = False, \
    chunk_hours: int = None, hwm_safety_minutes: int = 60, include_file_shortcuts: bool = True, \
    include_dbt_jobs: bool = False, input_path: str = './input') -> None

client: Application (client) id of the Entra application
secret: Secret value of the Entra application
tenant: Tenant id the Entra application is registered in
workspaces: List of Fabric workspace guids, must resolve to a single workspace
item_types: Which item types to extract, any of Warehouse / Lakehouse
items: Optional list of item names to extract, empty means all of them
host: Host name override, only when the source in K uses a Host name other than the SQL connection string
driver: The ODBC driver name
connection_timeout: ODBC connection timeout in seconds
output_path: full or relative path to where the outputs should go
mask: To mask the META/DDL/DATABASE_LOG files or not
masking_workers: Number of workers used for masking
compress: To gzip output files or not
meta_only: extract metadata only
events_only: extract query history only
chunk_hours: Number of hours to chunk the query history extraction into
hwm_safety_minutes: How far behind end_hwm to publish the next high water mark
include_file_shortcuts: Collect shortcuts under Files as FILE objects on their lakehouse
include_dbt_jobs: Collect Fabric dbt jobs
input_path: Landing folder for raw files read from OneLake


Step 6: Check the Collector Outputs

K Extracts

A set of files will be generated. These files will appear in the output_path directory you set in the configuration details:

File

Contents

METADATA

Tables, views and columns. Tables backed by a shortcut are typed EXTERNAL TABLE

DDLMETADATA, DDL

Views, procedures and functions with their definitions

METADATA_KEYS

Primary / foreign keys. Usually empty: Fabric only supports NOT ENFORCED keys and lakehouse tables have none

LINKAGES

Lineage: view / procedure / function → table and column, shortcuts, dbt models

META

Shortcut FILE objects, dbt projects, models, tests and jobs

PROPERTIES

fabric_shortcut_target on every table backed by a shortcut, dbt properties

ROLES

Workspace roles and the users / service principals in them

DATABASELOG

Query history from query insights

EVENTS, METRICS

dbt job run history and test results. Only when include_dbt_jobs is true and both metadata and query history are extracted (neither meta_only nor events_only set)

High Water Mark File

A high water mark file is created in the same directory as the execution called fabric_hwm.txt (or <name>_hwm.txt when --name is set). This file is only produced if you call the publish_hwm method.


Step 7: Push the Extracts to K

Once the files have been validated, you can push the files to the K landing directory.

You can use Azure Storage Explorer if you want to initially do this manually.


Known limitations

  • Query history covers the last 30 days only. On the first run, or after a gap longer than 30 days, anything older is gone in Fabric and cannot be collected.

  • Column level lineage is partial. Fabric only records column dependencies for some objects, and only within the same warehouse / lakehouse. Object level lineage is unaffected.

  • Notebooks and Data pipelines are not read by this collector. They come from the Power BI collector's Fabric options, see "Fabric items collected by the Power BI collector".

  • Lineage of tables built by dbt comes from the dbt models (include_dbt_jobs), as the warehouse itself keeps no record of how a table was built.

  • Group, whole-tenant and service principal profile role assignments are recorded as roles but their members are not listed, as this would require Microsoft Graph access.

  • Roles cover workspace access only. Item-level sharing, SQL permissions (GRANTs and database roles) and OneLake data access roles are not collected.

  • Failed queries are not collected, only those that succeeded.

Troubleshooting

Message

Cause / fix

The items in scope sit behind N different SQL endpoints

More than one workspace is in scope. Set workspaces to a single workspace id, see "One source per workspace"

No warehouses or lakehouses were found

Wrong workspace id, the Service Principal has no role on the workspace, or ‘Service principals can call Fabric public APIs’ is off

Could not acquire a token

Wrong client, secret or tenant, the Secret ID was used instead of the Secret Value, or the secret has expired

Could not read role assignments ... requires the Member role

The Service Principal has Contributor. ROLES will be empty; grant Member to collect roles

Could not read the run output of dbt job ... (401/403)

The OneLake tenant setting is off, or the Service Principal is below Contributor

... could not be extracted, stopping the run so K does not load a partial file

A metadata, lineage or query history query failed on one warehouse / lakehouse. The run stops before the high water mark is published so nothing is lost; check the Service Principal's role and re-run

Files load but tables show no lineage to other sources in K

The Host name of the source in K does not match the SQL connection string. Correct the Host name, or set host in the collector config to the Host name used in K

Last updated: