Read Fabric OneLake data in transformations

The features described in this article are in public preview and may change.

After you register a Fabric OneLake external data source, reference it in transformation SQL with ext_onelake, then write results to a CDF destination.

Write the SQL

Reference the source by externalId:

-- Table directly under Tables/
SELECT *
FROM ext_onelake('my-fabric-source', 'equipment')

-- Table under Tables/{schema}/{table}
SELECT *
FROM ext_onelake('my-fabric-source', 'equipment', 'operations')

Rule	Detail
Argument order	Source `externalId`, table name, optional schema name
Literals only	Each argument must be a single-quoted string literal
Two or three arguments	Any other arity is an error
Read-only	Use `ext_onelake` only as a `SELECT` source

For a data model destination, map columns to view properties and include externalId:

SELECT
  equipment_id                  AS externalId,
  equipment_name                AS name,
  CAST(commissioned_on AS DATE) AS commissionedDate,
  manufacturer                  AS manufacturer
FROM ext_onelake('my-fabric-source', 'equipment', 'operations')

Incremental loads with is_new

By default, each run reads the full Delta table. Incremental loads need a TIMESTAMP or epoch-millisecond LONG column that your pipeline updates when a row changes. Fabric does not create or update that column. You must populate it on every insert and update, then pass it to is_new(). CDF stores the high-water mark; the first run processes all rows.

SELECT
  equipment_id   AS externalId,
  equipment_name AS name,
  manufacturer   AS manufacturer
FROM ext_onelake('my-fabric-source', 'equipment', 'operations') a
WHERE is_new('equipment_cursor', a.lastUpdatedTime)

Argument	Detail
Filter name	Stable string unique within the transformation
Version column	A column your pipeline maintains, for example `a.lastUpdatedTime` — not the table alias alone

Passing the table alias as the second argument — is_new('cursor', a) — is rejected for OneLake. That alias form is for data modeling sources (cdf_nodes, cdf_edges, cdf_data_models). Always pass a column for Fabric OneLake.

Preview runs validate the query but do not advance the watermark. For general is_new patterns, see SQL patterns and best practices.

Create and run the transformation

Create a transformation with a data model destination (example):

curl -sS -X POST "${CDF_BASE}/transformations" \
  -H "Authorization: Bearer ${CDF_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{
    "items": [
      {
        "externalId": "fabric-equipment-to-dm",
        "name": "Fabric equipment to data model",
        "query": "SELECT equipment_id AS externalId, equipment_name AS name FROM ext_onelake('\''my-fabric-source'\'', '\''equipment'\'', '\''operations'\'')",
        "destination": {
          "type": "nodes",
          "view": {
            "space": "my-model-space",
            "externalId": "Equipment",
            "version": "v1"
          },
          "instanceSpace": "my-instance-space"
        },
        "conflictMode": "upsert",
        "ignoreNullFields": true,
        "isPublic": true
      }
    ]
  }'

Destination type	Use when
`nodes`	Instances of a single view
`edges`	Relationships between instances
`instances`	A data model type via `dataModel`

Bind session nonces and run the transformation as you do for other Transformations jobs. See Create a transformation.

Data engineering

Read Fabric OneLake data in transformations

Write the SQL

Incremental loads with is_new

Create and run the transformation

Next steps

​Write the SQL

​Incremental loads with is_new

​Create and run the transformation

​Next steps

Write the SQL

Incremental loads with is_new

Create and run the transformation

Next steps