Cloud Billing Database Schema - Dowd and Associates

Monthly Report

Table Name: `aws_billing_csv`
Column	Type	Field
`id_`	character varying
`account_`	character varying
`month_`	character varying
`invoice_i_d`	character varying	InvoiceID
`payer_account_id`	character varying	PayerAccountId
`linked_account_id`	character varying	LinkedAccountId
`record_type`	character varying	RecordType
`record_i_d`	character varying	RecordID
`billing_period_start_date`	timestamp with time zone	BillingPeriodStartDate
`billing_period_end_date`	timestamp with time zone	BillingPeriodEndDate
`invoice_date`	timestamp with time zone	InvoiceDate
`payer_account_name`	character varying	PayerAccountName
`linked_account_name`	character varying	LinkedAccountName
`taxation_address`	character varying	TaxationAddress
`payer_p_o_number`	character varying	PayerPONumber
`product_code`	character varying	ProductCode
`product_name`	character varying	ProductName
`seller_of_record`	character varying	SellerOfRecord
`usage_type`	character varying	UsageType
`operation`	character varying	Operation
`rate_id`	character varying	RateId
`item_description`	character varying	ItemDescription
`usage_start_date`	timestamp with time zone	UsageStartDate
`usage_end_date`	timestamp with time zone	UsageEndDate
`usage_quantity`	numeric	UsageQuantity
`blended_rate`	numeric	BlendedRate
`currency_code`	character varying	CurrencyCode
`cost_before_tax`	numeric	CostBeforeTax
`credits`	numeric	Credits
`tax_amount`	numeric	TaxAmount
`tax_type`	character varying	TaxType
`total_cost`	numeric	TotalCost

Detailed Billing Report

Table Name: `aws_billing_detailed_line_items`
Table Name: `aws_billing_detailed_line_items_aws_mp`
Column	Type	Field
`id_`	character varying
`account_`	character varying
`month_`	character varying
`invoice_i_d`	character varying	InvoiceID
`payer_account_id`	character varying	PayerAccountId
`linked_account_id`	character varying	LinkedAccountId
`record_type`	character varying	RecordType
`product_name`	character varying	ProductName
`rate_id`	character varying	RateId
`subscription_id`	character varying	SubscriptionId
`pricing_plan_id`	character varying	PricingPlanId
`usage_type`	character varying	UsageType
`operation`	character varying	Operation
`availability_zone`	character varying	AvailabilityZone
`reserved_instance`	boolean	ReservedInstance
`item_description`	character varying	ItemDescription
`usage_start_date`	timestamp with time zone	UsageStartDate
`usage_end_date`	timestamp with time zone	UsageEndDate
`usage_quantity`	numeric	UsageQuantity
`rate`	numeric	Rate
`cost`	numeric	Cost
`blended_rate`	numeric	BlendedRate
`blended_cost`	numeric	BlendedCost
`un_blended_rate`	numeric	UnBlendedRate
`un_blended_cost`	numeric	UnBlendedCost

Note aws_billing_detailed_line_items_aws_mp is the same as aws_billing_detailed_line_items, but is for AWS Marketplace records.

Detailed Billing Report with Resources and Tags

Table Name: `aws_billing_detailed_line_items_with_resources_and_tags`
Table Name: `aws_billing_detailed_line_items_with_resources_and_tags_aws_mp`
Column	Type	Field
`id_`	character varying
`account_`	character varying
`month_`	character varying
`invoice_i_d`	character varying	InvoiceID
`payer_account_id`	character varying	PayerAccountId
`linked_account_id`	character varying	LinkedAccountId
`record_type`	character varying	RecordType
`record_id`	character varying	RecordId
`product_name`	character varying	ProductName
`rate_id`	character varying	RateId
`subscription_id`	character varying	SubscriptionId
`pricing_plan_id`	character varying	PricingPlanId
`usage_type`	character varying	UsageType
`operation`	character varying	Operation
`availability_zone`	character varying	AvailabilityZone
`reserved_instance`	boolean	ReservedInstance
`item_description`	character varying	ItemDescription
`usage_start_date`	timestamp with time zone	UsageStartDate
`usage_end_date`	timestamp with time zone	UsageEndDate
`usage_quantity`	numeric	UsageQuantity
`rate`	numeric	Rate
`cost`	numeric	Cost
`blended_rate`	numeric	BlendedRate
`blended_cost`	numeric	BlendedCost
`un_blended_rate`	numeric	UnBlendedRate
`un_blended_cost`	numeric	UnBlendedCost
`resource_id`	character varying	ResourceId

