How the sales and stock data is governed, from source file to dashboard.
These are the four documents that sit behind the dashboards in my portfolio: where each number comes from, what each column means, who can see what, and which checks run before data is loaded. They describe an FMCG importer's sales and stock data in PostgreSQL, fed by a Python ETL and published as encrypted HTML dashboards.
source systems feeding one PostgreSQL database
datasets registered, each with an owner and a sensitivity class
roles × dashboards in the access matrix
data-quality checks, 4 of which block bad data from loading
01 · Data lineage
Source to dashboardEvery dashboard number can be traced back to one source file. Data moves left to right only; nothing is typed in by hand after the source.
Sources
Ingest & validate
PostgreSQL
sales_fact stock_lot + mastersBuild & protect
Consumers
Metric lineage
| Dashboard metric | Computed as | Comes from | Guarded by |
|---|---|---|---|
| Net sales (THB) | SUM(net_sales) | ERP invoice amount after discount, excl. VAT → core.sales_fact | Q2, Q3, Q7, Q9 |
| Units in cartons | unit_pieces ÷ pack_size | ERP quantity in pieces + product master pack size | Q4 |
| Stock status | Share of shelf life left: Expired / Critical <60% / Warning <70% / Safe | WMS mfg_date, exp_date → core.stock_lot | Q5, Q10 |
| Cover days | Stock on hand (cartons) ÷ average daily sales, Grade A only | core.stock_lot + average of the last 6 months with sales in core.sales_fact | Q2, Q8 |
02 · Data dictionary
What each table and column meansThe register lists every dataset with its grain, refresh, owner and sensitivity. Below it are the column definitions for the two core tables the dashboards read.
Dataset register
| Dataset | One row per | Source | Refresh | Business owner | Class |
|---|---|---|---|---|---|
core.sales_fact | Customer × product × month | ERP sales export | Monthly | Sales operations | Internal |
core.stock_lot | Company × product × lot | WMS stock export | Daily | Warehouse | Internal |
core.product_master | Product code | Product master file | On change | Brand / category team | Internal |
core.customer_master | Customer | ERP customer list | On change | Sales operations | Internal |
core.landed_cost | Product × shipment | Import costing | Per shipment | Finance | Restricted |
mart.salesperson_month | Salesperson × month | Roll-up of sales_fact | Monthly | Sales management | Restricted |
| Column | Type | Meaning | Rule / example | Class |
|---|---|---|---|---|
year_col | int | Sales year | 2024 … current year | Internal |
month_col | int | Sales month | 1–12; partial current month is excluded | Internal |
customer_name1 | text | Customer trading name | Must exist in customer master (Q3) | Internal |
channel | text | Sales channel of the customer | Modern Trade, Traditional Trade, Online, Other (Q6) | Internal |
product_code | text | Product code | Stored as text so leading zeros survive: 0001244, not 1244 (Q1) | Internal |
product_name | text | Product description | From product master, not from the invoice | Internal |
brand | text | Brand | e.g. BRIGHTLY, PURELY | Internal |
actual_company | text | Company that owns the brand | ALPHA or Beta | Internal |
group_category | text | Top-level product group | FMCG, Pet, Kitchen (Q6) | Internal |
category | text | Product category | Home Care, Personal Care, Tissue… | Internal |
sub_cat_cleaned_v1 | text | Sub-category after clean-up mapping v1 | Merges spelling variants, e.g. “Dishwash” → Dish Wash | Internal |
pack_size | int | Pieces per carton | > 0, from product master (Q4) | Internal |
unit_pieces | numeric | Quantity sold in pieces | ≥ 0 except credit notes | Internal |
unit_pack | numeric | Quantity sold in cartons | = unit_pieces ÷ pack_size | Internal |
net_sales | numeric(14,2) | Sales after discount, excl. VAT, THB | Totals must agree across every mart table (Q9) | Internal |
saler_name, person_key | text | Salesperson and their code | R01…; links sales to individual performance | Restricted |
| Column | Type | Meaning | Rule / example | Class |
|---|---|---|---|---|
company | text | Company holding the stock | Northwind or Bluepeak | Internal |
product_code | text | Product code with grade suffix | MK1001 = Grade A; -B = opened pieces; -Block = blocked | Internal |
name | text | Product description | As in the WMS | Internal |
product_type | text | Product type | Detergent, Shampoo, Dry Food… | Internal |
batch_lot | text | Production lot | Empty only when the SKU is out of stock | Internal |
mfg_date | date | Manufacture date | YYYY-MM-DD (Q5) | Internal |
exp_date | date | Expiry date | YYYY-MM-DD, after mfg_date (Q5) | Internal |
sale_uom | text | Unit the lot is sold in | CTN or PCS | Internal |
pcs_su | int | Pieces per sale unit | > 0; 1 for PCS | Internal |
good | int | Sellable quantity | ≥ 0 (Q8) | Internal |
damage | int | Damaged quantity | ≥ 0 (Q8) | Internal |
wip | int | Quantity being repacked or moved | ≥ 0 (Q8) | Internal |
total_qty | int | All stock in the lot | = good + damage + wip (Q8) | Internal |
grade | text | Derived from the code suffix | Grade A (ctn), Grade B (pcs), Grade Block (pcs) | Internal |
status | text | Derived expiry status | Expired, Critical, Warning, Safe, Out of Stock (Q10) | Internal |
avg_month_sale | numeric | Average cartons sold per month | Up to 6 recent months with sales; zero months skipped. Grade A only | Internal |
03 · Access matrix
Least privilege, by roleEach role has one password. It unlocks only the dashboards granted to that role; the others stay encrypted in the browser. Cost, margin and individual sales performance are limited to admin and executive.
Dashboards by role
| Role | Who | Stock Aging | Sale-In | Mall Sale-Out | Business | Profit Pulse | Stock Value |
|---|---|---|---|---|---|---|---|
admin | Data team | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
exec | Executives | ✓ | ✓ | ✓ | ✓ | ✓ | ✓ |
sale2mt | Sales lead, modern trade | ✓ | ✓ | ✓ | – | – | – |
sale2tt | Sales lead, traditional trade | ✓ | ✓ | – | – | – | – |
sale1 | Field sales | ✓ | – | – | – | – | – |
pcare | Category team | ✓ | – | – | – | – | – |
Database accounts
| Account | Used by | Can read | Can write |
|---|---|---|---|
etl_writer | The Python ETL only | staging, core | staging, core (no DROP or ALTER) |
report_reader | Excel and Power BI over ODBC | mart, without Restricted tables | No, read-only |
exec_reader | Dashboard build for admin and exec | mart, including Restricted tables | No, read-only |
owner | Schema changes and backups | Everything | Everything; not used day to day |
How it is enforced
Encrypted at rest in the file
Each dashboard's data is encrypted with AES-GCM. A role's password unwraps only the keys for its granted dashboards, so sharing the file does not share the data.
Nobody edits from Excel
Spreadsheets and Power BI connect with a read-only account. Only the ETL can write, so a wrong paste cannot change the source of truth.
Sensitive figures kept apart
Landed cost and per-salesperson results sit in Restricted tables that only the admin and exec builds read.
04 · Data-quality rules
Checked before data reaches the databaseEvery source goes through the same checks. A check either blocks the row or batch, warns and loads, or fixes the value in a known, logged way.
| # | Check | Stage | What it catches | Action |
|---|---|---|---|---|
| Q1 | Product codes kept as text | Load | Excel turning 0001244 into 1244, which attached sales to the wrong SKU | Fix |
| Q2 | Product code exists in product master | Validate | New or mistyped codes; one batch had 14 missing codes | Block row |
| Q3 | Customer is mapped | Validate | Rows that used to load as null and drop out of revenue totals | Block row |
| Q4 | Pack size present and > 0 | Validate | Cartons and net sales computed as zero | Warn |
| Q5 | One date format | Load | 1/12/25, 2025-12-01 and 12/1/25 in the same file, which made phantom gaps in trends | Fix to YYYY-MM-DD |
| Q6 | Values in the reference list | Validate | Channel, category or sub-category names drifting between source periods | Warn |
| Q7 | No duplicate rows on the table key | Validate | The same month loaded twice, doubling sales | Block batch |
| Q8 | Stock quantities add up | Validate | Negative quantities, or good + damage + wip ≠ total | Warn |
| Q9 | Totals agree across tables | Build | Monthly, customer, product and salesperson tables that differ beyond rounding | Stop build |
| Q10 | Expiry status rule | Build | Expired if past expiry; Critical if <60% of shelf life left; Warning <70%; no manufacture date falls back to 30 / 90 days | Classify |
Old records were cleaned the same way: SKUs that appeared under two names were merged so year-over-year trends compare like with like, and the database is backed up on a schedule.
See the dashboards these rules protect
Four live demos, with the full background in my profile.