Unrecognized variable in dataform

[Linda475]

This query looks fine and compiles, but when I run it I get the error Unrecognized name: date_checkpoint and I can not figure out why

config {
type: "incremental",
tags: ["daily"],
uniqueKey: ["date"],
description:"",
bigquery:{
partitionBy:"date",
updatePartitionFilter: 'date >= current_date()-5'
}
}

js {
const test = false;

const startDate = test ? `current_date()-5` : `date_checkpoint`;
const endDate = 'current_date()';

// Daily export tables only
const dateFilter = `(_table_suffix >= cast(${startDate} as string format "YYYYMMDD") and _table_suffix <= cast(${endDate} as string format "YYYYMMDD"))`;
}

select
distinct cast(event_date as date format 'YYYYMMDD') as date
from
`${dataform.projectConfig.vars.ga4Schema}.${dataform.projectConfig.vars.ga4Table}`

${when(incremental(), `where ${dateFilter}`)}
--${when(incremental(), `where (_table_suffix >= cast(date("2024-01-29") as string format "YYYYMMDD") and _table_suffix <= cast(${endDate} as string format "YYYYMMDD"))`)}


pre_operations {
declare date_checkpoint default date ('${dataform.projectConfig.vars.fullRefreshStartDate}');
set date_checkpoint = (
${when(incremental(),
`select max(date)+1 from ${self()}`,
`select date('${dataform.projectConfig.vars.fullRefreshStartDate}')`)}
);

-- delete rows that are about to be added again through the incremental refresh
${
when(incremental(),
`delete from ${self()} where date >= date_checkpoint;`
)
}

create table if not exists ${self()} (
date date
);
}

[ms4446 · Accepted solution]

The "Unrecognized name: date_checkpoint" error primarily arises from the way Dataform handles JavaScript and SQL. In a BigQuery context, variables declared with DECLARE are accessible throughout the script. However, Dataform's JavaScript blocks are evaluated at compile time, meaning SQL variables are not directly accessible within these blocks.

Here's how to work with dynamic variables in Dataform:

• Dynamic Variables within SQL:
  For dynamic variables needed solely within SQL, embed the logic directly within SQL blocks, utilizing Dataform's templating to inject dynamic values as needed.

  -- Example template for dynamic SQL logic 
  ${when(incremental(), `DELETE FROM ${self()} WHERE date >= (SELECT MAX(date) + 1 FROM ${self()})` )} 
  

• Compile-Time Variables or Configuration: For values needed across both JavaScript and SQL, utilize Dataform configurations or compute values within JavaScript blocks for static injection into SQL.

  // JavaScript block to define compile-time logic 
  const isIncremental = incremental(); // Determine at compile time 
  const date_checkpoint = isIncremental ? /* compute relevant date */ : /* some other date or default */; 
  

  -- Using pre-computed values in SQL 
  ${when(isIncremental, `DELETE FROM <span class="math-inline">\{self\(\)\} WHERE date \>\= '</span>{date_checkpoint}'` )} 
  

• Distinguish between compile-time JavaScript evaluation and runtime SQL execution in Dataform.
• For SQL-based dynamic values, calculate them directly within SQL using Dataform's templating capabilities.
• For cross-context values, leverage Dataform configurations or JavaScript for pre-computation and static value injection into SQL.