Run the warehouse implementations against real Snowflake and Databricks

[jat255]

Everything commons knows about Snowflake and Databricks has been verified against a fake. Nothing in pkg-py has ever run a statement against a real warehouse, and the R live tests have no way to run anywhere except one person's laptop. The catalog readers, the access and session checks, and the warehouse SQL emitters are all specified against what we believe those systems return.

What exists today

pkg-r/tests/testthat/test-live-warehouses.R (744 lines) plus helper-live-warehouses.R connect through ODBC (odbc::snowflake(), a DSN named Databricks) and skip unless R options name real objects: a test table, a denied table, an alternate Snowflake role, semantic views, parameterized models. pkg-r/tests/testthat/README.md documents the options. Nothing provisions the objects those options point at, no CI job runs any of it, and pkg-py has no equivalent at all: every warehouse test there drives a fake backend that replays canned rows.

The open question

Where does the warehouse come from? This is the decision the rest depends on, so it should be made first. Options worth pricing:

• a shared Posit-owned Snowflake account and Databricks workspace, with credentials in GitHub secrets;
• per-developer trial accounts plus a provisioning script;
• Databricks free edition or a Snowflake trial, created and torn down in CI;
• accepting that this stays manual, and lives in a documented pre-release checklist rather than in CI.

Then the mechanics

• Provisioning as code. The fixtures are not just a table. The access tests need an object the test principal genuinely cannot read, and the session test needs a second usable role. A SQL script (or Terraform) that creates the schema, tables, views, roles, and grants would bring any account to a known state and stop the test options being hand-typed identifiers.
• A Python harness of the same shape. Python connects through SQLAlchemy rather than ODBC/DBI, so this needs snowflake-sqlalchemy and databricks-sqlalchemy as optional dev dependencies, an environment-variable or config equivalent of the R options, and a clean skip when they are unset.
• Credential handling. What reaches CI, what stays local, and what a contributor without warehouse access sees. Today they get a clean skip, which should stay true.
• Scope of what gets asserted. Prefer what a fake cannot tell us over restating unit tests: identifier case folding per backend, the real row shapes of SHOW OBJECTS, DESC TABLE, system.information_schema and DESCRIBE TABLE, the SQLSTATE and message a genuine permission refusal carries, the session identity queries, and whether the Snowflake and Databricks SQL the definition emitters produce actually executes.

Why it matters

tests/shared/catalog-access-errors.json decides whether a driver failure is an authorization refusal, a transient fault, or neither, and commons caches the refusal or retries based on that answer. Its cases were written from documentation rather than from a captured failure. tests/shared/definition-warehouse-sql.json pins SQL that no warehouse has ever parsed, and it is hand-maintained precisely because there is no upstream authority to check it against. A live run is what turns both from plausible into verified. The same applies to the native semantic-model probes for Snowflake semantic views and Databricks metric views, which cannot be written honestly without one.

Not urgent for the conference demo, which runs on DuckDB, and not a blocker for the data layer milestone. It is what stands between "the warehouse support is implemented" and "the warehouse support is known to work".

[jat255]

A little bit of investigation on this:

The documentation is complete, but nobody can find it. pkg-r/tests/testthat/README.md covers every option the helper reads. I compared the option names in helper-live-warehouses.R against it and they match, including verified_query_view and the parameterized-model options. Its history tracks the feature commits (#96, #98, #99, #128, #132), so it has been kept current. No file in the repository links to it. It is absent from the root README.md, AGENTS.md, pkg-r/README.Rmd, every vignette, and _pkgdown.yml. You find it only if you already know to look in the test directory.

What the README leaves out. It lists the options but never says that the objects must exist first, so a reader can assume the suite creates them. test-live-warehouses.R:634 runs USE CATALOG hive_metastore and USE SCHEMA default with no condition, which means the Databricks workspace needs both, and a provisioning script has to account for it. There is no authentication setup for either backend, and no command for running this one file instead of the whole suite. Two Snowflake tests change session state with USE SCHEMA and USE ROLE. The role test restores the original with withr::defer. The namespace tests do not, so the connection is left in a changed state.

A provisioning script replaces most of this README, so the fix is not urgent. Until a warehouse decision lands, a link from the R contributor docs and a prerequisites paragraph are cheap.