CES Registration Audit — Model Design, Filters & Logic, and Validation
This document describes the CES registration audit and attendance/registration reporting built to support Tasks 1–17 of the CES Pre-Audit Report. It covers what filters and logic are applied to build the model, and ends with validation results and example queries you can run against the built tables.
1. What we built: one broad model
A single broad model is used so one row-level dataset supports all report tasks (1–17):
int_ces__registration_audit_wide— One row per registration with: reg type, geography (country, region), is_onsite, attendance flags, product_category, primary_business, department, buying_influence, customer_base, company_revenue_band, job_function, and ranking flags (fortune_500, interbrand, twice_retail). Excludes cancelled and INDJOB=ST01 (see filters below).rpt_ces_attendance_registration_overview— Same grain and columns as the intermediate; one row per registration. All 17 tasks are answered by filtering and aggregating this single report table.
There is only one intermediate and one report model; no duplication.
Naming: Model names use a single ces segment: int_ces__registration_audit_wide, rpt_ces_attendance_registration_overview.
2. Filters and logic
2.1 Row-level filters (applied in the model)
| Filter | Logic |
|---|---|
| Exclude cancelled | 2023–2025: CANCEL_FLAG not in (‘Y’, ‘1’, ‘TRUE’, ‘YES’). 2026: ISCANCELLED <> 1. |
| Exclude INDJOB = ST01 | Where present: exclude rows where job/title code = ‘ST01’ (e.g. 2023–2025: TITLE_CODED; 2026: INDJOBDESC). |
| Valid email | Email is not null and not empty (after trim/lower). |
2.2 Registration type (mapped to report categories)
- Industry: ATT, ATTO, or raw value ‘industry’.
- Exhibitor Personnel: EX, EP, EXP, or raw value ‘exhibitor’.
- Media: MEDIA or raw value ‘media’.
- Other: All other reg types (included in the model; filter to Industry/Exhibitor Personnel/Media for Tasks 1–4 as needed).
2.3 On-site vs pre-show (is_onsite)
- 2023–2025: On-site =
REGISTRATION_WEEK = 0orREG_INTERFACE = 'delegate'(year-specific columns; see schema table below). - 2026: Set to
falsein current logic (to be refined with business using REGISTRANTINTERFACE / REGSOURCECODE).
2.4 Attendance flags (two sources, kept separate for comparison)
| Column | Source | Meaning |
|---|---|---|
| attended_flag | Badge scans (BADGE_SCANS → stg_member_engagement__badge_scans) | At least one successful physical badge scan at the show. |
| attended_events_flag | Events attendance (ces_events_history → stg_member_engagement__events_attendance) | At least one attended event/session record for that year + email. |
Badge scan data is currently 2026 only; earlier years will have attended_flag = false until scan data is loaded. Events-based attendance may cover other years depending on source data.
2.5 Raw schema mapping (by year)
| Need | 2023 | 2024 / 2025 | 2026 |
|---|---|---|---|
| Reg type | REG_TYPE, REG_TYPE_DETAIL | REG_TYPE, REG_TYPE_DETAIL | REGTYPECODE |
| Cancelled | CANCEL_FLAG | CANCEL_FLAG | ISCANCELLED |
| Geography | REGION, COUNTRY, CITY, STATE | Same | REGIONCNTRY, COUNTRYCODE, COUNTRY, CITY, STATE/STATECODE |
| Job (exclude ST01) | TITLE_CODED | TITLE_CODED | INDJOBDESC |
| On-site vs pre-show | REGISTRATION_WEEK, REG_INTERFACE, etc. | Same | REGISTRANTINTERFACE, REGSOURCECODE |
| Email / identity | EMAIL_ADDRESS, FIRST_NAME, LAST_NAME | Same | EMAIL, FIRSTNAME, LASTNAME |
Demographics (product category, primary business, department, buying influence, customer base, company revenue, job function) and ranking flags are mapped from year-specific columns (see below).
2.6 Ranking flags
| Flag | 2023–2025 logic | 2026 |
|---|---|---|
| fortune_500_flag | Raw fortune_500 / fortune_500_global contain company names (e.g. Jones Financial (Edward Jones), Blackstone, IBM, Procter & Gamble), not Y/1/YES/TRUE. Flag = true when either field is non-empty (after trim). | Set to false until source list available. |
| interbrand_flag | True when raw value in (‘Y’, ‘1’, ‘YES’, ‘TRUE’). | Set to false until source available. |
| twice_retail_flag | True when raw value in (‘Y’, ‘1’, ‘YES’, ‘TRUE’) (or twice_appliance for 2023). | Set to false until source available. |
3. Architecture
Raw (CES_2023/2024/2025/2026_REGISTRATION) + BADGE_SCANS + events attendance
→ int_ces__registration_audit_wide (one row per registration; all fields + attended_flag + attended_events_flag)
→ rpt_ces_attendance_registration_overview (same grain; all fields; query for Tasks 1–17)
Existing models (e.g. stg_member_engagement__ces_registration, int_ces__registrations_with_attendance) are unchanged.
4. Model grain and columns
Grain: One row per registration (ces_year + email).
Report table columns: ces_year, email, first_name, last_name, company, badgecount, registration_type_report, country, region, city, state, is_onsite, product_category, primary_business, primary_business_other, department, buying_influence, customer_base, company_revenue_band, job_function, fortune_500_flag, interbrand_flag, twice_retail_flag, attended_flag, attended_events_flag.
4.1 Filtering by Las Vegas
Two common interpretations:
| Meaning | How to filter |
|---|---|
| Attendance in Las Vegas (physical presence at the show) | Use attended_flag = true (badge scan at the venue). No city/state needed; the show is in Las Vegas. |
| Registrants from Las Vegas (registrant’s city/state = Las Vegas, NV) | Use city and state: e.g. WHERE lower(trim(city)) LIKE '%las vegas%' AND upper(trim(state)) IN ('NV', 'NEVADA') (adjust to match raw values in your data). |
The model now includes city and state (from raw CITY, STATE for 2023–2025; CITY, STATECODE for 2026) so you can filter by registrant location.
5. Task coverage summary
| Task(s) | Supported | Key columns |
|---|---|---|
| 1, 2, 3, 4 | Yes | registration_type_report, is_onsite, attended_flag |
| 5, 6 | Yes | region, country |
| 7 | Yes | product_category, registration_type_report (Industry/Media) |
| 8, 9, 10 | Yes | fortune_500_flag, interbrand_flag, twice_retail_flag |
| 11–17 | Yes | primary_business, department, buying_influence, customer_base, company_revenue_band, job_function |
6. Open points for business
- INDJOB = ‘ST01’: Confirm exact column and value per year (e.g. TITLE_CODED or code column).
- On-site definition: Confirm which fields and values define on-site for 2023–2025 and 2026.
- 2026 ranking flags: Populate fortune_500_flag, interbrand_flag, twice_retail_flag when source lists are available.
7. Validation results (sample)
The following are sample validation results run against the model. Tables: CI_CD_DB_MARTS (e.g. CI_CD_DB_MARTS.CICD_43_REPORTS.RPT_CES_ATTENDANCE_REGISTRATION_OVERVIEW). Connection: Snow CLI with cta_cursor. Replace the table name in the queries below with your schema + table if different.
7.1 Task 1 — Validation run (CI_CD_DB_MARTS, cta_cursor)
Task 1 is run in two variants, each showing both attendance sources (badge scan vs events table):
- Current logic — All registrations (Industry, Exhibitor Personnel, Media); no job_function filter.
- Excluding students — Same filters plus exclude rows where
job_functioncontains “student” (e.g.lower(trim(job_function)) not like '%student%'orjob_function is null).
Variant 1 — Current logic (both attendance flags):
| CES_YEAR | REGISTRATION_TYPE_REPORT | REGISTRATION_COUNT | ATTENDANCE_BADGE_SCAN | ATTENDANCE_EVENTS |
|---|---|---|---|---|
| 2023 | Exhibitor Personnel | 45,300 | 0 | 174 |
| 2023 | Industry | 105,670 | 0 | 348 |
| 2023 | Media | 7,028 | 0 | 218 |
| 2024 | Exhibitor Personnel | 58,237 | 0 | 157 |
| 2024 | Industry | 112,112 | 0 | 197 |
| 2024 | Media | 7,949 | 0 | 15 |
| 2025 | Exhibitor Personnel | 59,577 | 0 | 93 |
| 2025 | Industry | 111,874 | 0 | 221 |
| 2025 | Media | 8,948 | 0 | 1,894 |
| 2026 | Exhibitor Personnel | 60,048 | 6,691 | 0 |
| 2026 | Industry | 119,006 | 20,831 | 0 |
| 2026 | Media | 9,467 | 3,884 | 0 |
Variant 2 — Excluding job_function = student (both attendance flags):
| CES_YEAR | REGISTRATION_TYPE_REPORT | REGISTRATION_COUNT | ATTENDANCE_BADGE_SCAN | ATTENDANCE_EVENTS |
|---|---|---|---|---|
| 2023 | Exhibitor Personnel | 45,292 | 0 | 174 |
| 2023 | Industry | 105,376 | 0 | 348 |
| 2023 | Media | 7,028 | 0 | 218 |
| 2024 | Exhibitor Personnel | 58,237 | 0 | 157 |
| 2024 | Industry | 111,536 | 0 | 197 |
| 2024 | Media | 7,949 | 0 | 15 |
| 2025 | Exhibitor Personnel | 59,577 | 0 | 93 |
| 2025 | Industry | 111,291 | 0 | 221 |
| 2025 | Media | 8,948 | 0 | 1,894 |
| 2026 | Exhibitor Personnel | 60,047 | 6,691 | 0 |
| 2026 | Industry | 118,165 | 20,784 | 0 |
| 2026 | Media | 9,467 | 3,884 | 0 |
Notes: Badge scan data is 2026 only (2023–2025 attendance_badge_scan = 0). Events-based attendance is present for 2023–2025 (attendance_events); 2026 events may be 0 depending on source load. Excluding students reduces registration_count mainly in Industry (e.g. 2023: 105,670 → 105,376; 2026: 119,006 → 118,165) and badge-scanned attendance in 2026 Industry (20,831 → 20,784).
SQL — Variant 1 (current logic, both flags):
SELECT ces_year, registration_type_report,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_badge_scan,
count_if(attended_events_flag) AS attendance_events
FROM CI_CD_DB_MARTS.CICD_43_REPORTS.RPT_CES_ATTENDANCE_REGISTRATION_OVERVIEW
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
GROUP BY ces_year, registration_type_report
ORDER BY ces_year, registration_type_report;SQL — Variant 2 (exclude job_function = student, both flags):
SELECT ces_year, registration_type_report,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_badge_scan,
count_if(attended_events_flag) AS attendance_events
FROM CI_CD_DB_MARTS.CICD_43_REPORTS.RPT_CES_ATTENDANCE_REGISTRATION_OVERVIEW
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
AND (job_function IS NULL OR lower(trim(job_function)) NOT LIKE '%student%')
GROUP BY ces_year, registration_type_report
ORDER BY ces_year, registration_type_report;Task 1 & 2 — Attendance / Completed Registration by type (overview)
Logic: Non-cancelled; registration_type in (Industry, Exhibitor Personnel, Media). Use attended_flag for badge-scan attendance, attended_events_flag for events-table attendance (see §7.1 for run results).
2023–2025: Badge scan = 0; events-based attendance may have counts. 2026: Badge scan has counts; events may be 0 depending on source.
Task 5 — By Region
Model has region and country; group by region (and optionally country) with count and count_if(attended_flag) for attendance by geography.
Task 7 — Product Category (Industry/Media)
Model has product_category and registration_type_report; filter to Industry/Media and group by product_category for registration and attendance counts.
Tasks 8–10 — Ranking flags
Model has fortune_500_flag, interbrand_flag, twice_retail_flag; filter to attended and count distinct company (or count rows) for each flag. For 2023–2025, fortune_500_flag is set from company names in the raw fortune_500/fortune_500_global fields (non-empty = true). Interbrand/twice may be 0 until source lists are applied.
Task 11 — Primary Business (Industry)
Model has primary_business (and primary_business_other); filter to Industry and group by primary_business. Tasks 12–17 use department, buying_influence, customer_base, company_revenue_band, job_function the same way.
Note: BADGE_SCANS currently has only 2026 data; 2025 (and 2024/2023) attendance counts from badge scan will be 0 until scan data for those years is loaded.
8. Validation and example queries
Run these against your built report table (e.g. rpt_ces_attendance_registration_overview or CI_CD_DB_MARTS.CICD_43_REPORTS.rpt_ces_attendance_registration_overview). Replace the table name if your schema differs.
Compare badge scan vs events attendance
SELECT ces_year, registration_type_report,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_badge_scan,
count_if(attended_events_flag) AS attendance_events,
count_if(attended_flag AND attended_events_flag) AS both,
count_if(attended_flag AND NOT attended_events_flag) AS badge_only,
count_if(NOT attended_flag AND attended_events_flag) AS events_only
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
GROUP BY ces_year, registration_type_report
ORDER BY ces_year, registration_type_report;Task 1 — Total Attendance (Confirmed Attendance by registration type)
SELECT ces_year, registration_type_report,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_count
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
GROUP BY ces_year, registration_type_report
ORDER BY ces_year, registration_type_report;Task 2 — Completed Registration
Same as Task 1; use registration_count as completed registrations.
Task 3 — Pre-show % to Attendance (exclude on-site)
SELECT ces_year, registration_type_report,
count(*) AS preshow_registration_count,
count_if(attended_flag) AS preshow_attendance_count,
round(100.0 * count_if(attended_flag) / nullif(count(*), 0), 1) AS pct_attended
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
AND is_onsite = false
GROUP BY ces_year, registration_type_report
ORDER BY ces_year, registration_type_report;Task 4 — On-Site Registration
SELECT ces_year, registration_type_report,
count(*) AS onsite_registration_count
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
AND is_onsite = true
GROUP BY ces_year, registration_type_report
ORDER BY ces_year, registration_type_report;Task 5 — Total Attendance by Region
SELECT ces_year, region,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_count,
count(distinct country) AS country_count
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
GROUP BY ces_year, region
ORDER BY ces_year, attendance_count DESC;Task 6 — Top 20 Countries by Attendance
SELECT ces_year, country,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_count,
round(100.0 * count_if(attended_flag) / nullif(sum(count_if(attended_flag)) OVER (PARTITION BY ces_year), 0), 1) AS pct_of_total
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
AND country IS NOT NULL AND trim(country) <> ''
GROUP BY ces_year, country
QUALIFY row_number() OVER (PARTITION BY ces_year ORDER BY count_if(attended_flag) DESC) <= 20
ORDER BY ces_year, attendance_count DESC;Task 7 — Media and Industry Attendance by Product Category
SELECT ces_year, product_category,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_count,
round(100.0 * count_if(attended_flag) / nullif(sum(count_if(attended_flag)) OVER (PARTITION BY ces_year), 0), 1) AS pct_of_total
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Media')
AND product_category IS NOT NULL AND trim(product_category) <> ''
GROUP BY ces_year, product_category
ORDER BY ces_year, attendance_count DESC;Task 8 — Fortune 500 Companies in Attendance
SELECT ces_year, count(distinct company) AS fortune_500_company_count
FROM rpt_ces_attendance_registration_overview
WHERE fortune_500_flag = true AND attended_flag = true
GROUP BY ces_year ORDER BY ces_year;Task 9 — Interbrand Companies in Attendance
SELECT ces_year, count(distinct company) AS interbrand_company_count
FROM rpt_ces_attendance_registration_overview
WHERE interbrand_flag = true AND attended_flag = true
GROUP BY ces_year ORDER BY ces_year;Task 10 — Retail Focus (Twice) in Attendance
SELECT ces_year, count(distinct company) AS twice_retail_company_count
FROM rpt_ces_attendance_registration_overview
WHERE twice_retail_flag = true AND attended_flag = true
GROUP BY ces_year ORDER BY ces_year;Task 11 — Industry Attendees by Primary Business
SELECT ces_year, primary_business,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_count,
round(100.0 * count(*) / nullif(sum(count(*)) OVER (PARTITION BY ces_year), 0), 1) AS pct_of_year
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report = 'Industry'
AND primary_business IS NOT NULL AND trim(primary_business) <> ''
GROUP BY ces_year, primary_business
ORDER BY ces_year, attendance_count DESC;Task 12 — Primary Business Other
SELECT ces_year, primary_business_other,
count(*) AS registration_count
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report = 'Industry'
AND primary_business_other IS NOT NULL AND trim(primary_business_other) <> ''
GROUP BY ces_year, primary_business_other
ORDER BY ces_year, registration_count DESC;Task 13 — Industry Attendees by Department
SELECT ces_year, department,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_count
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report = 'Industry'
AND department IS NOT NULL AND trim(department) <> ''
GROUP BY ces_year, department
ORDER BY ces_year, attendance_count DESC;Task 14 — Industry Attendees by Buying Influence
SELECT ces_year, buying_influence,
count(*) AS registration_count,
round(100.0 * count(*) / nullif(sum(count(*)) OVER (PARTITION BY ces_year), 0), 1) AS pct_of_year
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report = 'Industry'
AND buying_influence IS NOT NULL AND trim(buying_influence) <> ''
GROUP BY ces_year, buying_influence
ORDER BY ces_year, registration_count DESC;Task 15 — Industry Attendees by Customer Base
SELECT ces_year, customer_base,
count(*) AS registration_count,
round(100.0 * count(*) / nullif(sum(count(*)) OVER (PARTITION BY ces_year), 0), 1) AS pct_of_year
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report = 'Industry'
AND customer_base IS NOT NULL AND trim(customer_base) <> ''
GROUP BY ces_year, customer_base
ORDER BY ces_year, registration_count DESC;Task 16 — Industry Attendees by Company Revenue
SELECT ces_year, company_revenue_band,
count(*) AS registration_count,
round(100.0 * count(*) / nullif(sum(count(*)) OVER (PARTITION BY ces_year), 0), 1) AS pct_of_year
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report = 'Industry'
AND company_revenue_band IS NOT NULL AND trim(company_revenue_band) <> ''
GROUP BY ces_year, company_revenue_band
ORDER BY ces_year, registration_count DESC;Task 17 — Senior-Level Executive Attendance (by job function)
SELECT ces_year, job_function,
count(*) AS registration_count,
count_if(attended_flag) AS attendance_count,
round(100.0 * count_if(attended_flag) / nullif(count(*), 0), 1) AS attendance_rate_pct
FROM rpt_ces_attendance_registration_overview
WHERE registration_type_report IN ('Industry', 'Exhibitor Personnel', 'Media')
AND job_function IS NOT NULL AND trim(job_function) <> ''
GROUP BY ces_year, job_function
ORDER BY ces_year, attendance_count DESC;Running validation (e.g. CI/CD): If you use Snow CLI with connection cta_cursor, you can run the validation SQL file: snow sql --connection cta_cursor -f dbt_project/analyses/ces_validation_cicd_queries.sql, or use ./scripts/run_ces_validation_cta_cursor.sh. Edit table names in the SQL file if your PR or schema differs.