SQL Billing Rules

Custom billing rules to modify cost data, apply discounts, and exclude items from MSP billing.

SQL billing rules for MSPs let you modify cost data using SQL statements to apply custom pricing adjustments, discounts, markups, and exclusions based on your customer agreements. You can write SQL queries that run against either raw AWS Cost and Usage Report (CUR) data or processed Vantage cost data.

SQL Billing Rule Datasets

SQL billing rules comprise two different datasets:

Dataset	Description	When to Use
aws	Raw AWS Cost and Usage Report (CUR) data.	• Modify raw AWS CUR data before processing • Access columns of data that’s not available in Vantage Cost Reports • Work with Reserved Instances, Savings Plans
costs	Vantage-processed cost data. Use when inserting or adjusting normalized costs.	• Apply provider-agnostic billing rules

Rules written against aws only affect how data appears in Vantage and do not modify the original CUR file.

AWS Dataset

For the aws dataset, you can query any AWS CUR column except for resource_tag_% columns, which are custom tag keys that vary by account.

See the AWS Data Exports Data Dictionary for column definitions.

Costs Dataset

The Vantage costs dataset contains normalized cost data with the following columns:

Column	Type	Description	Example Values
amount	decimal	The cost amount in the specified currency	100.18
currency	string	Currency code	USD, EUR
usage_amount	decimal	Amount of usage	25
usage_unit	string	Unit of measurement	request, GB, Hours
provider	string	Cloud provider	aws, azure, gcp
billing_account_id	string	Billing account ID	10001
account_id	string	Account ID	10101010
service	string	Service name	Amazon S3, Amazon Relational Database Service, Azure Databricks, Log Management
region	string	AWS region	us-east-1, us-west-2
resource_id	string	Unique resource identifier	a-resource-id
cost_category	string	High-level cost category	API Request, Compute Instance, Storage, Data Transfer
cost_sub_category	string	Detailed cost subcategory	DataTransfer-Out-Bytes, DataTransfer-Regional-Bytes
cost_type	string	Type of cost (same as “Charge Type” in cost reports)	Usage, Discount, Fee, Credit, Tax

When creating SQL billing rules with the costs, you can discover the exact values to use by referencing your existing cost data in Vantage:

• Cost Reports Filters: Use the filters in Cost Reports to see available values for:
  • Service names: Look at the Service filter dropdown
  • Cost types: Use the Charge Type filter dropdown (see the Charge Type filter documentation for a complete list of available values)
  • Cost categories: Check the Category and Subcategory filter dropdowns, which vary by service
  • Regions: View available regions in the Region filter
  • Resource IDs: See actual resource identifiers in your cost data
• Available Columns List: In the SQL query editor, use the Available Columns list on the left to see all queryable columns and copy column names for your queries
• Autocomplete: The SQL editor provides autocomplete suggestions as you type column names and values

The exact values (service names, cost types, etc.) will vary based on your AWS account and the services you use. Always reference your actual cost data in Vantage to get the correct values for your environment.

Azure-Specific Field Mapping

Custom billing rules that use the costs schema run against normalized cost rows as stored after ETL, not against the Cost Report filter names in the table above. For Azure, that means costs.provider_account_id and costs.resource_account_id reflect fields from Microsoft cost data (for example, billing account ID vs subscription ID) and do not always match the “Subscription” and “Resource group” labels in reports or API filters.

Default behavior (most accounts):

• costs.resource_account_id is typically the Azure subscription identifier from cost data.
• costs.provider_account_id is typically the billing account identifier (for example, BillingAccountId from the source export), not the subscription.

Accounts with non-default Azure account-ID mapping:

Some accounts use a Vantage setting that swaps how those two columns are populated. In that case, costs.provider_account_id may hold the subscription ID and costs.resource_account_id may hold another dimension (for example, resource group name) from the export.

What to do when writing rules:

• Compare your condition to actual values on a known cost line (for example, CSV export or API) if you are not sure which column contains the subscription.
• If costs do not change as expected after saving a rule, verify you are filtering on the column that contains the subscription (or billing account) for that tenant.

SQL Operations

Use the following SQL operations to define billing rules:

Operation	Description
UPDATE	Adjust an existing cost value, such as applying a percentage discount.
DELETE	Remove specific line-items from being billed.
INSERT	Add new line-items, like charges or credits (costs dataset only).

Additional SQL Billing Rule Considerations

Date Logic

Do not use date logic in your SQL statement. Instead, use the rule’s Start Date and End Date fields to define the application period. If SQL date conditions conflict with the rule’s configured dates, the rule may not apply as expected.

Multiple Adjustments

For multiple adjustments with different conditions, create separate billing rules for each one. They will be evaluated in sequenced order.

Rule Order

Rules are applied in the order listed. If you need multiple adjustments to the same item (e.g., a discount followed by a fee), create separate rules in sequence.

Limits

Each workspace can define up to 20 billing rules. If you need more, contact [e-mail].

How to Construct SQL Statements

UPDATE Statements

UPDATE Basic Structure

UPDATE [dataset] 
SET [column] = [new_value]
WHERE [condition]

1. Start with UPDATE

   Choose your dataset (costs or aws).

   UPDATE costs
   
2. Specify what to change

   Use SET to define the new value.

   UPDATE costs
   SET costs.amount = costs.amount * 0.90
   
3. Add conditions

   Use WHERE to specify which rows to modify.

   UPDATE costs
   SET costs.amount = costs.amount * 0.90
   WHERE costs.service = 'Virtual Machines'
   

