34f5fa1b9c
DWH pipeline (new):
- dwh/261001_dwh_control.sql — watermarks + per-run audit log schema
- dwh/261002_bronze_constraints_audit.sql — ON CONFLICT key assertion
- dwh/261003_dwh_roles.sql — dwh_owner / grafana_ro contract assertion
- dwh/261004_dwh_observability_views.sql — v_table_freshness,
v_recent_failures, v_watermark_lag (readable by grafana_ro)
- docs/DWH_PIPELINE.md — operations runbook (setup, troubleshooting,
manual re-run, back-fill, rotation)
- DWH_Execution_Manual.md — reusable playbook for future data
projects (extract → blob → load pattern, 7 design principles,
snapshot-vs-incremental matrix, verification gates)
- docs/superpowers/{specs,plans}/2026-04-24-n8n-dwh-bronze-pipeline-*
— design spec + 27-task implementation plan
Security:
- dwh/260423_dwh_ddl_v1.sql — redacted plaintext role passwords to
'CHANGE_ME_BEFORE_APPLY' placeholders; added SECURITY header
documenting generation + rotation flow
Docs:
- CLAUDE.md — §3 adds tracksolid_dwh@31.97.44.246:5888 target,
§4 adds dwh/ + docs/DWH_PIPELINE.md to codebase map, §5 adds
bronze + dwh_control schema roll-up, §10 adds deploy task +
password rotation follow-up
Also includes miscellaneous in-progress files accumulated on this
branch (workspace, analytics notes, vehicle CSVs, extract helpers,
renamed markdown archives).
Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
79 lines
3 KiB
PL/PgSQL
79 lines
3 KiB
PL/PgSQL
-- =============================================================
|
||
-- DWH OBSERVABILITY VIEWS
|
||
-- Target Database: tracksolid_dwh
|
||
-- Purpose: Surface pipeline health for Grafana dashboards. Three views,
|
||
-- one concern each:
|
||
-- v_table_freshness — how long since each table was last loaded
|
||
-- v_recent_failures — failed runs in the last 24h
|
||
-- v_watermark_lag — per-table watermark vs. now
|
||
-- Applies after: 261001_dwh_control.sql
|
||
-- Readability: owned by dwh_owner → grafana_ro inherits SELECT via the
|
||
-- ALTER DEFAULT PRIVILEGES set in 261001. Explicit GRANT below
|
||
-- covers the case where defaults were set AFTER this file runs.
|
||
-- =============================================================
|
||
|
||
BEGIN;
|
||
|
||
SET ROLE dwh_owner;
|
||
|
||
-- 1. FRESHNESS
|
||
-- One row per table that has ever loaded successfully. `lag` drives the
|
||
-- freshness panel; `loads_last_24h` sanity-checks the cron cadence.
|
||
CREATE OR REPLACE VIEW dwh_control.v_table_freshness AS
|
||
SELECT
|
||
table_name,
|
||
MAX(run_finished_at) AS last_loaded_at,
|
||
NOW() - MAX(run_finished_at) AS lag,
|
||
COUNT(*) FILTER (WHERE run_started_at > NOW() - INTERVAL '24 hours') AS loads_last_24h
|
||
FROM dwh_control.extract_runs
|
||
WHERE status = 'loaded'
|
||
GROUP BY table_name;
|
||
|
||
COMMENT ON VIEW dwh_control.v_table_freshness IS
|
||
'Per-table load lag. Alert when lag > 4h during active hours (05:00–23:00 EAT).';
|
||
|
||
-- 2. RECENT FAILURES
|
||
-- Failures retain error_message; the CSV stays in dwh/exports/ for the next
|
||
-- scheduled run to retry. Panel should show run_id so operators can grep logs.
|
||
CREATE OR REPLACE VIEW dwh_control.v_recent_failures AS
|
||
SELECT
|
||
run_id,
|
||
table_name,
|
||
run_started_at,
|
||
run_finished_at,
|
||
csv_path,
|
||
error_message
|
||
FROM dwh_control.extract_runs
|
||
WHERE status = 'failed'
|
||
AND run_started_at > NOW() - INTERVAL '24 hours'
|
||
ORDER BY run_started_at DESC;
|
||
|
||
COMMENT ON VIEW dwh_control.v_recent_failures IS
|
||
'Failed extract/load runs in the last 24h. Alert on any row.';
|
||
|
||
-- 3. WATERMARK LAG
|
||
-- Distinguishes "pipeline ran but found nothing" (load_lag small, extract_lag
|
||
-- growing) from "pipeline is stuck" (both lags growing). Snapshot tables are
|
||
-- not in extract_watermarks so they do not appear here — that is intentional.
|
||
CREATE OR REPLACE VIEW dwh_control.v_watermark_lag AS
|
||
SELECT
|
||
table_name,
|
||
last_extracted_at,
|
||
last_loaded_at,
|
||
rows_loaded_last_run,
|
||
NOW() - last_loaded_at AS load_lag,
|
||
NOW() - last_extracted_at AS extract_lag
|
||
FROM dwh_control.extract_watermarks;
|
||
|
||
COMMENT ON VIEW dwh_control.v_watermark_lag IS
|
||
'Per-table watermark position vs. now. Incremental tables only (6 rows).';
|
||
|
||
RESET ROLE;
|
||
|
||
-- Explicit grants: defensive in case ALTER DEFAULT PRIVILEGES from 261001
|
||
-- was not in effect when these views were created.
|
||
GRANT SELECT ON dwh_control.v_table_freshness TO grafana_ro;
|
||
GRANT SELECT ON dwh_control.v_recent_failures TO grafana_ro;
|
||
GRANT SELECT ON dwh_control.v_watermark_lag TO grafana_ro;
|
||
|
||
COMMIT;
|