Using the Cost transformations

jkaser_EA · November 13, 2024, 9:57pm

Is anyone using these successfully when pulling the report generated from the S3 bucket? When doing so get failures when compiling the dbt for the cost_usage_table. I am syncing all of the required tables, but the models do not run for 27 of 34 models.

jonathan · November 14, 2024, 2:38pm

Hi @jkaser_EA! Are you seeing any specific errors when trying to run the transformation? Any SQL-related issues? Also, did both syncs (S3-CUR and the AWS tables) complete successfully, or were there any errors?

jkaser_EA · November 15, 2024, 5:36pm

When syncing s3-cur, I have the report coming out as csv, should it not be a csv. This is what the s3-cur sync looks like in the logs.

Starting sync for: s3-cur (cloudquery/s3@v1.3.25) → [postgresql (cloudquery/postgresql@v8.6.3)]

/ Syncing resources… (0/-, 0 resources/hr) [0s]

Syncing resources… (0/-, 0 resources/hr) [0s]

\ Syncing resources… (0/-, 0 resources/hr) [0s]

| Syncing resources… (0/-, 0 resources/hr) [0s]

/ Syncing resources… (0/-, 0 resources/hr) [0s]

Syncing resources… (0/-, 0 resources/hr) [0s]

\ Syncing resources… (0/-, 0 resources/hr) [0s]

| Syncing resources… (0/-, 0 resources/hr) [0s]

/ Syncing resources… (0/-, 0 resources/hr) [0s]

Syncing resources… (0/-, 0 resources/hr) [1s]
Syncing resources… (0/-, 0 resources/hr) [1s]

jkaser_EA · November 15, 2024, 5:48pm

Needs to be parquet. I will need to update that.

jkaser_EA · November 18, 2024, 8:49pm

ran the sync now with the correct format of file in the S3 bucket but still getting errors when running the compile for DBT

sync logs:

Starting sync for: s3-cur (cloudquery/s3@v1.3.25) -> [postgresql (cloudquery/postgresql@v8.6.3)]

/ Syncing resources... (0/-, 0 resources/hr) [0s] 
                                                  

- Syncing resources... (0/-, 0 resources/hr) [0s] 
                                                  

\ Syncing resources... (0/-, 0 resources/hr) [0s] 
                                                  

| Syncing resources... (3000/-, 7481 resources/s) [0s] 
                                                       

/ Syncing resources... (3000/-, 5990 resources/s) [0s] 
                                                       

- Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

\ Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

| Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

/ Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

- Syncing resources... (13000/-, 5990 resources/s) [1s] 
                                                        

\ Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

| Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

/ Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

- Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

\ Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

| Syncing resources... (16000/-, 11335 resources/s) [1s] 
                                                         

/ Syncing resources... (17483/-, 10048 resources/s) [1s] 
                                                         

- Syncing resources... (17483/-, 10048 resources/s) [1s] 
                                                         

\ Syncing resources... (17483/-, 10048 resources/s) [1s] 
                                                         

| Syncing resources... (17483/-, 10048 resources/s) [2s] 
                                                         

/ Syncing resources... (17483/-, 10048 resources/s) [2s] 
                                                         

- Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

\ Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

| Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

/ Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

- Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

- Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         
Sync completed successfully. Resources: 17483, Errors: 0, Warnings: 0, Time: 3s

dbt compile message:
Starting sync for: s3-cur (cloudquery/s3@v1.3.25) -> [postgresql (cloudquery/postgresql@v8.6.3)]

/ Syncing resources... (0/-, 0 resources/hr) [0s] 
                                                  

- Syncing resources... (0/-, 0 resources/hr) [0s] 
                                                  

\ Syncing resources... (0/-, 0 resources/hr) [0s] 
                                                  

| Syncing resources... (3000/-, 7481 resources/s) [0s] 
                                                       

/ Syncing resources... (3000/-, 5990 resources/s) [0s] 
                                                       

- Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

\ Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

| Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

/ Syncing resources... (13000/-, 5990 resources/s) [0s] 
                                                        

- Syncing resources... (13000/-, 5990 resources/s) [1s] 
                                                        

\ Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

| Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

/ Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

- Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

\ Syncing resources... (13000/-, 11335 resources/s) [1s] 
                                                         

| Syncing resources... (16000/-, 11335 resources/s) [1s] 
                                                         

/ Syncing resources... (17483/-, 10048 resources/s) [1s] 
                                                         

- Syncing resources... (17483/-, 10048 resources/s) [1s] 
                                                         

\ Syncing resources... (17483/-, 10048 resources/s) [1s] 
                                                         

| Syncing resources... (17483/-, 10048 resources/s) [2s] 
                                                         

/ Syncing resources... (17483/-, 10048 resources/s) [2s] 
                                                         

- Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

\ Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

| Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

/ Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

- Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         

- Syncing resources... (17483/-, 7536 resources/s) [2s] 
                                                         
Sync completed successfully. Resources: 17483, Errors: 0, Warnings: 0, Time: 3s

jkaser_EA · December 4, 2024, 5:58pm

The sync completes with out error, but when dbt runs there are errors.

[MainThread]: Done. PASS=5 WARN=0 ERROR=22 SKIP=7 TOTAL=34

