Work project at Collins Aerospace. In progress.
Quality dashboards on Snowflake
Quality reporting at Mission Systems grew up site by site, with every report pulling its own data its own way. I built one shared Snowflake backend in Power BI dataflows and put the quality dashboards on it: two global dashboards, one per SAP system, plus Defects in FIT and corrective actions from EtQ.
- My role
- Project lead; designed and built it myself
- Timeline
- 2026 – Present
- Tools
- Snowflake, Power BI dataflows (Power Query M and SQL), DAX, scikit-learn, EtQ Reliance
The old setup
Each site's dashboard ran its own queries against SAP, with typed-in plant lists and its own definitions. Defects in FIT covered one site and depended on a scheduled Python export of an SAP report that often failed. Another site kept a separate copy of the quality dashboard and patched it by hand whenever the main one changed. Defect categories, customers and production areas lived in hand-kept classification files, and the two SAP systems store the same things in different fields.
How it's built
- QNs, tasks and defect items (QMEL and the QM views)
- Causes (QMUR) and inspection lots (QALS)
- Orders, WBS, stock, users, catalog texts
- One query per table, per SAP system
- Merged into one table
- QNs, tasks, defect items, causes
- Inspection lots and quality stock
- Plants, user names, material prices
- Quality dashboard per SAP system
- Defects in FIT
- EtQ corrective actions
Each raw SAP table gets one staging query per SAP system, and the two merge into one table. Joins and lookups run once, in Snowflake SQL inside the dataflows, so every dashboard reads finished, named tables. Plants come from one global plant table, so a new site appears without editing a report. The second dashboard is rebuilt from a copy of the first, so every page, measure and field name matches.
I don't have direct Snowflake access, so every query and every check runs through dataflows under a data steward's credentials. Each change ships with a check query and the numbers it should return.
Fixes along the way
- Names beside every ID. Users, suppliers, materials, task codes and storage locations show SAP's own names, from one shared user table of about 200,000 SAP IDs.
- Work center names. Descriptions that were masked in the reporting views now come from SAP's work center tables (CRHD, CRTX).
- Owners who have left. Locked legacy user IDs link to the person's current ID, so 1 in 5 open tasks is correctly flagged for reassignment. Before, over a third would have been flagged wrongly.
- Causes by defect item. Each cause (QMUR) links to its own defect item, so the cause Pareto stops double-counting on QNs with several items.
- Receiving inspection backlog. Lots count by where their quality stock actually sits, and the result matches the warehouse's own stock report item for item.
- Corrective actions. One shared EtQ table maps every workflow phase to a standard stage. The old logic had miscounted more than 300 corrective actions.
Sample views
Four views from the dashboards, rebuilt with made-up data. Each one shows how it's built.
What it answers: how long do QNs take to close, and is the average telling the whole story? Switch what the rows are, then sort.
Plant C: the average is 48 business days but the median is 11. Most QNs close quickly and a handful stay open for months. The median says how long a typical QN takes; the gap says where the stragglers are.
Supplier: the average is 33 business days but the median is 12. Most QNs close quickly and a handful stay open for months. The median says how long a typical QN takes; the gap says where the stragglers are.
How it's built
A field parameter swaps the rows between plant, QN type, WBS and project, so one visual answers several questions. Both measures count only completed QNs, and a few long-open notifications pull the average far above the median, which is why the page shows both.
-- DAX: completed QNs only, in business days
QN TAT Median =
MEDIANX (
FILTER ( QM11, NOT ISBLANK ( QM11[Date QN Completed] ) ),
QM11[QN TAT (BD)]
)
QN TAT Average =
AVERAGEX (
FILTER ( QM11, NOT ISBLANK ( QM11[Date QN Completed] ) ),
QM11[QN TAT (BD)]
)What it answers: which few problems make up most of the defects, and what is causing them.
- Solder142 items,running total 36%
- Wire harness96 items,running total 61%
- Torque out of spec61 items,running total 77%
- Documentation38 items,running total 86%
- Foreign object debris24 items,running total 93%
- Cosmetic damage17 items,running total 97%
- Other12 items,running total 100%
390 defect items. Three problem codes make up nearly 80%.
- Process not followed96 items,running total 25%
- Work instruction unclear78 items,running total 45%
- Supplier material62 items,running total 61%
- Operator training48 items,running total 73%
- No cause recorded46 items,running total 85%
- Tooling or fixture36 items,running total 94%
- Design24 items,running total 100%
390 defect items; 88% have a cause recorded, a number the page tracks on its own card.
How it's built
Defect items come from QM15 with SAP's catalog texts (QPCT, QPGT). Each cause links to its own defect item rather than its QN, so a QN with three items doesn't count one cause three times. Darker bars are the categories that make up the first 80%.
-- DAX: running share for the Pareto
Item Running % =
VAR cur = [Item Count]
VAR above =
FILTER (
ALLSELECTED ( QM15[Problem Code Text] ),
[Item Count] >= cur
)
RETURN
DIVIDE (
SUMX ( above, [Item Count] ),
CALCULATE ( [Item Count], ALLSELECTED ( QM15 ) )
)What it answers: where open corrective actions are stuck, and how many are overdue. Two EtQ workflows, one set of stages.
38 of 315 open corrective actions are overdue, most of them still in root cause and plan. 8 sit in a phase the mapping doesn't know yet, which shows up as Unmapped instead of disappearing.
12 of 107 open corrective actions are overdue, most of them still in root cause and plan. 3 sit in a phase the mapping doesn't know yet, which shows up as Unmapped instead of disappearing.
26 of 208 open corrective actions are overdue, most of them still in root cause and plan. 5 sit in a phase the mapping doesn't know yet, which shows up as Unmapped instead of disappearing.
How it's built
EtQ has two corrective action workflows: an 8D for product issues and a separate one for process and audit findings, each with its own phase names. One shared table maps every phase to a standard stage, with an Unmapped bucket so a phase added later can't silently drop out of the counts. This page is in design now.
-- Snowflake SQL: one stage for every phase, both workflows
SELECT ca_number, workflow, phase,
CASE
WHEN phase IN ('D1','D2','D3','D4','D5',
'Investigation','Action Plan')
THEN 'Root cause and plan'
WHEN phase IN ('D6','Implementation')
THEN 'Implementation'
WHEN phase IN ('D7','D8','Effectiveness Check',
'Final Approval')
THEN 'Effectiveness and final approval'
ELSE 'Unmapped'
END AS ca_stage
FROM corrective_actions
WHERE status NOT IN ('Closed', 'Void');What it answers: which category does each new defect in Defects in FIT belong to? A model proposes one and a person accepts or changes it.
| Defect text | Proposed category | Confidence | Decision |
|---|---|---|---|
| Leak rate above limit at final functional test | High | ||
| Traveler missing inspection stamp | High | ||
| Scratch on housing found at receiving | High | ||
| Part marking illegible after coating | Medium | ||
| Test fixture fault, unit retested and passed | Medium | ||
| Adhesive used past its shelf life date | High | ||
| Paint chipped at mounting edge | Medium | ||
| Connector pin bent during assembly | Low |
How it's built
A scikit-learn text model trained on defects people had already categorized proposes a category and a confidence tier for each new one, and the proposals wait for a person. Beside it, a simple rule in the dataflow gives a whole defect code its category once 95% of that code's categorized defects, at least five, agree.
-- Snowflake SQL: a defect code earns a category once
-- 95% of its categorized defects, at least 5, agree
SELECT defect_code, category
FROM (
SELECT defect_code, category,
COUNT(*) AS n,
SUM(COUNT(*)) OVER (PARTITION BY defect_code)
AS code_total
FROM categorized_defects
GROUP BY defect_code, category
)
WHERE code_total >= 5
AND n >= 0.95 * code_total;Made-up sample data. Plant, category and defect names are generic. The real numbers belong to Collins Aerospace.