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;
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.
