Simplifying Stitch ETL’s Quickbooks Data in Redshift: Budgets Table

This is the third post of a six-part series of posts explaining how we make analyzing Quickbooks data easier by creating cached data sets from complex queries, resulting in simplified tables that anyone at our company is able to easily understand.

• Part 1: Introduction + Shadow Schema Strategy Explanation
• Part 2: The Quickbooks Shadow Schema
• Part 3: Budgets Table (You are here)
• Part 4: Cash Movements Table
• Part 5: Expenses Table
• Part 6: Revenue Table

This is the Budgets table, which is a rather simple query that just creates a cleaner table summarizing the budgets for each account in Quickbooks.

SELECT budget.name AS "Budget Name",
budget.startdate AS "Budget Start Date",
budget.enddate AS "Budget End Date",
account.acctnum || ' ' || account.fullyqualifiedname AS "Account",
detail.amount AS "Amount",
detail.budgetdate AS "For Date"
FROM quickbooks.quickbooks_budgets budget
JOIN quickbooks.quickbooks_budgets__budgetdetail detail ON budget.id = detail._sdc_source_key_id
JOIN quickbooks.quickbooks_accounts account ON detail.accountref__value = account.id


Up Next:

Simplifying Stitch ETL’s Quickbooks Data in Redshift: Cash Movements Table

Previous Post:

Simplifying Stitch ETL’s Quickbooks Data in Redshift: The Shadow Schema