> ## Documentation Index
> Fetch the complete documentation index at: https://docs.virtuous.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Column Reference

> Column conventions, key types, and a quick-reference grain table for all core CRM_RAW tables.

## **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:

```text theme={null}
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 [<u>Lookup values</u>](https://github.com/jgunkelvirtuous/mintlify-virtuous-data-documentation/blob/main/data-models/reference/lookup-values) to find the correct `ENTITY` and `PROPERTY` values for the decode join.

<Tip>
  **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.
</Tip>