Common patterns:

• Apply discount: SET costs.amount = costs.amount * 0.90 (10% discount)
• Apply markup: SET costs.amount = costs.amount * 1.15 (15% markup)
• Set fixed amount: SET costs.amount = 100.00

DELETE Statements

DELETE Basic Structure

DELETE FROM [dataset]
WHERE [condition]

1. Start with DELETE FROM

   Choose your dataset (costs or aws).

   DELETE FROM costs
   
2. Add conditions

   Use WHERE to specify which rows to remove.

   DELETE FROM costs
   WHERE costs.cost_type = 'SPPDiscount'
   

Common patterns:

• Remove by service: WHERE costs.service LIKE 'AWS Support%'
• Remove by cost type: WHERE costs.cost_type = 'AWS Marketplace'
• Remove by amount: WHERE costs.amount < 0.01
• Remove by multiple conditions: WHERE costs.provider = 'aws' AND costs.service = 'AmazonS3'

INSERT Statements

INSERT statements can only be used with the costs dataset. INSERT is not supported for the aws dataset.

INSERT Basic Structure

INSERT INTO costs (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)

1. Start with INSERT INTO

   Use the costs dataset for INSERT statements.

   INSERT INTO costs
   
2. List required columns

   Specify all necessary columns.

   INSERT INTO costs (provider, service, cost_category, cost_sub_category, cost_type, amount)
   
3. Add VALUES

   Provide values for each column.

   INSERT INTO costs (provider, service, cost_category, cost_sub_category, cost_type, amount)
   VALUES ('aws', 'One Time Charge', 'One Time Charge', 'One Time Charge', 'Usage', 100)
   

Common patterns:

• Add credit: VALUES ('aws', 'Billing Credit', 'Billing Credit', 'Billing Credit', 'Credit', -100)
• Add processing fee: VALUES ('aws', 'Processing Fee', 'Processing Fee', 'Processing Fee', 'Fee', 5)

Create SQL Billing Rules

To create a new SQL billing rule:

1. Create a new custom rule

   • From the top navigation, click Settings.
   • From the left navigation, under Partner Settings, select Billing Rules.
   • Click New Billing Rule, then select Custom.
2. Configure the rule

   Add the following rule details:

   • Title: Give your rule a descriptive name.
   • Start Date/End Date: Define the date range for which the rule should apply.
   • Select whether you want to automatically apply this billing rule to all existing and new managed accounts.
3. Write the rule SQL

   • From the Available Columns list, select either the AWS/aws or Vantage/costs schema.
   • Under Cost Fields, view all available fields. Click a field to copy it.
   • In the SQL editor, write your SQL statement using the selected dataset and columns. The SQL editor supports autocomplete for column names and syntax highlighting.
   • Click Submit to create the rule.

   

Example Rules

Many of the below examples are also documented in the FinOps as Code repository.

Apply a 5% Discount

Apply a 5% discount to all cost line items, excluding credits:

UPDATE aws
SET lineItem/UnblendedCost = aws.lineItem/UnblendedCost * 0.95
WHERE aws.lineItem/LineItemType != 'Credit'

At this time, multiplication is supported, but not division.

Exclude SPP Discounts

Remove Solution Provider Program discounts from billing:

DELETE FROM costs 
WHERE costs.cost_type = 'SPPDiscount'

Apply Service-Specific Markup

Apply a 5% markup to specific services:

UPDATE costs 
SET costs.amount = costs.amount * 1.05
WHERE costs.service IN ('Amazon Relational Database Service', 'AWS Lambda')

Unamortize Reserved Instance Costs

Convert amortized Reserved Instance costs back to usage-based costs:

UPDATE aws 
SET aws.lineItem/LineItemType = 'Usage'
WHERE aws.lineItem/LineItemType IN ('SavingsPlanCoveredUsage', 'DiscountedUsage') 
AND aws.savingsPlan/SavingsPlanARN || aws.reservation/ReservationARN NOT LIKE '%:' || aws.lineItem/UsageAccountId || ':%'

Exclude Marketplace Usage

Remove AWS Marketplace fees:

DELETE FROM costs
WHERE costs.cost_type = 'AWS Marketplace'

Apply Markup to Specific Services

Apply a 10% markup to compute services:

UPDATE costs 
SET costs.amount = costs.amount * 1.10
WHERE costs.cost_category = 'Compute'

Exclude Support Costs

Remove AWS Business support costs from billing:

DELETE FROM costs 
WHERE costs.service = 'AWS Support (Business)'

Apply Markup to Azure Services

Apply a 10% markup to Azure Virtual Machines:

UPDATE costs 
SET costs.amount = costs.amount * 1.10
WHERE costs.provider = 'azure' AND costs.service = 'Virtual Machines'

Exclude Other Provider Items

Remove Temporal Cloud Actions from billing:

DELETE FROM costs 
WHERE costs.provider = 'temporal' AND costs.service = 'Temporal Cloud' AND costs.cost_sub_category = 'Actions'

Exclude Partner Earned Credit (Azure CSP)

Remove Partner Earned Credits from Azure CSP billing:

DELETE FROM costs 
WHERE costs.cost_type = 'PartnerEarnedCredit'

Apply Discount to NCE Licenses (Azure CSP)

Apply a 5% discount to New Commerce Experience (NCE) License purchases for Azure CSP:

UPDATE costs 
SET costs.amount = costs.amount * 0.95
WHERE costs.provider = 'azure_csp' AND costs.service = 'NCE License' AND costs.cost_type = 'Purchase'