1 General Data Analytics Terms, Statistics, and ML Basics

Core Concepts to Cover

Business Impact /
Interview Talking
Concept Definition Point
ETL vs ELT ETL: Extract, ETL: legacy systems,
Transform, Load data quality. ELT:
(transform before cloud data warehouses
loading). ELT: Extract, (Snowflake, BigQuery)
Load, Transform leverage massive
(transform in the data compute for
warehouse). transformation.
Normalization (1NF, Eliminate redundancy Normalize for OLTP to
2NF, 3NF) and dependency. 1NF: avoid update anomalies.
atomic columns. 2NF: Denormalize for OLAP
remove partial to improve query
dependencies. 3NF: performance.
remove transitive
dependencies.
Star vs Snowflake Star: one fact table Star: simpler queries,
Schema surrounded by faster aggregations.
denormalized Snowflake: more
dimensions. Snowflake: storage-efficient, but
normalized dimensions. requires more joins.
Fact vs Dimension Fact: quantitative data Interview tip: Always
(sales, quantity). clarify grain before
Dimension: descriptive modeling.
attributes (customer,
product). Grain = level
of detail of a fact row.
Slowly Changing Type 0: retain original. Implementation: SQL
Dimensions (SCD) Type 1: overwrite. (MERGE, INSERT),
Type 2: add new row Power Query (buffered
with versioning. Type 3: lookups), Python
add previous value (tracking changes).
column.
Data Types Categorical (nominal, Storage optimization:
ordinal), Numerical use appropriate types
(continuous, discrete). (e.g., int vs bigint,
varchar vs char).
Sampling Methods Random, stratified, Stratified sampling
cluster. preserves class
proportions – useful for
imbalanced datasets.

1
 Business Impact /
Interview Talking
Concept Definition Point
Indexes Clustered: sorts and Too many indexes slow
stores data rows. down writes. Covering
Non-clustered: separate index can eliminate key
structure with pointers. lookups.
Transactions (ACID ACID: Atomicity, Choose based on
vs BASE) Consistency, Isolation, consistency
Durability (RDBMS). requirements.
BASE: Basically
Available, Soft state,
Eventually consistent
(NoSQL).
OLTP vs OLAP OLTP: transactional, Tooling: OLTP →
normalized, high RDBMS, OLAP →
concurrency. OLAP: columnar stores, data
analytical, warehouses.
denormalized, complex
queries.
Data Warehouse vs DWH: structured, Modern architectures
Data Lake vs Data schema-on-write. Data use lakehouses (Delta
Mart Lake: raw, Lake, Iceberg) to
schema-on-read (e.g., combine flexibility and
S3, ADLS). Data Mart: structure.
subset of DWH for a
business line.
Data Modeling Entity-Relationship Interview tip: Be ready
(ER) diagrams, to whiteboard a simple
surrogate keys star schema and explain
(auto-increment) vs relationships.
natural keys (business
identifiers). Cardinality:
one-to-one, one-to-many,
many-to-many. Bridge
tables resolve
many-to-many.
Degenerate dimension:
dimension attribute
stored in fact (e.g.,
order number).

Statistics

2
Term Definition Interview Example
Mean, Median, Central tendency Median is robust to
Mode measures. outliers – use for skewed
distributions like salary.
Variance, Std Dev Spread around the Used in risk analysis
mean. (financial portfolios).
Skewness, Kurtosis Shape of distribution. Skewed data may need
transformation for
linear models.
p-value, Hypothesis Probability of observing p < 0.05 is common
Testing data if null hypothesis is significance threshold.
true. Explain Type I (false
positive) and Type II
(false negative) errors.
Confidence Interval Range containing “We are 95% confident
population parameter that the true conversion
with certain confidence. rate is between 10% and
12%.”
Central Limit Distribution of sample Enables parametric
Theorem means approaches tests on non-normal
normal as sample size data with large samples.
increases, regardless of
population distribution.
Correlation vs Correlation does not Spurious correlations
Causation imply causation. example: ice cream
sales and drowning
incidents (both caused
by summer heat).
A/B Testing Randomized experiment Sample size calculation
with control and (power analysis),
treatment. significance level,
practical significance
(minimum detectable
effect).
Bias-Variance Bias: error from wrong Overfitting (high
Tradeoff assumptions. Variance: variance) vs underfitting
error from sensitivity to (high bias).
fluctuations. Cross-validation helps
balance.

