Inventory Control

Solution Overview

For many businesses, warehouse inventory management is a critical element for their success. In addition to topline metrics like conversions, sales, and revenue, the ability to track and manage the movement of products through your warehouse is equally critical to the health and growth of your business. Inventory management is a necessary component for many functions, including inventory forecasting, demand planning, setting stock and reorder inventory levels, and developing a process to deal with back-orders.

In this example, we’ll walk through how the Decodable data service is used to clean, transform, enrich, and aggregate real-time inventory data. The processed data can then be used to update inventory management systems to help inform business decisions.

Pipeline Architecture

Below we can see a sample of raw inventory event data. For this example, we will calculate summary statistics for inventory adjustments at 5-minute intervals. The adjustments will be limited to those classified as damaged goods, and will include a count of the items that were adjusted and the total quantity that was changed for all items during the interval. By using one or more Decodable pipelines, which are streaming SQL queries that process data, we can transform the raw data into a form that is best suited for how it will be consumed.

‍

For this example, two separate pipelines are used in series, with the output of each one being used as the input for the next. While it is possible to perform all the desired processing in a single large, complex pipeline, it is most often desirable to split them into smaller, more manageable processing steps. This results in pipelines that are easier to test and maintain. Each stage in the sequence of pipelines is used to bring the data closer to its final desired form using SQL queries.

Decodable uses SQL to process data that should feel familiar to anyone who has used relational database systems. The primary differences you’ll notice are that:

• You activate a pipeline to start it, and deactivate a pipeline to stop it
• All pipeline queries specify a source and a sink
• Certain operations, notably JOINs and aggregations, must include windows

Unlike relational databases, all pipelines write their results into an output data stream (or sink). As a result, all pipelines are a single statement in the form INSERT INTO <sink> SELECT ... FROM <source>, where sink and source are streams you’ve defined.

Unnest Data Stream Arrays

For this example, each record of the data stream contains a single inventory_requests field, which contains an array of inventory adjustment requests that need to be unnested (or demultiplexed) into multiple records. In addition, each request contains a line_items array that needs to be unnested as well. To accomplish this, a cross join is performed between the inventory-raw data stream and the results of using the unnest function on the requests, and then again on the line items.

For example, if a given input record contains an array of 3 requests each with 5 inventory line item updates, this pipeline will transform each input record into 15 separate output records for processing by subsequent pipelines.

When the pipeline is running, the effects of unnesting the input records can be seen in the Overview tab which shows real-time data flow statistics. The input metrics will show a given number of records per second, while the output metrics will show a higher number based on how many elements are in the inventory_requests and line_items arrays.

Pipeline: Extract Adjustment Data

‍

After creating a new pipeline and entering the SQL query, clicking the Run Preview button will verify its syntax and then fire up a new executable environment to process the next 10 records coming in from the source stream and display the results. Decodable handles all the heavy lifting on the backend, allowing you to focus on working directly with your data streams to ensure that you are getting the results you need.

Inventory Adjustments Summary

In this final pipeline stage, the data is aggregated into summary statistics that can then be used to inform business decisions related to the number of items that are being damaged. By leveraging the SQL tumble group window function, a count of the items that were adjusted and the total quantity that was changed for all items are calculated across a non-overlapping, continuous window with a fixed duration of 5 minutes.

Pipeline: Summarize Inventory Adjustments

‍

Conclusion

At this point, a sink connection (one that writes a stream to an external system, such as AWS S3, Kafka, Kinesis, Postgres, Pulsar, or Redpanda) can be created to allow the results to be consumed by your own applications and services.

‍

As we can see from this example, a sophisticated business problem can be addressed in a very straight-forward way using Decodable pipelines. It is not necessary to create docker containers, there is no SQL server infrastructure to set up or maintain, all that is needed is a working familiarity with creating the SQL queries themselves.

You can watch demonstrations of several examples on the Decodable YouTube channel.

Additional documentation for all of Decodable’s services is available here.

Please consider joining us on our community Slack.