Incremental PDT build "skipped due to error in required child"

[jo284]

I have had ongoing issues with incremental PDTs failing to build. I made changes to the sql trigger for my PDTs on 15th November, and after that, initially the tables built successfully (the last successful builds were at "inception"). The sql trigger is such that builds should be triggered once per day. However, since the original change, the tables have failed to build every time, displaying the error:

"Build skipped due to error in required child"

I notice that the trigger value is blank ('-') in Admin > PDT Details. However, when I look at Admin > Datagroups, the trigger values are present and as expected, so perhaps that isn't the problem.

The reference to a "required child" seems to imply some kind of dependency between tables, but my PDTs each only derive from one table in BigQuery (they are SQL-derived). There is a dependency between code in prod and preprod, but my PDTs are failing to build in both, each with the same error.

What might be causing this error and how might I resolve it?

[GavinW]

Just for troubleshooting purposes, what happens if you revert to the old trigger? I have a suspicion this is where your problem lies, so it would be great to eliminate it as a possibility.

[jo284]

We had build errors with the previous trigger too, which was also potentially a costly method, so I've reverted to an interval trigger, set to 24 hours, which we used to have a while back before we started having issues. 

One of the PDTs has built successfully for now (I will see if it builds again in 24 hours, as last time it failed at this point), but the other PDT (which has identical setup but a different data source) has just attempted to build, but I now get the error:

SQL Error in CREATE TABLE as SELECT: 404 Not Found
GET https://bigquery.googleapis.com/bigquery/v2/projects/ons-prices-con-data-preprod/queries/7b86c8e0-1df8-458d-b579-35b7ffc29ee3?location=europe-west2&prettyPrint=false
{
  "code": 404,
  "errors": [
    {
      "domain": "global",
      "message": "Not found: Table ons-prices-con-data-preprod:dataset.staged_master_td_test was not found in location europe-west2",
      "reason": "notFound"
    }
  ]

The odd thing about this is that the table name is not the one specified in the code - the query in the base file only refers to the table 'dataset.staged' (I've replaced the actual dataset name with 'dataset'), not 'dataset.staged_master_td_test' (a table I haven't heard of and that doesn't exist in BigQuery). 

We've had similar errors before which referred to a table path which was not the one specified in the code, i.e. Not found: Table ons-prices-con-data-preprod:looker_validation.FATAL_ERROR_dataset was not found in location europe-west2. We do have a looker_validation dataset, but no part of the code referred to it - neither the derived table nor the staged table that is was derived from were in looker_validation.

[GavinW]

For troubleshooting purposes, if possible, I would change the name of the table in the code and see if that feeds through to the generated SQL.

One other thing - what happens if you go to the explore level and click on 'rebuild derived tables'? 

I think it would make sense to open a case for this with support and try to get their eyes on what's going on.

[jo284]

To update: now, both the PDTs have build failures again. If I try and run a query in the explore for the PDT referred to above, I get the same error, 

"Not found: Table ons-prices-con-data-preprod:dataset.staged_master_td_test was not found in location europe-west2"

so I don't get the option to "rebuild derived tables and run".
For the other PDT, if I try to rebuild manually I get the same error I've had before: 
derived_table <dataset> creation failed: Java::NetStarschemaClouddbJdbc::BQSQLException: Query execution failed: - Not found: Table ons-prices-con-data-preprod:looker_validation.FATAL_ERROR_<dataset> was not found in location europe-west2
I will try changing the name of the table in the code for the first PDT and see if that feeds through.
 

[GavinW]

One more thing to validate is whether your SQL trigger query actually runs? 

[jo284]

What would be the best way to validate this? Since I changed it from a sql_trigger to interval_trigger, in Admin > Datagroups, I see Trigger value: 1733678200 for one PDT (and similar for the other PDT). But when I look in Admin > PDTs > PDT Details, both PDTs have a blank trigger value, '-' (like it was before I changed the trigger). 

I did notice that before I tried the manual rebuilds, it said the last attempted build for at least one of them was the 6th (Friday), when I had the successful build before, implying it didn't attempt to build in any of the 24 hour intervals since then - but then, I'm confused as to why the build showed as successful on Friday, and this morning showed as failed!

[GavinW]

If you go to your PDT page in Admin, you should be able to click on 'persistence rule' and see the query that is being run to decide whether to trigger the table rebuild or not. Run that in SQL Runner against the correct connection and make sure it works.

[jo284]

I think potentially because I have an interval_trigger now rather than a sql_trigger, I don't have links there any more. For these particular PDTs, I just have something like:
Trigger

Last Checked: 2024-12-09 16:43:00 UTC

with no URL to click.
However, we got to the bottom of the mystery test table, where something had gone wrong when creating the view in BigQuery. Now, that PDT has built successfully (will see if it rebuilds after 24 hours!)
Thanks for your help so far.

[jo284]

Unfortunately, the PDT did not rebuild successfully after 24 hours - the last successful build, as well as the last attempt (according to the PDT admin dashboard) was on Monday 9th. Under persistence rule, it says 
Last Checked: 2024-12-13 09:44:40 UTC
and the trigger value, as usual, is blank. The other PDT has the same "fatal error" as before.

I could try escalating to Looker support, but from what I read, only users with the Tech Support Editor IAM role can access the support, so I would have to find out who holds that role and ask if they can raise it.

[GavinW]

I think that's the way to go - without actually seeing your instance it's
difficult to give you good guidance.

[james100]

What could be happening here is that the watermark query that Looker does to determine what date to use to increment the PDT might be going over your "Max Billing Gigabytes" configured for the BigQuery connection. Do you have a maximum configured? How large is your PDT table?

[jo284]

Hi, thanks for your reply. I have now found the cause of the issue - the increment key was set to a variable name from the raw data, rather than the dimension name is was given in the LookML. Changing it to a dimension has resolved the issue.