If you missed it, check out Part 1, where we built a central Metadata Registry to map out the relationships in a relational database automatically.
We built a system that knows exactly what each table represents, how its metrics can be calculated, and how all the tables connect to one another. But a map isn’t very useful unless you have an engine to drive it.
In this final part, we will look at how we built the Execution Planner—a system that takes a simple, human-readable request and automatically writes the optimized data.table code required to generate the final dataset.
The Problem: Analysts Just Want Columns
When an analyst sits down to build a dashboard, they don’t want to think about the intricacies of left-joins and pre-aggregations. They usually just think:
“I want a list of products, their categories, the total revenue they generated, and how many times they were viewed.”
To get this, they normally have to write a massive, multi-step script. We wanted them to just pass a list of desired columns to DBmaps and let the package do the rest.
The Engine: create_join_plan()
We built create_join_plan() to act like an SQL query planner. It translates the analyst’s high-level request into a concrete, step-by-step recipe.
# 1. Define our desired output
selections <- list(
products = c("product_id", "category"), # Base columns
transactions = "RevenueByProduct", # Aggregated metric A
views = "ViewsByProduct" # Aggregated metric B
)
# 2. Generate the execution plan
plan <- create_join_plan(
base_table = "products",
selections = selections,
metadata_dt = meta,
join_map = paths
)When we inspect the plan, the magic revealed is that it doesn’t just figure out what to do; it literally writes the underlying data.table code for you.
# Output of print(plan)
step operation target code
1: 1 AGGREGATE agg_transactions agg_transactions <- transactions[, .(RevenueByProduct = sum(price * quantity)), by = .(product_id)]
2: 2 AGGREGATE agg_views agg_views <- views[, .(ViewsByProduct = .N), by = .(product_id)]
3: 3 MERGE merged_step_3 merged_step_3 <- merge(x = products, y = agg_transactions, by = c('product_id'), all.x = TRUE)
4: 4 MERGE merged_step_4 merged_step_4 <- merge(x = merged_step_3, y = agg_views, by = c('product_id'), all.x = TRUE)
5: 5 SELECT final_data final_data <- merged_step_4[, .SD, .SDcols = c('product_id','category','RevenueByProduct','ViewsByProduct')]The Safety Net: Catching Logical Errors
One of the most dangerous things in data engineering is a join that runs successfully but produces mathematically incorrect data because the keys were wrong. What if someone asks for RevenueByProduct (grouped by product) but tries to attach it to a Customers base table (keyed by customer)?
Warning: No direct path found from 'transactions' to 'customers'. Skipping this table.The planner protects the user by ensuring every operation follows a logical, auto-discovered path from the Metadata Registry.
Visualizing the Execution Plan
Because our execution plan is essentially a Directed Acyclic Graph (DAG), we integrated the DiagrammeR package to visualize the data flow.
DiagrammeR flowchart output
The graph visually maps the flow: source tables flow into intermediate aggregations, the base table merges sequentially with those aggregations, and everything funnels down into the final SELECT operation.
Executing the Plan
Finally, to get the actual data, we simply pass the generated plan to our executor.
# Run the plan to generate the final dataset
final_dt <- execute_join_plan(plan, all_tables)
print(head(final_dt))Conclusion
Building DBmaps was an incredible deep dive into system architecture and metaprogramming. By treating transformations as a directed graph governed by metadata, the process becomes safer and highly scalable.
Stay tuned for upcoming posts!