kingtommy dot com

PushNetwork ETL pipeline

Work project · Table, procedure and file names are from the production code and schema

PushNetwork served over half a billion impressions a day, and nobody queried the raw rows. pushnetwork-helper, a Node.js app of cron jobs, ran three separate pipelines: it rolled revenue up for reporting, published delivery configuration to S3, and the ad server loaded that configuration into memory.

Three rows. Row 1, the daily revenue rollup: impressions and revenue from 13 sources go through daily upsert procedures into the tables the dashboard reads. Row 2, configuration files: MariaDB queries are run by pushnetwork-helper and uploaded as about 30 JSON files to an S3 bucket. Row 3, out to delivery: get-ad-content loads those files into memory at startup and every 15 minutes, and decides ad requests from memory.
Three pipelines, one scheduler. Select to enlarge.

1. Daily revenue rollup

At :50 each hour doEveryHour50 imports revenue from 13 sources for today, yesterday and two days ago, one step at a time so dependent data is in place and nothing deadlocks. It then calls usp_insert_stats_daily_placement for those dates and usp_insert_reporting_control for the last four days. Networks revise recent numbers, so each run overwrites the last.

Each procedure takes one date and ends in ON DUPLICATE KEY UPDATE against a unique key, so a re-run overwrites a day instead of doubling it. It first unions the keys from impressions and revenue, so a placement with impressions and no revenue yet still gets a row. Every five minutes the helper also records the newest dateupdated of each output table in health_cron_records, which the Health screen shows against its schedule.

Union the keys from both sides, join, upsert pushnetwork database, usp_insert_stats_daily_placement
INSERT INTO stats_daily_placement
SELECT NULL id, all_placements.date, all_placements.source, ...,
       SUM(IFNULL(i.impressions,0)) AS impressions,
       IFNULL(r.clicks,0) AS clicks, IFNULL(r.revenue,0) AS revenue,
       NOW() datecreated, NOW() dateupdated
FROM (
    -- placements with clicks, conversions, and revenue
    SELECT date, source, owner_id, property, placement
      FROM revenue_daily_placement WHERE date=dt_start
    -- placements with impressions
    UNION DISTINCT
    SELECT date, source, user_id owner_id, site_id property, placement
      FROM impressions_daily i WHERE date=dt_start
) all_placements
LEFT JOIN (...) i ON i.source=all_placements.source AND ...
LEFT JOIN (...) r ON r.source=all_placements.source AND ...
GROUP BY date, source, owner_id, property, placement
-- uniquely keyed on date/source/owner_id/property/placement
ON DUPLICATE KEY UPDATE
    impressions = VALUES(impressions),
    clicks = VALUES(clicks),
    revenue = VALUES(revenue),
    dateupdated = NOW();

2. Configuration files

Every hour at :20, right after the weight optimization, refresh_all_configurations_hourly works through about 30 refresh_s3__* methods. Each runs one query and uploads the result as one JSON file to S3: creative_optimizations.json, revenue_source_weight_by_domain.json, placement_rules.json, blacklist.json and so on.

After each upload the helper reads the file back and compares it with what it sent. On a mismatch it retries up to three times, three seconds apart, then logs the refresh time in helper_app_options.

Read it back and compare pushnetwork-helper/maintenance.js:3279-3299
self.get_data_from_s3(optsToLoadFromS3, res => {
    if (!res || !res.success || !res.data) {
        onComplete(PN.simpleFail(`Failed to load data for verify_s3_data @ "${filename}"`.red, { options }))
        return
    }
    let dataLoaded = JSON.stringify(res.data)
    // Ensure that expected data is present in S3 file
    if (dataToCompare == dataLoaded) {
        onComplete(PN.simpleSuccess(`Successful validation via verify_s3_data`, resOpts))
        return
    }
    onComplete(PN.simpleFail(`Failed to validate via verify_s3_data - data at ${filename}`.red, { ... }))
})

3. Out to delivery

get-ad-content loads every file into memory when it starts and again on a 15-minute cron, with up to three retries per file. A separate five-minute job refreshes users.json. Every loader goes through the ad server's own small S3 reader, get_data_from_s3, which is separate from the helper's method of the same name. An ad request is decided from what is already in memory, so S3 is the whole interface between the two apps, and a new weight reaches delivery within 15 minutes.

Shared S3 read method aimtell-get-ad-content/bin/utils.js:174-218
/**
 * Retrieves data from an S3 bucket. Data is expected to be valid JSON
 */
get_data_from_s3(options, onComplete) {
    const self = this
    // validate options.filename (omitted)
    let bucketName = AWS_PN_BUCKET
    let filename = options.filename
    // Query S3
    const s3 = new AWS.S3({ ..., Bucket: bucketName })
    let params = { Bucket: bucketName, Key: filename }
    s3.getObject(params, function (err, data) {
        if (err) {
            onComplete(self.simpleFail('ERROR @ ...', { options, err }))
            return
        }
        if (data && data.Body) {
            let objectData = JSON.parse(data.Body.toString('utf-8'))
            onComplete(self.simpleSuccess('SUCCESS! @ ...', objectData))
        }
    });
}
Ad service loads config json aimtell-get-ad-content/src/ads/ad-service2.js:183-200
function generateCreativeOptimizations(attempt = 0) {
    if (attempt > 3) return
    Utils.get_data_from_s3({ filename: 'creative_optimizations.json' }, res => {
        if (!res || !res.success) {
            logError({ function: 'generateCreativeOptimizations', message: res.message, attempt });
            // Wait 1 second before retrying
            setTimeout(() => {
                generateCreativeOptimizations(attempt + 1)
            }, 1000);
            return
        }
        creative_optimizations = res.data
    })
}

All demos