Machine Learning Basics (Analyst-Friendly)

3
Concept Definition Business Application
Supervised vs Supervised: labeled Customer segmentation
Unsupervised data (regression, (unsupervised), churn
classification). prediction (supervised).
Unsupervised: no labels
(clustering, association).
Regression vs Regression predicts Regression: sales
Classification continuous values; forecast. Classification:
classification predicts fraud detection.
discrete classes.
Linear Regression Models linear Baseline model;
relationship. interpretable
Assumptions: linearity, coefficients.
independence,
homoscedasticity,
normality of errors.
Logistic Regression Predicts probability of Customer conversion,
binary outcome. Uses credit default.
sigmoid function.
Decision Trees Tree-like model splitting Easy to explain to
on feature values. Prone stakeholders.
to overfitting.
K-Means Clustering Partitions data into k Customer segmentation,
clusters based on anomaly detection
distance. Choose k (small clusters).
using elbow method.
Regularization (L1, L1 (Lasso) adds Prevents overfitting;
L2) absolute penalty, can handle multicollinearity.
shrink coefficients to
zero (feature selection).
L2 (Ridge) adds
squared penalty, keeps
all features but reduces
magnitude.
Gradient Descent Optimization algorithm Learning rate controls
to minimize loss step size; too high may
function by updating diverge, too low slow
parameters in direction convergence.
of negative gradient.
Clustering Metrics Silhouette score (how Evaluate cluster quality.
similar points are to
own cluster vs other
clusters), elbow method
(inertia).

4
Concept Definition Business Application
Model Evaluation Regression: MAE, MSE, Choose metric based on
Metrics RMSE, R². business goal (e.g.,
Classification: accuracy, recall for fraud to catch
precision, recall, F1, as many as possible).
ROC-AUC, confusion
matrix.
Multicollinearity & High correlation Inflates standard errors,
VIF between predictors. VIF unstable coefficients.
> 5–10 indicates Detect via correlation
problematic matrix or VIF.
multicollinearity.

2 Cross-Tool Process Comparisons by Lifecycle Phase

Data Loading / Connecting

Sample
Syntax / Performance
Tool Method Steps Best For Notes Pitfalls
Excel Import Data > Small Manual Data type
Wizard, Get Data datasets, refresh; inference
Power > From ad-hoc limited errors;
Query, File > rows truncation
Text-to- From (~1M)
Columns Text/CSV
SQL LOAD DATA COPY Large Use bulk Transaction
INFILE, table volume, operations; log growth;
BULK FROM scheduled disable deadlocks
INSERT, '[Link]' loads indexes
INSERT DELIMITER during
INTO ... ',' CSV load
SELECT HEADER;
(Post-
greSQL)
Python pd.read_csv(),
df = Medium Specify Memory
(Pandas) pd.read_sql(), data, pro-
pd.read_csv('[Link]',dtypes to blow-up
requests totyping,
dtype={'col': reduce with large
+ str}) API inte- memory; files
json_normalize gration chunk
large files

5
 Sample
Syntax / Performance
Tool Method Steps Best For Notes Pitfalls
NumPy [Link](),
data = Numeric- Memory- Limited to
[Link](), heavy mapped
[Link]('[Link]', numeric
large
[Link]() delimiter=',', arrays for data
(binary) arrays
skip_header=1) out-of-core
Power BI Import (in- Connect > Interactive Query DirectQuery
memory), Select dash- folding can be
Direct- data > boards pushes slow if
Query, Load or transfor- source not
Live Con- Transform mations to optimized
nection, source; use
Incremen- Incremen-
tal Refresh tal Refresh
for large
fact tables
Tableau Extract Connect Visual ex- Extracts Extract
(.hyper) vs to server ploration are refresh
Live Con- or file, columnar, scheduling
nection choose com- needed
Extract or pressed;
Live Live for
real-time
but may
be slower

