# How to Export to BigQuery

> **Note:** Once you [have a data table ready in Data Builder](https://knowledge.powermyanalytics.com/application/tools/data-builder-guide/), you are able to export it to BigQuery. Data exports are available as a paid add-on on the Business, Grow, and Pro plans, and are included with the Custom plan when your negotiated agreement includes data exports. Follow the instructions in this article to get started.

## Grant Required Roles and Permissions

A successful connection to BigQuery requires the user to have one of the following roles:

- BigQuery Admin
- BigQuery Data Editor
- BigQuery Owner

The **BigQuery Job User** role must be added to **Data Editor** and **Owner** roles.

### Add the Job User Role

In the Google account used for exports, go to **IAM and Admin** from the main menu in Google Cloud Console. Select **Permissions** and view by **Principals**.

![IAM and Admin Permissions view in Google Cloud Console](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-bigquery-1.png)

Find the account used for exports and click the pencil icon to **Edit principal**.

![Edit principal pencil icon in Google Cloud Console](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-bigquery-2.png)

Under **Role**, select **BigQuery**, then **BigQuery Job User**.

![Selecting the BigQuery Job User role](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-bigquery-3.png)

## Create a Dataset

A BigQuery dataset must be available before proceeding to create an export. Follow the instructions in [Google's guide to create a dataset](https://cloud.google.com/bigquery/docs/datasets#create-dataset). You will need to select this dataset when setting up your export to BigQuery. You can also open the Google Cloud console directly from the export setup by clicking the plus button next to the Dataset field.

> **Important:** The user creating an export must have [**Write** permissions to the dataset](https://cloud.google.com/bigquery/docs/dataset-access-controls#bq).

## Step 1: Configure Your Data Builder Data Table for Initial Backfill

> **Note:** Data tables exported to BigQuery must include at least one date field. Without a date field, the export will fail to complete successfully.

1. In your PMA hub, go to **Reports > Data Builder** and find your data table. Click the three-dots action menu and select **Edit**.
2. Go to **Available Data Range** and set it to the entire range of data you want to export (up to 2 years for most PMA connectors). To backfill from a specific start date up to yesterday, choose **Custom start to date** and enter a start date.
3. Click **Save Changes** to save the data table.

## Step 2: Create Your Export and Choose the Destination

1. Go to **Exports > BigQuery** in the left sidebar.
2. Click **\+ Create export**. The Create export wizard opens and walks you through four steps: **Destination**, **Data**, **Schedule**, and **Review**.
3. Under **Destination**, select the destination account associated with your BigQuery dataset.

    - If you haven't yet connected your Google account, make sure you are logged into the account associated with your BigQuery dataset, then click the plus button to grant BigQuery permissions to Power My Analytics. Once the account is added, return to the wizard and restart the export setup.
4. For **Dataset**, select the BigQuery dataset you'd like to use. Datasets are grouped by Google Cloud project. To create a new dataset, click the plus button to open the Google Cloud console in a new tab, create the dataset there, then return and check again.
5. Optionally, enter a **Table name prefix**. The prefix starts every table name this export creates; for example, a prefix of "myprefix" produces table names like "myprefix\_orders\_YYYYMMDD" when the data has dates. If you leave the prefix blank, table names are based on the data table name alone. Click **Next**.

## Step 3: Choose the Data to Export

> **Note:** A **Data Table** is a single table in Data Builder. A **Dataset** groups related data tables. Exporting a whole dataset sends every data table in it. (A Data Builder dataset is different from the BigQuery dataset you selected in the previous step.)

1. Under **Data table or dataset**, search for and select either a single data table or an entire dataset from Data Builder.
2. Click **Next**.

## Step 4: Set Your Export Schedule

1. Choose a **Refresh period**: **On demand**, **Monthly**, **Weekly**, **Daily**, or **Hourly**.

    - **On demand:** Your export has no schedule and runs only when you run it manually from the exports list. Leave **Run as soon as you create the export** enabled to run the export immediately after you create it.
    - **Monthly:** Choose a day of the month (1 through 28), the hour, and the timezone.
    - **Weekly:** Choose a day of the week, the hour, and the timezone.
    - **Daily:** Choose the hour and the timezone.
    - **Hourly:** Your export runs every hour; no further settings are needed.
2. For scheduled refresh periods, the **Timezone** is required and determines the local wall-clock time at which your export will run. Your export will run on this schedule until you pause or delete it.
3. Click **Next**.

> **Tip:** For your initial backfill, select **On demand** and leave **Run as soon as you create the export** enabled. You can switch to a scheduled refresh period afterward (see Set Up Rolling Updates below).

## Step 5: Review and Create Your Export

1. Under **Review**, check the Destination, Data, and Schedule summaries. Click **Edit** on any card to make a change.
2. Click **Create export**. If you left **Run as soon as you create the export** enabled, your initial backfill begins immediately.

## Step 6: Verify the Backfill

1. On the Exports to BigQuery page, find your new export. Click the three-dot action menu at the end of the row and select **View logs**. Confirm the export completed successfully.
2. To run the export manually at any time, open the same three-dot menu and select **Run export now**.

## Set Up Rolling Updates

### Configure your Data Builder data table

1. Go back to your data table in Data Builder. Click the three-dots action menu and select **Edit**.
2. Set the **Available Data Range** to a rolling window (e.g., last 30 days for e-commerce to account for returns).
3. Then click **Save Changes**.

### Configure your BigQuery export

1. Return to **Exports > BigQuery**. Open your export's three-dot action menu and select **Edit export**.
2. Under **Schedule**, choose your desired refresh period: Monthly, Weekly, Daily, or Hourly. Select the appropriate day and time, and confirm or update the **Timezone** in which the export should run.
3. Save your changes.

> **Important:** Make sure your data table's date range in Data Builder is at least as long as the refresh period in your export. Failing to do so may result in data missing from your backfill. For example:
>
> \- If your data table's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.
>
> \- If your data table's date range is one week and your export refreshes daily, your export will overwrite the past seven days, every day.

> **Note:** So that each run includes the most recent day, set the export's **Timezone** to the time zone your data sources sync in and schedule it at least 2 hours after their nightly sync. If your export is missing the most recent data, see [Why Is My Export Missing the Most Recent Data?](https://knowledge.powermyanalytics.com/application/exports/why-is-my-export-missing-the-most-recent-data/)

Your export will now run automatically at the scheduled time, keeping your BigQuery dataset up-to-date with the latest data from your PMA data tables.

## Date Partitioning

### How PMA exports to BigQuery

When your export runs, we will first delete any of your export data in BigQuery that falls within the date range of your export. We will then create new tables based on the date range of your export and populate these tables with the export data.

### Exports ranging less than 90 days

Our exports to BigQuery are partitioned by date. Exports that span less than 90 days will be partitioned by day. In Google Cloud console, the daily partitions are visible under the Filter dropdown next to the table name.

![Daily partitions in the Google Cloud console](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-bigquery-4.png)

![Daily partition filter next to the table name](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-bigquery-5.png)

### Exports ranging 90 days or more

Exports spanning 90 days or more will be partitioned by month. In Google Cloud console, monthly partitions are visible under the Filter dropdown next to the table name.

![Monthly partitions in the Google Cloud console](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-bigquery-6.png)

![Monthly partition filter next to the table name](https://knowledge.powermyanalytics.com/application/exports/images/how-to-export-to-bigquery-7.png)

## Export Limitations

If you are backfilling a large amount of your historical data into BigQuery, you may need to take additional steps to make sure your exports are under [BigQuery's limits for streaming inserts](https://cloud.google.com/bigquery/quotas#streaming_inserts).

Your export should run in under 10 minutes. **If the export runs for 30 minutes or more, it has hung and needs to be stopped.** To stop the hung export, open the export's three-dot action menu and select **Pause export**.

### Timeout Errors and "Failed to Fetch" Errors

When exporting large or complex datasets, you may encounter timeout errors or "Failed to Fetch" errors. These errors typically occur when requesting too much data at once, such as a data table with many fields covering a long date range.

Common causes of timeout errors:

- Long date ranges (e.g., a full year of data)
- Large number of fields in one data table
- Excessive use of live fields (indicated by the lightning bolt ⚡ icon in Schema Explorer)

### Understanding Live Fields

Live fields trigger real-time API calls to the source platform each time data is requested. While they provide the most current data, excessive use of live fields can cause timeouts, especially with long date ranges. You can identify live fields in the [Schema Explorer](https://hub.powermyanalytics.com/resources/schema) by looking for the yellow lightning bolt icon (⚡) next to the field label.

### How to Resolve Timeout Errors

To resolve timeout or "Failed to Fetch" errors, try the following approaches:

1. **Split by time period:** Instead of exporting a full year at once, run separate exports for quarterly segments (Q1, Q2, Q3, Q4).
2. **Reduce live fields:** Remove fields marked with the ⚡ icon to eliminate real-time API calls. Use Schema Explorer to identify these fields.
3. **Limit fields per data table:** Group related fields into separate data tables (e.g., one for order metrics, another for product metrics, another for customer data).

For detailed instructions on resolving these issues, see [BigQuery Export Limitations](https://knowledge.powermyanalytics.com/application/troubleshooting/bigquery-export-limitations/).

### How to accommodate large datasets

If your data has caused the export to hang, try segmenting the overall date range into two smaller date ranges. Edit the data table in Data Builder to use the first date range, then go to BigQuery and run your export.

**If your export completes successfully:**

1. Return to Data Builder.
2. Edit your data table and set Available Data Range to the second date range.
3. Go to BigQuery and run your export again.

**If your export is unsuccessful:**

You may need to segment the overall date range into four or more date ranges.

1. Reduce the size of the date range until the export runs successfully, indicating this range is within BigQuery's export limits.
2. Run the export for each date range according to the instructions above.
