Incremental Pull

Why _changed_at exists

Every current-state view in gold_adm_views carries a _changed_at DATE column: the date of the pipeline run in which that row's content last changed. Use it to pull daily deltas instead of re-scanning a whole view on every sync.

_changed_at exists because lastUpdated, the timestamp a source connector supplies, isn't a dependable change signal. It's optional in Brinqa, so a large share of rows never populate it at all, and when it is populated, a connector re-sync can bump it without the row's actual content changing. _changed_at is computed by BrinqaDL itself: it only advances when a row's tracked columns genuinely change, so you can trust it as a watermark. It's a DATE, not a TIMESTAMP. Change detection runs at day granularity, matching the pipeline's once-daily run cadence.

The high-water-mark pattern

SELECT *
FROM `your-project.gold_adm_views.v_findings`
WHERE _changed_at >= @your_high_water_mark

1. Query a view filtered on your last recorded high-water mark (@your_high_water_mark) for that view.
2. Upsert the results into your mirror, keyed on that view's merge key (see Which views carry it below; it isn't always id).
3. Record MAX(_changed_at) from the pulled rows as the new high-water mark for that view.

Three rules:

• Use >=, not >. _changed_at is date-granular and the pipeline can run more than once on the same calendar date (for example during a backfill). >= combined with a keyed upsert re-pulls the boundary date harmlessly and never misses a same-date change.
• Cursor each view independently. _changed_at is computed per view. A denormalized view like v_findings can change on a day a related view doesn't, and vice versa. Don't assume two views advance together, and don't share one high-water mark across views.
• The first pull is a full load. After you first connect, start your high-water mark at that date and pull everything. _changed_at is never NULL, so there's no gap to backfill separately.

Advancing the watermark

Pull once per day, after the daily pipeline run completes. An intraday re-pull against the same high-water mark returns the same rows and costs the same bytes scanned, so there's no benefit to polling more often. After a successful pull, advance your stored high-water mark to MAX(_changed_at) from the rows you just received, not to today's date. That way, if a pull is skipped or fails, the next one still catches up correctly from where you left off.

How change is detected

For the highest-volume views, such as v_findings, the pipeline compares a content hash of each row against the prior run and advances _changed_at only when the row's tracked content actually changed. The hash deliberately excludes connector sync timestamps, so a source re-sync that only bumps a timestamp does not advance _changed_at.

Other views are rebuilt in full on every run and restamped to the run date. For those, _changed_at tells you the view is current as of that date, and you pull it in full each time. This is inexpensive because these views are comparatively small.

That gives two delta qualities:

Quality	Meaning	How to pull
Delta	Only genuinely changed rows carry a new _changed_at; filtering also reduces bytes scanned	WHERE _changed_at >= @your_high_water_mark
Full-pull	The whole view is restamped every run	Same query; it returns everything, so you reload the view

Detecting deletions

No consumption view exposes a delete flag or a deleted-at column. Instead, a row that's removed at the source simply stops appearing in the current-state views, silently, with no per-row delete event.

That means the row disappearing from the view is the only signal you get. If you're upserting rows into your own store, periodically compare the full set of ids on a view against your mirror. Any id in your mirror that no longer appears in the view has been deleted (or, if it comes back later, was only temporarily removed). Track deletes on your own side this way rather than expecting a per-row delete event.

Which views carry it

Every consumption view in gold_adm_views carries _changed_at, including v_active_dates.

Views fall into a few groups:

• Findings and their attribute views (v_findings, v_finding_attributes): delta, content-hash detected, key id.
• Other attribute views (v_asset_attributes, v_ticket_attributes, v_owner_details, v_persons): delta, following their base view's change detection, key id (v_owner_details keys on owner_id).
• Entity views (v_assets, v_tickets, v_risk_summary, v_compliance_posture, v_coverage_gaps, v_business_services): full-pull, key id. v_threat_intel and v_cve_products use composite keys (finding x threat-intel row, and CVE x CPE, respectively).
• Reference and catalog views (for example v_assessments, v_attack_patterns, v_cve, v_cwe, v_environments, v_finding_definitions, v_sla_definitions, v_connectors): full-pull, key id (v_connectors keys on connector_name).
• Association views, one row per relationship rather than per entity (for example v_asset_domain, v_asset_technology, v_definition_cve, v_finding_risk_factor, v_ticket_finding): full-pull, keyed on the pair of endpoint id columns. v_owner_member is a three-column exception, keyed on (owner_id, user_id, association_type). v_ticket_finding, v_asset_environment, and v_asset_owner are snapshot-based, so _changed_at is the snapshot date and they behave as delta by snapshot date.
• Daily and weekly aggregate views (v_findings_daily, v_status_transitions, v_changes_daily, v_changes_weekly): delta, only the current date's rows restamp each run. v_findings_trend, v_mttr_sla, and v_mttr_sla_by_env are full-pull. v_findings_by_org_trend is mixed: daily rows are delta, weekly and monthly rows are full-pull. Key on the date or period plus the view's grouping dimensions, never on id alone.
• History views (v_finding_history, v_asset_history, v_asset_owner_history, v_finding_asset_history): delta, but _changed_at is an alias of that view's own required partition column (event_date or snapshot_date). Filtering on that native column name is required, not just recommended: a _changed_at-only filter errors on those views, because the partition filter must be on the underlying column. Key on id plus that partition date.

Best practices

• Never de-duplicate a multi-grain view on id alone. Association and aggregate views have multiple rows per entity. Upserting them keyed only on id silently collapses rows, for example turning a finding with three risk factors into one row. Always use the view's documented key.
• Select only the columns you need. BigQuery bills by columns scanned, so SELECT col1, col2 costs a fraction of SELECT *. Reserve SELECT * for cases where you genuinely mirror every column.
• Dry-run before a large pull. bq query --dry_run (or your BI tool's cost estimate) shows bytes scanned before you spend them.

For general query patterns, including array handling and time-series aggregation, see the query cookbook. For the underlying view and column concepts referenced here, see core concepts.