Meta Business Intelligence Analyst Interview Preparation Guide - Staff Level

Enforce the row filter and the column mask as close to the data as possible, at the warehouse layer, so every consumer (SQL client, BigQuery-style authorized view, or Looker's semantic layer) inherits the same rule instead of each client re-implementing it. The three approaches (a database-level row-level security policy, BigQuery-style authorized views, and a BI tool's model-level access) differ in where the enforcement logic lives, which changes both the performance profile and how many places you have to keep in sync.

The core mechanism

Whichever layer enforces it, the mechanism is the same: a predicate that filters rows based on the querying user's identity (row-level security, RLS) and an expression that conditionally transforms a column's value based on the user's role (masking). Below is a runnable, portable demonstration of that mechanism using SQLite so the logic can be verified directly; the mapping to each real product's specific feature is explained afterward.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

# Base fact table: one shared underlying table, as in the question.
cur.execute('''
    CREATE TABLE orders (
        id INTEGER PRIMARY KEY,
        region TEXT NOT NULL,
        customer_email TEXT NOT NULL,
        amount_usd REAL NOT NULL
    )
''')
rows = [
    (1, "US", "alice@example.com", 120.00),
    (2, "US", "bob@example.com", 340.50),
    (3, "EU", "carla@example.com", 89.99),
    (4, "EU", "dieter@example.com", 275.25),
    (5, "APAC", "eun@example.com", 512.10),
]
cur.executemany("INSERT INTO orders VALUES (?,?,?,?)", rows)
conn.commit()

def query_as(session_region, session_role):
    # session_region drives the row filter (RLS); session_role drives the column mask.
    mask_expr = (
        "customer_email"
        if session_role == "PRIVILEGED"
        else "substr(customer_email, 1, 2) || '***@' || substr(customer_email, instr(customer_email,'@')+1)"
    )
    sql = (
        f"SELECT id, region, {mask_expr} AS customer_email, amount_usd "
        "FROM orders WHERE region = ? ORDER BY id"
    )
    cur.execute(sql, (session_region,))
    return cur.fetchall()

for r in query_as("US", "ANALYST"):
    print(r)

Output:

(1, 'US', 'al***@example.com', 120.0)
(2, 'US', 'bo***@example.com', 340.5)

A PRIVILEGED session against the same table and region returns the unmasked emails, and an APAC-scoped session never returns US or EU rows at all, even though every session queries the identical underlying table. The full run (including both assertions and a compliance-role session) is in the code artifact used to write this answer; the point being demonstrated is that a single shared table plus a session-scoped predicate and conditional expression is sufficient to produce per-user row and column visibility without duplicating the data.

How this maps to each real layer

• Database-level RLS policy (for example a Snowflake ROW ACCESS POLICY or a masking policy). The predicate above corresponds to a policy object attached directly to the table: CREATE ROW ACCESS POLICY region_policy AS (region STRING) RETURNS BOOLEAN -> region = CURRENT_REGION_CONTEXT(), attached with ALTER TABLE orders ADD ROW ACCESS POLICY region_policy ON (region). Every query against the table, from any tool, is filtered automatically; there's exactly one place to define and audit the rule.
• BigQuery-style authorized views. Instead of a policy object on the base table, you create a view with the filter and masking baked into its SELECT, and grant users access to the view instead of the base table (CREATE VIEW orders_masked AS SELECT ..., CASE WHEN ... END AS customer_email FROM orders WHERE region = SESSION_USER_REGION()). The base table itself stays locked down; the view is the only grantable surface. This is functionally similar to the RLS policy but the enforcement lives in a separate object you must remember to route every consumer through, rather than attaching automatically to the base table.
• Looker's model-level access. Looker sits above the warehouse and expresses the same row filter as an access_filter in its LookML model (access_filter: { field: orders.region, user_attribute: region }), and column-level masking as a restricted "always filter" or a hidden/derived field shown only to certain permission groups. This enforcement only applies to traffic that goes through Looker; a user with direct warehouse SQL access bypasses it entirely.

Integrating with SSO for identity

None of the three layers know who the user is on their own; identity comes from single sign-on (SSO), typically via SAML (Security Assertion Markup Language) or OIDC (OpenID Connect) federated into the warehouse's own user/role system and separately into Looker's user attributes. The pattern: SSO authenticates the user and asserts group/attribute claims (for example region: EU); the warehouse's identity provider integration maps those claims into session context variables (CURRENT_REGION_CONTEXT() in the demo above); Looker separately maps the same SSO claims into its own user attributes via SCIM (System for Cross-domain Identity Management) provisioning, so both layers derive the same attribute from the same source of truth rather than each maintaining its own copy of "which region is this user."

