Data Preparation: 7 Easy Steps to Deliver High Quality Data

What is data preparation? 

Data preparation, also called preprocessing, is the process of collecting, cleaning, enriching, and storing data to make it available for business and analytical initiatives.

The data prep workflow gets data ready for multiple use cases:

1. Data analytics. From ad hoc data analyses to data mining, data analysts need to prepare the data into a structured format so they can visualize it, analyze it, and extract insights.
2. Business intelligence. To keep a finger on the company pulse, business analysts will import data into Microsoft Power BI, Tableau, or another BI tool, and construct KPIs and metrics that reflect the company’s performance. But the data needs to be cleaned and validated before they ingest it into the BI tool. If they imported raw data, a lot of KPIs and metrics would reflect the mess in the raw data (duplicated entries, missing values, outliers skewing results, etc.) and not the company’s performance.
3. Customer-facing data products. From simple customer dashboards showing how you used the app last month to personalized recommender systems that learn what you like, applications and products use customer data to elevate the experience of their users.
4. Data science initiatives. Machine learning algorithms can perform sci-fi-worthy tasks: from chatbots that answer your questions like a human to machine vision algorithms that can find Waldo from space. But machine learning algorithms achieve their results by training on data - videos, images, and texts. Unfortunately, big data algorithms cannot understand the raw input by themselves. You need to recode the unstructured data from images, videos, and texts into a machine-readable format before feeding it to the algorithms.

Data preparation is the prerequisite step for any data product and initiative. But how does data prep look in practice?

7 steps in the data preparation process (& best practices)

The data preparation workflow has 7 steps. Let’s go over each step and the best practices to complete each one. 

Step #1: Collect data

Data preparation starts by extracting raw data from its data sources. Data collection can be hard because each source of data has its own logic and needs to be handled in a different way. 

For example, collecting advertising data from Facebook Ads will require a different infrastructural design than collecting Excel reports on revenue performance from your accounting subsidiaries.

Best practice: Use devoted tools that take care of data collection for you. 

Data preparation tools automate the heavy lifting of data extraction, so you don’t have to worry about the technical aspects of data collection (like changing API endpoints, pagination, retrying at failures, etc.). For example, in Keboola, you can collect new data by configuring Extractors in a couple of clicks.

It is really that simple:

1. Log into Keboola (make a free account - no credit card required - if you don’t have an existing account)
2. Start building a new data collection flow by clicking Flows > New Flow from the main menu.
3. Pick an Extractor component to start collecting data from the data source you chose. For example, in the image below we selected Google Drive:

This is just the first step. You can build the entire data pipeline in Keboola by simply adding new components for each step of the data preparation workflow.

Contact us to find out how Keboola can help with your use case and data collection. 

Step #2: Clean data

Data cleaning, also known as data transformation or data wrangling, is the process of changing the raw data into a usable form. When data is extracted, it is rarely ready to be used as it is.

For example, unstructured data like video files or images need to be re-coded into a machine-readable language so we can process them.

But even with structured data (think CSV or Excel, data that looks like a table), you need to transform itto make it usable:

• Remove outliers
• Fill-in missing values
• Recode data in different formats to a standardized format
• Remove duplicate entries
• Aggregate and pivot datasets to be more in line with later analyses

Best practice: Data cleaning can be challenging for business users who don’t know how to use SQL or Python to clean data. Pick a data preparation tool that offers no-code transformations, so business users can prepare their own datasets without needing to wait on the IT or data department. 

For example, Keboola offers both no-code and low-code transformations, helping both data professionals and business users to self-service their needs. Engineers and business users can collaborate side-by-side on the same data sets by simply switching between the low-code and no-code tools.

Download: Get your data ready for analysis with Keboola’s Data cleaning checklist.

Step #3: Enrich data

Enrichment is the process of adding new data to your existing datasets, to bring more information to existing features for your analyses. The additional data helps you understand existing data better or build new features that support your data-driven decisions and products.

There are three ways to enrich data:

1. Data modeling. Join existing in-house datasets into an enriched single dataset. For example, at Keboola we helped partners from financial institutions build the customer 360 view that joins all customer data across all their data sources into a single enriched data product.
2. Enrichment with external data. Bring in services from outside your company to provide additional information. For example, many vendors offer services that allow you to enrich contacts in your email list. So you can use the contacts’ company information for segmented targeting. 
3. Manual labeling. Datasets often need to be labeled by a human. This is especially the case with machine learning models, where you need to add labels to help train your algorithms.

Best practice: Keep data enrichment and data collection separate. 

Enrichment is an added process that can succeed or fail irrespective of the data collection pipeline. By separating the two processes, you lower the chances of both failing. And if one does fail you, the bug resolution process will be much easier.

Step #4: Validate the data

Before you ingest data into your systems, validate it. Validation is an integral part of data quality assurance. 

There are two ways to validate data:

1. Statistical analysis - check the data has not deteriorated through the processes. For example, did new outliers creep in? Are there new data missing values?
2. Data visualization - plot the data into histograms, bar charts, scatterplots, and the visual representation of your choice to spot any anomalies. You can gauge from the distribution of values if anything seems wrong with the data before you ingest it into your data warehouse. 

Best practice: Keep it simple. Data validation can be an all-consuming task. Make sure to do the necessary checks on your data, but don’t over-engineer your solutions. 

