Obtaining value from data through analysis often requires significant prior efort on data preparation. Data preparation covers the discovery, selection, integration and cleaning of existing data sets into a form that is suitable for analysis. Data preparation, also known as data wrangling or extract transform load, is reported as taking 80% of the time of data scientists. How can this time be reduced? Can it be reduced by automation? There have been significant results on the automation of individual steps within the data wrangling process, and there are now a few proposals for end-to-end automation. This paper reviews the state-of-the-art, and asks the following questions: Can we automate data preparation - what techniques are already available? Should we - what data preparation activities seem likely to be able to be carried out better by software than by human experts? Must we - what data preparation challenges cannot realistically be carried out by manual approaches? 1https://www.nytimes.com/2014/08/18/technology/for-big-data-scientists-hurdleto-insights-is-janitor-work.html 2https://www.forbes.com/sites/gilpress/2016/03/23/data-preparation-most-timeconsuming-least-enjoyable-data-science-task-survey-says/#6d86d9256f63 • Source selection: choosing the data sets that are actually suitable for the problem at hand, in terms of relevance, coverage and quality. • Matching: identifying which properties of diferent sources may contain the same type of information. • Mapping: the development of transformation programs that combine or reorganize data sources to remove structural inconsistencies. • Data repair: the removal of constraint violations, for example between a zip code and a street name. • Duplicate detection: the identification of duplicate entries for the same real world object within individual data sets or in the results of mappings. • Data fusion: the selection of the data from identified duplicates for use in later steps of the process.
It is widely reported that data scientists are spending around 80%
of their time on data preparation1, 2. Likely data scientists can’t
realistically expect to spend 0% of their time on data preparation,
but 80% seems unnecessarily high. Why is this figure so high? It
isn’t because there are no products to support data preparation;
the data preparation tools market is reported to be worth $2.9B
and growing rapidly [
It seems likely that data preparation is expensive because it is
still in significant measure a programming task: data scientists
either write data wrangling programs directly (e.g., [
To realise this principle in practice, all the steps described above need to be automated, informed by the description of what is required. Providing a description of what is required involves the data scientist in understanding the problem to be solved, but this is what the data scientist should be focusing on3.
In this setting, we assume that it would be desirable for a
system to take more responsibility for data preparation tasks
than is currently the case, if there can be confidence that the
system will perform as well as a human expert. As a result in this
paper, we explore three questions:
• Can we automate? There has been previous work on
automating individual data preparation steps. Section 2
reviews such work, and the information that is used to
inform automation.
3We note that there are also likely to be further data preparation steps that are
more closely tied to the analysis to be undertaken [
CAN WE? What might an automated approach to data preparation look like? In our principle for data preparation automation in Section 1, it is proposed that the user should provide a description of what is required. This means that it can be assumed that the user is familiar with the application in which data preparation is to take place, and can provide information about it.
What sort of information may be required by an automated system? Table 1 lists proposals for automating each of the data preparation steps listed in Section 1, and for each of these steps other proposals exist that either use diferent techniques to underpin the automation or diferent evidence to inform the automation.
It cannot be assumed that the automation of data preparation can take place without some supplementary evidence to inform the step. In Table 1, the What Evidence is Used column outlines what data is used by each proposal to inform the decisions it makes. In some cases, rather little supplementary data is required. For example Data Profiling acts on source data directly, and Matching builds on source data and metadata.
However, in general, automation stands to benefit from
additional evidence. As an example, for Format Transformation, the
cited method infers format transformation programs from
examples. For example, if we have the source names Robert Allen
Zimmerman and Farrokh Bulsara4, and the target names R.
Zimmerman and F. Bulsara, a format transformation program can
be synthesized for reformatting extended names into
abbreviated names consisting of an initial, a dot, and the surname [
An important feature of automated approaches is that they
can often generate (large numbers of) alternative proposals. For
example, a mapping generation process is likely to produce
multiple candidate mappings, and a duplicate detection program can
likely generate alternative comparison rules and thresholds. As
a result, a popular approach is to generate a solution
automatically, and then request feedback on the results. This feedback can
then be used to refine individual steps within the automated data
preparation process (e.g., for mapping generation [
Up to this point, the focus has been on automation of
individual steps within the data preparation process; most results to date
have involved individual steps, but there are now a few more
end-to-end proposals. In Data Tamer [
In considering when or whether to automate data preparation
steps, it seems important to understand the consequences for
automation on the quality of the result, both for individual steps
and for complete data preparation processes. In enterprise data
integration, for example for populating data warehouses using
4The birth names of Bob Dylan and Freddie Mercury, in case you are wondering.
5Commercialised as Tamr: https://www.tamr.com/
ETL tools, the standard practice is for data engineers to craft
well understood ETL steps, and to work on these steps and their
dependencies until there is high confidence that the result is
of good quality. It is then expected that analyses over the data
warehouse will provide dependable results. This high-cost,
highquality setting is both important and well established, and may
represent a class of application for which expert authoring of
ETL processes will continue to be appropriate. In such settings,
the warehouse is primarily populated using data from inside the
organisation, typically from a moderate number of stable and
well understood transactional databases, to support management
reporting. However, there are other important settings for data
preparation and analytics; for example, any analysis over a data
lake is likely faced with numerous, highly heterogeneous and
rapidly changing data sources, of variable quality and relevance,
for which a labour-intensive approach is less practical.
However, such data lakes provide new opportunities, for example for
analysing external and internal data sets together [
It seems that there have been few studies on the efectiveness of automated techniques in direct comparison with manual approaches, but there are a few specific studies:
Format Transformation: Bartoli et al. [
Mapping generation: Qian et al. [
Overall, the evidence on the quality of the results of automated
data preparation in direct comparison with manual approaches
seems to be quite hard to come by in the literature, and
further studies would be valuable. However, research papers on
automated techniques often report empirical evaluations of their
absolute performance and/or performance against a
computational baseline, which provides evidence that such techniques
can provide respectable results. Furthermore, there are also
empirical evaluations of the impact of feedback on results; these
show significant variety. In some problems substantial
improvements are observed with modest amounts of feedback (e.g., [
Should automation be focused on individual steps or on the
end-to-end data preparation process? Likely this depends on the
task and environment at hand. Where data preparation involves
programming, data engineers have complete control over how
the data is manipulated, and thus bespoke processing and
complex transformations are possible. End-to-end automation will
not be able to provide the same levels of customization as are
available to programmers. As a result, there is certainly scope
for automating certain steps within an otherwise manual
process, although the potential cost savings, and synergies between
automated steps, will not be as substantial as with end-to-end
automation. Furthermore, we note that avoiding programming
is a common requirement in self-service data preparation [
Are there circumstances in which the only option is to automate? It seems that automation must be used if the alternative is to leave the task undone; in such situations, a best-efort automated approach creates opportunities for obtaining value from data that would otherwise be missed. Here are two situations where automation seems to be the only option: • The task presents challenges that are punishing for manual approaches. The big data movement is associated with the production of ever larger numbers of data sources, from which value can potentially be achieved by bringing the data together in new ways. The Variety, Veracity and Velocity features of big data mitigate against the use of manual data preparation processes, where specific cleaning and integration steps may need to be developed for each new format of data set. It seems likely that manually produced data preparation tasks will always lag behind the available data. In particular, the growth of open data sets and the development of data lakes present opportunities for exploratory analyses that require flexible and rapid data preparation, even if the results may not be as carefully curated as a human expert could produce given suficient time. • The resources are not available to enable a thorough, more manual approach. The knowledge economy doesn’t only consist of large enterprises; e.g., as noted in the UK government’s Information Economy Strategy6, the overwhelming majority of information economy businesses – 95% of the 120,000 enterprises in the sector – employ fewer than 10 people. As a result, many small and medium sized enterprises are active in data science, but cannot employ large teams or have large budgets for data preparation. For example, an e-Commerce start-up that seeks to compare its prices with those of competitors, or a local house builder that is trying to understand pricing trends in a region, may need to carry out analyses over a collection of data sets, but may not employ a team of data scientists. 6https://www.gov.uk/government/publications/information-economy-strategy What about the individual steps within data preparation, from Table 1? Are there cases in which an automated approach seems the most likely to succeed? The following seem like cases where it may be dificult to produce good results without automation: • Matching: Identifying the relationships between the attributes in n of sources involves n2 comparisons; even manually curating the results of such automated comparisons is a significant task. • Mapping: Exploring how data sets can be combined potentially involves considering all permutations; again, any manual exploration of how data sets can be combined for large numbers of sources seems likely to miss potentially useful solutions. • Entity Resolution: Entity resolution strategies need to conifgure significant numbers of parameters (typically in all of blocking, pairwise comparison and clustering), as well as defining a comparison function; this is a dificult, multidimensional search space for a human to navigate.
These challenges at the level of individual steps are compounded
when considering a pipeline of operations; we have the
experience that the best results come when parameter setting across
multiple steps is coordinated [
This paper has discussed the hypothesis that data preparation should be automated, with the many components being configured for specific sets of sources on the basis of information about the target. Three questions have been considered:
Can we? There are significant results on the automation of individual steps, and several proposals for end-to-end automation, where the steps are informed by data about the intended outcome of the process, typically in the form of training data or examples. For the future, further work on each of the steps, for example to use diferent sorts of evidence about the target, should increase the applicability of automated methods. Early work on automating end-toend data preparation seems promising, but there is likely much more to do.
Should we? A case can be made that automating many of the steps should be able to produce results that are at least as good as a human expert should manage, especially for large applications. There is a need for more systematic evaluation of automated techniques in comparison with human experts, to identify when automation can already be trusted to identify solutions that compete with those of experts, and those in which the automated technique or the evidence used can usefully be revisited.
Must we? There will be tasks that are out of reach for manual approaches. These may not only be the large and challenging tasks; if your budget is x and the cost of manual data preparation is 2x , then the task is out of reach. As in many cases the available budget may be severely constrained, there is likely to be a market for automated techniques in small to medium sized organisations, where at the moment more manual approaches are rather partial (e.g. investigating only a small subset of the available data). In addition, with the data lakes market predicted to grow at a 28% compound annual growth rate to $28B by 20237, eficient techniques for exploratory analyses over data lakes are likely to be in growing demand.
Acknowledgement: Research into Data Preparation at Manchester is funded by the UK Engineering and Physical Sciences Research Council (EPSRC) through the VADA Programme Grant. 7https://www.marketresearchfuture.com/reports/data-lakes-market-1601