AQUA is_deleted field is always false

[sartoris]

I am using AQUA, downloading data incrementally using stateless queries, keeping track of my own high watermark. I retrieve the deleted records, hoping the 'is_deleted' field is populated appropriately - but the is_deleted field always returns 'false'. Here's the request I send using https://knowledgecenter.zuora.com/Central_Platform/API/AB_Aggregate_Query_API/Bulk_data__extraction_... as a guide:

{ 
  "format": "csv", 
  "version": "1.0", 
  "name": "Test", 
  "encrypted": "none", 
  "useQueryLabels": "true", 
  "dateTimeUtc": "true", 
  "queries": [
  {
    "name":"Subscription",
    "query":"select accountid,id,name,updatedbyid,updateddate,version from Subscription where UpdatedDate >= 2020-06-24T00:00:00",
    "deleted":
    {
      "column":"is_deleted",
      "format":"Boolean",
      "forceExport":true
    }, 
    "apiVersion": "96.0", 
    "type":"zoql"
  }]
}

Here's the response - notice the 'forceExport' property is missing:

{
  "useLastCompletedJobQueries": false,
  "encrypted": "none",
  "batches": [
    {
      "deleted": {
        "column": "is_deleted",
        "format": "Boolean"
      },
      "localizedStatus": "pending",
      "recordCount": 0,
      "apiVersion": "96.0",
      "batchType": "zoql",
      "full": true,
      "batchId": "2c92a0ff72e5fd140172f1351b7050be",
      "status": "pending",
      "name": "Subscription",
      "query": "select accountid,id,name,updatedbyid,updateddate,version from Subscription  where UpdatedDate >= 2020-06-24T00:00:00"
    }
  ],
  "status": "submitted",
  "name": "Test",
  "id": "2c92a0ff72e5fd140172f1351b6250bd",
  "offset": 0,
  "version": "1.0",
  "format": "CSV"
}

The file that is returned does not include records I know have been deleted - even though it includes the is_deleted field - and all the is_deleted field values are 'false'.

What am I doing wrong?

[Yong]

This post might help most likely. 

https://community.zuora.com/t5/API/How-to-export-the-deleted-data-using-AQuA-API/m-p/33181

It's suggested to use stateless AQuA queries to extract the deleted data. 

If you would like to use stateful, you need a partner id and "

Must be set to version 1.1 to export deleted data." 

Sample stateful AQuA requests can be found in https://knowledgecenter.zuora.com/Central_Platform/API/AB_Aggregate_Query_API/BA_Stateless_and_State...

[sundar9987 · Accepted solution]

Hi,

I recently explored how to get the deleted records from Zuora. One of the things I see in the request that you sent is that "type:zoql". As far as I understood and tried, Zuora gives back deleted records only if the query type is ZoqlExport. Can you try changing the parameter as "type:zoqlexport" and the query as "select account.id, id, name, updatedDate from Subscription where ..".

Please let me know how it goes.

Thanks,

Sundar

[sartoris]

Thank you, Sundar. The zoqlexport type did indeed result in the is_deleted field to be populated. Now I need to rewrite my queries to include object names!