Transforming Invoice Data with dbt Cloud

dbt (data build tool) is the standard tool for transforming data inside your warehouse. It takes raw source tables — like the invoice and payment tables that TallyArc writes to Snowflake or BigQuery — and transforms them into clean, documented, tested analytics models that your BI tool and data consumers can trust.

The TallyArc → dbt workflow

1. TallyArc syncs raw invoice data to your warehouse (Snowflake, BigQuery, Redshift, Databricks)
2. TallyArc triggers your dbt Cloud job via webhook after each sync
3. dbt runs your transformation models, building staging, intermediate, and mart layers on top of the raw data
4. Your BI tool queries the dbt-built marts — clean, documented, with data quality tests already run

Triggering dbt jobs from TallyArc

1. In dbt Cloud, go to Account Settings → API Access and generate an API key
2. Note your account ID (visible in the dbt Cloud URL) and the job ID you want to trigger
3. In TallyArc, go to Data → dbt Cloud → Connect
4. Enter your account ID and API key
5. Set the trigger: run the dbt job after every TallyArc sync, or on a schedule

Recommended dbt models for invoice data

stg_invoices.sql — staging model

SELECT
  id AS invoice_id,
  client_id,
  UPPER(status) AS status,
  CAST(total_amount AS NUMERIC) AS total_amount,
  CAST(issue_date AS DATE) AS issue_date,
  CAST(due_date AS DATE) AS due_date,
  CAST(paid_date AS DATE) AS paid_date
FROM {{ source('tallyarc', 'invoices') }}

fct_ar_aging.sql — AR aging mart

SELECT
  invoice_id,
  client_id,
  total_amount,
  issue_date,
  due_date,
  CURRENT_DATE - due_date AS days_overdue,
  CASE
    WHEN CURRENT_DATE <= due_date THEN 'current'
    WHEN CURRENT_DATE - due_date <= 30 THEN '1-30 days'
    WHEN CURRENT_DATE - due_date <= 60 THEN '31-60 days'
    WHEN CURRENT_DATE - due_date <= 90 THEN '61-90 days'
    ELSE '90+ days'
  END AS aging_bucket
FROM {{ ref('stg_invoices') }}
WHERE status != 'paid'

Adding data quality tests

dbt's test framework catches data issues before they reach your dashboards:

# schema.yml
models:
  - name: stg_invoices
    columns:
      - name: invoice_id
        tests: [unique, not_null]
      - name: total_amount
        tests: [{dbt_utils.accepted_range: {min_value: 0}}]
      - name: status
        tests: [{accepted_values: {values: ['DRAFT','SENT','PAID','OVERDUE']}}]