Interview Question: How would you load a 50GB CSV into a database for
analysis?
Answer: Use bulk load tools (e.g., COPY in PostgreSQL, BULK INSERT in
SQL Server). If memory constrained in Python, read in chunks with Pan-
das (chunksize) and process incrementally. Consider splitting file or using
distributed processing (Spark).

Data Cleaning

Power
Query Tableau
Task Excel SQL Pandas (M) Prep
Handle IFERROR(A1,0),
COALESCE(col,0),
[Link](0)[Link],
IFNULL([Field],0)
NULLs ISBLANK() ISNULL [Link]

6
 Power
Query Tableau
Task Excel SQL Pandas (M) Prep
Remove Data > DISTINCT df.drop_duplicates() Remove
[Link]
Dupli- Remove or Duplicate
cates Duplicates ROW_NUMBER() Rows step
OVER
(PARTITION
BY ...)
Change Format CAST(col df['col'].astype(int) Change
[Link]
Data Cells, Text AS INT) data type
Type to in profile
Columns pane
Outlier Quartile Use CTE Q1 = Custom Calculated
Detec- formulas, with column field with
df['col'].quantile(0.25);
tion condi- NTILE or filter by with LOD or
(IQR) tional percentile IQR percentile table calc
formatting functions logic
String LEFT, [Link], Calculated
SUBSTRING, df['col'].[Link](),
Manipu- RIGHT, [Link],fields
CHARINDEX, .[Link](r'pattern')
lation MID, TRIM, TRIM, [Link] using
SUBSTITUTE REPLACE string
functions
Missing Fill with UPDATE [Link]([Link]()), ZN()
[Link]
Data mean (forward
SET col = [Link]() (replace
Strategy using (SELECT fill) null with
formula AVG(col) zero) in
...) Tableau
WHERE col
IS NULL

Interview Question: You receive a dataset with 20% missing values in a key
numeric column. How do you handle it?
Answer: First, investigate missingness pattern (MCAR, MAR, MNAR).
Options: drop rows if missing is random and dataset large; impute with
mean/median (simple but biases variance); use regression imputation or
model-based methods. Document assumptions and trade-offs.

Filtering Data

7
Tool Syntax / Method Notes
SQL WHERE (row filter), WHERE before GROUP BY,
HAVING (post-aggregate HAVING after. Use
filter) indexes on filtered
columns.
Pandas df[df['col'] > 5], query() is often more
[Link]('col > 5'), readable; use boolean
.loc[], .iloc[] indexing.
Power BI Visual-level, page-level, Cross-filter direction
report-level filters; matters; avoid
slicers; filter pane high-cardinality filters
on large data.
Tableau Dimension filters (order Use context filters for
of operations: extract, performance; data
data source, context, source filters for RLS.
dimension, measure)

Data Transformation / Reshaping

Power
Task Excel SQL Pandas Query Tableau
Conditional =IF(A1>10,"High","Low") Conditional
CASE WHEN [Link](df['col']>10, Calculated
Column col > 10 'High', column in field with
THEN 'Low') Add IF [col]
'High' Column > 10 THEN
ELSE 'High'
'Low' END ELSE
'Low' END
Pivot Power UNPIVOT [Link](df, Unpivot Pivot in
(Wide to Query: (SQL Columns
id_vars=['id'], Data
Long) Unpivot Server) or var_name='year', Interpreter
use CROSS value_name='sales') or use
JOIN + Tableau
UNION Prep
Unpivot PivotTable PIVOT Pivot
[Link](index='id', Not
(Long to (SQL Column
columns='year', directly;
Wide) Server) values='sales') use
with custom
aggregate SQL or
Tableau
Prep

8
 Power
Task Excel SQL Pandas Query Tableau
Aggregate PivotTable, SELECT Group By Drag di-
[Link]('category')['sales'].sum()
/ Group SUMIFS category, mensions
By SUM(sales) and
FROM measures;
table use table
GROUP BY calcs for
category subtotals
Join / VLOOKUP, JOIN Merge
[Link](df1, Relationships
Merge XLOOKUP, (INNER, df2, Queries (logical) or
INDEX-MATCH LEFT, on='key', blends (ag-
etc.) how='left') gregated)

Advanced Analytics / Window Functions

