Airbnb Business Intelligence Analyst (Entry Level) - Interview Preparation Guide

Direct answer
A data-quality issue caught in a production BI (business intelligence) report, wrong totals, duplicates, or missing rows, usually starts with someone noticing the number looks off, not with an automated check catching it first, which is itself a useful signal about what to fix afterward: strengthening detection so the NEXT issue is caught by monitoring rather than by a stakeholder's suspicion.

Structured elaboration
How it's typically detected: either a stakeholder flags a number that doesn't match their own expectation or a separate source they trust, or, in a better-instrumented setup, an automated check (a reconciliation or anomaly-detection check, the kind) catches it before anyone downstream sees the wrong number at all.

Root-cause investigation: working backward from the symptom, are the wrong numbers coming from a transformation bug (a join fanning out rows, causing duplicates), a source-data problem (the upstream system itself sent bad data), or a timing issue (the report ran before all the day's data had landed). Checking the pipeline's logs and re-running the relevant transformation step against a known sample is usually faster than guessing.

Fix and prevention: the immediate fix corrects the specific issue (deduplicating, correcting a join, re-running with the right data), and prevention means adding a specific automated check that would have caught this exact class of problem next time, closing the detection gap, not just fixing this one instance.

Worked example
A quarterly report shows total order count roughly 8% higher than the finance team's own independently-tracked figure. Detection: a finance analyst flagged the discrepancy after comparing it against their own tracking, not an automated check, since none existed for this specific report at the time. Root-cause investigation: tracing the pipeline revealed a recent change joined the orders table to a new promotions table to compute a discount-eligibility flag, and that join was one-to-many (a small number of orders had multiple matching promotion records), silently duplicating those orders in the final count. The fix: correcting the join to deduplicate before the final aggregation, and reprocessing the affected historical period. Prevention: adding a row-count reconciliation check comparing the source orders table's row count against the final reporting table's row count after every pipeline run, specifically the kind of check that would have caught this exact fan-out bug the moment it was introduced, rather than a month later when a finance analyst happened to cross-check manually.

Trade-offs and pitfalls
A fix that corrects the immediate wrong number without adding the specific preventative check that would catch the same class of bug again is only half the job, and it's tempting to stop there once the pressing issue is resolved, especially under time pressure to move on to the next task; the discipline of actually closing the detection gap, not just the immediate symptom, is what distinguishes a genuinely learned lesson from a one-off fix that the same bug pattern can reintroduce later in a different part of the pipeline.

Direct answer

Pick one metric per side of the market plus one that measures whether the two sides are in healthy balance: a buyer speed-to-value metric, a seller supply-utilization metric, and a marketplace-health metric that penalizes low-quality transactions rather than just counting them. Each needs an explicit instrumentation plan and one intervention it should trigger when it moves the wrong way.

Structured elaboration

The reason to pick exactly one metric per row instead of a longer list: each side of a marketplace can be optimized in a way that actively harms the other (fast matches for buyers can mean routing all demand to a handful of top sellers, which tanks fill rate for everyone else), so the three together act as a check on each other rather than three independent scoreboards.

Worked example

Fill rate. A category has 500 seller listing-slots available this week and 350 of them convert into a completed transaction.

$\text{Fill Rate} = \frac{350}{500} = 0.70$

A 70% fill rate means three in ten available slots went unused; if the historical baseline for this category is 82%, that gap is what triggers the dynamic-pricing and promotion intervention.

Match-quality-adjusted GMV. Suppose the marketplace closes three transactions this week:

$\text{Raw GMV} = 200 + 150 + 100 = 450$
$\text{MQ-GMV} = 200(1.0) + 150(0.4) + 100(0.8) = 200 + 60 + 80 = 340$
$\frac{\text{MQ-GMV}}{\text{Raw GMV}} = \frac{340}{450} \approx 0.756$

Quality-adjusted value is about 75.6% of raw GMV this week. The gap is driven almost entirely by order B's refund; if raw GMV had been reported alone, the week would have looked $110 stronger than the sustainable value it actually delivered.

Trade-offs & pitfalls

A senior answer names how each metric can be gamed and what it costs to fix that: TTFVM can be improved by loosening the "valid" filter (showing lower-quality matches faster looks good on the metric and bad for buyers), so relevance and availability thresholds must be fixed before optimizing the clock. Fill rate can be inflated by sellers listing narrow, easy-to-fill slots instead of the inventory buyers actually want, so it should be read alongside a demand-coverage check, not in isolation. MQ-GMV's quality multiplier depends on post-transaction signals that arrive with a lag (ratings, refunds), which means the most recent week is always provisional and should not be used alone for a real-time pause decision. The common wrong turn is treating these as three independent KPIs to maximize instead of a system: a promotion that boosts TTFVM by force-ranking a few sellers will quietly erode fill rate for the rest of supply, and that trade-off is the actual senior-level insight the question is testing for.

LATERAL (PostgreSQL) and CROSS APPLY/OUTER APPLY (SQL Server) let a subquery in the FROM/JOIN clause reference columns from an earlier table in the same query, row by row, and return multiple rows or columns per outer row. A plain correlated subquery in the SELECT list can only ever return a single scalar value per outer row; standard SQL doesn't allow a correlated subquery in FROM at all unless it's marked LATERAL. That distinction is exactly what's needed here: "most recent payment" is one row, expressible as a scalar correlated subquery, but "three most recent payments as a list" is inherently multiple rows per user, which only a lateral join can produce.

Approach

SELECT
  u.id AS user_id,
  u.email,
  p_latest.payment_id,
  p_latest.amount,
  p_latest.paid_at,
  p_recent.payments_json
FROM users u
LEFT JOIN LATERAL (
  SELECT id AS payment_id, amount, paid_at
  FROM payments p
  WHERE p.user_id = u.id
  ORDER BY p.paid_at DESC
  LIMIT 1
) p_latest ON true
LEFT JOIN LATERAL (
  SELECT json_agg(t) AS payments_json
  FROM (
    SELECT id AS payment_id, amount, paid_at
    FROM payments p
    WHERE p.user_id = u.id
    ORDER BY p.paid_at DESC
    LIMIT 3
  ) t
) p_recent ON true;

Each LATERAL subquery can see u.id from the table to its left, runs conceptually once per user, and is allowed to return more than one row internally (the inner LIMIT 3) before being folded into a single payments_json value with json_agg. LEFT JOIN LATERAL ... ON true keeps users with zero payments in the result with nulls, rather than dropping them the way an inner JOIN would.

SQL Server equivalent, using OUTER APPLY (no ON clause needed; the correlation lives inside the subquery) and FOR JSON PATH in place of json_agg:

SELECT
  u.id AS user_id,
  u.email,
  p_latest.payment_id,
  p_latest.amount,
  p_latest.paid_at,
  p_recent.payments_json
FROM users u
OUTER APPLY (
  SELECT TOP (1) p.id AS payment_id, p.amount, p.paid_at
  FROM payments p
  WHERE p.user_id = u.id
  ORDER BY p.paid_at DESC
) p_latest
OUTER APPLY (
  SELECT (
    SELECT TOP (3) id AS payment_id, amount, paid_at
    FROM payments p2
    WHERE p2.user_id = u.id
    ORDER BY p2.paid_at DESC
    FOR JSON PATH
  ) AS payments_json
) p_recent;

Key points

• CROSS APPLY/inner LATERAL drops users with no matching rows; OUTER APPLY/LEFT JOIN LATERAL ... ON true keeps them with nulls, which matters here since a brand-new user may have zero payments.
• Any regular (non-lateral) subquery in FROM cannot reference u.id at all in PostgreSQL; that's precisely the restriction LATERAL lifts. (A plain function call in FROM, like generate_series, is a narrow exception and is already allowed to reference earlier tables without the keyword.)
• Index payments(user_id, paid_at DESC) so each lateral subquery's per-user ORDER BY ... LIMIT n is satisfied by an index scan instead of a full sort of that user's payment rows.

What LATERAL gets you that scalar SELECT correlation couldn't

A correlated subquery in the SELECT list must collapse to one value; there is no syntax for "the top 3 rows" there without pre-aggregating into a single scalar (like the json_agg result actually is). What LATERAL adds is the ability to run a genuinely row-producing, multi-row query per outer row and then decide, in the outer query, whether to keep it as multiple joined-out rows or fold it into one scalar (as json_agg does above). Without LATERAL, getting "the 3 most recent payments" per user would require either a full self-join with row-numbering and a GROUP BY/aggregation step to compact it back down, or a client-side loop; LATERAL does both the per-user top-N selection and the correlation in one place.

Worked example

User 1 has four payments (amounts 10, 20, 30, 40 on 2024-01-01, 01-05, 01-10, 01-15); user 2 has one payment (amount 50 on 2024-02-01). Selecting the top-3-most-recent-per-user via an equivalent row-numbering pass gives user 1's latest as the 40/2024-01-15 payment and their 3 most recent as [40, 30, 20]; user 2's latest and only entry is 50/2024-02-01. (The row-selection logic here was verified against SQLite 3.51 using an equivalent ROW_NUMBER() partition, since SQLite has neither LATERAL nor json_agg; the LATERAL/CROSS APPLY/json_agg syntax itself is traced against documented PostgreSQL and SQL Server behavior rather than executed locally.)

Complexity

Each lateral subquery costs O(log k) per user to find the top-k recent payments if payments(user_id, paid_at DESC) is indexed (an index range scan bounded by LIMIT), times O(users) overall, so the whole query is close to linear in the number of users plus a small constant per user for the top-k lookup. Without that index, each lateral subquery degrades to scanning and sorting all of that user's payments, which is the same shape of cost a naive per-row correlated approach would have; the win from LATERAL here is purely expressive (multi-row per-row subqueries in one query), not a performance guarantee by itself.

Edge cases

• Users with zero payments: handled by LEFT JOIN LATERAL ... ON true / OUTER APPLY, which return nulls instead of dropping the user row.
• Fewer than 3 payments for a user: the inner LIMIT 3 naturally returns however many exist; no special-casing needed.
• Ties on paid_at: as with any ORDER BY ... LIMIT pattern, add a deterministic tiebreaker (e.g., ORDER BY paid_at DESC, id DESC) if two payments can share a timestamp and which one lands in "latest" matters.

Trade-offs and pitfalls

LATERAL/CROSS APPLY reads cleanly and lets the planner reason about each per-user lookup as an indexed operation, which is usually both faster and more maintainable than the alternative of a windowed self-join plus a re-aggregation step. The cost is dialect narrowness: MySQL only gained LATERAL support in a fairly recent 8.0.x release, and older engines or some BI (business intelligence) tool SQL layers may not support it at all, in which case the fallback is exactly that self-join-with-ROW_NUMBER()-then-GROUP BY pattern, accepting the extra aggregation step as the price of portability.

Whether a SELECT alias can be reused in GROUP BY or ORDER BY differs by engine: MySQL and PostgreSQL both generally allow it, SQL Server does not permit it in GROUP BY (though it does in ORDER BY); writing portable SQL means not relying on the permissive behavior.

Structured elaboration

-- Works in PostgreSQL and MySQL
SELECT DATE_TRUNC('month', ts) AS month, COUNT(*) FROM events GROUP BY month ORDER BY month;
-- Portable across all three (repeats the expression instead of the alias)
SELECT DATE_TRUNC('month', ts) AS month, COUNT(*)
FROM events GROUP BY DATE_TRUNC('month', ts) ORDER BY DATE_TRUNC('month', ts);

The reason engines differ comes down to when aliases are considered "in scope": logically, GROUP BY is evaluated before the SELECT list assigns aliases, so strictly by the standard SQL execution order, the alias shouldn't exist yet when GROUP BY runs. PostgreSQL and MySQL extend the standard to allow it anyway as a convenience; SQL Server follows the stricter interpretation for GROUP BY.

Worked example

A query grouping by a computed month expression will run unmodified on PostgreSQL and MySQL using the alias, but needs the full expression repeated in GROUP BY to run on SQL Server without a syntax error.

Trade-offs and pitfalls

If the SQL only ever runs on one known engine, use whichever form that engine's team finds most readable. If the query needs to be portable (a shared library, a multi-engine analytics tool, or simply not knowing the eventual target), always repeat the expression rather than relying on alias reuse, since that form works everywhere without exception.

Direct answer

One practical way to detect early product-market fit signals is to look at 30-day retention within acquisition-week cohorts: if a meaningful and growing share of each new cohort is still active a month later, and that share holds up or improves as more cohorts are observed, that is a reasonable early heuristic that the product is delivering repeatable value rather than a one-time novelty.

Structured elaboration

The cohort dimension to use is acquisition week, because it lets you compare successive groups of new users on equal footing (same amount of elapsed time since joining) rather than comparing an aggregate metric that mixes users at very different points in their lifecycle. The metric to pair with it is either 30-day retention or the percentage of a cohort completing the product's core action at least once in a defined follow-up window, whichever better reflects genuine repeat value for that specific product.

A simple threshold heuristic: if 30-day retention for successive weekly cohorts is trending upward, or at minimum holding flat above a level the team considers meaningfully better than a typical unengaged baseline for the category, that is treated as an early positive signal. The threshold itself is necessarily product-specific (a reasonable bar for a daily habit product looks nothing like a reasonable bar for an infrequently-used utility), so a team usually calibrates it against comparable products in the same category rather than a universal number.

Worked example

Suppose a new note-taking app tracks 30-day retention for its first six weekly signup cohorts: 8%, 11%, 14%, 13%, 17%, and 19%. Even though each individual number is modest in isolation, the upward trend across six consecutive cohorts, rather than a flat or declining line, is itself informative: it suggests something about the product or its onboarding is genuinely improving cohort quality over time, which is a stronger early signal than any single cohort's absolute retention number. By contrast, six cohorts showing 15%, 12%, 16%, 11%, 14%, 13%, hovering with no clear trend, would be a weaker signal even at a similar average level, since it looks more like noise around a stable (and possibly weak) baseline than evidence of improving fit.

Trade-offs and pitfalls

Early cohorts are small by definition, so a trend across only a handful of weekly cohorts can be noisy; treating six data points as a confirmed trend rather than a suggestive early read risks over-claiming certainty the sample size does not support. It is also easy to conflate a genuinely improving product with an improving ACQUISITION mix (later cohorts skewing toward higher-intent users because of a change in where signups are coming from), so a careful read checks whether the acquisition channel mix has stayed roughly constant across the cohorts being compared before crediting the product itself for the trend.

Direct answer. A join adds columns by relating two tables through a key; a set operation stacks or compares whole result sets that already share the same shape, adding no new columns at all. Reaching for the wrong one produces either a result with the wrong number of rows (a join where you meant a set operation) or a result missing the relationship you needed (a set operation where you meant a join).

Structured elaboration. Two cases where a set operation is correct: combining monthly exports of the same report into one longer time series (UNION ALL: same columns, you just want more rows of the same shape), and comparing two snapshots of the same population to find what changed (EXCEPT/INTERSECT: again the same columns, but now you're asking a membership question rather than adding information). Two cases where a join is correct: enriching an orders table with the customer's name from a customers table (you need a NEW column, name, that orders doesn't have), and finding customers who have never placed an order (you're relating two DIFFERENT entities through a key, not stacking or comparing same-shaped rows).

Worked example. Using a join where a set operation was needed: if you wanted "combine this month's revenue rows with last month's revenue rows into one series" and instead wrote a JOIN on date between the two tables, you'd get one row per matching date pair with columns from BOTH months side by side, an entirely different (and probably useless) shape from the simple longer list you actually wanted; a UNION ALL is the one-line correct answer. Using a set operation where a join was needed: if you wanted "customers alongside their order count" and instead tried to UNION customers and orders together, the operation would fail outright (or produce nonsense) the moment the two tables don't have the identical column count and types, because UNION has no concept of "relate these through a key," only "stack these because they're the same shape."

Trade-offs and pitfalls. The duplicate and NULL handling differ sharply between the two: a join can silently multiply rows through an unexpected one-to-many relationship (fan-out), a failure mode that has nothing to do with NULLs at all, and a plain equality join predicate never matches two NULL keys to each other. A set operation instead dedups automatically (for UNION/INTERSECT/EXCEPT, though not UNION ALL) and, in most engines, treats two NULLs in the same column position as equal for that deduplication, the exact opposite NULL behavior from a join. Recognizing which shape a problem actually has, "I need to add information from elsewhere" versus "I need to stack or compare things that are already the same shape", is what should drive the choice, not which one happens to occur to you first.

Approach: compute daily 30-day rolling NPS per dashboard (window ending each day), then extract the last three non-overlapping 30-day windows per dashboard (e.g., Latest 30d, Prior 30d, Prior2 30d), compute trend = NPS_latest - NPS_prior2 (or slope), and rank dashboards by that trend. Enforce a minimum-sample threshold and optionally compute confidence intervals for low-sample dashboards.

SQL (Postgres-style):

WITH daily AS (
  -- normalize each feedback to a day
  SELECT
    dashboard_id,
    occurred_at::date AS day,
    score
  FROM feedback
),
rolling AS (
  -- 30-day rolling aggregation ending at each day
  SELECT
    dashboard_id,
    day,
    COUNT(*) FILTER (WHERE score BETWEEN 9 AND 10)       AS promoters,
    COUNT(*) FILTER (WHERE score BETWEEN 0 AND 6)        AS detractors,
    COUNT(*)                                             AS responses,
    100.0 * ( (COUNT(*) FILTER (WHERE score BETWEEN 9 AND 10))::float
            - (COUNT(*) FILTER (WHERE score BETWEEN 0 AND 6))::float )
      / NULLIF(COUNT(*),0)                                AS nps
  FROM daily
  GROUP BY dashboard_id, day
),
rolling_30 AS (
  -- compute 30-day rolling sums using RANGE on dates
  SELECT
    dashboard_id,
    day AS window_end,
    SUM(promoters) OVER (PARTITION BY dashboard_id ORDER BY day
                         RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW) AS promoters_30,
    SUM(detractors) OVER (PARTITION BY dashboard_id ORDER BY day
                          RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW) AS detractors_30,
    SUM(responses) OVER (PARTITION BY dashboard_id ORDER BY day
                        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW) AS responses_30
  FROM rolling
),
windows AS (
  -- produce non-overlapping 30-day windows by picking window_end = max(day) per 30-day bucket
  SELECT
    dashboard_id,
    window_end,
    promoters_30, detractors_30, responses_30,
    CASE WHEN responses_30 = 0 THEN NULL
         ELSE 100.0 * (promoters_30 - detractors_30) / responses_30 END AS nps_30
  FROM rolling_30
),
rank_prep AS (
  -- for each dashboard pick the last three distinct 30-day windows (by window_end)
  SELECT
    dashboard_id,
    window_end,
    nps_30,
    ROW_NUMBER() OVER (PARTITION BY dashboard_id ORDER BY window_end DESC) AS rn
  FROM windows
  WHERE window_end IS NOT NULL
),
pivot AS (
  SELECT
    dashboard_id,
    MAX(CASE WHEN rn = 1 THEN nps_30 END) AS nps_latest,
    MAX(CASE WHEN rn = 2 THEN nps_30 END) AS nps_prior,
    MAX(CASE WHEN rn = 3 THEN nps_30 END) AS nps_prior2,
    MAX(CASE WHEN rn = 1 THEN (SELECT responses_30 FROM windows w2 WHERE w2.dashboard_id = rp.dashboard_id AND w2.window_end = (SELECT window_end FROM rank_prep rp2 WHERE rp2.dashboard_id = rp.dashboard_id AND rp2.rn=1)) END) AS resp_latest
  FROM rank_prep rp
  GROUP BY dashboard_id
)
SELECT
  dashboard_id,
  nps_prior2, nps_prior, nps_latest,
  -- simple trend: change from prior2 to latest
  (nps_latest - nps_prior2) AS trend,
  resp_latest,
  -- mark low-sample dashboards
  CASE WHEN resp_latest < 30 THEN 'LOW_SAMPLE' ELSE 'OK' END AS sample_flag
FROM pivot
ORDER BY trend DESC NULLS LAST;

Notes / Reasoning:

• NPS = %Promoters (9–10) − %Detractors (0–6). Neutral (7–8) ignored.
• Rolling_30 uses a 30-day trailing window; you can change to calendar windows by bucketing dates (e.g., date_trunc('day', occurred_at) then generate non-overlapping intervals).
• Ranking: I use difference between latest and prior2 to measure trend over three windows; you can compute linear regression slope for more robust trend detection.
• Low-sample handling: set a minimum responses threshold (e.g., 30). For dashboards below threshold, show LOW_SAMPLE, suppress ranking, or surface confidence intervals (Wilson score) so stakeholders see uncertainty.
• Additional improvements: de-duplicate users if multiple feedbacks per user should be unique (use latest per user), compute confidence intervals for NPS, smooth with EWMA, or require both absolute NPS and trend to act.

Direct answer. When event counts in a downstream table suddenly double, the fastest way to localize the bug is to check the SAME data at each stage of the pipeline (staging versus production, before versus after each transformation) to find the exact stage where the count doubles, rather than guessing at the cause from the final table alone.

Structured elaboration. Start at the earliest available checkpoint (raw ingestion) and work forward: if the raw ingested event count is normal but a staging table downstream of the first transformation already shows double, the bug lives in that specific transformation step (commonly a join that's fanning out rows because of an unexpectedly non-unique join key, or a retry mechanism re-processing the same batch). If ingestion itself is already double, the bug is upstream, likely a client or server retry sending the same event twice, or a message-queue redelivery without idempotency. Metadata and version checks help narrow the window: check the job-run history for the transformation pipeline for a recent code deploy or configuration change, and check whether the doubling started exactly at a specific run ID or timestamp, which usually pins down the responsible deploy precisely.

Worked example. A downstream analytics table's event count doubles overnight. Checking the immediately-upstream staging table shows it's still at the normal count, isolating the bug to the transformation JOB that populates the downstream table specifically. Checking that job's recent deploy history shows a change shipped the same day that altered a join condition; the new join key wasn't unique per event, causing each event to fan out against two matching rows in a lookup table instead of one, exactly doubling the output. Reverting that specific join-condition change (not the whole deploy) restores normal counts.

Trade-offs and pitfalls. The value of checking staging versus production data specifically, rather than only comparing final output to a prior day's final output, is that it converts 'the number is wrong' into 'the number becomes wrong at THIS specific stage,' which is a vastly smaller, faster search than debugging an entire multi-stage pipeline from the final output backward with no isolation.

Row-level security (RLS) must be tested, auditable, and aligned with workspace permissions to ensure users see only permitted data. Best practices:

• Validate roles logically and functionally:

  • Define roles in Power BI Desktop with clear DAX filters and descriptive names.
  • Use “View as role” in Power BI Desktop to test sample users and combinations.
  • Publish to a test workspace and use the Power BI Service “Test as role” (App workspaces > dataset > Security) to verify behavior for different user UPNs and groups.

• Monitor access and permissions:

  • Apply least-privilege: assign dataset permissions separately from workspace roles (Contributor vs. Member vs. Admin).
  • Use Azure AD groups for role membership (avoid individual UPNs) to ease management.
  • Ensure the report/app uses datasets with RLS enabled and that app consumers are only Users/Viewers.

• Integrate auditing and logs for compliance:

  • Enable Power BI audit logs in Microsoft Purview / Office 365 Security & Compliance center.
  • Collect DatasetEvents, ReportViewed, ExportedReport, and RLS-related events; correlate with Azure AD sign-ins and Conditional Access logs.
  • Store logs in a SIEM or Log Analytics workspace for retention, alerting (failed access attempts), and periodic review.

• Testing and governance practices:

  • Create test accounts representing each persona (internal, external contractor, manager).
  • Include negative tests (ensure restricted rows are not visible via filters, bookmarks, export, or API).
  • Validate against sources: ensure source query-level filters or source security are consistent with RLS.
  • Document role definitions, DAX filters, and owner approvals in change control.

Pre-release checklist:

1. Confirm role definitions and DAX filters are documented and peer-reviewed.
2. Run “View as role” in Desktop and “Test as role” in Service for every persona.
3. Test via published app with User/Viewer permissions and test accounts.
4. Verify workspace permissions: dataset owners, admins, contributors properly assigned.
5. Confirm usage of Azure AD groups for role assignment.
6. Perform negative tests: export, API, drillthrough, bookmarks, paginated reports.
7. Ensure audit logs are enabled and ingested to SIEM; run sample queries to confirm events appear.
8. Sign-off from data owner and security/compliance team; record approvals.

This approach ensures RLS is functionally correct, governed, auditable, and maintainable.

Approach

I would automate the steps that are repetitive and easy to validate first: import the CSV, clean the headers, normalize the data types, refresh summary sheets, then hand off PDF export to Power Automate or a similar wrapper if I am using Office Scripts. That is because Office Scripts is great for workbook logic, but PDF distribution is usually better handled outside the script.

Robustness

• Check that the expected columns exist before doing anything else.
• Trim header names, remove extra spaces, and standardize case.
• Reject or log rows with missing required fields.
• Parse dates carefully and flag values that do not convert.
• Write problems to an error sheet instead of failing silently.
• Refresh pivots or formulas only after the raw data passes basic validation.

Sample Office Script pattern

function main(workbook: ExcelScript.Workbook) {
  const raw = workbook.getWorksheet('Raw');
  const errors = workbook.getWorksheet('Errors') || workbook.addWorksheet('Errors');

  // Read used range and validate headers
  const range = raw.getUsedRange();
  if (!range) throw new Error('Raw sheet is empty');

  const values = range.getValues();
  const headers = values[0].map(h => String(h).trim().replace(/\s+/g, ' '));

  // Example: ensure required columns exist
  const required = ['Date', 'Amount', 'Region'];
  required.forEach(col => {
    if (!headers.includes(col)) throw new Error('Missing column: ' + col);
  });

  // Clean summary sheets after validation
  workbook.refreshAllDataConnections();
  workbook.getWorksheets().forEach(ws => {
    if (ws.getName().startsWith('Summary')) {
      ws.getUsedRange()?.getFormat().autofitColumns();
    }
  });
}

That structure makes the automation easier to debug and safer when the source CSV is messy.