Price to implement bigquery on-demand

[raquelnsouza]

I want to implement Bigquery in a company, they have several spreadsheets and we want to migrate this data to a relational database.

However, I have a lot of doubts about the costs.
I'm thinking about using it on demand.

I would have to migrate my current data, and this database will also feed an Appsheet that will insert, add and change data in the dw tables. Additionally, I will connect to Power BI to generate reports that will query the BigQuery data.

The company is small.

I'm worried about implementing it and generating high costs, I've already used the cost calculator and it gave a value of 23 dollars per month.
I would allocate it to the São Paulo region.

I don't believe I would process more than 2TB per month in queries...

However, I'm afraid of overestimating the cost, I wanted tips to estimate it as realistically as possible.

[joelvilca]

Hello @raquelnsouza ,

I have been working with Bigquery for several years and implementing a migration of CSV/TXT/EXCEL files, etc. to Bigquery is easy and cheap depending on the amount of data processed monthly, the good thing is that Bigquery gives us many ways to monitor these costs based on the queries made day by day, before that I would like to show you how Bigquery bills us.

BigQuery Key Costs

You can find more information here : Bigquery princing 

Processing (analysis)	On-demand Typically gives you access to up to 2,000 concurrent slots, shared across all queries in a single project.	Starting at $6.25 per TiB analyzed. The first TiB of each month is free.
Storage	Active Local Storage Based on uncompressed bytes used in tables or table partitions modified in the last 90 days.	Starting at $0.02 Per GiB. The first 10 GiB of each month are free.

• Storage: If you have, for example, 100 GB of data, the monthly storage cost would be approximately $2.30.
• Queries: If you estimate to process up to 2 TB of data per month in queries, the cost would be approximately $13 per month.
• Connections to AppSheet and Power BI: These tools may also incur additional costs, but are typically included in the cost of the queries if they are running on BigQuery.

Cost Optimization

• Partitioning and Clustering: Using partitioning and clustering on your tables can significantly reduce query costs because only the necessary data is processed.
• Testing and Monitoring: Start with a small amount of data and monitor costs for the first month. This will give you a better idea of ​​how to adjust your usage to keep costs under control. Here is a query to track your costs on your project:

SELECT
  user_email USUARIO,
  statement_type TIPO_DECLARACION,
  TIMESTAMP(FORMAT_TIMESTAMP('%F %H:%M:%S', start_time, 'America/Lima')) QUERY_INICIO,
  extract(day from start_time) QUERY_INICIO_DIA_STRING,
  TIMESTAMP(FORMAT_TIMESTAMP('%F %H:%M:%S', end_time, 'America/Lima')) QUERY_FINAZLIZACION,
  (UNIX_MICROS(end_time) - UNIX_MICROS(start_time)) / 1000000.0 AS QUERY_DURACION_SEGUNDOS,
  EXTRACT(HOUR FROM TIMESTAMP(FORMAT_TIMESTAMP('%F %H:%M:%E6S', start_time, 'America/Lima'))) QUERY_INICIO_HORA, 
  query QUERY,

  total_bytes_processed BYTES_PROCESADOS,
  total_bytes_billed BYTES_FACTURADOS,
  (((total_bytes_billed/1024)/1024)/1024) as GIGABYTES_FACTURADOS,
  FORMAT('%.8f',(((total_bytes_billed/1024)/1024)/1024)) as ROUND_GIGABYTES_FACTURADOS,
  ((((total_bytes_billed/1024)/1024)/1024)/1024)*6.25 as QUERY_COSTO,
  FORMAT('%.8f',((((total_bytes_billed/1024)/1024)/1024)/1024)*6.25) as ROUND_QUERY_COSTO,
  ARRAY_TO_STRING(
    ARRAY(
      SELECT DISTINCT CONCAT(table.dataset_id)
      FROM UNNEST(referenced_tables) AS table
    ),
    ', '
  ) AS DATASET_REFERENCIADAS,
  ARRAY_TO_STRING(
    ARRAY(
      SELECT CONCAT(table.dataset_id, '.', table.table_id)
      FROM UNNEST(referenced_tables) AS table
    ),
    ', '
  ) AS TABLAS_REFERENCIADAS,
  ARRAY_LENGTH(
    ARRAY(
      SELECT CONCAT(table.dataset_id, '.', table.table_id)
      FROM UNNEST(referenced_tables) AS table
    )
  ) AS TOTAL_TABLAS_REFERENCIADAS,
  dml_statistics.inserted_row_count FILAS_INSERTADAS,
  dml_statistics.deleted_row_count FILAS_ELIMINADAS,
  dml_statistics.updated_row_count FILAS_ACTUALIZADAS


FROM
  `{project_id}.{region}.INFORMATION_SCHEMA.JOBS`​

Change project_id to your project id and region to your google cloud region.