Skip to content

DATA ANALYST.md

Latest commit

 

History

History
277 lines (203 loc) · 8.47 KB

DATA ANALYST.md

File metadata and controls

277 lines (203 loc) · 8.47 KB
Raw

🚀 Data Analyst Roadmap: From Zero to Job-Ready (2025 Edition)

Complete Learning Path for Python, Power BI & Advanced Excel

(Includes all levels: Foundations → Intermediate → Advanced) For beginners to career switchers | Covers: Excel, Power BI, SQL, Python, Projects, and Job Prep

🎥 Recommended Video Resource

Data Analyst Roadmap Video

🔰 PHASE 0: Prerequisites (Optional for Tech Folks)

  • Computer Basics: OS, folders, shortcuts, installations
  • Math Refresher: Averages, ratios, percentages
  • Basic Stats: Mean, median, mode, std dev, probability

💻 PHASE 1: Core Tools Mastery (Excel → Power BI → Python)

📊 Excel for Data Analysis

Level 1 – Basics

  • Formulas: SUM, IF, VLOOKUP, INDEX, MATCH
  • Conditional formatting, filtering, sorting

Level 2 – Intermediate

  • Pivot Tables, Charts, Date & Text functions
  • XLOOKUP, Data Validation, Named Ranges

Level 3 – Analyst Level

  • Power Query (ETL with no-code)
  • DAX & Power Pivot
  • Dashboards with slicers & timelines
  • Solver, What-if, Scenario Manager
  • 🧠 Bonus: Basic Macros

🐍 Python for Data Analysis

Level 1 – Core Python

  • Data types, conditionals, loops, functions
  • File I/O: CSV, Excel
  • Error handling, environment setup (Jupyter/VS Code)

Level 2 – Data Analysis

  • pandas, numpy for EDA
  • Handling missing data, duplicates, type conversions
  • Merging, grouping, pivoting data
  • Visuals: matplotlib, seaborn, plotly

Level 3 – Advanced Python

  • Web scraping: requests, BeautifulSoup, Selenium
  • APIs: JSON data handling
  • Time series: datetime, resample, rolling
  • Automation: openpyxl, xlsxwriter
  • Forecasting: statsmodels, Prophet
  • Large data: Dask, Modin
  • 🧠 ML optional: scikit-learn basics

📈 Basic Power BI (Desktop + Service)

Level 1 – Beginner

  • Load data (Excel, CSV)
  • Power Query cleaning
  • Simple visuals & filters

Level 2 – Intermediate

  • Data Modeling & Relationships
  • DAX basics: CALCULATE, FILTER, IF, SUMX
  • Time Intelligence: DATESYTD, SAMEPERIODLASTYEAR

Level 3 – Pro Level

  • Advanced visuals, bookmarks, buttons
  • Row-Level Security (RLS)
  • Publishing reports & setting refresh in Power BI Service

🧠 PHASE 2: Core Analytics Concepts

These apply across Excel, Power BI, Python & SQL

  • Exploratory Data Analysis (EDA)
  • Distributions, outliers, skewness
  • Correlation vs Causation
  • Hypothesis Testing (t-test, chi-square, ANOVA)
  • Regression (linear/logistic)
  • A/B Testing
  • Data Cleaning & Wrangling
  • Scaling, normalization, encoding
  • Feature engineering basics

🧮 PHASE 3: SQL & Databases (Basics)

Core SQL

  • SELECT, WHERE, GROUP BY, ORDER BY
  • Joins: INNER, LEFT, RIGHT, FULL
  • Aggregations & filtering
  • CASE, COALESCE

Advanced SQL

  • Subqueries & Nested queries
  • CTEs (Common Table Expressions)
  • Window functions: ROW_NUMBER(), RANK(), LEAD()

Database Knowledge

  • Relational vs Non-relational DBs
  • Normalization (1NF, 2NF, 3NF)
  • ER Diagrams & Schemas

🧮 PHASE 4 : Data Analytics & Engineering (Advance Learning)

Master the tools that handle massive data. This phase moves you from data analyst to engineer-level data processing.

🔢 SQL (MySQL, PostgreSQL)

Level 1 – Basics

  • SELECT, WHERE, GROUP BY, ORDER BY
  • Filtering with conditions
  • Joins: INNER, LEFT, RIGHT, FULL
  • Aliasing and CASE statements

📚 W3Schools SQL Tutorial
📹 MySQL Full Course – freeCodeCamp
📹 PostgreSQL Full Course – Amigoscode

