Building the Data Stack From Scratch

The mandate

Read the posting literally: "establish end-to-end data infrastructure — decide where data sourcing, storage, and querying happens" and "consolidate data access across financial, product, and growth metrics to eliminate duplicate systems." That is not a request for a dashboard. It is a request to own the entire data platform, from the point where a Stripe webhook fires to the point where the VP of Business Operations reads a number and trusts it.

Today that platform does not exist. What exists — at a profitable, roughly-50-person, fast-moving company — is the usual pre-first-hire sprawl: finance has a spreadsheet pulling from the Stripe dashboard, growth has PostHog open in a browser tab, an engineer wrote a one-off script that queries the production Postgres for a board deck, and someone on the token side keeps a Dune dashboard bookmarked. Every one of those is a "system," and several of them disagree about basic facts like how many paying users there are. Your first act of value is not building something new — it is making all of them reconcile to one warehouse and one set of definitions.

Two company-specific constraints shape everything that follows. First, this is a privacy-first company, so your default is self-hosted, OSS tooling that keeps data inside the company's own trust boundary — you should not be shipping product data to a dozen third-party SaaS processors, and you'll want to be the person who says so unprompted (Chapter 02). Second, the product iterates fast, so you build for change: metrics defined once, in code, so a schema churn doesn't silently break the number the CEO quotes on a podcast.

The sources you're consolidating

Before choosing tools, inventory what's actually generating data. Here is the likely source map at the company, and — critically — which sources are content-free (safe to warehouse in full) versus which touch the privacy boundary and must be handled as metadata only. This table is also a great thing to sketch on a whiteboard in the interview: it proves you understand both the business and the constraint.

Note the two genuinely different data-gravity problems here. Stripe, Postgres, PostHog, and Zendesk are standard SaaS/DB sources with off-the-shelf connectors. The API-gateway logs (high-volume, emitted by Rust and Go services) and the on-chain data (a blockchain, not a database) are the two that need custom thought — they're where a naive "just buy Fivetran" answer falls apart.

The reference stack

Here is a defensible end-to-end design. The one-liner to memorize, because it's the whole chapter in a sentence:

Layer by layer, with the choices and the reasoning a senior candidate should be ready to defend:

Warehouse

Start on the existing Postgres — a read replica or a separate analytics schema. At the company's scale (single-digit-millions of users, with API request volume well within a single Postgres's reach), Postgres with good indexing will carry you for a long time and costs nothing new to stand up. When query volume or transformation cost outgrows it, the low-ops moves are BigQuery (serverless, cheap at this scale, trivial to hand out read access) or DuckDB + MotherDuck (astonishingly cheap, self-hostable, great for a small team). Snowflake is overkill early — its cost and ops surface are aimed at a data-team-of-many; adopting it as the first hire is a classic over-engineering tell.

Ingestion

For the standard sources use Airbyte (OSS, self-hosted) — the connectors for Stripe, PostHog, and Zendesk are mature, and self-hosting keeps that data inside the company's boundary, which is the culturally-aligned default here. Fivetran is the managed alternative if you'd rather trade dollars for zero connector maintenance, but sending product data to a third-party processor cuts against the privacy posture and I'd flag that trade-off rather than make it silently. The two custom pieces: a lightweight loader for the API-gateway logs (structured-log ingestion — the Rust/Go services emit JSON lines; ship them to the warehouse via a log pipeline or a small batch job), and an on-chain loader pulling token/credit activity from Dune or Flipside APIs (or an L2 indexer) on a schedule.

Transformation

dbt, non-negotiable. It's where consolidation actually happens: raw sources in, conformed financial / product / growth marts out, with tests and documentation. This is also where metric definitions become code (next section).

BI & experimentation

Self-hosted Metabase for dashboards and self-serve — fast, OSS, easy for non-analysts to slice, and it keeps the BI layer in-boundary. Hex for notebook-style deep dives and narrative analysis. For experiments, PostHog is already chosen (feature flags + A/B) so lean on it now; graduate to GrowthBook (OSS, warehouse-native) if experimentation becomes a core workflow, per Chapter 05.

The whole thing as a data-flow diagram:

  SOURCES                    INGESTION              WAREHOUSE            TRANSFORM              CONSUMPTION
  ---------------------      -----------------      -------------       -----------------      ---------------------
  Stripe (billing)     ─┐                                              ┌ stg_stripe          ┌ Metabase (dashboards,
  Postgres (app DB)    ─┤    Airbyte (OSS,     ─┐                      │ stg_app_db          │   self-serve KPIs)
  PostHog (events)     ─┤    self-hosted)       ├─►  Postgres  ──► dbt ┤ stg_posthog    ──►  ┤
  Zendesk (support)    ─┘                        │   (→ BigQuery /      │                     │ Hex (notebooks,
                                                 │    DuckDB later)     ├ fct_financial       │   deep-dive analysis)
  API-gateway logs     ─── custom log loader  ──►│                      ├ fct_product     ──► │
  (Rust/Go services)                             │                      ├ fct_growth          │ PostHog / GrowthBook
                                                 │                      └ metrics (semantic   │   (experiment tracking)
  On-chain tokens      ─── Dune / Flipside    ──►┘                        layer / marts)      └
  (Ethereum L2)            API loader

  Everything self-hostable ──► data stays inside the company's trust boundary

