Dynamic BigQuery in dbt

Help
,
1

Hi there !

I’m trying to use dynamic query from BigQuery in dbt but it’s not working. I have tried to convert it with Jinja thanks to ChatGPT but it’s not working anymore. Do you know how can I run the following query in a dbt model ?

DECLARE dynamic_sql STRING;
SET dynamic_sql = (
  SELECT STRING_AGG(DISTINCT CONCAT(' MAX(CASE WHEN field_name = "', field_name, '" THEN field_value END) AS `',
         REGEXP_REPLACE(field_name, '[^a-zA-Z0-9_]', '_')), '`, ')
  FROM jira.all_latest_fields
);

SET dynamic_sql = CONCAT('SELECT issue_id, ', dynamic_sql, '` FROM jira.all_latest_fields GROUP BY issue_id');

EXECUTE IMMEDIATE dynamic_sql;

Many thanks for your time !

2

I think you should contact experts in custom software development and who have experience with Big Query https://tech-stack.com/services/big-data-and-analytics