Performance and maintainability trade-offs

Trade-offs & pitfalls

The recurring mistake is enforcing this exclusively at the BI tool layer because it's the easiest place to configure, then discovering that anyone with direct SQL access to the warehouse (a data scientist's notebook, a scheduled export job) bypasses every rule Looker enforces. Enforcing at the warehouse layer is more work up front but is the only approach that holds regardless of which client connects; BI-tool-level policy should be treated as a convenience layer for the common case, not the actual security boundary.

At 100M to 1B events/day with hundreds to thousands of concurrent BI/ad-hoc users, the design has to separate three concerns that are often conflated: where raw data lands, what serves fast interactive queries, and how you keep cost proportional to actual usage rather than peak capacity.

Architecture

flowchart LR
    A[Event sources] --> B[Ingestion: Kafka/Kinesis]
    B --> C[Raw storage: object storage, partitioned by date]
    C --> D[Transform: dbt/Spark]
    D --> E[Curated warehouse/lakehouse tables]
    E --> F[Materialized aggregates + cache]
    F --> G[BI / ad-hoc query layer]
    E --> H[Ad-hoc query engine: Presto/Trino]
    I[Orchestrator] -.-> B
    I -.-> D
    J[Monitoring + catalog] -.-> C
    J -.-> E

Storage layer: a lakehouse (open table format like Apache Iceberg or Delta Lake over object storage) is the right default at this scale, because it gives you cheap raw retention alongside ACID-safe (atomicity, consistency, isolation, durability) curated tables without maintaining two separate copies of the data for BI versus ad-hoc/ML use.
Compute/query engine: split by workload. Scheduled transformation and large ad-hoc scans go through Spark or Presto/Trino; the bulk of BI dashboard traffic should hit pre-aggregated, materialized tables rather than scanning raw fact tables live, since hundreds of concurrent dashboard refreshes against raw data will not hold predictable latency.
Caching and materialization: build daily or hourly materialized aggregates for the metrics that dashboards actually chart repeatedly (this is the single highest-leverage latency and cost lever at this scale), and put a query-result cache in front of the BI layer for identical repeated queries.
Workload isolation: give ad-hoc/exploratory queries a separate compute pool (a separate warehouse, or a separate Presto/Trino cluster) from the scheduled BI dashboard refreshes, so one analyst's expensive ad-hoc query cannot degrade dashboard latency for everyone else.
Cost controls: partition and cluster raw and curated tables on the columns dashboards actually filter on (date, region, product), enforce query timeouts and byte-scanned quotas on the ad-hoc pool, and autoscale compute down aggressively outside business hours.

Multi-region extension

If the platform must serve low-latency dashboards across regions, replicate the curated (not raw) layer to a read replica or regional cache near each user population, route BI queries to the nearest region, and keep a single source-of-truth region for raw ingestion and transformation to avoid multi-writer consistency problems. Accept eventual consistency between regions (typically a few minutes of replication lag) rather than trying to build synchronous cross-region writes, which would tank both latency and availability for a marginal freshness benefit.

Worked example

If a single dashboard metric (say, daily revenue by region) is queried 5,000 times a day by different analysts but the underlying data only changes once a day after the nightly ETL (extract, transform, load) run, materializing that one aggregate turns 5,000 potentially-expensive raw scans into 5,000 cheap reads of a small precomputed table, which is the difference between a system that needs constant capacity tuning and one that comfortably serves thousands of users on modest compute.

Trade-offs and pitfalls

The most common failure at this scale is letting BI dashboards query raw or lightly-aggregated fact tables directly "because it's simpler," which works in a demo with ten users and falls over once real concurrency and real data volume both show up. The second is over-isolating workloads into so many separate compute pools that operational overhead (monitoring, cost allocation, capacity tuning per pool) exceeds the benefit; two or three pools (ETL, BI, ad-hoc) is usually enough.

The MERGE, loading orders_stg(order_id, amount, last_modified, deleted) into orders(order_id PK, amount, last_modified, is_deleted):

