google bigquery - How to Load Large Tables Beyond the 10GB Query Limit in Power BI Premium? - Stack Overflow

I am working with a large dataset stored in Google BigQuery and need to load a table exceeding 10GB into Power BI Premium. The table changes entirely every day, so a full daily refresh of the entire table is required.

Challenges:

1. BigQuery’s 10GB Query Result Limit: The query result size exceeds this limit, preventing me from fetching the entire table in a single query.
2. Power BI Import Mode: The dataset must be fully refreshed daily, ruling out incremental refresh or partial updates as a solution.
3. Daily Full Table Updates: The entire table changes daily, so I cannot rely on partitioning or historical partitions for refresh.

What I’ve Considered:

• Exporting Data to Google Cloud Storage (GCS):

  • Using BigQuery to export the table as Parquet files to GCS and then loading these files into Power BI.
  • Automating the export and refresh process to handle daily updates.

• Chunking Data in BigQuery:

  • Dividing the table into smaller manageable parts (e.g., by rows or IDs) and combining these parts in Power BI.

Constraints:

• I am not interested in approaches to reduce the table size below 10GB (e.g., aggregating data, reducing columns, or filtering). Let’s assume all such optimizations have already been implemented.
• The solution must support scheduled refresh in Power BI Premium.

Question:

What is the best scalable and robust solution for loading large tables exceeding BigQuery’s 10GB limit into Power BI Premium, ensuring the entire table is refreshed daily?

I am looking for practical and proven approaches to:

1. Overcome BigQuery’s query result size limit.
2. Automate the workflow for seamless daily updates.
3. Leverage Power BI Premium’s capabilities for large datasets.

Additional Details:

• Data Source: Google BigQuery
• Table Size: ~50GB
• Power BI Environment: Premium
• Frequency: Daily full-table refresh required

I’d greatly appreciate any insights, examples, or step-by-step approaches to address this challenge.