> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Bigquery table snapshots should be frequent remediation

### Triage and Remediation

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        To remediate the misconfiguration of not taking frequent snapshots of GCP BigQuery tables, follow these steps using the GCP console:

        1. Open the GCP Console and navigate to the BigQuery section.

        2. Click on the dataset that contains the table you want to take snapshots of.

        3. Click on the table you want to take snapshots of.

        4. In the details panel on the right, click on the "Snapshot" tab.

        5. Click on the "Create Snapshot" button.

        6. Enter a name for the snapshot and click "Create".

        7. Repeat this process periodically to ensure that you have multiple snapshots of your table for redundancy.

        Note: You can also automate the process of taking snapshots by using the BigQuery API or by setting up a scheduled snapshot using Cloud Functions or Cloud Scheduler.

        #
      </Accordion>

      <Accordion title="Using CLI">
        To remediate the misconfiguration "GCP BigQuery Table Snapshots Should Be Taken Frequently For Redundancy", we can use the following steps:

        1. Open the GCP Cloud Shell.

        2. Run the following command to create a snapshot of a BigQuery table:

        ```
        bq cp <project_id>:<dataset_id>.<table_id> <project_id>:<dataset_id>_<table_id>_snapshot_$(date +%Y%m%d)
        ```

        Note: Replace `<project_id>`, `<dataset_id>` and `<table_id>` with the appropriate values.

        3. Schedule a cron job to run this command frequently to take snapshots of the table at regular intervals.

        ```
        crontab -e
        ```

        4. Add the following line to the crontab file to run the snapshot command every day at midnight:

        ```
        0 0 * * * bq cp <project_id>:<dataset_id>.<table_id> <project_id>:<dataset_id>_<table_id>_snapshot_$(date +%Y%m%d)
        ```

        5. Save the crontab file and exit.

        With these steps, we have remediated the misconfiguration by taking frequent snapshots of the BigQuery table for redundancy.
      </Accordion>

      <Accordion title="Using Python">
        To remediate the misconfiguration of not taking frequent snapshots of GCP BigQuery tables, you can use the following steps in Python:

        1. Import the necessary libraries:

        ```
        from google.cloud import bigquery
        from google.cloud.bigquery.table import Table
        ```

        2. Connect to the GCP project and authenticate the user:

        ```
        client = bigquery.Client(project=<project_id>)
        ```

        3. Get the list of all tables in the dataset:

        ```
        dataset_ref = client.dataset(<dataset_name>)
        tables = client.list_tables(dataset_ref)
        ```

        4. For each table, check if a snapshot exists and if not, create a new snapshot:

        ```
        for table in tables:
            table_ref = dataset_ref.table(table.table_id)
            table = Table(table_ref)
            snapshot_name = f"{table.table_id}_snapshot"
            if not table.snapshot(snapshot_name):
                table.create_snapshot(snapshot_name)
        ```

        5. Set up a cron job to run this script on a regular basis to ensure that snapshots are taken frequently for redundancy.

        By following these steps, you can remediate the misconfiguration of not taking frequent snapshots of GCP BigQuery tables and ensure that your data is backed up for redundancy.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        # Existing table (for context only)
        resource "google_bigquery_table" "SOURCE_TABLE" {
          project    = "PROJECT_ID"          # replace with your project ID
          dataset_id = "DATASET_ID"          # replace with your dataset ID
          table_id   = "SOURCE_TABLE_ID"     # replace with your source table ID

          # ... existing schema and settings ...
        }

        # Schedule frequent BigQuery table snapshots (≤ 15 days apart; here: daily)
        resource "google_bigquery_data_transfer_config" "source_table_snapshots" {
          project        = "PROJECT_ID"         # replace with your project ID
          display_name   = "SOURCE_TABLE daily snapshots"
          data_source_id = "scheduled_query"
          location       = "LOCATION"           # e.g. "US", "EU", or region of the dataset

          schedule = "every 24 hours"          # ensures snapshots are taken far more often than every 15 days

          # Service account that runs the query must have BigQuery permissions on the dataset/table
          service_account_name = "SERVICE_ACCOUNT_EMAIL"  # replace with SA email that will own the transfer

          params = {
            # DDL that creates/refreshes a snapshot table from the source table
            query = <<-EOT
              CREATE OR REPLACE SNAPSHOT TABLE
                `PROJECT_ID.DATASET_ID.SOURCE_TABLE_snapshot`
              CLONE
                `PROJECT_ID.DATASET_ID.SOURCE_TABLE_ID`;
            EOT

            destination_table_name_template = "SOURCE_TABLE_snapshot"
            destination_dataset_id          = "DATASET_ID"
            write_disposition               = "WRITE_TRUNCATE"
          }
        }
        ```

        This does not modify the existing table resource but adds a scheduled query that creates (or refreshes) a snapshot table at least once per day, satisfying the “snapshot age ≤ 15 days” requirement going forward.

        Terraform verification: `terraform plan` should show one new `google_bigquery_data_transfer_config.source_table_snapshots` to be created and no other changes to existing BigQuery tables.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>