import duckdb
import os
cwd = os.getcwd()Imports
Olist connects small businesses from all over Brazil to channels without hassle and with a single contract. Those merchants are able to sell their products through the Olist Store and ship them directly to the customers using Olist logistics partners.
Basically, the customer flow is 1. customers browse through products provided by various sellers. 2. If they like them, they’ll order. They can have multiple order_items. 3. Once customers are ready, they’ll head through order_payments process and wait for delivery. 4. After customers confirm that their received their items OR after estimated delivary date, customers will be given prompt to order_reviews.
Staging
Choosing which tables and columns to use and making sure they are casted into the right type
stg_customers = f'''
CREATE OR REPLACE VIEW stg_customers AS
SELECT
customer_id::varchar AS customer_id,
customer_unique_id::varchar AS customer_unique_id,
customer_state::varchar AS customer_state
FROM read_csv_auto('../raw_data/olist_customers_dataset.csv')
'''
stg_products = f'''
CREATE OR REPLACE VIEW stg_products AS
SELECT
product_id::varchar AS product_id,
COALESCE(product_category_name_english, products.product_category_name)::varchar AS category_name
FROM read_csv_auto('../raw_data/olist_products_dataset.csv') AS products
LEFT JOIN read_csv_auto('../raw_data/product_category_name_translation.csv') AS category_translation ON products.product_category_name = category_translation.product_category_name
'''
stg_orders = f'''
CREATE OR REPLACE VIEW stg_orders AS
SELECT
order_id::varchar AS order_id,
order_status::varchar AS order_status,
customer_id::varchar AS customer_id,
order_purchase_timestamp::timestamp AS order_purchase,
order_estimated_delivery_date::timestamp AS order_estimated_delivery,
order_delivered_carrier_date::timestamp AS order_delivered_carrier,
order_delivered_customer_date::timestamp AS order_delivered_customer
FROM read_csv_auto('../raw_data/olist_orders_dataset.csv') AS orders
'''
stg_order_items = f'''
CREATE OR REPLACE VIEW stg_order_items AS
SELECT
order_id::varchar AS order_id,
product_id::varchar AS product_id,
seller_id::varchar AS seller_id,
price::decimal(10,2) AS price,
freight_value::decimal(10,2) AS freight_value
FROM read_csv_auto('../raw_data/olist_order_items_dataset.csv') AS order_items
'''
stg_order_payments = f'''
CREATE OR REPLACE VIEW stg_order_payments AS
SELECT
order_id::varchar AS order_id,
payment_value::decimal(10,2) AS payment_value,
payment_installments::smallint AS payment_installments,
payment_type::varchar AS payment_type
FROM read_csv_auto('../raw_data/olist_order_payments_dataset.csv') AS order_payments
'''
stg_order_reviews = f'''
CREATE OR REPLACE VIEW stg_order_reviews AS
SELECT
review_id::varchar AS review_id,
order_id::varchar AS order_id,
review_score::smallint AS review_score,
review_answer_timestamp::timestamp AS review_answer
FROM read_csv_auto('../raw_data/olist_order_reviews_dataset.csv') AS order_reviews
'''
if os.path.exists(f"{cwd}/../sql/olist.db"):
os.remove(f"{cwd}/../sql/olist.db")
con = duckdb.connect(f"{cwd}/../sql/olist.db")
con.execute(open(f"{cwd}/../sql/01_staging.sql").read())<_duckdb.DuckDBPyConnection at 0x10bf415f0>Dashboard Sets
A possible dashboard set is product - category satisfaction.
The overview, frontpage would be the general health. Which would be, daily checkouts, unique customers, how many items bought, how many sellers involved in checkouts (a bit obscure). How many unique items (if the company care about expanding reach).
Customers retention per category. Explains what categories drive purchasing habit from customers (Maybe a bit weird because various categories would have different periods if they have that property)
Categories. Find out what categories are dominating, How much each categories contributing in revenue, Reviews per category.
Deliveries. Successful deliveries, time to deliver and reviews
Let us check if any of these are feasible with our data
Overview Page
General information about the platform’s health.
overview_sql = f'''
WITH categories AS (
SELECT
product_id,
category_name
FROM stg_products AS products
),
order_summary AS (
SELECT
order_id,
COUNT(*) AS item_count,
product_id,
seller_id,
SUM(price) AS price,
SUM(freight_value) AS freight_value
FROM stg_order_items
GROUP BY order_id, product_id, seller_id
)
SELECT
DATE(orders.order_purchase) AS order_date,
orders.order_id,
customers.customer_unique_id,
orders.order_status,
order_summary.product_id,
order_summary.item_count,
order_summary.seller_id,
order_summary.price,
order_summary.freight_value,
COALESCE(categories.category_name, 'Uncategorized') AS category_name
FROM stg_orders AS orders
INNER JOIN order_summary ON orders.order_id = order_summary.order_id
LEFT JOIN categories ON order_summary.product_id = categories.product_id
LEFT JOIN stg_customers AS customers ON orders.customer_id = customers.customer_id
'''
con.sql(f"CREATE OR REPLACE TABLE overview AS {overview_sql}")
con.sql(f"SELECT * FROM overview LIMIT 5")┌────────────┬──────────────────────────────────┬──────────────────────────────────┬──────────────┬──────────────────────────────────┬────────────┬──────────────────────────────────┬───────────────┬───────────────┬───────────────────────────┐
│ order_date │ order_id │ customer_unique_id │ order_status │ product_id │ item_count │ seller_id │ price │ freight_value │ category_name │
│ date │ varchar │ varchar │ varchar │ varchar │ int64 │ varchar │ decimal(38,2) │ decimal(38,2) │ varchar │
├────────────┼──────────────────────────────────┼──────────────────────────────────┼──────────────┼──────────────────────────────────┼────────────┼──────────────────────────────────┼───────────────┼───────────────┼───────────────────────────┤
│ 2018-08-15 │ 0028de0ca693a1bb26448916a81105cc │ f94d7c267f1bb6956a0c6c6fef65da8b │ delivered │ 059344baebbeaa42fa9f2bbe11b1583e │ 1 │ 955fee9216a65b617aa5c0531780ce60 │ 29.99 │ 15.31 │ construction_tools_lights │
│ 2018-05-02 │ 0029f17cf0e7640c5cb6825af681303f │ 83e1722d7fa45e1393a1226cd68c46b1 │ delivered │ 01c666c82f414c762ad21bffa56e8b49 │ 1 │ d3dcf0604eabf0224fbd5948b5e02f69 │ 94.90 │ 18.54 │ toys │
│ 2017-05-18 │ 003324c70b19a16798817b2b3640e721 │ fbe6316a06058c651539cbf59ec5a0ef │ delivered │ 2b939dc9b176d7fa21594d588815d4a4 │ 2 │ dbc22125167c298ef99da25668e1011f │ 205.80 │ 28.90 │ luggage_accessories │
│ 2017-08-10 │ 0045e3085f083f0f38d24bb3f22e6593 │ 5eb8cc30e45a67bdb05240e7e735967d │ delivered │ 9545d45c37449ccbc376de3a04c66e71 │ 1 │ 431af27f296bc6519d890aa5a05fdb11 │ 116.90 │ 13.84 │ health_beauty │
│ 2018-05-03 │ 0079bca8e89bd52fdb87408e4f3fb94d │ 9bc0afc85b2f40c797f96f7911397708 │ delivered │ 19421075ae0b585f2dc13ff149e2119d │ 1 │ 4c2b230173bb36f9b240f2b8ac11786e │ 49.90 │ 7.39 │ sports_leisure │
└────────────┴──────────────────────────────────┴──────────────────────────────────┴──────────────┴──────────────────────────────────┴────────────┴──────────────────────────────────┴───────────────┴───────────────┴───────────────────────────┘Sanity checks:
Payment total from overview derived from order_items, matches order_payments
con.sql(f'''
WITH payment_table AS (
SELECT order_id, ROUND(SUM(payment_value), 2)::DECIMAL(10,2) AS payment_total, MAX(payment_installments) AS payment_installments
FROM stg_order_payments
GROUP BY order_id
)
SELECT
ROUND(MEDIAN(payment_table.payment_total/overview_agg.payment_total), 2)::DECIMAL(10,2) AS error_ratio_median,
payment_installments,
COUNT(*) AS count
FROM (
SELECT order_id, SUM(price+freight_value) AS payment_total
FROM overview
GROUP BY order_id
) AS overview_agg
LEFT JOIN payment_table ON overview_agg.order_id = payment_table.order_id
WHERE overview_agg.payment_total != payment_table.payment_total AND ABS(overview_agg.payment_total - payment_table.payment_total) > 0.02
GROUP BY payment_installments
ORDER BY payment_installments
''')┌────────────────────┬──────────────────────┬───────┐
│ error_ratio_median │ payment_installments │ count │
│ decimal(10,2) │ int16 │ int64 │
├────────────────────┼──────────────────────┼───────┤
│ 0.97 │ 1 │ 20 │
│ 1.03 │ 2 │ 12 │
│ 1.05 │ 3 │ 35 │
│ 1.06 │ 4 │ 37 │
│ 1.07 │ 5 │ 32 │
│ 1.08 │ 6 │ 31 │
│ 1.13 │ 7 │ 18 │
│ 1.13 │ 8 │ 16 │
│ 1.13 │ 9 │ 7 │
│ 1.13 │ 10 │ 35 │
│ 1.10 │ 11 │ 4 │
│ 1.16 │ 12 │ 14 │
│ 1.24 │ 13 │ 1 │
│ 1.24 │ 15 │ 2 │
│ 1.24 │ 20 │ 1 │
│ 1.25 │ 21 │ 1 │
│ 1.29 │ 24 │ 1 │
└────────────────────┴──────────────────────┴───────┘
17 rows 3 columnsMost likely credit card interest that causes discrepencies. Normally, we would decide that total from order_items should be the one used since bank interest is none of our concern. However, for one installments, the median is 0.97, which should not happen. There are 20 discrepencies for 1 installments, let us take a look first before deciding anything
con.sql(f'''
WITH payment_table AS (
SELECT order_id, ROUND(SUM(payment_value), 2)::DECIMAL(10,2) AS payment_total, MAX(payment_installments) AS payment_installments,
ARRAY_AGG(payment_type) AS payment_type
FROM stg_order_payments
GROUP BY order_id
),
categories AS (
SELECT product_id, category_name
FROM stg_products AS products
)
SELECT
payment_type, overview_agg.payment_total, payment_table.payment_total
FROM (
SELECT order_id, SUM(price+freight_value) AS payment_total
FROM overview
GROUP BY order_id
) AS overview_agg
LEFT JOIN payment_table ON overview_agg.order_id = payment_table.order_id
WHERE overview_agg.payment_total != payment_table.payment_total AND ABS(overview_agg.payment_total - payment_table.payment_total) > 0.02
AND payment_installments = 1
ORDER BY payment_installments
''')┌────────────────────────────┬───────────────┬───────────────┐
│ payment_type │ payment_total │ payment_total │
│ varchar[] │ decimal(38,2) │ decimal(10,2) │
├────────────────────────────┼───────────────┼───────────────┤
│ [credit_card, credit_card] │ 28.75 │ 30.30 │
│ [voucher, credit_card] │ 94.59 │ 94.95 │
│ [voucher, credit_card] │ 55.80 │ 57.60 │
│ [credit_card] │ 17.38 │ 16.38 │
│ [credit_card] │ 37.37 │ 34.38 │
│ [debit_card] │ 33.43 │ 30.83 │
│ [debit_card] │ 333.92 │ 318.97 │
│ [credit_card] │ 115.65 │ 105.26 │
│ [debit_card] │ 47.31 │ 45.71 │
│ [debit_card] │ 350.41 │ 333.91 │
│ [credit_card, voucher] │ 44.09 │ 45.42 │
│ [debit_card] │ 59.00 │ 56.50 │
│ [boleto] │ 1972.00 │ 1972.03 │
│ [credit_card, voucher] │ 50.10 │ 50.70 │
│ [credit_card] │ 17.77 │ 18.38 │
│ [credit_card] │ 35.14 │ 25.14 │
│ [boleto] │ 224.14 │ 224.17 │
│ [debit_card] │ 231.92 │ 222.02 │
│ [debit_card] │ 161.47 │ 157.57 │
│ [boleto] │ 524.32 │ 524.28 │
└────────────────────────────┴───────────────┴───────────────┘
20 rows 3 columnsSo we have three payment type that can cause discrepencies and we have no clue what causes it for the first installments. Nevertheless, we determined that the total payments from order_payments table are affected by external causes that does not directly affect our revenue. Hence, we will be using total payments derived from price + freight_value.
It is unfortunate that we do not have a good sanity check in this, but with the limited information that we have, this is all we can do.
NULL values
con.sql(f'''
SUMMARIZE overview
'''
)┌────────────────────┬───────────────┬──────────────────────────────────┬──────────────────────────────────┬───────────────┬────────────────────────────┬────────────────────┬────────────┬────────────┬────────────┬────────┬─────────────────┐
│ column_name │ column_type │ min │ max │ approx_unique │ avg │ std │ q25 │ q50 │ q75 │ count │ null_percentage │
│ varchar │ varchar │ varchar │ varchar │ int64 │ varchar │ varchar │ varchar │ varchar │ varchar │ int64 │ decimal(9,2) │
├────────────────────┼───────────────┼──────────────────────────────────┼──────────────────────────────────┼───────────────┼────────────────────────────┼────────────────────┼────────────┼────────────┼────────────┼────────┼─────────────────┤
│ order_date │ DATE │ 2016-09-04 │ 2018-09-03 │ 727 │ 2017-12-31 07:14:06.109836 │ NULL │ 2017-09-13 │ 2018-01-19 │ 2018-05-05 │ 102425 │ 0.00 │
│ order_id │ VARCHAR │ 00010242fe8c5a6d1ba2dd792cb16214 │ fffe41c64501cc87c801fd61db3f6244 │ 87618 │ NULL │ NULL │ NULL │ NULL │ NULL │ 102425 │ 0.00 │
│ customer_unique_id │ VARCHAR │ 0000366f3b9a7992bf8c76cfdf3221e2 │ ffffd2657e2aad2907e67c3e9daecbeb │ 93296 │ NULL │ NULL │ NULL │ NULL │ NULL │ 102425 │ 0.00 │
│ order_status │ VARCHAR │ approved │ unavailable │ 7 │ NULL │ NULL │ NULL │ NULL │ NULL │ 102425 │ 0.00 │
│ product_id │ VARCHAR │ 00066f42aeeb9f3007548bb9d3f33c38 │ fffe9eeff12fcbd74a2f2b007dde0c58 │ 24292 │ NULL │ NULL │ NULL │ NULL │ NULL │ 102425 │ 0.00 │
│ item_count │ BIGINT │ 1 │ 20 │ 18 │ 1.0998291432755676 │ 0.4569262566527153 │ 1 │ 1 │ 1 │ 102425 │ 0.00 │
│ seller_id │ VARCHAR │ 0015a82c2db000af6aaaf3ae2ecb0532 │ ffff564a4f9085cd26170f4732393726 │ 3886 │ NULL │ NULL │ NULL │ NULL │ NULL │ 102425 │ 0.00 │
│ price │ DECIMAL(38,2) │ 0.85 │ 13440.00 │ 7557 │ 132.69849841347326 │ 205.30884670665242 │ 45 │ 82 │ 147 │ 102425 │ 0.00 │
│ freight_value │ DECIMAL(38,2) │ 0.00 │ 1794.96 │ 7602 │ 21.98593644129851 │ 20.464658702056713 │ 13 │ 17 │ 23 │ 102425 │ 0.00 │
│ category_name │ VARCHAR │ Uncategorized │ watches_gifts │ 76 │ NULL │ NULL │ NULL │ NULL │ NULL │ 102425 │ 0.00 │
└────────────────────┴───────────────┴──────────────────────────────────┴──────────────────────────────────┴───────────────┴────────────────────────────┴────────────────────┴────────────┴────────────┴────────────┴────────┴─────────────────┘
10 rows 12 columnsSome nulls in category_name
con.sql(f'''
SELECT *
FROM stg_products
WHERE product_id = '4914f8796af2ecd359fd8f44b9b92339'
'''
)┌──────────────────────────────────┬───────────────┐
│ product_id │ category_name │
│ varchar │ varchar │
├──────────────────────────────────┼───────────────┤
│ 4914f8796af2ecd359fd8f44b9b92339 │ NULL │
└──────────────────────────────────┴───────────────┘Nothing we can do about this.
We are in the clear for overview now.
Customers retention
First, we have to confirm whether there are repeat orders from the same customer. Then, we need to see how many are there any how spaced out are they. The first problem that arised is that we have no first purchase timestamp or first sign up timestamp, which discards the option to have a cohort analysis.
con.sql(f'''
SELECT customers.customer_unique_id, COUNT(DISTINCT orders.customer_id) AS order_count
FROM stg_customers AS customers
RIGHT JOIN stg_orders AS orders ON customers.customer_id = orders.customer_id
GROUP BY customer_unique_id
HAVING order_count > 1
ORDER BY order_count DESC
''')┌──────────────────────────────────┬─────────────┐
│ customer_unique_id │ order_count │
│ varchar │ int64 │
├──────────────────────────────────┼─────────────┤
│ 8d50f5eadf50201ccdcedfb9e2ac8455 │ 17 │
│ 3e43e6105506432c953e165fb2acf44c │ 9 │
│ 1b6c7548a2a1f9037c1fd3ddfed95f33 │ 7 │
│ ca77025e7201e3b30c44b472ff346268 │ 7 │
│ 6469f99c1f9dfae7733b25662e7f1782 │ 7 │
│ 63cfc61cee11cbe306bff5857d00bfe4 │ 6 │
│ f0e310a6839dce9de1638e0fe5ab282a │ 6 │
│ dc813062e0fc23409cd255f7f53c7074 │ 6 │
│ de34b16117594161a6a89c50b289d35a │ 6 │
│ 47c1a3033b8b77b3ab6e109eb4d5fdf3 │ 6 │
│ · │ · │
│ · │ · │
│ · │ · │
│ f172c33b146e90b95ee12499eb6632cb │ 2 │
│ 8bc85b1061383d0dcd4e995a726da763 │ 2 │
│ acc374bd9024806a83121b1d2565db2f │ 2 │
│ e68856e8450afe7390b02d1b2bcefb29 │ 2 │
│ 36be413fe0f4a74eee7b764e758730ed │ 2 │
│ 09ab36a420b5c87c5c9772bdb494f2e3 │ 2 │
│ 72421b30e46c895e99e8560a48a33fb6 │ 2 │
│ 73f97c6a5af65a71194cc2265ae82501 │ 2 │
│ 73de7b7b976fc6b75283b05f67fd6b5b │ 2 │
│ d5f79f616536f08c3946ee6ac810a43a │ 2 │
└──────────────────────────────────┴─────────────┘
2997 rows (20 shown) 2 columns~3000 out of ~99500 customers have multiple orders. Not a good amount. But, let’s see the timeline
# cust_uniq_id | order count | mean_gap | median_gap | min_gap | max_gap |
con.sql(f'''
WITH multiple_orders_count AS (
SELECT customers.customer_unique_id, COUNT(DISTINCT orders.customer_id) AS order_count
FROM stg_customers AS customers
RIGHT JOIN stg_orders AS orders ON customers.customer_id = orders.customer_id
GROUP BY customer_unique_id
HAVING order_count > 1
),
next_order_table AS (
SELECT
customers.customer_unique_id,
customers.customer_id,
LEAD(order_purchase::date, 1) OVER (PARTITION BY customers.customer_unique_id ORDER BY order_purchase) - order_purchase::date AS next_order_days
FROM multiple_orders_count
INNER JOIN stg_customers AS customers ON multiple_orders_count.customer_unique_id = customers.customer_unique_id
LEFT JOIN stg_orders AS orders ON customers.customer_id = orders.customer_id
QUALIFY next_order_days IS NOT NULL
)
SELECT
customer_unique_id,
MEAN(next_order_days) AS mean_next_order,
MEDIAN(next_order_days) AS median_next_order,
MIN(next_order_days) AS min_next_order,
MAX(next_order_days) AS max_next_order,
COUNT(*) AS order_count_after_first
FROM next_order_table
GROUP BY customer_unique_id
HAVING order_count_after_first > 1
''')┌──────────────────────────────────┬────────────────────┬───────────────────┬────────────────┬────────────────┬─────────────────────────┐
│ customer_unique_id │ mean_next_order │ median_next_order │ min_next_order │ max_next_order │ order_count_after_first │
│ varchar │ double │ double │ int64 │ int64 │ int64 │
├──────────────────────────────────┼────────────────────┼───────────────────┼────────────────┼────────────────┼─────────────────────────┤
│ 06a52782a04f0086d16b9c22d0e29438 │ 0.0 │ 0.0 │ 0 │ 0 │ 2 │
│ 18e3d276253780b44b5b7bf83f6785ec │ 104.5 │ 104.5 │ 7 │ 202 │ 2 │
│ 1b6e96ed99cb8d135efe220d761bbd67 │ 255.5 │ 255.5 │ 213 │ 298 │ 2 │
│ 310647380793836bfa5b7b6b3f518423 │ 91.5 │ 91.5 │ 54 │ 129 │ 2 │
│ 46958f708ade6b6a88b2e5aca1e66de5 │ 0.0 │ 0.0 │ 0 │ 0 │ 2 │
│ 4dcabe94c61f202327839a734955b0cf │ 1.0 │ 1.0 │ 0 │ 2 │ 2 │
│ 4facc2e6fbc2bffab2fea92d2b4aa7e4 │ 140.33333333333334 │ 20.0 │ 4 │ 397 │ 3 │
│ 50cd97d731f3f5e3f321673c2671a23d │ 48.5 │ 48.5 │ 0 │ 97 │ 2 │
│ 5b09c620db60411b0be6f1817c6cbe71 │ 75.5 │ 75.5 │ 0 │ 151 │ 2 │
│ 6419a1be8feac26ec793667b71cbaeb4 │ 102.5 │ 102.5 │ 56 │ 149 │ 2 │
│ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │
│ 86df00dc5fd68f4dd5d5945ca19f3ed6 │ 63.5 │ 63.5 │ 51 │ 76 │ 2 │
│ 94a19681bf6faa3c6ce3e2df1c2d97cb │ 38.5 │ 38.5 │ 2 │ 75 │ 2 │
│ 9aae8213ff9bc04cc3ce48b85f43a173 │ 0.0 │ 0.0 │ 0 │ 0 │ 2 │
│ acea6bd29b8c1e3c6a8b266a8fb4475e │ 82.33333333333333 │ 73.0 │ 22 │ 152 │ 3 │
│ b9badb100ff8ecc16a403111209e3a06 │ 241.0 │ 241.0 │ 137 │ 345 │ 2 │
│ bf6656ef50e6fd00e7517b01d615b4f3 │ 89.0 │ 89.0 │ 86 │ 92 │ 2 │
│ cd11a0920c498edb1da9ecd2d31aa599 │ 51.5 │ 51.5 │ 0 │ 103 │ 2 │
│ d387ea85dc301a91740e31360d355686 │ 62.5 │ 62.5 │ 62 │ 63 │ 2 │
│ e13e8b789e5a8e6fe1445f924a4ed4f6 │ 0.0 │ 0.0 │ 0 │ 0 │ 2 │
│ e2492e4188991b6276a4a62a287a5451 │ 34.0 │ 34.0 │ 5 │ 63 │ 2 │
└──────────────────────────────────┴────────────────────┴───────────────────┴────────────────┴────────────────┴─────────────────────────┘
252 rows (20 shown) 6 columnsOut of ~3000 customers with multiple orders, only 252 customers that have more than 2 orders. Because there are so few data, the statistics explaining next order in days looked erratic. Wtih all the problems due discovered due to lack of data, we will be dropping this page completely.
Categories
Explaining performances of categories. We want to see how revenue, late deliveries, reviews, and count are spread amongst categories. Unfortunately, for reviews and late deliveries, it is per order, not per item.
con.sql(
"SELECT * FROM stg_order_reviews"
)┌──────────────────────────────────┬──────────────────────────────────┬──────────────┬─────────────────────┐
│ review_id │ order_id │ review_score │ review_answer │
│ varchar │ varchar │ int16 │ timestamp │
├──────────────────────────────────┼──────────────────────────────────┼──────────────┼─────────────────────┤
│ 7bc2406110b926393aa56f80a40eba40 │ 73fc7af87114b39712e6da79b0a377eb │ 4 │ 2018-01-18 21:46:59 │
│ 80e641a11e56f04c1ad469d5645fdfde │ a548910a1c6147796b98fdf73dbeba33 │ 5 │ 2018-03-11 03:05:13 │
│ 228ce5500dc1d8e020d8d1322874b6f0 │ f9e4b658b201a9f2ecdecbb34bed034b │ 5 │ 2018-02-18 14:36:24 │
│ e64fb393e7b32834bb789ff8bb30750e │ 658677c97b385a9be170737859d3511b │ 5 │ 2017-04-21 22:02:06 │
│ f7c4243c7fe1938f181bec41a392bdeb │ 8e6bfb81e283fa7e4f11123a3fb894f1 │ 5 │ 2018-03-02 10:26:53 │
│ 15197aa66ff4d0650b5434f1b46cda19 │ b18dcdf73be66366873cd26c5724d1dc │ 1 │ 2018-04-16 00:39:37 │
│ 07f9bee5d1b850860defd761afa7ff16 │ e48aa0d2dcec3a2e87348811bcfdf22b │ 5 │ 2017-07-18 19:30:34 │
│ 7c6400515c67679fbee952a7525281ef │ c31a859e34e3adac22f376954e19b39d │ 5 │ 2018-08-14 21:36:06 │
│ a3f6f7f6f433de0aefbb97da197c554c │ 9c214ac970e84273583ab523dfafd09b │ 5 │ 2017-05-18 12:05:37 │
│ 8670d52e15e00043ae7de4c01cc2fe06 │ b9bf720beb4ab3728760088589c62129 │ 4 │ 2018-05-23 16:45:47 │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ · │ · │ · │ · │
│ f8ac2ca757c6702a8f7b9f82a3361b54 │ b77e196ee314322dcec6124fa3e38b6b │ 1 │ 2018-09-02 16:27:56 │
│ c5b14992eb69dc68e3e855cd95f0ed57 │ 7b374a3cd8b325fd49e6789ccfa88639 │ 5 │ 2018-05-21 18:16:28 │
│ c210b4ed36c4ce629a8ce851be0d5828 │ de1c6db658e84e0ba847b2c1bc857bb5 │ 1 │ 2018-04-17 22:55:46 │
│ 110160bcffa1c9bef8b1868aba109cf1 │ 930fff097a9427c8a9a70559aa36fa76 │ 5 │ 2018-09-03 00:11:20 │
│ 3dd6dd8bd30dd50a2b15328239cfcd91 │ acfa87544c19d596707d25cf2f7dab61 │ 1 │ 2018-08-27 03:32:14 │
│ 6a1edd1c07b9ceb1a031a9e3d0a0c69a │ 4ac735cd851ba30e7b280f7e045544a0 │ 5 │ 2017-09-30 09:51:15 │
│ a2e0d982abe0a650156ec056ca4d1b85 │ a4244008bc97ae9cdc3807237fa83229 │ 5 │ 2018-07-07 15:50:37 │
│ c5ab0934968065d986a65f00567d4c54 │ 8d74231228bdbc2e89fc0125abfc1c87 │ 3 │ 2017-08-12 03:30:12 │
│ db5a5956ff1b3b42cea50a82a54bf781 │ 1644056a29868cbba3251a81fae0e1f8 │ 2 │ 2017-05-24 13:02:42 │
│ 78fa666f7808563cf40c8eac105316bf │ 2a19516124bb3d5f4d0d07555104e322 │ 4 │ 2017-03-19 21:48:51 │
└──────────────────────────────────┴──────────────────────────────────┴──────────────┴─────────────────────┘
? rows (>9999 rows, 20 shown) 4 columns# order_id | category_name | order_status | item_count | payment_total | delivery_days | is_late | delay_days | review_score | is_single_category
categories_sql = f'''
WITH order_summary AS (
SELECT
order_id,
product_id,
COUNT(*) AS item_count,
SUM(price) AS price,
SUM(freight_value) AS freight_value
FROM stg_order_items AS order_items
GROUP BY order_id, product_id
),
category_table AS (
SELECT products.product_id, category_name
FROM stg_products AS products
),
order_categorized AS (
SELECT
order_id,
COALESCE(category_name, 'Uncategorized') AS category_name,
SUM(item_count) AS item_count,
SUM(price) AS price,
SUM(freight_value) AS freight_value
FROM order_summary
LEFT JOIN category_table ON order_summary.product_id = category_table.product_id
GROUP BY order_id, category_name
),
single_category AS (
SELECT order_id,
CASE
WHEN ARRAY_LENGTH(ARRAY_AGG(DISTINCT category_name)) > 1 THEN FALSE
ELSE TRUE
END AS is_single_category
FROM order_categorized
GROUP BY order_id
),
reviews AS (
SELECT order_id, review_score
FROM stg_order_reviews
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY review_answer DESC) = 1
)
SELECT
orders.order_id,
category_name,
order_status,
item_count,
price,
freight_value,
COALESCE(order_delivered_customer::date - order_purchase::date, NULL) AS delivery_days,
order_delivered_customer::date - order_estimated_delivery::date AS delay_days,
CASE
WHEN delay_days > 0 THEN TRUE
WHEN delay_days <= 0 THEN FALSE
ELSE NULL
END AS is_late,
review_score,
is_single_category
FROM stg_orders AS orders
LEFT JOIN order_categorized ON orders.order_id = order_categorized.order_id
LEFT JOIN single_category ON orders.order_id = single_category.order_id
LEFT JOIN reviews AS reviews ON orders.order_id = reviews.order_id
'''
con.sql(f"CREATE OR REPLACE TABLE category AS {categories_sql}")
con.sql(f"SELECT * FROM category")┌──────────────────────────────────┬───────────────────────────┬──────────────┬────────────┬───────────────┬───────────────┬───────────────┬────────────┬─────────┬──────────────┬────────────────────┐
│ order_id │ category_name │ order_status │ item_count │ price │ freight_value │ delivery_days │ delay_days │ is_late │ review_score │ is_single_category │
│ varchar │ varchar │ varchar │ int128 │ decimal(38,2) │ decimal(38,2) │ int64 │ int64 │ boolean │ int16 │ boolean │
├──────────────────────────────────┼───────────────────────────┼──────────────┼────────────┼───────────────┼───────────────┼───────────────┼────────────┼─────────┼──────────────┼────────────────────┤
│ bb05bd3bbacf1e3c6026b43b44a6631c │ sports_leisure │ delivered │ 1 │ 22.30 │ 8.88 │ 8 │ -10 │ false │ 5 │ true │
│ bb9a1bc465b8456a8d36e06370273b58 │ health_beauty │ delivered │ 1 │ 61.99 │ 60.49 │ 27 │ 4 │ true │ 4 │ true │
│ bbfb7c72fb40d3623c225c5b1bf1e989 │ watches_gifts │ delivered │ 1 │ 49.00 │ 8.72 │ 8 │ -12 │ false │ 4 │ true │
│ be86cd9f96e674ebdbb586454780a1ea │ computers_accessories │ delivered │ 1 │ 52.00 │ 15.11 │ 8 │ -29 │ false │ 3 │ true │
│ c32dd42a4b8a42c008777be9de862c10 │ telephony │ delivered │ 1 │ 26.99 │ 15.65 │ 13 │ -14 │ false │ 4 │ true │
│ c32fc9ccb03292ac2e7a2c2f027394ff │ watches_gifts │ delivered │ 1 │ 49.00 │ 15.44 │ 7 │ -21 │ false │ 4 │ true │
│ c575d4ac51a449488b640e19092ab952 │ consoles_games │ delivered │ 1 │ 32.50 │ 11.85 │ 9 │ -10 │ false │ 5 │ true │
│ c5f43352377291361dad9a7366ae1c89 │ telephony │ delivered │ 1 │ 24.99 │ 16.79 │ 135 │ 109 │ true │ 5 │ true │
│ c6df71a18bb2d9e7d88ee3e4d7080982 │ telephony │ delivered │ 1 │ 11.97 │ 7.39 │ 13 │ -5 │ false │ 4 │ true │
│ c6f02030f6bc633b8073c50178eb2833 │ construction_tools_lights │ delivered │ 1 │ 169.99 │ 23.99 │ 8 │ -21 │ false │ 5 │ true │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 8d585a78c58bc6ee2c28323cfd8ce6c7 │ bed_bath_table │ delivered │ 1 │ 99.90 │ 21.19 │ 24 │ -2 │ false │ 5 │ true │
│ 9562a9a598a619a1726397eb5246e623 │ housewares │ delivered │ 1 │ 26.40 │ 15.29 │ 7 │ -17 │ false │ 5 │ true │
│ 97b6ab675f38dce08fc58f7e5ddfa1a7 │ baby │ delivered │ 1 │ 240.00 │ 35.48 │ 42 │ -1 │ false │ 3 │ true │
│ 99e1d586758dee362e01030739d3d87a │ bed_bath_table │ delivered │ 1 │ 56.99 │ 22.98 │ 19 │ -3 │ false │ 4 │ true │
│ 9a3966c23190dbdbaabed08e8429c006 │ Uncategorized │ delivered │ 1 │ 3980.00 │ 62.74 │ 5 │ -50 │ false │ 5 │ true │
│ 9add7e9338f3b8cac642ce405bb64655 │ furniture_decor │ delivered │ 1 │ 399.99 │ 193.78 │ 18 │ -12 │ false │ 5 │ true │
│ a08c01d5f51d5678f97d00ad2f228f8f │ housewares │ delivered │ 1 │ 75.00 │ 13.08 │ 2 │ -16 │ false │ 5 │ true │
│ a1119e009d1d91655405307f4c095818 │ drinks │ delivered │ 1 │ 58.00 │ 7.67 │ 11 │ 2 │ true │ 1 │ true │
│ a1eca0e86d2739a6adeb18015e4bc249 │ baby │ delivered │ 1 │ 50.00 │ 16.11 │ 18 │ -10 │ false │ 5 │ true │
│ a4277113bcf10ed364a0182042fc8155 │ housewares │ delivered │ 1 │ 44.90 │ 23.28 │ 9 │ -16 │ false │ 5 │ true │
└──────────────────────────────────┴───────────────────────────┴──────────────┴────────────┴───────────────┴───────────────┴───────────────┴────────────┴─────────┴──────────────┴────────────────────┘
? rows (>9999 rows, 20 shown) 11 columnsSanity checks
NULL values
con.sql(f'''
SUMMARIZE category
'''
)┌────────────────────┬───────────────┬──────────────────────────────────┬──────────────────────────────────┬───────────────┬─────────────────────┬────────────────────┬─────────┬─────────┬─────────┬────────┬─────────────────┐
│ column_name │ column_type │ min │ max │ approx_unique │ avg │ std │ q25 │ q50 │ q75 │ count │ null_percentage │
│ varchar │ varchar │ varchar │ varchar │ int64 │ varchar │ varchar │ varchar │ varchar │ varchar │ int64 │ decimal(9,2) │
├────────────────────┼───────────────┼──────────────────────────────────┼──────────────────────────────────┼───────────────┼─────────────────────┼────────────────────┼─────────┼─────────┼─────────┼────────┼─────────────────┤
│ order_id │ VARCHAR │ 00010242fe8c5a6d1ba2dd792cb16214 │ fffe41c64501cc87c801fd61db3f6244 │ 87618 │ NULL │ NULL │ NULL │ NULL │ NULL │ 100245 │ 0.00 │
│ category_name │ VARCHAR │ Uncategorized │ watches_gifts │ 76 │ NULL │ NULL │ NULL │ NULL │ NULL │ 100245 │ 0.77 │
│ order_status │ VARCHAR │ approved │ unavailable │ 9 │ NULL │ NULL │ NULL │ NULL │ NULL │ 100245 │ 0.00 │
│ item_count │ HUGEINT │ 1 │ 21 │ 18 │ 1.1325022619885392 │ 0.522268687362423 │ 1 │ 1 │ 1 │ 100245 │ 0.77 │
│ price │ DECIMAL(38,2) │ 0.85 │ 13440.00 │ 7753 │ 136.64063235146276 │ 209.5685056184807 │ 46 │ 86 │ 150 │ 100245 │ 0.77 │
│ freight_value │ DECIMAL(38,2) │ 0.00 │ 1794.96 │ 7635 │ 22.639082537448477 │ 21.430904326478046 │ 14 │ 17 │ 24 │ 100245 │ 0.77 │
│ delivery_days │ BIGINT │ 0 │ 210 │ 155 │ 12.473287072731946 │ 9.536871750189059 │ 7 │ 10 │ 16 │ 100245 │ 2.96 │
│ delay_days │ BIGINT │ -147 │ 188 │ 191 │ -11.913060909791827 │ 10.18037246428974 │ -17 │ -12 │ -7 │ 100245 │ 2.96 │
│ is_late │ BOOLEAN │ false │ true │ 2 │ NULL │ NULL │ NULL │ NULL │ NULL │ 100245 │ 2.96 │
│ review_score │ SMALLINT │ 1 │ 5 │ 5 │ 4.078032793477496 │ 1.3539790874408633 │ 4 │ 5 │ 5 │ 100245 │ 0.77 │
│ is_single_category │ BOOLEAN │ false │ true │ 2 │ NULL │ NULL │ NULL │ NULL │ NULL │ 100245 │ 0.77 │
└────────────────────┴───────────────┴──────────────────────────────────┴──────────────────────────────────┴───────────────┴─────────────────────┴────────────────────┴─────────┴─────────┴─────────┴────────┴─────────────────┘
11 rows 12 columnsWe have 0.77 null percentage for (category_name, item_count, payment_total, review_score, is_single_category) and 2.96 null percentage (delivery_days, delay_days, is_late).
con.sql(f'''
SELECT order_status, COUNT(*) AS null_count
FROM category
LEFT JOIN stg_order_items AS order_items ON category.order_id = order_items.order_id
WHERE is_single_category IS NULL
GROUP BY order_status
'''
)┌──────────────┬────────────┐
│ order_status │ null_count │
│ varchar │ int64 │
├──────────────┼────────────┤
│ shipped │ 1 │
│ invoiced │ 2 │
│ created │ 5 │
│ unavailable │ 603 │
│ canceled │ 164 │
└──────────────┴────────────┘There are no nulls in delivered orders. These nulls are caused by missing product_id. Hence, we will left it as is since we do not have anything to fill it with.
Deliveries
It seems that most of the data required are already in the categories table. However, we will be adding a couple more columns.
# order_id | category_name | order_status | item_count | payment_total | delivery_days | is_late | delay_days | review_score | is_single_category
categories_sql = f'''
WITH order_summary AS (
SELECT
order_id,
product_id,
COUNT(*) AS item_count,
SUM(price) AS price,
SUM(freight_value) AS freight_value
FROM stg_order_items AS order_items
GROUP BY order_id, product_id
),
category_table AS (
SELECT products.product_id, category_name
FROM stg_products AS products
),
order_categorized AS (
SELECT
order_id,
COALESCE(category_name, 'Uncategorized') AS category_name,
SUM(item_count) AS item_count,
SUM(price) AS price,
SUM(freight_value) AS freight_value
FROM order_summary
LEFT JOIN category_table ON order_summary.product_id = category_table.product_id
GROUP BY order_id, category_name
),
single_category AS (
SELECT order_id,
CASE
WHEN ARRAY_LENGTH(ARRAY_AGG(DISTINCT category_name)) > 1 THEN FALSE
ELSE TRUE
END AS is_single_category
FROM order_categorized
GROUP BY order_id
),
reviews AS (
SELECT order_id, review_score
FROM stg_order_reviews
QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY review_answer DESC) = 1
)
SELECT
orders.order_id,
category_name,
order_status,
item_count,
price,
freight_value,
COALESCE(order_delivered_carrier::date - order_purchase::date, NULL) AS fulfilment_days,
COALESCE(order_delivered_customer::date - order_delivered_carrier::date, NULL) AS delivery_days,
order_delivered_customer::date - order_estimated_delivery::date AS delay_days,
fulfilment_days + delivery_days AS order_completion_days,
order_completion_days - delay_days AS estimated_completion_days,
CASE
WHEN delay_days > 0 THEN TRUE
WHEN delay_days <= 0 THEN FALSE
ELSE NULL
END AS is_late,
review_score,
is_single_category
FROM stg_orders AS orders
LEFT JOIN order_categorized ON orders.order_id = order_categorized.order_id
LEFT JOIN single_category ON orders.order_id = single_category.order_id
LEFT JOIN reviews AS reviews ON orders.order_id = reviews.order_id
'''
con.sql(f"CREATE OR REPLACE TABLE categories_deliveries AS {categories_sql}")
con.sql(f"SELECT * FROM categories_deliveries")┌──────────────────────────────────┬──────────────────────────┬──────────────┬────────────┬───────────────┬───────────────┬─────────────────┬───────────────┬────────────┬───────────────────────┬───────────────────────────┬─────────┬──────────────┬────────────────────┐
│ order_id │ category_name │ order_status │ item_count │ price │ freight_value │ fulfilment_days │ delivery_days │ delay_days │ order_completion_days │ estimated_completion_days │ is_late │ review_score │ is_single_category │
│ varchar │ varchar │ varchar │ int128 │ decimal(38,2) │ decimal(38,2) │ int64 │ int64 │ int64 │ int64 │ int64 │ boolean │ int16 │ boolean │
├──────────────────────────────────┼──────────────────────────┼──────────────┼────────────┼───────────────┼───────────────┼─────────────────┼───────────────┼────────────┼───────────────────────┼───────────────────────────┼─────────┼──────────────┼────────────────────┤
│ 26a44b28ac942b09a771479c880882d9 │ garden_tools │ delivered │ 1 │ 59.90 │ 17.67 │ 3 │ 7 │ -20 │ 10 │ 30 │ false │ 5 │ true │
│ 284039cca9a61886953286f5dae55fdf │ cool_stuff │ delivered │ 1 │ 199.00 │ 18.00 │ 1 │ 8 │ -26 │ 9 │ 35 │ false │ 5 │ true │
│ 29b71dc3837a954158a1d1b30d0605bc │ bed_bath_table │ delivered │ 1 │ 203.00 │ 25.03 │ 2 │ 7 │ -10 │ 9 │ 19 │ false │ 1 │ true │
│ 2b2e646f2556658e3d06c53ee0465153 │ furniture_living_room │ delivered │ 1 │ 116.94 │ 9.58 │ 7 │ 5 │ -15 │ 12 │ 27 │ false │ 2 │ false │
│ 2c45c33d2f9cb8ff8b1c86cc28c11c30 │ fashion_bags_accessories │ canceled │ 1 │ 135.00 │ 18.51 │ 5 │ 26 │ -29 │ 31 │ 60 │ false │ 1 │ true │
│ 30647325eed2f0ac046e27a3cb6b76fc │ market_place │ delivered │ 1 │ 339.74 │ 41.01 │ 1 │ 21 │ -17 │ 22 │ 39 │ false │ 5 │ true │
│ 3204d768f424c527945af498dfdc005f │ health_beauty │ delivered │ 1 │ 122.99 │ 10.17 │ 1 │ 4 │ -18 │ 5 │ 23 │ false │ 4 │ true │
│ 3227045c7007663566ae3d19aed8cca7 │ home_appliances │ delivered │ 1 │ 97.00 │ 13.78 │ 4 │ 2 │ -3 │ 6 │ 9 │ false │ 5 │ true │
│ 36df29e4671cdc14371883d209fed7dc │ telephony │ delivered │ 1 │ 17.00 │ 8.29 │ 1 │ 3 │ -9 │ 4 │ 13 │ false │ 4 │ true │
│ 383e1abcae2216bb7149870157ac31a9 │ musical_instruments │ delivered │ 1 │ 39.90 │ 26.89 │ 4 │ 14 │ -17 │ 18 │ 35 │ false │ 4 │ true │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ 3ab4eea6776259ae8dfbabaeffece352 │ health_beauty │ delivered │ 1 │ 370.00 │ 17.80 │ 2 │ 6 │ -43 │ 8 │ 51 │ false │ 4 │ true │
│ 45b3000bcd10464ac178f32cd783fc83 │ health_beauty │ delivered │ 1 │ 250.00 │ 54.11 │ 4 │ 25 │ -17 │ 29 │ 46 │ false │ 4 │ true │
│ 4a44bde077699b5d6718fd30e34c0b90 │ auto │ delivered │ 1 │ 199.90 │ 21.89 │ 21 │ 13 │ -10 │ 34 │ 44 │ false │ 2 │ true │
│ 4d60e5659a5be3b15be731b5882ecc3e │ furniture_decor │ delivered │ 1 │ 75.00 │ 8.82 │ 6 │ 1 │ -19 │ 7 │ 26 │ false │ 3 │ true │
│ 521061459524ada27c911dfdc0059314 │ cool_stuff │ delivered │ 1 │ 265.00 │ 24.57 │ 2 │ 2 │ -5 │ 4 │ 9 │ false │ 5 │ true │
│ 5253f3630960eb737c90b4ea7cf42614 │ sports_leisure │ delivered │ 1 │ 53.90 │ 8.72 │ 7 │ 8 │ -1 │ 15 │ 16 │ false │ 5 │ true │
│ 52f0252ece9cc6fa57f20b1f309ba1e2 │ housewares │ delivered │ 1 │ 72.00 │ 16.75 │ 3 │ 6 │ -23 │ 9 │ 32 │ false │ 5 │ true │
│ 56c52cf14c501d31e4d208bf86cecb53 │ auto │ delivered │ 2 │ 399.80 │ 43.78 │ 6 │ 9 │ -25 │ 15 │ 40 │ false │ 1 │ true │
│ 570eccb41678dc30af2792b28b86bb45 │ health_beauty │ delivered │ 1 │ 370.00 │ 28.85 │ 2 │ 15 │ -19 │ 17 │ 36 │ false │ 5 │ true │
│ 58a0927d5f0c99b74be566b3d5ed7c59 │ garden_tools │ delivered │ 1 │ 179.99 │ 45.72 │ 3 │ 12 │ -16 │ 15 │ 31 │ false │ 5 │ true │
└──────────────────────────────────┴──────────────────────────┴──────────────┴────────────┴───────────────┴───────────────┴─────────────────┴───────────────┴────────────┴───────────────────────┴───────────────────────────┴─────────┴──────────────┴────────────────────┘
? rows (>9999 rows, 20 shown) 14 columnscon.execute(open(f"{cwd}/../sql/01_staging.sql").read())
con.execute(open(f"{cwd}/../sql/02_marts.sql").read())<_duckdb.DuckDBPyConnection at 0x10bf415f0>Validations
In order to know that to validate, we will reiterate what are the tables granularity.
Overview table: (
order_id,product_id)Categories and Deliveries table: (
order_id,category_name)
Hence, the tests should be
Are all
order_idfrom staging included? (For both) (We know that this is not true, but include it in the check) This tells us whether we missed orders we shouldn’t have.Are the granularities correct? Checks whether the granularities for each table is correct and assert that all of the other columns are grouped correctly.
Does the
payment_totalanditem_countmatched with staging? (For both) This tells us whether there are any irregularities from the counting and total.Does deliveries statistics and reviews stays constant for each orders? (For categories_deliveries)
con.sql(
f'''
WITH grain_overview AS (
SELECT order_id, product_id
FROM overview
GROUP BY order_id, product_id
HAVING COUNT(*) > 1
),
grain_categories AS (
SELECT order_id, category_name
FROM categories_deliveries
GROUP BY order_id, category_name
HAVING COUNT(*) > 1
),
missing_orders AS (
SELECT order_id
FROM stg_orders
WHERE order_id IN (SELECT order_id FROM stg_order_items)
AND order_id NOT IN (SELECT order_id FROM overview)
),
revenue_count_overview AS (
SELECT COALESCE(SUM(price + freight_value) = (SELECT SUM(price + freight_value) FROM stg_order_items), FALSE) AND
COALESCE(SUM(item_count) = (SELECT COUNT(*) FROM stg_order_items), FALSE) AS pass_check
FROM overview
),
revenue_count_categories AS (
SELECT COALESCE(SUM(price + freight_value) = (SELECT SUM(price + freight_value) FROM stg_order_items), FALSE) AND
COALESCE(SUM(item_count) = (SELECT COUNT(*) FROM stg_order_items), FALSE) AS pass_check
FROM categories_deliveries
),
deliveries_statistics AS (
SELECT order_id
FROM categories_deliveries
GROUP BY order_id
HAVING COUNT(DISTINCT review_score) > 1
OR COUNT(DISTINCT delay_days) > 1
OR COUNT(DISTINCT order_status) > 1
OR COUNT(DISTINCT is_single_category) > 1
)
SELECT
'grain_overview' AS check_name,
COUNT(order_id) = 0 AS pass_check
FROM grain_overview
UNION ALL
SELECT
'grain_categories' AS check_name,
COUNT(order_id) = 0 AS pass_check
FROM grain_categories
UNION ALL
SELECT
'unexplained_missing_orders' AS check_name,
COUNT(*) = 0 AS pass_check
FROM missing_orders
UNION ALL
SELECT
'revenue_count_overview' AS check_name,
pass_check
FROM revenue_count_overview
UNION ALL
SELECT
'revenue_count_categories' AS check_name,
pass_check
FROM revenue_count_categories
UNION ALL
SELECT
'deliveries_statistics_check' AS check_name,
COUNT(*) = 0 AS pass_check
FROM deliveries_statistics
'''
)┌─────────────────────────────┬────────────┐
│ check_name │ pass_check │
│ varchar │ boolean │
├─────────────────────────────┼────────────┤
│ grain_overview │ true │
│ grain_categories │ true │
│ unexplained_missing_orders │ true │
│ revenue_count_overview │ true │
│ revenue_count_categories │ true │
│ deliveries_statistics_check │ true │
└─────────────────────────────┴────────────┘con.execute(open(f"{cwd}/../sql/03_checks.sql").read())<_duckdb.DuckDBPyConnection at 0x10bf415f0>Export
Normally, this part wouldn’t exist. But, since the project is local, we will be exporting into a new csv so tableau can ingest the data.
con.sql(f'''
SELECT CASE
WHEN COUNT(*) > 0 THEN error('The table did not pass validation checks')
END
FROM (
SELECT check_name
FROM check_results
WHERE pass_check IS FALSE
);
COPY
overview
TO '../exports/overview.csv'
DELIMITER ','
CSV HEADER;
COPY
categories_deliveries
TO '../exports/categories_deliveries.csv'
DELIMITER ','
CSV HEADER;
''')con.execute(open(f"{cwd}/../sql/04_export.sql").read())<_duckdb.DuckDBPyConnection at 0x10bf415f0>