dbt modeling for consolidation

Consolidation is not "put everything in one database" — it's "define each metric once, in one place, so finance's MRR and growth's MRR are literally the same SQL." dbt gives you a staging → marts layout where raw sources are cleaned in staging, then conformed into marts at a shared grain. The highest-leverage mart for a freemium + API + token business is a customer-day fact that unions billing and usage, because it lets you answer revenue, engagement, and retention questions from one table.

-- One row per paying customer per day: the conformed grain that
-- billing, product, and growth all agree to reconcile against.
-- Sources are already content-free; nothing here touches a prompt.

with billing as (
    select
        customer_id,
        date_day,
        plan_tier,                        -- free / pro / pro_plus / max
        mrr_usd,                          -- normalized to daily-active MRR
        is_active_subscription
    from {{ ref('stg_stripe__subscriptions_daily') }}
),

usage as (                                -- from API-gateway logs, metadata only
    select
        customer_id,
        date_day,
        count(*)                as api_requests,
        sum(tokens_in + tokens_out) as tokens_processed,
        count(distinct model_id)    as models_used,
        avg(latency_ms)             as avg_latency_ms
    from {{ ref('stg_gateway__request_events') }}
    group by 1, 2
),

staking as (                             -- on-chain, cohort-level, consented link only
    select
        customer_id,                     -- populated ONLY where user consented to link wallet
        date_day,
        sum(token_staked)        as token_staked,
        sum(credit_consumed_usd) as credit_consumed_usd
    from {{ ref('stg_onchain__consented_staking_daily') }}
    group by 1, 2
)

select
    b.customer_id,
    b.date_day,
    b.plan_tier,
    b.mrr_usd,
    b.is_active_subscription,
    coalesce(u.api_requests, 0)        as api_requests,
    coalesce(u.tokens_processed, 0)    as tokens_processed,
    coalesce(u.models_used, 0)         as models_used,
    s.token_staked,
    s.credit_consumed_usd
from billing b
left join usage   u using (customer_id, date_day)
left join staking s using (customer_id, date_day)

On top of conformed marts sits the piece that actually kills duplicate systems: a semantic / metrics layer where each business metric is defined once as code. When "active paying subscriber," "MRR," or "NRR" lives in exactly one place, every dashboard and every ad-hoc query inherits the same definition — and a rapid product change means editing one model, not hunting through fourteen dashboards.

# Single source of truth for headline metrics (dbt Semantic Layer, MetricFlow
# spec, dbt 1.6 and later). Change a definition here and every dashboard
# downstream follows: the "eliminate duplicate systems" mandate made concrete.
# The older calculation_method / expression spec came from the dbt_metrics
# package and no longer parses. MetricFlow also needs a metricflow_time_spine model.
semantic_models:
  - name: customer_day
    model: ref('fct_customer_day')
    primary_entity: customer_day
    defaults:
      agg_time_dimension: date_day
    entities:
      - name: customer
        type: foreign
        expr: customer_id
    dimensions:
      - name: date_day
        type: time
        type_params:
          time_granularity: day
      - name: plan_tier
        type: categorical
      - name: is_active_subscription
        type: categorical
      - name: has_api_usage
        type: categorical
        expr: api_requests > 0
    measures:
      - name: mrr_usd
        agg: sum
      - name: paying_customers
        agg: count_distinct
        expr: customer_id

metrics:
  - name: mrr
    label: Monthly Recurring Revenue
    type: simple
    type_params:
      measure: mrr_usd
    filter: |
      {{ Dimension('customer__is_active_subscription') }} = true

  - name: weekly_active_paying_subscribers
    label: Weekly Active Paying Subscribers   # candidate north star (ch. 04)
    type: simple
    type_params:
      measure: paying_customers
    filter: |
      {{ Dimension('customer__plan_tier') }} != 'free'
      and {{ Dimension('customer__has_api_usage') }} = true

Build vs buy, and sequencing

A senior first hire is judged as much on what they refuse to build as on what they build. The bias for a small, privacy-conscious, fast-moving team is: buy/adopt the commodity, build only the two things that are genuinely company-shaped.

