Video Summary - How Data Analyst Projects Work in Big Companies (What Freshers Are Never Told)

Main ideas / lesson

In big companies, Data Analyst/BI work rarely looks like “download a dataset and build a Power BI report.”
Instead, BI follows a modern, multi-layer architecture where different specialists build and manage each layer.
Freshers should understand the full data pipeline and architecture so they can:
- troubleshoot issues in “support” work (know where data comes from and where it goes)
- explain real project experience in interviews (architecture-level reasoning matters more than memorizing specific tools)

Modern BI / enterprise data architecture (as described)

1) Data sources (single origin systems)

Data originates in an ERP/CRM-type system, e.g.:
- Salesforce, SAP, and other ERP/CRM tools
This incoming data is considered raw and not directly usable for business insights.

Implication: the business can’t gain insights from raw data alone—BI solutions require processed, modeled data.

2) Data Engineering layer (ETL/ELT into staging)

Purpose: move data from source systems into a staging area.

A Data Engineer performs:
- ETL/ELT pipelines (tools mentioned include ETL/ELT and ADF)
- ingest data from ERP/CRM systems
- store/land it in a staging layer

3) Data Warehouse layer (build business-ready structures)

Purpose: transform staging/raw data into analytics structures.

Common warehouse options mentioned:
- Azure Data Warehouse
- Databricks

Inside the data warehouse: transformations and outputs

Data arrives still raw, not prepared for business.
Business requirements define what outputs are needed (e.g., “KPI ABC from data XYZ”).
The warehouse uses:
- Stored procedures to implement business logic and transformations
- Stored procedures may include:
  - joins across datasets
  - inserts/updates
  - reusable business logic (e.g., converting amounts using exchange rates into multiple currencies)

Layers / artifacts created in the warehouse

Stored procedures handle heavy logic, but the final consumed output for the next layer is described as:
- Views (not stored procedures themselves, and not materialized views for final consumption)
Outputs are organized into:
- Facts: transactional/measurement data
  - e.g., order numbers, settled amounts, requests made
- Dimensions: descriptive entity attributes
  - e.g., product info, customer info, employee info (name, phone, etc.)

Fallback/optimization (as described)

If needed, the speaker describes:
- using Materialized Views over raw/multi-table data
- then pipeline execution “builds into” a table
- and ends with views again as final output for downstream layers

4) Semantic Layer (security + modeling + KPIs; Power BI consumes from here)

Purpose: provide governed, business-friendly metrics and modeling while preventing direct access to warehouse data.

Tools/systems mentioned for semantic layer:
- Fabric
- Azure Analysis Server
- “metric view” / similar semantic modeling tooling (wording unclear, intent is clear)

Key concept: Power BI is not supposed to consume raw warehouse views directly (often due to security and governance).

Semantic layer responsibilities

Create/import the final warehouse outputs into the semantic layer (described via import).
Perform data modeling:
- establish relationships (one-to-many)
- resolve many-to-many relationships (bridge tables, etc.)
Create KPIs using DAX (the intent is DAX):
- KPIs are business measures/logic (e.g., counted when it equals…)
- multiple KPI types may exist
Handle Row-Level Security (RLS) here:
- RLS is set in the semantic layer, not within Power BI

Handling large data

Final delivery into Power BI uses:
- DirectQuery / Live connection
Import mode is said to be unsuitable due to data size (“data is very huge”).

5) Power BI layer (visuals + dashboards)

Purpose: presentation only.

Once data is available via direct query/live connection:
- Power BI developers create visuals, dashboards, and publish reports.
Power BI responsibilities are described as mostly limited to:
- visualization and report publishing

CI/CD and multi-environment deployment (Azure DevOps + platform governance)

Multi-environment flow

Work is not done in only one place (not directly in “production” from day one).
Environments mentioned:
- Development → QA → Production
A platform team handles approvals and checks:
- prevents risky changes to existing production data
- ensures pipeline failures don’t occur

CI/CD pipeline described conceptually (Git-style workflow)

For both data warehouse and semantic layer repositories:

Create a local repository/branch (e.g., using VS Code)
Make changes on that branch
Push changes to DevOps
Create a pull request into Development
When PR is complete, deploy to Dev environment
Test in Dev
Create another PR to move the same changes to QA
Continue the promotion flow to Production
Platform team provides approvals and validation gates

Development workflow for Power BI (using environments)

Power BI can be developed using Dev/QA/Production connections.
Example behavior described:
- If semantic layer data is available in Development, build visuals there.
- Later, switching the environment/connection details yields updated data while table structures remain consistent.

Business validation and release process

UAT (User Acceptance Testing):
- Business checks the full report.
After production release:
- business informs users via email
- documentation is created describing:
  - what users should use
  - how they should use it

Interview / career takeaway (explicitly stated)

For interviews and real experience explanations, emphasize:
- the architecture concept (warehouse → semantic layer → Power BI)
- the full data pipeline
Tool-specific details (which exact warehouse product, etc.) are less important than demonstrating you understand the layered enterprise BI structure.

Detailed end-to-end methodology (as presented)

Understand the architecture
- Identify all layers: source → data engineering → data warehouse → semantic layer → Power BI → CI/CD deployment.
Data pipeline
- Start at ERP/CRM sources (e.g., Salesforce/SAP).
- Data Engineer:
  - build ETL/ELT pipelines (ADF mentioned)
  - land data into staging
- Data Warehouse:
  - implement business logic using stored procedures
  - transform staging/raw into analytics-ready structures
  - output views
  - model into facts and dimensions
  - optionally use materialized views as intermediate optimization (still ending with views downstream)
- Semantic layer:
  - import warehouse views
  - model relationships (including many-to-many via bridge tables)
  - define KPIs using DAX
  - apply row-level security (RLS)
- Power BI:
  - connect using DirectQuery / live connection
  - build visuals, dashboards, publish to workspace
Security and governance
- Avoid giving end users direct access to the raw warehouse layer.
- Centralize modeling, KPIs, and RLS in semantic layer.
CI/CD deployment workflow (Dev → QA → Prod)
- For warehouse and semantic repositories:
  - local branch → push to DevOps → PR → pipeline deploy to Dev → test
  - PR merge to QA → pipeline deploy → approvals
  - PR merge to Production → approvals + validation
Business validation
- Perform UAT
- After production:
  - notify users
  - produce usage documentation

Speakers / sources featured

Speaker: Shivam Srivastava (named in the subtitles)
Sources/tools mentioned:
- Microsoft Power BI
- Microsoft Fabric
- Azure Data Warehouse
- Azure Analysis Server
- Databricks
- ETL / ELT pipelines
- ADF (Azure Data Factory)
- Azure DevOps
- Git-style versioning via repositories/PRs
- DAX (for KPI definitions)

How Data Analyst Projects Work in Big Companies (What Freshers Are Never Told)

Key takeaways