Note aws_billing_detailed_line_items_with_resources_and_tags_aws_mp is the same as aws_billing_detailed_line_items_with_resources_and_tags, but is for AWS Marketplace records.

Note If you have cost allocation tags in your report, columns of type character varying will be added for each tag.

Cost Allocation Report

Table Name: `aws_cost_allocation`
Table Name: `aws_cost_allocation_aws_mp`
Column	Type	Field
`id_`	character varying
`account_`	character varying
`month_`	character varying
`invoice_i_d`	character varying	InvoiceID
`payer_account_id`	character varying	PayerAccountId
`linked_account_id`	character varying	LinkedAccountId
`record_type`	character varying	RecordType
`record_i_d`	character varying	RecordID
`billing_period_start_date`	timestamp with time zone	BillingPeriodStartDate
`billing_period_end_date`	timestamp with time zone	BillingPeriodEndDate
`invoice_date`	timestamp with time zone	InvoiceDate
`payer_account_name`	character varying	PayerAccountName
`linked_account_name`	character varying	LinkedAccountName
`taxation_address`	character varying	TaxationAddress
`payer_p_o_number`	character varying	PayerPONumber
`product_code`	character varying	ProductCode
`product_name`	character varying	ProductName
`seller_of_record`	character varying	SellerOfRecord
`usage_type`	character varying	UsageType
`operation`	character varying	Operation
`availability_zone`	character varying	AvailabilityZone
`rate_id`	character varying	RateId
`item_description`	character varying	ItemDescription
`usage_start_date`	timestamp with time zone	UsageStartDate
`usage_end_date`	timestamp with time zone	UsageEndDate
`usage_quantity`	numeric	UsageQuantity
`blended_rate`	numeric	BlendedRate
`currency_code`	character varying	CurrencyCode
`cost_before_tax`	numeric	CostBeforeTax
`credits`	numeric	Credits
`tax_amount`	numeric	TaxAmount
`tax_type`	character varying	TaxType
`total_cost`	numeric	TotalCost

Note aws_cost_allocation_aws_mp is the same as aws_cost_allocation, but is for AWS Marketplace records.

Note If you have cost allocation tags in your report, columns of type character varying will be added for each tag.

Field Descriptions

The following descriptions were copied from CSV Report Fields from the AWS Account Billing 1.0 documentation. The AWS Account Billing Guide supersedes this, but does not have this particular information.

Invoice ID

The 8-digit identifier for the AWS invoice. This value maps to the invoice number on the PDF file for the corresponding month.

Payer Account ID

The 12-digit AWS identifier for the paying account for the report.

Linked Account ID

For consolidated billing accounts, the 12-digit AWS identifier for the linked account.

Record Type

The CSV files can contain several different types of record:

