Exploratory Data Analysis and Building ML Datasets from Data Warehouses

Comprehensive review flashcards covering Exploratory Data Analysis (EDA) principles, data quality, distributions, anomaly detection, dimensional modeling, and SQL pipeline construction for machine learning datasets.

What are the two primary components of Exploratory Data Analysis (EDA)?

1. Data cleaning: Resolving missing values, incorrectly formatted variables, and data errors/outliers.
2. Data understanding: Using summary statistics, low-dimensional projections (e.g., PCA), clustering, and visualizations to gain intuition and guide method selection.

What are common real-world examples of data quality issues encountered even in official or enterprise datasets?

In the French death registry, issues included missing birthdates, varying date formats by year, and obsolete departments/countries. In JCDecaux bicycle trip data, dates were stored as strings (failing to handle daylight saving time), system bugs caused missing data, and station IDs remained unchanged when physical stations moved.

How do the three missing data mechanisms—MCAR, MAR, and MNAR—differ in risk and detection?

• Missing Completely at Random (MCAR): Values are missing independently of any variable; deleting rows carries little risk.
• Missing at Random (MAR): Missingness depends on another observed variable; deletion causes bias, but imputation based on related variables works well.
• Missing Not at Random (MNAR): Missingness depends on the unobserved value itself; standard imputation fails, and detecting MNAR cannot be done from data alone (requires domain expertise).

Why is treating categoricals encoded as numbers (e.g., $0$, $1$, $2$) as numerical variables considered a critical error?

Treating encoded categorical levels as numerical introduces an artificial mathematical ordering and distance metric (implying $2 > 1 > 0$ and $2 - 1 = 1 - 0$) that does not exist in the true categorical attribute.

How do One-Hot Encoding and Dummy Encoding differ when transforming categorical features?

For a categorical variable with $k$ categories, One-Hot Encoding creates $k$ binary indicator columns (one for every category). Dummy Encoding drops one baseline category and creates $k - 1$ columns to prevent multicollinearity (dummy variable trap).

How is Median Absolute Deviation (MAD) defined mathematically, and how does it contrast with standard deviation?

$\text{MAD}(X) = \text{median}(|x_i - \text{median}(X)|)$. Unlike standard deviation, which measures squared deviations from the mean and is heavily distorted by extreme outliers, MAD is a robust measure of statistical dispersion centered around the median.

What characterizes a Log-Normal distribution compared to a Normal distribution?

A Log-Normal distribution applies to positive values whose natural logarithm follows a normal distribution. It is bounded by zero on the left, right-skewed, and typically arises from multiplicative rather than additive processes.

What is a Power Law distribution, and why does the $3\sigma$ outlier rule fail on it?

A Power Law distribution is scale-free (lacking a characteristic scale or mean), where the probability of occurrence decreases as a power of size ($P(x) \propto x^{-\alpha}$). The $3\sigma$ rule fails because power laws can exhibit infinite variance and heavy tails where extreme values occur naturally through self-reinforcing dynamics.

What is the formula for the Interquartile Range (IQR) outlier filter?

$\text{IQR} = Q_3 - Q_1$. Values outside the interval $[Q_1 - 1.5 \times \text{IQR}, Q_3 + 1.5 \times \text{IQR}]$ are flagged as potential outliers.

What fundamental data analysis lesson is demonstrated by the Datasaurus Dozen?

Multiple distinct datasets can share virtually identical summary statistics (such as $X$ mean $\approx 54.26$, $Y$ mean $\approx 47.83$, $X$ standard deviation $\approx 16.76$, $Y$ standard deviation $\approx 26.93$, and correlation $\approx -0.06$) while representing completely different graphical patterns. Summary statistics must always be verified with visualizations.

What is the difference between Pearson's correlation and Spearman's rank correlation?

Pearson's correlation ($\rho_{X,Y} = \frac{\text{cov}(X, Y)}{\sigma_X \sigma_Y}$) measures the strength and direction of linear relationships between raw values. Spearman's correlation ($r_s$) calculates Pearson's correlation on the rank-transformed variables, assessing monotonic relationships regardless of whether they are linear.

