Stop Writing Manual Data Joins: Building a Metadata-Driven Engine in R

The Problem with Relational Data Analysis

If you work with relational databases, you know the drill. You have detailed “fact” tables (like transactions or page views) and descriptive “dimension” tables (like customers or products).

Before you can build a machine learning model or a simple dashboard, you have to write the same boilerplate code over and over: aggregate the transactions by user, aggregate the views by user, left-join both to the customer table, handle the missing values, etc.

In R, using data.table or dplyr, this often results in massive, brittle blocks of code. If a schema changes, your script breaks. If an analyst accidentally joins on the wrong key, they get silent, massively duplicated data.

We wanted a system where users could just declare what they want, and let the computer figure out the steps to get there. That’s where DBmaps comes in handy.

The Core Concept: Metadata as Code

The philosophy behind DBmaps is simple: Metadata should drive the execution.

Instead of writing imperative code (e.g., merge(A, B, by="id")), you define the analytical potential of your tables once. You create a “Table of Tables.”

You tell the system:

What a table represents.
What its primary keys are.
How its metrics can be mathematically aggregated.

Let’s look at how this works in practice using a standard e-commerce dataset (Customers, Products, Transactions, Views).

Defining the “Table of Tables”

The heavy lifting happens in the table_info() function. Instead of aggregating data right away, we store unevaluated expressions (using R’s quote()) that tell the system how to calculate metrics later.

Here is how we define the metadata for our transactions table. Notice how we specify that Revenue can be aggregated multiple ways (by customer, by product, etc.).

transactions_info <- table_info(
  table_name = "transactions", 
  source_identifier = "transactions.csv", 
  identifier_columns = c("customer_id", "product_id", "time"),
  key_outcome_specs = list(
    list(
      OutcomeName = "Revenue", 
      ValueExpression = quote(price * quantity), 
      AggregationMethods = list(
        list(AggregatedName = "RevenueByCustomer", 
             AggregationFunction = "sum", 
             GroupingVariables = "customer_id"),
        list(AggregatedName = "RevenueByProduct", 
             AggregationFunction = "sum", 
             GroupingVariables = "product_id")
      )
    )
  )
)

Centralizing Intelligence: The Metadata Registry

Defining metadata for a single table is great, but a real database has dozens, sometimes hundreds, of tables. Passing around individual R lists for every table would quickly become a messy, unscalable nightmare. We realized we needed a central “brain” for the package—a Metadata Registry. The registry acts as a single, searchable catalog of your entire data ecosystem. Instead of loose variables, you initialize a registry and add your table definitions to it.

# Initialize the empty registry
meta <- create_metadata_registry()

# Populate it with our defined tables
meta <- add_table(meta, customers_info)
meta <- add_table(meta, products_info)
meta <- add_table(meta, views_info)
meta <- add_table(meta, transactions_info)

Internally, this registry converts the nested lists into a structured data.table. This is crucial because it allows the DBmaps engine to rapidly query the metadata later.

Auto-Discovering the Schema: map_join_paths

Once the registry is populated, it plays its most crucial role: mapping the relationships between tables.

In traditional workflows, an analyst has to look at an Entity-Relationship (ER) diagram to figure out how table A connects to table B. We automated this with the map_join_paths() function.

# Provide a list of the actual data tables
all_tables <- list(
  customers = customers,
  products = products,
  transactions = transactions,
  views = views
)

# Generate the join map based on the metadata
paths <- map_join_paths(meta, all_tables)
print(paths)

Output:

table_from      table_to      key_from                    key_to
1: views        products      product_id                  product_id
2: views        customers     customer_id                 customer_id
3: transactions customers     customer_id                 customer_id
4: transactions products      product_id                  product_id
5: transactions views         customer_id,product_id,time customer_id,product_id,time

By cross-referencing the metadata, the system has automatically discovered every valid, directional, many-to-one join that can be performed in this database. It knows exactly how to get from transactions to products without us having to explicitly write the join logic.

Up Next in Part 2…