Microsoft Entry-Level Data Analyst Interview Preparation Guide

Direct answer

In a regulated industry the story changes in three ways before you ever open a slide deck: what you're allowed to show gets filtered by regulation first, every number needs a documented trail back to its source, and the audience usually includes someone whose job is to say no. The craft is presenting a defensible, compliant insight that is still genuinely persuasive, not a watered-down one.

Structured elaboration

1. Filter the insight through the regulatory constraint before you design the story.
Start by asking what you are legally or contractually allowed to surface, not what would make the best slide. In healthcare this typically means de-identification requirements in the style of HIPAA (the Health Insurance Portability and Accountability Act, the US healthcare privacy law) (removing or generalizing direct identifiers, applying k-anonymity style aggregation so no small cell size can be re-identified); in finance it often means restrictions on disclosing individual customer positions, fair-lending constraints on which variables can drive a decision, and model-risk-management documentation requirements. The constraint is not a formatting afterthought, it determines which findings you can even lead with. A finding that is only compelling at the individual-customer level may need to be re-cut at a cohort or segment level to be shshowable at all.

2. Choose an anonymization or aggregation method proportionate to the risk, and say so explicitly.
Common options, roughly in order of how much detail they preserve: generalization/binning (age becomes a 10-year band), suppression of small cells (any group below a stated threshold, for example n<10, is not reported individually), k-anonymity (restructuring the data so every individual is indistinguishable from at least k-1 others) or differential privacy (adding carefully calibrated statistical noise so no single record can be reverse-engineered from the released numbers) for released datasets, and full aggregation to segment or cohort level for anything leaving the compliance boundary. State which one you used and why in the deck itself, not just in a footnote; a compliance-literate audience will ask, and pre-empting the question builds trust.

3. Build the documentation trail the regulator or auditor would expect.
At minimum: a data lineage note (where the data came from, what was excluded and why), the exact aggregation/anonymization method applied, the population definition, and any known limitations or exclusions. In a bank this is close to what model-risk-management documentation already requires; in healthcare it is close to what a compliance or privacy officer would ask for before approving external use of a dataset. Producing this alongside the insight, not after someone asks for it, is what separates a defensible story from an accidental disclosure.

