superset-sql-developer

# Superset SQL Developer Skill ## Purpose Expert guidance for writing optimized SQL queries for Apache Superset datasets, virtual datasets, and SQL Lab. This skill helps you create performant queries, design efficient datasets, and leverage PostgreSQL features - with specific patterns for Finance SSC, BIR compliance, and Odoo integration. ## When to Use This Skill - Creating new Superset datasets - Writing SQL for charts and dashboards - Optimizing slow queries - Designing virtual datasets - Building metrics and calculated columns - Querying Odoo database via Supabase - Implementing row-level security ## SQL Development Workflow ### 1. Develop in SQL Lab ``` SQL Lab → Test Query → Verify Results → Save as Dataset ``` **SQL Lab Best Practices:** - Start with `LIMIT 10` to test quickly - Use `EXPLAIN ANALYZE` to check performance - Save query history for reuse - Add comments to document logic ### 2. Create Dataset ``` Datasets → + Dataset → SQL Query or Table ``` **Dataset Types:** - **Physical Table:** Direct table reference (fastest) - **Virtual Dataset:** SQL query (flexible) - **Jinja Template:** Dynamic SQL (advanced) ### 3. Define Metrics ``` Dataset → Edit → Metrics Tab → + Add Metric ``` **Metric Examples:** ```sql -- Sum of amounts SUM(amount) -- Count distinct COUNT(DISTINCT agency_id) -- Percentage SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) -- Average with filter AVG(CASE WHEN processing_time_seconds > 0 THEN processing_time_seconds END) ``` --- ## Query Optimization Fundamentals ### Performance Targets ```yaml Fast Query: < 1 second Good Query: 1-5 seconds Acceptable: 5-10 seconds Needs Work: 10-30 seconds Too Slow: > 30 seconds (optimize or async) ``` ### Query Optimization Checklist **1. Use WHERE Clauses Wisely** ```sql -- ✅ GOOD: Filter on indexed columns SELECT * FROM bir_filing_tracker WHERE agency_id = 5 AND filing_date >= '2025-10-01' -- ❌ BAD: No filters, scans entire table SELECT * FROM bir_filing_tracker -- ❌ BAD: Function on column prevents index use SELECT * FROM bir_filing_tracker WHERE EXTRACT(YEAR FROM filing_date) = 2025 -- ✅ GOOD: Filter allows index use SELECT * FROM bir_filing_tracker WHERE filing_date >= '2025-01-01' AND filing_date < '2026-01-01' ``` **2. Optimize Aggregations** ```sql -- ✅ GOOD: Group by indexed columns SELECT agency_id, COUNT(*) as filing_count FROM bir_filing_tracker WHERE filing_date >= '2025-01-01' GROUP BY agency_id -- ❌ BAD: Group by non-indexed computed value SELECT TO_CHAR(filing_date, 'Month YYYY'), COUNT(*) FROM bir_filing_tracker GROUP BY TO_CHAR(filing_date, 'Month YYYY') -- ✅ BETTER: Group by date-truncated column (can be indexed) SELECT DATE_TRUNC('month', filing_date) as month, COUNT(*) FROM bir_filing_tracker GROUP BY DATE_TRUNC('month', filing_date) ``` **3. Join Efficiently** ```sql -- ✅ GOOD: Join on indexed foreign keys SELECT t.task_name, a.agency_name, COUNT(*) as count FROM month_end_closing_tasks t INNER JOIN agencies a ON t.agency_id = a.agency_id WHERE t.closing_period = '2025-10' GROUP BY t.task_name, a.agency_name -- ❌ BAD: Cartesian product (missing join condition) SELECT t.task_name, a.agency_name FROM month_end_closing_tasks t, agencies a -- ⚠️ CAUTION: LEFT JOIN might be slower -- Only use if you need NULL results SELECT t.*, a.agency_name FROM month_end_closing_tasks t LEFT JOIN agencies a ON t.agency_id = a.agency_id ``` **4. Limit Result Sets** ```sql -- ✅ GOOD: Use LIMIT for large results SELECT * FROM document_processing_logs WHERE processed_at >= CURRENT_DATE ORDER BY processed_at DESC LIMIT 1000 -- ✅ GOOD: Use TOP N with window functions SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY agency_id ORDER BY filing_date DESC) as rn FROM bir_filing_tracker ) sub WHERE rn <= 10 -- Top 10 per agency -- ❌ BAD: No limit, returns millions of rows SELECT * FROM document_processing_logs ``` **5. Use Appropriate Data Types** ```sql -- ✅ GOOD: Specific data types SELECT amount::NUMERIC(10,2), -- For money filing_date::DATE, -- For dates agency_id::INTEGER -- For IDs -- ❌ BAD: Casting everything as text SELECT amount::TEXT, filing_date::TEXT, agency_id::TEXT ``` --- ## PostgreSQL Optimization Features ### 1. Indexes ```sql -- Check if index exists SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'bir_filing_tracker'; -- Suggest indexes based on common queries -- Index on frequently filtered columns CREATE INDEX idx_bir_filing_agency_date ON bir_filing_tracker(agency_id, filing_date); -- Index for status queries CREATE INDEX idx_bir_filing_status ON bir_filing_tracker(filing_status) WHERE filing_status IN ('Pending', 'Overdue'); -- Partial index -- Composite index for joins CREATE INDEX idx_closing_tasks_lookup ON month_end_closing_tasks(agency_id, closing_period, task_status); ``` ### 2. Materialized Views ```sql -- Create materialized view for expensive aggregations CREATE MATERIALIZED VIEW mv_bir_filing_summary AS SELECT agency_id, filing_period, form_type, COUNT(*) as total_filings, SUM(CASE WHEN filing_status = 'Completed' THEN 1 ELSE 0 END) as completed, SUM(CASE WHEN filing_status = 'Overdue' THEN 1 ELSE 0 END) as overdue, MAX(filing_date) as last_filing_date FROM bir_filing_tracker GROUP BY agency_id, filing_period, form_type; -- Create indexes on materialized view CREATE INDEX idx_mv_bir_agency ON mv_bir_filing_summary(agency_id); CREATE INDEX idx_mv_bir_period ON mv_bir_filing_summary(filing_period); -- Refresh strategy REFRESH MATERIALIZED VIEW CONCURRENTLY mv_bir_filing_summary; -- Schedule: Every 15 minutes or on-demand ``` ### 3. Window Functions ```sql -- Running totals SELECT filing_date, COUNT(*) as daily_count, SUM(COUNT(*)) OVER (ORDER BY filing_date) as cumulative_count FROM bir_filing_tracker WHERE filing_period = '2025-Q4' GROUP BY filing_date ORDER BY filing_date; -- Ranking SELECT agency_name, completion_rate, RANK() OVER (ORDER BY completion_rate DESC) as ranking FROM agency_performance; -- Previous period comparison SELECT month, revenue, LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue, revenue - LAG(revenue, 1) OVER (ORDER BY month) as month_over_month_change FROM monthly_revenue; ``` ### 4. Common Table Expressions (CTEs) ```sql -- Break complex queries into readable steps WITH completed_tasks AS ( SELECT agency_id, closing_period, COUNT(*) as completed_count FROM month_end_closing_tasks WHERE task_status = 'Done' GROUP BY agency_id, closing_period ), total_tasks AS ( SELECT agency_id, closing_period, COUNT(*) as total_count FROM month_end_closing_tasks GROUP BY agency_id, closing_period ) SELECT a.agency_name, c.closing_period, c.completed_count, t.total_count, (c.completed_count::FLOAT / t.total_count::FLOAT * 100) as completion_percentage FROM completed_tasks c JOIN total_tasks t ON c.agency_id = t.agency_id AND c.closing_period = t.closing_period JOIN agencies a ON c.agency_id = a.agency_id ORDER BY completion_percentage DESC; ``` --- ## Finance SSC SQL Patterns ### Pattern 1: BIR Filing Status Query ```sql -- Virtual Dataset: BIR Filing Status Dashboard SELECT bft.filing_id, bft.filing_period, bft.form_type, bft.filing_date, bft.due_date, bft.filing_status, bft.filing_amount, a.agency_id, a.agency_code, a.agency_name, u.full_name as assignee_name, -- Calculated Fields CASE WHEN bft.filing_status = 'Completed' THEN 'Completed' WHEN bft.filing_status = 'Pending' AND bft.due_date >= CURRENT_DATE THEN 'Pending' WHEN bft.filing_status = 'Pending' AND bft.due_date < CURRENT_DATE THEN 'Overdue' ELSE bft.filing_status END as display_status, -- Days variance CASE WHEN bft.filing_date IS NOT NULL THEN DATE_PART('day', bft.filing_date - bft.due_date) ELSE DATE_PART('day', CURRENT_DATE - bft.due_date) END as days_variance, -- Quarter helper 'Q' || DATE_PART('quarter', bft.filing_date) || ' ' || DATE_PART('year', bft.filing_date) as filing_quarter FROM bir_filing_tracker bft JOIN agencies a ON bft.agency_id = a.agency_id LEFT JOIN users u ON bft.assignee_id = u.user_id WHERE bft.filing_date >= '2024-01-01' -- Last 2 years ORDER BY bft.filing_date DESC; ``` **Metrics to Define:** ```yaml Total Filings: COUNT(*) Completed: SUM(CASE WHEN filing_status = 'Completed' THEN 1 ELSE 0 END) Overdue: SUM(CASE WHEN display_status = 'Overdue' THEN 1 ELSE 0 END) Completion Rate: SUM(CASE WHEN filing_status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) Total Amount Filed: SUM(filing_amount) ``` ### Pattern 2: Month-End Closing Progress ```sql -- Virtual Dataset: Month-End Closing Tracker SELECT mect.task_id, mect.closing_period, mect.task_category, mect.task_name, mect.task_description, mect.due_date, mect.completion_date, mect.task_status, mect.priority_level, a.agency_id, a.agency_code, a.agency_name, u.full_name as assignee_name, -- Calculated Fields EXTRACT(EPOCH FROM (mect.completion_date - mect.due_date)) / 86400 as days_variance, CASE WHEN mect.task_status = 'Done' THEN 'Completed' WHEN mect.task_status IN ('In Progress', 'Started') THEN 'In Progress' WHEN mect.due_date < CURRENT_DATE THEN 'Overdue' WHEN mect.due_date <= CURRENT_DATE + INTERVAL '3 days' THEN 'Due Soon' ELSE 'On Track' END as task_health, -- Aging CASE WHEN mect.task_status != 'Done' THEN DATE_PART('day', CURRENT_DATE - mect.due_date) ELSE NULL END as days_overdue FROM month_end_closing_tasks mect JOIN agencies a ON mect.agency_id = a.agency_id LEFT JOIN users u ON mect.assignee_id = u.user_id WHERE mect.closing_period >= TO_CHAR(CURRENT_DATE - INTERVAL '6 months', 'YYYY-MM') ORDER BY mect.due_date; ``` **Metrics to Define:** ```yaml Total Tasks: COUNT(*) Tasks Completed: SUM(CASE WHEN task_status = 'Done' THEN 1 ELSE 0 END) Tasks Overdue: SUM(CASE WHEN task_health = 'Overdue' THEN 1 ELSE 0 END) Completion Percentage: SUM(CASE WHEN task_status = 'Done' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) Avg Days to Complete: AVG(days_variance) FILTER (WHERE task_status = 'Done') ``` ### Pattern 3: InsightPulse AI Processing Metrics ```sql -- Virtual Dataset: Document Processing Analytics SELECT dpl.log_id, dpl.document_id, dpl.document_type, dpl.processing_status, dpl.processed_at, dpl.processing_time_seconds, dpl.ocr_confidence_score, dpl.page_count, dpl.file_size_bytes, dpl.error_message, -- Calculated Fields DATE_TRUNC('hour', dpl.processed_at) as processing_hour, DATE_TRUNC('day', dpl.processed_at) as processing_date, CASE WHEN dpl.processing_status = 'Completed' AND dpl.ocr_confidence_score >= 0.95 THEN 'High Quality' WHEN dpl.processing_status = 'Completed' AND dpl.ocr_confidence_score >= 0.85 THEN 'Good Quality' WHEN dpl.processing_status = 'Completed' THEN 'Review Needed' WHEN dpl.processing_status = 'Failed' THEN 'Failed' ELSE 'Processing' END as quality_status, -- Speed category CASE WHEN dpl.processing_time_seconds <= 2 THEN 'Fast' WHEN dpl.processing_time_seconds <= 5 THEN 'Normal' ELSE 'Slow' END as processing_speed_category, -- File size category CASE WHEN dpl.file_size_bytes < 1000000 THEN 'Small (<1MB)' WHEN dpl.file_size_bytes < 5000000 THEN 'Medium (1-5MB)' ELSE 'Large (>5MB)' END as file_size_category FROM document_processing_logs dpl WHERE dpl.processed_at >= CURRENT_DATE - INTERVAL '30 days' ORDER BY dpl.processed_at DESC; ``` **Metrics to Define:** ```yaml Documents Processed: COUNT(*) Success Rate: SUM(CASE WHEN processing_status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) Avg Processing Time: AVG(processing_time_seconds) Avg OCR Confidence: AVG(ocr_confidence_score) FILTER (WHERE ocr_confidence_score > 0) Error Rate: SUM(CASE WHEN processing_status = 'Failed' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ``` --- ## Time-Series Query Patterns ### Daily/Weekly/Monthly Aggregation ```sql -- Pattern: Flexible time grain aggregation SELECT DATE_TRUNC('day', filing_date) as period, -- Change to 'week', 'month', 'quarter' agency_id, COUNT(*) as filing_count, SUM(filing_amount) as total_amount FROM bir_filing_tracker WHERE filing_date >= '2025-01-01' GROUP BY DATE_TRUNC('day', filing_date), agency_id ORDER BY period, agency_id; ``` ### Period-over-Period Comparison ```sql