Spotify Data Analyst Interview Preparation Guide (Mid-Level)

The mediocre version of this answer treats it as a binary, ship or don't. The real skill is designing a third option, delivering with explicit, temporary mitigations, so the outcome isn't a rubber-stamp of either extreme.

Framework, weighing each option:

• Deliver on time with workarounds: protects the deadline, costs some rigor or completeness. Only acceptable if the gap isn't safety or compliance-critical and the workaround's limitations can be clearly disclosed.
• Delay: protects correctness and completeness, costs the deadline and whatever depends on it. The right call when the missing capability is load-bearing for something that can't be safely approximated.
• Deliver with mitigations: a middle path, ship a scoped-down or caveated version with the gap explicitly flagged and a committed close-out date. Usually the right default unless one of the extremes is clearly forced by the situation.

Interim safeguards to put in place regardless of the path chosen: an explicit, written caveat visible to whoever consumes the output, not just said out loud; a named owner and date for closing the gap; and a rollback or kill-switch if the workaround turns out worse than expected once real usage reveals it.

Communicating the plan to close the gap afterward: treat the interim delivery as a promise with a due date, not a permanent state. Put the close-out date on the same calendar or tracker as the original deadline so it doesn't quietly disappear, and proactively update stakeholders when the gap closes, or if the timeline slips, rather than waiting to be asked.

Worked example, primary: a stakeholder needs a new automated report shipped by end of quarter, but full regression testing on the underlying data pipeline hasn't finished, only about 70% of test cases have passed, the rest are still running. Delivering with workarounds: ship the report with a visible caveat, "figures for category X are pending final validation, expected accurate within plus or minus 3%," and manually spot-check the highest-value rows by hand before sending, rather than fully trusting the pipeline. Delaying only makes sense if the remaining 30% of tests specifically cover a category the stakeholder cares about most; if it's lower priority, delaying the whole report to protect a corner nobody's using yet is the wrong trade. Interim safeguard: the caveat stays visible in the report until testing completes, and a hard date, five business days out, is set to either confirm the numbers or issue a correction.

The same shape applies when a KPI (Key Performance Indicator) request is blocked by restricted data access rather than incomplete testing. A BI analyst asked for a customer-segment breakdown, where access to a needed table is pending an approval that won't clear before the deadline, delivers the aggregate KPI that IS accessible, explicitly flags which cut is missing and why, and sets the segment breakdown as a committed follow-up once access clears, rather than either withholding the whole report or fabricating the missing cut from a proxy without disclosure.

When the deadline itself is regulatory, a compliance date that can't move, and the missing capability is a stability safeguard rather than a nice-to-have, for example shipping a required change without the usual load-testing window, this isn't a call to make alone. It needs explicit sign-off from legal, product, and ops before shipping, because the risk (a production incident) and the requirement (the regulatory deadline) are both real and in tension, and the interim safeguard has to include an agreed rollback plan owned by ops, not just a caveat in a doc.

Direct answer. A standard B-tree index stores values in sorted order, which lets it efficiently find a matching PREFIX of a string; a wildcard at the START of a LIKE pattern means there's no fixed prefix to search for, so the index can't narrow the search at all and the engine falls back to checking every row's text against the pattern directly.

Structured elaboration. LIKE 'foo%' (wildcard only at the end) can use a standard B-tree index efficiently, because every string matching that pattern shares the same prefix, 'foo', which the index's sorted order can jump straight to. LIKE '%foo%' or LIKE '%foo' (wildcard at the start) has no such fixed prefix: a match could be any string CONTAINING or ENDING WITH 'foo' anywhere, which the sorted order of a standard index gives no leverage on at all, so the engine has no better option than scanning and checking every row.

Realistic options: a specialized full-text search index (built for token or substring matching rather than prefix matching) is the most direct fix if your engine supports one and the matching semantics fit; a trigram or n-gram index (where supported) can accelerate substring matches specifically, including leading-wildcard patterns, by indexing small overlapping fragments of the text rather than the whole string; and, if the real requirement is closer to "search," moving that specific workload to a dedicated search engine designed for it is often the more durable long-term answer once a standard relational index genuinely can't help.

Worked example. A dashboard filter doing WHERE comments LIKE '%refund%' against a large text column is the exact shape that defeats a plain B-tree index; if the underlying database supports a trigram index, adding one on that column can make this specific kind of substring search dramatically faster without changing the query at all, since the trigram structure is built specifically to handle "the pattern could start anywhere" searches that a standard index can't.

