Brainforge Knowledge

def-539-pylon-plain-support-modeling

3 min read

DEF-539 — Plain + Pylon support modeling (Default / brainforge-bi)

Date: 2026-04-09
Linear: DEF-539
Code: brainforge-bi/analytics/ (dbt project default)

Product direction

  • Pylon is production; Plain is historical overlap.
  • Union + dedupe: count each logical ticket once; Pylon forward for new volume.
  • Stakeholder context: Slack #client-default; metrics spec in Google Doc Dashboard 4 (see ticket attachments).

Warehouse

  • MotherDuck: raw_data.pylon.issues, raw_data.pylon.accounts (Polytomic; _polytomic_deleted_at on tables).
  • DEF-519: Supabase copies may exist for app use; authoritative analytics path for this stack is MotherDuck raw_data (aligned with Plain under raw_data.plain).

Dedupe rules

  1. Bridge: For each Pylon issue, unnest(coalesce(external_issues, [])) and collect non-null external_id values as candidate Plain thread_id values.
  2. Plain exclusion: Plain threads whose thread_id appears in that set are excluded from Plain aggregates (threads, messages joined to those threads).
  3. Pylon inclusion: All non-deleted Pylon issues (with account join) contribute to Pylon-side daily ticket counts.
  4. Result: int_ce__ticket_activity_daily = plain_ticket_activity union all pylon_ticket_activity (no double-count for linked threads).

Implementation notes (dbt)

  • int_ce__ticket_activity_daily is materialized: ephemeral so its SQL is inlined into parents. A persisted view hit DuckDB/MotherDuck binder errors (“Contents of view were altered”) when fct_support_tickets_daily ran; ephemeral avoids that.
  • Union output is wrapped in explicit cast(...) so Plain vs Pylon branches share stable types (double for avg resolution days, bigint for counts).
  • Build: dbt build --selector support_ticket_lineage --profile default --target cicd (see analytics/selectors.yml). Do not rely on raw_pylon__issues+ alone—it omits the raw_pylon__accounts branch.
  • Staging tests: not_null on int_stg__pylon_issues.issue_id, account_id and int_stg__pylon_accounts.account_id (schema_pylon.yml).

Grain and gaps

  • Plain: (metric_date, customer_id) plus domain enrichment (unchanged logic, filtered threads).
  • Pylon: (metric_date, account_id) rolled to domain fields; customer_id is null; messages_created is 0 until a Pylon message source exists.

Downstream

  • fct_support_tickets_daily, fct_customer_enablement_daily, int_ce__ticket_activity_team_daily — still ref("int_ce__ticket_activity_daily"); dbt inlines ephemeral SQL into these models.
  • fct_support_tickets_daily joins SF via a narrow subquery on int_ce__sf_unique_domain_account (account_id, account_name, domain_key).

Omni handoff

Prefer the mart for dashboards (SF labels already joined):

ObjectGrainKey fields
marts.fct_support_tickets_dailymetric_date × ticket-activity row × SF account (when domain_key matches)metric_date, customer_id (Plain only; null for Pylon-only rows), salesforce_account_id, salesforce_account_name, domain_name, company_name, domain_key, tickets_created, messages_created, avg_ticket_resolution_days

Topic guidance: one fact topic on fct_support_tickets_daily with time dimension metric_date; optional attributes company_name, domain_key; join to existing Salesforce/account dimensions if duplicated columns are undesirable.

Future: add support_source (plain / pylon) in SQL if product needs split filters; not in v1.

Validation checklist (MotherDuck)

  1. dbt build --selector support_ticket_lineage --profile default --target cicd (and dbt test --select int_stg__pylon_issues int_stg__pylon_accounts).
  2. Row counts: Pylon daily tickets > 0 when sync live; Plain linked thread IDs absent from Plain side when present in int_ce__pylon_external_plain_thread_ids.
  3. Spot-check external_id matches known Plain thread IDs.
  4. If an old intermediate.int_ce__ticket_activity_daily view still exists in MotherDuck from before ephemeral, drop it once to avoid confusion (optional; dbt no longer manages that relation).

Graph View