Brainforge Knowledge

data-freshness-and-lineage

2 min read

Data Freshness and Lineage (Internal dbt Project)

Last verified: 2026-03-05

This document covers:

  1. Raw source freshness context for the four Phase 3 sources.
  2. How to generate and view dbt lineage docs.
  3. Source → staging → mart dependencies for the current project.

Project path: knowledge/engineering/data-platform/dbt/

1) Raw source freshness and ownership

If cadence is not formally defined in code/scheduler config, it is marked as TBD (do not assume).

Raw source schemaExample freshness table/columnSync mechanism / ownerExpected cadenceLast observed load timestamp (UTC, from 2026-03-05 check)If cadence is TBD, where to confirm
RAW.CLOCKIFYRAW.CLOCKIFY.TIME_ENTRIES.updated_atCustom sync (Data Platform)TBD (planning docs mention daily-or-better target)2026-03-04 22:02:29Data Platform + Ops; confirm in source sync job config
RAW.OPERATING_IORAW.OPERATING_IO.RAW_TIME_ENTRIES.created_atOperating.io sync (Data Platform)TBD2026-03-04 22:02:17Data Platform + Ops; confirm in source sync job config
RAW.POLYTOMIC_QUICKBOOKSRAW.POLYTOMIC_QUICKBOOKS.INVOICES._POLYTOMIC_UPDATED_ATPolytomic (Finance/Ops + Data Platform)TBD (planning docs mention month-close cadence as likely baseline)2026-03-04 12:00:49Finance/Ops + Data Platform; confirm in Polytomic connector schedule
RAW.POLYTOMIC_HUBSPOTRAW.POLYTOMIC_HUBSPOT.DEAL_PROPERTY_HISTORY._POLYTOMIC_UPDATED_ATPolytomic (GTM/Ops + Data Platform)TBD2026-03-02 20:50:09GTM/Ops + Data Platform; confirm in Polytomic connector schedule

How to check “when was source X last loaded?”

From repo root:

dbt show \
  --project-dir knowledge/engineering/data-platform/dbt \
  --profiles-dir knowledge/engineering/data-platform/dbt \
  --inline "select 'quickbooks' as source_name, to_varchar(max(_POLYTOMIC_UPDATED_AT), 'YYYY-MM-DD HH24:MI:SS') as last_loaded_at from RAW.POLYTOMIC_QUICKBOOKS.INVOICES"

For a multi-source snapshot, use:

select 'clockify' as source_name, to_varchar(max(updated_at), 'YYYY-MM-DD HH24:MI:SS') as last_loaded_at
from RAW.CLOCKIFY.TIME_ENTRIES
union all
select 'operating', to_varchar(max(created_at), 'YYYY-MM-DD HH24:MI:SS')
from RAW.OPERATING_IO.RAW_TIME_ENTRIES
union all
select 'quickbooks', to_varchar(max(_POLYTOMIC_UPDATED_AT), 'YYYY-MM-DD HH24:MI:SS')
from RAW.POLYTOMIC_QUICKBOOKS.INVOICES
union all
select 'hubspot', to_varchar(max(_POLYTOMIC_UPDATED_AT), 'YYYY-MM-DD HH24:MI:SS')
from RAW.POLYTOMIC_HUBSPOT.DEAL_PROPERTY_HISTORY;

2) Generate and view dbt lineage docs

From repo root:

dbt docs generate \
  --project-dir knowledge/engineering/data-platform/dbt \
  --profiles-dir knowledge/engineering/data-platform/dbt

Artifacts are written to:

  • knowledge/engineering/data-platform/dbt/target/manifest.json
  • knowledge/engineering/data-platform/dbt/target/catalog.json
  • knowledge/engineering/data-platform/dbt/target/index.html

To serve docs locally:

dbt docs serve \
  --project-dir knowledge/engineering/data-platform/dbt \
  --profiles-dir knowledge/engineering/data-platform/dbt \
  --port 8080

Then open: http://localhost:8080

3) Current lineage map (source → staging → marts)

Source → staging models

Raw source table(s)Staging model(s)
RAW.CLOCKIFY.TIME_ENTRIESstg_clockify_time_entries
RAW.OPERATING_IO.RAW_TIME_ENTRIES, RAW.OPERATING_IO.RAW_PERSONS, RAW.OPERATING_IO.RAW_PROJECTSstg_operating_time_entries
RAW.POLYTOMIC_QUICKBOOKS.INVOICESstg_quickbooks_invoices
RAW.POLYTOMIC_QUICKBOOKS.BILLSstg_quickbooks_bills
RAW.POLYTOMIC_QUICKBOOKS.PAYMENTSstg_quickbooks_payments
RAW.POLYTOMIC_QUICKBOOKS.JOURNAL_ENTRIESstg_quickbooks_journal_entries
RAW.POLYTOMIC_HUBSPOT.DEAL_PROPERTY_HISTORYstg_hubspot_deal_property_history
RAW.POLYTOMIC_HUBSPOT.COMPANY_PROPERTY_HISTORYstg_hubspot_company_property_history

Staging → mart models

Mart modelUpstream staging dependenciesUpstream raw sources
mart_delivery_time_effortstg_clockify_time_entries, stg_operating_time_entriesRAW.CLOCKIFY.TIME_ENTRIES, RAW.OPERATING_IO.RAW_TIME_ENTRIES, RAW.OPERATING_IO.RAW_PERSONS, RAW.OPERATING_IO.RAW_PROJECTS

Notes:

  • As of this update, QuickBooks and HubSpot staging models are present, but no finance mart is materialized yet in this dbt project.
  • For full graph edges, use dbt docs lineage graph (index.html or dbt docs serve).

Quick answers

  • When was QuickBooks last loaded?
    Use max(_POLYTOMIC_UPDATED_AT) from RAW.POLYTOMIC_QUICKBOOKS.INVOICES.
    Last checked in this repo update: 2026-03-04 12:00:49 (UTC).

  • What feeds mart_delivery_time_effort?
    Direct: stg_clockify_time_entries and stg_operating_time_entries.
    Raw lineage: RAW.CLOCKIFY.TIME_ENTRIES, RAW.OPERATING_IO.RAW_TIME_ENTRIES, RAW.OPERATING_IO.RAW_PERSONS, RAW.OPERATING_IO.RAW_PROJECTS.

Graph View