In our last lesson, we defined the fundamental vocabulary of data analysis: KPIs, metrics, dimensions, measures, and granularity. These are the building blocks. Today, we'll assemble those blocks into a coherent structure by outlining the typical end-to-end workflow of a data analyst. Understanding this process is key to managing your team's projects, setting realistic expectations, and recognizing the distinct stages of turning a business question into a data-driven answer.
This workflow is a structured methodology, not unlike the software development lifecycle (SDLC) you're familiar with from your work with PHP and Vue.js. Just as you move from requirements gathering to development, testing, and deployment, an analyst follows a parallel path from question to insight.
We will look at this workflow from two complementary perspectives: a high-level conceptual model that defines the purpose of each stage, and a more technical, engineering-focused model that details the tasks.
The Analyst's Journey: A Six-Step Process
A great way to understand the data analysis process at a high level is to see it as a sequence of six core activities: Ask, Prepare, Process, Analyze, Share, and Act. The following video from the Google Data Analytics Certificate program provides an excellent overview of this framework.
Breaking Down the Data Analysis Process | Google Data Analytics Certificate
This video breaks down the data analysis process into six clear, logical steps. It's a widely recognized framework that helps structure any analytical project.
As you watch, focus on how each step builds on the last. We'll examine them one by one. Ask (The "Ask" Phase): This is the crucial starting point. It's about defining the problem and understanding what the stakeholder—often, someone in your position—truly needs. Prepare (The "Prepare" Phase): Once the question is clear, the analyst identifies and collects the necessary data. This involves finding data sources, which could be anything from your company's databases to external third-party sources. Process (The "Process" Phase): Raw data is rarely ready for analysis. This step involves cleaning the data—handling errors, dealing with missing values, and ensuring consistency. This is often the most time-consuming part of the workflow. Analyze (The "Analyze" Phase): With clean data, the analyst performs the actual analysis, looking for patterns, trends, and relationships to answer the initial question. This is where they apply the concepts of measures and dimensions we discussed previously. Share (The "Share" Phase): The findings are communicated to stakeholders. This usually involves creating visualizations, dashboards (like in Power BI), or written reports to tell the story discovered in the data. Act (The "Act" Phase): The final, and most important, step. The insights from the analysis are used to make informed business decisions.
This six-step cycle provides a clear, high-level map of an analyst's responsibilities. As a manager, you will be most involved in the "Ask" and "Act" phases, but understanding the work involved in the intermediate steps is vital for effective project management.
The Developer's View: The Analytics Development Lifecycle (ADLC)
The six-step model is excellent for conceptual understanding. However, for a more technical and operational view that should resonate with your development background, we can look at the Analytics Development Lifecycle (ADLC). This framework, championed by dbt Labs, explicitly treats analytics as an engineering discipline.
What should a data analytics workflow look like? - dbt Labs
This article from dbt Labs frames the analytics workflow in terms familiar to software engineers. It emphasizes reproducibility, testing, and automation—principles you apply daily in your own work.
First, read the introductory section, paying attention to the three roles defined as "hats, not badges". This explains the fluid roles of Engineer, Analyst, and Decision-maker. Next, focus on the main section, The Analytics Development Lifecycle (ADLC), and read through the descriptions of the cyclical stages from Plan to Analyze. As you read, notice the direct parallels to a standard SDLC: Plan: Requirements gathering. Develop: Writing code (SQL, Python). Test: Unit and integration testing for data. Deploy: Automated deployment, akin to CI/CD. Operate and observe: Monitoring and incident response.
The ADLC provides the vocabulary to discuss the how of your team's work. When they talk about testing data models or deploying a new pipeline, they are operating within this lifecycle.
Mapping Concepts to SQL Practice
So how do these abstract stages translate into the day-to-day work of an analyst writing SQL queries? This flowchart provides a concrete map.
Let's connect this to the models we've discussed:
- 1. Data Acquisition & Exploration: This is part of the Prepare phase (Google model) and the Plan/Develop phase (ADLC). The analyst uses commands like
SHOW TABLESandDESCRIBEto understand what data is available. - 2. Data Filtering & Cleaning: This maps directly to the Process phase. Analysts write
SELECTqueries withWHEREclauses to filter data and use functions to handleNULLvalues. - 3. Data Transformation & Joining: This is also part of the Process and Develop phases. Here,
JOINs combine data from different tables, andCASEstatements create new, calculated columns—transforming the raw data into a more useful shape. - 4. Aggregation & Summarization: This is the core of the Analyze phase.
GROUP BYis used with functions likeSUMandCOUNTto aggregate measures by different dimensions. - 5. Advanced Analysis: Still in the Analyze phase, analysts use more complex tools like Window Functions and Common Table Expressions (CTEs) for deeper insights. We will cover these specific SQL techniques in a future module.
- 6. Final Output & Reporting: This is the Share phase. The final, cleaned, and aggregated data is sorted (
ORDER BY) and often sent to a BI tool like Power BI or Metabase for visualization.
Seeing It All Come Together: An End-to-End Project
To make this entire process tangible, let's watch segments of a video where an analyst tackles a project from start to finish. The project involves taking a request, using SQL to prepare data, and building a Power BI dashboard to deliver recommendations.
End-to-End Data Analysis Project 2024 | SQL & Power BI | Beginner Friendly
This video by Absent Data provides a practical, step-by-step walkthrough of a complete data analysis project. We'll use it to see our workflow models in action.
You don't need to watch the entire video or follow the technical steps. Just watch these key segments to see how the phases connect: The business request: This is the Ask phase. An email from a stakeholder outlines the requirements and the central business question. SQL development: This covers the Prepare, Process, and Analyze phases. The analyst writes SQL queries to combine tables (UNION), join data (LEFT JOIN), and create new calculated columns (Revenue, Profit) using a Common Table Expression (CTE). Dashboard creation: This is the Share phase. The data from the SQL query is loaded into Power BI, and various charts are built to visualize the findings. Answering the question: This is the Act phase. The analyst uses the dashboard to analyze the data and formulates a specific, data-driven recommendation in response to the original business question.
This example demonstrates how the different stages—asking, preparing, processing, analyzing, sharing, and acting—form a continuous flow that turns raw data into a valuable business asset.
Conclusion
In this lesson, we've deconstructed the data analyst's workflow. You now have a mental map of the journey from a business question to a data-driven action.
Key takeaways:
- The data analysis process can be understood through a high-level, six-step framework: Ask, Prepare, Process, Analyze, Share, and Act.
- A more technical perspective, the Analytics Development Lifecycle (ADLC), frames analytics with the same rigor as software engineering, using familiar concepts like planning, developing, testing, and deploying.
- These abstract phases translate into concrete SQL operations like filtering, joining, transforming, and aggregating data.
- The ultimate goal of the workflow is to enable the Act phase, where insights are used to drive decisions.
In our next lesson, we will dive deeper into a key concept from the "Prepare" phase: the distinction between an operational database (OLTP) and an analytical database (OLAP). Understanding this difference is crucial for appreciating why your team needs specialized systems like data warehouses for their work.