Brainforge Knowledge

transformation-layer-guide

2 min read

Transformation Layer Guide

Purpose: Reference for building dbt staging and marts from RAW.
Related: raw-schema-profiles

Source → Staging → Mart Flow

flowchart LR
    subgraph raw [RAW]
        QB[POLYTOMIC_QUICKBOOKS]
        HS[POLYTOMIC_HUBSPOT]
        CL[CLOCKIFY]
        OP[OPERATING_IO]
    end
    subgraph staging [Staging]
        stg_qb[stg_quickbooks_*]
        stg_hs[stg_hubspot_*]
        stg_cl[stg_clockify_time_entries]
        stg_op[stg_operating_time_entries]
    end
    subgraph marts [Marts]
        mart_delivery[mart_delivery_time_effort]
        mart_finance_acct[mart_finance_account_monthly]
        mart_finance_summary[mart_finance_summary]
    end
    QB --> stg_qb
    HS --> stg_hs
    CL --> stg_cl
    OP --> stg_op
    stg_cl --> mart_delivery
    stg_op --> mart_delivery
    stg_qb --> mart_finance_acct
    mart_finance_acct --> mart_finance_summary

Phase 1: Delivery (Time/Effort)

stg_clockify_time_entries

  • Source: RAW.CLOCKIFY.TIME_ENTRIES
  • Grain: One row per time entry
  • Key columns: TIME_ENTRY_ID, START_TIME, END_TIME, DURATION_HOURS, USER_NAME, PROJECT_NAME, USER_ID, PROJECT_ID
  • No JSON flattening — columns are flat

stg_operating_time_entries

  • Source: RAW.OPERATING_IO.RAW_TIME_ENTRIES
  • Flatten: RAW_DATA:date, RAW_DATA:personId, RAW_DATA:projectId, RAW_DATA:seconds, RAW_DATA:approvalStatus
  • Compute hours: seconds / 3600
  • Join: To RAW_PERSONS, RAW_PROJECTS for names (or use Operating as source of truth for allocation %)

mart_delivery_time_effort

  • Unify: Clockify + Operating (or pick one)
  • Grain: person + project + date + hours
  • Consider: Clockify has 14k entries, Operating has 2.5k — may represent different populations (billable vs internal)

Phase 2: Finance

stg_quickbooks_invoices

  • Source: RAW.POLYTOMIC_QUICKBOOKS.INVOICES
  • Extract: CUSTOMER_REF:value::TEXT AS customer_id
  • Filter: _POLYTOMIC_DELETED_AT IS NULL

stg_quickbooks_bills

  • Source: RAW.POLYTOMIC_QUICKBOOKS.BILLS
  • Extract: VENDOR_REF:value::TEXT AS vendor_id

stg_quickbooks_payments

  • Source: RAW.POLYTOMIC_QUICKBOOKS.PAYMENTS

stg_hubspot_deals

  • Source: RAW.POLYTOMIC_HUBSPOT.DEAL_PROPERTY_HISTORY
  • Pivot: Latest value per DEAL_ID, PROPERTY_NAME
  • Key properties: amount, dealstage, closedate, createdate

mart_finance_account_monthly

  • Sources: stg_quickbooks_journal_entries (+ optional Finance+Ops mapping table when present)
  • Grain: month + account_id + account_category (+ service_line if mapped)
  • Purpose: Canonical monthly ledger rollup with revenue / cogs / operating_expenses categories when mapping exists. Falls back to unmapped when mapping is unavailable.

mart_finance_summary

  • Sources: mart_finance_account_monthly, stg_quickbooks_invoices, stg_quickbooks_bills, stg_quickbooks_payments
  • Grain: month
  • Metrics: revenue_amount, cogs_amount, operating_expenses_amount, accounts_receivable_balance_amount, accounts_payable_balance_amount, cash_received_amount, plus invoice/bill equivalents

Graph View