--- title: "Choosing a Deployment" output: rmarkdown::html_vignette vignette: > %\VignetteIndexEntry{Choosing a Deployment} %\VignetteEngine{knitr::rmarkdown} %\VignetteEncoding{UTF-8} --- ```{r, include = FALSE} # Every chunk here shows a setup rather than running one: most of them # need a server, cloud credentials, or a second machine. knitr::opts_chunk$set( collapse = TRUE, comment = "#>", eval = FALSE ) ``` ```{r setup} library(ducklake) library(dplyr) ``` A DuckLake is two things. A catalog database records every table, column, snapshot, and data file, and a directory of Parquet files holds the rows. Where you put each of those decides who can use the lake, how many people can write to it at once, and what you have to run. This article takes the choices in the order they come up: which catalog, where the data goes, who can reach it, and what to set up on day one. The other articles then cover each topic in depth. ## What a lake looks like After `attach_ducklake("trial", lake_path = "~/lakes/trial")` and a few tables, the directory holds: ``` ~/lakes/trial/ ├── trial.ducklake # the catalog: a DuckDB database file ├── main/ # one directory per schema │ ├── adsl/ # one directory per table │ │ └── ducklake-....parquet │ └── adae/ └── bronze/ └── dm/ ``` The catalog is small and changes on every commit. The data files never change once written, which is what makes them simple to copy, cache, and replicate. Very small writes do not even reach the data directory: DuckLake keeps them inline in the catalog until they are flushed (`vignette("data-inlining")`). That split is the whole design. Every deployment below is a choice of where the catalog lives and where the files live. ## Which catalog The catalog backend sets the concurrency model. The DuckDB file that `attach_ducklake()` uses by default is the right start for one person on one machine; it is also the one that cannot be shared. | Situation | Catalog | Why | |---|---|---| | One person, one machine | DuckDB file (the default) | Nothing to install or run. One writer at a time: DuckDB locks the file while the lake is attached, and a second session cannot open it. | | A team on a shared drive | SQLite | Several sessions write to the same lake. SQLite serializes them and DuckLake retries when it meets a lock. No server to run. | | Many users, remote storage | PostgreSQL | Row-level locking for concurrent writers, database roles for access control, `pg_dump` for backups. Needs a server (PostgreSQL 12 or newer). | | Sharing without shared storage | Any of the above, served with Quack | Clients connect to one DuckDB process over the network; only the server touches the catalog and files. | | A published, read-only lake | DuckDB file on an HTTPS host | Readers attach the catalog straight from the URL; you update it by replacing the file. | MySQL works but is not recommended upstream because of limitations in the DuckDB MySQL connector, and `attach_ducklake()` warns when you pick it. The three shapes in code: ```{r catalogs} # One person: catalog and data side by side in one directory attach_ducklake("trial", lake_path = "~/lakes/trial") # A team on a shared drive: SQLite catalog next to the data attach_ducklake( "trial", backend = "sqlite", catalog_connection_string = "/shared/lakes/trial/catalog.sqlite", lake_path = "/shared/lakes/trial/data" ) # Many users: PostgreSQL catalog, data on object storage create_storage_secret("s3", provider = "credential_chain") attach_ducklake( "trial", backend = "postgres", catalog_connection_string = "dbname=lakes host=db.example.org", lake_path = "s3://acme-lakes/trial", metadata_schema = "trial" ) ``` `metadata_schema` gives each lake its own schema inside one PostgreSQL database, so a department's studies can share a server without sharing catalog tables. When you mean to open a lake that already exists, say so with `create = FALSE`. A mistyped path then fails instead of quietly creating a new, empty lake: ```{r existing} attach_ducklake("trial", lake_path = "/shared/lakes/trial/data", backend = "sqlite", catalog_connection_string = "/shared/lakes/trial/catalog.sqlite", create = FALSE) ``` ## Where the data goes `lake_path` accepts a local directory, a network mount, or an object-storage URI (`s3://`, `gs://`, `r2://`, `az://`). Object storage needs credentials registered first with `create_storage_secret()`; the `credential_chain` provider picks them up the way the AWS SDKs do, so nothing sensitive sits in a script. Two constraints shape the layout: - DuckDB cannot write a database file to object storage, so with the default DuckDB catalog and a remote `lake_path`, put the catalog on local disk with `catalog_connection_string`. `attach_ducklake()` stops with that advice when the layout would not work. - On Windows, the duckdb R package can load the `ducklake`, `httpfs`, `sqlite`, and `quack` extensions but not `postgres`, `mysql`, `aws`, or `azure`. A Windows session can be a reader or writer of a SQLite or DuckDB-catalog lake on S3 with explicit keys; PostgreSQL catalogs, `credential_chain`, and Azure need Linux, macOS, or WSL. Storage choice is also a performance choice. Local disk is fastest and private; a shared drive is convenient for a small team and slow across a WAN; object storage scales and is reachable from anywhere at the cost of latency per file. Sorting and partitioning large tables (`set_table_sorting()`, `set_table_partitioning()`) matter most on object storage, where every file skipped is a request avoided. ```{r pruning} # Sorting suits high-cardinality columns like timestamps or ids set_table_sorting("events", "event_time") # Partitioning suits low-cardinality columns like year or region set_table_partitioning("sales", c("year(order_date)", "region")) ``` ## Who can reach it DuckLake has no user accounts of its own. Access control lives in the two layers you already chose: the catalog database and the storage. The DuckLake documentation describes three roles built from those layers, and they map onto R sessions directly. | Role | Catalog | Storage | In R | |---|---|---|---| | Reader | `SELECT` on the metadata tables | read on the schema or table paths | `attach_ducklake(..., read_only = TRUE)` | | Writer | `SELECT`, `INSERT`, `UPDATE`, `DELETE` on the metadata tables | read, write, and delete on its schemas' paths | ordinary `attach_ducklake()` | | Superuser | also `CREATE` on the database | everything | ordinary `attach_ducklake()` | Because DuckLake writes each schema's files under a directory of their own, schemas are the unit of storage permissions: a bucket policy that grants `bronze/*` to the ingestion role and `gold/*` to analysts works without touching the catalog. Organizing a lake by schema (`create_schema()`) is therefore worth doing before the first table lands. The upstream guide has the PostgreSQL grants and S3 policies spelled out: . For a frozen view, attach the lake pinned to a snapshot (`attach_ducklake(snapshot_version = ...)`): every read sees that moment and writes are refused, which is how a report is reproduced months later. ## Day-one setup A few settings pay for themselves later. Set them when you create the lake, and they persist in the catalog for every client. ```{r day-one} # 1. Keep DuckDB's downloaded extensions between sessions. Interactively, # say yes when duckdb offers to create ~/.duckdb. For scripts and CI, # set DUCKDB_R_HOME=~/.duckdb in ~/.Renviron or the job's environment. # A container image runs install_ducklake() at build time instead. # 2. Create the lake, then its schemas attach_ducklake("trial", lake_path = "~/lakes/trial", author = "Data Engineer") with_transaction({ create_schema("bronze") create_schema("silver") create_schema("gold") }, author = "Data Engineer", commit_message = "Create the trial lake") # 3. Retention: how far back time travel reaches, and when released files # are deleted. Without these a checkpoint keeps every snapshot and file. set_ducklake_option("expire_older_than", "90 days") set_ducklake_option("delete_older_than", "7 days") # 4. Make every commit explain itself set_ducklake_option("require_commit_message", TRUE) # 5. Compression for files that are read far more often than written set_ducklake_option("parquet_compression", "zstd") ``` Then two habits. Run `checkpoint_ducklake()` on a schedule (after the nightly load, say) to flush inlined data, compact small files, and apply the retention policy. And back up: `backup_ducklake()` copies a DuckDB or SQLite catalog with `COPY FROM DATABASE` while the lake stays attached, plus the data directories; a PostgreSQL catalog is backed up with `pg_dump`, and object storage with the provider's replication. Restore one backup before you need to, so the procedure is known to work (`vignette("storage-and-backups")`). When several sessions write to the same lake, DuckLake retries transactions that race each other and reports only real conflicts. Keep transactions short, and if a busy catalog produces conflict errors, give `set_ducklake_retry()` more attempts (`vignette("transactions")`). ## Upgrading Two things change under a lake over time. Upgrading the duckdb R package brings a new DuckDB engine, and the DuckLake extension is downloaded again for it on first use. And a lake created with an older extension may carry an older catalog format: DuckDB 1.5.1 wrote DuckLake 0.4, while 1.5.2 and later write 1.0. Attaching such a lake stops with a version mismatch until it is migrated, which is a one-time, permanent change, so take a backup first: ```{r migrate} backup_ducklake("trial", lake_path = "~/lakes/trial", backup_path = "~/lakes/backups") attach_ducklake("trial", lake_path = "~/lakes/trial", automatic_migration = TRUE) ``` ## Where to next - `vignette("ducklake")` to attach a lake, add a table, and follow its history - `vignette("loading-data")` for files, URLs, Parquet in place, and migrating from DuckDB or Iceberg - `vignette("storage-and-backups")` for the file layout, backups, and maintenance - `vignette("transactions")` for commit metadata and working with several writers - `vignette("quack-remote-access")` for serving a lake over the network - [Clinical Trial Data Lake](https://tgerke.github.io/ducklake-r/articles/clinical-trial-datalake.html) for a complete regulated workflow