datapond

This package is superseded. Please use ducklake by Travis Gerke instead — a tidyverse-friendly DuckLake interface available on CRAN (install.packages("ducklake")). This repository is archived for reference.

---

datapond was a simple and lightweight data lake infrastructure for small to medium data requirements. It provided a unified R interface for DuckLake — a modern data lakehouse built on DuckDB that adds ACID transactions, time travel, and schema evolution on top of Parquet files.

Installation

# Install pak if you don't have it
install.packages("pak")

# Install datapond from GitHub
pak::pak("CathalByrneGit/datapond")

Quick Start

library(datapond)

# Connect to DuckLake with SQLite catalog (recommended for shared drives)
db_connect(
  catalog_type = "sqlite",
  metadata_path = "/data/lake/catalog.sqlite",
  data_path = "/data/lake/data"
)

# See what's available
db_list_schemas()
#> [1] "main" "trade" "labour"

db_tables("trade")
#> [1] "imports" "exports" "products"

# Read current data (returns a lazy dplyr table)
imports <- db_read(schema = "trade", table = "imports")

imports |>
  filter(year == 2024) |>
  group_by(country) |>
  summarise(total = sum(value)) |>
  collect()

# Read data as it was at a specific version
imports_v5 <- db_read(schema = "trade", table = "imports", version = 5)

# Read data as it was at a specific time
imports_jan <- db_read(
  schema = "trade",
  table = "imports",
  timestamp = "2025-01-15 00:00:00"
)

# Preview write to see impact before executing
db_preview_write(my_data, schema = "trade", table = "imports", mode = "append")

# Write with commit metadata
db_write(
  my_data,
  schema = "trade",
  table = "imports",
  mode = "append",
  commit_author = "jsmith",
  commit_message = "Added Q1 2025 data"
)

# Write with partitioning for faster queries
db_write(
  my_data,
  schema = "trade",
  table = "imports",
  partition_by = c("year", "month")
)

# Bucket partitioning for high-cardinality columns (e.g., user IDs)
db_write(
  events_data,
  table = "events",
  bucket_by = list(column = "user_id", buckets = 16)
)

# Sorted/clustered tables for better range query performance
db_write(
  sales_data,
  table = "sales",
  sort_by = c("sale_date", "region")
)

# Streaming: DuckLake auto-inlines small writes (< threshold rows)
# Adjust threshold per table if needed
db_set_inline_threshold(table = "events", threshold = 1000)
# Flush when ready
db_flush_inlined(table = "events")

# Explicit column types for stricter schema control
db_write(
  financial_data,
  table = "transactions",
  col_types = list(
    id = "BIGINT",
    amount = "DECIMAL(12,2)",
    rate = "DOUBLE"
  )
)

# Transform data without leaving DuckDB (zero-copy, no R memory)
# Just pipe a lazy table from db_read() through dplyr and into db_write()
db_read(table = "raw_imports") |>
  filter(year == 2024, !is.na(value)) |>
  mutate(value_eur = value * exchange_rate) |>
  group_by(country, month) |>
  summarise(total = sum(value_eur), .groups = "drop") |>
  db_write(schema = "clean", table = "monthly_summary")

# Arrow integration - read/write without DuckDB compute
arrow_tbl <- db_read_arrow(table = "imports", as_data_frame = FALSE)
db_write_arrow(arrow_tbl, table = "imports_copy")

# Preview upsert to see how many inserts vs updates
db_preview_upsert(my_data, schema = "trade", table = "products", by = "product_id")

# Upsert (update existing, insert new)
db_upsert(
  my_data,
  schema = "trade",
  table = "products",
  by = "product_id",
  commit_message = "Price updates"
)

# View snapshot history
db_snapshots()

# Compare versions (set-based)
diff <- db_diff(schema = "trade", table = "imports",
                from_version = 5, to_version = 10)
diff$added
diff$removed

# Get row-level changes via Data Change Feed (more efficient)
changes <- db_changes(schema = "trade", table = "imports",
                      from_version = 5, to_version = 10)
# Returns: snapshot_id, rowid, change_type (insert/delete/update_*), plus all columns

# Filter to just inserts
new_rows <- db_changes(table = "imports", from_version = 5,
                       change_types = "insert")

# Rollback if something went wrong
db_rollback(schema = "trade", table = "imports", version = 5)

# Clean up old snapshots
db_vacuum(older_than = "30 days", dry_run = FALSE)

# Check file statistics (useful before compaction)
db_file_stats()

# Compact small files into larger ones for better performance
db_compact(table = "imports")

# Remove orphaned files after vacuum or compact
db_cleanup_files(dry_run = FALSE)

# Export to Iceberg format for Spark/Trino compatibility
db_export_iceberg(table = "imports")

db_disconnect()

Data Documentation & Discovery

library(datapond)
db_connect(
  catalog_type = "sqlite",
  metadata_path = "/data/lake/catalog.sqlite",
  data_path = "/data/lake/data"
)