The trap to name explicitly is over-engineering. The "full modern data stack" — Snowflake + Fivetran + a real-time streaming layer + a data catalog + a separate reverse-ETL tool — over-serves a roughly-50-person company and burns your first quarter on plumbing nobody asked for. The posting itself signals the antidote: it wants someone "comfortable iterating fast rather than perfecting," which is a direct instruction not to gold-plate the infrastructure.

The 30-60-90

The single most likely infrastructure question in the loop is "what would your first 90 days look like?" Here is a plan calibrated to the first-data-hire reality — and be honest about the timeline: at a company with no data foundation, you will not produce a meaningful strategic insight for roughly three months, because the first three months are spent making the data trustworthy. Saying that out loud is a credibility signal, not a weakness.

This is the strategy-and-execution 30-60-90 for the platform. Chapter 10 carries a complementary 30-60-90 framed as the pitch you actually deliver in the room, plus the questions to ask and how to close — read the two together and you'll have both the substance and the delivery. Next, the metrics that platform exists to serve: Chapter 04 · Metrics That Matter.

✓ Check yourself

Answer from memory before opening each one.

1. Finance quotes one MRR figure and growth quotes another. A colleague proposes a governance meeting. What do you build instead, and why does it end the argument?

   Show answer

   Define MRR once in the semantic layer on top of the dbt marts, and have every dashboard read it from there. Two people cannot disagree about a number when only one definition exists, and a change means editing one model. That is a build answer, not a process answer. See dbt modeling for consolidation.

2. Which two sources need custom loaders, and why does "just buy a connector" fail for them?

   Show answer

   The API-gateway logs and the on-chain data. The logs are high-volume output from company services with a company-specific schema, and a blockchain is not a database or a SaaS source. Write a small gateway loader and a thin loader over Dune or Flipside, and do not run your own indexer yet. See the sources and build versus buy.

3. You build the customer-day mart by left-joining usage and staking onto billing. The row count after the joins is higher than the billing row count. What does that mean and what do you check?

   Show answer

   The mart should have exactly one row per billing customer-day, so more rows means a join fanned out. A source CTE is not at the customer-day grain: usage or staking was not grouped, or billing has duplicate customer-days. Add a unique test on customer and day to each input and to the mart. See the customer-day mart.

4. An executive offers budget for Snowflake, Fivetran, a streaming layer and a data catalog in month one. Do you accept?

   Show answer

   No. Run Postgres, self-hosted Airbyte, dbt and Metabase, the smallest stack that reconciles the numbers and ships a dashboard, and add a tool only when a measured bottleneck earns it. Self-hosting also keeps data inside the trust boundary. Move to BigQuery or DuckDB when volume outgrows Postgres. See build versus buy.

5. A teammate wants to load raw Zendesk ticket bodies into the warehouse for sentiment analysis. The app database and gateway logs are fine to warehouse in full. What separates the cases?

   Show answer

   The app database and gateway logs never held prompt content, so the warehouse is born clean. Tickets contain text users wrote, so treat them as sensitive: load metadata and tags, not raw bodies. See the sources you are consolidating.

6. Six weeks in, you have a careful dbt project and no dashboards. Why is that a failure even if the models are right?

   Show answer

   Leadership has seen nothing, so trust has not been earned for the foundation work. Ship a visible dashboard on core KPIs straight from source in days 31 to 60, then consolidate in the model: dashboard first, model second. See the 30-60-90.

Takeaway

• The deliverable is consolidation, one warehouse, one metrics layer and one source of truth; the specific tools are swappable detail.
• Default to self-hosted open source so data stays inside the trust boundary; here that is a values choice that also happens to be cheap.
• Start on the existing Postgres and move to BigQuery or DuckDB only when volume forces it.
• Gateway logs and on-chain data need small custom loaders; Stripe, Postgres, PostHog and Zendesk use off-the-shelf connectors.
• Define each metric once in code in the semantic layer, so numbers agree by structure and not by policy.
• More than one tool per layer in month one is over-engineering; earn each addition with a real bottleneck.
• Ship a visible dashboard before the model is perfect, and say honestly that a strategic insight takes about three months.

The platform exists to serve metrics, so next is which ones to track across subscription, API and token revenue. → Metrics That Matter

Further reading

• How we structure our dbt projects — dbt Labs, accessed 2026. The staging, intermediate and marts layout this chapter uses.
• About MetricFlow — dbt Labs, accessed 2026. How the semantic layer defines metrics once and serves them to every tool.
• Semantic models — dbt Labs, accessed 2026. Entities, dimensions and measures, the objects a metric is built from.
• Add data tests to your DAG — dbt Labs, accessed 2026. The unique, not_null and relationships tests that catch a fan-out.
• Airbyte documentation — Airbyte, accessed 2026. Connectors and self-hosted deployment for the standard sources.
• Metabase documentation — Metabase, accessed 2026. The self-hosted BI layer, including permissions for self-serve access.