Happy Business Starts Here

How to identify deletes in Zuora objects when pulling from US Production/Sandbox API?

Highlighted
Senior Tutor

How to identify deletes in Zuora objects when pulling from US Production/Sandbox API?

Team - We have a ETL process which connects to Zuora US Sandbox/Production APIs to pull data into Datawarehouse. Eventhough we get new/modified records, we are unable to get the deletes that happens in Zuora. How do I identify if a delete has happened in any of the Zuora objects (eg: Subscription object)?

 

Thanks,

Sundar

5 REPLIES 5
Highlighted
Zuora Staff

Re: How to identify deletes in Zuora objects when pulling from US Production/Sandbox API?

You can export deleted records via AQuA in Stateful mode. Here's the Knowledge Center link for Export Deleted Data and then to export deleted data in stateless mode, see Extract deleted data in each query.

 

There's a column called "is_deleted" which will have a value of "true" when a record has been deleted. 

 

Regards,

Caleb

Highlighted
Senior Tutor

Re: How to identify deletes in Zuora objects when pulling from US Production/Sandbox API?

Thank you for your quick response. Does this mean that "Is_Deleted" field is present for all the objects in Zuora ? (like Account, Subscription etc)?

Highlighted
Support SME

Re: How to identify deletes in Zuora objects when pulling from US Production/Sandbox API?

The following API objects do not support the exporting of deleted records:

  • PaymentTransactionLog
  • RefundTransactionLog
  • RevenueScheduleItemInvoiceItemAdjustment
  • AccountingCode
  • AccountingPeriod

https://knowledgecenter.zuora.com/Central_Platform/API/AB_Aggregate_Query_API/B_Submit_Query/a_Expor...

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

Using AQuA stateless queries to extract the deleted data is the suggested method.

Highlighted
Senior Tutor

Re: How to identify deletes in Zuora objects when pulling from US Production/Sandbox API?

Thank you for your response. Primarily we connect to Zuora API for pulling data for Datawarehouse purposes. We use Matillion (ETL tool) provided Zuora connector to integrate with Zuora system and fetch records. Eventhought we are able to get the data from Zuora, I stuck on how to get the deleted records using the Matillion Zuora connector. There is no option in the connector to specify the "ForceExport=true". Did you have any of the other customers with this issue and was there any other work around? I can think of hitting Zuora API directly instead of using Matillion's Zuora connector.

Highlighted
Senior Tutor

Re: How to identify deletes in Zuora objects when pulling from US Production/Sandbox API?

Hi Caleb,

Thank you for your response. I want to extract the deleted records in Stateless mode by sending a query to Zuora. Eventhough I followed the steps mentioned in the Knowledge articles, I am not getting the 'is_deleted' column in the output when tried through Postman. I am getting the values for AccountID & ID in the Response.

Can you please guide me incase I am missing something in the API Request.

Zuora Endpoint -  https://rest.apisandbox.zuora.com/v1/action/query/

API Request - Body (Postman):

{
"format": "csv",
"version": "1.0",
"type": "zoql",
"queryString":"select AccountId, Id from Subscription where UpdatedDate>='2020-01-15T00:00:00' and UpdatedDate <'2020-06-16T00:00:00'",
"deleted": {"column": "is_deleted","format": "Boolean","forceExport": "true"}
}