StatementTotal — A summary of all charges in the billing period.
InvoiceTotal — A summary of all charges for an invoice. An invoice total for each invoice is included.
AccountTotal — A summary of all charges for an account in a billing period. For consolidated billing customers, this includes a total for each linked account. This total can include multiple invoices per account. This record type appears only in reports for consolidated billing accounts.
PayerLineItem — An item that includes all usage with the same product, usage type, and operation. Sum all the payer line items to reconcile the amount with your account totals.
LinkedAccountLineItem — For consolidated billing customers only: Linked-account line items are proportionally allocated from the paying-account line items based on linked account usage. Summing all linked-account line items reconciles your statement and invoice totals if you add the CB Rounding Error value.
CBRoundingError — For consolidated billing customers only: This difference results from rounding charges that are allocated from the consolidated billing account invoice to individual linked accounts. If you have opted in to the hourly report, your CB rounding error reflects both rounding errors from consolidated billing as well as from the calculation of hourly charges, i.e., splitting the bill over 720 or 744 hours. The invoice reconciles when you add the rounding errors to the sum of all line items. In the hourly report, this record type appears as "Rounding."

Hourly Report Usage Notes AWS does not provide payer-level line items in the detailed billing (hourly) report, so there is no distinction between the paying account and linked accounts. All usage appears as "LineItem."

Record ID

Unique identifier for each line item. Record IDs are unique for the billing period.

Hourly Report Usage Notes The record IDs are not necessarily unique row identifiers; a record may be split between line items if different rates were applied. Some examples would be with pricing tiers with data transfer or having an instance be billed as a reserved instance or on demand.

Billing Period Start Date

The start of the applicable charge period. All usage charges and recurring fees between the period start and end are included in your monthly invoice.

In most months, the start of the charge period is the first day of the month. If the account is new, or was added to the consolidated bill, or if the price for the listed product changed during the month the report covers, the Start Date reflects the date of the change.

For example, if an AWS account was added to the consolidated bill on December 10 at noon UTC, then the Start Date shown in the December report is 2009-12-10 12:00:00 UTC. If the price for the listed product changes on December 23 at 9:00 a.m. UTC, the report lists a second row for the new price with a Start Date of 2009-12-23 09:00:00 UTC.

Example: 2012/02/01 00:00:00

Note This example is for the CSV file, in the database it has been parsed and is already a timestamp with time zone

Billing Period End Date

The end of the applicable charge period. All usage charges and recurring fees between the period start and end are included in your monthly invoice. If the account is terminated, or is removed from the consolidated bill, or if the price for the listed product changed, the End Date reflects the date of the change.

Example: 2012/02/29 23:59:59

Note This example is for the CSV file, in the database it has been parsed and is already a timestamp with time zone

Invoice Date

The date and time your AWS invoice was issued.

Example: 2013/01/03 08:22:38

Note This example is for the CSV file, in the database it has been parsed and is already a timestamp with time zone

Product Code

Short name of the AWS product.

Product Name

Long name of the AWS product.

Seller of Record

The official seller of services; this will normally be Amazon Web Services, Inc.. Services purchased from AWS Marketplace list a different seller of record.

Detailed Billing Report Usage Notes AWS Marketplace users receive two hourly reports. One report is for AWS Marketplace activity, and contains your Marketplace ID in the name of the report. The other report reflects AWS usage outside AWS Marketplace.

Usage Type

Specifies operational details of the usage line item.

The following example describes Amazon EC2 High-Memory Double Extra Large Instance box usage in the US West (Oregon) region.

Example: USW1-BoxUsage:m2.2xlarge

Operation

Describes the specific usage of the line item. For example, a value of "RunInstances" indicates the operation of an Amazon EC2 instance.

Availability Zone

Specifies Availability Zone in which charged usage occurred.

Hourly Report Usage Notes This column enables you to group instances by Availability Zone. Reserved Instance discounts apply only to on-demand instances within the same Availability Zone as the Reserved Instance(s).

Reserved Instance

Indicates whether the line item was calculated at a Reserved Instance rate. Values are "Y" for Yes and "N" for No. Reserved Instance purchase fees are also flagged with a "Y".

Note These values are for the CSV file, in the database it has been parsed and is already a boolean

Rate ID

Numeric rate ID that maps to the Item description. The value is assigned by AWS for the Item description, and identifies the billing rate for computation purposes.

