Script Engine: Bringing the Power of Scripts to Make your Data Transformation More Efficient

Before detailing how Script Engine can help you transform data, it is helpful to know why we have added this as a feature to our product. Enriching raw data isn't just a phrase; it's a challenge that every data-driven company is currently confronting.

‍

A large excel sheet of raw data will never give the company the same statistical results as transformed data. Even a small fraction of missing or duplicate values in data can cause your analysis to fail. Therefore, it is critical to have data in a structured, valid, and understandable format to make accurate decisions.

‍

Now the question is what are the ways to transform the data?

‍

In the market, there are two types of users- coders and non-coders. For non-coders we already have No-Code Transformation feature that allows them to simplify, standardized, and enrich the data using simple mechanics drag and drop. Users who have good understanding of languages such as JavaScript/Python can use our Script Engine for data transformation.

Define Script Engine 

Script Engine is a hand-coding solution that allows users to transform the data using languages such as JavaScript/Python. By writing scripts, you can easily transform the raw dataset into a cleaned, enriched, and structured dataset. 

‍

Let's understand how a Script Engine can help you in transforming the data.

Sanitization 

Sometimes, it isn't easy to sanitize the data we frequently receive from numerous data sources in different column naming formats. Although you can use No code data transformation to clean the column names, it doesn't give you the advanced capability as Script Engine. However, with JavaScript/Python, you can easily make the column names simple, consistent, and clean. 

‍

In the following example, we have replaced the spaces in column names with underscores(_) and trimmed the leading and trailing spaces.

‍

Here's an output of this written script-

‍

The capability of the Script Engine is not limited to sanitizing the column names; you can convert the column values to any data type such as float, String, Numeric, and so on.

Validation

The two biggest issues companies have with their data are poor quality and inaccuracy. Repercussions from inaccurate data analysis lead to bad business decisions that have a negative impact on how a business runs.

‍

So, before using data for analysis, it is imperative to check its quality and accuracy. For example, validating the format of contact, email address, phone numbers, and postal is very easy with writing scripts.

Aggregation

Aggregation transformation is one of the most used transformations in the real world. It is used to perform a calculation on the data such as sum, average, count, etc. Grouping the column values gives you the single column value that provides you with the summary of the data list.

‍

For example, we have a dataset of Sales reports of a company that has quarterly sales each year. We can use the aggregation function here to get the company's annual sales report.

Before we tell you how you can handle the errors in our Script Engine, let's understand the execution of transformed data into a pipeline. Basically, data is inserted into a pipeline in the form of batches.

‍

For example, there's an excel sheet with 1000 rows of data, meaning 1 row is treated as a 1 batch. So, before running the script, we give you a way to handle the error mechanism.

Here's are the error handling mechanism-

Fail Pipeline 

Fail Pipeline is a bit precarious to choose because when the previous row batch gets failed, the Fail Pipeline skips the next to succeed row baches and will report the entire Pipeline as a failure. 

Fail Current Batch 

Fail Current Batch will skip the error row batch, pass the error-free batches, and save the entire Pipeline from failure. 

‍

The simplest example to understand the error handling is- Assume there's a small Food Sales data of 10 rows that contain columns like Total Revenue, Unit Price, Sales Channels, and so on.

‍

The column name Sales Channel has an "Offline" value in some rows. So we write a script that states- raise an exception if Sales Channel is "Offline." and select "Current Fail Pipeline" as an error handling mechanism. 

‍

When we test the script, the rows that has Sales Channel = "Offline" gets failed and rest of them have passed successfully.

‍

To identify the root cause of the error, you can either click on the "Download Error File" or click the "Error" text located in the "Test Result" window.

If you download the Bolt with exception, you will see how many rows are failed and successfully executed.

Debugging In Script Engine 

Debugging is a deductive process that gives absolution to code failure. If you work with scripting languages such as JavaScript/python, you will know they don't always play nicely. Thankfully we provide you the function called Script.log() that can be used to log any variable or print any message to debug the custom scripts. 

Import Libraries 

Advanced data transformation is easily achievable using languages like Python and JavaScript. We forbid users from devoting more time to building scripts than to comprehend the data.

Boltic's Script Engine provides preloaded libraries and packages for JavaScript and Python programming languages to facilitate data transformation. These are collections of pre-written functions and statements that can be used to increase the language's functionality and facilitate data transformation by creating scripts.

‍

Read Script Engine doc to know how to use it for data transformation.

Conclusion 

Script Engine is built to make the data maximally useful. The capabilities of the languages such as JavaScript/Python can allow users to work more intelligently and scrutinize the data as per their requirements. Then, whatever transformation you be in need of, just write the script. We provide a strong error handling mechanism to make your scripting experience smooth and unwrinkled.