Working with Fabric Lakehouses and OneLake

Find and connect to a Lakehouse

Find the workspace by name, then select one of its Lakehouses:

library(fabricQueryR)

workspaces <- fabric_workspaces()
matches <- Filter(
  \(x) identical(x$displayName, "Analytics workspace"),
  workspaces
)
stopifnot(length(matches) == 1L)
workspace <- matches[[1L]]
lakehouse <- workspace$lakehouses()[[1L]]

lakehouse$displayName

$lakehouses() is the workspace method for fabric_lakehouses().

Fabric provides a SQL analytics endpoint for querying the managed tables under Tables/. It does not query ordinary files under Files/. The endpoint address is already included in lakehouse, so you do not need to copy it from the Fabric portal.

The discovered FabricLakehouse is a read-only R6 object. For a single SQL query, call $sql_query() (fabric_sql_query()):

orders <- lakehouse$sql_query(
  "SELECT TOP 10 * FROM dbo.orders"
)

The default numeric_policy = "auto" uses ODBC’s numeric conversion and warns once per session that precision may be lost. Use numeric_policy = "driver" to explicitly accept that conversion without a warning, or "exact" to reject unsafe ODBC results. Use backend = "adbc" for exact conversion; see the setup in Bring Fabric data into R.

The method opens and closes the SQL connection for you. If you want to run several commands with ‘DBI’, use $sql_connect() (fabric_sql_connect()):

con <- lakehouse$sql_connect()
DBI::dbListTables(con)
DBI::dbGetQuery(con, "SELECT TOP 10 * FROM dbo.orders")
DBI::dbDisconnect(con)

For most work with managed tables, start with SQL. Use $sql_query() (fabric_sql_query()) for filters, joins, and summaries, or $sql_read_table() (fabric_sql_read_table()) to read one table without writing SQL. Use $sql_connect() (fabric_sql_connect()) only when you want to keep a connection open for several ‘DBI’ calls.

You can also read a Delta table directly through OneLake with $read_table() (fabric_lakehouse_read_table()). This is useful for an Arrow stream or an earlier table version. Direct Delta reads use Python through ‘reticulate’; see ?fabric_delta_config for runtime requirements and setup diagnostics. They require OneLake data access and a supported Delta protocol. Use SQL or Spark for tables with unsupported features such as Type Widening or V2 Checkpoints.

The $onelake_*() methods correspond to the fabric_onelake_*() functions and work with ordinary files under Files/, while $write_table() (fabric_lakehouse_write_table()) adds or replaces managed table data. These operations do not need a SQL connection. Never change the files underneath Tables/ directly because they are part of a managed Delta table.

List and read ordinary files

Start by listing a small folder with $onelake_list() (fabric_onelake_list()):

files <- lakehouse$onelake_list(
  path = "Files/incoming"
)

files[c("path", "is_directory", "content_length")]

Read a supported tabular file directly into R with $onelake_read_file() (fabric_onelake_read_file()):

orders <- lakehouse$onelake_read_file(
  path = "Files/incoming/orders.csv"
)

head(orders)

Use $onelake_metadata() (fabric_onelake_metadata()) for size and other properties without reading the contents. Use $onelake_download() (fabric_onelake_download()) for a file type that should stay as bytes or be saved to local disk.

Write or upload a file

$onelake_write_file() (fabric_onelake_write_file()) turns an R or Arrow object into CSV, Parquet, or Arrow IPC content based on the path or explicit format:

lakehouse$onelake_write_file(
  path = "Files/exports/orders.parquet",
  data = data.frame(
    order_id = 1:3,
    amount = c(10.5, 20, 30.25)
  )
)

If the file already exists on your computer, upload it with $onelake_upload() (fabric_onelake_upload()) without first parsing it in R:

lakehouse$onelake_upload(
  path = "Files/incoming/logo.png",
  source = "logo.png"
)

Both operations can replace a destination. Use a new path while learning and check ?fabric_onelake_write_file or ?fabric_onelake_upload before replacing shared data.

Discover and read managed tables

List tables with $tables() (fabric_lakehouse_tables()):

tables <- lakehouse$tables()
tables[c("schema", "name", "type", "format")]

Select a discovered row or supply the table name to $read_table() (fabric_lakehouse_read_table()):

table <- tables[1L, ]

rows <- lakehouse$read_table(
  table,
  columns = c("order_id", "amount"),
  limit = 100L
)

Column selection and row limits happen before the result is fully collected in R. For SQL joins, grouping, or complex filters, use $sql_query() (fabric_sql_query()) against the Lakehouse SQL analytics endpoint instead.

Write an R object as a managed table

Use $write_table() (fabric_lakehouse_write_table()):

result <- lakehouse$write_table(
  table = "orders_from_r",
  data = data.frame(
    order_id = 1:3,
    amount = c(10.5, 20, 30.25)
  ),
  mode = "Overwrite"
)

result$rows
result$staging_retained

The function stages Parquet files under a unique Files/ path, asks Fabric to perform a managed Delta load, waits for completion, and cleans up after confirmed success. Fabric can infer a missing table’s schema. Use Spark or a separately schema-controlled process when inference is not suitable.

Column names are preserved exactly. The managed load endpoint accepted precomposed caf\u00e9 but rejected decomposed e\u0301 and connector punctuation in x\u203fy in the Fabric sandbox on 7 September 2026. This conflicts with the documented Unicode categories. The package rejects those names before staging; explicitly normalize or rename them in your data if necessary.

Turn existing files into a table

When CSV or Parquet files already exist in the Lakehouse, ask Fabric to load them directly with $load_table() (fabric_lakehouse_load_table()):

operation <- lakehouse$load_table(
  table = "orders_from_csv",
  path = "Files/incoming/orders.csv",
  format = "Csv",
  header = TRUE,
  delimiter = ",",
  mode = "Overwrite"
)

operation <- fabric_operation_wait(operation, timeout = 900)

The source can be one file or a folder. Folder loads can filter by extension and include nested folders. The source must already be inside the selected Lakehouse’s Files/ area.

Read a large or historical Delta table

Use $read_table() (fabric_lakehouse_read_table()) with an Arrow stream when the selected data may not fit comfortably in memory:

local({
  stream <- lakehouse$read_table(
    table = "large_orders",
    result = "arrow_stream"
  )
  on.exit(nanoarrow::nanoarrow_pointer_release(stream), add = TRUE)
  reader <- arrow::as_record_batch_reader(stream)
  on.exit(reader$Close(), add = TRUE, after = FALSE)
  row_count <- 0
  repeat {
    batch <- reader$read_next_batch()
    if (is.null(batch)) break
    # Process or write this batch before reading the next one.
    row_count <- row_count + batch$num_rows
  }
  row_count
})

The stream is disk-backed and single-use. Close the Arrow reader when finished so its staged temporary file is deleted; if you consume the ‘nanoarrow’ stream directly, call stream[["release"]](). Do not rely on garbage collection for this cleanup. version can select an earlier Delta table version when that history is available:

older_rows <- lakehouse$read_table(
  table = "orders",
  version = 42L,
  limit = 100L
)

fabric_onelake_read_delta_table() is the lower-level reader for compatible Delta paths in Lakehouses and Warehouses. Prefer the Lakehouse wrapper for a normal discovered-table workflow.