Subscription ID

Unique identifier for the AWS product described by the line item.

Pricing Plan ID

Numeric ID for the rate listed in the AWS pricing plan for the corresponding service.

Item Description

Description of the type of usage for this line item. This description matches the description in the Account Activity page, including the average price of any usage that is based on a volume pricing tier. Any price value shown is rounded to three decimal places.

Note In the monthly report, you may occasionally encounter inconsistencies between rates listed in the Item Description field and the Unblended Rate field. This is due to the allocation of blended rates that are applied as part of consolidated billing.

Usage Start Date

The start of usage for this line item.

Usage End Date

The end of usage for this line item.

Usage Quantity

Computing resources used, in the units specified by each service. For example, your service may determine pricing by amount of storage, number of requests, or hours of operation. For more information about how usage is calculated, go to http://aws.amazon.com/pricing/.

Note: Some services refer to this field as "Usage Value."

Blended Rate

For consolidated billing accounts, the effective rate for the line item, calculated as an average of the cost of identical Amazon EC2 instances operating in that hour in the same Availability Zone. For a detailed explanation of blended rates and how they are calculated, see Blended Rates for Consolidated Billing Accounts.

Note Only populated if consolidated billing is enabled.

Blended Cost

Cost for the operation listed in the line item based on the blended rate. For some operations, such as BoxUsage for a full hour of an Amazon EC2 instance, the blended rate and the blended cost are the same. For other operations, such as I/O requests or data transfer, this column is the product of the blended rate multiplied by the value in the Usage Quantity column.

Note Only populated if consolidated billing is enabled.

Unblended Rate

Published rate per hour of operation for the usage type specified in the Item Description field.

Note Only populated if consolidated billing is enabled, otherwise Rate is populated.

Unblended Cost

Cost for the operation listed in the item based on the unblended rate. For some operations, such as BoxUsage for a full hour of an Amazon EC2 instance, the unblended rate and the unblended cost are the same. For other operations, such as I/O requests or data transfer, this column is the product of the blended rate multiplied by the value in the Usage Quantity column.

Note Only populated if consolidated billing is enabled, otherwise Cost is populated.

Rate

Published rate per hour of operation for the usage type specified in the Item Description field.

Note Only populated if consolidated billing is not enabled, otherwise Unblended Rate is populated.

Cost

Cost for the operation listed in the line item based on the unblended rate. For some operations, such as BoxUsage for a full hour of an Amazon EC2 instance, the unblended rate and the unblended cost are the same. For other operations, such as I/O requests or data transfer, this column is the product of the unblended rate multiplied by the value in the Usage Quantity column.

Note Only populated if consolidated billing is not enabled, otherwise Unblended Cost is populated.

Currency Code

Currency of the displayed charges. Example: USD

Cost Before Tax

The month-to-date charges, before any applicable taxes.

Hourly Report Usage Notes Taxes are not broken down by line items in hourly reports. Taxes are applied only to monthly line items. In the hourly report, tax is listed using separate line items.

Credits

Amount of credit that applies to the line item.

Hourly Report Usage Notes In an hourly report, credits are line items instead of columns. Credit lines are applied to invoices, not to the entire report. Sort by the Invoice ID to find the credits that apply to an invoice.

Tax Amount

Estimated tax collected by AWS.

Tax Type

Type of tax collected by AWS, such as US sales tax or VAT.

Total Cost

The total charges, after any applicable taxes.

Resource ID

Identifier for the AWS resource as defined by each AWS service.

Cost Allocation Tags

If you have added cost allocation tags to your resources, each tag appears as an additional column in the CSV file.

Payer Account Name

Linked Account Name

Taxation Address

Payer PO Number

Amazon Web Services, the "Powered by Amazon Web Services" logo, AWS, Amazon EC2, and AWS Marketplace are trademarks of Amazon.com, Inc. or its affiliates in the United States and/or other countries.