Skip to main content
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:
DatasetDescriptionWhen to Use
awsRaw 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
costsVantage-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:
ColumnTypeDescriptionExample Values
accrued_atdateDate when the cost was accrued2024-01-01
amountdecimalThe cost amount in the specified currency100.18
currencystringCurrency codeUSD, EUR
usage_amountdecimalAmount of usage25
usage_unitstringUnit of measurementrequest, GB, Hours
providerstringCloud provideraws, azure, gcp
billing_account_idstringBilling account ID10001
account_idstringAccount ID10101010
servicestringService nameAmazon S3, Amazon Relational Database Service, Azure Databricks, Log Management
regionstringAWS regionus-east-1, us-west-2
resource_idstringUnique resource identifiera-resource-id
cost_categorystringHigh-level cost categoryAPI Request, Compute Instance, Storage, Data Transfer
cost_sub_categorystringDetailed cost subcategoryDataTransfer-Out-Bytes, DataTransfer-Regional-Bytes
cost_typestringType 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.

​
SQL Operations

Use the following SQL operations to define billing rules:
OperationDescription
UPDATEAdjust an existing cost value, such as applying a percentage discount.
DELETERemove specific line-items from being billed.
INSERTAdd new line-items, like charges or credits.

​
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 support@vantage.sh.

​
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 Basic Structure
INSERT INTO costs (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
1

Start with INSERT INTO

Choose your dataset (costs or aws).
INSERT INTO aws
2

List required columns

Specify all necessary columns.
INSERT INTO aws (lineItem/LineItemType, lineItem/ProductCode, lineItem/UsageAccountId, lineItem/UnblendedCost, lineItem/CurrencyCode, lineItem/LineItemDescription, lineItem/UsageStartDate, lineItem/UsageEndDate)
3

Add VALUES

Provide values for each column.
INSERT INTO aws (lineItem/LineItemType, lineItem/ProductCode, lineItem/UsageAccountId, lineItem/UnblendedCost, lineItem/CurrencyCode, lineItem/LineItemDescription, lineItem/UsageStartDate, lineItem/UsageEndDate)
VALUES ('Fee', 'AWS Support', '123456789012', 125.00, 'USD', 'Monthly Support Fee', '2024-01-01', '2024-01-31')
Common patterns:
  • Add credit: VALUES ('Credit', 'AWS', '123456789012', -100.00, 'USD', 'Billing Credit', '2024-01-01', '2024-01-31')
  • Add processing fee: VALUES ('Fee', 'AWS', '123456789012', 5.00, 'USD', 'Processing Fee', '2024-01-01', '2024-01-31')

​
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.
Creating a custom billing 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'

​
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'
⌘I