BigQuery cost connection

IP RESTRICTIONS

Make sure to allow traffic from one of the Paradime IPs in your firewall depending on the data location selected.

BigQuery setup guide

1. Creating a Service Account User in BigQuery

Follow these steps to create a new service account user for Paradime and grant the required permissions at the Project level:

Go to Google Cloud Console and navigate to IAM & Admin:
- Select your project from the project selector at the top.
- Go to the "IAM & Admin" section.
Create Service Account:
- Click on "Service Accounts".
- Click "Create Service Account".
- Enter the Service Account name (e.g., paradime-radar-user).
- Click "Create and Continue".
Assign Roles:
- Assign the necessary roles to the service account:
  - BigQuery Resource Viewer
  - BigQuery Job User
Complete and Save:
- Click "Done" after assigning the roles.
- The service account will now be created with the assigned permissions.
Generate Key:
- Go to the "Keys" tab and click "Add Key" -> "Create New Key".
- Choose JSON format and save the key file securely. (We will need this later to connect BigQuery to Paradime.)

2. Create the BigQuery Dataset

Make sure to name the BigQuery Dataset as paradime_cost_analytics

To set up your BigQuery Project for Paradime, follow these steps:

Go to Google Cloud Console, navigate to BigQuery and select your project.
Create a Dataset:
- Name: paradime_cost_analytics
- Select the appropriate Dataset region (this will be needed for the Paradime connection setup).
Grant Access:
- Assign the BigQuery User and BigQuery Data Editor roles to the service account user created previously, ensuring these permissions are at the Dataset level.

3. Create and configure a GCS Bucket

Ensure the GCS Bucket region is the same as the Dataset region.

Finally we will need to setup a GCS bucket for Paradime.

Navigate to Cloud Storage and select Buckets
- Click "Create".
- Name your GCS Bucket (e.g., paradime-bucket).
- Select your Bucket region (e.g., US).
- Leave the rest of the Bucket configuration using the default options unless required by your organization.
- Click on "Create" to complete creating your GCS Bucket.
Set lifecycle policy
- Click on the "Lifecycle" tab for your GCS Bucket.
- Select "Add Rule".
- Choose the "Delete Object' action and click on "Continue".
- In the "Set Condition" section, select "Age" and set the configuration to 2 days and click on "Continue".
- Finally click on "Create" to set the lifecycle policy.
Grant the Paradime service account user access to the GCS Bucket
- Click on the "Permission" tab for your GCE Bucket.
- Add the Paradime Service Account user email as a "New Principal'.
- Assign the role Grant Storage Admin to the service account user.
- Click on the "Save" button to complete the setup.

Connecting multiple BigQuery Projects to Paradime?

If you are planning to connect multiple BigQuery Projects to Paradime you will need to for each of your BigQuery projects:

Grant access to the BigQuery Project to the existing service account from the first step in this guide 1. Creating a Service Account User in BigQuery

Go to Google Cloud Console and navigate to IAM & Admin:
- Select your project from the project selector at the top.
- Go to the "IAM & Admin" section and select "IAM"
Assign Roles:
- Select the option "Grant Access".
- Assign the necessary roles to the service account created initially in this guide:
  - BigQuery Resource Viewer
  - BigQuery Job User

Create the BigQuery Dataset in each of the BigQuery Projects and grant the required access, similar to the second step in this guide 2. Create the BigQuery Dataset

Ensure the Paradime Dataset region is the same across all BigQuery projects

Go to Google Cloud Console, navigate to BigQuery and select your project.
Create a Dataset:
- Name: paradime_cost_analytics
- Select the appropriate Dataset region (this will be needed for the Paradime connection setup).
Grant Access:
- Assign the BigQuery User and BigQuery Data Editor roles to the service account user created previously, ensuring these permissions are at the Dataset level.

dbt™️ project setup guide

1. Create a macro to manage query comments

To enable Paradime to enrich your BigQuery queries with additional metadata you will need to create a new dbt™️ macro called get_query_comment.sql in the macros folder of your project.

get_query_comment.sql dbt™️ macro

get_query_comment.sql

