Column Configuration

After writing a model’s SQL query and previewing the results, you configure each output column to tell Zeotap how to use it. Column configuration defines the semantic meaning of each column — whether it identifies a record, carries data attributes, or serves as a primary key.

Column Types

Every column in a model is assigned one of two types:

Identifier

An identifier column contains values that can be used to match or look up records in a destination system. Examples include:

• email — Match contacts by email address
• customer_id — Match records by internal ID
• phone — Match contacts by phone number
• external_id — Match by a destination-specific identifier

Identifier columns are used during sync to determine which records in the destination to create, update, or delete. At least one identifier column is typically required for upsert and mirror sync modes.

When multiple identifier columns are present, the sync uses the one mapped to the destination’s primary identifier field (e.g., “Email” in Salesforce, “email” in HubSpot).

Attribute

An attribute column contains data values that describe or enrich a record. Examples include:

• first_name, last_name — Personal information
• lifetime_value — Computed metrics
• signup_date — Timestamps
• segment, tier — Categorizations
• city, country — Geographic data

Attribute columns are mapped to fields in the destination during sync. They carry the actual data being activated.

Primary Key

The primary key is the column (or combination of columns) that uniquely identifies each row in the model’s result set. The primary key is critical for:

• Deduplication — Ensuring each row represents a unique record
• Change detection — Determining which records have been added, updated, or removed between sync runs
• Upsert/mirror operations — Matching source records to destination records

Single Column Primary Key

The most common case. A single column uniquely identifies each row:

SELECT
  customer_id,  -- Primary key
  email,
  name,
  lifetime_value
FROM customers

Configure customer_id as the primary key in the column configuration panel.

Composite Primary Key

When no single column is unique, combine multiple columns:

SELECT
  account_id,    -- Part of composite primary key
  contact_id,    -- Part of composite primary key
  email,
  role
FROM account_contacts

Select both account_id and contact_id as primary key columns. Together, they uniquely identify each row.

Choosing a Primary Key

Guidelines for selecting the right primary key:

Validation: Zeotap warns if the preview results contain duplicate values in the primary key columns, as this indicates the key does not uniquely identify rows.

Data Type Detection

Zeotap automatically detects the data type of each column from the query preview results:

If the auto-detected type is incorrect (e.g., a numeric ID is detected as a number but should be treated as text), you can override the type in the configuration panel.

Nested Columns

Zeotap also discovers the structure inside nested columns, so their fields appear in the pickers that build audience filters and relationship join keys instead of having to be typed by hand.

Snowflake also reports plain VARIANT, OBJECT and ARRAY columns whose contents the catalog cannot describe. For those, Zeotap reads a small bounded sample of the table and offers the keys it finds. Sampling is deliberately cheap and best-effort — if it cannot run, the column simply has no suggested fields and you can still type the path yourself.

Array wrappers are transparent: the fields listed for an array<struct<…>> column are the fields of each element. Map and dictionary columns list no fields, because their keys are data rather than schema.

Declared Types and Nested Paths

The data type a column carries is what tells Zeotap how to read into that column, so it matters whenever you address a nested value by path. When you declare or override column types on a model:

• declare an array as array<element_type> — for example array<string> or array<struct<device_id:string>> — not as text;
• declare a JSON payload as json.

A JSON payload left declared as text cannot be used as a path join key at all — the relationship is rejected when you save it, with a message naming the column. A real array left declared as text is read as JSON held in a string instead of as an array, which produces a query the warehouse rejects. Columns introspected directly from a warehouse table already carry the right type, so this matters mainly for SQL models whose output types you set yourself.

Configuration Panel

The column configuration panel appears after a successful query preview. It displays a table with one row per output column:

Editing Column Configuration

For each column, you can:

1. Toggle type — Switch between Identifier and Attribute using the dropdown
2. Set primary key — Check/uncheck the primary key checkbox
3. Override data type — Change the auto-detected data type if needed

Validation Rules

Zeotap enforces the following rules:

• At least one primary key — Every model must have at least one column marked as the primary key
• Primary keys must be non-nullable — Columns used as primary keys should not contain NULL values (a warning is shown if NULLs are detected in preview)
• Unique column names — All column names must be unique (use SQL aliases to rename duplicates)
• At least one identifier — Models used in syncs should have at least one identifier column for record matching

Impact on Syncs

Column configuration directly affects how syncs operate:

Identifier Columns in Sync

When creating a sync, you map model columns to destination fields. Identifier columns are available for mapping to the destination’s identifier fields (e.g., Email, External ID). The destination uses these to determine whether to create a new record or update an existing one.

Primary Key in Sync

The primary key determines how Zeotap tracks changes between sync runs:

• New rows — Rows with primary key values not seen in the previous run are treated as inserts
• Changed rows — Rows with existing primary key values but different attribute values are treated as updates
• Missing rows — Rows present in the previous run but absent in the current run are treated as deletes (relevant for mirror sync mode)

Attribute Columns in Sync

Attribute columns carry the data payload. During sync, each attribute column is mapped to a specific field in the destination. Unmapped attribute columns are ignored.

Example Configurations

Customer Profiles

SELECT
  customer_id,
  email,
  phone,
  first_name,
  last_name,
  city,
  country,
  lifetime_value,
  segment
FROM customers

Event Aggregations

SELECT
  user_id,
  event_type,
  COUNT(*) AS event_count,
  MAX(event_time) AS last_occurrence
FROM events
GROUP BY user_id, event_type

Note: Both user_id and event_type form a composite primary key since neither is unique on its own.

Account-Contact Mapping

SELECT
  account_id,
  contact_id,
  email,
  role,
  is_primary
FROM account_contacts

Column Sensitivity & PII Masking

Columns can also be assigned sensitivity levels that control data visibility and sync behavior. Mark columns containing PII (email, phone, SSN, etc.) as Redacted, Sync Only, or Blocked to enforce masking in previews, suppress value suggestions, and control sync access.

Sensitivity is configured on the entity config page (Relationships > Model > Configure). Zeotap can automatically detect likely PII columns based on column name patterns.

See PII Masking for full details.

Next Steps

• Create a Reverse ETL sync using your configured model
• Map fields to destination fields
• Set up relationships to define entity relationships
• Configure PII masking for sensitive columns