Set a variable value in Query Service

Question

Hi, I am looking to query the latest snapshot from one of the profile_snapshot_export_xxx datasets, so the variable/parameter cannot be hardcoded. As a result, I need to do a subquery first, find the snapshot_id from the latest made_current_at date, and then store the value into a variable to be used by the main query. The subquery looks like this: select snapshot_id FROM
  (
  SELECT row_number() OVER (ORDER BY made_current_at desc) `rn`, made_current_at, snapshot_id, parent_id 
    from (
          select history_meta('profile_snapshot_export_xxx') order by made_current_at DESC
         ) tab
  )
where rn = 1  Is it possible to save the snapshot_id into a variable, and reference it in my main query? If so, can somebody share me an example how to make it work? I tried different variations of the syntax but none works. The documentation doesn't really explain much either. Thanks.

abhinavbalooni · Accepted Answer

Hey @akwankl

Not sure if you've had a look at this documentation which is very useful for what you are trying to do.

In this case there is a meta data dataset created and then updated with the snapshot details but is a really neat solution.

https://experienceleague.adobe.com/docs/experience-platform/query/key-concepts/incremental-load.html?lang=en

Let us know if the above helps.

Cheers,

Abhinav

Manoj_Kumar · Answer

Hello @akwankl

SET @my_snapshot_id=select snapshot_id FROM
  (
  SELECT row_number() OVER (ORDER BY made_current_at desc) `rn`, made_current_at, snapshot_id, parent_id 
    from (
          select history_meta('profile_snapshot_export_xxx') order by made_current_at DESC
         ) tab
  )
where rn = 1

Then, try using the @my_snapshot_id variable in your main query.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded