BigQuery table is empty

[renukab01]

I have around 60 tables in a big query project. I created charts for visualisations in Looker studio. Everything worked well. Suddenly, charts associated with one table shows 'No data'.

I have refreshed the data multiple times. I checked in big query, and found the table is totally empty. How is that possible? The table have values just two days back. All my other tables still have data. How to solve this? Please help.

[dawnberdan]

Hi @renukab01,

Welcome to Google Cloud Community!

You're facing an issue where a particular table in BigQuery has unexpectedly become empty, resulting in your charts in Looker Studio showing "No data." Here’s a guide to help you troubleshoot and resolve the problem:

1. Check the Table Data in Bigquery

• In BigQuery, run the following query to directly check if there is any data. This will return the first 10 rows of data from the table. If this still shows no data, the table may have been cleared or is not being populated correctly.

SELECT * FROM `your_project.your_dataset.your_table` LIMIT 10;

2. Review the Data Loading Process

• Review job history in BigQuery to see if there were any errors during the most recent data load that might have failed.
• You can check BigQuery job logs for errors or failed queries in the Google Cloud Console under BigQuery > Jobs.

3. Audit Log Check

• There might have been manual deletion or truncation of data. In Google Cloud Console, navigate to IAM & Admin > Audit Logs, and search for any actions related to the table. Look for any DELETE or TRUNCATE operations that could explain why the data is missing.

4. Retention Policies or Table Expiration

• BigQuery tables can have expiration settings that automatically delete data after a certain period. Check if the table has an expiration setting applied: -> Navigate to the table in BigQuery, click on the table, and look for any expiration settings under the "Details" tab.  If it's a partitioned table, make sure partitions haven't expired or been dropped.

5. Check Looker Studio Data Source

• In Looker Studio, ensure the data source for that specific table is correctly connected. Try re-authenticating the connection if there was a disconnection. You may refresh the data source in Looker Studio.

6. Time Range and Filters in Looker Studio

• In Looker Studio, verify the time range and filters applied to the chart. For example, if the chart is set to show only data from the past 24 hours and no data has been loaded into the table in that timeframe, it could explain why you're seeing "No data." You may try adjusting the time range.

7. BigQuery Table Schema and Permissions

• Ensure that the schema of the table hasn’t changed unexpectedly. If the schema was altered (e.g., columns dropped), it might prevent Looker Studio from rendering the data correctly.
• Check that the BigQuery table's permissions have not been changed, preventing access to data for Looker Studio. Ensure the service account you are using has access to read the table.

8. Re-import Data

• If all else fails, you may need to re-import the data into the table. This could mean either restoring from a backup (if available) or rerunning the process that populates the table in the first place.

9. Check for Billing Issues or Quotas

• Ensure there are no billing issues or quota limits that might be affecting BigQuery storage. Sometimes, storage or usage limits can lead to issues with data being stored or queried.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.