A guide to data automation

 

Data automation is becoming increasingly important for companies handling large volumes of data every day. But it’s a broad term that covers a lot of processes and applications, all of which Switchboard manages for customers so their business and engineering teams can focus on mission-critical initiatives that drive revenue. So, if you’re wondering what exactly we mean by data automation, read on.

 

What is data automation?

 

Automation is a cornerstone of modern civilization. From mechanical production lines of the early 20th century, to artificial intelligence in computer software, automation adds a layer of efficiency, predictability, and speed to many processes. With the vast amount of digital data generated on a daily basis today, using automation is the only way of managing it effectively.

 

Data automation describes any activity which uploads, processes, or otherwise handles data using automatic tools rather than performing them manually. In practice, this might involve updating a database programmatically, rather than business or engineering teams manually uploading or reformatting data respectively. 

Data automation can be applied to each stage of ETL (Extract, Transform, and Load), and it can be used to build different types of data pipelines.

Types of data automation?

 

There are different types of data automation pipelines which can be used depending on the requirements of the data application.

 

Batch data pipeline

 

Batch data pipelines process or transfer a large amount of data from source to its destination in one process. This is either carried out periodically or at predefined intervals. For example, data can be transferred from a CRM system to a data warehouse on a weekly or monthly basis. A report can then be generated from this dataset.

​

Streaming data pipeline

 

Streaming data pipelines process or transfer data continuously as it is created at the source. For example, streaming data can be used to move real-time data from multiple sources into ML (Machine Learning) algorithms for analysis to make product recommendations. The ML scores can then be used in a response to the user, or stored for feedback.

 

Change data capture pipeline

 

Rather than process or update the whole dataset, these pipelines only process or update the differences made since the last sync – only data that has been changed needs to be processed. Change data capture pipelines are often used between two cloud services which share the same dataset.

​

Source data automation

 

Source data automation refers to the practice of extracting data from a source system in real time. For example, scanning ticket QR codes at an event to authorize entry and update the guest list in real time. This automated method of entry removes the step where data is collected manually, resulting in increased speed, reduction in cost, and elimination of human errors. Data can be collected instantly and processed in real time.

​

 

Data automation examples

 

Since it’s easier to conceptualize data automation through practical use cases, let’s take a look at a couple of simple examples of the process in action.

 

Automatically update ecommerce website with supplier data

 

A retailer buys their products from a wholesale supplier, but their product availability and prices are dependent on those of the supplier. They risk selling products that are out of stock, or at a loss. The retailer wants their ecommerce website’s product listings to be automatically updated with data from their wholesaler’s online catalog. This is automated using the following pipeline:

​​

1. Set up an automated scrape to run twice a day that extracts both product prices and available stock from the supplier’s web catalog.

2. Host the script on a cloud-based VPS (Virtual Private Server) so the process isn’t dependent on a local computer.

3. Once processed by the script, the dataset is passed to the retailer’s CMS (Content Management System), such as a Shopify or Magento API, which then updates the retailer’s ecommerce website.

 

Automatically monitor property data and post to Twitter

 

A real estate agent wants to automatically send tweets about the latest properties for sale in various counties. This can be carried out using the following process:

 

1. Each morning, a cloud-hosted script collects property-for-sale counts from various online sources, separated by county.

2. The script detects whether any new data is present, then imports these into a spreadsheet.

3. The spreadsheet is analyzed using a combination of formulas and macros, then the text for a tweet is composed using a series of rules and added to a separate spreadsheet.

4. Another script detects any additions to the tweet spreadsheet, then automatically posts new entries to Twitter.

​

 

Benefits of data automation

 

Automation provides considerable benefits to data pipelines and workflows. Here are just a few of the main advantages over manual intervention:

 

• Increased speed – Naturally, automation saves significant amounts of time spent extracting, transforming, and loading data because it performs operations much faster than humans can. As data sets become increasingly large, the time savings will continue to increase over time.

• Improved data quality – Less exposure to manual processing leads to fewer errors in data. Automation provides far greater reliability and gives teams the confidence to make better business decisions based on the data.

• Better scalability – Any changes required can be quickly propagated throughout the data pipeline, and this can be easily implemented via a drag-and-drop UI. In contrast, manually updating tasks requires the work of data experts. Automation makes improvements increasingly easier over manual intervention as data sets become larger and more numerous.

• Better use of talent – Automation takes care of repetitive tasks, such as standardization and validation, which would normally be time-consuming to accomplish manually. This frees the data engineering team from low-skill tasks, such as fundamental reporting, to focus on more productive work, such as high-level analysis which can inform mission-critical initiatives.

• Lower cost – All of these factors associated with automation add up to a lower total cost incurred for processing data. Even when you consider the initial outlay of implementing an automated DataOps solution, the ROI is soon evident. Producing more accurate data sets - faster - speeds up business analytics, which in turn provides more profitable activities with a faster turnaround. Since person-hours are more expensive than computing time, a greater level of automation results in more cost-effective solutions.

​
 