Trade-offs and pitfalls. Specialized indexes for this kind of search (trigram or full-text) cost more storage and more write-time maintenance than a standard B-tree, and aren't a good fit for every use case (very short strings, or a genuinely rare query pattern that doesn't justify the ongoing cost); weigh how often this kind of search actually runs, and how latency-sensitive it is, against that ongoing cost before reaching for a specialized index.

Direct answer

Run five automated nightly checks: orphaned facts (a fact row's foreign key with no matching dimension row), duplicate current-flag rows (more than one current_flag = true per business key in an Slowly Changing Dimension Type 2 (SCD2) dimension), unexpected dimension growth (a sudden spike in new rows, often signaling a bad join or dedup failure upstream), missing current_flag (a business key with zero current_flag = true rows, meaning it has no resolvable current state), and referential-integrity/reconciliation checks (aggregates from the star schema matching source-of-truth totals).

Structured elaboration

• Orphaned facts: SELECT COUNT(*) FROM fact f LEFT JOIN dim d ON f.dim_key = d.dim_key WHERE d.dim_key IS NULL. A nonzero count means facts are pointing at dimension keys that don't exist, usually from a load-ordering bug or a failed dimension load. Remediation: investigate and fix the load sequencing, and reconcile the specific orphaned rows once the dimension catches up.
• Duplicate surrogate keys / duplicate current-flag rows: SELECT business_key, COUNT(*) FROM dim WHERE current_flag = true GROUP BY business_key HAVING COUNT(*) > 1. A nonzero result set means an SCD2 load bug (a new version inserted without properly closing the old one). Remediation: identify the specific load run that introduced it and correct the affected rows.
• Unexpected growth in dimension size: compare today's row count against a rolling baseline (e.g., a 30-day average plus some tolerance); a spike often signals a dedup failure (the same real-world entity being inserted as new rows repeatedly instead of matching to its existing record). Remediation: investigate the specific new rows for duplication patterns.
• Missing current_flag: SELECT business_key FROM dim GROUP BY business_key HAVING SUM(CASE WHEN current_flag THEN 1 ELSE 0 END) = 0. A business key with no current row can't be resolved by any current-state query. Remediation: investigate whether the entity was incorrectly closed out without a replacement version.
• Referential integrity / reconciliation: compare a star-schema-derived aggregate (like total revenue) against an independent source-of-truth total (the source system's own reporting, or a hand-computed check) for the same period, flagging any mismatch beyond a small tolerance for investigation.

Worked example

A nightly check finds 40 orphaned fact rows for yesterday's load, all pointing at product_key values not yet in product_dim. Investigating reveals the product dimension load failed silently the previous night; the fix is re-running the failed dimension load and then re-resolving the 40 orphaned facts against the now-complete dimension, plus adding an alert that fails the fact load explicitly if its upstream dimension load didn't complete successfully.

Trade-offs and pitfalls

These checks are cheap to run nightly and catch the highest-frequency, highest-impact modeling-level defects (as opposed to general data-quality issues like malformed values, which belong to a broader data-quality discipline). Treat any of these checks failing as a load-blocking or urgently-investigated event, not a background metric to review occasionally, since orphaned facts and duplicate current-flags directly corrupt downstream reporting the moment they occur.

Direct answer

Maintain the cumulative metric as an incrementally-updated table keyed on the entity (for example, per user), where each run only touches the partitions affected by NEW or CORRECTED data since the last run, using a merge/upsert rather than recomputing the running total from the full history every time.

Structured elaboration

• Table design: partition (or cluster) the maintained cumulative table by the entity key and, if the source data itself is naturally date-partitioned, by date as well, so a given run only needs to read the partitions that actually changed.
• Merge/upsert pattern: for each entity affected by new data since the last run, recompute that entity's cumulative value by adding the new increment to its PREVIOUSLY STORED cumulative value, rather than re-summing the entity's entire history. This turns an $O(\text{full history})$ recompute into an $O(\text{new data})$ update.
• Handling corrections to historical partitions: if a correction lands against data from months ago (a late refund, a corrected transaction amount), the incremental approach above breaks, because it assumed only NEW increments need to be added, not that OLD increments might change. The fix: when a correction is detected, recompute the cumulative value starting from the corrected partition forward, not just apply the delta at the current date, since every day's running total downstream of the correction is now stale. This is more expensive than a routine incremental update but still far cheaper than a full history recompute, since it only replays forward from the earliest affected date rather than from the beginning of time.
• Detecting that a correction happened at all: usually requires either an explicit "corrected records" feed from the source system, or a reconciliation check comparing a freshly-computed total for an old period against the currently stored one and flagging a mismatch.

Worked example

Consider a per-user cumulative revenue table maintained incrementally. On a normal day, a new $50 order for user 42 arrives; the maintained pipeline reads user 42's current stored cumulative value (say $1,200), adds $50, and writes back $1,250, touching only that one row. Contrast this with a correction: a $30 refund is discovered against an order from 60 days ago for the same user. A pure "add the new increment" pipeline would incorrectly just subtract $30 from TODAY's cumulative value, leaving every day BETWEEN the refunded order and today reporting a cumulative total that is $30 too high (since the running total at each of those intermediate dates was itself computed by summing everything up to that point, and none of those historical days ever saw the correction). The correct handling replays the cumulative sum forward starting from the date of the original order 60 days ago, correcting that day's cumulative total and every day after it through today, using the corrected order-level data as the source of truth.

Trade-offs & pitfalls

• The single biggest defect in a naive incremental design is exactly the correction case above: an interviewer probing this question is very likely testing whether the candidate's design silently mishandles a correction to old data, not just whether they know how to add up numbers.
• Detecting corrections is often the harder engineering problem than applying them, especially against a source system that doesn't clearly flag which historical records changed; a reconciliation job comparing recomputed vs. stored totals on a sample of old partitions is a common pragmatic mitigation.
• Replaying forward from a correction point is still bounded work (from the correction date to today), not unbounded, which is the entire point of this design relative to a full nightly recompute; but it does mean a correction to very old data is proportionally more expensive to apply than one to recent data.

Direct answer

A plain GROUP BY over transactions only ever produces rows for months that actually had activity, so months with zero transactions are missing, not zero. Fix it by generating a full 12-month calendar spine and LEFT JOINing the aggregated transactions onto it, then wrapping the aggregate columns in COALESCE(..., 0) so a month with no matches shows 0 instead of disappearing.

Approach

1. Build a "spine" of every month you need in the output, independent of whether any transaction happened. In portable SQL this is typically a recursive CTE (common table expression) or a generate_series (Postgres) / calendar table; this answer uses SQLite's recursive CTE.
2. Aggregate transactions by month separately, filtered to the same window.
3. LEFT JOIN the spine to the aggregated transactions on month, so every calendar month survives even with zero matches.
4. COALESCE the aggregate columns to 0 for the LEFT JOIN's NULL rows.

Worked example (sqlite3, verified)

Sample data (reference "current month" pinned to 2024-04, so the trailing 12 full months are 2023-04 through 2024-03):

CREATE TABLE transactions (transaction_id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER, transaction_date TEXT);
INSERT INTO transactions VALUES
  (1, 1, 100, '2023-05-03'),
  (2, 2, 200, '2023-05-20'),
  (3, 1, 150, '2023-07-01'),
  (4, 3, 300, '2023-07-15'),
  (5, 1, 50,  '2023-07-28'),
  -- 2023-06 and 2023-09 have zero transactions and must still appear as zero rows
  (6, 2, 400, '2023-10-01'),
  (7, 4, 250, '2024-02-14'),
  (8, 1, 999, '2022-12-01');  -- outside the 12-month window

Naive GROUP BY, shown first to demonstrate the bug:

SELECT strftime('%Y-%m', transaction_date) AS year_month,
       SUM(amount) AS total_revenue, COUNT(DISTINCT customer_id) AS unique_customers
FROM transactions
WHERE transaction_date >= '2023-04-01' AND transaction_date < '2024-04-01'
GROUP BY year_month
ORDER BY year_month;

Result: only 4 rows (2023-05, 2023-07, 2023-10, 2024-02). 8 of the 12 months in the window are silently missing, exactly the bug the question is asking about.

Calendar spine via recursive CTE:

WITH RECURSIVE months(month_start) AS (
  SELECT date('2023-04-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2024-03-01')
)
SELECT month_start FROM months ORDER BY month_start;

This recursive CTE runs in passes: the first SELECT produces a single base row (2023-04-01), then each following pass takes the previous pass's output row and builds the next one from it (date(month_start, '+1 month')), feeding back into months again. It keeps generating new rows this way until a pass's output fails the WHERE condition (month_start < date('2024-03-01')), at which point the recursion stops.

Result: 12 rows, 2023-04-01 through 2024-03-01, one per calendar month, confirmed by count.

Full zero-filled query:

WITH RECURSIVE months(month_start) AS (
  SELECT date('2023-04-01')
  UNION ALL
  SELECT date(month_start, '+1 month') FROM months WHERE month_start < date('2024-03-01')
),
monthly_txn AS (
  SELECT strftime('%Y-%m-01', transaction_date) AS month_start,
         SUM(amount) AS total_revenue, COUNT(DISTINCT customer_id) AS unique_customers
  FROM transactions
  WHERE transaction_date >= '2023-04-01' AND transaction_date < '2024-04-01'
  GROUP BY month_start
)
SELECT strftime('%Y-%m', m.month_start) AS year_month,
       COALESCE(t.total_revenue, 0) AS total_revenue,
       COALESCE(t.unique_customers, 0) AS unique_customers
FROM months m
LEFT JOIN monthly_txn t ON t.month_start = m.month_start
ORDER BY year_month;

Result: 12 rows, exactly one per month. 2023-04, 2023-06, 2023-08, 2023-09, 2023-11, 2023-12, 2024-01, and 2024-03 all show (0, 0); 2023-05 shows (300, 2), 2023-07 shows (500, 2), 2023-10 shows (400, 1), 2024-02 shows (250, 1). This matches the naive query's 4 non-zero months exactly, now with the 8 zero months made explicit instead of silently absent.

Key points

• The calendar spine must be built independent of the transactions table; if you generate months by extracting distinct months from transactions itself, you can never produce a month with zero rows, since it wouldn't exist in the source data to extract from.
• LEFT JOIN direction matters: the spine is the "keep every row" side, monthly_txn is the side that's allowed to have no match.
• COALESCE is applied to the aggregate output columns (total_revenue, unique_customers), not to the join key; the join key match/mismatch is what determines which rows need the COALESCE fallback in the first place.

Complexity, edge cases & pitfalls

• Dialect variation: this recursive CTE approach is portable across SQLite, Postgres, and SQL Server; Postgres also supports the more compact generate_series(date1, date2, interval '1 month'), and BigQuery has GENERATE_DATE_ARRAY. The underlying LEFT JOIN plus COALESCE pattern is identical across all of them, only the spine-generation syntax changes.
• If the transactions table is large, filtering the date window before aggregating (as done in monthly_txn) keeps the aggregation cheap; the spine itself is always cheap (12 rows), so the LEFT JOIN's cost is dominated by the aggregation, not the spine generation.
• A common mistake is generating the spine as a set of month labels (strings like '2023-04') rather than actual dates, then trying to compare or sort them lexicographically; that happens to work for zero-padded YYYY-MM strings but breaks immediately for anything that isn't already zero-padded and sortable as text.
• unique_customers in a zero-activity month is correctly 0 via COALESCE; be careful not to instead COALESCE the join key or the raw customer_id column, which would produce a wrong (non-zero) sentinel value rather than a true zero count.

Given funnel counts, identifying which step to prioritize requires computing each step's own conversion rate first, then reasoning about which is furthest from a realistic benchmark, since the step with the biggest absolute number drop isn't always the one with the worst underlying conversion rate.

Worked example

For a checkout flow with product-page visits = 10,000, add-to-cart = 1,500, checkout started = 600, purchase completed = 300:
$\text{Visit} \to \text{Cart}: \frac{1{,}500}{10{,}000} = 15\%$
$\text{Cart} \to \text{Checkout}: \frac{600}{1{,}500} = 40\%$
$\text{Checkout} \to \text{Purchase}: \frac{300}{600} = 50\%$

The biggest absolute count drop happens between visit and cart (8,500 users lost), but that's expected at the top of any funnel where most visitors are just browsing; the visit-to-cart rate of 15% is not unusual for e-commerce. The step most worth investigating first is Cart to Checkout: a 40% drop-off between adding something to a cart (an expressed purchase intent) and even starting checkout is a much larger loss of ALREADY-INTERESTED users than is typical, and is often attributable to fixable friction (unexpected shipping costs revealed at that step, a confusing 'proceed to checkout' button, or account-creation requirements) rather than a fundamental lack of interest.

Why this step, not simply the biggest raw number

A user who has already added an item to their cart has demonstrated real intent; losing 60% of them at the very next step is a much stronger signal of a fixable, high-leverage problem than the visit-to-cart drop, which mostly reflects normal top-of-funnel browsing behavior that's expensive and slow to move. Prioritizing by 'which step has the worst conversion RELATIVE TO a realistic expectation for that stage,' not by raw volume lost, is what a senior analyst does differently from a junior one here.

Trade-offs and pitfalls

Without an external benchmark or historical baseline for what a 'normal' conversion rate looks like at each stage, this reasoning is a judgment call, not a certainty; the right next step is validating the hypothesis with qualitative research (session recordings, exit surveys at the checkout-start step) before committing significant engineering effort to a fix.

Direct answer

I decide based on whether the new area only pays off for this one project or amortizes across future work, how risky it would be to look shallow in front of whoever's depending on the result, and whether the gap can be covered another way, a colleague, a partner, or an existing playbook, instead of by me personally learning it from zero.

Structured elaboration

Amortization: a skill that only matters for this one project is a weaker bet than one likely to keep paying off afterward, all else equal.

Risk of shallow competence: if the project puts me in front of a client or a senior stakeholder who already knows the adjacent area well, a thin understanding is more exposed and more costly to get caught out on than it would be internally.

Whether the gap can be covered another way: before assuming I personally have to close it, I check whether a colleague already has it, whether an existing playbook or reference implementation gets us most of the way, or whether the smarter move is to go deeper on what I already do and lean on someone else for the adjacent piece.

A realistic view of how far I can actually get in the window: a few weeks is enough to become dangerous-but-functional in most adjacent areas, not expert, so the plan has to assume that ceiling rather than an optimistic one.

The related case of several competing new areas on the same project, not just one: when that happens, the same logic applies but the constraint shifts from picking one over the other to splitting a fixed amount of learning time across more than one, deliberately, rather than defaulting to whichever is most comfortable.

Worked example

A project was coming up that would clearly lean on Kubernetes, an adjacent technical area I'd never worked in myself, and I had a few weeks before it started. I checked first whether someone else already covered it well; nobody did. I judged the risk of looking shallow as moderate, since the audience for this particular piece was mostly internal, not an expert client. I chose to spend the window on Kubernetes rather than going deeper on what I already knew, specifically because it looked likely to matter on projects after this one too, not just this one. By the time the project needed it, I was the one writing the deployment manifests and readiness probes for the new service, and the rollout held up on its own without needing to pull in the platform team. For the side I didn't pick, going deeper on my existing strength, I didn't just drop it: I lined up a teammate who already had strong depth there to review that part of the work, so the gap was covered by someone else's existing skill rather than by hoping it wouldn't come up.

Trade-offs and pitfalls

The common failure here is underestimating how visible shallow knowledge is to someone who already has real depth in the area, which shows up fastest in exactly the moments that matter most. The opposite failure is over-investing in depth on something that turns out to only matter for this one project, which doesn't amortize and would have been better spent elsewhere.

Direct answer: Structure the rollback policy around three layers: metric triggers that define when a rollback is even a candidate, severity tiers that determine how fast and how automatically it happens, and a notification plan that scales with severity, so a minor guardrail dip and a major user-harm event are handled by two clearly different paths rather than one blunt rule.

Structured elaboration

1. Metric triggers: define, per feature category, which metrics are rollback-eligible (usually guardrails: error rate, revenue-negative movement, safety/trust signals) and the specific threshold that makes a trigger fire, set before launch, not improvised during an incident.
2. Severity tiers:
   • Tier 1 (automatic, immediate): a metric breaches a bright-line safety or correctness threshold (e.g., error rate above X%, a payment failure spike); rollback is automated, no human approval required, because the cost of delay outweighs the cost of a false-positive rollback.
   • Tier 2 (fast human-in-the-loop): a guardrail metric crosses a concerning but not catastrophic threshold; an on-call owner is paged and must decide within a short window (e.g., 30 minutes) whether to roll back or hold.
   • Tier 3 (scheduled review): a metric is trending in a bad direction but slowly, or the primary metric simply missed its target with no acute harm signal; this goes to the next regular review, not an emergency process.
3. Notification: Tier 1 rollback triggers an automatic incident channel post and a summary to the feature owner and their manager after the fact; Tier 2 pages the on-call owner directly and requires an explicit decision logged; Tier 3 is folded into the normal launch-review cadence with no page.
4. Automated rollback mechanism: for Tier 1, the rollback must be technically instant (a feature flag flip, not a code deploy), since the tier's whole premise is that speed matters more than judgment in the moment.

Worked example: A payments feature has a Tier 1 trigger of "payment failure rate above 2% sustained for 5 minutes" (automatic flag flip, no approval, incident channel auto-posts), a Tier 2 trigger of "refund rate 1.5x baseline sustained for 2 hours" (pages the on-call PM, who must decide within 30 minutes whether to hold or roll back), and a Tier 3 condition of "conversion lift below the pre-agreed threshold at the 30-day mark with no guardrail breach" (goes to the standing weekly launch review, no page).

Trade-offs and pitfalls: A policy with only one tier (either everything is automatic, or everything requires human judgment) is wrong in both directions: fully automatic rollback on soft signals causes noisy, unnecessary reversals that erode trust in the system, while requiring human judgment for a catastrophic, unambiguous safety breach costs precious minutes. The other common gap is defining triggers and tiers but never load-testing the actual rollback mechanism itself, so the "automatic" rollback turns out to take 20 minutes to propagate when the policy assumed it was instant.

Direct answer

The underlying finding stays identical, but you change altitude, vocabulary, and level of supporting detail. An executive gets the headline, the business impact, and the recommended decision in one or two lines up front. The implementation team gets the mechanism, the caveats, and enough of the underlying data to act on it correctly.

Structured elaboration

• Altitude: conclusion-first for the executive, versus enough method detail for the team to trust and reproduce the diagnosis.
• Vocabulary: business-impact language (revenue, risk, timeline) for the executive, technical specifics (segments, funnels, thresholds) for the team.
• Format: a one-slide or one-paragraph summary versus a working document with a data appendix.
• What must never change: the number itself and the direction of the conclusion, in both versions.

Worked example

Finding: onboarding drop-off at step 3 is costing an estimated 6% of new signups per month.
Executive version: "we're losing about 6 of every 100 new signups at the step-3 confirmation screen, fixing it could recover meaningful revenue this quarter, recommend prioritizing it."
Team version: "62% of that drop-off happens on mobile between form submit and confirmation render, median time to abandon is 9 seconds, this looks like a loading-state issue on mobile specifically."
Both versions agree on the 6% headline number and the recommendation to prioritize the fix.

Trade-offs and pitfalls

The two versions can quietly drift into different conclusions if you're not careful, always trace both back to the same underlying analysis. Over-simplifying for the executive can also strip out the one caveat that would have changed their decision, so pick what to omit deliberately, not by default.

What the interviewer probes next

Expect a question about what happens when the executive summary gets forwarded on without you in the room, and how you prevent it from being read out of context.

Start by profiling the model to find hot spots: use Performance Analyzer in Power BI Desktop, DAX Studio / VertiPaq Analyzer to see table/column memory, and measure refresh and visual render times.

Practical steps (in order):

• Remove unused columns/tables: drop any fields not used in visuals, measures, or relationships. This directly reduces VertiPaq footprint.
• Reduce column cardinality: replace high-cardinality columns (GUIDs, long strings) with surrogate keys or bucketize (e.g., month-year, category groups). Where full detail isn’t needed, store truncated values or hashes only when necessary.
• Change data types: convert strings to integers, use Date vs DateTime when time not required, and set columns to the smallest numeric type supported. Avoid storing booleans as text.
• Replace calculated columns with measures: move row-level calculations into measures where possible (measures compute at query time and don’t consume model storage). Use variables in DAX to avoid repeated work.
• Introduce aggregation tables: create pre-aggregated fact tables (monthly, product-category) for common queries; configure Power BI aggregations so visuals hit these instead of the detailed fact.
• Consider DirectQuery or composite models when data is extremely large or frequently changing: DirectQuery offloads storage but increases query latency and depends on source performance. Use composite models to keep commonly queried aggregates in-import and detail in DirectQuery.

Validation:

• Track model .pbix size, VertiPaq memory by table/column before/after.
• Compare refresh duration, visual render times, and DAX query durations (DAX Studio).
• Use Performance Analyzer to ensure visuals now call aggregates/measures and fewer queries hit large tables.
• Run stakeholder scenarios (top 10 reports) to confirm correctness and end-user perceived responsiveness.

Trade-offs: importing reduces latency but uses memory; DirectQuery reduces footprint but needs robust source performance and may limit DAX. Document changes and keep a rollback plan.