# Source behaviour the checks depend on

Facts here were checked against the pages listed in `sources.yaml`, most recently on 2026-11-06. Vendors change these; when a live page disagrees, follow the page and say what changed. Items marked "verify at run time" could not be confirmed from public documentation for every account.

## GA4 BigQuery export

- Google updates a daily table `events_YYYYMMDD` for up to 2 calendar days plus today after its date: the table dated 20220101 is updated through 20220104. Events that arrive later are not recorded, and Google may still reprocess old tables after a fix. Freeze comparisons at run date minus 4 days, the newest table that can no longer change (a run on 2026-11-01 ends on 2026-10-28).
- Reports and the Data API estimate Sessions and Active users with HyperLogLog++ and can apply thresholding and modelling; the export holds exact, unmodelled rows. Google's example shows 1,463 sessions in reports and 1,501 in BigQuery for the same week. Never put a report count and an export count in one ratio; compare shares, or compare event and key event counts.
- Streaming export writes `events_intraday_YYYYMMDD`. It is best effort, has no event limit, and a day's intraday table is deleted once that day's daily table is complete. Never count an intraday table together with its daily table, and never treat intraday as complete for a finished day.
- Standard properties have a 1,000,000-event daily batch export limit. Persistent excess pauses the export, and missed days are not reprocessed. Analytics 360 has higher limits.
- An invalid payment method on the linked project leaves gaps that cannot be re-exported. Changing the dataset region needs a relink and leaves a gap; replicating the dataset avoids it.
- `event_date` is in the property's time zone (YYYYMMDD string). `event_timestamp` is UTC microseconds.
- `traffic_source` is first-user acquisition (user-scoped) and is absent from intraday tables. It is not the session source.
- `collected_traffic_source` is event-level: manual UTM fields plus gclid, dclid and srsltid. There is no gbraid or wbraid field.
- `session_traffic_source_last_click` is session-scoped. Its `cross_channel_campaign` record holds source, medium, campaign name and ID and source platform; `google_ads_campaign` holds the Google Ads customer, campaign and ad group. Tables from before mid-2024 lack it.
- The schema has no channel group field. classify_channels.py predicts it.
- A session is `user_pseudo_id` plus the `ga_session_id` event parameter. Count sessions once before summing.
- `batch_event_index`, `batch_ordering_id` and `batch_page_id` order events within a batch. `is_active_user` is filled only in daily tables.
- App data needs Android SDK 17.2.5 or iOS SDK 16.20.0 or later for the current schema fields.

## Google Ads

- Each account has a fixed time zone and currency (`customer.time_zone`, `customer.currency_code`). Reports and the API use the account time zone.
- `metrics.cost_micros` divided by 1,000,000 is spend in account currency.
- `metrics.conversions` counts the actions included in the Conversions column (primary actions); `metrics.all_conversions` adds secondary and other actions.
- Conversions are reported against the interaction (click) date by default. `metrics.conversions_by_conversion_date` reports them by conversion date and appears in the UI as "Conversions (by conv. time)".
- Each conversion action has a click-through window from 1 to 90 days, so old click dates keep gaining conversions until the window closes. Read the window per action at run time.
- Report rows whose metrics are all zero are not returned. A missing row is not evidence of a failed load, nor of zero activity.
- Customer IDs display as 123-456-7890 and are stored as 1234567890. Keep them as strings.
- BigQuery Data Transfer Service for Google Ads: tables and views such as `p_ads_CampaignBasicStats_<customer_id>` with `segments_date`, `metrics_cost_micros` and `metrics_conversions`; refresh window 7 days by default, 1 to 30. Inspect the actual table names with `get_table_info` before querying.
- Fivetran Google Ads: incremental syncs cover the past 3 days; the daily rollback covers the Conversion Window Size, 1 to 90 days, default 30.

## Meta

- Each ad account has a fixed currency and time zone (account settings).
- `action_report_time` assigns conversions to the impression time, the conversion time or a mix. Ads Manager and the API can differ; record the value on both sides before comparing.
- Since 12 January 2026 the Insights API no longer returns the 7-day view and 28-day view windows. 1-day click, 7-day click, 28-day click, 1-day engaged view and 1-day view remain. Models that still request the removed windows read nulls, often coalesced to zero.
- Retention: unique-count fields and hourly breakdowns keep 13 months, frequency breakdowns 6 months, totals 37 months.
- Aggregate action types overlap with their component types (an omni type and its pixel type, for example). Verify the hierarchy for the account's events at run time before summing.
- Fivetran Facebook Ads: prebuilt reports roll back 9 days; custom reports follow the longest selected attribution window (28-day click gives 28); `total_actions` is pre-aggregated and summing per-action rows can double count.
- Whether Meta appends fbclid to non-ad clicks for an account's traffic is not documented as ad-only; do not use fbclid as proof of paid.
- The Insights API documents no field that counts modelled conversions, so a bridge never subtracts a modelled figure for Meta.

## LinkedIn Ads

- adAnalytics returns an empty response when there is no activity to report and also when the identity cannot read the account, so a missing row proves nothing. Confirm activity from Campaign Manager or the account list.
- Performance metrics by account, campaign or creative are near real time. Professional demographic metrics are approximate, can lag 12 to 24 hours, drop values under 3 events and keep only the top 100 values per creative per day. Never reconcile them to totals.
- Because of that approximation, summed daily values can differ slightly from a full-period request. Pull full periods at the level you compare (account, not summed campaigns).
- `costInLocalCurrency` is in the ad account's currency. The schema page says `dateRange` is in UTC, so confirm the day basis on a sample day before day-level joins with other platforms.
- The API is versioned monthly and old versions sunset on a published date (version 202511 sunsets on 16 November 2026). Custom jobs pinned to a version belong on the OPS-07 calendar.