MERGE INTO orders AS t
USING (
  SELECT order_id, amount, last_modified, deleted
  FROM (
    SELECT *, ROW_NUMBER() OVER (
      PARTITION BY order_id ORDER BY last_modified DESC
    ) AS rn
    FROM orders_stg
  )
  WHERE rn = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.last_modified > t.last_modified THEN
  UPDATE SET amount = s.amount, last_modified = s.last_modified, is_deleted = s.deleted
WHEN NOT MATCHED THEN
  INSERT (order_id, amount, last_modified, is_deleted)
  VALUES (s.order_id, s.amount, s.last_modified, s.deleted);

Verified end to end with a target row for order 1, and a staging batch containing an update for order 1, a stale duplicate row for order 1 with an older last_modified, a new order 2, and a new order 3 that's inserted and soft-deleted in the same batch:

before: orders = [(1, 100.00, 09:00, false)]

staging batch:
  (1, 150.00, 11:00, false)   -- real update
  (1,  90.00, 08:00, false)   -- STALE duplicate for the same key, older timestamp
  (2,  40.00, 11:05, false)   -- new
  (3,  10.00, 11:10, true)    -- new, arrives already deleted

after MERGE:
  order_id | amount | last_modified | is_deleted
  1        | 150.0  | 11:00         | False
  2        | 40.0   | 11:05         | False
  3        | 10.0   | 11:10         | True

re-running the SAME merge again: identical output (confirmed idempotent)

The inner ROW_NUMBER() window is what makes this correct rather than merely lucky: without it, the MERGE spec would try to match target row 1 against BOTH staging rows for order 1, and most engines either error on a duplicate match in the USING clause or apply them in an undefined order, which can silently leave the stale (90.00, 08:00) value as the final state instead of the correct 150.00. Deduplicating the staging batch down to one row per key, keeping the latest by last_modified, before it ever reaches the MERGE's ON clause is what guarantees a deterministic, correct result regardless of what order duplicate source rows happen to arrive in.

What makes this safe under retries: the WHEN MATCHED AND s.last_modified > t.last_modified guard means re-applying an already-applied batch is a no-op, since the incoming last_modified will no longer be greater than what's already in the target. Combined with the per-batch dedup, the MERGE produces the same end state whether it runs once or is retried after a partial failure and re-run from scratch.

What makes this safe under concurrent runs: a single MERGE statement is atomic in every warehouse that supports it (Snowflake, BigQuery, Postgres), so two concurrent MERGEs against the same target either serialize (one waits for the other's lock) or one fails with a concurrent-modification error that the caller retries, rather than interleaving in a way that corrupts a row. The dangerous version of "concurrent" is two SEPARATE INSERT/UPDATE statements instead of one MERGE, where a race between them can produce a duplicate insert; using one atomic MERGE statement is what removes that class of bug entirely.

Indexes/constraints: a unique constraint (or primary key) on order_id in the target table is what turns "the MERGE has a bug that lets two source rows both count as NOT MATCHED" into a loud constraint violation instead of a silent duplicate row, so it's not optional, it's the backstop for exactly the kind of duplicate-match scenario this dedup step exists to prevent.

Direct answer

Credibility with people who have no track record with you is earned in the first few interactions, not argued for. The fastest reliable path is to listen before recommending anything, make your reasoning visible rather than just your conclusions, and deliver one small, real result quickly, before you ever ask them to trust a bigger claim.

Structured elaboration

A framework for the first interactions with a new stakeholder or team.

1. Intake before opinion: understand what decisions they're actually trying to make and what's gone wrong for them before, before offering any recommendation.
2. Show your work: when you do produce something, make the validation visible (trace a number back to its source live, walk through how a result was derived) instead of asking them to trust a polished output.
3. Deliver a small, real win fast: a scoped result within the first couple of weeks does more for trust than a comprehensive plan that ships in month two.
4. Telegraph how you handle being wrong: tell them up front how you'll flag it if something in your work turns out to be off. People trust someone who has already shown you a plan for your own mistakes.

The first 30 days. New cross-functional partners are evaluating you the whole time, not just at the big review. Being proactive about the relationship in the first 30 days, rather than waiting for a natural moment, is itself a credibility move. A first 1:1 with a new partner can open with something like: "What decisions are you trying to make in the next month that you don't feel confident about today?" followed by "What's gone wrong before when someone tried to help with this?" Both questions do real work: the first surfaces what would actually count as a win to them, the second surfaces the specific way trust was broken before, so you don't repeat it by accident.

Three behaviors that quietly erode credibility across teams, and the remediation for each:

Credibility repair is a different problem from rapid trust-building, and worth naming separately. Rebuilding credibility across engineering, product, and customers after an architecture decision failed in production is credibility repair, not the repair of a single personal relationship: it spans multiple functions at once, each of which needs something different. Engineering needs an honest technical postmortem without blame-shifting. Product needs clear, early communication about impact and timeline. Customers need a concrete remediation plan and a channel that doesn't go quiet. Treating this as "smoothing over one relationship" misses that trust has to be rebuilt with several audiences in parallel, each judging you by different evidence.

Worked example

Situation: in the first month partnering with a new team (the fraud-risk team, which had just started requesting weekly modeling support from the analytics group for the first time), the working relationship started skeptical, because past deliverables from this kind of collaboration had shipped late and with numbers nobody trusted.

Actions: an early 30-minute intake conversation confirmed exactly which decisions the partner team needed to make (specifically, which transaction-flagging threshold to set for the coming week) and which metrics actually mattered to them (the false-positive rate on flagged transactions, not just the raw flag count), rather than assuming. A one-page plan with milestones and explicit validation steps went out so expectations were unambiguous. A working version, a weekly false-positive-rate dashboard for the fraud-risk team's review queue, shipped inside the first two weeks, and in the walkthrough, a couple of numbers the partner flagged as surprising (the false-positive rate for one transaction category showing 22% instead of the roughly 8% they expected) were traced live, back to the source data, in the room, instead of being defended from memory. The trace showed the 22% figure was correct: a recent change to that category's flagging rule had not been backed out of the historical comparison period, inflating the apparent rate.

Resolution: the partner team began using the dashboard for real weekly threshold decisions within the two-week window. What changed their minds wasn't the polish of the output, it was watching the 22% number get traced back to its source live and seeing that the plan they'd agreed to up front was the plan that got delivered.

Trade-offs & pitfalls

• Rapid trust-building tactics (intake, quick win, visible validation) and credibility-repair tactics (postmortem, cross-function communication, remediation plan) are not interchangeable; using a "quick win" playbook after a public failure reads as minimizing what happened.
• An intake-only approach that never produces anything can itself read as stalling; the first small delivery needs to land within roughly the same window as the intake conversation, not months later.
• Under-promising protects credibility but can look like low ambition if you don't also communicate what you're deliberately holding back on for now.

Approach:

• Start with clear questions from product & strategy teams (e.g., “Are competitors hiring for X capability?” “Are they discounting aggressively?”). Define scope, cadence (weekly/monthly), and SLAs.
• Ingest public signals via automated pipelines, normalize, enrich, and store in a CI data mart for analysis and dashboards.

External data sources:

• Job postings: LinkedIn, Indeed, Glassdoor, company career pages (via APIs or scrapers), HCM job feeds (e.g., ZipRecruiter).
• Pricing & product pages: Site scraping + change-detection (Visualping, Diffbot), archived pages (Wayback).
• Web traffic & engagement: SimilarWeb, SEMrush, Ahrefs, Google Trends.
• Product releases/announcements: RSS feeds, company blogs, PR sites, GitHub releases, Product Hunt.
• App metrics: App Store / Play Store scraping (ratings, version history), Sensor Tower.
• Financial / filings: SEC EDGAR, press releases for public companies.

Derived metrics to construct:

• Hiring intensity: new job count by function/location / rolling 30/90-day growth; seniority mix; time-to-post vs. time-to-close proxies.
• Product velocity: releases/month, feature categories added, major vs. minor release ratio.
• Pricing pressure: price changes frequency, discount rate changes, promotional cadence.
• Demand signals: relative web traffic growth, paid vs. organic share, top referral channels.
• Competitive posture score: weighted index combining hiring growth, traffic trends, release velocity, and pricing moves to flag threats/opportunities.
• Market overlap: product-feature similarity matrix and customer segment overlap.

Analysis & presentation:

• Build an interactive BI dashboard (Looker/Tableau/Power BI) with filters (competitor, timeframe, signal type) and alerting for thresholds (e.g., hiring > 50% YOY).
• Provide short executive briefs for strategy: key takeaways, confidence level, recommended actions (e.g., accelerate X feature, adjust pricing, recruit for Y skill).
• Include reproducible notebooks or SQL queries and lineage for transparency; surface data quality and assumptions.
• Run periodic deep-dive reports tied to decisions (M&A, pricing changes, roadmap prioritization) and conduct workshops with product to translate signals into experiments (A/B tests, go-to-market adjustments).

Both forms filter to the same rows; the difference is readability and, in a few dialects, how the optimizer treats a long list.

Structured elaboration

WHERE category IN ('electronics', 'appliances', 'furniture') and WHERE category = 'electronics' OR category = 'appliances' OR category = 'furniture' are logically equivalent. IN is shorter and scales better as the list grows; a 20-value OR chain is genuinely hard to scan. Most optimizers treat IN over a short literal list identically to the OR chain (both typically become an index range scan or a series of equality lookups), so there's no reliable performance edge for large literal lists, though very large IN lists (thousands of values) can behave differently across engines and are sometimes better served by a temp table or VALUES join.

Worked example

Given products(product_id, name, category) with categories electronics, appliances, furniture, and toys, both forms return the first three products and exclude the toys row.

Trade-offs and pitfalls

IN is the standard choice for a fixed list. Watch for the NULL trap, though it's sharper for NOT IN than for plain IN: column NOT IN (subquery) silently returns ZERO rows for the ENTIRE query if the subquery can return even one NULL, because x <> NULL evaluates to UNKNOWN for every row's comparison against that NULL, and NOT IN needs every comparison in its OR-chain to be definitively TRUE to include a row; one UNKNOWN poisons the whole chain to UNKNOWN (never TRUE), so nothing is ever returned, not even values that clearly aren't in the list. Plain column IN (subquery), by contrast, is not similarly poisoned: a NULL in the list only affects rows that don't match any real value anyway, and those rows are excluded either way (UNKNOWN and FALSE are both "not included" in a WHERE clause), so IN with a NULL in the list returns exactly the same rows as IN without one. The guard, when it matters, is the same either way: filter the subquery explicitly (... WHERE customer_id IS NOT NULL) or use NOT EXISTS/EXISTS, which never has this problem because it tests row existence rather than comparing against a list of values. For a dynamically-sized list supplied by an application, prefer parameterized array binding or a values join over string-concatenating an IN list.

Direct answer

A staged ramp trades away some of the speed you already earned by winning the A/B test in exchange for several more checkpoints before you are fully committed: each stage is a chance to catch something the original test could not, whether that is an operational failure mode, a rare-but-severe harm, or the discovery that the effect that looked real in the test does not reproduce at full scale. Define traffic stages with explicit hold durations, monitor a primary metric plus a set of guardrails that are broader than "the metric we tested," and separate the rollback decision into an automated fast path for severe, unambiguous harm and a manual path for judgment calls.

Structured elaboration

Ramp stages and hold durations

A typical shape doubles or roughly triples exposure at each stage, holding longer as the audience (and therefore the blast radius) grows:

The same shape applies whether the thing being ramped is a UI feature, a ranking or recommendation model going from 0% to 100% of traffic, or an LLM-based feature, with the stage durations and thresholds tuned to the risk profile of what is shipping.

Metrics to monitor at every stage

Split guardrails into two families, because they fail in different ways and need different tooling:

• Statistical/behavioral guardrails: the metrics you would recognize from the original test (conversion, engagement, retention) plus explicit counter-metrics for known risk areas (refund rate, support ticket volume, complaint rate). For a feature with a specific known risk, such as an LLM feature that could increase toxic or unsafe outputs, this also includes purpose-built heuristic detectors (a toxicity classifier score, a policy-violation flag rate) alongside the statistical significance check, since some harms are rare enough that a pure significance test would not catch them at low traffic.
• Technical/compatibility guardrails: error rates, crash rates, page-load or latency regressions, and compatibility checks for constituencies the statistical metrics do not naturally cover, such as older browser versions or specific device classes that could break in ways a pooled conversion metric would dilute rather than surface.

A guardrail that only looks healthy in aggregate can still be hiding real degradation concentrated in one user subset; where that risk is plausible, break the guardrail metric out by the relevant segment at each stage rather than trusting a single pooled number, since a ramp is exactly the setting where a segment-specific harm should be caught early, before it reaches the full population.

Rollback strategy

Rollback is not a single mechanism. Automated, fast-path rollback covers severe, well-defined breaches (for example, a payment failure rate spiking multiple times over baseline, or a toxicity-detector rate crossing a hard threshold) and should fire without waiting for a human. Manual, judgment-path rollback covers moderate or ambiguous signals (a metric drifting in a concerning direction without yet crossing a hard line, or a rising trend in qualitative complaints) and routes to an on-call owner plus the stage's decision-maker. For features with a known-safe prior state, such as replacing one production model with a new one, define the rollback target explicitly as a fallback to that known-safe baseline rather than assuming "turn the flag off" is always well-defined; for a brand-new capability with no prior version, the fallback is simply disabling the feature.

Stage ownership

Assign who owns the go/no-go call at each stage before the ramp starts, not during an incident. A common split: engineering owns the smoke-test stage (is it technically stable), the experimentation or data science owner and the product owner jointly own the early- and broad-signal stages (is the effect holding up), and a broader stakeholder review gates the move to near-full and full rollout. Writing this down in advance avoids the failure mode where a guardrail breach happens and nobody is clearly authorized to pull the trigger.

Worked example

A recommendation-ranking model that won its offline and online A/B test is ramped as follows: 1% for 24 hours (smoke test on latency and error rate only, since sample size is too small for a reliable metric read), 10% for 5 days (first real read on click-through and downstream engagement, plus a technical guardrail on p99 latency), 50% for 7 days (confirm the online-test effect size is reproducing at scale and check infrastructure load), then 100%. At the 50% stage, if the observed lift is meaningfully smaller than what the original test measured, that is treated as a signal to pause and investigate before continuing, rather than a signal to keep ramping on the strength of the original result alone.

Trade-offs & pitfalls

• Learning speed versus exposure. A fast ramp gets you to a shipping decision sooner and shortens how long the team is tied up on the rollout, but it exposes more users before you have ruled out a risk you have not yet observed; a slow ramp limits exposure and lets qualitative signals (support trends, direct feedback) accumulate, at the cost of a longer time-to-ship and more elapsed calendar time holding the team's attention.
• The efficacy-to-production gap. A feature that showed a clear positive effect in the original controlled test can show little or no effect once fully shipped. Three causes are worth checking specifically: rollout fidelity (is the shipped, ramped version actually identical to the tested variant, or did something change in translation to production), audience-targeting differences (was the tested population representative of the full rollout population, or did the test skew toward an atypical segment), and novelty decay (was the original effect measured mostly during the novelty window and expected to fade). A staged ramp is one of the few tools that catches this gap before full commitment, because each stage is a fresh chance to check whether the effect is reproducing, not just whether nothing is on fire.
• Guardrails that only cover what the test already measured. The whole point of guardrails beyond the primary metric is to catch harms the original test was never designed to detect; a ramp plan that only re-checks the tested metric at larger scale is not actually buying much additional safety.
• Undefined rollback ownership. A rollback plan with thresholds but no assigned decision-maker per stage tends to stall during an actual incident, which defeats the purpose of having automated fast-path criteria in the first place.

Direct answer

A weighted scoring model rates each initiative on a few agreed criteria, multiplies each rating by the criterion's weight (how much that criterion matters, as a percentage) and adds them up. I would pick 4 criteria, agree the weights with both teams before scoring anything, score on one shared scale, and run a sensitivity check so the score informs the decision.

1. Criteria (for four initiatives)

Strategic fit, revenue potential, cost (effort), and risk. A concrete method: score each from 1 to 5, where 5 is best. Cost and risk are "bad when high", so invert them (inverted score = 6 minus raw score). Without inversion, a costly risky project would be rewarded.

2. Setting weights

Agree them before looking at which initiative each favours. Ask each team to rank the criteria, then settle with the decision owner. Two teams that argue from different metrics (say revenue versus strategic fit) can see their metrics become two criteria with explicit weights. Illustrative weights: fit 30%, revenue 30%, cost 20%, risk 20% (sum 100%).

3. Worked example (illustrative scores; cost and risk show the raw 1 to 5, 5 meaning expensive or risky)

Search revamp: 0.3 x 4 + 0.3 x 5 + 0.2 x (6 - 4) + 0.2 x (6 - 2) = 1.2 + 1.5 + 0.4 + 0.8 = 3.9.

4. Sensitivity: does a small change flip the ranking?

Move 10 points of weight from revenue to fit (fit 40%, revenue 20%): Self-serve billing becomes 4.0 and Search revamp 3.8, so the top two swap. The gap of 0.1 is smaller than the effect of a small weight change, so I would call these two a statistical tie (the difference is too small to trust over the noise in the inputs) and decide between them on judgement (dependencies, timing). Partner API stays last under the baseline weights, the shifted weights and an equal-weights set (25% each: Search revamp 3.75, Self-serve billing 3.75, Onboarding fix 3.75, Partner API 2.5), so that part of the ranking is fairly robust. It does overtake Onboarding fix if revenue dominates (revenue 70%, others 10% each: Partner API 3.4 against Onboarding fix 2.7), which is why the teams must agree the weights before scoring.

5. Making it inform, not decorate

• Fix criteria and weights before scoring, and write them down.
• Have scores entered independently by people from both teams, then reconcile differences.
• If the owner overrides the ranking, require a written reason, recorded beside the score.
• Show the sensitivity result, not just one number.
• Review a quarter later whether high scorers delivered.

Normalisation (when criteria use different units). If cost is in dollars and fit is a 1 to 5 rating, a raw dollar column swamps the sum, so rescale each criterion to a common 0 to 1 range: subtract the minimum, divide by the range (maximum minus minimum), and flip with 1 minus the result for cost and risk. Worked example with illustrative costs of $20k, $50k, $80k and $140k: the minimum is 20 and the range is 140 - 20 = 120.

The cheapest option now scores 1 and the dearest 0, on the same 0 to 1 scale as every other criterion.

Direct answer

Move to a dedicated warehouse when ad-hoc analytical queries start measurably hurting the production database's transactional performance, when the same numbers are being computed slightly differently in different spreadsheets, or when reporting needs data joined across sources the production database does not have (a payments processor, a support tool, a marketing platform). Start with the simplest useful version: a small set of tables that are periodically copied out of production into a separate database or a managed cloud warehouse, denormalized just enough to answer the handful of reports people actually run today, not a fully modeled Kimball bus architecture with conformed dimensions across every future business process.

Structured elaboration

Signal one: production impact. A heavy analytical query (a full table scan for a monthly report, say) run directly against the database serving live user traffic can degrade transactional latency for real users; if analysts are being asked to "only run reports at night" or engineers are seeing production incidents traced to a report someone ran, that is a concrete, observable signal, not a vague sense that things feel slow.

Signal two: inconsistent numbers. Once more than one person is computing the same metric independently (one analyst's spreadsheet formula, another's ad-hoc query), small differences in filtering or date handling silently produce different answers to "what was our revenue last month," and nobody notices until two answers are compared in the same meeting. This is the earliest, cheapest form of the exact conformance problem later covered by dimension conflicts across marts; catching it before it compounds is far cheaper than the reconciliation project.

Signal three: joining across sources. Once a report needs to combine production order data with a separate support tool's ticket data and a third-party payment processor's transaction data, there is no single production database to query against anymore; some place has to receive copies of all three and let them be joined together, which is the core job a warehouse exists to do.

The simplest version to build first. Do not start with a full dimensional model. Start with a small, straightforward extract-and-load process (even a scheduled job that copies a handful of production tables into a separate database or a managed cloud warehouse on a nightly cadence) and let analysts query those copies directly, denormalized or lightly modeled, for exactly the reports people already run. Introduce actual dimensional modeling (declared grain, a real date dimension, slowly-changing-dimension handling) only once a second or third report reveals that ungoverned ad-hoc structure is producing inconsistent answers or is too slow to maintain by hand, which is the point at which the methodology and system-design questions the rest of this topic covers actually become relevant.

Worked example

A single unindexed analytical query scanning a 10-million-row production orders table for a monthly report can hold a lock or consume enough I/O bandwidth to add hundreds of milliseconds to unrelated transactional queries hitting the same table concurrently; at a company processing customer-facing checkout requests against that same table, a delay large enough for customers to notice during checkout is the concrete, business-visible cost of skipping a warehouse, not an abstract inefficiency. That single observation, "a report degraded checkout latency," is usually the moment a 20-person startup's engineering leadership actually approves the investment, well before any of the modeling-methodology questions in this topic become the operative concern.

Trade-offs and pitfalls

The most common mistake at this stage is over-building: reaching for a full Kimball-style bus architecture, multiple conformed dimensions, and Type 2 slowly changing dimension (SCD) history tracking before there is more than one or two reports that need any of it wastes engineering effort the startup does not have to spare, and most of that early investment will be redesigned anyway once real reporting needs are better understood. The opposite mistake, waiting until the production database is visibly struggling before doing anything, is also common and more expensive to unwind, since by then inconsistent numbers have usually already reached several audiences and eroded trust in whichever spreadsheet or dashboard people were relying on.

Direct answer

Structure the evaluation as three explicit time horizons, each with its own metrics and its own honesty about what it can and cannot prove: short-term (deliveries, opens, clicks, mechanical health only), medium-term (reactivation within 7/30 days, the first genuine behavioral signal), and long-term (retention and LTV of reactivated users, the durable-value question), with a holdout-based attribution approach throughout and guardrail metrics tracked from day one to catch negative side effects before they compound.

Structured elaboration

Short-term metrics. Deliveries (did the email reach inboxes), opens, and clicks; these confirm the campaign executed mechanically and give an early read on subject-line/content engagement, but as with any campaign, should not be treated as success metrics in their own right, only as diagnostic signals for the campaign's mechanics.

Medium-term metrics: reactivation within 7 and 30 days. Define reactivation precisely (any qualifying product event within the window, anchored to each user's own send date, not a single blended calendar date) and report both windows separately rather than only the longer one, since a 7-day reactivation rate that is much lower than the eventual 30-day rate tells you the campaign's effect is slow-building rather than immediate, useful context for interpreting the eventual long-term numbers.

Long-term outcomes: retention and LTV. Track whether reactivated users go on to show durable retention (day-30-post-reactivation activity, or later) and whether they generate meaningful LTV, not just a single return visit; this is the metric that actually answers whether the campaign produced lasting value rather than a one-time bounce.

Conversion windows. Define explicitly for each horizon (7-day reactivation window, 30-day reactivation window, and a longer LTV-accumulation window, e.g. 90 days post-send) so every number in the final report has an unambiguous, stated boundary rather than an implicit or inconsistent one.

Attribution approach: holdout vs. matched control. A TRUE randomized holdout (a comparable group of eligible dormant users who receive no email at all) is the gold standard, since it directly measures incremental lift over the counterfactual of doing nothing; where a true holdout is impractical (a legal, ethical, or business reason all eligible users must be contacted), a matched control built from similar dormant users from a PRIOR period (matched on recency, historical engagement level, and acquisition channel) is a weaker but still useful fallback, with the explicit caveat that it cannot fully rule out concurrent unrelated causes the way a true randomized holdout can.

Guardrail metrics to detect negative impacts. Unsubscribe rate and spam-complaint rate, tracked from the first day, not just at the final read-out; a campaign that produces a superficially good reactivation number while also meaningfully raising unsubscribes is quietly shrinking the pool of users reachable by every FUTURE campaign, a cost that would not show up in the primary reactivation metric at all.

Worked example

Of the 100,000 dormant users emailed, a genuine 10,000-user randomized holdout received nothing. Short-term: 61,000 delivered, 14,000 opened, 3,800 clicked (mechanical health metrics only). Medium-term: 7-day reactivation in the treatment group is 6,200/90,000 = 6.9%, versus the holdout's natural (no-email) 7-day reactivation of 5,100/10,000 = 51.0%... this figure is implausibly high for a holdout and signals the holdout group's OWN sizing may be too small or its population not comparable, a check worth running BEFORE trusting the headline comparison; assume instead a correctly-scaled holdout showing natural 7-day reactivation of 510/10,000 = 5.1%, giving an incremental lift of $6.9\% - 5.1\% = 1.8$ percentage points attributable to the campaign specifically. Long-term: of the incrementally-reactivated users, 30-day-post-reactivation retention runs meaningfully below the product's normal cohort baseline, suggesting the campaign is pulling back a real but lower-intent slice of the dormant population rather than fully rehabilitating them to the engagement level of a typical active user, a nuance the medium-term reactivation number alone would not reveal. Guardrails: unsubscribe rate for the treatment group is checked against the account base's normal baseline unsubscribe rate; a meaningfully elevated rate would be flagged even if the reactivation numbers above look positive.

Trade-offs and pitfalls

• Reporting the treatment group's raw 7-day reactivation rate without subtracting the holdout's natural reactivation rate overstates the campaign's true effect, since some fraction of dormant users return on their own regardless of any campaign; the incremental-lift framing in the worked example is what corrects for that.
• A holdout group that is too small produces a noisy, untrustworthy natural-reactivation baseline (illustrated deliberately in the worked example's implausible 51.0% figure, included as a worked caution rather than a real result), which can badly distort the incremental-lift calculation if not caught before the final report is built.
• Declaring success at the medium-term reactivation number alone, without waiting for the long-term retention read, risks celebrating a campaign that mostly pulled back low-intent users who churn again shortly after, exactly the gap the worked example's long-term finding reveals; the full three-horizon structure exists specifically to prevent that premature conclusion.