[show-and-tell] Using {targets} with Docker, DuckDB, and {renv} for Reproducible Data Pipelines

[philiporlando]

Description

I've been working on this project which demonstrates how to build reproducible data pipelines using Docker for isolated environments, DuckDB for fast, in-process SQL analytics, and {targets} for orchestrating R workflows. This project also incorporates {renv} to enhance Docker’s handling of R package dependencies, letting users manage and version control packages through an renv.lock file.

I wanted to share with other {targets} users that may be searching for a portable data pipeline solution. Looking forward to any feedback and to learn about other ways that folks are building reproducible data pipelines.

[wlandau]

Nice example, thanks for sharing!

[philiporlando]

Thanks! I really appreciate all the effort you’ve put into {targets}—it’s been incredibly helpful for my work, and your active presence in the community is awesome. Looking forward to continuing to learn and contribute!

[daranzolin]

Thanks for sharing, @philiporlando! What is the advantage of passing a table hash via digest::digest() instead of the table itself in ingest_data() and transform_data()?

[philiporlando]

Thanks for taking a look and asking about this! Passing the entire table (especially a large one) as an argument can be memory-intensive, so I was aiming to offload as much computation to DuckDB as possible.

A hash offers a lightweight way for {targets} to detect state changes in external resources like database tables. In this case, transform_data() avoids loading the full ingest_data() table into memory—DuckDB handles the "heavy" lifting via {dbplyr} and {duckdb}, with only the resulting summary data loaded into R. It's overkill for the demo, but has proven useful in larger {targets} pipelines in my experience.

[philiporlando]

I've implemented a slightly different approach when working with a large PostgreSQL database on a separate project. Instead of calling digest::digest() on an entire table in R memory, like with how I'm hashing within ingest_data(), I've offloaded this to the database as well:

handle_schema_table_query_inputs <- function(schema, table, query) {
  if (missing(query) && (missing(schema) || missing(table))) {
    stop(
      glue::glue_collapse(
        "Either 'schema' and 'table' must be provided together or ",
        "just 'query' should be provided."
      )
    )
  }
  return(NULL)
}

get_table_checksum <- function(
  schema = NULL, table = NULL, query = NULL, con = NULL
) {
  handle_schema_table_query_inputs(schema, table, query)
  if (!missing(table)) {
    logger::log_info("Fetching checksum from {schema}.{table}")
    checksum_query <- glue::glue(
      "SELECT md5(array_agg(t.*)::text) AS checksum FROM {schema}.{table} t"
    )
  } else {
    checksum_query <- glue::glue(
      "SELECT md5(array_agg(t.*)::text) AS checksum FROM ({query}) t"
    )
  }

  checksum <- DBI::dbGetQuery(con, statement = checksum_query)$checksum

  msg <- ifelse(is.null(table), "query", glue::glue("{schema}.{table}"))
  logger::log_info("Checksum for {msg} is {checksum}")

  return(checksum)
}

con <- DBI::dbConnect(...)
checksum <- get_table_checksum(query = "SELECT * FROM schema.table", con = con)

[wlandau]

A couple thoughts to add:

• Another example came up with aggregation specifically: Use {duckdb} to speed up aggregation of large tar_map_rep() patterns tarchetypes#201.
• tar_repository_cas() might make DuckDB integration more seamless overall.

[phewson]

I found this guide very useful, thank you. I've been trying to make sense of pipelines that involve a database call as well. I had wanted to create a database connection as a tar_target(), run some queries and then have another tar_target() close the connection, but I couldn't get that working. So I'm using something like con <- create_connection() \n on.exit(dbDisconnect(con) \n dbGetQuery(con, sql_query), which I think is the same kind of logic as you where you open and close a connection within the querying function ( create_connection is defined in R/ and sql_query is another tar_target). So your code was a bit of a lifesaver. I have a running pipeline now, so I'm happy.