Step #5: Store data

Store the prepared data where it is going to be consumed. This can be a data lake, a data warehouse, a BI tool, or even your app if you use data for customer-facing features.

Best practice: Rely on tools that do the heavy lifting for you. Data loading (technical expression for storing data) abilities differ between tools:

• ETL tools help you collect, clean, and then store data.
• ELT tools help you collect, store (in a data lake), and only afterward clean data.
• Reverse ETL tools help you send data from your data warehouse into an app.

Not all tools offer all three storage paradigms. So choose the tool that answers your storage needs. Or opt for tools like Keboola, which offer all three. Extra benefit? Keboola provisions a Snowflake DWH for you if you don’t have one.

Step #6: Document data

To be able to share and collaborate on the same datasets (and therefore avoid long explanation meetings or re-doing someone else’s work), you need to document them.

When documenting data, we define:

1. What each table field means
2. Values in a table field (expected values, why some might be missing, …)
3. Formulas used to compute derived metrics or dimensions
4. Anything important that might skew data analysis

Best practice: Document data in the same location where you produce and consume it. The most common mistake is documenting data separately from the data processes. This causes misalignment issues (data changes, the documentation doesn’t), discoverability issues (“Where is that page which explains my table?”), and maintenance issues (increases room for errors when changing data/documentation).

The best way to document data is to use features like the Data Catalog, which keeps data explanations tightly coupled with the data itself and can be easily shared alongside the data you share.

Step #7: Automate the data preparation pipeline

Once you set up the data prep workflow, automate it. Unless you’ll only need a dataset once (low chances), make your life easier by automating end-to-end all the preceding 6 steps.

Automation helps you streamline your work, lowers the chances of manual errors, and allows you to establish templates that can be reused and shared by others.

Best practices: Make data pipelines observable. Monitor your data pipelines to make sure everything is going as expected. Make sure to set up alerts if a step in the pipeline fails. If you can, make pipelines (or choose self-serving data processing tools like Keboola) that let you introspect and dig deeper into the logs to figure out why a pipeline failed. 

The seven steps described above are not a linear process. More often, we move from one step to the next and revisit the previous one for some more cleaning.

But when done correctly, data preparation offers several business advantages. In fact, we counted seven benefits of good data preparation.

What are the benefits of data preparation?

When data preparation is done right, the process benefits your business in seven ways:

1. Identify and fix errors quickly. Data prep happens before data integration. Which allows you to examine data before you send it to your data warehouse or use it in your decision-making. The process of preparing data brings up issues with the data and helps you identify and fix errors before you propagate them to the entire organization.
2. Trust data. Data validation and sanitation are integral parts of the data preparation process. Quality data assures you can trust the data you work with.
3. Eliminate duplicate work. Oftentimes ad hoc data preparation workflows duplicate the same tasks across different departments. For example, both sales business analysts and marketing business analysts would extract and clean customer transaction data for an ad hoc report. Organizing all your data preparation work centrally and under a common data management framework allows you to deduplicate work and lowers the chances of making human errors in one-time data preparation workflows.
4. Make better data-driven business decisions. The data you use to drive business decision-making affects the quality of your business outcomes. Good data leads to good decisions. 
5. Free more time for insights. As said, data preparation is the most time-consuming process in a data operative’s schedule. By taking control of data preparation and automating it, you can free up resources and spend more time on revenue-generating tasks.
6. Remove friction from collaboration. A well-prepared dataset will be clear to all its users - from documenting fields to understanding the metrics and logic in the dataset. Well-prepared datasets help bridge gaps between data operatives and business users and help them understand and collaborate on the same data.
7. Optimize the data prep process. A good data preparation workflow monitors and measures data pipelines used to produce the data for the various use cases. If you can measure, you can manage. Hence, setting up your data preparation workflows correctly helps you spot the inefficiencies in them and improve them.

Unlock all 7 benefits with self-service data preparation tools like Keboola.

Streamline data preparation with Keboola

Keboola is a data platform as a service that is packed with features that help you prepare data:

1. Over 250+ connectors help you set up data integration (both collection and storage) with a couple of clicks.
2. No-code (transformations, visual builder) and low-code (Python, SQL) features enable everyone to set up and automate their own data preparation workflows.
3. Keboola supports ETL, ELT, and reverse ETL data pipelines.
4. The in-built Data Catalog helps you document and share data across the board.
5. Full observability out of the box for every workflow in Keboola.

And so much more! 

Keboola is designed to automate and streamline all of your data operations, so you can cut the manual work and spend more time on strategic tasks that benefit the business. 

From security to governance and machine learning, Keboola has features that support all your data operations.

Keboola helped multiple clients automate the heavy lifting of data preparation. With Keboola:

1. Mall Group joined the data pipelines of its 50+ subsidiary companies into a single easy-to-manage data platform.
2. Harri was able to build a state-of-the-art AI product with 3x fewer people.
3. Rohlik ran all its data preparations from its humble beginnings to when it became the first Czech unicorn ($1 billion valuation).

Keboola provides the infrastructural backbone that lets you digitally grow and transform with data.

Curious about how you can streamline and automate data processing with Keboola?

Let’s jump on a quick call to discuss how to make your data operations easier.