Data automation: tools and techniques

 

There are many software tools used for data automation, ranging from programming languages that fundamentally manipulate data, to comprehensive platforms that provide access to multiple pipelines.

​

 

Excel for data automation

 

Microsoft Excel has become the de facto standard for storing and manipulating data (at least among non-technical teams) since it was released in 1982. While Excel is able to perform sophisticated operations using formulas, pivot tables, and macros, it is no longer the most suitable tool for a modern data pipeline. 

 

The limitations of Excel are threefold. First, there is a lack of error control. Major issues can be caused by a single cell or slightly mis-keyed formula, and it’s difficult to locate problems in a spreadsheet. Debugging or testing methods are challenging, which makes spreadsheets very error-prone.

 

Second, it’s not straightforward to repurpose existing spreadsheets for use with new data sets. For example, the number of rows or columns required by the formula or macro may not fit the number of new records, generating invalid results.

 

Finally, Excel struggles to scale. It has row limitations, and the lack of sufficient memory and processing power often makes Excel slow when dealing with the colossal data sets that are used in modern data pipelines. Performance degradation and frequent crashes when running a complex set of operations make it problematic for professional workflows.​

 

Prefect vs. Python for data automation

 

Prefect is a workflow management system based on the Python programming language that adds new functionality to make data automation easier. More specifically, Prefect introduces ‘decorators’, which extend the capabilities of existing functions. When building your data pipeline, each task is represented by a function. Applying a decorator to the function adds a layer of individual rules, such as dependencies on other tasks, or conditions for when to execute.

 

While Python is the fundamental coding language, Prefect’s extensions of its functionality allow you to build more sophisticated data automation pipelines more easily.
 

Prefect vs. Airflow for data automation

 

Apache Airflow is a free and open-source platform for creating, scheduling, and monitoring data workflows. Like Prefect, it is based on the Python programming language, but differs in that it only uses standard Python. Airflow provides a structured environment, complete with tools for logging, management, and debugging.

 

In Airflow, you build data pipelines as DAGs (Directed Acyclic Graphs). These are sequences of tasks that produce the intended pipeline’s functionality, requirements, and dependencies. Each DAG requires an ‘execution date’, which must be a unique point in time. This creates the limitation that no two DAGs can run at the same precise moment.

 

Another way in which Airflow differs from Prefect is that the structures it provides are quite rigid, which can lead you to convert your workflow to fit the architecture, rather than vice versa. Conversely, Prefect provides versatile building blocks which enable you to more easily build your pipeline as you conceived it in the first place.

​
 

Types of data processing

 

There are several different types of processing used in data automation depending on the situation and the scale of the data being manipulated.

 

Batch processing

 

In batch processing, multiple records are collected into a group and processed simultaneously. This can be a one-time event or performed on a regular basis, such as daily, weekly, or monthly. For example, when a company processes payroll, every employee’s data is processed simultaneously in one batch, usually on a monthly basis.

 

Online processing

 

‘Online’ means ‘ongoing’, where data from different sources is continuously fed into the system for processing. With this method, precomputed operations are automatically performed on the data before a user request even begins. For example, scanning barcodes in a store to update inventory data. This situation wouldn’t suit batch processing because waiting to update the inventory in one go at the end of the month would result in outdated information being used. Online processing is essentially the opposite of batch processing, and although it isn’t as computationally efficient, it provides up-to-date information whenever needed.

 

Real-time processing

 

Real-time processing is akin to online processing, since it involves automatically updating data as soon as changes are made. However, real-time processing typically deals with smaller amounts of data to avoid delays, and usually uses sensors rather than manual inputs to gather data. For example, real-time processing is used in financial transactions and control systems, in which immediate responses are critically important.

 

Multiprocessing

 

‘Multiprocessing’ is a catch-all term that can have different meanings in data automation. In essence, it’s a setup where multiple CPUs operate on the same dataset simultaneously within the same system. A data set is split up into smaller frames, each of which can be processed by a core working in parallel with the others. Naturally, this is a more efficient way of processing data than waiting for a single core to complete each record sequentially.

 

Distributed processing

 

This is similar to multiprocessing, in that a large dataset is split up into smaller subsets which are stored or processed simultaneously. However, distributed processing uses multiple servers, instead of simply multiple CPU cores within a single machine. Data processing tasks are executed in parallel and the workload is shared across the servers’ bandwidth, which enables the data to be processed and transferred more efficiently. The shorter processing period means this method is generally more cost effective for an enterprise. Significantly, if one of the servers stops working, processing can be redistributed to the others, providing a higher fault tolerance.

​

 

Automated data analysis

 

In addition to practices like ETL, automation can also be applied to data analytics, which is the process of modeling data to draw conclusions and gain business insights. So, rather than reports being manually compiled by data analysts, these can be generated automatically and kept updated in real-time.

 

The role of data analysts will change dramatically in the coming years, and this process will, in part, be shaped by the impact of automation. While this may initially seem threatening to their job security, automation will actually free them from menial tasks, such as poring over or reformatting raw data. Data analysis automation will not displace analysts, but will instead provide them with more time to apply their skills to higher-value problems.

 

