Auditing data transformation
Auditing data transformation can be simply described as gathering metadata about the transformation process. The most basics metadata would be a timestamp, atomic transformation description, data volume on input, data volume on output, time elapsed.
If you work with R only interactively you may find it more like a fancy tool. On the other hand for automated scheduled R jobs it may be quite helpful to have traceability on the lower grain of processing than just binary success or fail after the script is executed, for example a logging each query against the data.
Similar features are already available in ETL tools for decades.
I've addressed this in my dtq package.
It silently eavesdrop every call to
[ for any
It can be perceived as preconfigured and faster version of
base::trace tailored for data transformation auditing.
I believe it should be possible to port the idea to magrittr's
%>% call if somebody need auditing there.
Populate example sales data.
library(dtq) set.seed(1) DT <- data.table( user = 1:10, group = letters[1:5], time = as.POSIXct(seq(from=1.4e9L+1L,to=1.5e9L,by=1e3), origin="1970-01-01"), sales = rnorm(10*5*1e5, 5) ) knitr::kable(head(DT, 2))
Order data, various aggregations.
## just to clarify below usage of unnamed args in `[` # DT[ i, j, by, keyby ] # first sale by user in each month DT[order(time), head(.SD, 1), .(user, year(time), month(time))] # total sales by user in each month DT[, .(sales = sum(sales)), .(user, year(time), month(time))] # total sales by group of users in each months, returning ordered and keyed data DT[, .(sales = sum(sales)),, .(group, year(time), month(time))]
Lets preview transformation metadata recorded by
knitr::kable(dtl(print = TRUE))
|1||1||1||DT||[i = order(time), j = head(.SD, 1), by = .(user, year(time), month(time))]||2015-06-04 00:17:05||R_GlobalEnv||5.026816||5e+06||390|
|2||2||1||DT||[j = .(sales = sum(sales)), by = .(user, year(time), month(time))]||2015-06-04 00:17:09||R_GlobalEnv||3.543426||5e+06||390|
|3||3||1||DT||[j = .(sales = sum(sales)), keyby = .(group, year(time), month(time))]||2015-06-04 00:17:12||R_GlobalEnv||3.439573||5e+06||195|
Populate budget data to set together with sales data.
BUDGET <- CJ( group = letters[1:5], year = 2014:2016, month = 1:12 ) # cross of dimensions BUDGET[, budget := rnorm(3*12*5, 12*1e4, 12*1e3)] # budget value measure
For more complex processing I've found it very useful to organize single conceptual query on data into single chain of
In below example I will calculate year-to-date sales and budget values and last year sales vs current sales ratio.
# column names measures <- c("sales","budget") ytd_measures <- paste("ytd", measures, sep="_") ly_ytd_measures <- paste("ly", ytd_measures, sep="_") # query DT[, .(sales = sum(sales)),, .(group, year = year(time), month = month(time)) ][BUDGET, `:=`(budget = i.budget, budget_ratio = sales / i.budget) ][, c(ytd_measures) := lapply(.SD, cumsum), .(group, year), .SDcol = measures ][, c(ly_ytd_measures) := shift(.SD), .(group, month), .SDcol = ytd_measures ][, ytd_sales_vs_ly_ytd_sales := ytd_sales / ly_ytd_sales ]
dtq logs again.
knitr::kable(dtl(print = TRUE))
|1||1||1||DT||[i = order(time), j = head(.SD, 1), by = .(user, year(time), month(time))]||2015-06-04 00:17:05||R_GlobalEnv||5.0268161||5000000||390|
|2||2||1||DT||[j = .(sales = sum(sales)), by = .(user, year(time), month(time))]||2015-06-04 00:17:09||R_GlobalEnv||3.5434259||5000000||390|
|3||3||1||DT||[j = .(sales = sum(sales)), keyby = .(group, year(time), month(time))]||2015-06-04 00:17:12||R_GlobalEnv||3.4395734||5000000||195|
|5||5||1||DT||[j = .(sales = sum(sales)), keyby = .(group, year = year(time), month = month(time))]||2015-06-04 00:17:16||R_GlobalEnv||3.3816404||5000000||195|
|6||5||2||DT||[i = BUDGET, j =
Time added to transformation due to logging using
dtq is minimal.
For time consuming queries it is unnoticeable ~ 0% overhead.
For the most pessimistic, unrealistic query:
for(i in seq_len(1e3)) data.table(a=1L)[,.(a)] it adds around 30% overhead.
Processes evaluated by
dtq on each data.table
- gathering information
- current call
- top environment of the current call
nrowon input and output
proc.timeon start and end, or
- append gathered info to log storage
cfunction is used to append current log to existing logs
All other information (sources, sequences, queries) are extracted from
dtq logs while accessing them by
dtl(). So they are not adding any overhead during data transformation.
dtl() is a function to query against R6 class log storage.
Using in package
It is common to form your scripts into package so you may want to track data transformation from within your package.
It is handled by default, you don't need anything more than
library(dtq) on script init.
Actually you can restrict logging to particular package or global env, which is useful if some of your dependencies are also using data.table and by default would be logged together with your logs.
Example use cases
Storing dtq logs in rds/csv/db
Traceability is good but if you close R session the
dtq logs will be gone.
Simplest would be
append=TRUE on the end of script.
dtl(print = TRUE) to get rid off list-type columns which stores the
If you want to keep R data types you should then use
You can easily analyse your logs and detect the most time consuming sub-queries. Compare its timing to volume of in/our rows, etc.
Apply business rules on metadata
Your currency exchange source system is commited to provide daily exchanges rates in 5 minutes intervals.
- You expect exactly 288 rows on the input. If that is not true you catch it and send email directly from R to source system support.
- In your high level processing you expect to have 5 different KPIs on output. If it is not true you catch it and send email to yourself.
I would appreciate if someone could recommend a commenting service for github jekyll blogs, without tracking feature like in disqus service. Including self hosted of course.