Core Concepts

Consumption views and the star schema

BrinqaDL organizes data into three BigQuery layers (see the data lake overview for the full breakdown). The layer you query directly is the consumption views (v_*), a set of pre-joined views over the underlying star schema.

The star schema itself follows a standard dimensional pattern: fact-like views such as v_findings and v_tickets sit at the center, surrounded by dimension-like views such as v_assets, v_owner_details, and v_environments. Many-to-many relationships get their own bridge views, for example v_asset_owner and v_ticket_finding, so a single asset can link to multiple owners, and a single ticket can link to multiple findings, without duplicating rows on either side.

Because the consumption views are already pre-joined, you rarely need to assemble the star schema yourself. Reach for a bridge view, or a directory view (the one-per-entity catalog views such as v_cwe, v_attack_techniques, v_sla_definitions), only when a current-state view like v_findings doesn't already carry the column or relationship you need.

Numeric id joins, never uid

Every entity carries two identifiers:

• id: an internal, numeric surrogate key.
• uid: an external, display-oriented identifier.

Always join on id. Foreign key columns on the fact-like views (risk_owner_id, remediation_owner_id, finding_type_id, and so on) are numeric id references to the corresponding dimension-like or directory view. Never join on uid. It's meant for display, and it isn't guaranteed to identify a single row on its own (see skeleton rows below).

environment_id is a partial exception: an asset or finding can belong to multiple environments, so environment_id is a derived pick of the primary (first) environment, not a guaranteed 1:1 foreign key. The full set is in the environment_ids array; join against that when you need every environment, not just the primary one.

Sentinel rows

Internally, the star schema uses a row with id = -1 in every dimension-like view and directory view, representing "Unknown" or "not applicable," so a foreign key always has somewhere to point even when there's nothing real to resolve to.

