Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Operation: how to detect whether the current execution is a full-refresh #1752

Open
federicojasson opened this issue May 29, 2024 · 0 comments

Comments

@federicojasson
Copy link

federicojasson commented 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:

  • 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!

@federicojasson 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants