Introduction

As the data ecosystem grows, the collection and storage of data is an integral part of the data environment, which is driven by business needs. The Denodo Platform delivers considerable benefit by enabling a logical data fabric across this distributed data landscape, and by making data accessible to an array of personas. With the Denodo Platform, these personas who are not involved and not aware of the initial steps in data collection, are able to rip the benefits of the data ecosystem by gaining insight and access to the data. These personas include business analysts, data developers, and anyone who needs to access specific company data sets.

The movement towards data democratization has encouraged the emergence of self-service data preparation tools that support exploratory data analysis. The data preparation approach with the Denodo Platform is Dataset based. The user principally interacts  with  “data products”.  A data product consists of one or more related data views, and is presented in a way that is similar to a table in a spreadsheet. Then, operations can be carried out on columns, e.g., by changing the formatting or splitting the values in one column over several. For example, there is some missing data, the names of columns representing the same data are not necessarily the same, the values within the street columns have different levels of detail in the different company sources, etc.

In this article we will explain the role of the Denodo Platform in a typical data preparation workflow. We will show how the presence of a data virtualization layer in the data architecture can enhance and speed up some of the most common tasks in the data preparation process.

The data prep workflow gets data ready for multiple use cases like Data Visualization, Data Science, etc. In this step, you are essentially working on the enhanced data products which already contain semantic definitions provided by the Denodo Platform. As a business user, you are able to take an existing data product which is likely providing data from multiple data sources and build on top of that with an additional data preparation layer to meet your needs. It is the prerequisite step for any data product and initiative. But how does data prep look in practice with the Denodo Data Marketplace ?

The data preparation workflow has 3 steps:

• Loading data to start a data preparation session.
• Data preparation to manipulate live data to build your query.
• Query execution.

Each step is important and requires the use of specific functionalities, particularly during data transformation. Let’s go over each step and the best practices to complete each one.

Denodo Data Marketplace and Data Preparation

When dealing with raw source data, it's not uncommon to encounter issues like duplicate records, inconsistencies in formatting, context, or null values where they shouldn't be. The fundamental tasks for successful analytical data preparation include blending, profiling, cleansing, filtering, transforming and standardizing data for analytics. These activities are done in a responsive environment, so the blending and shaping of data are immediately visible to the practitioners who are exploring and creating data for their analysis.

The Denodo Data Marketplace provides data preparation wizards for querying and customizing the data, which enables users to connect to the data with their favorite data visualization tools. It provides faster results without sacrificing governance controls.

The Data Marketplace data preparation features provide a new way for querying a view in Virtual DataPort, intended for business users with little knowledge on the view schema or VQL, based on the what you see is what you get principle :

• VQL Shell: you can express your need for information using a query in VQL which is the language required by Virtual DataPort. SQL-like statements are available in the VQL shell. For a full list of commands, enter the VQL command HELP. For a reference of VQL commands, see the Denodo Virtual DataPort VQL Guide.
• Query wizard: you can define the different elements of a query. When all parts are ready, you run the query to get the results. You can define complex conditions to filter the data of the view. If the view contains mandatory fields, the wizard will automatically create a filter for each one.
• Assisted Query: In the Assisted Query tab of a view you can define your information needed in natural language and an engine based on large language models translates it into a VQL query. Prior to using this feature, you will need to configure both the AI Integration API Configuration and the AI Integration Assisted Query Configuration. The Denodo Platform integrates with major GenAI providers. Currently, there are four options when configuring an API: Amazon Bedrock, Azure OpenAI Service, OpenAI or Custom. Assisted Query uses the capabilities of AI to create VQL queries from natural language input. This approach not only simplifies the querying process but also broadens its accessibility, allowing everyone to interact with databases.

Each user will be able to use their preferred method according to their needs and their experience in VQL or SQL.

Business case

