Skip to content

Backfilling Missed BE Events

Recover events missed from the stream by fetching them from the backend, formatting them as JSON and inserting them in S3, so they land in the same EventDump table. The example is the August 2024 Marketplace Order outage.

1. Identify missing data

Count events per day to find the dates with gaps, then use those dates as filters on the backend DB.

2. Fetch from the backend

Query the relevant backend table for the missing dates. Backend tables are not always 1:1 with the events, which can combine several sources. Ask a backend developer if in doubt.

select
    o.OrderId,
    o.Created,
    o.UserId,
    os.State,
    o.Source,
    o.CountryId,
    o.CurrencyId,
    oi.Amount - coalesce(o.CreditAmount, 0) as TotalAmount,
    o.CreditAmount,
    oi.Amount - coalesce(oi.NetAmount, oi.Amount) as TotalDeduction,
    oi.VatAmount as TotalVatAmount,
    o.PaymentId,
    'Marketplace' as PaymentSource,
    o.PaymentMethodType,
    o.PaymentBrand,
    o.ApplicationId,
    o.AppVersion,
    o.InstanceId
from dbo.vOrder o
left join dbo.OrderState as os on o.State = os.Id
left join dbo.vOrderItem as oi on o.OrderId = oi.OrderId
where o.Created >= '2024-08-07'
    and o.Created <= '2024-08-10';

Download as CSV and upload it to the TMP_STATIC schema (see ad-hoc file upload).

3. Format and keep only missing data

Build JSON objects in the same shape as existing events, and drop orders already present in the stream (the outage may have been intermittent). Use object_construct over the uploaded table, left joined to the stage events with a where on the null side.

Notes:

  • eventId is generated by the backend and not stored. Use md5(to_varchar(object_construct(...eventData...)))
  • MachineName is set to the static string ManualInsert to flag recovered records
  • Keys are camelCase (eventId, eventData, eventCreated, eventName, userId) because the data goes to S3 (Firehose format), not straight to the table
  • EventLoadedByPipeTimestamp is added by Snowpipe, do not create it
  • S3 files are one-row files: remove all newlines. The file has no extension and an explicit name, e.g. MarketplaceMissedOrderEvent2024-08-07

4. Upload to S3

Upload to the rebtel-event-prod/ bucket under the incident date (the first date if it spans several), in a new manual/ folder instead of an hour folder. This keeps manual files distinguishable from Firehose ones.

After a few seconds check dw4_stage.s3."EventDump".

5. Refresh dbt

Refresh the relevant stage models and their children.