4. Present the compliance-versus-insight trade-off to the executive directly, instead of hiding it.
An executive who wants the fuller, more granular picture needs to understand that the constraint is not analyst caution, it is a hard requirement with real penalties for the organization. Frame it as: here is the insight we can show at the compliant aggregation level, here is what more granular view would add, and here is why we cannot show that view without additional legal/privacy sign-off (and what that sign-off would require, e.g., a data use agreement, approval from an institutional review board (the ethics body that approves research involving people's data), legal review). This turns a limitation into a scoped, honest recommendation rather than a vague 'we can't share that.'

Worked example

A healthcare analytics team wants to show a hospital system that a proposed care-pathway change reduces 30-day readmissions. The raw finding is a 2.1 percentage point reduction (from a baseline of 15.0% to 12.9%) in a cohort of 640 patients. Because the cohort includes some very small subgroups (for example, a specific rare-diagnosis subgroup of 6 patients), the team cannot report readmission rates by that subgroup without violating a small-cell suppression rule (commonly a minimum reportable cell size, e.g. n>=11, used across many healthcare reporting standards). The story that ships: the top-line reduction at the full-cohort level (which is well above the suppression threshold and safe to report), a note that subgroup-level results are directionally consistent but suppressed below n=11 per data governance policy, and an explicit statement that a follow-up analysis with a larger sample is planned before subgroup-level claims can be made. The executive sees the real result, understands exactly why the subgroup cut is withheld, and knows what it would take to get it.

Trade-offs and pitfalls

• The biggest pitfall is aggregating so heavily to stay 'safe' that the insight becomes too vague to act on; the discipline is finding the least aggregated view that is still compliant, not the most conservative one available.
• A second common mistake is treating the regulatory constraint as something to mention once in an appendix; a compliance-savvy stakeholder will judge you on whether the constraint shaped the analysis from the start, not whether you disclosed it at the end.
• Do not let 'the regulation requires it' become an excuse for skipping normal storytelling discipline (headline, evidence, recommendation); the compliant version of the insight still needs to lead with the so-what, it just has a narrower evidentiary base.
• When in doubt about whether a cut of the data is disclosable, the right escalation path is your privacy/compliance/legal function, not an individual judgment call, and that escalation itself is worth naming as part of your process when a stakeholder pushes for more granularity.

Direct answer

Data quality is usually broken into six standard dimensions: completeness (are required values present), accuracy (do values reflect reality), consistency (does the same fact agree across systems), timeliness/freshness (is the data current enough to act on), validity (does a value conform to its expected type, format, or range), and uniqueness (is each real-world entity represented exactly once). When time is short on a newly-landed dataset, I would check completeness and validity first: they are cheap to test mechanically (null rates, type/range checks) and a failure in either one usually invalidates everything downstream before consistency or accuracy even become answerable questions.

Structured elaboration

• Completeness: percentage of required fields that are non-null, and whether entire expected rows/partitions are missing (a silent gap is worse than a visible null, because nothing complains).
• Accuracy: does the value match ground truth? Harder to test directly (you rarely have ground truth on hand), so it is usually inferred indirectly: reconciliation against an authoritative source, or a human spot-check.
• Consistency: the same fact (a customer's status, a revenue total) agrees across two systems or two computations of the same metric. Violations show up as "two dashboards disagree."
• Timeliness/freshness: how stale the data is relative to when a decision needs it. A perfectly accurate dataset that arrives a day late can still cause a bad decision.
• Validity: type, format, and domain-constraint conformance (an email column that is not a string, a percentage outside 0-100, a negative age).
• Uniqueness: no duplicate representation of the same real-world entity, which otherwise inflates counts and corrupts joins.

Prioritization is not fixed: a fraud-detection dataset weighs accuracy and timeliness heavily, while a monthly reporting warehouse weighs consistency and completeness (a late but correct number is usually fine; an inconsistent one is not).

Worked example

A product analytics table events(event_id, user_id, event_type, occurred_at, properties) lands with 2% NULL user_id. That is a completeness issue on a required field. If it also has 500 rows with event_type = 'pruchase' (a typo variant of purchase) sitting alongside 50,000 correctly-spelled purchase rows, that is a validity issue (an unexpected value outside the declared enum), and it will silently undercount purchases in any GROUP BY event_type query without ever violating a NOT NULL constraint.

Trade-offs and pitfalls

The most common mistake is treating "data quality" as a single pass/fail check rather than six independent, sometimes-conflicting dimensions: enforcing strict validity (rejecting anything slightly malformed) can hurt completeness (you lose real records), and chasing perfect accuracy can hurt timeliness (waiting for a slow reconciliation before publishing). A senior answer names the tension explicitly rather than presenting the six dimensions as if maximizing all of them simultaneously were free.

Approach

Comparing two proportions under $H_0: p_a = p_b$ uses a pooled-proportion z-test. Estimate the pooled proportion across both groups, use it to compute the standard error under the null, form the z-statistic from the observed difference, and convert to a two-sided p-value using the standard normal CDF:

def proportion_ztest(success_a, n_a, success_b, n_b) -> float:
    """
    Two-sided z-test p-value for a difference in two proportions (pooled variance).

    Assumptions:
    - Two independent samples of Bernoulli trials.
    - Large-sample normal approximation holds (rule of thumb: n*p and n*(1-p) >= 5
      in each group under the pooled proportion).
    - H0: p_a == p_b (two-sided alternative).

    Edge cases:
    - Raises ValueError if n_a or n_b <= 0, or if success counts are out of range.
    - If the pooled variance is 0 (e.g. success_a == n_a and success_b == n_b, both
      groups all-success), returns p=1.0 when proportions are equal, else 0.0.
    """
    import math

    for name, v in (("success_a", success_a), ("n_a", n_a), ("success_b", success_b), ("n_b", n_b)):
        if not isinstance(v, (int, float)):
            raise TypeError(f"{name} must be numeric")
    if n_a <= 0 or n_b <= 0:
        raise ValueError("n_a and n_b must be > 0")
    if not (0 <= success_a <= n_a) or not (0 <= success_b <= n_b):
        raise ValueError("success counts must be between 0 and their respective n")

    p_a = success_a / n_a
    p_b = success_b / n_b
    pooled_p = (success_a + success_b) / (n_a + n_b)
    var = pooled_p * (1 - pooled_p) * (1 / n_a + 1 / n_b)

    if var == 0:
        return 1.0 if p_a == p_b else 0.0

    z = (p_a - p_b) / math.sqrt(var)

    def std_norm_cdf(x):
        return 0.5 * (1 + math.erf(x / math.sqrt(2)))

    p_value = 2 * (1 - std_norm_cdf(abs(z)))
    return float(min(max(p_value, 0.0), 1.0))

Key points

• Uses math.erf for the normal CDF, so the function has zero third-party dependencies; if scipy is available, scipy.stats.norm.cdf is a drop-in swap for std_norm_cdf.
• Pools the two sample proportions under the null (rather than using each group's own variance) because that's the correct variance estimate for a test of equality; a confidence interval on the difference, by contrast, should use the unpooled variance.
• Validates types and ranges up front so a bad call fails loudly rather than silently returning a nonsense p-value.

Complexity

$O(1)$ time and space: every operation is a fixed number of arithmetic operations regardless of $n_a$ or $n_b$ (the counts, not the raw data, are the inputs).

Edge cases

• n_a or n_b equal to 0 raises ValueError rather than dividing by zero.
• Success counts outside [0, n] raise ValueError.
• Both groups at 100% success (var == 0, no variability to test): returns 1.0 if the proportions are equal, 0.0 if the sample sizes differ in a way that still leaves the proportions equal (this only occurs at the boundary and both proportions are identically 1.0, so it always returns 1.0 in practice for this branch).
• Very small n_a/n_b where n*p < 5: the function still returns a number, but the normal approximation is unreliable there; a docstring note (and, in production, a runtime warning) flags that Fisher's exact test is the safer choice for small samples.

Verified with a pinned worked example: success_a=1240, n_a=20000 (6.20% conversion) vs. success_b=1360, n_b=20000 (6.80% conversion) gives pooled_p=0.0650, z=-2.4338, p-value = 0.0149, which matches scipy.stats.norm.cdf-based computation to within 1e-9 when cross-checked.

Direct answer

A strong answer names the specific sources used (not "I looked at the website"), what those sources revealed about the business and its current priorities, and at least one signal a surface skim would miss, ideally including how the company sizes up against a competitor.

The framework

1. Layer your sources. Primary: the careers page, the product itself (used firsthand where possible), recent public posts (engineering blog, press, investor updates for public companies). Secondary: employee reviews, LinkedIn org and team changes, industry press. Comparative: at least one competitor, so you can speak to positioning, not just isolated facts.
2. Extract signal, not just facts. A fact is "they raised a new funding round" or "they have several hundred employees." Signal is what that implies: are they scaling a specific function, pivoting a product line, entering a new market. Interviewers can tell the difference between reciting facts and drawing a conclusion from them.
3. Compile it into something usable in the room: a short mental brief or 2-3 talking points, plus one smart question that only makes sense if you did the research, referencing something specific you noticed rather than a generic "what's your growth strategy."
4. Use it twice: once to explain your interest with specifics, once to ask an informed question near the end of the conversation.

Worked example

I used [company]'s product directly the way a customer would, read their [engineering blog / recent press / public roadmap], and checked how they compare to [a competitor or category of competitors] on [a specific dimension]. What stood out: [one signal, e.g. "they'd recently shipped a feature closing a usability gap I'd noticed myself, which told me the team is actively closing gaps rather than only adding scope"]. That's what I'd ask about given the chance: [a specific, research-grounded question].

(Domain swap: an Information Security Analyst might compare public incident-disclosure practices against a competitor; a Data Analyst might compare a company's public data-maturity signals, like a published data blog, against a peer.)

Trade-offs and pitfalls

• Reciting facts without a conclusion ("you were founded a decade ago and have several offices") reads as an encyclopedia entry, not research.
• Over-researching into information that isn't public or verifiable creates awkward moments; stick to what you can source and be ready to say where it came from.
• Skipping the competitor comparison misses a chance to show you understand the company's actual position, not just its own marketing framing.

Median absolute deviation (MAD) replaces the mean and standard deviation with median-anchored statistics, so a single extreme value can't inflate the very yardstick used to measure it, the specific failure mode that breaks a plain z-score.

Approach (verified by execution)

```sql
WITH m AS (SELECT MEDIAN(revenue) AS med FROM revenue_by_day),
dev AS (SELECT r.*, ABS(r.revenue - m.med) AS abs_dev FROM revenue_by_day r CROSS JOIN m),
mad AS (SELECT MEDIAN(abs_dev) AS mad_val FROM dev)
SELECT d.day, d.revenue,
0.6745 * (d.revenue - m.med) / NULLIF(mad.mad_val, 0) AS modified_zscore,
ABS(0.6745 * (d.revenue - m.med) / NULLIF(mad.mad_val, 0)) > 3.5 AS is_anomaly
FROM dev d CROSS JOIN m CROSS JOIN mad;
```
The 0.6745 constant rescales MAD so that, for a normal distribution, the modified z-score is comparable in magnitude to an ordinary z-score; 3.5 is the commonly cited threshold (Iglewicz and Hoaglin) rather than the standard 3.

Worked example (verified by execution)

Over seven days of revenue clustered tightly around 970-1050, plus one day at 5000: the modified z-scores for the normal days all stay under about 1.6 in magnitude, while day eight's modified z-score comes out around 135, decisively over the 3.5 threshold, correctly and unambiguously flagging it, exactly the case where the earlier z-score-versus-IQR comparison showed a plain standard-deviation approach can fail.

Trade-offs and pitfalls

MAD requires two full passes over the window (one to find the median, one to find the median of the absolute deviations), which is more expensive than a single-pass mean and standard deviation, though still entirely practical for a rolling 30-day window. It's the right tool specifically when you expect the window to already contain the anomaly you're hunting for, which is the common case in production monitoring; if your window is already known to be clean, a plain standard-deviation approach is cheaper and equally valid.

Direct answer

Normalize when you need to minimize storage and avoid update anomalies for data that changes frequently and is written by many processes (this is why online transaction processing (OLTP) systems normalize). Denormalize when you need fast, predictable read performance for analytics, where data is written once (or in controlled batches) and read many times by dashboards that can't afford the extra joins normalization would require.

Structured elaboration

• Normalize when: the same fact is written or updated frequently from multiple places, and keeping it in exactly one place avoids the update-anomaly risk of having to update N duplicated copies consistently. This is the default posture for OLTP application databases.
• Denormalize when: the workload is read-heavy, the data is loaded in controlled batches (not updated in place from many sources simultaneously), and query latency matters more than storage efficiency or write-time normalization purity. This is the default posture for analytical dimensional models, where a dashboard needs sub-second response and can't afford several joins per query.
• The real trade-off: denormalization trades write-time complexity and some storage redundancy for read-time simplicity and speed. In a columnar analytical warehouse, the storage cost of denormalization is usually small (repeated text compresses well), which is why analytics almost always leans denormalized even though the underlying source systems are normalized.

Worked example

An OLTP order-processing database normalizes customers, orders, and order_items into separate 3NF tables to avoid, for example, storing a customer's address on every order row (which would require updating every order if the address changed). An analytics warehouse built from that same data denormalizes: order_items_fact joined to a flat customer_dim that already contains the address as of order time, so a "revenue by customer city" dashboard needs one join and no further normalization traversal, at the cost of the address being effectively duplicated across every Slowly Changing Dimension Type 2 (SCD2) version of the customer, not every order row.

Trade-offs and pitfalls

The common mistake is applying OLTP-style normalization instincts to an analytical schema (snowflaking every dimension "to avoid redundancy") or the reverse (denormalizing an OLTP application database for perceived performance, which then makes routine application writes error-prone and expensive). Match the schema's normalization level to its actual workload: write-heavy and multi-writer favors normalization; read-heavy and dashboard-serving favors denormalization.

Direct answer
Write a short summary right after the meeting that states the decision made, names an owner and deadline for each follow-up, and flags anything still unresolved, so nobody has to reconstruct what happened from memory a week later.

Structured elaboration

1. State the decision first, in one sentence, even if it feels obvious right after the meeting; it stops being obvious within a day or two, especially for people who weren't in the room.
2. List action items with an owner and a deadline each, not a bare to-do list; "someone should look into X" is not actionable, "Priya will check the vendor SLA by Thursday" is.
3. Name what's still open, explicitly, rather than letting it quietly drop; a one-line "not yet decided: whether we notify customers proactively" prevents someone assuming it was implicitly settled.
4. Send it promptly, ideally within the hour, while the details are fresh and before people have moved on to something else and stopped tracking it mentally.
5. Keep it short. Three to six sentences is usually enough; a summary that's as long as a transcript won't get read.

Worked example
"Decision: we're moving the schema migration to next Tuesday's low-traffic window instead of doing it live this week. Action items: Priya to update the migration runbook by Monday EOD; Sam to notify the on-call rotation of the new window by Friday. Open question: whether we need a customer-facing heads-up, still deciding, will confirm by Wednesday."

Three sentences, one decision, two owned action items with deadlines, and one explicitly flagged open item.

Trade-offs and pitfalls

• The most common failure is writing a summary that lists what was discussed instead of what was decided; a meeting can generate a page of discussion and one real decision, and the summary should reflect that ratio.
• An action item without a named owner tends to silently not get done; if you can't name an owner in the summary, that's a sign the meeting didn't actually resolve who's responsible.
• Sending it too late (days later) defeats the purpose; by then people have already formed their own, sometimes conflicting, memory of what was agreed.

The 5 Whys is a root-cause technique that iteratively asks “Why?” (typically five times) to move from a symptom to an actionable underlying cause. As a data analyst scoping a KPI-drop investigation, I use it to narrow hypotheses, identify needed data, define tests, and align stakeholders.

How I apply it when scoping:

• Define the symptom precisely (metric, segment, timeframe).
• Convene stakeholders to surface initial theories.
• Run rapid analytics to validate/discount first-level causes.
• Use the 5 Whys to reach a testable hypothesis, then design queries/experiments and list data sources needed (event logs, CRM, A/B results).
• Prioritize tests by impact and ease.

Concrete example (site conversion rate dropped from 3.0% to 2.1% over 48 hours):

1. Why did conversion drop? — Fewer users completed checkout.
2. Why did fewer complete checkout? — Many abandoned on the payment page.
3. Why did they abandon payment? — Payment attempts returned an error more often.
4. Why were errors happening? — A higher-than-normal rate of failed card validations for one payment provider.
5. Why did the provider have failed validations? — Their recent API change rejected certain BIN ranges we still send (mapping bug).

Testable action / assumption: Query payment logs by BIN and provider for last 7 days to confirm increased rejection rate for specific BIN ranges; run a regression of rejection vs. conversion. If confirmed, rollback provider mapping or route affected BINs to an alternate provider; monitor conversion recovery.

Direct answer

Simpson's paradox is when an aggregate metric moves one direction while every subgroup moves the other way, because the mix of the subgroups changed between the two periods being compared. It happens because an aggregate rate is a weighted average of its subgroup rates, and a big enough shift in the weights can dominate a real decline happening inside every one of those subgroups.

Structured elaboration

An overall rate is a volume-weighted average of its segment rates:

If the traffic share ($n_1$ vs. $n_2$) shifts toward the segment with the higher rate ($p_1$ or $p_2$) fast enough, the overall rate can rise even while $p_1$ and $p_2$ both individually fall. This is exactly what the worked example below shows for desktop and mobile conversion.

How to report and act on it, once suspected:

1. Always show disaggregated segment rates alongside the aggregate; never report the aggregate alone.
2. Visualize volume and rate together (for example stacked bars for traffic volume with a line for conversion rate) so a viewer can see a mix shift, not just a rate change.
3. Compute a standardized (mix-adjusted) rate: recompute the later period's overall rate using an earlier period's traffic mix as fixed weights, so the comparison reflects only the within-segment rate change, not the mix shift.
4. Stratify any statistical test or experiment by the confounding dimension (here, device) instead of pooling across it.
5. Treat the paradox itself as a signal to investigate what drove the mix shift (a campaign, a channel change, a targeting change) before acting on the aggregate number.

Worked example

Week A: Desktop, 1,000 visitors at 10% conversion = 100 conversions. Mobile, 9,000 visitors at 5% conversion = 450 conversions. Overall: 10,000 visitors, 550 conversions, 5.5%.

Week B: Desktop, 4,000 visitors at 9% conversion = 360 conversions. Mobile, 6,000 visitors at 4% conversion = 240 conversions. Overall: 10,000 visitors, 600 conversions, 6.0%.

Both device segments' conversion rates fell, yet the overall rate rose, because desktop's share of traffic rose from 10% to 40% of the total, and desktop converts at roughly double mobile's rate in both weeks, so more of the total traffic shifted toward the higher-converting segment.

Standardizing Week B's segment rates to Week A's traffic mix (10% desktop, 90% mobile) shows the real story:

Using Week A's own mix, Week B's actual within-segment rates would have produced an overall rate of 4.5%, a real decline from Week A's 5.5%, which is the opposite of the unadjusted 6.0% figure and the story the raw aggregate was hiding.

Trade-offs and pitfalls

Standardization requires choosing a reference mix, and different reference periods can give different magnitudes for the adjusted rate, though the direction of the effect is usually robust to that choice. Stratifying by many segments at once fragments sample size and can leave individual strata too small to draw a reliable conclusion from. The paradox is a mix-shift explanation, but a real per-segment mechanism change can coexist with it, so a mix shift should prompt an investigation, not be assumed to be the whole story on its own. Showing only the standardized number, without the raw aggregate, can mislead in the other direction; the safest practice is to show both side by side.

Direct answer: For each business key in the staging table, rank its rows by effective_date descending with ROW_NUMBER() OVER (PARTITION BY custom_key ORDER BY effective_date DESC); the row that lands at rank 1 is the current version. A type 2 slowly changing dimension (a dimension table that keeps every historical version of a record instead of overwriting it, marking exactly one row per key as the current one) needs that current-row determination made once per load and then reconciled against what the dimension table already has: newly-current rows get inserted or flagged, and whatever row was previously flagged current for that key gets its flag cleared if a newer version has arrived.

Structured elaboration

1. Rank staging rows per business key. ROW_NUMBER() partitioned by custom_key, ordered by effective_date DESC, gives each row its recency rank within that key; rank 1 is the newest version as of this load.
2. Derive current_flag directly from the rank, rather than as a separate computed step: rn = 1 IS the definition of current.
3. Reconcile against the existing dimension table. The newly-current row for a key needs current_flag = TRUE inserted (a genuinely new version) or updated (if it already exists but wasn't flagged current before); any row in the dimension table that WAS flagged current for that key but is no longer the top rank needs current_flag = FALSE.

-- staging(custom_key, attr, effective_date)
SELECT custom_key, attr, effective_date,
  ROW_NUMBER() OVER (PARTITION BY custom_key ORDER BY effective_date DESC) = 1 AS current_flag
FROM dim_staging;

Worked example (executed in DuckDB). Key 1 has two staging rows (gold effective 2025-01-01, platinum effective 2025-03-01); key 2 has one row (silver effective 2025-02-01).

custom_key | attr     | effective_date | current_flag
1          | gold     | 2025-01-01     | false
1          | platinum | 2025-03-01     | true
2          | silver   | 2025-02-01     | true

Key 1's later row (platinum) is correctly the only one flagged current; the earlier gold row is retained (type 2 keeps history) but demoted. Key 2, with only one row, is trivially current.

Writing the upsert. The insert/update side depends on the engine. Where MERGE is available (SQL Server, Oracle, Snowflake, Postgres 15+), one statement can both insert genuinely new rows and update current_flag on rows that already exist:

MERGE INTO dim_table AS tgt
USING (
  SELECT custom_key, attr, effective_date,
    ROW_NUMBER() OVER (PARTITION BY custom_key ORDER BY effective_date DESC) = 1 AS current_flag
  FROM dim_staging
) AS src
ON tgt.custom_key = src.custom_key AND tgt.effective_date = src.effective_date
WHEN MATCHED THEN
  UPDATE SET current_flag = src.current_flag
WHEN NOT MATCHED THEN
  INSERT (custom_key, attr, effective_date, current_flag)
  VALUES (src.custom_key, src.attr, src.effective_date, src.current_flag);

-- Demote any row for a key that just received a new current version
UPDATE dim_table
SET current_flag = FALSE
WHERE current_flag = TRUE
  AND custom_key IN (SELECT custom_key FROM dim_staging)
  AND effective_date < (
    SELECT MAX(effective_date) FROM dim_staging s WHERE s.custom_key = dim_table.custom_key
  );

On engines without MERGE (or where the team prefers explicit control), the same outcome is two statements: an INSERT ... WHERE NOT EXISTS for genuinely new (key, effective_date) combinations, followed by an explicit UPDATE that sets current_flag = FALSE on every previously-current row whose key now has a newer version, and TRUE on the new one.

Trade-offs & pitfalls

• Late-arriving data (a row with an effective_date earlier than the current dimension row, but arriving after the load that made that row current) needs its own handling: naively re-running the rank-and-flag logic against the union of staging and existing dimension rows, not staging alone, is what correctly re-derives which row is current after a backdated insert.
• A tie on effective_date for the same key needs a secondary ORDER BY key (a load sequence number or ingestion timestamp), or which of two same-day versions ends up flagged current is the identical nondeterminism problem covered under dedup tie-breaking elsewhere in this topic.
• RANK() instead of ROW_NUMBER() would let two rows share rank 1 on an exact effective-date tie, which almost never matches the type 2 requirement of exactly one current row per key; use ROW_NUMBER() specifically, not RANK(), for this pattern.
• Don't forget an effective_end_date (or equivalent) column alongside current_flag: setting the previous row's end date to the new row's effective date at demotion time is what makes point-in-time historical queries ('what did this record look like on date X') possible, not just 'what is it now.'