Processing Email Data in Data Lakes and Warehouses

When Email Data Outgrows Spreadsheets

Most teams start managing email lists in spreadsheets or their CRM. As data volume grows and sources multiply, limitations appear:

Architecture Overview

Data flow for email data

Technology options

Schema Design for Email Data

Staging tables (raw)

-- Raw email contacts from various sources
CREATE TABLE staging.raw_contacts (
    id              VARCHAR,
    source_system   VARCHAR,        -- 'salesforce', 'hubspot',
                                    -- 'csv_upload', etc.
    source_id       VARCHAR,        -- ID in source system
    email           VARCHAR,
    first_name      VARCHAR,
    last_name       VARCHAR,
    company         VARCHAR,
    title           VARCHAR,
    phone           VARCHAR,
    source_file     VARCHAR,        -- For file-based imports
    raw_data        VARIANT,        -- Full raw record (JSON)
    ingested_at     TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP(),
    batch_id        VARCHAR
);

Cleaned tables

-- Normalised and deduplicated contacts
CREATE TABLE analytics.dim_contact (
    contact_id          VARCHAR PRIMARY KEY,
    email_normalised    VARCHAR NOT NULL,
    email_domain        VARCHAR,
    first_name          VARCHAR,
    last_name           VARCHAR,
    company_name        VARCHAR,
    job_title           VARCHAR,
    phone_normalised    VARCHAR,
    is_valid_email      BOOLEAN,
    is_role_based       BOOLEAN,
    is_disposable       BOOLEAN,
    is_catch_all        BOOLEAN,
    verification_status VARCHAR,    -- 'verified', 'unverified',
                                    -- 'invalid', 'unknown'
    first_seen_at       TIMESTAMP_NTZ,
    last_seen_at        TIMESTAMP_NTZ,
    source_count        INTEGER,    -- Number of sources this
                                    -- contact appears in
    primary_source      VARCHAR,
    consent_status      VARCHAR,    -- 'opted_in', 'opted_out',
                                    -- 'unsubscribed', 'unknown'
    gdpr_basis          VARCHAR,    -- 'consent', 'legitimate_interest',
                                    -- 'contract'
    suppressed          BOOLEAN DEFAULT FALSE,
    created_at          TIMESTAMP_NTZ,
    updated_at          TIMESTAMP_NTZ
);

-- Track which sources each contact came from
CREATE TABLE analytics.bridge_contact_source (
    contact_id      VARCHAR,
    source_system   VARCHAR,
    source_id       VARCHAR,
    first_seen_at   TIMESTAMP_NTZ,
    last_seen_at    TIMESTAMP_NTZ,
    PRIMARY KEY (contact_id, source_system, source_id)
);

-- Email event history
CREATE TABLE analytics.fct_email_event (
    event_id        VARCHAR PRIMARY KEY,
    contact_id      VARCHAR,
    event_type      VARCHAR,    -- 'sent', 'delivered', 'opened',
                                -- 'clicked', 'bounced',
                                -- 'unsubscribed', 'complained'
    campaign_id     VARCHAR,
    event_timestamp TIMESTAMP_NTZ,
    metadata        VARIANT     -- Additional event data (JSON)
);

SQL Patterns for Email Data

Email normalisation

-- Normalise email addresses
SELECT
    LOWER(TRIM(email)) AS email_normalised,
    SPLIT_PART(LOWER(TRIM(email)), '@', 2) AS email_domain,
    SPLIT_PART(LOWER(TRIM(email)), '@', 1) AS email_local_part
FROM staging.raw_contacts
WHERE email IS NOT NULL
  AND email LIKE '%@%.%'
  AND LENGTH(email) <= 254;

Deduplication with priority

-- Deduplicate contacts, keeping the most complete record
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY LOWER(TRIM(email))
            ORDER BY
                -- Prefer records with more fields filled
                (CASE WHEN first_name IS NOT NULL THEN 1 ELSE 0 END
                + CASE WHEN last_name IS NOT NULL THEN 1 ELSE 0 END
                + CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
                + CASE WHEN title IS NOT NULL THEN 1 ELSE 0 END
                + CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END
                ) DESC,
                -- Then prefer newer records
                ingested_at DESC
        ) AS rn
    FROM staging.raw_contacts
    WHERE email IS NOT NULL
)
SELECT * FROM ranked WHERE rn = 1;

Role-based address detection

-- Flag role-based email addresses
SELECT
    email_normalised,
    CASE
        WHEN email_local_part IN (
            'info', 'admin', 'support', 'sales', 'contact',
            'help', 'billing', 'abuse', 'postmaster',
            'webmaster', 'noreply', 'no-reply',
            'marketing', 'security', 'legal', 'hr',
            'office', 'team', 'hello'
        ) THEN TRUE
        ELSE FALSE
    END AS is_role_based
FROM analytics.dim_contact;

Domain analysis

-- Analyse email domains for list composition
SELECT
    email_domain,
    COUNT(*) AS contact_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2)
        AS percentage,
    COUNT(CASE WHEN is_valid_email THEN 1 END)
        AS valid_count,
    COUNT(CASE WHEN is_role_based THEN 1 END)
        AS role_based_count,
    COUNT(CASE WHEN is_disposable THEN 1 END)
        AS disposable_count
FROM analytics.dim_contact
GROUP BY email_domain
ORDER BY contact_count DESC
LIMIT 50;

Cross-source deduplication

-- Find contacts that appear in multiple sources
SELECT
    c.email_normalised,
    c.source_count,
    LISTAGG(DISTINCT bs.source_system, ', ')
        WITHIN GROUP (ORDER BY bs.source_system) AS sources,
    MIN(bs.first_seen_at) AS earliest_seen,
    MAX(bs.last_seen_at) AS latest_seen
FROM analytics.dim_contact c
JOIN analytics.bridge_contact_source bs
    ON c.contact_id = bs.contact_id
GROUP BY c.email_normalised, c.source_count
HAVING c.source_count > 1
ORDER BY c.source_count DESC;

ETL Pipeline Design

Ingestion patterns by source

dbt model structure

PII Handling

Access controls

Data masking

-- Create a masked view for analysts who do not need
-- to see individual email addresses
CREATE VIEW analytics.v_contact_masked AS
SELECT
    contact_id,
    -- Hash the email for joining without exposing it
    SHA2(email_normalised) AS email_hash,
    email_domain,
    -- Mask name
    LEFT(first_name, 1) || '***' AS first_name_masked,
    LEFT(last_name, 1) || '***' AS last_name_masked,
    company_name,
    job_title,
    is_valid_email,
    is_role_based,
    verification_status,
    consent_status,
    source_count,
    primary_source
FROM analytics.dim_contact;

Retention and deletion

Reverse ETL: Syncing Clean Data Back

Sync patterns

Pre-Processing with Email Extractor

Before loading email data into a data warehouse, the data often exists in unstructured formats. Upload raw files (PDF reports, HTML pages, XLSX exports, CSV dumps) to Email Extractor to extract email addresses. Download the results as CSV, which can then be loaded into a staging table through a standard CSV ingestion pipeline. This pre-processing step is especially useful for ad hoc data sources like conference attendee lists, business card scans and exported documents that do not have a structured API.

Related Guides

• Data Pipeline Email Processing
• Data Quality Scoring Email Lists
• Data Deduplication at Enterprise Scale
• Data Governance Frameworks for Email
• CRM Data Hygiene Guide