# R Databricks Unity Catalog

> Finding and querying Unity Catalog data from R. Covers browsing catalogs, schemas and tables with brickster, writing dbplyr pipelines rather than SQL strings (including why spatial ST_ functions and the odbc BINARY bug are not reasons to drop to SQL, and why an st_ call above collect() is the server's function and not sf's, which models the Earth differently unless the coordinates are projected), in_catalog() for three-part names, glimpse() without collect(), deciding where the collect() line goes, and using dplyr::sql() for expressions dbplyr cannot translate or tbl(con, sql()) to start from a whole hand-written query. Load when locating a table or writing a query; load r-databricks-connections first to choose a connection path.

- Skill: `posit-dev/r-databricks-unity-catalog` (Agent Skill)
- Install (CLI): `npx skillmds@latest add posit-dev/r-databricks-unity-catalog`
- Raw SKILL.md: https://api.skillmd.com/api/skills/posit-dev/r-databricks-unity-catalog/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: posit-dev (https://skillmd.com/u/posit-dev)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/posit-dev/r-databricks-unity-catalog

---


# Finding and querying Unity Catalog data from R

*Load `r-databricks-connections` first if you have not yet chosen a connection path. This skill assumes a DBI connection is already open, and cites `r-databricks-brickster` for the exact signature of any `db_*` call it names rather than restating it.*

## Browse, then query

These are two different operations, with two different costs. Browsing asks Unity Catalog for metadata: `db_uc_catalogs_list()`, `db_uc_schemas_list()`, `db_uc_tables_list()`, `db_uc_tables_summaries()` and `db_uc_tables_exists()`. None of these move a single row of data, so calling them freely to locate a table costs nothing worth worrying about.

Querying is different: it is `dbplyr` over a DBI connection, and it moves data. Find the table first with the browsing calls, confirm it is the one you want, then query it. See `r-databricks-brickster` for the full `db_uc_*` family and its argument list.

## Write `dplyr`, not SQL strings

**Default to a `dbplyr` pipeline. Reach for `dbGetQuery()` only when nothing else reaches the thing you need.** The exceptions are narrow and worth naming, because two plausible-sounding ones are wrong:

- **Spatial `ST_` functions do not need SQL.** `dbplyr` sends a function name it does not recognise to the server verbatim, so `st_area()`, `st_setsrid()`, `st_union_agg()` and the rest push down from an ordinary pipeline with no `sql()` wrapper. `[verified: ran it on 2026-08-25]` **This is also a trap: see the next section before using it.**
- **The `odbc` `BINARY` bug does not need SQL either.** It constrains which *connection* you open, not which idiom you write: `brickster` ships a full `dbplyr` backend, so `tbl(acon, ...) |> collect()` carries `BINARY` intact. `[verified: ran it on 2026-08-25]`

What genuinely needs a SQL string is short: `SHOW FUNCTIONS`, `DESCRIBE` and friends; DDL (`CREATE`, `DROP`, `COMMENT ON`); and a scalar expression with no `FROM` clause, which has no table to hang a `tbl()` on. `[verified: ran it on 2026-08-25]`

Two more replacements worth knowing, both of which remove a hand-built string:

| Instead of | Use | Why |
|----|----|----|
| `dbGetQuery(con, "SHOW TABLES IN ...")` | `dbListTables(con, catalog_name =, schema_name =)` | Same result, named arguments |
| `tbl(con, I(glue("{cat}.{sch}.{tbl}")))` | `tbl(con, in_catalog(cat, sch, tbl))` | Quotes each part, so a name needing escaping still works |

**`in_catalog()` needs `dbplyr` attached**, and this is easy to miss: a page can use `tbl()` and a chain of `dplyr` verbs without ever calling `library(dbplyr)`, because `dplyr` dispatches to the backend on its own. `in_catalog()` is not re-exported, so it is the call that fails. `[verified: ran it on 2026-08-26]` Either attach `dbplyr` or qualify it as `dbplyr::in_catalog()`.

It works on all three connection paths: `odbc`, `brickster::DatabricksSQL()`, and a `sparklyr::spark_connect()` handle. The sparklyr case is the one worth stating, because a Spark connection is not a DBI connection and the two are not otherwise interchangeable. `[verified: ran it on 2026-08-26]`