Level 2 – Advanced SQL

  • Subqueries & Nested Queries
  • CTEs (WITH clause)
  • Window Functions: ROW_NUMBER(), RANK(), LEAD()
  • Views, Indexes, Performance tips

📘 Mode SQL Tutorial
📹 Advanced SQL Course – freeCodeCamp

🧊 Snowflake + Hive

Snowflake

  • Warehousing Concepts
  • Virtual Warehouses, Databases, Schemas
  • SQL in Snowflake
  • Time Travel & Cloning

📘 Snowflake Official Docs
📹 Snowflake Full Course – Simplilearn

Apache Hive

  • Hive Architecture (Metastore, Driver, Compiler)
  • HiveQL (similar to SQL)
  • Partitions & Buckets
  • Joins and UDFs

📘 Hive Tutorial – TutorialsPoint
📹 Hive Full Course – Edureka

⚡ Apache Spark

Spark with Python (PySpark)

  • RDDs, DataFrames
  • Transformations vs Actions
  • Spark SQL
  • Machine Learning with Spark MLlib
  • Real-time streaming with Spark Streaming

📘 Spark Guide – DataBricks
📹 PySpark Full Course – freeCodeCamp

📡 Apache Kafka

  • Kafka Architecture (Brokers, Producers, Consumers)
  • Topics & Partitions
  • Real-time data pipelines
  • Kafka Streams & Connect

📘 Apache Kafka Docs
📹 Kafka Full Course – Simplilearn

💾 Databricks

  • Notebooks and Clusters
  • Delta Lake
  • MLflow integration
  • SQL + Python + Spark in one place

📘 Databricks Academy Free Training
📹 Databricks for Beginners – Simplilearn

🟥 Amazon Redshift

  • Data Warehousing Concepts
  • Columnar Storage
  • Spectrum & Federated Queries
  • Integrating with BI tools

📘 AWS Redshift Docs
📹 Amazon Redshift Tutorial – AWS

✅ Wrap-Up Checklist

  • Mastered SQL across two RDBMS
  • Built at least one ETL pipeline
  • Understood Hive & Data Lakehouse
  • Deployed Spark Jobs locally or via Databricks
  • Published a dashboard powered by Snowflake/Redshift
  • Hosted projects on GitHub with README

📁 PHASE 5: Portfolio Projects

Time to show what you can do. Build, host, explain.

Project Tools Used
Sales Dashboard Excel, Power BI
Netflix Dataset EDA Python (Pandas, Seaborn)
Customer Churn Prediction Python (Scikit-learn, Seaborn)
Web-Scraped News Headlines Python (BeautifulSoup, Pandas)
COVID-19 Time-Series Forecast Python (Prophet)
Dynamic BI Dashboard Power BI
Automated Excel Report Python (OpenPyXL, XlsxWriter)
Real-Time Log Processing Kafka, Spark
Large-Scale Sales Aggregation SQL, Redshift
Cloud BI Dashboard Power BI, Snowflake
ETL Pipeline Python, Hive/Spark, Redshift
Data Engineering Notebook Databricks, PySpark

📦 Host all projects on GitHub with clean READMEs.

🎯 PHASE 6: Getting Job-Ready

📝 Resume & LinkedIn

  • List tools + projects with business impact
  • Clean design, metrics-driven bullets
  • LinkedIn headline + featured section

📂 GitHub Portfolio

  • Jupyter Notebooks with visuals
  • Excel dashboards as downloads
  • Power BI links (PDF exports or screenshots)

🔁 Practice

  • SQL: StrataScratch, LeetCode SQL
  • Excel: ExcelJet, Spreadsheeto
  • Python: HackerRank, DataCamp
  • Mock Interviews: Use Glassdoor Qs

🛠 BONUS PHASE: Power Skills (Optional but Recommended)

  • ✅ Tableau: For broader BI skills
  • ✅ Git/GitHub: Version control, collaboration
  • ✅ Streamlit/Dash: Turn Python into web dashboards
  • ✅ Cloud (AWS/GCP/Azure): S3, Lambda, RDS basics
  • ✅ APIs: Connect apps, scrape data

🔁 Suggested Learning Flow

For Non-Tech Background:

SQL → Power BI → Snowflake → Hive → Spark → Kafka → Redshift → Databricks

For Tech Background:

Python → SQL → Spark → Hive → Kafka → Snowflake → Redshift → Databricks → Power BI