Limit Facebook Data Retrieved

The facebook API’s (Graph, Marketing and Marketing Insights) are notoriously known for issues with rate limiting. Invantive SQL automatically adapts it’s throughput based upon measurements, rate limiters and errors returned, allowing the data to be retrieved completely over time.

However, often it is meaningful to use incremental copying of facebook data to reduce runtimes. This article explains how to restrict the volume.


The built-in views such as AdAccountAds retrieve all ads per ad account. This can easily take 15 minutes for 5.000 ads. The view definition is:

select dtl.* prefix with 'dtl_'
,      act.* prefix with 'act_' 
from   MyAdAccounts act 
join   AdsByAdAccount( dtl

The query on AdsByAdAccount however has more parameter than solely the ad account ID:

For instance, you can include the parameter updated_since to solely retrieve ads updated in a limited number of days, such as using the following statement:

create or replace table AdAccountAds@inmemorystorage
select *
from   MyAdAccounts act
join   AdsByAdAccount(ad_account_id =>, updated_since=> unix_timestamp(sysdate-30))

This presents you with all changes on the last thirty days.

Using the techniques with for instance ‘union distinct on’ you can then combine the previously loaded ads with the added and updated ads.