delta_loading: Import and export a data-chunk with history from historical...

In SCDB: Easily Access and Maintain Time-Based Versioned Data (Slowly-Changing-Dimension)

Import and export a data-chunk with history from historical data

Description

delta_export() exports data from tables created with update_snapshot() in chunks to allow for faster migration of data between sources.

delta_load() import deltas created by delta_export() to rebuild a historical table.

Usage

delta_export(conn, db_table, timestamp_from, timestamp_until = NULL)

delta_load(conn, db_table, delta, logger = NULL)

Arguments

conn	(DBIConnection(1)) Connection object.
db_table	(⁠id-like object(1)⁠) A table specification (coercible by id()).
timestamp_from	(POSIXct(1), Date(1), or character(1)) The timestamp describing the start of the export (including).
timestamp_until	(POSIXct(1), Date(1), or character(1)) The timestamp describing the end of the export (not-including). If NULL (default), all history after timestamp_from is exported.
delta	.data (data.frame(1), tibble(1), data.table(1), or tbl_dbi(1)) A "delta" exported from delta_export() to load. A list of "deltas" can also be supplied.
logger	(Logger(1)) A configured logging object. If none is given, one is initialized with default arguments.

Details

This pair of functions is designed to facilitate easy migration or incremental backups of a historical table (created by update_snapshot()).

To construct the basis of incremental backups, delta_export() can be called with only timestamp_from at the desired frequency (weekly etc.)

To migrate a historical table in chunks, delta_export() can be called with timestamp_until to constrain the size of the delta.

In either case, the table can then be re-constructed by "replaying" the deltas with delta_load(). The order the deltas are replayed does not matter, but all have to be replayed to achieve the same state as the source table.

Value

delta_export() returns a lazy-query containing the data (and history) in the source to be used in conjunction with delta_load().

This table is a temporary table that may need cleaning up.

delta_load() returns NULL (called for side effects).

See Also

update_snapshot

Examples

  conn <- get_connection()

  data <- dplyr::copy_to(conn, mtcars)

  # Copy the first 3 records
  update_snapshot(
    head(data, 3),
    conn = conn,
    db_table = "test.mtcars",
    timestamp = "2020-01-01"
  )

  # Create a delta with the current state
  delta <- delta_export(
    conn,
    db_table = "test.mtcars",
    timestamp_from = "2020-01-01"
  )

  # Update with the first 5 records
  update_snapshot(
    head(data, 5),
    conn = conn,
    db_table = "test.mtcars",
    timestamp = "2021-01-01"
  )

  dplyr::tbl(conn, "test.mtcars")

  # Create a backup using the delta
  delta_load(
    conn = conn,
    db_table = "test.mtcars_backup",
    delta = delta
  )

  dplyr::tbl(conn, "test.mtcars_backup")

  close_connection(conn)

SCDB documentation built on May 18, 2026, 9:06 a.m.