## Microsoft Advertising

- Report downloads carry a header with the time zone, the last completed available day and "Potential Incomplete Data". Set ReturnOnlyCompleteData to true for closed days: the request then fails with error 2004 instead of returning partial data.
- A click can take up to 2 hours to process and a conversion 3 hours; data is generally complete after 3 hours, but invalid-traffic adjustments can take a week or more.
- A report across campaigns in different time zones is complete only after the day has ended in all of them.
- The reporting guide says downloaded data is relative to UTC, while ReportTimeZone only sets what "today" means (Pacific time by default). Confirm the day basis on a sample day before day-level joins.
- msclkid reaches GA4 only in the `page_location` query string.

## HubSpot

- Original Traffic Source and its two drill-downs record the first known web source; HubSpot sets them automatically. A user can change the value on a record, which clears the drill-downs, and any import or integration allowed to write the property can do the same, so read its history. Record Source is a separate property that says how the record was created. Associated companies and deals mirror these properties. The usual internal name is `hs_analytics_source`; verify property names in the portal's settings.
- Deal Amount is the deal value. The calculated ARR, MRR and TCV properties use line items. Close date can hold an expected date or be set by automation. None of them is cash collected.
- Deleted records arrive with `_fivetran_deleted` true, unless the table is in history mode, where a deleted record is one with no active row. Merged-away records keep their old IDs in merge history; current-state models must point at the surviving record.
- With Fivetran history mode on deals, the raw table holds versions; filter `_fivetran_active` for the current state.
- The portal time zone affects UI reports; API timestamps are UTC.

## Salesforce

- `IsDeleted` is true once a record moves to the Recycle Bin (Salesforce's system fields page, checked 2026-11-06); connectors also mark deletes in their own column.
- `SystemModstamp` does not capture every field change: when object A retrieves a value from object B, the change shows on B's timestamp and not on A's (same page). A cross-object formula is that case, so incremental copies can hold stale formula values. Recompute in the warehouse or confirm the connector's handling.
- Lead source and campaign influence fields are configured per org (administrators set the Lead Source options); read the org's definitions before mapping them to channels.

## Stripe

- Amounts are integers in the currency's smallest unit. Zero-decimal currencies such as JPY have no minor unit, so never apply a blanket divide by 100.
- Charge currency and settlement currency can differ. The Balance report is in settlement currency; keep both and convert under the documented FX policy.
- Invoice fields measure different things: `total_excluding_tax` is a different basis from `total`, and `amount_due` can reflect customer balance adjustments. Name the field in every metric.
- `livemode` false marks test-mode objects. Exclude them by the flag, not by name patterns.
- Metadata set on a customer does not propagate to every related object. Check the field on the object you extract (invoice, subscription, charge).
- A credit note reduces what an invoice asks for and can mark an open invoice paid without a payment; a refund reduces cash. Do not subtract both from one measure.
- Stripe Data Pipeline refresh cadence and warehouse availability depend on the account; verify at run time.

## Fivetran system columns and history mode

- `_fivetran_synced` is when Fivetran last wrote the row. It is not updated for TRUNCATE, for mass deletes during a re-sync or for deletes in history-mode tables, so it is a weak freshness signal.
- `_fivetran_deleted` marks a deleted source row.
- History mode adds `_fivetran_active`, `_fivetran_start` and `_fivetran_end` and drops `_fivetran_deleted`. Count entities with `_fivetran_active` true.
- Schema change handling per connection: Allow all, Allow columns or Block all. Record it in the manifest.

## dbt freshness and artifacts

- A source is checked only when it has `warn_after` or `error_after` with a count and period.
- Without `loaded_at_field` or `loaded_at_query`, dbt uses warehouse metadata where the adapter supports it. On BigQuery, metadata freshness for a wildcard source such as `events_*` is unreliable.
- `dbt build` does not include source freshness. A dbt platform job's "Run source freshness" checkbox runs it first and does not stop later steps; an explicit `dbt source freshness` step (`dbt freshness` in dbt v2) stops the steps after it.
- `run_results.json` (schema v6) holds `unique_id`, `status` and `failures` per node. `sources.json` (schema v3) holds `max_loaded_at`, `snapshotted_at`, `max_loaded_at_time_ago_in_s`, `criteria` and `status` (pass, warn, error, runtime error). dbt v2 writes `freshness.json` (schema v0), with statuses Pass, Warn and Error, and leaves out a node whose freshness query failed; check the dbt version and artifact schema before parsing. run_results.json is overwritten by each command, so a job's last step decides what it holds.

## Warehouse defaults

- BigQuery: `@@time_zone` starts as UTC in every script, so `DATE(timestamp)` without a zone is a UTC date. Write `DATE(ts, 'America/New_York')`.
- BigQuery INFORMATION_SCHEMA: on-demand queries bill at least 10 MB and are never cached; PARTITIONS is Preview, limited to 1,000 tables per query, and its `last_modified_time` may not reflect deletions.
- Snowflake: the TIMEZONE parameter defaults to America/Los_Angeles, and TIMESTAMP_LTZ values render in the session zone. INFORMATION_SCHEMA.TABLES `LAST_ALTERED` moves on DDL, on DML that affects no rows and on background maintenance; `LAST_DDL` tracks DDL only.
- Snowflake Account Usage views lag 45 minutes to 3 hours; Information Schema views do not lag.