Big Query ingestion issue

Hi,

We recently integrated Amplitude in our app. We have a lot of older events data which we also wanted to ingest so as to avoid a cold start. We linked out big query and after running the query we got a success feedback and no errors. However, we can’t see any events in our dashboard. Why might this be happening and how do we fix this?

Find more posts tagged with

BigQuery

Accepted answers

eddie.gaona

Aman Kedia Hope all is well!

We were able to look into your setup and you’re using `CURRENT_TIMESTAMP` for the time based import. This is the reason why there isn’t data within your project.

We advise the following within the UI when setting up the import:

Timestamp column required for time based import only, usually server upload time or database insertion time, must be in TIMESTAMP. In other words, provide a valid created_at timestamp column to use time-based import. CURRENT_TIMESTAMP or NOW is not a valid value.

The documentation on time-based imports can be found, here.

All comments

Aman Kedia

Aman Kedia

Saish Redkar Yuanyuan Zhang timothy-permutable wlaubernds hey, can either of you help with this?

Saish Redkar

Hi Aman Kedia

I haven’t used the BigQuery data source, but here’s how I debug my Snowflake data source ingestion

The ingestion jobs log history is a good place to start. If you see a failed job, then you know where to look for in your pipeline. But since your screenshot says the recent jobs have been successful, I would further look into the “General” Tab of your BiqQuery source.
Under the “General Tab”, look at “Daily Events Uploaded by Source (in UTC)”. Sometimes the ingestion jobs would be successful if all the rows specified get ingested. But if your source table itself has less rows then the you’ll see a drop in data under the daily events uploaded chart.
If all the above looks good, then you would want to check your Events under tracking plan. Verify if those newly ingested events appear under “Unexpected”. If they are listed under this, you need to add them to your tracking plan. It would also worthwhile to check if the newly ingested events are part of your blocked event list just in case
Once you make the required changes ( if needed ), you should see your ingested events in your chart dropdowns.

Hope this helps.

Aman Kedia

Hey Saish Redkar thank you for responding. However, I checked all this and nothing seems off.

The tracker in General tab is a flat line. So the ingestion pipeline hasn’t worked at all.
There are a few events under “Unexpected” tab
1. These are all from a different source (app)
2. Can you share some resources so I can read up more on what these unexpected events are?

But as things stand, out big query pipeline isn’t ingesting any data in Amplitude.

Yuanyuan Zhang

Hi Aman Kedia

Is there no events ingested by BigQuery at all? or only very few are ingested?
Are you using full time sync or time-based import?
As Saish said, you should also check your tracking plan. Did you set the schema to reject unplanned events?
Are you under the 2000 event type limit?

Aman Kedia

Hey

No events ingested at all (screenshot attached)
Time based import. The other option isn’t applicable for this use case (we can’t even select it. it’s in disabled state)
How do I check that? And had that been the case, they would come up in “Review Unexpected” section, right?
Yes, only 441 events.

Saish Redkar

Aman Kedia

If the ingestion was successful, these new events would appear as unexpected in your tracking plan. Unless, the “reject unplanned events” action is selected. But it looks like the daily uploaded events by this source is empty.

If you had this setting, you would have received a notification of the event being rejected

One the pipeline side, can you verify that your SQL is using the correct schema and table from BigQuery?

As mentioned earlier, if the source table has empty rows and your connection is querying the table in sync, your logs would always indicate success.

Just linking in the BQ import docs here just in case https://www.docs.developers.amplitude.com/data/sources/bigquery/?h=bigqu

Aman Kedia

Wouldn’t the upload show error if there was a schema issue? Before finishing the set up, it shows a sample of 100 rows which seemed fine (refer to point number 7 in this link you have shared). So I’m unsure about schema being incorrect. And as per this doc, this should have been the final check and ingestion should happen if everything till this step was fine.

Saish Redkar

You might want to raise a support ticket with the Amplitude Team since the most common items in the debugging checklist seems to be checked out. The Support Team will be able to analyze your ingestion logs in a detailed manner and pinpoint what’s the exact cause.

Esther Trapadoux can help convert this into a support ticket.

Aman Kedia

Esther Trapadoux Please help us with this.

Esther Trapadoux

Aman Kedia sorry about the long delay!! Can you actually file the bug request here? That way the team can properly track and follow up with you. 🙏

eddie.gaona

Aman Kedia Hope all is well!

We were able to look into your setup and you’re using `CURRENT_TIMESTAMP` for the time based import. This is the reason why there isn’t data within your project.

We advise the following within the UI when setting up the import:

The documentation on time-based imports can be found, here.

Saish Redkar

Thanks for jumping in eddie.gaona !

Aman Kedia

Hi,

eddie.gaona thanks for helping out! Followed your advise and was able to resolve the issue. We can ingest events now. Thanks a lot!

Quick Links

Unanswered Questions

Guide and Survey Analytics not Working
Hey there I've been playing around for a couple of hours with the guide and analytics which fires properly on the UI but wouldn't give me any analytics. I read that there is another script that should be installed for the analytics part of things. Would anyone be able to guide me towards the potential script or some…
How can I connect my Google Analytics Data with Amplitude
All my data is being collected through Firebase I want to export all this data in Amplitude and keep it synced with GA4, I tried adding data sources as BigQuery and GA4 both but for some reason it only partially imported the data and now its not working, I cant even see connected sources. Any help will be appreciated.
Import data from one amplitude account to another
How do I transfer data from one amplitude account to another? It seems all the community chats about this have completely broken links. Easy to export, no way to import, very frustrating. :(
@amplitude/plugin-web-vitals-browser build fails on CRA v4 (react-scripts@4.0.3)
When using @amplitude/plugin-web-vitals-browser with Create React App v4 (react-scripts@4.0.3), the build fails with: ./node_modules/@amplitude/plugin-web-vitals-browser/node_modules/web-vitals/dist/web-vitals.js<br /><br />SyntaxError:…
Question about Amplitude Export API Data Delay
Hello, I'm using SpringBoot and Kotlin to integrate with the Amplitude Export API. The official Amplitude documentation (https://amplitude.com/docs/apis/analytics/export) states that "data is available to export at a minimum within 2 hours of when the servers received it." However, there's no clear information about the…