**Always name the catalog and schema in `dbListTables()`.** Omitting them is not an error and does not warn: it lists whatever the connection defaults to, which on a shared warehouse is somebody else's schema. A list of unfamiliar tables then reads as a missing grant when it means you are looking in the wrong place. `[verified: ran it on 2026-08-25]`

## The spatial pass-through is a trap as well as a convenience

The mechanism that makes spatial work push down is the same one that lets a pipeline run cleanly and answer wrongly.

Inside a `dplyr` verb on a remote table, `st_area(g)` is **not** a call to `sf::st_area()`. Nothing in R evaluates it: `dbplyr` captures the name and sends it as text. Attaching `sf` changes nothing, and detaching it changes nothing. The same expression is the server's function above `collect()` and `sf`'s below it. `[verified: ran it on 2026-08-25]`

A name the server does not have fails loudly with `UNRESOLVED_ROUTINE`, whether it is a typo or a real `sf` function with no equivalent. That is the safe case. The dangerous case is a name present on both sides meaning something different:

| Divergence | What happens |
|----|----|
| **Value** | Server-side `st_distance()`, `st_length()`, `st_area()` are planar; `sf` on unprojected coordinates uses s2 spherical geometry. On two WGS84 points: server `5`, `sf` 555,813 m. Both correct, no warning |
| **Answer** | The same difference makes predicates diverge **exactly on boundaries**: for a point on the interior of a polygon edge, `st_intersects()`, `st_covers()` and `st_touches()` are TRUE server-side and FALSE in `sf`, `st_disjoint()` the reverse. Interior and exterior agree. A corner point agrees `[verified: ran it on 2026-08-28]` |
| **Shape** | `st_contains()` gives one boolean per row against `sf`'s sparse index list; `st_distance()` a scalar against a matrix; `st_buffer()` rejects `nQuadSegs` |
| **Absence** | `st_makevalid()` and about twenty other `sf` staples have no server-side equivalent; the server diagnoses invalidity and will not repair it |

`[documented: established downstream, 2026-08-24 and 2026-08-28]`

**"Measures are unsafe, predicates are safe" is false**, and the predicate case is the more dangerous of the two: a boundary is where a spatial join lands its ambiguous records, so a point-in-polygon count comes out a few rows wrong rather than obviously wrong.

**The fix is to project, and it fixes both.** `sf` engages s2 only when `st_is_longlat()` is TRUE, so on a projected CRS it runs planar GEOS, the same model the server uses, and the two then agree on every case tested. Ask the server for a measurement or a predicate only on coordinates already projected into a metric CRS.

Five things are **not** fixes. Attaching `sf` (the generic is never called). Tagging the SRID: keep wrapping `st_geomfromwkb()` in `st_setsrid()`, because stored WKB carries no SRID and the server reports 0, but `st_distance()` ignores the SRID, so a labelled column is not a checked one. Changing connection path: `odbc`, `brickster` and `sparklyr` were each measured and all three are wrong identically `[verified: ran it on 2026-08-28]`. Any automated guard, because correctness depends on the SRID and `dbplyr` builds SQL before consulting the server. And `sf_use_s2(FALSE)`, which is a **trap**: it selects an ellipsoidal rather than a planar model, repairing the predicates while leaving the measures wrong by five orders of magnitude, which is more dangerous than either error alone.

The 22-name overlap that creates all of this is one measurement against one DBR and one `sf` version, not a contract. The other 94 `sf` exports fail loudly.

Reach for `show_query()` whenever you are unsure which side you are on: whatever it prints is the server's.

For a spherical answer on unprojected coordinates, name it: `st_distancesphere()` and `st_distancespheroid()`. Casting to the geography type is **not** the fix, because `st_distance()` rejects a geography argument outright.

## `glimpse()` works without `collect()`

To see a table's shape, `glimpse()` beats printing it or taking a `head()`. It returns the columns, their types and a few values, and it needs no `collect()` because a column listing is metadata.

```r
tbl(con, in_catalog(catalog, schema, "measurements")) |>
  glimpse()
```

It prints `Rows: ??`, which is the honest answer rather than a gap: `dbplyr` has not counted the rows, because counting is a query nobody asked for. `[verified: ran it on 2026-08-25]`

## The `collect()` line, as the governing idea