How does an Isolation Forest detect anomalies?

It constructs an ensemble of isolation trees (iTrees) using random feature selection and random split values. Anomalies require fewer random splits to isolate into single leaves compared to normal points in dense clusters, resulting in shorter average path depths (higher anomaly scores).

What are the typical operating parameters of an Isolation Forest?

• Subsample size: Typically $256$ instances per tree.
• Number of trees: Typically $200$ to $300$ trees.
• Stop criteria: Instance isolated in its own leaf or reaching a maximum depth constraint.

In machine learning dataset construction from a data warehouse, what defines an observation unit for churn prediction?

One row represents a unique pair $(\text{customer\_id}, t_{\text{cutoff}})$. Features ($X$) are computed strictly from historical events dated $t < t_{\text{cutoff}}$, while the target label ($y$) is computed from future events within the forward window $[t_{\text{cutoff}}, t_{\text{cutoff}} + \Delta t]$.

What are the structural differences between OLTP and OLAP systems?

• Access pattern: OLTP uses point lookups ($1$ row by key); OLAP executes large scans across millions of rows.
• Columns accessed: OLTP retrieves most columns per row; OLAP accesses few columns.
• Target latency: OLTP operates in milliseconds; OLAP ranges from seconds to minutes.
• Physical layout: OLTP uses row-oriented storage; OLAP uses columnar storage.

Why are machine learning training datasets built against a data warehouse rather than production OLTP databases?

Production OLTP databases undergo continuous in-place updates and transaction purging, preventing experiment replication. Data warehouses remain frozen between scheduled batch loads, guaranteeing reproducible training sets, and running analytical queries on OLTP would lock tables and degrade application performance.

How does schema-level access separation function between the ETL process and data scientists in a warehouse?

The automated ETL process is the sole writer to shared schemas (sales, customer, product), where data scientists have read-only (SELECT) access. Data scientists are granted dedicated write permissions only within their personal schema workspace (e.g., ds_workspace) to save ML tables.

How does partition pruning improve query performance, and what query error disables it?

Partition pruning allows the query engine to read partition file metadata and skip files outside date boundaries (e.g., reading $31$ daily partitions instead of $1100$). Applying functions to the partition key (e.g., WHERE EXTRACT(YEAR FROM sale_date) = 2024) hides boundaries from the optimizer and forces a full table scan.

How do 3NF, One Big Table (OBT), and Star Schemas compare in join count and trade-offs?

• Normalized (3NF): Eliminates redundancy across entity hierarchies but requires deep join chains ($\approx 5$ joins), driving up execution costs.
• One Big Table (OBT): Requires $0$ joins but suffers from extreme storage redundancy and cannot represent unsold products or inactive entities.
• Dimensional (Star Schema): Balances storage and query efficiency with controlled redundancy, joining a central numeric fact table to denormalized dimension tables ($\approx 2$ joins).

What two denormalization decisions convert a 3NF schema into a Star Schema?

1. Merging transaction headers and lines into a single fact table (eliminating $1$ join; the transaction key becomes a degenerate dimension).
2. Flattening descriptive hierarchies (e.g., shop $\subset$ town $\subset$ department $\subset$ region) into a single dimension table (eliminating $3$ joins).

What are the three column categories in a fact table?

1. Measures: Numeric values recorded for each event (e.g., quantity, line amount) intended for aggregation.
2. Foreign Keys: Relational integer keys referencing surrounding dimension tables (store_id, product_id, date_id).
3. Degenerate Dimensions: Transaction IDs (e.g., sale_id) that group lines belonging to the same transaction without referencing an external table.

Why do warehouse dimension tables use synthetic surrogate keys instead of operational business keys?

Surrogate keys (e.g., customer_key) uniquely identify a specific historical version or state of an entity. Business keys (customer_id) remain constant over time; joining fact tables directly on business keys causes fan-out that duplicates historical transaction amounts.

How can the true grain of an unfamiliar fact table be empirically verified?

By comparing the total row count COUNT(*) against candidate primary key combinations using COUNT(DISTINCT ...). If COUNT(*) exceeds COUNT(DISTINCT sale_id, product_id), rows represent a finer grain, such as distinct price or promotion lines per product in a basket.

