I am currently using ML.GENERATE_TEXT to generate insights, with my source being a BQ table. However, I have noticed that the output is not consistent each time I run the query. Despite trying to adjust the temperature and top-p parameters, this has not resolved the issue.
I am sharing a sample query with you and would greatly appreciate any insights or suggestions you may have regarding this scenario. Your expertise and assistance would be invaluable in helping me understand and address this inconsistency.
Thank you in advance for your help.
SELECT
ml_generate_text_result
FROM
ML.GENERATE_TEXT(
MODEL \`<>\`,
(
SELECT
CONCAT(‘You are the research agent. Create a three summarizes the client’s case history with Bank over the last 180 days …..etc’, string_agg( concat(Colum1, column2,column3),‘’)) AS prompt
FROM `Table 1` C
LEFT JOIN `Table2` CM ON C.CASE = CM.CASE
LEFT JOIN `Table 3` T ON C.ID = T.CHILD_ID
LEFT JOIN `Table 4` Q
ON C.CASE = Q.CASE AND Q.DT = ‘2025-08-12’
LEFT JOIN `Table 5` E ON C.CASE = E.CASE and ASSIGN IN (“Esc”, “Oper”)
WHERE
C.ID = ‘71’
OR C.ID IN (
SELECT CHILD_ID
FROM (
SELECT CHILD_ID,
ROW_NUMBER() OVER (PARTITION BY CHILD_ID ORDER BY SNAPSHOT_DT DESC) AS rn
FROM \`Table 6\`
WHERE ULT \_ID = '71'
) sub
WHERE rn = 1
)
and
date(substr(C. DT,0,10)) > “2024-12-31”
--ORDER BY C. DT DESC
),
STRUCT(0.5 AS temperature,
1024 AS max_output_tokens,
0.8 AS top_p,
40 AS top_k )
);