Every `dbplyr` pipeline has one line that decides where the work happens: above it, the work runs on Databricks; below it, the work runs in R. The only real question for any pipeline is how big the data is when it crosses that line.

```r
library(dplyr)

result <- tbl(con, in_catalog("catalog", "schema", "measurements")) |>
  filter(sample_year >= 2020) |>
  group_by(site_id) |>
  summarise(n = n(), mean_value = mean(value, na.rm = TRUE)) |>
  collect()          # <- the line. Everything above ran on Databricks.
```

Vendor documentation calls this "pushdown" (the term you will see if you go looking), but the useful mental model is the line itself, not the label, so this skill uses "the `collect()` line" as the primary vocabulary from here on.

## Measure what crosses, before you pull it

Two habits catch a pipeline that would otherwise land an unexpectedly large result in R's memory. Use `dplyr::show_query()` to see what SQL is actually being sent, and check a row count before collecting rather than finding out after:

```r
pipeline <- tbl(con, in_catalog("catalog", "schema", "measurements")) |> filter(sample_year >= 2020)
pipeline |> show_query()
pipeline |> summarise(n = n()) |> collect()   # how big is it before you pull it
```

If the count is small, `collect()` the real pipeline with confidence. If it is not, narrow the filter, aggregate further on the server side, or reconsider whether the whole result belongs in R at all.

## The escape hatch: `dplyr::sql()`

`dbplyr` cannot translate every expression to Spark SQL, and its coverage is not the real ceiling. `dplyr::sql()` embeds arbitrary Spark SQL inside an otherwise ordinary `dbplyr` pipeline, including functions `dbplyr` has no translation for and Databricks' native spatial functions. So the actual ceiling is "what Spark SQL can express", a vastly larger set than "what `dbplyr` happens to translate". `[verified: ran it on 2026-08-04]`

```r
tbl(con, in_catalog("catalog", "schema", "sites")) |>
  mutate(code = sql("regexp_extract(site_name, '([A-Z]{2}[0-9]+)', 1)")) |>
  collect()
```

The same escape hatch has a second, larger form: pass a `sql()` object to `tbl()` in place of a table name and a whole hand-written query becomes a lazy table. Use it when the query already exists (a colleague's, or one tuned in the Databricks SQL editor) rather than translating it into verbs. `[verified: ran it on 2026-08-24]`

```r
q <- tbl(con, sql("SELECT id, site_name FROM catalog.schema.sites WHERE region = 'NW'"))
q |> count(site_name) |> collect()
```

`dbplyr` does not reparse the query: it wraps it as a subquery and writes around it, so verbs piped on afterwards still push down. Confirm with `show_query()`. Works on both the `odbc` and `brickster` connection paths. `[verified: ran it on 2026-08-24]`

The wrapper is load-bearing. A bare character string is read as a table *name*, and fails with `TABLE_OR_VIEW_NOT_FOUND` quoting the whole SELECT back as an identifier, which reads as a server fault rather than a missing `sql()`. `[verified: ran it on 2026-08-24]`

## A known backend gap, attributed correctly

`dbplyr` refuses most `stringr` regex verbs on the Spark backend: `str_detect(x, 'a')` fails with "Only fixed patterns are supported on this backend", even though the same warehouse runs `regexp_extract()`, `regexp_replace()` and `rlike` without complaint. That is a `dbplyr` backend-translation gap, not a Databricks limitation, and `sql()` is the workaround. `[verified: ran it on 2026-08-04]`

## After `collect()`, `duckdb` raises the tabular ceiling

Once data is local, a disk-backed `duckdb` file raises what R can handle next: pulling through the warehouse in chunks and landing in `duckdb` aggregates 5M rows × 10 columns in 0.16 s under a 512 MB memory limit, spilling a full-table sort to disk rather than failing. `[verified: ran it on 2026-08-05]` The benefit comes from `duckdb` running *after* `collect()`, not from Arrow transport, and it does not help when what overflows is a spatial object in R's own heap, because `duckdb` has no geometry type to hold it.

## Scope note

Governance, `GRANT`/`REVOKE`, the privilege model, row filters, column masks and external locations are out of scope here. Where a `databricks` command-line tool is available, the official `databricks-unity-catalog` skill covers that ground; treat this paragraph as a pointer to that material, not as an instruction to install or run anything.

## The `|>` rule

Use the base pipe `|>` in every example, never the magrittr pipe.