Interview Q&A + Day-Of

Lead with the principle: optimize for trust and my own time as a team of one. Then the sequence: listen (stakeholder discovery — what do founders re-check in spreadsheets?) → centralize (warehouse + managed ingestion for top sources) → one trusted metric end-to-end by day 30 (likely revenue), tested and documented → expand the trusted core (30–60) with reconciliation and alerting → durability (60–90): docs, self-serve, a backlog. Name what I'd deliberately defer (streaming, ML) and the trigger to revisit. See chapter 08.

Tie every request to a decision it drives; requests with no decision get parked. Rank by value × reversibility, and favor the cheap version first (offer a 1-day version of a 3-week ask). Be willing to say "not yet" with a reason. Make the backlog visible so prioritization is transparent, not personal.

Buy the undifferentiated heavy lifting (connectors, warehouse, BI, scheduling); build what's specific to the business (metering models, metric definitions, reconciliation). Managed over self-hosted while I'm one person. Avoid resume-driven architecture — match the stack to the stage.

Ask what decision needs sub-daily latency — often the real need is "fresh by morning," not real-time. If daily numbers aren't trusted yet, real-time just makes us wrong faster. Propose nailing batch first, offer micro-batch as a middle ground, and reserve true streaming for a decision that genuinely depends on sub-minute data. Constructive "not yet."

Trust: leadership stops maintaining shadow spreadsheets. Decisions enabled by data that weren't before. Time-to-answer dropping for common questions. And a leading indicator of reliability: I find data issues before stakeholders do. Dashboards are outputs; trusted decisions are the outcome.

WHERE filters rows before grouping; HAVING filters groups after aggregation. Push filters into WHERE when possible — correct and faster. You can't reference an aggregate in WHERE because it runs before GROUP BY (logical query order).

All assign ordered positions within a partition. ROW_NUMBER is always unique (1,2,3,4 — arbitrary tiebreak). RANK leaves gaps after ties (1,2,2,4). DENSE_RANK has no gaps (1,2,2,3). Use ROW_NUMBER for dedup/latest-per-group, DENSE_RANK for "Nth distinct value."

A filter on the right table in WHERE drops the NULL rows that LEFT JOIN produced for non-matches, effectively making it inner. Fix: move the condition into the ON clause so unmatched left rows survive (chapter 02).

If the subquery returns even one NULL, NOT IN returns no rows, because x <> NULL evaluates to unknown for every row. Use NOT EXISTS or a LEFT JOIN ... WHERE right IS NULL anti-join, which are NULL-safe.

ROW_NUMBER() OVER (PARTITION BY key ORDER BY updated_at DESC, id DESC) then filter = 1 — with a deterministic tiebreaker so it's stable. Or QUALIFY in Snowflake/BigQuery. Avoid MAX + self-join (breaks on ties, slower).

Subtract a row number (ordered by date) from the date. For consecutive dates both increase by 1, so the difference is constant within an unbroken run — group by that constant to collapse each streak. Then aggregate for start/end/length.

Aggregate the one-to-many side to the join grain first (in a CTE), then join. Or declare the result grain and check row counts before/after the join. Reaching for DISTINCT to fix it is a smell — it hides the real grain bug.

You pay for bytes scanned: select only needed columns (no SELECT *), filter on the partition column, partition by date and cluster by a common filter key (e.g. customer), and pre-aggregate hot rollups. Avoid functions on the partition column in WHERE.

A pipeline is idempotent if re-running it yields the same correct result. It matters because jobs fail and get retried/backfilled — without idempotency, retries duplicate or corrupt data. Achieve it with delete-insert by partition, MERGE on a key, and deterministic transforms.

ELT for modern cloud warehouses: land raw, transform in-warehouse with versioned SQL/dbt. Preserves raw to re-transform without re-extracting, and keeps logic in one tested place. Pre-transform (ETL-style) only for PII you can't land or volumes you must filter for cost.

Process only rows above a high-water mark (max updated_at), with a small lookback window to catch late/updated rows. The classic bug is keying on created_at when rows can be updated later — you miss updates. Use a column that moves on every change, plus a unique key for upsert.

Aggregate by event time (not ingestion time) for correctness, reprocess a lookback window so late events fold into the right period, and document a finality cutoff (e.g. data > 7 days old is final) to bound recomputation. Track ingestion time too for debugging.

Land raw as semi-structured (JSON/VARIANT) so ingestion survives new fields; promote fields to typed columns deliberately in staging. Additive changes are safe; for breaking ones, a schema test fails loudly at ingest as a tripwire, and ideally a data contract with the producer (chapters 04/05).

