Error on upstream_prod fallback for bigquery ML model

[franzoni315]

Hi!

I am using upstream_prod package with a bigquery machine learning model and I am getting an error. The ML model is being referenced on a datamart, but it does not exist in production yet, so the fallback to development is being correctly triggered. However, it looks like the code on ref.sql {% set return_ref = load_relation(parent_ref) %} is returning none for this bigquery ML model, and generates the error:

19:55:52  1 of 1 START sql view model franzoni.dtm_revenue_forecast ................. [RUN]
19:55:52  [dtm_revenue_forecast] mdl_revenue_forecast not found in prod, falling back to default target
19:55:52  1 of 1 ERROR creating sql view model franzoni.dtm_revenue_forecast ........ [ERROR in 0.65s]
19:55:52  
19:55:52  Finished running 1 view model, 3 hooks in 0 hours 0 minutes and 6.14 seconds (6.14s).
19:55:52  
19:55:52  Completed with 1 error and 0 warnings:
19:55:52  
19:55:52  Compilation Error in model dtm_revenue_forecast (models/ml_models/dtm_revenue_forecast.sql)
19:55:52    [dtm_revenue_forecast] upstream_prod couldn't find the specified model:
19:55:52    
19:55:52    DATABASE: xxx
19:55:52    SCHEMA:   ml_models
19:55:52    RELATION: mdl_revenue_forecast
19:55:52    
19:55:52    Check your variable settings in the README or create a GitHub issue for more help.
19:55:52    
19:55:52    > in macro default__ref (macros/ref.sql)
19:55:52    > called by macro ref (macros/ref.sql)
19:55:52    > called by macro ref (macros/ref.sql)
19:55:52    > called by model dtm_revenue_forecast (models/ml_models/dtm_revenue_forecast.sql)
19:55:52  
19:55:52  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

[LewisDavies]

Just to get the obvious question out the way, does xxx.ml_models.mdl_revenue_forecast definitely exist? Does select * from xxx.ml_models.mdl_revenue_forecast return data?

I haven't used BigQuery ML at all - do these models create a table like a typical SQL model? If not I'll have to do some digging to see how they behave. Do you have any other ML models that are giving this error?

[franzoni315]

Yes! It is somewhat like a 'table', it appears in the search panel like an object, and you can do something like a select * from, but a little different. Here is an example from Bigquery ML docs:

SELECT
  *
FROM
  ML.PREDICT(MODEL `mydataset.mymodel`,
    (
    SELECT
      label,
      column1,
      column2
    FROM
      `mydataset.mytable`))

https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#predict_an_outcome