Spend Data Mismatch Between Source and Destination

Issue

The spend data does not match between the source (Snapchat) and destination (warehouse).

Environment

Resolution

There are two possible reasons you may notice a spend mismatch. They are as follows:

1. The Snapchat API contains the spend in micro-currency and you haven't yet done the conversion.

   From :

   Micro-currency conversion: 1.00 local currency unit = 1000000 Micro-currency.

2. You may need to account for the time zone difference in your date field:

   For example, you are looking at spend data between 2021-04-01 and 2021-04-26 and you have a time zone in Snapchat of UTC + 7.

   The correct way to query for these dates are as follows:

   select sum(spend) from <schema>.<table> where date between '2021-04-01 07:00:00' and '2021-04-27 06:01:00';