No — that's ops overhead for one person and a handful of pipelines. Start with a managed scheduler (dbt Cloud) or Dagster for its asset model and local dev. Move to heavier orchestration only when DAG complexity demands it. Match tooling to stage.

Reproduce and quantify (which number, how wrong, since when). Check whether it's the data or the definition — half are definition mismatches, cheapest to rule out first. Then walk the pipeline top-down via lineage: source freshness/volume → staging dedup → join fan-out → metric logic → BI filter. Reconcile to the billing source of truth. Communicate throughout, fix idempotently, and add a test so it can't recur silently.

Freshness, volume, completeness, uniqueness, validity, consistency, accuracy. For a solo hire, freshness + volume + key-uniqueness on the few trusted metrics is most of the value for least effort (chapter 05).

At the boundaries, not everywhere. Source/ingest: schema + freshness. Staging: dedup-key uniqueness, not-null, valid enums. Marts: grain uniqueness, FK to dimensions, business invariants, reconciliation. Shift-left: catch issues as early as possible.

An agreement with producers on schema/types/semantics/SLA so a backend deploy doesn't silently break analytics. Formal enforcement (schema tests, dbt contracts) is ideal; pragmatically at a startup it's a schema test that pings me on change plus a lightweight "tell me before you rename a field." Show you know both ideal and pragmatic.

Freshness check (max load time within SLA) and volume check (today's rows within a band of the trailing average, accounting for seasonality). These two catch most real-world silent breaks for minimal effort. Route Tier-1 failures to a page, softer ones to a channel.

What one row represents. Everything — keys, joins, aggregations, tests — follows from it. Pick the finest grain you'll need for a fact (you can roll up, not down). A mixed-grain table silently breaks every aggregate.

A new dimension row per change, with valid-from/valid-to and an is_current flag, so you can join facts to the attribute value that was active at the time. Use it when point-in-time truth matters (bill at the plan tier active when usage occurred). Don't SCD2 everything — it bloats dims and complicates joins; reserve it for attributes whose history matters. dbt snapshots implement it.

Both: model the core as a star (conformed dimensions, clean facts, reusable logic) and materialize wide OBT marts on top for specific dashboards (ergonomics, no fan-out for analysts). Pick per consumer, not as a religion.

Normalized for OLTP/source systems (writes, integrity, no duplication). Denormalized for analytics (read speed, simplicity) — columnar warehouses make wide tables cheap to scan. Denormalize marts but keep conformed dimensions so logic isn't duplicated.

Keep the append-only event log as source of truth (you can rebuild state from events, never the reverse) and materialize current-state and daily-snapshot tables on top for fast querying. That's the event-sourcing intuition.

One-way doors: the grain of a core fact, whether a key dimension is SCD2, identity/key strategy, what raw data you retain. Cheap to change: derived columns, new marts, non-breaking renames, a one-off OBT. Spend judgement proportional to reversibility — capture finest grain and full history on the one-way doors even if not needed yet.

A transaction fact at instance-hour grain (gpu_seconds, cost) with keys to dim_customer, dim_instance_type, dim_region, dim_date. SCD2 on plan/pricing so usage bills at the rate active at the time. Keep the telemetry event log as truth; materialize daily usage and a metering↔billing reconciliation. Test grain uniqueness, FK integrity, and metered-vs-invoiced consistency (chapters 06/07).

Metering errors are revenue errors. Risks: duplicate heartbeats (at-least-once → over-bill, fix with dedup), missed heartbeats/gaps (define the billing rule), partial-hour rounding (small rules move big money at scale), and clock skew/late data (aggregate by event time with lookback). Make all rules explicit and testable.

Rented GPU-hours ÷ available GPU-hours per day, using a generated date spine + LEFT JOIN so zero-rental days still appear. The subtle part is defining the denominator (include machines in maintenance? offline hosts? reserved-idle?) — the choice changes the number, so pick one and document it. Segment by GPU type/region to find stranded supply.

Use STAR and pick a story where you set the scope (no one handed you a spec) — this is the founding-hire signal. Emphasize: how you found the real problem via stakeholder conversations, how you sequenced, a tradeoff you made deliberately, and the measurable outcome (a trusted number, a decision enabled). Show comfort operating without a manager defining the work.

Pick a case where you said "not yet" or offered the cheap version, tied to a decision, and it led to a better outcome — not stubbornness. Shows judgement and that you won't just build whatever's asked into an unmaintainable pile. Land it on the result and the relationship preserved.