[Deprecated] Data model and data flow for Billing

This is an ETL process. The extract and load part are well isolated and only have a dependency to their source/target system. In contrast, the transform part needs to bring information from both the source and the target together. Instead of having one single ETL process, a multistage model with an intermediate persistence will be used. The first stage fetches the data from the source into that intermediate storage. The second stage enriches that data by bringing in missing pieces from the target system. The third stage then ingests the data into the target system.

This architecture is chosen to easily accommodate ingesting the data into different target systems. Additionally, the rather loose coupling simplifies writing and maintaining the code base. We take into account ease of replacing for code that talks to the target system to accommodate replacing the billing/ERP system and reselling of APPUiO Cloud.

Many aspects of this system are expected to change over time. For example, the price for a given product and any discounts are valid only for a defined time window. The system deals with them by assigning time ranges to those data records. The time range during denotes the earliest point and the latest point in time that records becomes valid.

To define records which are valid from the beginning up until a certain time, and records which are valid until further notice, (-)infinity can be used. The database holds whether the lower and upper bounds are inclusive or exclusive. By default the lower range is inclusive, the upper is exclusive [lower,upper). To avoid ambiguous data points, the system must implement constraints and validations to ensure that no overlapping time ranges can be defined.

Identifiers on the source and the target system aren’t necessarily the same. On the source, the billing entity might be identified by the string acme-corp. However, on the target, that same billing entity might be identified by the integer 42. In order to bring those two worlds together, the intermediate storage contains lookup tables holding both the source and target identifiers.

It often happens that the same product gets sold at different prices and discounts can be granted in general or for a given time period. Also, the scope can vary. For example, it’s possible that a billing entity gets a different price but only for a specific instance of a product. A discount might be granted to everybody for a given time period or only to a single billing entity. In order to support these—and even more constellations—the source and target matching gets used.

On the source, all relevant information is available as labels (dimensions) on a time series. The query extracting the data from the source concatenates those dimensions into a source identifier for a product or a discount. After that, finding a matching record in the intermediate storage is a multi step process: First, the full identifier is looked up in the intermediate storage. If no record is found for the full identifier, one segment gets dropped from the end. This lookup process is repeated until a record is found, or only one segment is left. If the last segment is reached without finding an existing record, a new record will be created.

The lookup process–as described–builds a strict hierarchy. For some use cases, this hierarchy, where zones are less specific than tenants, matches product variants. For example, the described hierarchy easily allows specifying different prices per APPUiO Zone. However, for other use cases, the hierarchy requires the same product or discount to be defined multiple times. For example, this is the case if we want to grant a tenant a discount on all zones. With the strict hierarchy, we’d have to create a record of this discount for each zone.

To eliminate the need for duplicate product or discount records, the lookup process also supports wild cards for segments. The enhanced lookup process will also search for records where any segments except the first and last one are replaced by a literal asterisk (in all combinations). Note that the lookup process doesn’t support partial wild cards for individual segments. Either a segment holds the value generated by the query or a single asterisk.

One iteration of the enhanced lookup process differs from the strictly hierarchical process described above as follows: If no record is found for the identifier, the identifier’s intermediate segments are successively replaced by asterisks. First a single segment is replaced by an asterisk, then two, etc., until all intermediate segments have been replaced by asterisks. We always replace segments with asterisks from right to left. A lookup is made for each combination of asterisks. If no record is found for any combination of asterisks, a segment from the end of the identifier is dropped and the full iteration is repeated.

Discounts and products are timed objects.

The system is supposed to handle different products. We assume that the data required to bill one product can be generated with a single PromQL query.

Queries are timed objects.

Some items contain a name or description that’s to be ingested into the target system. Some of those texts are supposed to be dynamic. To enable this, they’re treated as templates and the dynamic parts get inserted wile creating the records on the target system.

A fact can be built from many factors. As a result, it may be hard or impossible to understand how the final fact corresponds to its inputs. Being able to display some of those factors, in isolation, can be immensely helpful when trying to understand the final result. To allow displaying individual factors which contribute to a fact, the system knows the concept of sub queries.

A query can have any number of sub queries. A sub query can only be part of a single main query. Only the main query is billable and sub queries just add context. Sub queries can’t have other sub queries. All sub queries associated with a query are run at the same time as the main query. When creating invoices, sub query facts can be used to build up the description text of a line item.

At the center of the model is the fact. A fact represents a sampled amount of a billed item (such as memory usage for a single namespace). The sampling rate for facts is one hour. For ingestion into the target, facts belonging to the same billed item are aggregated to a single line item. The value (field quantity) of the fact is the observed quantity to be billed.

Records in the "date time" table indicate date and time of facts. The field timestamp is a Unix timestamp, and the values of the other fields correspond to the value of timestamp in UTC. For example, 1640656800 translates to year 2021, month 12, day 28 and hour 3.

The query holds the PromQL query string used to generate facts. It also holds metadata associated with the fact, for example the unit and the description to be shown for the line item on the invoice. The description is a rendered string from a template.

The product holds the amount to be charged per unit. Its source identifier is a segmented match as explained in System idea. This is effectively the price table.

The discount holds a percentage to be discounted. Its source identifier is a segmented match as explained in System idea.

The category allows grouping line items together. Taking the example of APPUiO Cloud, a category is a namespace on a specific cluster. All billed items of that namespace will be grouped together on the resulting invoice.

The query phase may have created dimension records that are incomplete. In the enrichment phase, one or several jobs communicate with the target system to create or fetch any missing information in dimension records. It’s possible that this phase creates new records in the target system. For example, in order to get the target id of a category, this category may need to be created in the target system first.

This phase doesn’t necessarily need to be executed as frequently the query phase. However, the enrichment must be completed successfully at least once before the ingestion phase can be run.

In this phase, the actual invoices and line items are created in the target system. The ingestion is usually executed once after a billing period has concluded. For example, ingestion can be executed at the beginning of a month to generate the invoices for the previous month. Thanks to the enrichment phase, jobs in this phase only need to query the intermediate storage to retrieve all the data which is required to create invoices.

Depending on the sample interval in the intermediate storage, the ingestion phase must aggregate records retrieved from the intermediate storage. For example, the ingestion job may need to sum all records belonging to a single billed item for the requested month.