SQL
(OVER Power BI
Function Clause) Pandas (DAX) Tableau
Row ROW_NUMBER() df['rn'] = INDEX() or
RANKX(ALL(Table),
Number OVER Table[salary],RANK() table
[Link]('dept')['salary'].rank(method='first',
(PARTITION ascending=False)
, DESC, calculation
BY dept Dense) for
ORDER BY dense rank
salary
DESC)
Lag / Lead LAG(sales,1) CALCULATE(SUM(sales),
df['prev_sales'] LOOKUP(SUM([sales]),
OVER = PREVIOUSMONTH(date))
-1)
(PARTITION [Link]('product')['sales'].shift(1)
BY product
ORDER BY
date)
Running SUM(sales) [Link]('product')['sales'].cumsum()
TOTALYTD(SUM(sales),
RUNNING_SUM(SUM([sales]))
Total OVER 'Date'[Date])
(PARTITION
BY product
ORDER BY
date ROWS
UNBOUNDED
PRECEDING)

9
 SQL
(OVER Power BI
Function Clause) Pandas (DAX) Tableau
Moving AVG(sales) df['sales'].rolling(3).mean()
CALCULATE(AVERAGE(sales),
WINDOW_AVG(SUM([sales]),
Average OVER (ORDER DATESINPERIOD('Date'[Date],
-2, 0)
BY date LASTDATE('Date'[Date]),
ROWS -3, MONTH))
BETWEEN 2
PRECEDING
AND CURRENT
ROW)

Interview Question: Explain the difference between ROW_NUMBER(),

RANK(), and DENSE_RANK() in SQL.
Answer: All assign a rank based on order. ROW_NUMBER gives unique
sequential numbers even for ties. RANK gives same rank to ties but skips next
rank(s). DENSE_RANK gives same rank to ties but does not skip numbers.

Aggregation & Advanced Calculations

• Cohort Analysis: Group users by acquisition month, then track reten-
tion over time.
– SQL: SELECT DATE_TRUNC('month', signup_date) as cohort,
... GROUP BY cohort, period
– Pandas: pivot table with [Link]()
– Power BI: use CALCULATE with filters and DISTINCTCOUNT
– Tableau: create cohort field, then use table calculations.
• RFM Analysis: Recency, Frequency, Monetary scoring.
– SQL: Use NTILE(4) OVER (ORDER BY last_purchase_date DESC)
for recency, similar for others.
– Pandas: [Link]() to assign quartiles.
– Power BI/Tableau: Use calculated fields and grouping.
• KPI Types: Leading (predictive, e.g., website traffic) vs Lagging (out-
come, e.g., revenue). How to compute: often time-series comparisons.

3 Tricky Technical Deep-Dives and Optimization

Python Specifics
• is vs ==: is checks object identity (same memory location), == checks
value equality.
– Example: a = [1,2,3]; b = [1,2,3]; a == b → True, a is b →
False.

10
 • Loops vs Vectorization: Vectorized operations (using NumPy/Pandas)
are C-optimized and much faster. Avoid explicit loops over DataFrame
rows; use .apply() only if vectorization not possible.
• Data Structures:
– List: ordered, mutable, allows duplicates; O(1) append/pop from
end, O(n) lookup.

– Tuple: immutable, faster iteration.

– Dict: key-value, O(1) average lookup.

– Set: unordered, unique, O(1) membership.

• Mutable Default Arguments:
def func(lst=[]):
[Link](1)
return lst
Default list is created once and reused across calls → unexpected behavior.
Fix: lst=None and initialize inside.
• Memory Optimization:
– Use astype('category') for low-cardinality strings.

– Read large CSV in chunks: pd.read_csv('[Link]', chunksize=10000).

– Use [Link] for arrays larger than RAM.

SQL Specifics
• Join Types:
– INNER JOIN: only matching rows.

– LEFT JOIN: all from left, nulls from right if no match.

– RIGHT JOIN: opposite of left.

– FULL OUTER JOIN: all from both.

– CROSS JOIN: Cartesian product.

