Match index & backfill

The DROP match index lets Astralis compare DROP's SHA-256 consumer hashes with your own consumer identifiers without moving raw identity data. Astralis materializes the index — a table of hashed identifiers — directly inside your Snowflake warehouse. Matching then happens as an in-warehouse join: raw identifiers are read, normalized, and hashed by SQL running in Snowflake, and only hashes and consumer keys ever leave the warehouse.

The index is populated by a backfill job that runs a gather query you author against your own tables.

The gather query

The gather query is a read-only SQL statement (it must start with SELECT or WITH; DML and DDL are rejected) that emits one row per identifier per person. It must produce exactly these columns:

Astralis normalizes and hashes each row in-warehouse according to the DROP specification, then merges the results into the match index table. Choose where the index lives with the Target schema setting (DATABASE.SCHEMA format); the Mapping tab lists the schemas visible to your Snowflake connection.

The {SINCE} window

Include the {SINCE} placeholder in your query (at most once) to support incremental backfills:

SELECT kind, external_id, v1, v2, v3, v4, exempt, resolved_at
FROM my_identifier_view
WHERE resolved_at >= {SINCE}

• On a full build (first backfill), {SINCE} binds to the Unix epoch, so every row is gathered.
• On an incremental run, {SINCE} binds to the watermark — the maximum resolved_at materialized so far. The comparison uses >=, so boundary rows are safely re-processed.

Example

Given a source table of resolved consumer identities:

CREATE TABLE consumer_identities (
  person_id     STRING,       -- your stable person key
  email         STRING,
  phone         STRING,
  is_exempt     BOOLEAN,
  updated_at    TIMESTAMP_NTZ
);

A gather query that emits one row per identifier per person. Selecting the changed people in a CTE applies {SINCE} once, then fans each of them out into one row per identifier kind — so a person who falls inside the window contributes their complete identifier set, not just the value that changed:

WITH changed_people AS (
  SELECT person_id, email, phone, is_exempt, updated_at
  FROM consumer_identities
  WHERE updated_at >= {SINCE}
)
 
SELECT 'EMAIL' AS kind, person_id AS external_id,
       email AS v1, NULL AS v2, NULL AS v3, NULL AS v4,
       is_exempt AS exempt, updated_at AS resolved_at
FROM changed_people
WHERE email IS NOT NULL
 
UNION ALL
 
SELECT 'PHONE' AS kind, person_id AS external_id,
       phone AS v1, NULL AS v2, NULL AS v3, NULL AS v4,
       is_exempt AS exempt, updated_at AS resolved_at
FROM changed_people
WHERE phone IS NOT NULL

Validate the mapping

The Mapping tab's validation runs your gather query as a dry run and checks the result contract (required columns, kinds, statement shape) without writing to the index. Fix any reported violations before triggering a backfill.

Full vs. incremental backfill

The first successful backfill activates the integration — scheduled cycles will not run before it completes. You can trigger a backfill manually from the Mapping tab and check the last result on the backfill status card.

Retro-match sweep

After every successful backfill, Astralis automatically re-probes previously unmatched work items (status 5 Not found) against the refreshed index. If a hash now matches — for example, because a new consumer record was resolved — Astralis mints the erasure privacy request right away. The corrected status is not retroactively uploaded; DROP redelivers work items on subsequent lists, and the next regular cycle reports the corrected status. See Operations & audit.