# Document your tables with structured metadata (stored as JSON in native comments)
db_comment(
  table = "imports",
  comment = list(
    description = "Monthly import values by country and commodity code",
    owner = "Trade Section",
    tags = c("trade", "monthly", "official")
  )
)

# Document individual columns
db_comment(
  table = "imports",
  column = "value",
  comment = list(
    description = "Import value in thousands",
    units = "EUR (thousands)"
  )
)

# Search for tables
db_search("trade")
db_search("official", field = "tags")

# Find columns across all tables
db_search_columns("country")

# Generate a data dictionary
dict <- db_dictionary()
# Export to Excel
writexl::write_xlsx(dict, "data_dictionary.xlsx")

Data Lineage

# 1. Manual table-level lineage (sources= listed explicitly)
db_lineage(
  table = "monthly_summary",
  sources = c("raw.transactions", "raw.products"),
  transformation = "Aggregated by month and product category"
)

# 2. Auto column-level lineage from a lazy dbplyr pipeline
#    (sources are inferred; requires dplyneage)
pipeline <- raw_transactions |>
  dplyr::left_join(raw_products, by = "product_id") |>
  dplyr::group_by(month, category) |>
  dplyr::summarise(total = sum(amount), .groups = "drop")

db_lineage(table = "monthly_summary", pipeline = pipeline)

# 3. One-liner: write and record lineage in a single step
pipeline |> db_write(schema = "main", table = "monthly_summary", track_lineage = TRUE)

# 4. Retrieve lineage (returns sources, transformation, and column_edges)
db_get_lineage(table = "monthly_summary")
#> $sources
#> [1] "raw.transactions" "raw.products"
#>
#> $transformation
#> [1] "Aggregated by month and product category"
#>
#> $column_edges
#> # A data frame: ...

# 5. Visualise column-level lineage as an interactive diagram (requires dplyneage)
db_lineage_flow(table = "monthly_summary")

Optional dependency: column-level lineage and db_lineage_flow() require the dplyneage package — install with pak::pak("tgerke/dplyneage").

Interactive Browser

library(datapond)
db_connect(
  catalog_type = "sqlite",
  metadata_path = "/data/lake/catalog.sqlite",
  data_path = "/data/lake/data"
)

# Launch interactive browser
db_browser()

The browser provides a point-and-click interface with six tabs:

• Preview - View sample rows from any table (select from tree on left)
• Metadata - See documentation, owner, tags, and column descriptions
• Search - Find tables by name, description, owner, or tags; search columns across all tables
• Dictionary - Generate and export a full data dictionary as CSV
• Access Control - Guidance on folder-based permissions with DuckLake
• Connection - View current connection status and catalog info

Choosing a Catalog Backend

DuckLake stores metadata (table definitions, snapshots, file tracking) in a catalog database. You can choose from four backends:

Backend	catalog_type	Concurrency	Best For
DuckDB	"duckdb"	Single client only	Personal/dev use
SQLite	"sqlite"	Multi-read, single-write	Shared network drives
PostgreSQL	"postgres"	Full concurrent access	Large teams, remote access
Quack	"quack"	Multi-writer (EXPERIMENTAL)	Future: serverless multi-writer

Recommended: SQLite for Shared Drives

db_connect(
  catalog_type = "sqlite",
  metadata_path = "/data/lake/catalog.sqlite",
  data_path = "/data/lake/data"
)

Why SQLite?

• Still just a file on the network drive (no server needed)
• Supports multiple people reading simultaneously
• Single-writer with automatic retry (handles realistic usage)
• Works with existing IT permissions model

PostgreSQL for Production Scale

If you need true multi-user concurrent writes or remote access:

db_connect(
  catalog_type = "postgres",
  metadata_path = "dbname=ducklake_catalog host=db.cso.ie",
  data_path = "/data/lake/data"
)

Quack Remote Protocol (EXPERIMENTAL)

The Quack protocol enables client-server DuckDB with multiple concurrent writers. DuckLake now supports Quack as a catalog backend (DuckDB 1.5.3+).

# Connect to a DuckDB server running Quack
db_connect(
  catalog_type = "quack",
  metadata_path = "quack:db-server.cso.ie:9494/catalog.ducklake",
  data_path = "/data/lake/data",
  quack_token = "my-secret-token"  
)

# Or use environment variable
Sys.setenv(QUACK_TOKEN = "my-secret-token")
db_connect(
  catalog_type = "quack",
  metadata_path = "quack:db-server.cso.ie:9494/catalog.ducklake",
  data_path = "/data/lake/data"
)

Note: Quack is currently in beta (DuckDB 1.5.x). Production-ready version planned for DuckDB 2.0 (Fall 2026). See vignette("catalog-backends") for security options.

Encryption (Zero-Trust Storage)

DuckLake supports encryption for all data files, enabling zero-trust storage where data can reside on untrusted infrastructure:

