Bulk data extraction from Zuora using AQuA

Query each data source individually

When extracting data from Zuora with AQuA, Zuora recommends that you avoid querying fields from the joined objects of a data source. You should restrict your query to only include the fields from the base object of the data source and the IDs of the parent objects. Once loaded into your external repository, you can use the query language of the external repository to reconstruct the dependent relationships.

For example:

SELECT id,
       rateplancharge.id,
       rateplan.id AS rateplanid,
       tier,
       subscription.status
FROM RatePlanChargeTier
WHERE ...
                

SELECT id,
       rateplancharge.id,
       ...
FROM RatePlanChargeTier
WHERE ...

SELECT id,
       rateplan.id,
       ...
FROM RatePlanCharge
WHERE ...

SELECT id,
       subscription.id,
       ...
FROM RatePlan
WHERE ...
                

Batch multiple queries into a single job

AQuA allows you to submit multiple queries in a single job.  All queries in a job are performed in a single database transaction, against a consistent snapshot of your transactional data.  This ensures referential integrity between data sources even though you are querying each data source individually.

Filter by UpdatedDate only

If you include either of the following column types in a WHERE clause, the query may require a full scan of your data:

• An unindexed column
• A column in a join table

If you include either of these column types in a WHERE clause, the response time is unpredictable.

The UpdatedDate column is indexed in all data sources. By filtering on the UpdatedDate column, Zuora's system can efficiently extract just the rows required by the query without scanning a full table.

You can add needed columns to perform additional filtering after you load data into your external data warehouse.

For example:

SELECT 
...
FROM RatePlanChargeTier
WHERE RatePlanCharge.Name = 'Discount'
AND   (Subscription.Status = 'Active' OR Subscription.Status = 'Cancelled')
AND   RatePlanCharge.EffectiveEndDate > 'today - 2 day'

SELECT 
...
FROM RatePlanChargeTier
WHERE UpdatedDate >= {startTime} and UpdatedDate < {endTime}

Use AQuA stateless queries and track your own high water mark

While AQuA supports stateful queries, customers prefer to use the high water mark approach described in this article. Zuora highly recommends that you use the stateless mode for bulk data extraction. This is the approach that Zuora plans to evolve in future APIs.

• Use AQuA stateless queries. Make sure that you set version to 1.0 in your AQuA request. See Stateless and Stateful Modes for more information.
• Track your own high water mark for data replication.

The high water mark is a timestamp that specifies the latest update time for each extraction. That is, the WHERE clause for every query should be in the following format:

WHERE UpdatedDate >= {last high water mark} and UpdatedDate < {new high water mark}

Each time you run your query job, set the new high water mark to at least 2 hours earlier than the current time. It might take a long time for some transactions to be completed. By offsetting the high water mark, you will be sure to include any updates that have not been completed at the time your query is executed.

For example, if the current time is  2019-05-11T06:30:00, you can issue a query with the following WHERE clause:

WHERE UpdatedDate >= '2019-05-10T04:00:00' AND UpdatedDate < '2019-05-11T04:00:00'

For the initial extract, choose a date earlier than you started using Zuora as the last high water mark, such as 2000-01-01.

Extract deleted data in each query

For each query, make sure that you set the forceExport to true within deleted.

See the following example for a batch of queries in stateless mode:

{
        "format": "csv",
        "version": "1.0",
        "name": "replication-client",
        "useQueryLabels": true,
        "encrypted": "none",
        "queries": [
            {
                "name": "query",
                "query": "select Id from account where UpdatedDate >= '2018-01-03T00:00:00' and UpdatedDate < '2018-01-04T00:00:00'",
                "type": "zoqlexport",
                "apiVersion": "96.0",
                "deleted": {
                    "column": "is_deleted",
                    "format": "Boolean",
                    "forceExport": true
                }
            },
            {
                "name": "query",
                "query": "select Id from subscription where UpdatedDate >= '2018-01-03T00:00:00' and UpdatedDate < '2018-01-04T00:00:00'",
                "type": "zoqlexport",
                "apiVersion": "96.0",
                "deleted": {
                    "column": "is_deleted",
                    "format": "Boolean",
                    "forceExport": true
                }
            }
        ]
}

Query and staging tables

To perform the merge, you need two tables for each Zuora data source in your data warehouse:

• A staging table for loading the incremental data. Each time you load an incremental extraction, you must truncate this table and insert the incremental data into this table.
• A query table containing the full replica of the data source.  Data from the staging table is merged into the query table as part of each load operation.

Data loading steps

To complete a single load operation of a data source, Zuora recommends the following steps:

1. Delete all data from the staging table
2. Load incremental batch into the staging table
3. Delete from the query table any row that has the same Id as a row in the staging table
4. Insert into the query table all the rows whose is_deleted is false in the staging table

Zuora recommends that you script all the steps for all data sources so that you can perform them in a single transaction. In this way, your data warehouse clients always query against a consistent snapshot of the complete data set.

You can keep track of how fresh the data in the data warehouse is by maintaining a data freshness table of data sources and their last update dates.