• CHAR vs VARCHAR: CHAR pads fixed length, VARCHAR variable.
CHAR can be faster for fixed-length data but wastes space.
• DELETE vs TRUNCATE: DELETE is DML, can be rolled back, trig-
gers fired, slower. TRUNCATE is DDL, minimal logging, faster, cannot

11
 rollback (in most RDBMS).
• Indexes:
– Clustered index determines physical order; only one per table.

– Non-clustered: separate structure; can have many.

– Covering index includes all columns needed for query, avoiding key
lookup.
• CTEs vs Subqueries: CTEs improve readability, can be recursive. Some
databases materialize CTEs, others inline them; performance may vary. In
PostgreSQL, CTEs are optimization fences (materialized by default).
• Transactions and Isolation Levels:
– Read Committed: default in many; sees only committed data.

– Repeatable Read: prevents non-repeatable reads.

– Serializable: highest isolation, prevents phantom reads.

Performance Optimization
• SQL:
– Use EXPLAIN to analyze query plan.

– Avoid SELECT *; fetch only needed columns.

– Use indexes on columns in WHERE, JOIN, ORDER BY.

– Partition large tables by date.

– Rewrite subqueries as joins when possible.

• Pandas:
– Use vectorized operations.

– Filter early to reduce data size.

– Use inplace=False (returns new DataFrame) vs inplace=True

(modifies existing).

– Use .at and .iat for scalar access, not .loc for single values.

– Consider [Link]() for complex expressions.

• Power BI / Tableau:

12
 – Reduce cardinality of columns used in relationships.

– Use extracts (Power BI Import, Tableau Extract) instead of live

connections for large data.

– Limit number of visuals on a page.

– Optimize DAX: avoid iterators over large tables; use CALCULATE

with proper filter context.

– In Tableau, use context filters to improve performance; avoid table

calculations that scan entire table.

4 Advanced Topics: Architecture, Governance, Security,

Deployment, and Tool-Specifics
Data Architecture Concepts

Concept OLTP OLAP

Purpose Process transactions Analyze data
Schema Highly normalized Denormalized (star,
snowflake)
Queries Simple, short, frequent Complex aggregations,
less frequent
Example Order entry system Sales data warehouse
Tools PostgreSQL, MySQL, Snowflake, Redshift,
SQL Server BigQuery

• Data Warehouse vs Data Lake vs Data Mart

| Feature | Data Warehouse | Data Lake | Data Mart | |—|—|—
|—| | Data | Structured, processed | Raw, any format | Subset of ware-
house, department-focused | | Schema | Schema-on-write | Schema-on-read
| Schema-on-write | | Users | Business analysts | Data scientists, engineers
| Business users | | Cost | Expensive storage | Cheap storage | Moderate |
• Data Modeling:
– Surrogate key: artificial, system-generated (e.g., auto-increment).
Natural key: business identifier (e.g., product code).

– Cardinality: one-to-many (dimension → fact). Many-to-many

resolved via bridge table.

13
 – Degenerate dimension: dimension attribute stored in fact table (e.g.,
order number) because no separate dimension.

Data Governance and Security

• Data Governance: policies, roles, data catalog (e.g., Collibra, Alation),
data lineage, stewardship.

• GDPR: Right to be forgotten, data minimization, consent. Implement

deletion scripts and anonymization.

• Data Security:
– Encryption at rest (TDE) and in transit (TLS/SSL).

– Data masking (dynamic data masking in SQL Server, or views).

– Row-Level Security (RLS) in Power BI/Tableau: filter data based

on user role.

– SQL: GRANT SELECT ON table TO user; REVOKE.

Deployment and Model Ops

• Model Saving: pickle, joblib for Python models. Save to blob storage
or model registry (MLflow).

• API Deployment: Flask/FastAPI app containerized with Docker,

deployed on Kubernetes or cloud functions.

• CI/CD: Automate testing and deployment with GitHub Actions, Jenkins.

• Monitoring: Track data drift (Kolmogorov-Smirnov test), model perfor-

mance decay, set up alerts.

DAX (Power BI) Advanced

