{ "edges": [ { "to": "0b323db9-8b30-453b-a396-ca74ce1b0308", "from": "cea373b4-c913-4d9f-b566-90b9ddb35879", "toPort": "0", "fromPort": "0" }, { "to": "d809a9c0-0dd8-45b8-be46-91132ecb234c", "from": "8db194eb-88ae-4ff5-bee9-46da8c912288", "toPort": "0", "fromPort": "0" }, { "to": "bbf08a47-ce4b-49fa-8557-1227f6b30dd6", "from": "8db194eb-88ae-4ff5-bee9-46da8c912288", "toPort": "0", "fromPort": "0" }, { "to": "4ec88710-8d0b-480a-8239-ed25e0d118c6", "from": "8db194eb-88ae-4ff5-bee9-46da8c912288", "toPort": "0", "fromPort": "0" }, { "to": "f3d04639-05a9-4b69-ba7c-87ac6d9fafc2", "from": "8db194eb-88ae-4ff5-bee9-46da8c912288", "toPort": "0", "fromPort": "0" }, { "to": "cea373b4-c913-4d9f-b566-90b9ddb35879", "from": "4ec88710-8d0b-480a-8239-ed25e0d118c6", "toPort": "3", "fromPort": "0" }, { "to": "cea373b4-c913-4d9f-b566-90b9ddb35879", "from": "d809a9c0-0dd8-45b8-be46-91132ecb234c", "toPort": "0", "fromPort": "0" }, { "to": "cea373b4-c913-4d9f-b566-90b9ddb35879", "from": "f3d04639-05a9-4b69-ba7c-87ac6d9fafc2", "toPort": "4", "fromPort": "0" }, { "to": "cea373b4-c913-4d9f-b566-90b9ddb35879", "from": "bbf08a47-ce4b-49fa-8557-1227f6b30dd6", "toPort": "1", "fromPort": "0" }, { "to": "8db194eb-88ae-4ff5-bee9-46da8c912288", "from": "52fa31e9-1811-4999-8254-4516e56610e7", "toPort": "0", "fromPort": "0" }, { "to": "f678cd51-4d24-4318-993d-2795fad9bce4", "from": "0b323db9-8b30-453b-a396-ca74ce1b0308", "toPort": "0", "fromPort": "0" }, { "to": "51ba3e03-248d-4e7a-8df2-7c11a3a4eca5", "from": "0b323db9-8b30-453b-a396-ca74ce1b0308", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "52fa31e9-1811-4999-8254-4516e56610e7", "name": "Schedule Trigger", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "hours", "hoursInterval": 24 } ] } }, "position": [ 112, 400 ] }, { "id": "8db194eb-88ae-4ff5-bee9-46da8c912288", "name": "Config", "type": "set", "params": { "options": {}, "assignments": { "assignments": [ { "id": "cfg-01", "name": "tableName", "type": "string", "value": "restaurant_orders" }, { "id": "cfg-02", "name": "nullCheckCol", "type": "string", "value": "restaurant_orders" }, { "id": "cfg-03", "name": "duplicateCheckCol", "type": "string", "value": "order_id" }, { "id": "cfg-04", "name": "outlierCol", "type": "string", "value": "order_id" }, { "id": "cfg-05", "name": "outlierMin", "type": "number", "value": 0 }, { "id": "cfg-06", "name": "outlierMax", "type": "number", "value": 150 }, { "id": "cfg-07", "name": "rowCountMin", "type": "number", "value": 500 }, { "id": "cfg-08", "name": "rowCountMax", "type": "number", "value": 100000 }, { "id": "cfg-09", "name": "nullThresholdPct", "type": "number", "value": 5 }, { "id": "cfg-10", "name": "dupThresholdPct", "type": "number", "value": 1 }, { "id": "25a47144-53ab-4923-ae70-78d297745dd9", "name": "Distribution list", "type": "string", "value": "user@example.com" } ] } }, "position": [ 336, 400 ] }, { "id": "d809a9c0-0dd8-45b8-be46-91132ecb234c", "name": "Null Check", "type": "integration", "params": { "query": "SELECT 'null_check' AS check_type, COUNT(*) AS total_rows, SUM(CASE WHEN {{ $('Config').item.json.nullCheckCol }} IS NULL OR CAST({{ $('Config').item.json.nullCheckCol }} AS TEXT) = '' THEN 1 ELSE 0 END) AS null_count, ROUND(SUM(CASE WHEN {{ $('Config').item.json.nullCheckCol }} IS NULL OR CAST({{ $('Config').item.json.nullCheckCol }} AS TEXT) = '' THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS null_pct FROM {{ $('Config').item.json.tableName }}", "options": {}, "service": "postgres", "operation": "executeQuery" }, "position": [ 560, 112 ] }, { "id": "bbf08a47-ce4b-49fa-8557-1227f6b30dd6", "name": "Duplicate Check", "type": "integration", "params": { "query": "SELECT 'dup_check' AS check_type, COUNT(*) AS total_rows, COUNT(*) - COUNT(DISTINCT {{ $('Config').item.json.duplicateCheckCol }}) AS dup_count, ROUND((COUNT(*) - COUNT(DISTINCT {{ $('Config').item.json.duplicateCheckCol }})) * 100.0 / NULLIF(COUNT(*), 0), 2) AS dup_pct FROM {{ $('Config').item.json.tableName }}", "options": {}, "service": "postgres", "operation": "executeQuery" }, "position": [ 560, 304 ] }, { "id": "4ec88710-8d0b-480a-8239-ed25e0d118c6", "name": "Row Count", "type": "integration", "params": { "query": "SELECT 'row_count' AS check_type, COUNT(*) AS row_count FROM {{ $('Config').item.json.tableName }}", "options": {}, "service": "postgres", "operation": "executeQuery" }, "position": [ 560, 496 ] }, { "id": "f3d04639-05a9-4b69-ba7c-87ac6d9fafc2", "name": "Outlier Check", "type": "integration", "params": { "query": "SELECT 'outlier_check' AS check_type, COUNT(*) AS total_rows, SUM(CASE WHEN {{ $('Config').item.json.outlierCol }} < {{ $('Config').item.json.outlierMin }} OR {{ $('Config').item.json.outlierCol }} > {{ $('Config').item.json.outlierMax }} THEN 1 ELSE 0 END) AS outlier_count, ROUND(SUM(CASE WHEN {{ $('Config').item.json.outlierCol }} < {{ $('Config').item.json.outlierMin }} OR {{ $('Config').item.json.outlierCol }} > {{ $('Config').item.json.outlierMax }} THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS outlier_pct FROM {{ $('Config').item.json.tableName }}", "options": {}, "service": "postgres", "operation": "executeQuery" }, "position": [ 560, 688 ] }, { "id": "cea373b4-c913-4d9f-b566-90b9ddb35879", "name": "Merge", "type": "merge", "params": { "numberInputs": 5 }, "position": [ 832, 352 ] }, { "id": "0b323db9-8b30-453b-a396-ca74ce1b0308", "name": "Evaluate & Format", "type": "code", "params": { "jsCode": "const cfg = $('Config').item.json;\nconst items = $input.all();\n\nfunction findCheck(type) {\n return (items.find(i => i.json.check_type === type) || { json: {} }).json;\n}\n\nconst nullR = findCheck('null_check');\nconst dupR = findCheck('dup_check');\nconst countR = findCheck('row_count');\nconst outlierR = findCheck('outlier_check');\n\nfunction evalStatus(value, threshold, mode) {\n if (mode === 'above') {\n if (value === 0) return { label: 'PASS', color: '#16a34a' };\n if (value <= threshold) return { label: 'WARN', color: '#d97706' };\n return { label: 'FAIL', color: '#dc2626' };\n }\n if (mode === 'range') {\n if (value >= cfg.rowCountMin && value <= cfg.rowCountMax) return { label: 'PASS', color: '#16a34a' };\n if (value < cfg.rowCountMin * 0.8 || value > cfg.rowCountMax * 1.2) return { label: 'FAIL', color: '#dc2626' };\n return { label: 'WARN', color: '#d97706' };\n }\n}\n\nconst nullPct = parseFloat(nullR.null_pct ?? 0);\nconst dupPct = parseFloat(dupR.dup_pct ?? 0);\nconst rowCount = parseInt(countR.row_count ?? 0, 10);\nconst outlierPct = parseFloat(outlierR.outlier_pct ?? 0);\n\nconst checks = [\n { name: 'Null / missing values', col: cfg.nullCheckCol, value: `${nullPct}%`, threshold: `< ${cfg.nullThresholdPct}%`, st: evalStatus(nullPct, cfg.nullThresholdPct, 'above') },\n { name: 'Duplicate rows', col: cfg.duplicateCheckCol, value: `${dupPct}%`, threshold: `< ${cfg.dupThresholdPct}%`, st: evalStatus(dupPct, cfg.dupThresholdPct, 'above') },\n { name: 'Row count anomaly', col: cfg.tableName, value: rowCount.toLocaleString(), threshold: `${cfg.rowCountMin} – ${cfg.rowCountMax}`, st: evalStatus(rowCount, null, 'range') },\n { name: 'Outlier values', col: cfg.outlierCol, value: `${outlierPct}%`, threshold: `${cfg.outlierMin} – ${cfg.outlierMax}`, st: evalStatus(outlierPct, 5, 'above') },\n];\n\nconst hasFail = checks.some(c => c.st.label === 'FAIL');\nconst hasWarn = checks.some(c => c.st.label === 'WARN');\nconst overall = hasFail ? 'FAIL' : hasWarn ? 'WARN' : 'PASS';\nconst oc = { PASS: '#16a34a', WARN: '#d97706', FAIL: '#dc2626' }[overall];\nconst now = new Date().toISOString().slice(0, 19).replace('T', ' ') + ' UTC';\n\nconst badge = (label, color) =>\n `${label}`;\n\nconst tableRow = c =>\n `` +\n `${c.name}` +\n `${c.col}` +\n `${c.value}` +\n `${c.threshold}` +\n `${badge(c.st.label, c.st.color)}` +\n ``;\n\nconst htmlReport =\n `` +\n `

` +\n `

Data Quality Report

` +\n `

Table: ${cfg.tableName} · ${now}

` +\n `

${badge(overall, oc)}

` +\n `` +\n `` +\n `` +\n `` +\n `` +\n `` +\n `` +\n `` +\n `${checks.map(tableRow).join('')}` +\n `

Check	Column	Value	Threshold	Status

` +\n `

` +\n `Generated by n8n Data Quality Bot · Thresholds configured in the Config (Set) node` +\n `

`;\n\nreturn [{\n json: {\n overall,\n htmlReport,\n emailSubject: `[${overall}] Data Quality — ${cfg.tableName} — ${now}`,\n reportEmail: cfg.reportEmail,\n sheet_timestamp: now,\n sheet_table: cfg.tableName,\n sheet_null_pct: nullPct,\n sheet_dup_pct: dupPct,\n sheet_row_count: rowCount,\n sheet_outlier_pct: outlierPct,\n sheet_status: overall\n }\n}];" }, "position": [ 1008, 400 ] }, { "id": "f678cd51-4d24-4318-993d-2795fad9bce4", "name": "Log to Google Sheets", "type": "integration", "params": { "columns": { "value": { "Dup %": "={{ $json.sheet_dup_pct }}", "Table": "={{ $json.sheet_table }}", "Null %": "={{ $json.sheet_null_pct }}", "Status": "={{ $json.sheet_status }}", "overall": "={{ $json.overall }}", "Outlier %": "={{ $json.sheet_outlier_pct }}", "Row Count": "={{ $json.sheet_row_count }}", "Timestamp": "={{ $json.sheet_timestamp }}" }, "schema": [ { "id": "Timestamp", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Timestamp", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Table", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Table", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Null %", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Null %", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Dup %", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Dup %", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Row Count", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Row Count", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Outlier %", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Outlier %", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "Status", "type": "string", "display": true, "removed": false, "required": false, "displayName": "Status", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "overall", "type": "string", "display": true, "removed": false, "required": false, "displayName": "overall", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "htmlReport", "type": "string", "display": true, "removed": true, "required": false, "displayName": "htmlReport", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "emailSubject", "type": "string", "display": true, "removed": true, "required": false, "displayName": "emailSubject", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sheet_timestamp", "type": "string", "display": true, "removed": true, "required": false, "displayName": "sheet_timestamp", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sheet_table", "type": "string", "display": true, "removed": true, "required": false, "displayName": "sheet_table", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sheet_null_pct", "type": "string", "display": true, "removed": true, "required": false, "displayName": "sheet_null_pct", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sheet_dup_pct", "type": "string", "display": true, "removed": true, "required": false, "displayName": "sheet_dup_pct", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sheet_row_count", "type": "string", "display": true, "removed": true, "required": false, "displayName": "sheet_row_count", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sheet_outlier_pct", "type": "string", "display": true, "removed": true, "required": false, "displayName": "sheet_outlier_pct", "defaultMatch": false, "canBeUsedToMatch": true }, { "id": "sheet_status", "type": "string", "display": true, "removed": true, "required": false, "displayName": "sheet_status", "defaultMatch": false, "canBeUsedToMatch": true } ], "mappingMode": "defineBelow", "matchingColumns": [], "attemptToConvertTypes": false, "convertFieldsToString": false }, "options": {}, "service": "googleSheets", "operation": "append", "sheetName": { "__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1zIzM4gtIvtEj6t9YmBCQtieYLZLRmXj_37BoBtVTWw8/edit#gid=0", "cachedResultName": "Sheet1" }, "documentId": { "__rl": true, "mode": "list", "value": "1zIzM4gtIvtEj6t9YmBCQtieYLZLRmXj_37BoBtVTWw8", "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1zIzM4gtIvtEj6t9YmBCQtieYLZLRmXj_37BoBtVTWw8/edit?usp=drivesdk", "cachedResultName": "Log for order" } }, "position": [ 1232, 496 ] }, { "id": "51ba3e03-248d-4e7a-8df2-7c11a3a4eca5", "name": "Send a message", "type": "integration", "params": { "sendTo": "={{ $('Config').item.json['Distribution list'] }}", "message": "={{ $json.htmlReport }}", "options": {}, "service": "gmail", "subject": "={{ $json.emailSubject }}", "operation": "read" }, "position": [ 1232, 304 ] } ] }

Monitor SQL data quality and send email reports with Google Sheets logging