​

Automated data analysis examples
 

Adtech

Data automation is a key part of programmatic advertising. Adtech platforms, websites, and apps use real-time data analysis to monitor user behavior. By identifying certain points, called ‘micro-moments’ – where site visitors usually want to buy a product – they are able to serve a relevant ad at just the right time. This is achieved by tracking properties such as search history to identify user visits and demographic data. Users are more likely to click on more relevant ads, so these command a higher bid than a less targeted approach.

 

Detecting bank fraud

Automated data analysis, combined with real-time processing, enables financial institutions to flag potentially fraudulent transactions as they occur. By monitoring payment card usage, algorithms are able to identify abnormal purchases, perhaps in a different geolocation, and automatically add another layer of security to check veracity.

 

Manufacturing workloads

By recording data such as downtime and machine work queues, manufacturing companies are able to analyze the data and better plan workloads, so that equipment and staff operate closer to maximum capacity. Another example is using data for predictive maintenance - i.e. performance preventative maintenance when it is needed and not simply by a routine schedule. This improves efficiency and ultimately helps make the company more profitable.
 

​

Benefits of automated data analysis

 

Automated data analysis bears the same advantages as general data automation, i.e., increased speed, improved data quality, better scalability, better use of talent, and lower cost. However, automating analysis provides an additional benefit: the use of AI (Artificial Intelligence). AI – and, more specifically, ML – provides the means to make a vast number of decisions at a much faster rate than humans. It allows analytics tools to learn from data, identify patterns within data sets, and automate model building.

 

​

How to build a data automation strategy

 

Using data automation effectively can be a substantial challenge for any business, but breaking it down into bite-sized steps can help. The following steps can be used to create a powerful data automation strategy:

 

1. Identify problems – Which aspects of your company’s operations could benefit from automation? Consider the areas where the most manual work is conducted, or where data operations seem to be failing. Create a list of processes which could be improved.

2. Classify data – Sort your data into categories, ready for automation. Consider the sources to which you have access, and their formats. 

3. Prioritize operations – Decide which operations could benefit the most from automation. In general, operations that require manual intervention generally derive more benefit from data automation. 

4. Define transformations – Identify the transformations required to achieve the intended results. This could be something as simple as converting relational databases into CSV files.

5. Execute the operations – Implement the data pipeline.

6. Schedule any updates – Data must be continually updated. Create a schedule for update tasks to be carried out without the need for manual intervention.

Challenges of data automation 
 

• Soaring data volumes: DataOps platforms are processing more data than any time in history. And this is set to increase rapidly. As it is no longer feasible to keep adding physical servers to keep pace with this scale, cloud-based platforms are becoming the default solution, with elastic resources available to support changing workloads.
   

• Multiplying data sources: The number of disparate data sources is also increasing. This compounds complexity, making data pipelines increasingly difficult to build and maintain. Integrating new and different types of data requires exponentially more effort than before. Dedicated data platforms build connectors as fast as new ones come online, i.e., much faster than can reasonably be done in-house. 
   

• Growing data legislation: It’s becoming more and more challenging to comply with regulations for data security and governance. Not only does this make the integration process increasingly complicated, it presents significant business risks. Outsourcing to a trusted platform can keep your company in legal compliance without additional effort from your team.
   

​

The build vs. buy dilemma

The key decision in data automation is usually not ‘Do we automate?’, but rather ‘Do we build in-house, or buy a solution?’ There are three main considerations when making this decision:
 

• Time to value – You may already have data experts in-house who know how to automate data. The relevant factor is how long it would take them to build a working and reliable pipeline that is production-ready. If you can purchase a ready-made solution that can deliver results in weeks, then speed to implementation tips the scale towards purchasing a turnkey data automation platform. 
   

• Accessibility to business users – If your data engineers build a solution which isn’t easy to use, business teams will have to rely on them heavily for support and changes. A much better outcome is for the data automation pipelines to be user friendly enough to avoid the need for data engineers altogether. Established platforms have already completed the usability tests and created an environment in which these teams can access and manipulate their data as easily as possible.
   

• Scale – How large are your data sets and how quickly are they growing? Making sure you have the necessary headcount and expertise to scale your operations in-house soon becomes an expensive, not to mention drawn-out affair. Instead, implement a tried-and-tested, scalable cloud-based tool with flexible resources.
   

​

Why data automation is critical for your business
 

Data automation is a critical part of modern business operations because it’s no longer sustainable to manually process the volumes of data we see today, or can expect in the coming years. Data automation isn’t a nice-to-have – it’s imperative for long-term business success. 

This is where Switchboard comes in. We provide a data unification platform which enables you to automate data without the considerable in-house costs associated with a bespoke solution. Outsourcing data automation can achieve the same, if not better results than managing it in-house, and get your teams closer to the data they need, faster. If you’re dealing with large or complex data sets, and you think it’s time to automate, contact our team today to discuss your needs.