On all those with errors this is the message:
relation “cost_usage_table” does not exist

jonathan · December 4, 2024, 7:32pm

when running the transformation you need to specify the cost and usage report table name as it is saved in your database (usually a combination of its name and billing period)

dbt run --vars '{"cost_usage_table": "<cost_and_usage_report>"}'

for example:

dbt run --vars '{"cost_usage_table": "my_cur_data_BILLING_PERIOD=2024_11"}'

jkaser_EA · December 6, 2024, 4:23pm

Two different things. Got this error first: Postgres adapter: Postgres error: syntax error at or near “=”
LINE 16: testreports_costreporttest_data_BILLING_PERIOD=2024_11

updated the table name to not have “=” and now has “_”.

After runnig with that table name got a new error:
Postgres adapter: Postgres error: relation “testreports_costreporttest_data_billing_period_2024_11” does not exist

LINE 10: from testreports_costreporttest_data_BILLING_PERIOD_2024_11

jkaser_EA · December 12, 2024, 7:37pm

Jonathan, there is an issue where the dbt_project.yml calling the table table does not pass the “” in that is needed since there is an = character in the name. Can prove this my going to the sql for say aws_cost_by__account and setting the table name in the state include the “” in the name.

select

bill_payer_account_id as account_id,

sum(line_item_unblended_cost) as cost

from “reports_costreport_data_BILLING_PERIOD=2024_12”

where line_item_line_item_type = ‘Usage’

group by bill_payer_account_id

having sum(line_item_unblended_cost) > 0

from dbt.log for the __account view creation:
e[0m17:04:30.569344 [debug] [Thread-1 ]: On model.aws_cost.aws_cost__by_account: /* {“app”: “dbt”, “dbt_version”: “1.9.0”, “profile_name”: “aws_cost”, “target_name”: “dev”, “node_id”: “model.aws_cost.aws_cost__by_account”} */

create view “CloudQuery”.“public”.“aws_cost__by_account__dbt_tmp”

as (
select
bill_payer_account_id as account_id,
sum(line_item_unblended_cost) as cost
from “reports_costreport_data_BILLING_PERIOD=2024_12”
where line_item_line_item_type = ‘Usage’
group by bill_payer_account_id
having sum(line_item_unblended_cost) > 0

– SELECT line_item_usage_account_id, SUM(line_item_unblended_cost) as cost
– FROM reports_costreport_data_BILLING_PERIOD=2024_12
– GROUP BY line_item_usage_account_id
– HAVING SUM(line_item_unblended_cost) > 0
– ORDER BY SUM(line_item_unblended_cost) DESC
);

by_container_usuage view creation:
create view “CloudQuery”.“public”.“aws_cost__by_container_usage__dbt_tmp”

as (
SELECT
bill_payer_account_id,
line_item_usage_account_id,
line_item_resource_id,
line_item_operation,
line_item_product_code,
SUM(CAST(line_item_usage_amount AS DOUBLE PRECISION)) AS sum_line_item_usage_amount,
SUM(line_item_unblended_cost) sum_line_item_unblended_cost
FROM
reports_costreport_data_BILLING_PERIOD=2024_12
WHERE
line_item_product_code IN (‘AmazonECS’,‘AmazonEKS’)
AND line_item_line_item_type IN (‘DiscountedUsage’, ‘Usage’)
GROUP BY
bill_payer_account_id,
line_item_usage_account_id,
line_item_resource_id,
line_item_operation,
line_it

jonathan · December 12, 2024, 8:52pm

Thanks for the extra details, our policies are open source so you can take a look at the code itself, DBT injects the table name into the query, it should handle = and any other characters (by wrapping the name with “”).

github.com

cloudquery/policies/blob/main/transformations/aws/cost/models/general/aws_cost__by_account.sql

select
bill_payer_account_id as account_id,
sum(line_item_unblended_cost) as cost
from {{ var('cost_usage_table') }}
where line_item_line_item_type = 'Usage'
group by bill_payer_account_id
having sum(line_item_unblended_cost) > 0

-- SELECT line_item_usage_account_id, SUM(line_item_unblended_cost) as cost
-- FROM {{ var('cost_usage_table') }}
-- GROUP BY line_item_usage_account_id
-- HAVING SUM(line_item_unblended_cost) > 0
-- ORDER BY SUM(line_item_unblended_cost) DESC

I am trying to replicate your issue now, will update once I fix it

Topic		Replies	Views
Failed to sync AWS pricing table to PostgreSQL database CloudQuery Plugins	1	2	March 21, 2024
Struggling with DBT compliance transformations on EC2 for visualization tools CloudQuery Plugins	4	6	July 16, 2024
CloudQuery sync issues with GCP projects and empty tables CloudQuery Plugins	21	3	September 22, 2023
CloudQuery policy execution options after syncing to CSV files CloudQuery Plugins	4	0	September 14, 2023
Help with incremental syncing of CloudTrail management and data events in CloudQuery CloudQuery Plugins	13	11	August 28, 2024

Using the Cost transformations

Related topics