Data Analysis Workflows
Complete workflows for common data analysis tasks. Covers the full process from understanding requirements to delivering validated results.
Workflow 1: Answer Data Questions
Answer a data question, from a quick lookup to a full analysis to a formal report.
1. Understand the Question
Parse the user's question and determine:
- Complexity level:
- Quick answer: Single metric, simple filter, factual lookup (e.g., "How many users signed up last week?")
- Full analysis: Multi-dimensional exploration, trend analysis, comparison (e.g., "What's driving the drop in conversion rate?")
- Formal report: Comprehensive investigation with methodology, caveats, and recommendations (e.g., "Prepare a quarterly business review of our subscription metrics")
- Data requirements: Which tables, metrics, dimensions, and time ranges are needed
- Output format: Number, table, chart, narrative, or combination
2. Gather Data
Options for data access:
- Ask the user to provide data:
- Paste query results directly
- Upload a CSV or Excel file
- Describe the schema so you can write queries for them to run
- If writing queries for manual execution, use the `sql-queries` skill for dialect-specific best practices
- Once data is provided, proceed with analysis
3. Analyze
- Calculate relevant metrics, aggregations, and comparisons
- Identify patterns, trends, outliers, and anomalies
- Compare across dimensions (time periods, segments, categories)
- For complex analyses, break the problem into sub-questions and address each
4. Validate Before Presenting
Before sharing results, run through validation checks:
- Row count sanity: Does the number of records make sense?
- Null check: Are there unexpected nulls that could skew results?
- Magnitude check: Are the numbers in a reasonable range?
- Trend continuity: Do time series have unexpected gaps?
- Aggregation logic: Do subtotals sum to totals correctly?
If any check raises concerns, investigate and note caveats.
5. Present Findings
For quick answers:
- State the answer directly with relevant context
- Include the query used (collapsed or in a code block) for reproducibility
For full analyses:
- Lead with the key finding or insight
- Support with data tables and/or visualizations
- Note methodology and any caveats
- Suggest follow-up questions
For formal reports:
- Executive summary with key findings
- Methodology section explaining approach and data sources
- Detailed findings with supporting evidence
- Caveats and limitations
- Recommendations and next steps
- Appendix with full queries and data tables
Workflow 2: Explore and Profile Datasets
Generate a comprehensive data profile for a table or uploaded file. Understand its shape, quality, and patterns before diving into analysis.
1. Access the Data
If a file is provided (CSV, Excel, Parquet, JSON):
- Read the file and load into a working dataset
- If the file is large, sample it for profiling
If user describes a table:
- Ask for sample data or schema information
- Generate profiling queries for the user to run
2. Profile the Dataset
Generate a report covering:
Basic Statistics
- Row count: Total number of records
- Column count: Number of fields
- Primary key: Identify unique identifier(s)
- Grain: One row per what? (user, order, event, etc.)
- Time range: For temporal data, first and last dates
Column-Level Analysis
For each column, report:
- Type: String, integer, float, date, boolean, etc.
- Null rate: Percentage of missing values
- Cardinality: Number of unique values
- Sample values: Examples of what the column contains
- Distribution: For numeric columns, min/max/mean/median/std dev
Data Quality Flags
Automatically identify potential issues:
- High null rates (>10%)
- Unexpected values (e.g., negative values in a count field)
- Duplicate rows
- Inconsistent formatting (mixed case, trailing spaces)
- Time gaps in temporal data
3. Recommend Next Steps
Based on profiling results, suggest:
- High-value dimensions to explore (columns with moderate cardinality, low nulls)
- Data quality issues to address before analysis
- Potential relationships to investigate (correlated columns, hierarchies)
- Analysis starting points based on the data's structure
Workflow 3: Write Optimized SQL
Write a SQL query from a natural language description, optimized for your specific SQL dialect and following best practices.
1. Understand the Request
Parse the user's description to identify:
- Output columns: What fields should the result include?
- Filters: What conditions limit the data (time ranges, segments, statuses)?
- Aggregations: Are there GROUP BY operations, counts, sums, averages?
- Joins: Does this require combining multiple tables?
- Ordering: How should results be sorted?
- Limits: Is there a top-N or sample requirement?
2. Determine SQL Dialect
Ask the user which database they're using if not specified:
- PostgreSQL / Aurora / Supabase
- Snowflake
- BigQuery
- Databricks / Spark SQL
- Redshift
- MySQL / MariaDB
Each dialect has syntax differences for date functions, string operations, and window functions.
3. Write the Query
Use CTEs (Common Table Expressions) for complex queries:
```sql
WITH base_data AS (
-- First CTE: Get the base dataset
SELECT ...
),
aggregated AS (
-- Second CTE: Perform aggregations
SELECT ...
)
-- Final SELECT from CTEs
SELECT * FROM aggregated;
```
Best practices:
- Use explicit JOIN syntax (not implicit joins in WHERE clause)
- Qualify all column names with table aliases when joining
- Use meaningful CTE and alias names
- Add comments explaining complex logic
- Format for readability (indent, line breaks)
- Consider performance (filter early, avoid SELECT *, use appropriate indexes)
4. Add Context
Include with the query:
- Purpose: One-line description of what the query does
- Tables used: Which tables are referenced
- Performance notes: Expected row counts, potential bottlenecks
- Dialect-specific notes: Any syntax specific to the database being used
Workflow 4: Create Visualizations
Create publication-quality data visualizations using Python. Generates charts from data with best practices for clarity, accuracy, and design.
1. Understand the Request
Determine:
- Data source: Query results, pasted data, CSV/Excel file, or data to be queried
- Chart type: Explicitly requested or needs to be recommended
- Purpose: Exploration, presentation, report, dashboard component
- Audience: Technical team, executives, external stakeholders
2. Get the Data
If data is not yet provided:
- Ask the user to paste data or upload a file
- If querying is needed, use the SQL workflow to generate the query first
3. Choose the Right Chart Type
Select visualization based on what you're showing:
| What You're Showing |
Best Chart |
When to Use |
| Trend over time |
Line chart |
Continuous time series, shows change |
| Comparison across categories |
Bar chart |
Comparing discrete categories |
| Part-to-whole |
Stacked bar / Pie |
Composition, relative sizes |
| Distribution |
Histogram / Box plot |
Understanding spread and outliers |
| Correlation |
Scatter plot |
Relationship between two variables |
| Ranking |
Horizontal bar |
Ordered list, easy to read labels |
4. Generate Python Code
Use `plotly` for interactive charts, `seaborn` or `matplotlib` for static charts.
Chart template pattern:
```python
import matplotlib.pyplot as plt
import pandas as pd
Load data
df = pd.DataFrame({...})
Create figure
fig, ax = plt.subplots(figsize=(10, 6))
Plot
ax.plot(df['x'], df['y']) # or .bar(), .scatter(), etc.
Style
ax.set_title('Clear, Descriptive Title', fontsize=14, fontweight='bold')
ax.set_xlabel('X Axis Label')
ax.set_ylabel('Y Axis Label')
ax.grid(axis='y', alpha=0.3)
Show
plt.tight_layout()
plt.show()
```
5. Apply Design Principles
- Clarity: Remove chart junk, use clear labels
- Accuracy: Don't truncate axes to exaggerate differences
- Accessibility: Use colorblind-friendly palettes
- Context: Add reference lines, annotations for key points
Workflow 5: Build Interactive Dashboards
Build a self-contained interactive HTML dashboard with charts, filters, tables, and professional styling. Opens directly in a browser -- no server or dependencies required.
1. Understand Dashboard Requirements
Determine:
- Purpose: Executive overview, operational monitoring, deep-dive analysis, team reporting
- Audience: Who will use this dashboard?
- Key metrics: What numbers matter most?
- Dimensions: What should users be able to filter or slice by?
- Data source: Live query, pasted data, CSV file, or sample data
2. Gather the Data
If data needs to be queried, use the SQL workflow.
If data is provided, ensure it's in a structured format (CSV or JSON).
3. Design Dashboard Layout
Organize into sections:
- Header: Title, date range, key filters
- KPI cards: 3-5 big numbers that matter most
- Primary charts: 2-3 main visualizations (trends, comparisons)
- Details section: Tables or secondary charts for drill-down
- Footer: Methodology notes, data source, last updated
4. Generate HTML/JS Code
Use this template structure:
```html
// Charts
// Filters
// Interactivity
</script>
5. Add Interactivity
Implement:
- Dropdown filters: Filter by category, time range, segment
- Chart interactions: Hover tooltips, click to drill-down
- Table sorting: Click column headers to sort
- Dynamic updates: Filters update all charts simultaneously
Workflow 6: Validate Analysis Before Sharing
Review an analysis for accuracy, methodology, and potential biases before sharing with stakeholders. Generates a confidence assessment and improvement suggestions.
1. Review Methodology and Assumptions
Examine:
Data selection:
- Which tables/sources were used? Are they the right ones?
- What time range? Is it recent enough?
- Any filters applied? Do they introduce bias?
Aggregation logic:
- Are groupings appropriate for the question?
- Do subtotals match totals?
- Are nulls handled correctly (excluded vs. counted as zero)?
Statistical methods:
- If using averages, is median more appropriate?
- If comparing percentages, are denominators consistent?
- If showing trends, is seasonality accounted for?
2. Check for Common Pitfalls
Survivorship bias:
- Example: Analyzing only current customers excludes churned users
- Check: Does the analysis need to include historical records?
Selection bias:
- Example: Survey responses from only power users
- Check: Is the sample representative of the population?
Simpson's paradox:
- Example: Overall trend contradicts subgroup trends when aggregated
- Check: Break down by segments before concluding
Correlation vs. causation:
- Example: Ice cream sales correlate with drowning deaths (both caused by summer)
- Check: Are confounding variables at play?
3. Sanity Check Results
Compare results to expectations:
- Magnitude: Are numbers in the right ballpark?
- Direction: Do trends match known business context?
- Consistency: Do related metrics tell the same story?
If something looks off:
- Recheck the query
- Look for data quality issues
- Consider if external factors could explain it
4. Assess Confidence Level
Rate confidence as:
- High confidence: Methodology sound, results validated, limitations documented
- Medium confidence: Minor concerns or caveats, but core findings are solid
- Needs more work: Methodological issues or data quality problems need addressing
5. Document Limitations
Every analysis has limits. Document:
- What's excluded from the data
- Assumptions made
- Margin of error or uncertainty
- Known data quality issues
- Alternative interpretations
Example caveat:
"This analysis excludes trial users, which may overstate conversion rate by ~5pp compared to total signups."
When to Use Each Workflow
- Answer Data Questions: User has a specific question to answer
- Explore Datasets: Encountering a new table or dataset
- Write SQL: Need to generate queries for manual execution
- Create Visualizations: Need charts for reports or presentations
- Build Dashboards: Need interactive, shareable dashboards
- Validate Analysis: Before sharing important findings with stakeholders
Use these workflows in combination for complex projects. For example:
- Explore data → 2. Write SQL → 3. Create visualization → 4. Validate → 5. Share
1---
2name: data-analysis-workflows
3description: >
4 Comprehensive data analysis workflows including answering data questions, exploring datasets,
5 writing SQL queries, creating visualizations, building dashboards, and validating analyses.
6 Use when conducting data analysis tasks, from quick lookups to comprehensive reports.
7metadata:
8 source: https://skillmd.com/skills/ahang1598/data-analysis-workflows
9---
10
11# Data Analysis Workflows
12
13Complete workflows for common data analysis tasks. Covers the full process from understanding requirements to delivering validated results.
14
15## Workflow 1: Answer Data Questions
16
17Answer a data question, from a quick lookup to a full analysis to a formal report.
18
19### 1. Understand the Question
20
21Parse the user's question and determine:
22
23- **Complexity level**:
24 - **Quick answer**: Single metric, simple filter, factual lookup (e.g., "How many users signed up last week?")
25 - **Full analysis**: Multi-dimensional exploration, trend analysis, comparison (e.g., "What's driving the drop in conversion rate?")
26 - **Formal report**: Comprehensive investigation with methodology, caveats, and recommendations (e.g., "Prepare a quarterly business review of our subscription metrics")
27- **Data requirements**: Which tables, metrics, dimensions, and time ranges are needed
28- **Output format**: Number, table, chart, narrative, or combination
29
30### 2. Gather Data
31
32**Options for data access:**
33
341. Ask the user to provide data:
35 - Paste query results directly
36 - Upload a CSV or Excel file
37 - Describe the schema so you can write queries for them to run
382. If writing queries for manual execution, use the \`sql-queries\` skill for dialect-specific best practices
393. Once data is provided, proceed with analysis
40
41### 3. Analyze
42
43- Calculate relevant metrics, aggregations, and comparisons
44- Identify patterns, trends, outliers, and anomalies
45- Compare across dimensions (time periods, segments, categories)
46- For complex analyses, break the problem into sub-questions and address each
47
48### 4. Validate Before Presenting
49
50Before sharing results, run through validation checks:
51
52- **Row count sanity**: Does the number of records make sense?
53- **Null check**: Are there unexpected nulls that could skew results?
54- **Magnitude check**: Are the numbers in a reasonable range?
55- **Trend continuity**: Do time series have unexpected gaps?
56- **Aggregation logic**: Do subtotals sum to totals correctly?
57
58If any check raises concerns, investigate and note caveats.
59
60### 5. Present Findings
61
62**For quick answers:**
63- State the answer directly with relevant context
64- Include the query used (collapsed or in a code block) for reproducibility
65
66**For full analyses:**
67- Lead with the key finding or insight
68- Support with data tables and/or visualizations
69- Note methodology and any caveats
70- Suggest follow-up questions
71
72**For formal reports:**
73- Executive summary with key findings
74- Methodology section explaining approach and data sources
75- Detailed findings with supporting evidence
76- Caveats and limitations
77- Recommendations and next steps
78- Appendix with full queries and data tables
79
80---
81
82## Workflow 2: Explore and Profile Datasets
83
84Generate a comprehensive data profile for a table or uploaded file. Understand its shape, quality, and patterns before diving into analysis.
85
86### 1. Access the Data
87
88**If a file is provided (CSV, Excel, Parquet, JSON):**
891. Read the file and load into a working dataset
902. If the file is large, sample it for profiling
91
92**If user describes a table:**
931. Ask for sample data or schema information
942. Generate profiling queries for the user to run
95
96### 2. Profile the Dataset
97
98Generate a report covering:
99
100#### Basic Statistics
101- **Row count**: Total number of records
102- **Column count**: Number of fields
103- **Primary key**: Identify unique identifier(s)
104- **Grain**: One row per what? (user, order, event, etc.)
105- **Time range**: For temporal data, first and last dates
106
107#### Column-Level Analysis
108
109For each column, report:
110- **Type**: String, integer, float, date, boolean, etc.
111- **Null rate**: Percentage of missing values
112- **Cardinality**: Number of unique values
113- **Sample values**: Examples of what the column contains
114- **Distribution**: For numeric columns, min/max/mean/median/std dev
115
116#### Data Quality Flags
117
118Automatically identify potential issues:
119- High null rates (>10%)
120- Unexpected values (e.g., negative values in a count field)
121- Duplicate rows
122- Inconsistent formatting (mixed case, trailing spaces)
123- Time gaps in temporal data
124
125### 3. Recommend Next Steps
126
127Based on profiling results, suggest:
128- **High-value dimensions** to explore (columns with moderate cardinality, low nulls)
129- **Data quality issues** to address before analysis
130- **Potential relationships** to investigate (correlated columns, hierarchies)
131- **Analysis starting points** based on the data's structure
132
133---
134
135## Workflow 3: Write Optimized SQL
136
137Write a SQL query from a natural language description, optimized for your specific SQL dialect and following best practices.
138
139### 1. Understand the Request
140
141Parse the user's description to identify:
142
143- **Output columns**: What fields should the result include?
144- **Filters**: What conditions limit the data (time ranges, segments, statuses)?
145- **Aggregations**: Are there GROUP BY operations, counts, sums, averages?
146- **Joins**: Does this require combining multiple tables?
147- **Ordering**: How should results be sorted?
148- **Limits**: Is there a top-N or sample requirement?
149
150### 2. Determine SQL Dialect
151
152Ask the user which database they're using if not specified:
153- PostgreSQL / Aurora / Supabase
154- Snowflake
155- BigQuery
156- Databricks / Spark SQL
157- Redshift
158- MySQL / MariaDB
159
160Each dialect has syntax differences for date functions, string operations, and window functions.
161
162### 3. Write the Query
163
164Use CTEs (Common Table Expressions) for complex queries:
165
166\`\`\`sql
167WITH base_data AS (
168 -- First CTE: Get the base dataset
169 SELECT ...
170),
171aggregated AS (
172 -- Second CTE: Perform aggregations
173 SELECT ...
174)
175-- Final SELECT from CTEs
176SELECT * FROM aggregated;
177\`\`\`
178
179**Best practices:**
180- Use explicit JOIN syntax (not implicit joins in WHERE clause)
181- Qualify all column names with table aliases when joining
182- Use meaningful CTE and alias names
183- Add comments explaining complex logic
184- Format for readability (indent, line breaks)
185- Consider performance (filter early, avoid SELECT *, use appropriate indexes)
186
187### 4. Add Context
188
189Include with the query:
190- **Purpose**: One-line description of what the query does
191- **Tables used**: Which tables are referenced
192- **Performance notes**: Expected row counts, potential bottlenecks
193- **Dialect-specific notes**: Any syntax specific to the database being used
194
195---
196
197## Workflow 4: Create Visualizations
198
199Create publication-quality data visualizations using Python. Generates charts from data with best practices for clarity, accuracy, and design.
200
201### 1. Understand the Request
202
203Determine:
204
205- **Data source**: Query results, pasted data, CSV/Excel file, or data to be queried
206- **Chart type**: Explicitly requested or needs to be recommended
207- **Purpose**: Exploration, presentation, report, dashboard component
208- **Audience**: Technical team, executives, external stakeholders
209
210### 2. Get the Data
211
212If data is not yet provided:
213- Ask the user to paste data or upload a file
214- If querying is needed, use the SQL workflow to generate the query first
215
216### 3. Choose the Right Chart Type
217
218Select visualization based on what you're showing:
219
220| What You're Showing | Best Chart | When to Use |
221|---------------------|------------|-------------|
222| **Trend over time** | Line chart | Continuous time series, shows change |
223| **Comparison across categories** | Bar chart | Comparing discrete categories |
224| **Part-to-whole** | Stacked bar / Pie | Composition, relative sizes |
225| **Distribution** | Histogram / Box plot | Understanding spread and outliers |
226| **Correlation** | Scatter plot | Relationship between two variables |
227| **Ranking** | Horizontal bar | Ordered list, easy to read labels |
228
229### 4. Generate Python Code
230
231Use \`plotly\` for interactive charts, \`seaborn\` or \`matplotlib\` for static charts.
232
233**Chart template pattern:**
234
235\`\`\`python
236import matplotlib.pyplot as plt
237import pandas as pd
238
239# Load data
240df = pd.DataFrame({...})
241
242# Create figure
243fig, ax = plt.subplots(figsize=(10, 6))
244
245# Plot
246ax.plot(df['x'], df['y']) # or .bar(), .scatter(), etc.
247
248# Style
249ax.set_title('Clear, Descriptive Title', fontsize=14, fontweight='bold')
250ax.set_xlabel('X Axis Label')
251ax.set_ylabel('Y Axis Label')
252ax.grid(axis='y', alpha=0.3)
253
254# Show
255plt.tight_layout()
256plt.show()
257\`\`\`
258
259### 5. Apply Design Principles
260
261- **Clarity**: Remove chart junk, use clear labels
262- **Accuracy**: Don't truncate axes to exaggerate differences
263- **Accessibility**: Use colorblind-friendly palettes
264- **Context**: Add reference lines, annotations for key points
265
266---
267
268## Workflow 5: Build Interactive Dashboards
269
270Build a self-contained interactive HTML dashboard with charts, filters, tables, and professional styling. Opens directly in a browser -- no server or dependencies required.
271
272### 1. Understand Dashboard Requirements
273
274Determine:
275
276- **Purpose**: Executive overview, operational monitoring, deep-dive analysis, team reporting
277- **Audience**: Who will use this dashboard?
278- **Key metrics**: What numbers matter most?
279- **Dimensions**: What should users be able to filter or slice by?
280- **Data source**: Live query, pasted data, CSV file, or sample data
281
282### 2. Gather the Data
283
284If data needs to be queried, use the SQL workflow.
285If data is provided, ensure it's in a structured format (CSV or JSON).
286
287### 3. Design Dashboard Layout
288
289Organize into sections:
290
2911. **Header**: Title, date range, key filters
2922. **KPI cards**: 3-5 big numbers that matter most
2933. **Primary charts**: 2-3 main visualizations (trends, comparisons)
2944. **Details section**: Tables or secondary charts for drill-down
2955. **Footer**: Methodology notes, data source, last updated
296
297### 4. Generate HTML/JS Code
298
299Use this template structure:
300
301\`\`\`html
302<!DOCTYPE html>
303<html lang="en">
304<head>
305 <meta charset="UTF-8">
306 <meta name="viewport" content="width=device-width, initial-scale=1.0">
307 <title>Dashboard Title</title>
308 <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.min.js"></script>
309 <style>
310 /* CSS styling */
311 </style>
312</head>
313<body>
314 <div class="container">
315 <!-- Dashboard content -->
316 </div>
317 <script>
318 // Data
319 const data = { /* embed data here */ };
320
321 // Charts
322 // Filters
323 // Interactivity
324 </script>
325</body>
326</html>
327\`\`\`
328
329### 5. Add Interactivity
330
331Implement:
332- **Dropdown filters**: Filter by category, time range, segment
333- **Chart interactions**: Hover tooltips, click to drill-down
334- **Table sorting**: Click column headers to sort
335- **Dynamic updates**: Filters update all charts simultaneously
336
337---
338
339## Workflow 6: Validate Analysis Before Sharing
340
341Review an analysis for accuracy, methodology, and potential biases before sharing with stakeholders. Generates a confidence assessment and improvement suggestions.
342
343### 1. Review Methodology and Assumptions
344
345Examine:
346
347**Data selection:**
348- Which tables/sources were used? Are they the right ones?
349- What time range? Is it recent enough?
350- Any filters applied? Do they introduce bias?
351
352**Aggregation logic:**
353- Are groupings appropriate for the question?
354- Do subtotals match totals?
355- Are nulls handled correctly (excluded vs. counted as zero)?
356
357**Statistical methods:**
358- If using averages, is median more appropriate?
359- If comparing percentages, are denominators consistent?
360- If showing trends, is seasonality accounted for?
361
362### 2. Check for Common Pitfalls
363
364**Survivorship bias:**
365- Example: Analyzing only current customers excludes churned users
366- Check: Does the analysis need to include historical records?
367
368**Selection bias:**
369- Example: Survey responses from only power users
370- Check: Is the sample representative of the population?
371
372**Simpson's paradox:**
373- Example: Overall trend contradicts subgroup trends when aggregated
374- Check: Break down by segments before concluding
375
376**Correlation vs. causation:**
377- Example: Ice cream sales correlate with drowning deaths (both caused by summer)
378- Check: Are confounding variables at play?
379
380### 3. Sanity Check Results
381
382Compare results to expectations:
383
384- **Magnitude**: Are numbers in the right ballpark?
385- **Direction**: Do trends match known business context?
386- **Consistency**: Do related metrics tell the same story?
387
388If something looks off:
389- Recheck the query
390- Look for data quality issues
391- Consider if external factors could explain it
392
393### 4. Assess Confidence Level
394
395Rate confidence as:
396
397- **High confidence**: Methodology sound, results validated, limitations documented
398- **Medium confidence**: Minor concerns or caveats, but core findings are solid
399- **Needs more work**: Methodological issues or data quality problems need addressing
400
401### 5. Document Limitations
402
403Every analysis has limits. Document:
404- What's excluded from the data
405- Assumptions made
406- Margin of error or uncertainty
407- Known data quality issues
408- Alternative interpretations
409
410**Example caveat:**
411> "This analysis excludes trial users, which may overstate conversion rate by ~5pp compared to total signups."
412
413---
414
415## When to Use Each Workflow
416
417- **Answer Data Questions**: User has a specific question to answer
418- **Explore Datasets**: Encountering a new table or dataset
419- **Write SQL**: Need to generate queries for manual execution
420- **Create Visualizations**: Need charts for reports or presentations
421- **Build Dashboards**: Need interactive, shareable dashboards
422- **Validate Analysis**: Before sharing important findings with stakeholders
423
424Use these workflows in combination for complex projects. For example:
4251. Explore data → 2. Write SQL → 3. Create visualization → 4. Validate → 5. Share