Dataform Pre-operations Variables Not Executing in SQL Query

Question

I am using Dataform to manage SQL pipelines and encountering an issue with pre_operations. Although the variables are declared and set in the pre_operations block, they don't seem to be executing or reflecting in the main SQL query. Here is a snippet of my code:

pre_operations {
  DECLARE start_date_string STRING;
  DECLARE end_date_string STRING;
  DECLARE start_date DATE;
  DECLARE end_date DATE;

  SET start_date = DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY);
  SET end_date = DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY);
  SET start_date_string = FORMAT_DATE('%Y%m%d', start_date);
  SET end_date_string = FORMAT_DATE('%Y%m%d', end_date);
}

-- Main SQL Query follows

In the main query, I use _TABLE_SUFFIX between start_date_string and end_date_string as a condition in the WHERE clause. However, it seems like the start_date_string and end_date_string variables are not being recognized.

Here are my specific questions:

1. Why might the pre_operations variables not be recognized in the main SQL query?
2. Are there any specific considerations or common issues in Dataform that could cause this behavior?
3. Is there an alternative approach or a recommended best practice to ensure pre_operations variables are executed and recognized in the main SQL query?

Any insights or solutions would be greatly appreciated.

Thank you!

In an attempt to resolve this issue, I tried incorporating the SET operations directly into the DECLARE statements as default values. Specifically, I modified the declarations to:

sql

DECLARE start_date DATE DEFAULT DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY);
DECLARE end_date DATE DEFAULT DATE_SUB(CURRENT_DATE("Asia/Tokyo"), INTERVAL 1 DAY);
DECLARE start_date_string STRING DEFAULT FORMAT_DATE('%Y%m%d', start_date);
DECLARE end_date_string STRING DEFAULT FORMAT_DATE('%Y%m%d', end_date);

I was expecting this change to initialize the variables with the correct values at declaration, making them available for use in the main SQL query. However, this approach also did not yield the expected results, as the variables still were not recognized or applied correctly in the main query's WHERE clause.