BigQuery warehouse refresh and merge behavior for app revenue

What this BigQuery refresh behavior is for

This page explains how Netice approaches managed BigQuery output for app revenue data. It is written for data engineers, analytics engineers, finance operations teams and technical buyers who need to know what happens when a first run, recurring refresh, rerun or backfill updates a BigQuery app revenue table.

The main daily app-sales table is app_sales_daily. It is produced by the Daily App Sales workflow and is designed for operational analytics over Apple App Store and Google Play daily app-sales reporting. It is not a raw report landing table and not a monthly finance table.

The monthly finance table, where Finance Unified is configured, is finance_unified. It uses finance-period semantics rather than daily revenue_date semantics. These two BigQuery outputs should remain separate in destination naming, refresh behavior and downstream models.

Managed BigQuery table model

A managed BigQuery table is the table Netice keeps current for a configured app revenue task. The table should be stable enough for downstream SQL, BI models and scheduled dashboards to depend on it, while still allowing recent provider windows to be refreshed when data arrives late or changes.

Daily app-sales BigQuery refresh

The daily app-sales BigQuery path is designed around app_sales_daily. It receives enriched daily rows from app store sales-style reporting. The table includes source and report-family context such as source, platform, source_kind and source_report, so Apple and Google rows can coexist without losing their source meaning.

A refresh does not need to mean “append everything again.” Netice stages candidate rows, checks the candidate output and refreshes the selected window in the managed table. This is important because app stores can publish late data, update recent reports or produce empty windows that should not automatically erase existing data.

What gets staged before a refresh

In a safe warehouse workflow, candidate rows are prepared before the final managed table is updated. That staging step gives Netice a place to validate candidate data shape, source context, type compatibility and duplicate behavior.

BigQuery setup for daily app sales can require a GCS staging bucket. That staging bucket is part of the BigQuery loading process. It is not the same thing as a customer-facing GCS file export. Public documentation should therefore avoid showing staging object names, job IDs or internal table names.

Window-scoped daily refresh

Daily App Sales uses daily reporting windows. A first run, backfill or recurring refresh may target a selected date range. The output should be interpreted as a refresh of that selected app-sales window, not as a blind full-table rewrite.

The current validation evidence supports a recurring daily refresh model for recent completed days, with a default recent-window behavior and a configured cap. That is useful because provider data can arrive late or stabilize after the first time a day is processed.

Empty candidate and provider-late handling

Empty candidate data is a special case. A provider can have no rows for a window because data is late, unavailable, filtered out, not generated yet or affected by source access. A safe BigQuery refresh should not automatically interpret an empty candidate as permission to delete the existing target window.

The public wording should be simple: Netice uses guards so an empty candidate window is not treated as a normal replacement of existing warehouse data. This is not a customer-visible restore guarantee or an SLA; it is a safety boundary for managed output behavior.

Duplicate and schema checks

Managed warehouse output needs quality checks. BigQuery app revenue rows are not useful if duplicate candidate rows, incompatible table types or invalid casts silently flow into the final table.

Netice’s public article should describe the high-level controls: staged candidate validation, duplicate checks, typed table behavior and schema/type compatibility checks. It should not publish internal SQL scripts or implementation-specific merge logic.

Source access vs destination access

A BigQuery refresh can fail because of source access, destination access, provider availability or warehouse schema issues. These are different categories and should not be collapsed into “the run failed.”

Google Play or Apple App Store source access lets Netice read provider reports. BigQuery destination access lets Netice write the customer-owned warehouse table and use the configured staging path. Fixing one side does not automatically fix the other.

Finance Unified BigQuery contrast

Finance Unified BigQuery behavior is relevant as a contrast, not as a substitute for daily app-sales refresh. The daily table app_sales_daily uses daily app-sales windows. Finance Unified uses finance_unified and finance-period replacement scope.

Finance rows use source_kind = 'FINANCE'. They come from monthly platform finance report families such as Apple financeReports and Google Play Earnings where configured. They should not be inserted into app_sales_daily, and daily rows should not be used as if they were monthly finance rows.

Raw report BigQuery caveat

Raw reports are provider-native artifacts. Current public-facing evidence should not advertise raw BigQuery loading as generally available or public-default. Raw report warehouse code paths may exist behind gates, but the safe public statement is that provider-native raw reports should be planned around GCS/S3 unless product explicitly enables and approves raw warehouse documentation.

This distinction matters because raw reports, daily enriched app-sales rows and Finance Unified rows have different schemas and meanings. A raw Google Play file is not automatically the app_sales_daily table. A raw Apple finance report file is not automatically a finance_unified row.

Safe synthetic examples

The examples below are synthetic. They are intended to show safe mental models and query shapes without exposing real project IDs, datasets, tables, staging paths, job IDs, task IDs, run IDs, logs, source rows, app IDs, SKUs, source row hashes, credentials or customer data.

Managed BigQuery targets

Daily App Sales table: example_project.example_dataset.app_sales_daily
Finance Unified table: example_project.example_dataset.finance_unified

Daily refresh mental model

Requested window: 2026-02-01 through 2026-02-07
Candidate rows: staged and checked before managed table update
Target update: selected daily window only
Stable table: example_project.example_dataset.app_sales_daily

Daily app-sales query

SELECT
  revenue_date,
  platform,
  source_report,
  COUNT(*) AS rows
FROM `example_project.example_dataset.app_sales_daily`
WHERE revenue_date BETWEEN DATE '2026-02-01' AND DATE '2026-02-07'
GROUP BY revenue_date, platform, source_report
ORDER BY revenue_date, platform, source_report;

Finance contrast query

SELECT
  fiscal_period,
  source_report,
  COUNT(*) AS rows
FROM `example_project.example_dataset.finance_unified`
WHERE fiscal_period = DATE '2026-02-01'
GROUP BY fiscal_period, source_report
ORDER BY fiscal_period, source_report;

The first query is daily operational analytics. The second query shows finance-period shape. Neither query is payout reconciliation, accounting close, audit evidence or an internal Netice merge script.