{% macro get_query_comment(node, extra = {}) %}
    {%- set comment_dict = extra -%}
    {%- do comment_dict.update(
        app='dbt',
        dbt_version=dbt_version,
        project_name=project_name,
        target_name=target.name,
        target_database=target.database,
        target_schema=target.schema,
        invocation_id=invocation_id
    ) -%}

    {%- if node is not none -%}
        {%- do comment_dict.update(
            node_name=node.name,
            node_alias=node.alias,
            node_package_name=node.package_name,
            node_original_file_path=node.original_file_path,
            node_database=node.database,
            node_schema=node.schema,
            node_id=node.unique_id,
            node_resource_type=node.resource_type,
            node_meta=node.config.meta,
            node_tags=node.tags,
            full_refresh=flags.FULL_REFRESH,
            which=flags.WHICH,
        ) -%}

        {%- if flags.INVOCATION_COMMAND -%}
            {%- do comment_dict.update(
                invocation_command=flags.INVOCATION_COMMAND
            ) -%}
        {%- endif -%}

        {%- if node.resource_type != ('seed') -%} {# Otherwise this throws an error saying 'Seeds cannot depend on other nodes.' #}
            {%- if node.refs is defined -%}
                {% set refs = [] %}
                {% for ref in node.refs %}
                    {%- if dbt_version >= '1.5.0' -%}
                        {%- do refs.append(ref.name) -%}
                    {%- else -%}
                        {%- do refs.append(ref[0]) -%}
                    {%- endif -%}
                {% endfor %}
                {%- do comment_dict.update(
                    node_refs=refs | unique | list
                ) -%}
            {%- endif -%}
        {%- endif -%}
        {%- if node.resource_type == 'model' -%}
            {%- do comment_dict.update(
                materialized=node.config.materialized,
            ) -%}
        {%- endif -%}
    {%- endif -%}

--- only if using dbt cloud

    {%- if env_var('DBT_CLOUD_PROJECT_ID', False) -%}
    {%- do comment_dict.update(
        dbt_cloud_project_id=env_var('DBT_CLOUD_PROJECT_ID')
    ) -%}
    {%- endif -%}

    {%- if env_var('DBT_CLOUD_JOB_ID', False) -%}
    {%- do comment_dict.update(
        dbt_cloud_job_id=env_var('DBT_CLOUD_JOB_ID')
    ) -%}
    {%- endif -%}

    {%- if env_var('DBT_CLOUD_RUN_ID', False) -%}
    {%- do comment_dict.update(
        dbt_cloud_run_id=env_var('DBT_CLOUD_RUN_ID')
    ) -%}
    {%- endif -%}

    {%- if env_var('DBT_CLOUD_RUN_REASON_CATEGORY', False) -%}
    {%- do comment_dict.update(
        dbt_cloud_run_reason_category=env_var('DBT_CLOUD_RUN_REASON_CATEGORY')
    ) -%}
    {%- endif -%}

    {%- if env_var('DBT_CLOUD_RUN_REASON', False) -%}
    {%- do comment_dict.update(
        dbt_cloud_run_reason=env_var('DBT_CLOUD_RUN_REASON')
    ) -%}
    {%- endif -%}

-- only if using bolt scheduler
    {%- if env_var('PARADIME_SCHEDULE_NAME', False) -%}
    {%- do comment_dict.update(
        paradime_schedule_name=env_var('PARADIME_SCHEDULE_NAME')
    ) -%}
    {%- endif -%}

    {%- if env_var('PARADIME_SCHEDULE_RUN_ID', False) -%}
    {%- do comment_dict.update(
        paradime_schedule_run_id=env_var('PARADIME_SCHEDULE_RUN_ID')
    ) -%}
    {%- endif -%}

    {%- if env_var('PARADIME_SCHEDULE_RUN_START_DTTM', False) -%}
    {%- do comment_dict.update(
        paradime_schedule_run_start_dttm=env_var('PARADIME_SCHEDULE_RUN_START_DTTM')
    ) -%}
    {%- endif -%}

    {%- if env_var('PARADIME_SCHEDULE_TRIGGER', False) -%}
    {%- do comment_dict.update(
        paradime_schedule_trigger=env_var('PARADIME_SCHEDULE_TRIGGER')
    ) -%}
    {%- endif -%}

    {%- if env_var('PARADIME_SCHEDULE_GIT_SHA', False) -%}
    {%- do comment_dict.update(
        paradime_schedule_git_sha=env_var('PARADIME_SCHEDULE_GIT_SHA')
    ) -%}
    {%- endif -%}

    {{ return(tojson(comment_dict)) }}
{% endmacro %}

2. Update your dbt_project.yml file

This step ensures that with each dbt™️ run, the query comment is appended to the query running in BigQuery.

dbt_project.yml

query-comment:
  comment: '{{ get_query_comment(node) }}'
  append: true

Configured Cost connection in Paradime

From the account setting page of your Paradime workspace, add your new BigQuert Cost connection using:

Your BigQuery Service Account JSON
The BigQuery Project IDs
The Dataset Location for the Dataset used by Paradime
The GCS Bucket name used by Paradime

If connecting multiple project, makre sure to use commas and no spaces between each BigQuery Project ID.

✅ dev-project,staging-project,production-project

❌ dev-project, staging-project, production-project

PreviousCost connection NextSnowflake cost connection

Last updated 16 days ago