• Row Context vs Filter Context: Row context iterates over rows (e.g.,
calculated column). Filter context is the set of filters applied (e.g., from
slicers). CALCULATE transforms filter context.
• CALCULATE(): Modifies filter context. Example: CALCULATE(SUM(Sales[Amount]),
Sales[Region] = "West")
• ALL(), ALLEXCEPT(): Remove filters. ALL(Table) removes all fil-
ters from table; ALLEXCEPT(Table, Column) keeps filters on specified col-
umn.
• Time Intelligence:
– TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

14
 – SAMEPERIODLASTYEAR('Date'[Date])

– DATESINPERIOD('Date'[Date], LASTDATE('Date'[Date]), -3,

MONTH)
• Performance Tips: Use variables to store intermediate results; avoid us-
ing FILTER over entire table when possible; prefer CALCULATE with simple
predicates.

Tableau Advanced (LOD)

• LOD Expressions:
– FIXED: Compute independently of view dimensions. Example:
{FIXED [CustomerID] : SUM([Sales])} gives total per customer
regardless of view.

– INCLUDE: Compute at a finer level than view, then aggregate up.

– EXCLUDE: Remove dimension from calculation.

• Table Calculations vs LOD: Table calcs are computed after aggregation

based on what’s in the view; LODs are computed before view aggregation
and are more flexible. Use LOD when you need a dimension not in the
view.
• Performance Tips: Use extracts, reduce marks, use context filters to
limit data before table calcs, avoid blending when possible (use relation-
ships).

5 Storytelling, Dashboard Design, Cognitive Bias, and Be-

havioral Questions
Choosing the Right Chart

Goal Chart Type

Compare categories Bar chart (horizontal if many categories)
Show trend over time Line chart
Show distribution Histogram, box plot
Show relationship between two variables Scatter plot
Show part-to-whole Stacked bar, pie (only for few categories)
Show geographic data Map
Show correlation matrix Heatmap

• Pre-attentive attributes: color, size, position, shape. Use them to

direct attention (e.g., highlight outliers in red).

15
 • Cognitive biases:
– Confirmation bias: stakeholders seek data that confirms their beliefs.
Mitigate by showing both sides.

– Anchoring: first value seen influences judgment. Present baseline

neutrally.

– How dashboards mislead: truncated axes, inappropriate aggregation,

cherry-picking time periods.

Storytelling Framework
• Context: What is the business problem? (e.g., declining sales in region)

• Challenge: What obstacles exist? (e.g., incomplete data, multiple

factors)

• Action: What analysis did you perform? (e.g., segmented customers,

compared cohorts)

• Result: What did you find and what is the recommended action? (e.g.,
target high-value customers with retention campaign)

Behavioral / Scenario-Based Questions (STAR)

Situation, Task, Action, Result
Sample 1: Dealing with Dirty Data
- Situation: During a critical executive dashboard release, I discovered that the
source system had duplicated transactions.
- Task: Ensure the dashboard showed accurate numbers before the morning
presentation.
- Action: Immediately communicated to stakeholders about the delay, wrote
a SQL script to deduplicate based on transaction ID and timestamp, validated
against source counts, and updated the dashboard.
- Result: The presentation used accurate data, and we implemented a daily
data quality check to prevent recurrence.
Sample 2: Stakeholder Changing Requirements
- Situation: Midway through a sales dashboard project, the VP of Sales re-
quested a completely different metric.
- Task: Adapt without derailing timeline.
- Action: Scheduled a quick meeting to understand the new requirement, re-
alized it could be added as a separate page without breaking existing work.
Prioritized the change, updated wireframes, and communicated revised delivery
date.
- Result: VP was satisfied, and the dashboard was delivered two days later

16
with both old and new views.
Sample 3: Performance Issue
- Situation: A Power BI report was timing out due to large data volume.
- Task: Improve performance to load under 5 seconds.
- Action: Analyzed the data model, identified high-cardinality columns in rela-
tionships, switched to DirectQuery for some tables but then changed to Import
with incremental refresh, optimized DAX measures by removing unnecessary
iterators.
- Result: Report loaded in 3 seconds, user satisfaction improved.

6 Interview Prep Aids: Question Bank, Cheat Sheets,

