Complete BI, Data Warehousing (DW) and SQL
Interview Guide
PART 1: SQL CONCEPTS
1. What is SQL? SQL (Structured Query Language) is used to store, retrieve, manipulate, and
analyze data in relational databases. It allows users to interact with databases using commands for
querying, updating, and managing data.
2. SQL Command Categories DDL (Data Definition Language): CREATE, ALTER, DROP,
TRUNCATE DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE DCL
(Data Control Language): GRANT, REVOKE TCL (Transaction Control Language): COMMIT,
ROLLBACK, SAVEPOINT
3. SQL Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
These rules ensure data accuracy and integrity in tables.
4. WHERE Clause Operators Comparison (=, >, <, >=, <=, !=), Logical (AND, OR, NOT), Pattern
(LIKE), Range (BETWEEN), List (IN), NULL checks (IS NULL, IS NOT NULL).
5. Aggregate Functions COUNT(), SUM(), AVG(), MIN(), MAX(). Used to perform calculations on
groups of rows.
6. GROUP BY and HAVING GROUP BY groups rows based on column values. HAVING filters
grouped results.
7. JOINs INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN. Used to combine rows from two or
more tables based on a related column.
8. Subqueries A query inside another query. Used when the result of one query depends on
another.
9. Views Virtual tables created using SELECT queries. Used for security, simplicity, and reusability.
10. Indexes Indexes improve data retrieval speed by allowing faster searching of records.
11. Window Functions Functions like RANK(), ROW_NUMBER(), DENSE_RANK(), SUM() OVER().
Used for advanced analytical queries without collapsing rows.
12. Normalization Organizing data to reduce redundancy and dependency. Includes 1NF, 2NF,
3NF.
PART 2: DATA WAREHOUSING (DW)
1. What is a Data Warehouse? A centralized repository that stores historical, integrated,
subject-oriented, and non-volatile data for reporting and analysis.
2. OLTP vs OLAP OLTP supports daily transactions with current data and simple queries. OLAP
supports analysis with historical data and complex queries.
3. ETL Process Extract data from source systems, Transform it by cleaning and applying business
rules, and Load it into the data warehouse.
4. Fact and Dimension Tables Fact tables store measurable business data (sales, revenue).
Dimension tables store descriptive attributes (customer, product, time, location).
5. Schema Types Star Schema: One fact table connected to multiple dimension tables. Snowflake
Schema: Dimension tables are normalized. Galaxy Schema: Multiple fact tables share dimensions.
6. Slowly Changing Dimensions (SCD) Type 0: No change allowed. Type 1: Overwrite old data.
Type 2: Add new record to preserve history. Type 3: Add new column to track previous value.
7. Granularity Level of detail in the fact table (e.g., daily, monthly, yearly). Higher granularity means
more detailed data.
8. Measures Types Additive: Can be summed across all dimensions. Semi-additive: Cannot be
summed across time. Non-additive: Cannot be summed at all (e.g., ratios).
9. Load Types Full Load loads all data every time. Incremental Load loads only new or changed
data.
10. Data Quality Checks Includes null checks, duplicate checks, referential integrity checks, and
reconciliation with source systems.
11. Metadata Data about data: Technical metadata (table structures), Business metadata (meaning
of data), Operational metadata (load history).
PART 3: BUSINESS INTELLIGENCE (BI)
1. What is BI? Business Intelligence is the process of collecting, analyzing, and presenting data to
support business decision-making.
2. BI Architecture Data Sources → ETL → Data Warehouse → BI Tools → End Users.
3. KPIs and Metrics Metrics are measurable values. KPIs are key metrics aligned with business
goals such as revenue growth or customer retention.
4. Reports vs Dashboards Reports are static and detailed. Dashboards are interactive and provide
summary views in real-time.
5. Types of BI Descriptive: What happened? Diagnostic: Why did it happen? Predictive: What will
happen? Prescriptive: What should be done?
6. BI Tools Common tools include Power BI, Tableau, Looker, and Qlik.
PART 4: INTERVIEW PREPARATION TIPS
1. Be strong in SQL basics, joins, and aggregations. 2. Understand ETL, data modeling, and
schema design. 3. Practice explaining concepts in simple terms. 4. Be ready to solve data problems
logically. 5. Show eagerness to learn and adapt.