db_connect(
  metadata_path = "//secure-server/catalog.sqlite",
  data_path = "//untrusted-cloud-storage/data",
  encrypted = TRUE
)

How it works:

• Each parquet file is encrypted with a unique AES key (auto-generated)
• Keys are stored in the catalog database (separate trust zone)
• Keys are automatically retrieved when reading encrypted files
• No key management required from users

This is ideal when data files must reside on third-party or shared storage but you control the catalog server.

Access Control

datapond relies on file system permissions for access control. DuckLake automatically organises data into {schema}/{table}/ folders within the data path, so you can grant permissions at the schema level.

# Create schemas - DuckLake creates data folders automatically
db_create_schema("trade")
db_create_schema("labour")

# Write data - files go to data/trade/imports/ automatically
db_write(imports_data, schema = "trade", table = "imports")

# Then grant folder ACLs:
# - data/trade/   → Trade team read/write
# - data/labour/  → Labour team read/write

Function Reference

Connection

Function	Description
db_connect()	Connect to a DuckLake catalog (supports duckdb/sqlite/postgres backends)
db_disconnect()	Close connection
db_status()	Show connection info (including catalog type)

Reading Data

Function	Description
db_read()	Read table (with optional time travel by version or timestamp)

Writing Data

Function	Description
db_write()	Write data.frame or lazy dplyr query (zero-copy transforms stay in DuckDB)
db_upsert()	MERGE operation (update existing rows + insert new rows)

Note: db_write() accepts both data.frames (data passes through R) and lazy dbplyr tables from db_read() (stays entirely in DuckDB, no R memory used). Use the lazy approach for in-lake transformations.

Arrow Integration

Function	Description
db_read_arrow()	Read table directly as Arrow Table (bypasses DuckDB compute)
db_write_arrow()	Write Arrow Table/RecordBatch to DuckLake

Preview Operations

Function	Description
db_preview_write()	Preview write impact before executing
db_preview_upsert()	Preview inserts vs updates before executing

Discovery

Function	Description
db_list_schemas()	List schemas
db_tables()	List tables in a schema
db_list_views()	List views in a schema
db_list_macros()	List macros in a schema
db_table_exists()	Check if a table exists
db_create_schema()	Create a new schema

Views & Macros

Function	Description
db_create_view()	Create a SQL view stored in the catalog
db_drop_view()	Drop a view
db_create_macro()	Create a scalar or table macro
db_drop_macro()	Drop a macro

Documentation & Search

Function	Description
db_comment()	Add metadata to table/column (list for structured, string for simple)
db_get_docs()	Retrieve documentation for a table
db_dictionary()	Generate full data dictionary
db_search()	Search by name, description, owner, or tags
db_search_columns()	Find columns by name across all tables
db_lineage()	Record data lineage (sources, transformation, or pipeline for column-level)
db_get_lineage()	Retrieve lineage (sources, transformation, column_edges) for a table
db_lineage_flow()	Render interactive column-level lineage diagram (requires dplyneage)

Partitioning & Clustering

Function	Description
db_set_partitioning()	Set or remove partitioning on a table
db_get_partitioning()	Get current partition columns for a table
db_set_clustering()	Set sort/clustering order for better query performance
db_recluster()	Re-sort existing data to match clustering order

Data Inlining (Streaming)

Function	Description
db_flush_inlined()	Write inlined data to parquet files
db_set_inline_threshold()	Configure auto-flush threshold for inlined data

Iceberg Export (Experimental)

Function	Description
db_export_iceberg()	Export table to Iceberg format for Spark/Trino/Presto
db_iceberg_metadata()	Get Iceberg-compatible metadata for a table

Metadata & Maintenance

Function	Description
db_snapshots()	List all snapshots
db_catalog()	Table info and stats
db_table_cols()	Get column names for a table
db_view_cols()	Get column names for a view
db_diff()	Compare two snapshots (set-based)
db_changes()	Get row-level changes via Data Change Feed
db_insertions()	Get only inserted rows between snapshots
db_deletions()	Get only deleted rows between snapshots
db_rollback()	Restore table to a previous version
db_vacuum()	Clean up old snapshots and unreferenced files
db_compact()	Merge small Parquet files for better performance
db_file_stats()	Get file counts and sizes to identify compaction candidates
db_cleanup_files()	Remove orphaned files after vacuum or compact
db_query()	Run arbitrary SQL
db_enable_logging()	Enable DuckDB/DuckLake logging for debugging

Interactive Tools

Function	Description
db_browser()	Launch interactive Shiny browser for exploring data
run_example()	Run bundled example scripts (call without args to list)

Learn More

• vignette("concepts") - Background on DuckLake, catalog backends, and access control
• vignette("code-walkthrough") - Detailed explanation of how the package works
• DuckLake Documentation - Official DuckLake docs

Contributing

Found a bug or have a feature request? Please open an issue on GitHub.

Development

After modifying roxygen2 comments in R files, regenerate documentation:

devtools::document()
devtools::check()

Run tests:

devtools::test()