STAR Templates
Technical SQL Questions (with expected answers)
1. What is the difference between UNION and UNION ALL?
• UNION removes duplicates, UNION ALL keeps all rows. UNION is slower
due to sorting.
2. Write a query to find the top 3 highest-paid employees per de-
partment.
WITH ranked AS (
SELECT *, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk
FROM employees
)
SELECT * FROM ranked WHERE rnk <= 3;
3. How do you handle slowly changing dimension type 2 in SQL?
• Use MERGE to insert new rows and update effective end dates.
4. Explain the execution order of SQL clauses.
• FROM → WHERE → GROUP BY → HAVING → SELECT →
ORDER BY → LIMIT.

Python/Pandas Questions
1. How do you apply a function to each row in a DataFrame?
• [Link](lambda row: func(row['col1'], row['col2']),
axis=1). But prefer vectorized operations if possible.
2. What is the difference between .loc and .iloc?
• .loc uses label-based indexing, .iloc uses integer position.
3. How to handle missing values in a DataFrame?
• [Link](), [Link](), [Link](). Choose based on
context.

17
DAX/Tableau Questions
1. Explain the difference between CALCULATE and CALCULATETABLE.
• CALCULATE returns a scalar, CALCULATETABLE returns a table.
2. When would you use a FIXED LOD instead of a table calculation
in Tableau?
• When you need a dimension not in the view, or when you want the
calculation to be independent of view filters.

Architecture and Governance Questions

1. How would you design a data warehouse for a retail company?
• Star schema: fact tables for sales, inventory; dimension tables for
product, store, time, customer.
2. What is data lineage and why is it important?
• Tracking data from source to destination. Important for debugging,
impact analysis, and compliance.

Behavioral Questions (STAR templates)

• Ownership: Tell me about a time you took ownership of a project.
• Conflict: Describe a time you disagreed with a stakeholder and how you
resolved it.
• Failure: Share a project that didn’t go as planned and what you learned.

Quick Reference Cheat Sheet (One-Page Summary)

SQL Snippets
• Deduplicate: WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION
BY id ORDER BY date DESC) rn FROM t) DELETE WHERE rn > 1;
• Running total: SUM(sales) OVER (PARTITION BY category ORDER BY
date)
• Pivot: SELECT ... FROM (SELECT category, year, sales FROM t)
PIVOT (SUM(sales) FOR year IN ([2020],[2021])) AS pvt

Pandas Snippets
• Read CSV: pd.read_csv('[Link]', parse_dates=['date'],
dtype={'col': str})
• Groupby + agg: [Link]('category').agg({'sales':'sum','profit':'mean'})
• Merge: [Link](df1, df2, on='key', how='left')

DAX Patterns
• YTD: TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])
• Previous month: CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH('Date'[Date]))

18
 • Dynamic ranking: RANKX(ALL(Product[Name]), [Total Sales], ,
DESC, Dense)

Tableau LOD Quick Rules

• FIXED: Independent of view dimensions. {FIXED [Region] :
SUM([Sales])}
• INCLUDE: Adds dimensions to view level. {INCLUDE [CustomerID] :
AVG([Sales])}
• EXCLUDE: Removes dimension. {EXCLUDE [Category] : SUM([Sales])}

Power BI Optimization Checklist

• Use star schema.
• Reduce cardinality of columns in relationships.
• Avoid bi-directional cross-filtering unless necessary.
• Use measures instead of calculated columns when possible.
• Enable query reduction options (e.g., reduce queries).

7 Appendix: Examples, Templates, and One-Page Check-

list
Sample ER Diagram (Retail)
+-------------+ +--------------+
| Customer | | Sales |
+-------------+ +--------------+
| CustomerID (PK) |<--| CustomerID (FK) |
| Name | | ProductID (FK) |
| Region | | DateKey (FK) |
+-------------+ | Quantity |
| Amount |
+-------------+ +--------------+
| Product | | Date |
+-------------+ +--------------+
| ProductID (PK) |-->| DateKey (PK) |
| ProductName| | Year |
| Category | | Month |
+-------------+ +--------------+

SQL Snippets for Common Tasks

• Incremental Load (MERGE)
MERGE target_table AS T
USING source_table AS S

19
 ON [Link] = [Link]
