CD2: context_external_tools workflow_state values question

[agschmid]

Hello,

I'm a little confused with the values in context_external_tools: workflow_state. In CD1 external_tool_activation_dim: workflow_state and the external_tool_activation_dim: privacy_level are 2 separate fields and in CD2, the values for both of those fields are combined into context_external_tools: workflow_state.

It does not make sense to me that the values are combined since they are two totally different things. Can someone help me understand the logic behind this?

context_external_tools
https://data-access-platform-api.s3.eu-central-1.amazonaws.com/index.html#tag/context_external_tools

[PUBLIC]: CD1 to CD2 schema mapping
https://docs.google.com/spreadsheets/d/1kqCXAD9K45L0QeEtbuuMAFp2fW8o0oC8EBzJf58SjrY/edit#gid=1613096...

external_tool_activation_dim: workflow_state > context_external_tools: workflow_state
"CASE WHEN context_external_tools.workflow_state = 'deleted' THEN 'deleted' ELSE 'active' END,
CASE WHEN context_external_tools.workflow_state = 'deleted' THEN NULL ELSE t.workflow_state END"

external_tool_activation_dim: privacy_level > context_external_tools: workflow_state
CASE WHEN workflow_state = 'deleted' THEN NULL ELSE workflow_state END

[agschmid · Accepted solution]

The following needs to be included in the WHERE clause to find active external tools:
WHERE (workflow_state != 'deleted' AND workflow_state IS NOT NULL)