# Setlive (SET) Data Ingestion Flow

This document outlines the process for ingesting SET fan registration data into our Snowflake data warehouse.

## Overview

SET delivers a daily CSV containing every fan record that had an update for a given day. The file covers activity across all three SET collection types:

| **Collection Type** | **Description** |
|---------------------|-----------------|
| `set.live`          | Fan opt-ins at live events / shows |
| `set.fan`           | Digital campaign registrations |
| `set.bio`           | Link-in-bio form submissions |

**Row granularity:** one row per artist / fan / source. A single fan can appear multiple times in the same daily file — for example, if they attend a SET.Live event and also complete a SET.Fan form on the same day, two rows are produced. `EMAIL` is the fan's unique identifier but is **not** unique per row; the natural grain is `EMAIL + SET_ARTIST_ID + SOURCE`.

## S3 Drop Location

Incoming data files are stored here:  
`s3://prod-orcdbucket/feed-drop/SetLive/`

> **Note:** The `filename_template` in `config.py` is currently hardcoded to the test file `oidc-test-1776701561539.csv`. Update it to a date-parameterised regex (e.g. `'setlive_{date:%Y-%m-%d}\\.csv$'`) once the vendor confirms the production filename convention.

## Snowflake Destination

| **Property** | **Value**         |
|--------------|-------------------|
| **Account**  | `DELPHI`          |
| **Database** | `FAN_LIVE_EVENTS` |
| **Schema**   | `QA / PROD`       |

## Daily Reports

| **Report Name** | **Staging Raw Table**    | **Errors Table**               | **Frequency** |
|-----------------|--------------------------|--------------------------------|---------------|
| Opt Ins         | `RAW_SETLIVE_OPT_INS`    | `RAW_SETLIVE_OPT_INS_ERRORS`   | Daily         |

Rows are split at load time: records with a non-null `EMAIL`, `COLLECTION_ID`, and `SET_ARTIST_ID` are written to the main table; any row missing one of those fields is routed to the errors table.

## Key Features

### 1. Protecting PII

The following columns are masked via Snowflake masking policy `FAN_LIVE_EVENTS.<SCHEMA>.SETLIVE_VARCHAR_MASK_ETL`, applied to the transient temp table before any data is loaded:

| **Column**    | **Description**       |
|---------------|-----------------------|
| `EMAIL`       | Fan email address     |
| `FIRST_NAME`  | Fan first name        |
| `LAST_NAME`   | Fan last name         |
| `POSTAL_CODE` | Fan postal code       |
| `PHONE_NUMBER`| Fan phone number      |

### 2. Data Validation

- Rows missing `EMAIL`, `COLLECTION_ID`, or `SET_ARTIST_ID` are routed to `RAW_SETLIVE_OPT_INS_ERRORS` rather than dropped.
- A `TRANSIENT TABLE` is used for the temp staging table so PII data has no Snowflake Fail-safe retention window.

### 3. Idempotency & Restatements

The flow checks DynamoDB feed status at bootstrap and skips dates already marked `INGESTED`. To reprocess a restated file for a given date, trigger the Jenkins job with `RELOAD=true`, which clears the status and allows the delete-then-reload cycle to run.

### 4. Monitoring and Alerts

Pipeline failures in prod emit a Slack alert to `#data-alerts`. Feed status is tracked in DynamoDB table `{env}_feed_ingestion_status` under key `setlive_opt_ins`.

---

For more details on transformations, validation rules, or pipeline configurations, please refer to the [Technical Design Document](#).