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

This is the second 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 (You are here)
• Part 3: Budgets Table
• Part 4: Cash Movements Table
• Part 5: Expenses Table
• Part 6: Revenue Table

List of tables in the quickbooks_shadow schema.

This is the schema creation script for the shadow schema. I’ll outline how I load the data in a separate post and link it here when it is available.

create schema quickbooks_shadow;

create table quickbooks_shadow.accounts (
	name varchar(1000),
	subaccount boolean,
	parentref_value varchar(200),
	fullyqualifiedname varchar(200),
	active boolean,
	classification varchar(200),
	accounttype varchar(200),
	accountsubtype varchar(200),
	currentbalance float,
	currentbalancewithsubaccounts float,
	currencyref_value varchar(200),
	currencyref_name varchar(200),
	domain varchar(200),
	sparse boolean,
	id int,
	metadata_createtime varchar(200),
	metadata_lastupdatedtime varchar(200)
);

create table quickbooks_shadow.creditmemos (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.invoices (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.journalentries (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.refundreceipts (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.salesreceipts (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.deposits (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.creditmemolines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.refundreceiptlines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.invoicelines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.salesreceiptlines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	salesitemlinedetail_itemref_value varchar(200),
	salesitemlinedetail_itemref_name varchar(200),
	salesitemlinedetail_unitprice float,
	salesitemlinedetail_qty float
);

create table quickbooks_shadow.journalentrylines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	journalentrylinedetail_accountref_value varchar(200),
	journalentrylinedetail_accountref_name varchar(200),
	journalentrylinedetail_entity_entityref_name varchar(200),
	journalentrylinedetail_entity_entityref_value varchar(200),
	journalentrylinedetail_entity_type varchar(200),
	journalentrylinedetail_postingtype varchar(200)
);

create table quickbooks_shadow.depositlines (
	parent_id varchar(200),
	id varchar(200),
	linenum int,
	description varchar(1000),
	amount float,
	depositlinedetail_accountref_value varchar(200),
	depositlinedetail_accountref_name varchar(200),
	depositlinedetail_entity_entityref_name varchar(200),
	depositlinedetail_entity_entityref_value varchar(200),
	depositlinedetail_entity_type varchar(200),
	depositlinedetail_postingtype varchar(200)
);


create table quickbooks_shadow.payments (
	id varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.purchases (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.bills (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.billpayments (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.vendorcredits (
	id varchar(200),
	docnumber varchar(200),
	txndate varchar(200)
);

create table quickbooks_shadow.paymentlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.purchaselines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.billlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.billpaymentlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

create table quickbooks_shadow.vendorcreditlines (
	parent_id varchar(200),
	id varchar(200),
	amount float
);

grant all on schema quickbooks_shadow to <USERNAME>;
grant all on all tables in schema quickbooks_shadow to <USERNAME>;

Up Next:

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

Previous Post:

Simplifying Stitch ETL’s Quickbooks Data in Redshift: Introduction