You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Some context about my use case and why I think this would be helpful:
I'm implementing an incremental table that reads from a federated table (Cloud SQL) using EXTERNAL_QUERY.
To avoid fetching all records from a large table in the external database, I want to use SELECT MAX(...) FROM ${self()} to get a timestamp value that acts as checkpoint.
Because the query passed to EXTERNAL_QUERY cannot be built from a variable (see this SO question), I have to use the workaround of running EXECUTE IMMEDIATE <my_external_query>.
Running EXECUTE IMMEDIATE ... requires an operation.
An operation cannot use incremental(), and during a full-refresh calling SELECT MAX(...) FROM ${self()} is incorrect.
My current workaround to avoid using an operation is to write the EXECUTE IMMEDIATE ... statement in a pre_operations block in an incremental definition file. However, reusing the results of that statement in another file becomes messy.
Thanks!
The text was updated successfully, but these errors were encountered:
federicojasson
changed the title
Operation: find out whether the current execution is a full-refresh
Operation: how to detect whether the current execution is a full-refresh
May 29, 2024
Hello 👋
Is there any way to detect whether a full-refresh is being executed from an operation?
This doesn't work, because
incremental()
is not defined in the context of an operation:config { type: "operation" } ${when(incremental(), ..., ...)}
Some context about my use case and why I think this would be helpful:
EXTERNAL_QUERY
.SELECT MAX(...) FROM ${self()}
to get a timestamp value that acts as checkpoint.EXTERNAL_QUERY
cannot be built from a variable (see this SO question), I have to use the workaround of runningEXECUTE IMMEDIATE <my_external_query>
.EXECUTE IMMEDIATE ...
requires an operation.incremental()
, and during a full-refresh callingSELECT MAX(...) FROM ${self()}
is incorrect.My current workaround to avoid using an operation is to write the
EXECUTE IMMEDIATE ...
statement in apre_operations
block in anincremental
definition file. However, reusing the results of that statement in another file becomes messy.Thanks!
The text was updated successfully, but these errors were encountered: