Column Reference - Virtuous API Docs

Column roles

Every column is classified into one of these roles:

Role	Description	Example
`PK`	Primary key — globally unique identifier	`GIFT_KEY`
`FK`	Foreign key — references a primary key in another table	`CONTACT_KEY`
`Identifier`	Non-key internal numeric ID (not for joins)	`GIFT_ID`
`Date/Time`	Timestamp or date field	`GIFT_DATE_UTC`
`Flag`	Boolean true/false field	`GIFT_IS_DELETED`
`System`	Snowflake sync metadata column	`SF__ROW_SYNCED_DATE_TIME_UTC`
`Attribute`	All other business data columns	`AMOUNT`, `NAME`

Key column conventions

`_KEY` vs `_ID`

Suffix	Type	Use for joins?	Notes
`_KEY`	TEXT	Yes — always use this	Global hash-based key; stable across environments
`_ID`	NUMBER	No	Virtuous internal numeric ID; not reliable for cross-table joins

`_IS_DELETED`

Every table has an ENTITY_IS_DELETED flag. Always filter WHERE ENTITY_IS_DELETED = FALSE — soft-deleted records are retained in the table but should be excluded from reports.

`SF__` prefix

Columns prefixed with SF__ are Snowflake sync metadata (e.g. SF__ROW_SYNCED_DATE_TIME_UTC). Use SF__ROW_SYNCED_DATE_TIME_UTC to check data freshness. These columns are not business data and can be ignored in most queries.

Core tables quick reference

Table	Grain	Primary Key	Key foreign keys
`CONTACTS`	One row per contact (household or org)	`CONTACT_KEY`	—
`CONTACT_INDIVIDUALS`	One row per person within a contact	`CONTACT_INDIVIDUAL_KEY`	`CONTACT_KEY`
`GIFTS`	One row per gift transaction	`GIFT_KEY`	`CONTACT_KEY`, `SEGMENT_KEY`
`GIFT_DESIGNATIONS`	One row per project split within a gift	`GIFT_DESIGNATION_KEY`	`GIFT_KEY`, `PROJECT_KEY`
`PROJECTS`	One row per fund or program	`PROJECT_KEY`	—
`CAMPAIGNS`	One row per campaign	`CAMPAIGN_KEY`	—
`COMMUNICATIONS`	One row per channel within a campaign	`COMMUNICATION_KEY`	`CAMPAIGN_KEY`
`SEGMENTS`	One row per audience subgroup within a communication	`SEGMENT_KEY`	`COMMUNICATION_KEY`
`RECURRING_GIFTS`	One row per recurring giving commitment	`RECURRING_GIFT_KEY`	`CONTACT_KEY`
`RECURRING_GIFT_PAYMENTS`	One row per recurring payment instance	`RECURRING_GIFT_PAYMENT_KEY`	`RECURRING_GIFT_KEY`, `GIFT_KEY`
`PLEDGES`	One row per pledge commitment	`PLEDGE_KEY`	`CONTACT_KEY`, `GIFT_ASK_KEY`
`PLEDGE_PAYMENTS`	One row per pledge installment	`PLEDGE_PAYMENT_KEY`	`PLEDGE_KEY`, `GIFT_KEY`
`GIFT_ASKS`	One row per formal gift solicitation	`GIFT_ASK_KEY`	`CONTACT_KEY`, `PROJECT_KEY`
`TASKS`	One row per task associated with a contact	`TASK_KEY`	`CONTACT_KEY`
`RECEIPTS`	One row per receipt issued to a contact	`RECEIPT_KEY`	`CONTACT_KEY`
`TYPE_REFERENCES`	One row per valid code value	—	—

All tables above are in the CRM_RAW table group. In Snowflake SQL, reference them as [database].[schema].TABLE_NAME.

Finding columns with numeric codes

To identify every column in the schema that may need a decode lookup:

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    COMMENT AS DESCRIPTION
FROM [database].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '[schema]'
    AND (
        COLUMN_NAME LIKE '%STATUS%'
        OR COLUMN_NAME LIKE '%TYPE%'
        OR COLUMN_NAME LIKE '%FREQUENCY%'
    )
    AND COLUMN_NAME NOT LIKE '%_KEY'
    AND COLUMN_NAME NOT LIKE '%_ID'
ORDER BY TABLE_NAME, COLUMN_NAME;

Cross-reference the results against Lookup values to find the correct ENTITY and PROPERTY values for the decode join.

Data freshness: These tables sync continuously from Virtuous CRM. Typical lag is under 4 hours. To check when a record was last updated, inspect the SF__ROW_SYNCED_DATE_TIME_UTC column on any table.

​Column roles

​Key column conventions

​_KEY vs _ID

​_IS_DELETED

​SF__ prefix

​Core tables quick reference

​Finding columns with numeric codes

Column roles

Key column conventions

`_KEY` vs `_ID`

`_IS_DELETED`

`SF__` prefix

Core tables quick reference

Finding columns with numeric codes