WHEN MATCHED AND T.last_update < S.last_update THEN
UPDATE SET [Link] = [Link], T.last_update = S.last_update
WHEN NOT MATCHED THEN
INSERT (id, col, last_update) VALUES ([Link], [Link], S.last_update);
• SCD Type 2 Implementation
UPDATE dim_customer
SET end_date = CURRENT_DATE, is_current = 0
WHERE customer_id = @customer_id AND is_current = 1;

INSERT INTO dim_customer (customer_id, name, address, start_date, end_date, is_current)

VALUES (@customer_id, @name, @address, CURRENT_DATE, NULL, 1);
• Cohort Analysis
SELECT
DATE_TRUNC('month', first_purchase) as cohort_month,
months_since,
COUNT(DISTINCT user_id) as users
FROM (
SELECT
user_id,
MIN(purchase_date) as first_purchase,
EXTRACT(MONTH FROM age(purchase_date, MIN(purchase_date) OVER (PARTITION BY use
FROM purchases
GROUP BY user_id, purchase_date
) t
GROUP BY 1, 2
ORDER BY 1, 2;
• RFM Analysis
WITH rfm AS (
SELECT
customer_id,
NTILE(4) OVER (ORDER BY MAX(purchase_date) DESC) as r_score,
NTILE(4) OVER (ORDER BY COUNT(*) DESC) as f_score,
NTILE(4) OVER (ORDER BY SUM(amount) DESC) as m_score
FROM orders
GROUP BY customer_id
)
SELECT *, r_score*100 + f_score*10 + m_score as rfm_cell
FROM rfm;

20
Pandas Notebook Snippets
# Cleaning
df = df.drop_duplicates()
df['date'] = pd.to_datetime(df['date'])
df['category'] = df['category'].astype('category')

# Feature engineering
df['revenue'] = df['quantity'] * df['price']
df['month'] = df['date'].[Link]

# Window functions
df['running_total'] = [Link]('product')['sales'].cumsum()
df['sales_rank'] = [Link]('category')['sales'].rank(method='dense', ascending=False)

# Cohort
cohort_data = [Link](['cohort_month', 'period']).size().unstack(0)

DAX Snippets
// Sales YTD
Sales YTD = TOTALYTD(SUM(Sales[Amount]), 'Date'[Date])

// Previous Year Sales

Sales PY = CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR('Date'[Date]))

// 3-Month Rolling Average

Rolling 3M Avg =
VAR LastDate = LASTDATE('Date'[Date])
RETURN CALCULATE(AVERAGE(Sales[Amount]), DATESINPERIOD('Date'[Date], LastDate, -3, MONTH))

// Dynamic Top N Products

Top 5 Products =
VAR TopProducts = TOPN(5, ALL(Product[Name]), [Total Sales])
RETURN CALCULATE([Total Sales], TopProducts)

Tableau LOD Examples

• Customer Lifetime Value: {FIXED [Customer ID] : SUM([Sales])}
• % of Total Sales: SUM([Sales]) / {FIXED : SUM([Sales])}
• Sales vs Category Average: SUM([Sales]) - {FIXED [Category] :
AVG([Sales])}

Power BI Model Checklist

□ Star schema: fact tables in center, dimensions around.
□ Relationships: one-to-many from dimension to fact.

21
 □ Columns: hide foreign keys, sort by appropriate columns.
□ Measures: use variables for complex logic.
□ RLS: defined and tested.
□ Incremental refresh: set for large fact tables.

One-Page Interview Checklist (Printable)

Before Interview: - Review key concepts (star schema, window functions, nor-
malization). - Prepare 3 STAR stories (ownership, conflict, failure). - Practice
whiteboarding a simple data model. - Research company’s tech stack.
During Interview: - Clarify questions before answering. - Use examples from
past experience. - Show business impact of your decisions. - Ask thoughtful
questions about data architecture, team structure, challenges.
Common Questions to Ask Interviewer: - How does the team handle data
governance? - What’s the current data stack and any plans to evolve? - How do
you measure success of analytics projects? - What’s the biggest data challenge
the company faces?

This master document is designed to be your comprehensive guide. Use it to

drill down on weak areas, practice with the question bank, and internalize the
cross-tool comparisons. Good luck with your interviews!

22