Weather conditions have a significant impact on flight operations. They are monitored and analyzed daily to help planes fly safely. They need to be monitored to ensure that the likelihood of anything negative happening to the planes is as low as possible. With rocket launches, the risk and consequences of misread or interpreted data can be devastating.

We will try to work on preparing weather data to plan NASA rocket launch dates based on Data Science & AI use cases. Data preparation for machine learning is the process of cleaning, transforming, and organizing raw data into a format that machine learning algorithms can understand. The data is taken from the list of NASA missions on the Wikipedia page. You can find the data here: https://en.wikipedia.org/wiki/List_of_NASA_missions 

Common Data Preparation Problems

Raw data is usually not suitable for direct analysis. This is because the data might come from different sources in different formats. Business analysts, data scientists, data engineers or non-IT users often face data preparation problems. Software vendors have developed self-service data preparation tools. Those tools enable BI users and data science teams to perform the required data preparation tasks for analytics and data visualization projects. But they do not eliminate data prep's inherent complexities. The Denodo Data Marketplace can help in this process.

There is a whole class of different problems that can appear in a data set.

• Duplicates: Data is duplicated in the dataset, which can lead to analysis errors or incorrect conclusions.
• Spelling differences: Users entered data in different ways (name, address, etc.), making analysis impossible.
• Differences in format: The problem of regional formats (examples: '.', ',', ...) when importing data or subsequently transforming it.
• Numbers stored as text: Presence of spaces or other signs that prevent recognition of the format.
• Encoding differences: Text encoding which changes between 2 sources which must be processed in the same process (Latin1/iso8859 and utf-8 for example). 

The Data Preparation feature in the Data Marketplace addresses these issues and is intended for business users who are not very familiar with SQL. This functionality allows these users to query views, add filters, do transformations, etc.

Loading Data

The first step for data preparation is to load the data. With the Denodo Data Marketplace, a representative sample is extracted from the initial dataset.

The Data Preparation tab provides an access to the Columns section which presents a panel with all the available fields from the view and its related views.

To start the data preparation session, we selected the columns we are interested in using drag and drop (Figure 1), then we loaded the data with the button “Load data” (Figure 2).

Figure 1: Columns selection for data preparation.

For our analysis, we only need some of the column data. The dataset has 100 entries and 9 columns, several of which have null values.

Figure 2: Loading and exploring the data.

In this step, we need to examine the dataset to gain an understanding of its structure, content, and quality. It includes tasks such as exploring the data, identifying missing values, and exploring the distribution of data.

Once the initial data is loaded in the previous step, you can start creating your query from the Definition section.

Preparing Data

At this stage, the objective is not to analyze correlations but rather to look for errors that could have crept in here and there. It's about discovering data, exploring it, applying filters or transformations, creating or deleting columns, etc. It is essential to identify empty fields or data formats. For example, you might detect a category of data that needs to be modified to be usable. If your dataset contains outliers, transformation operations are useful to continue leveraging your valuable data.

At this level the Denodo Data Marketplace presents you with a representative subset of the data which functions as a preview of the results of your query execution. You can apply actions on this data, one at a time, iteratively, until you are satisfied with the result. Each action is executed in real time. The results of the query execution are made available to you so that you can immediately see the effect of your actions on the data.

The Definition section has three different areas (Figure 3):

• A table with live query results in the center displays a preview of the current query results after applying the actions on the view. The table data is updated in real time with each action on the data.
• The data preparation panel on the left contains the action history, that is, the list of actions that were applied to construct the results of the current query in chronological order.
• The Filters and Transformations panels on the right show a list of actions in alphabetical order to apply to the selected column. The list of available actions may be different for each column as it depends on the column’s data type.

Figure 3: The Definition section of the Data Preparation feature.

Handling Missing Data

This is a widespread problem in the data analysis world. Missing data can occur in the dataset for several reasons: the data for the specific field was not added by the user/data collection application, the data was lost during a manual transfer, a programming error, etc.