How do warehouses handle unresolved foreign keys without dropping records or breaking aggregations?

Rather than discarding the transaction or inserting a NULL (which causes INNER JOIN queries to drop rows), the ETL pipeline remaps missing foreign keys to a dedicated default row (key $-1$, labeled 'Unknown').

Why does writing WHERE status <> 'cancelled' cause silent row exclusion under SQL Three-Valued Logic?

In SQL 3VL, comparisons with NULL evaluate to UNKNOWN. If status contains NULL (often representing normal, unflagged orders), status <> 'cancelled' evaluates to UNKNOWN and the WHERE clause silently drops those rows. The safe alternative is WHERE status IS DISTINCT FROM 'cancelled' or WHERE status <> 'cancelled' OR status IS NULL.

Why does using an INNER JOIN to link a customer dimension to a sales fact table corrupt a churn prediction cohort?

An INNER JOIN automatically drops entities with no transactions in the fact table. For churn modeling, inactive customers with zero purchases are the primary target population ($y = 1$); dropping them biases the training population to active buyers.

What is the difference between additive, semi-additive, and non-additive measures?

• Additive: Can be summed across all dimensions (e.g., revenue, quantity sold).
• Semi-additive: Can be summed across spatial/categorical dimensions but not across time (e.g., bank account balances, warehouse inventory stock).
• Non-additive: Cannot be summed across any dimension (e.g., unit price, profit margin percentage, ratios).

What causes join fan-out when joining two fact tables, and how is it prevented?

Joining two 1-to-many fact tables directly on an entity key (e.g., purchases and returns on customer_id) creates a Cartesian product of event lines per customer, multiplying sums. It is prevented by aggregating each fact table to exactly one row per entity in separate Common Table Expressions (CTEs) before performing a 1-to-1 join.

How do SCD Type 1 and SCD Type 2 handle attribute updates, and what is the risk of SCD Type 1 in machine learning?

• SCD Type 1 overwrites old values directly in place, erasing historical states. In ML, this introduces temporal lookahead leakage because past events are described using future attributes.
• SCD Type 2 preserves history by closing the existing record and inserting a new row with a valid date range $[\text{valid\_from}, \text{valid\_to})$.

How does a Point-in-Time (As-Of) join link cohort observation dates to SCD Type 2 dimension tables?

It joins the cohort table to the dimension table on the natural key using a half-open temporal validity predicate: c.customer_id = d.customer_id AND c.observation_date >= d.valid_from AND c.observation_date < d.valid_to.

Why do accumulating snapshot fact tables pose a hidden leakage risk in historical ML dataset construction?

Accumulating snapshots update multiple timestamp columns in place as a process progresses (e.g., ordered_at, shipped_at, delivered_at). When queried historically months later, timestamps that occurred after observation date $d$ are already populated, introducing lookahead leakage unless explicitly filtered out.

What are the four sequential stages of the machine learning dataset construction pipeline?

1. The Cohort: Establish eligible entity-date pairs $(\text{customer\_id}, t_{\text{cutoff}})$.
2. Features from behavior: Aggregate transactional metrics over a historical window strictly before $t_{\text{cutoff}}$.
3. Features from attributes: Extract point-in-time entity characteristics at $t_{\text{cutoff}}$ using an as-of join.
4. Target label: Determine outcome status over a forward window after $t_{\text{cutoff}}$. All stages are assembled onto the cohort via LEFT JOIN.

Why is a temporal train-test split required for time-dependent ML models, and why must a buffer gap be inserted?

A random split leaks future behavioral patterns into past training samples. A temporal split divides data by observation date $d$. A buffer gap equal to the label window length (e.g., $90$ days) must separate the training and test periods so that forward-looking labels of training instances do not overlap with the test set.

What four fields should be stored in an execution manifest to guarantee pipeline reproducibility?

1. executed_at: Timestamp of query execution.
2. n_rows: Total number of records returned.
3. sum_amount: Verification sum of a key numeric feature (e.g., annual spend).
4. query_sha: SHA-256 hash of the parameterized extraction SQL script.