The consumption views hide that sentinel row. Current-state views like v_owner_details and v_environments never list an id = -1 row. Meanwhile an unresolved foreign key on a fact-like view reads as either -1 (some columns carry the sentinel value) or NULL (others are left empty when there's no match). Either way, the value can point at a row your view doesn't contain.

This matters for joins: an INNER JOIN on that foreign key silently drops the row (no owner, no environment, no match, so the row disappears from your result). Use a LEFT JOIN instead, and treat a NULL result, or a foreign key value of -1, as "unknown" or "unassigned" rather than expecting the join to surface an Unknown row.

Skeleton rows

Sometimes the raw source data references an entity before that entity has fully loaded, for example a finding that names an owner the pipeline hasn't ingested yet. That produces an incomplete placeholder row, a skeleton row, that shares the referenced entity's uid but carries its own id.

The pipeline deduplicates by id before publishing the consumption views, always keeping the fully-populated row over a skeleton placeholder. You won't see skeleton rows in v_* views. This is also why id, not uid, is the safe join key: a skeleton row and its complete counterpart can briefly share a uid while carrying different id values.

severity vs severity_bucket

severity is the display-ready classification string, for example 4-high or 3-medium. severity_bucket is the integer form of the same value, extracted from the leading number in severity.

Valid severity_bucket tiers are 0, 2, 3, 4, and 5 (there's no 1). severity_bucket is NULL when severity carries no numeric prefix, such as a connector-supplied value like BestPractice.

Use severity_bucket for numeric comparisons and thresholds, for example severity_bucket >= 4 for "high or critical." Use severity when you want the human-readable label.

statusCategory is a dimension, not a filter

Every Brinqa customer configures their own status workflow, and statusCategory is the simplified grouping computed from that customer-specific configuration. The values it produces are specific to your configuration, not a fixed Brinqa vocabulary, so don't assume a default label like 'open' applies to every customer.

Before filtering on statusCategory, discover your own values first:

SELECT DISTINCT statusCategory
FROM `gold_adm_views.v_findings`

Filtering on a value you've confirmed from your own data, for example WHERE LOWER(statusCategory) = LOWER('open'), is a normal, correct query once 'open' is a value your workflow actually produces. statusCategory also works well as a GROUP BY dimension for reporting ("how many findings fall into each category this week") when you want the full breakdown rather than one filtered value.

Incremental pulls with _changed_at

Every consumption view carries a _changed_at date column. Pull only what changed since your last sync:

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

This pattern applies across every v_* view, whether the underlying data is high-volume (findings, assets) or a small reference table. Track your own high-water mark (the max _changed_at you've already pulled) and advance it after each successful sync, rather than re-pulling an entire view every time.

One caveat: the four history views (v_finding_history, v_asset_history, v_finding_asset_history, v_asset_owner_history) require a date filter on their own partition column (event_date or snapshot_date), not just _changed_at. A query against one of them with no partition filter errors instead of running. See Which views carry it in the incremental pull guide.

Glossary

• Assessment: A scan or assessment campaign record, carrying status, compliance status, and risk rollups for the work that produced a set of findings. Query v_assessments for the catalog.
• Asset: An infrastructure entity, such as a host, container image, subnet, or cloud resource. Query v_assets.
• asset_type: The infrastructure category of an asset: Host, ContainerImage, Subnet, and so on.
• ATT&CK Mitigation: A MITRE ATT&CK mitigation, a defensive measure that reduces the effectiveness of one or more techniques. Query v_attack_mitigations.
• Attack Pattern (CAPEC): A CAPEC attack pattern describing a method of exploiting a weakness. Links to CWEs through v_attack_pattern_weakness and to CVEs through v_cve_attack_pattern. Parent and child hierarchy is in v_attack_pattern_parent. Query the catalog directly through v_attack_patterns.
• Bridge Table: A view that resolves a many-to-many relationship in the star schema. For example, v_asset_owner connects assets to their owners, and v_ticket_finding connects tickets to findings. Bridge views only list confirmed pairs, so sentinel rows never appear in them.
• Business Service: A business-level grouping of assets that represents a deployed service or application portfolio. Carries a criticality rating and an owner. Query v_business_services for the rollup, or v_asset_business_service to join individual assets to their business services.
• Change-Rate View: A trending view built from change-history data that counts state changes (NEW, CHANGED, UNSEEN) per time period. Answers "how fast" questions: daily finding intake rate, weekly remediation throughput, ticket closure velocity. Examples: v_changes_daily, v_changes_weekly, v_status_transitions.
• connectorNames: Array column listing which scanner connectors found an entity, for example Rapid7 InsightVM or Wiz. Use UNNEST() to expand it, or join v_connectors for connector metadata.
• Consumption view: A pre-joined, query-ready BigQuery view, prefixed v_. These are the only views you query directly.
• Coverage Gap: An asset that hasn't been scanned in 30+ days, or has never been assessed. Surfaced by v_coverage_gaps to highlight blind spots in your scanning coverage.
• CPE (Common Platform Enumeration): An identifier naming an affected product (part, vendor, product, version). Query v_cve_products for the per-CVE, per-product drilldown, or v_cpe for the catalog directly.
• CQRS Pattern: Command Query Responsibility Segregation, the architecture pattern behind the data lake's current-state views (v_findings, v_assets) and their paired change-history views (v_finding_history, v_asset_history), which capture an append-only history for temporal analysis.
• CVSS: Common Vulnerability Scoring System. A severity score (0-10) based on vulnerability characteristics.
• Dedup: The deduplication step applied before the consumption views are built. Skeleton and other placeholder rows are removed for you, so you see one clean row per entity. Join on the numeric id. See Skeleton rows above.
• environment_name: The deployment environment an asset belongs to, such as Production or Development.
• EOL Advisory: An end-of-life or end-of-support advisory for a product, capturing the EOL date, release date, and whether the product is already past its EOL date. Query v_eol_advisories for the catalog.
• EPSS: Exploit Prediction Scoring System. A probability (0-1) that a CVE will be exploited in the next 30 days.
• Event Table: An append-only log of state changes (NEW, CHANGED, UNSEEN) for an entity over time. Surfaced through the change-history views v_finding_history and v_asset_history, and the grouped v_changes_daily / v_changes_weekly views.
• event_type: In the change-history views: NEW (first seen), CHANGED (an attribute changed), UNSEEN (disappeared from the source).
• Finding: A security issue detected by a scanner, such as a vulnerability, violation, alert, or incident. Query v_findings.
• finding_type: The scanner category of a finding: Vulnerability, Violation, Alert, Incident, and so on.
• Incremental Merge: The pipeline's strategy for keeping the star schema current: each run detects which rows actually changed and merges in only those, rather than reprocessing everything from scratch. Not every consumption view surfaces that merge as its own row-by-row update; some rebuild in full on every run from data that was itself incrementally updated underneath. See Incremental pull for which views behave as a true delta versus a full-pull restamp, and their merge keys (most key on id; a few, such as v_owner_details, use a different key).
• KEV: CISA Known Exploited Vulnerabilities catalog. Findings flagged is_kev = TRUE have confirmed active exploitation.
• Materialization: How a consumption view gets built in BigQuery. table creates a full table on each run. incremental merges in only new and changed rows. view computes on read.
• MITRE ATT&CK Tactic: A MITRE ATT&CK tactic, the adversary's tactical goal (for example Initial Access or Persistence). Techniques achieve tactics through v_attack_technique_tactic. Query the catalog directly through v_attack_tactics.
• MITRE ATT&CK Technique: A MITRE ATT&CK technique (Txxxx) describing an adversary behavior. Techniques roll up to tactics through v_attack_technique_tactic, are mitigated by entries in v_attack_mitigations (joined through v_mitigation_technique), and link to CVEs through v_cve_attack_technique. Sub-technique hierarchy is in v_attack_technique_parent. Query the catalog directly through v_attack_techniques.
• MTTR (Mean Time To Remediate): The average elapsed time, in days, to close out a finding. Query v_mttr_sla for the company-wide number, or v_mttr_sla_by_env to slice by environment. Use a longer window (for example 90 days) for a stable trend rather than averaging pre-averaged shorter windows.
• Parallel Array Sets: Threat-intel rollup columns such as cwe_ids / cwe_uids / cwe_names (and the equivalent attack_technique_*, attack_tactic_*, and attack_pattern_* families on v_threat_intel) are parallel sets, not positionally paired arrays. cwe_ids holds surrogate ids, cwe_uids holds external identifiers (for example CWE-79), and cwe_names holds display names. Element N of one array doesn't necessarily describe element N of another. To pair an id with its uid or name, join through the matching catalog view, v_cwe, v_attack_techniques, and so on.
• Partition Pruning: A BigQuery optimization where queries that filter on a view's partition column (for example _changed_at on v_findings) skip reading irrelevant partitions, reducing cost and latency.
• QUALIFY: BigQuery clause for filtering window function results. Used with ROW_NUMBER() for time-travel style queries against the history views.
• Risk Rating: A bucketed label derived from the numeric risk score. Values follow the pattern 5-critical, 4-high, 3-medium, 2-low, 0-none, giving both a readable display value and a sortable order.
• riskScore: Computed risk score (0-100) assigned by the platform's risk engine.
• Sentinel Value: Internally, a row with id = -1 representing "Unknown" or "not applicable." Consumption views hide this row, so a foreign key pointing to it (or left NULL) needs a LEFT JOIN to avoid silently dropping the row. See Sentinel rows above.
• severity: Risk classification string: 5-critical, 4-high, 3-medium, 2-low, 0-none.
• severity_bucket: Integer form of severity, used for numeric comparisons and trend charts. Extracted from the leading number in severity (for example 4-high becomes 4). Valid tiers are 0, 2, 3, 4, 5 (there's no 1); NULL when severity has no numeric prefix. See severity vs severity_bucket above.
• Skeleton Row: An incomplete placeholder row created when the raw data references an entity that hasn't fully loaded yet. Shares the referenced entity's uid but carries its own id. The pipeline removes these before they reach the consumption views. See Skeleton rows above.
• SLA Tier: The compliance level of an entity against its service level agreement target: Compliant, NonCompliant, or Warning. Computed from the target number of days and the actual elapsed days.
• snapshot_date: The date a point-in-time snapshot was taken. Used for point-in-time reconstruction in the history views, v_finding_asset_history and v_asset_owner_history.
• Star Schema: The dimensional modeling pattern behind the data lake: fact-like views such as v_findings and v_tickets, surrounded by dimension-like views such as v_assets and v_owner_details, connected through foreign keys and bridge views.
• State-Snapshot View: A trending view built from aggregated current-state data that captures inventory levels at a point in time. Answers "how many" questions: total open findings on a given date, or weekly average risk posture. Examples: v_findings_daily, v_findings_trend.
• Status Category: A simplified grouping of the free-text status field. Each customer's status configuration defines its own category values, so confirm your own values (for example SELECT DISTINCT statusCategory) before filtering, and it also works well as a reporting dimension you group by. See statusCategory is a dimension, not a filter above.
• Ticket: A remediation work item (for example a ServiceNow incident) linked to one or more findings. Query v_tickets.
• UNNEST(): BigQuery function for expanding array columns into rows. Required when filtering or aggregating connectorNames, tags, and similar array columns.
• Weakness (CWE): A Common Weakness Enumeration entry (CWE-####) describing a category of software or hardware weakness. CVEs map to CWEs through v_cve_weakness; finding definitions map through v_definition_weakness. Parent and child hierarchy is in v_weakness_parent. Query the catalog directly through v_cwe.