Sometimes it is essential to understand the cause, as it will influence how you process this data. To handle missing values, we will fill the missing values with appropriate values.

In this context, we will consider the following fields:

To change the NULL values ​​of the 'Launched' column, we will create a new column called 'launch_new' using the 'Create new column from expression' option. We will use the SQL COALESCE() function to replace all NULL values ​​in the 'Launched' column with the character 'No' (Figure 4).

Figure 4: Change NULL values using an expression.

In the same way, we can replace the NULL values ​​in the 'Condition' column with the character string 'Fair'. The figure below shows the result.

Figure 5: New columns updated and created to handle missing values.

Transform the Data

Data transformation involves the provision of a format transformation rule that given an incorrectly formatted value transforms it into a correctly formatted one.

One of the most common types of data we encounter in data science use cases is categorical data. Demographics, weather conditions, etc. are omnipresent in our datasets. These are types of data that can be grouped into different categories but have no natural order or numerical value.

Most machine learning algorithms fundamentally work with numerical data. These algorithms use mathematical operations to learn patterns in data, make predictions, and calculate errors. Categorical data, in its raw form, is not suitable for these operations.

Data transformation should always be based on an established set of business rules. These rules inform what data is required for a purpose and what form it should take. Business rules must be clearly defined for data to be valuable. Once defined, the rules should be communicated across the business and maintained by the subject matter expert (SME).

In this context, we will consider the following transformations:

When categorical data is properly transformed into numerical data, it allows machine learning algorithms to efficiently process and learn from this data. This capability leads to more accurate models, better generalization to new data, and ultimately improved model performance.

To transform the values ​​of the columns 'Wind_Direction' and 'Crewed or Uncrewed', we will use the 'Replace' transformation function available on the Transformation panel (Figure 6). A transformation is a function that converts the column values. This conversion may modify the data type of the column value too.

Figure 6: Change column values following business rules.

In addition, the Denodo Data Marketplace provides support for data enrichment, which is the process of refining, improving, and enriching a dataset by adding new features or columns. This helps improve the accuracy and reliability of raw data. The figure below shows the result of this transformation.

The Data preparation panel contains the actions history, i.e., the list of actions that were applied to build the current query results in chronological order. At any time, you can save your query with the list of actions to execute it later.

Figure 7: Data transformation result.

Query Execution

Once everything is well organized, cleaned and improved, all that's left to do is run your query.  Click the Execute button in the toolbar and you will be redirected to the Results section.

After the data preparation step, you can save the resulting view of the data (query itself). You can share it within your team, and you can even create a new data product to make this newly transformed data query available to other departments in your organization based on your governance and data sharing policies.

In the Results section you can check the results of the query executed on the view, export them to a CSV, HTML or Excel file using the Export button on the toolbar.

Figure 8: Query execution.

Conclusion

Data preparation is an iterative process of transforming data into a meaningful form to support analysis and decision making. In this document, we have presented key functionalities within the Denodo data preparation feature. The goal of data preparation is to ensure that your business or organization has accurate, up-to-date data to improve your analytics.

With Denodo Data Marketplace's data preparation pipeline, you have to follow a series of steps to get every view and data product in your business performing at an optimal level. Analytics practitioners (specifically business analysts and citizen data scientists) are more productive because they have the access and the features to cleanse and prepare data for their reporting and analytics in a user-friendly Excel-like environment that does not require coding.

Some key elements:

• Accuracy: Data must be accurate; duplicates and erroneous data must be removed.
• Data format: this is one of the most common problems, it must be managed to promote data accessibility.
• Security: securing your sensitive data is essential in a world where data leaks can cause considerable damage to your business. See : Best practices in designing fine-grained privileges in multi-layered virtual models
• Up-to-date data: Data preparation is not just a one-time process, but an ongoing one. Data must be up to date at all times.

References

Introducing the Data Marketplace  

Using Denodo Data Marketplace