Complete Data Model

Load 'Order Line', 'Order Status' & 'Product' data

Complete the data model by creating tables and loading data for :

• order_line
• order_status
• product

CREATE TABLE $schema_name.order_line (
	order_line_id INTEGER,
	order_id INTEGER,
	product_id INTEGER,
	quantity INTEGER
);

CREATE TABLE $schema_name.order_status (
	order_status_id INTEGER,
	status_name TEXT
);

CREATE TABLE $schema_name.product (
	product_id INTEGER,
	product_name TEXT,
	selling_price REAL,
	cost_price REAL
);

Query the Data Model

TIP : Use SELECT LEFT(order_date::text,7) to get date in Year_Month (i.e YYYY_MM) format

1. Return Total Order Amount by Month ( i.e Year_Month | Total_Order_Amount )
2. Return Total Order Amount by Month & Product (i.e Year_Month | Product_Name | Total_Order_Amount)
3. Return a List of Customers whose Orders than have not yet been dispatched (i.e Customer_Name | Date | Order_Amount)

Analyze the data in dbt

Uncover insights and trends in the data (assume today is 30th June 2019). Tell us more about the Company!

Generate the models as we did on the day and create marts to show the insights you have generated

Send us the DAG and insights you have generated

Useful links