Meta Data Engineer Interview Preparation Guide - Junior Level

Direct answer

When expected row count legitimately varies by day (a table that only receives sales rows on business days, for instance), the completeness metric needs a baseline that's aware of the CALENDAR, not just a flat trailing average, otherwise every weekend looks like a catastrophic drop and every real weekend anomaly gets lost in that noise.

Structured elaboration

• The formula: completeness = actual_row_count / expected_row_count, where expected_row_count is NOT a single fixed number but a lookup based on the specific day's calendar classification (business day, weekend, holiday), computed from the trailing historical median for that SAME classification, not the overall trailing median across all days.
• Required inputs: a calendar classification for each date (business day, weekend, or holiday, ideally pulling from an authoritative business-calendar source rather than a hardcoded weekday check, since holidays don't follow a simple weekday pattern), and a rolling window of historical row counts segmented by that classification (for example, the trailing 8 business-day counts to compute the business-day baseline).
• Computing it in production: for each new day, first classify it, then look up the appropriate historical baseline for that classification, then compute the ratio and compare against a tolerance band.

Worked example

In SQL, roughly:

WITH classified AS (
  SELECT date, row_count,
    CASE WHEN date IN (SELECT holiday_date FROM company_holidays) THEN 'holiday'
         WHEN EXTRACT(DOW FROM date) IN (0,6) THEN 'weekend'
         ELSE 'business_day' END AS day_type
  FROM daily_row_counts
),
baseline AS (
  SELECT day_type,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY row_count) AS expected_count
  FROM classified
  WHERE date >= CURRENT_DATE - INTERVAL '56 days' AND date < CURRENT_DATE
  GROUP BY day_type
)
SELECT c.date, c.day_type, c.row_count, b.expected_count,
  c.row_count::FLOAT / NULLIF(b.expected_count, 0) AS completeness_ratio
FROM classified c JOIN baseline b ON c.day_type = b.day_type
WHERE c.date = CURRENT_DATE;

If today is a business day with a historical business-day median of 50,000 and today's actual count is 47,500, the completeness ratio is 0.95, comfortably within a normal tolerance band. The same 47,500 on a WEEKEND, where the historical weekend median might be 8,000, would compute a wildly different (and correctly alarming) ratio of nearly 6x, immediately flagging that something unusual happened rather than that number simply representing a normal weekend.

Trade-offs and pitfalls

Segmenting the baseline by calendar classification is what prevents the two most common false-alarm patterns: treating every weekend as a completeness incident, and, just as importantly, treating a genuinely anomalous weekend (way more or fewer rows than a normal weekend) as unremarkable because it's being compared against an all-days average that includes much busier weekdays. The pitfall in implementation is a naive weekday check (day-of-week 0 or 6) that misses HOLIDAYS falling on a weekday, an authoritative holiday calendar as the classification source, not just a day-of-week rule, is necessary to avoid a holiday triggering a false "row count is way down" alert on what is actually a completely expected low-volume day.

Direct answer

Tailoring the same finding for engineers, operations managers, and executives means changing the UNIT of the number and the implied next action for each, while keeping the underlying fact identical across all three so nobody hears a different "truth."

Structured elaboration

For a 5% quarter-over-quarter increase in churn:

• Backend engineers: message in system/data terms - "Churn ticked up 5% QoQ; before we treat this as a product signal, we should confirm it's not a measurement artifact from the recent event-schema migration." Supporting data: event volume and schema-version breakdown by cohort. Visualization: a time series with the migration date marked. Action: verify instrumentation before anything else.
• Operations managers: message in workload terms - "Churn is up 5%, which means roughly 15% more cancellation-related support volume next month; here's the staffing impact." Supporting data: historical correlation between churn rate and support ticket volume. Visualization: a simple bar comparing current vs. projected ticket load. Action: confirm staffing coverage for the surge.
• Executive leadership: message in revenue terms - "Churn rose 5% this quarter, which costs roughly $X in annualized revenue if it persists; we have one credible hypothesis and a plan to test it in four weeks." Supporting data: revenue-at-risk calculation. Visualization: a single trend line with the dollar figure annotated. Action: approve the four-week investigation.
  Two stakeholders wanting different EMPHASIS in the same room (say, sales wants pricing implications, product wants feature usage) can usually be reconciled with one shared headline plus two supporting bullets, one per concern, rather than two separate narratives; splitting the story into competing versions makes people trust neither.

Worked example

Shared headline across all three audiences: "Churn rose from 4.0% to 4.2% this quarter." What changes is only the SECOND sentence and the visualization: engineers get a data-integrity check, operations gets a staffing forecast, executives get a dollar figure and an ask. Organizational culture and values (how blunt vs. diplomatic the framing should be, how much uncertainty to surface up front) shape the TONE of each version but should never change the underlying number or headline.

Trade-offs and pitfalls

The main risk is drifting the FACT, not just the framing, between audiences - rounding differently, quietly dropping a caveat for the executive version that the engineering version kept. If two people from different audiences compare notes, the numbers must match exactly even though the framing differs. A second pitfall is over-customizing to the point that each version reads as a completely different story; keeping the headline sentence identical across all three is what prevents that.

Direct answer

Do not pick a "winner" among the four existing customer dimensions by default; audit what each one actually gets right for its own mart's purpose, design a new canonical customer dimension that is a superset covering every legitimate distinction the four marts need (billing account for finance, individual contact for marketing, and so on), then migrate each mart onto it one at a time behind a compatibility view so none of their existing reports break mid-migration. Prevent recurrence with a lightweight governance gate: no new dimension ships without checking whether an existing conformed one already covers it.

Structured elaboration

Deciding what becomes canonical. The instinct to just pick the "biggest" or "oldest" of the four dimensions is usually wrong, because each one likely encodes a real, legitimate business distinction its own team needed (finance genuinely cares about billing accounts, marketing genuinely cares about individual contacts within an account) that a poorly-chosen "winner" would silently drop. Instead, treat this as a modeling exercise: interview each mart's owners about what their dimension actually needs to support, and design a new conformed customer dimension whose grain and attributes are a genuine superset, not a copy of whichever dimension happened to be first or largest.

Migrating without breaking existing reports. Apply the same compatibility-view mechanism used for a single department's evolution: for each of the four marts, keep its fact table pointing at a view carrying the OLD dimension's name and columns, backed by the new conformed dimension underneath, and migrate that mart's reports to the conformed dimension directly on its own team's schedule. Do this mart by mart, not all four simultaneously, so a mistake discovered while migrating the first mart does not have to be independently re-diagnosed and re-fixed in the other three.

Governance to prevent recurrence. The root cause here was not bad intentions, it was the absence of a checkpoint: nothing stopped a new mart from quietly building its own customer dimension because doing so is always locally faster than reusing and possibly extending an existing one. Put a lightweight review gate in front of any new dimension: before a team builds one, they check a shared dimension catalog for an existing conformed version and either reuse it, propose a reviewed extension if it is close but missing an attribute, or get an explicit, documented exception if the business concept genuinely differs (a customer for billing purposes really is a different entity from a customer contact for marketing purposes, and conflating them would be its own mistake).

Worked example

The core cost of the current state, quantified: finance defines a customer as one row per billing account, marketing defines it as one row per individual contact. Given the same underlying population (three billing accounts, one of which has two separate contacts):

CREATE TABLE dim_customer_finance (customer_key VARCHAR PRIMARY KEY, billing_account_id VARCHAR);
INSERT INTO dim_customer_finance VALUES
  ('FIN-1', 'ACCT-100'), ('FIN-2', 'ACCT-101'), ('FIN-3', 'ACCT-102');

CREATE TABLE dim_customer_marketing (customer_key VARCHAR PRIMARY KEY, email VARCHAR, billing_account_id VARCHAR);
INSERT INTO dim_customer_marketing VALUES
  ('MKT-1', 'ada@example.com', 'ACCT-100'),
  ('MKT-2', 'bob@example.com', 'ACCT-100'),   -- same billing account as MKT-1, a second contact
  ('MKT-3', 'carol@example.com', 'ACCT-101'),
  ('MKT-4', 'dave@example.com', 'ACCT-102');

SELECT count(*) AS finance_customer_count FROM dim_customer_finance;      -- one row per billing account
SELECT count(*) AS marketing_customer_count FROM dim_customer_marketing; -- one row per individual contact

Executed against this representative dataset (3 billing accounts, one of which has 2 contacts, for 4 contact rows total), finance's query returns 3 and marketing's returns 4, both internally correct for their own definitions and both wrong to compare directly, which is exactly the discrepancy that reaches an executive meeting as "why do finance and marketing disagree about customer count." The fix is not to force one number to be right, it is to conform the dimension so both numbers have an explicit, documented, and DIFFERENT name ("billing accounts" and "individual contacts"), sourced from the same canonical dimension's different grains or a companion bridge table, so nobody accidentally compares them as if they were the same metric again.

Trade-offs and pitfalls

The most common mistake is rushing to a single "one true customer table" that forces every mart's legitimate distinction into one grain, which either breaks the marts whose reporting genuinely needs a different grain or quietly re-introduces exactly the confusion this exercise was meant to fix, just under one table name instead of four. The second common mistake is skipping the governance gate once the immediate reconciliation is done: without an ongoing checkpoint, a fifth mart built a year later has no reason not to repeat the exact same mistake, since nothing in the process changed, only the specific dimensions that happened to get fixed this time.

Direct answer. A LATERAL join lets a subquery on the right-hand side reference columns from a table on the left-hand side of the FROM clause, row by row, which is exactly what you need to compute "the top N related rows per outer row" without a correlated subquery in the SELECT list or a window function over the whole joined result.

Structured elaboration. A LATERAL subquery is evaluated once per row of whatever precedes it in the FROM clause, with that outer row's columns visible inside the subquery, similar in spirit to a correlated subquery but structured as a proper join rather than an expression in the SELECT list, which lets it return multiple rows and columns naturally, and lets the optimizer reason about it more like an ordinary join than an opaque per-row expression.

Worked example. I verified this with two customers and six orders (four for customer 1, two for customer 2), returning the top 3 orders by amount per customer:

SELECT c.customer_id, o.order_id, o.total
FROM customers c,
LATERAL (
  SELECT order_id, total
  FROM orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY total DESC
  LIMIT 3
) o
ORDER BY c.customer_id, o.total DESC;

This correctly returned customer 1's top three orders (80, 65, 50, correctly excluding their fourth, smaller order) and customer 2's two available orders (999, 10), confirming the LATERAL subquery's WHERE o.customer_id = c.customer_id correctly re-scoped to each outer row and its own ORDER BY ... LIMIT 3 correctly capped the result per customer, not globally across all customers.

Trade-offs and pitfalls. LATERAL is usually more optimizer-friendly than an equivalent correlated scalar subquery specifically because it's structured as a genuine per-row join the optimizer can index-nest efficiently (an index on orders(customer_id, total) makes each per-customer lookup cheap), rather than an opaque per-row expression the optimizer has less visibility into; it's also a natural fit for "top N per group" specifically because the LIMIT lives inside the LATERAL subquery, scoped per outer row, which a plain window function approach achieves differently (ranking every row, then filtering on rank) with a comparable but structurally different cost profile.

Complexity

With a supporting index on the inner table's join and sort columns, this executes as roughly (outer rows) times (a cheap, index-bounded lookup for N rows), which scales far better than materializing every related row and sorting them all before trimming to N.

Edge cases

An outer row with fewer than N matching inner rows (customer 2's two orders, in the example) correctly returns just those, with no error and no padding, which is worth confirming explicitly since a naive alternative implementation can sometimes mishandle that case.

Direct answer

Keep a hash map from key to a node holding the value and a frequency count, and a separate doubly linked list (a list where each node points to both its neighbors, so removal and insertion at any known node are O(1), no shifting required) per frequency value. Eviction always pops from the lowest-frequency list, and within that list the head is the least-recently-used (LRU) entry, so the head is exactly the least-frequently-used (LFU) entry with ties broken by recency. Tracking the current minimum frequency separately means you never have to scan for the lowest bucket.

Structured elaboration

Three pieces of state:

• key_node: hash map from key to its node (value, frequency).
• freq_lists: hash map from frequency to a doubly linked list of nodes at that frequency, ordered by recency (oldest at the head, newest at the tail).
• min_freq: the smallest frequency that currently has at least one entry.

On get(key) (and on put for an existing key): look up the node in O(1) via key_node, remove it from its current frequency's list, increment its frequency, and append it to the new frequency's list (append means "just used," so it goes to the tail, the recent end). If removing it emptied the old list and that old frequency was min_freq, bump min_freq by one, since a bucket can only ever empty at the current minimum (nothing decreases frequency).

On put(key, value) for a new key at full capacity: evict the head of freq_lists[min_freq] (LFU, LRU tie-break), remove it from key_node, then insert the new key at frequency 1 and reset min_freq to 1 (a brand new key is always tied for lowest).

Every step touches only hash map entries and linked-list splices at already-known nodes, so both operations run in O(1) average time.

Worked example

class _Node:
    __slots__ = ("key", "value", "freq", "prev", "next")

    def __init__(self, key=None, value=None):
        self.key = key
        self.value = value
        self.freq = 1
        self.prev = None
        self.next = None


class _FreqList:
    """Doubly linked list of nodes sharing one frequency, ordered by
    recency (head = least recently used within this frequency)."""

    def __init__(self):
        self.head = _Node()
        self.tail = _Node()
        self.head.next = self.tail
        self.tail.prev = self.head
        self.size = 0

    def append(self, node):
        node.prev = self.tail.prev
        node.next = self.tail
        node.prev.next = node
        self.tail.prev = node
        self.size += 1

    def remove(self, node):
        node.prev.next = node.next
        node.next.prev = node.prev
        self.size -= 1

    def pop_lru(self):
        node = self.head.next
        self.remove(node)
        return node


class LFUCache:
    def __init__(self, capacity: int):
        self.capacity = capacity
        self.size = 0
        self.min_freq = 0
        self.key_node: dict[int, _Node] = {}
        self.freq_lists: dict[int, _FreqList] = {}

    def _bump(self, node):
        old_freq = node.freq
        self.freq_lists[old_freq].remove(node)
        if self.freq_lists[old_freq].size == 0:
            del self.freq_lists[old_freq]
            if self.min_freq == old_freq:
                self.min_freq += 1
        node.freq += 1
        self.freq_lists.setdefault(node.freq, _FreqList()).append(node)

    def get(self, key: int) -> int:
        if key not in self.key_node:
            return -1
        node = self.key_node[key]
        self._bump(node)
        return node.value

    def put(self, key: int, value: int) -> None:
        if self.capacity == 0:
            return
        if key in self.key_node:
            node = self.key_node[key]
            node.value = value
            self._bump(node)
            return
        if self.size == self.capacity:
            evict = self.freq_lists[self.min_freq].pop_lru()
            del self.key_node[evict.key]
            if self.freq_lists[self.min_freq].size == 0:
                del self.freq_lists[self.min_freq]
            self.size -= 1
        node = _Node(key, value)
        self.key_node[key] = node
        self.freq_lists.setdefault(1, _FreqList()).append(node)
        self.min_freq = 1
        self.size += 1


cache = LFUCache(2)
cache.put(1, 1)
cache.put(2, 2)
print(cache.get(1))   # 1, freq(1) becomes 2
cache.put(3, 3)       # capacity full; key 2 is the only entry at min_freq=1, evicted
print(cache.get(2))   # -1, evicted
print(cache.get(3))   # 3, freq(3) becomes 2
cache.put(4, 4)       # freq-1 bucket is empty so min_freq=2; key 1 is LRU there, evicted
print(cache.get(1))   # -1, evicted
print(cache.get(3))   # 3
print(cache.get(4))   # 4

Running this prints, in order:

1
-1
3
-1
3
4

Key points

• Each frequency bucket is itself an LRU list, which is what makes the tie-break automatic: the head is always the oldest entry at that frequency.
• min_freq only ever needs to increase, and only when the bucket it points at empties out, so tracking it costs O(1) instead of scanning.

Complexity

for space (one node per stored key, plus the bucket lists which partition those same nodes).

Edge cases

• capacity == 0: put is a no-op, get always returns -1.
• Repeated put on an existing key must bump frequency the same way get does (shown above), not just overwrite the value.
• A put that overwrites a key that would otherwise trigger eviction must update, not evict, since the key is already present.

Trade-offs & pitfalls

The classic bug is forgetting to advance min_freq when the bucket it points at becomes empty. It's tempting to just track the smallest key of freq_lists, but that requires a scan; keeping min_freq as its own O(1)-updated variable is what preserves the overall bound. In Python, a bucket can be implemented as an OrderedDict instead of a hand-rolled doubly linked list (using move_to_end and popitem(last=False)), trading a little control for much less code; for an interview, either is acceptable as long as you can name why it's still O(1). Also worth naming as depth beyond what most interviews require: an LFU cache is more complex to build and reason about than a plain LRU cache, so in practice teams only reach for LFU when access patterns are genuinely skewed by frequency rather than recency, and even then often prefer an approximate LFU (a lossy frequency sketch) over an exact one to bound memory growth.

Direct answer

Choosing a connector, on either the source or the sink side, comes down to six things: how reliably it delivers data, how it tracks and persists progress (its offset or checkpoint model), how well it understands and communicates the source or target's schema, whether its latency and throughput fit your freshness needs, how it handles authentication and secrets, and how mature it is to actually operate day to day. A managed connector, a cloud-native one, and something you build yourself trade these off differently, and the right choice depends on which of the six actually matters most for this particular integration.

Structured elaboration

Reliability

• What delivery guarantee does it actually provide: at-least-once, at-most-once, or something closer to exactly-once via idempotent writes (writes that produce the same end result even if the same write is accidentally repeated, for example because a retry re-sends a call that actually succeeded the first time, so a retry never creates a duplicate)? Most connectors are honestly at-least-once; treat any "exactly-once" claim skeptically until you have seen how it is implemented.
• How does it behave on a transient failure: does it retry automatically, or does it require manual intervention to resume?

Offset and checkpoint management

• Does the connector track its own progress durably (so a restart resumes cleanly), and can you inspect or manually adjust that state if something needs to be replayed?
• For a source connector, this is usually a cursor or timestamp; for a sink connector, it is usually the last successfully-committed offset from the upstream topic or queue.

Schema support

• Does it understand the source or target's schema well enough to detect a breaking change, or does it treat every record as an opaque blob?
• For structured targets (a warehouse table, a typed sink), does the connector handle schema evolution (a new column, a type change) gracefully, or does it require manual reconfiguration on every source-side change?

Latency and throughput

• Is the connector fundamentally a polling design (batch-oriented, with latency bounded by the poll interval) or a streaming design (event-driven, near-real-time)? This is often the single biggest constraint on what freshness service-level agreement (SLA) you can promise.
• What is its realistic sustained throughput ceiling, and does that comfortably clear your actual data volume with headroom for growth?

Security

• How does it store and rotate credentials: a secrets manager integration, or configuration files that are easy to leak?
• Does it support the authentication model the source or target actually requires (OAuth2 with refresh tokens, mutual TLS, or cloud IAM (Identity and Access Management) roles), or only a simpler scheme that will not work for a security-conscious source?

Operational maturity

• How much observability does it expose out of the box: lag metrics, error rates, a dead-letter mechanism for records it cannot process?
• How is it upgraded, and what happens to in-flight work during that upgrade?

How the calculus differs by connector type

• A managed connector (Fivetran-style) tends to score well on operational maturity and reliability out of the box, at the cost of less visibility into exactly how it tracks offsets or handles schema changes internally.
• A cloud-native connector (a first-party AWS/GCP service) usually integrates cleanly with the platform's own IAM and secrets model, at the cost of being locked to sources and targets that specific cloud vendor supports well.
• A custom-built connector gives you full control over every one of the six dimensions, at the cost of having to implement and then operate all of them yourself, including the parts (idempotent retries, checkpoint persistence, schema-change detection) that are easy to get subtly wrong.

Worked example

A team choosing between three sink connectors for the same Kafka topic (a managed Snowflake sink, a cloud-native Kinesis Firehose-to-S3 delivery, and a custom Python consumer) needs sub-minute freshness and exactly-once-in-practice writes via a natural key. The managed Snowflake sink turns out to support exactly this pattern (a MERGE-based idempotent write keyed on a record ID) as a documented configuration option, so it wins on both fit and lowest operational burden. If the same team instead needed a target with no managed connector available at all, a proprietary internal service, the custom-build path would be forced regardless of preference, and the evaluation shifts to "how much of these six dimensions can we realistically implement well," not whether to build.

Trade-offs & pitfalls

• Do not evaluate a connector purely on throughput numbers from its marketing page; ask specifically how it behaves on failure, since that is where most real incidents originate.
• "It supports schema evolution" can mean anything from "handles a new nullable column automatically" to "requires you to manually update a mapping file"; get the specific behavior, not just the checkbox.
• A connector's offset model matters more than it looks: one that cannot be manually rewound makes recovering from a bad batch far harder than one that exposes and lets you adjust its checkpoint.
• Security is the dimension teams most often under-weight during evaluation and most regret later, particularly credential rotation, which a "quick proof of concept" connector rarely handles well from day one.

Approach

Chain four small generator functions, each a thin, focused stage: read lines lazily from the file handle, tokenize and lowercase each line as it arrives, filter stopwords per line, and batch tokens into fixed-size lists. Because every stage is a generator, no stage pulls more than one line or token ahead of what the next stage actually asks for, so nothing downstream ever forces an upstream stage to materialize the whole file. Laziness only holds as long as every stage in the chain stays a generator; the moment one stage needs to look backward or ahead across the whole stream (sorting, deduplicating against the full history, or genuine random access), the streaming model has to break: spill to disk, or switch to an indexable structure.

Code (Python 3.12)

from typing import Iterable, Iterator, List, Set
import re
import io

_token_re = re.compile(r"\b\w+\b")

def read_lines(f: Iterable[str]) -> Iterator[str]:
    for line in f:
        yield line

def tokenize(lines: Iterable[str]) -> Iterator[str]:
    for line in lines:
        for tok in _token_re.findall(line.lower()):
            yield tok

def filter_stopwords(tokens: Iterable[str], stopwords: Set[str]) -> Iterator[str]:
    for tok in tokens:
        if tok not in stopwords:
            yield tok

def batch(tokens: Iterable[str], batch_size: int) -> Iterator[List[str]]:
    buf: List[str] = []
    for tok in tokens:
        buf.append(tok)
        if len(buf) >= batch_size:
            yield buf
            buf = []
    if buf:
        yield buf   # flush the final partial batch

text = ("The cat sat on the mat.\n"
        "The dog and the cat ran.\n"
        "A fox jumped over the lazy dog.\n")
f = io.StringIO(text)   # stand-in for open("huge_corpus.txt", encoding="utf-8")
stop = {"the", "a", "on", "and", "over"}

pipeline = batch(filter_stopwords(tokenize(read_lines(f)), stop), batch_size=3)
for chunk in pipeline:
    print(chunk)

Running this prints, in order:

['cat', 'sat', 'mat']
['dog', 'cat', 'ran']
['fox', 'jumped', 'lazy']
['dog']

Key points

• Every yield keeps state minimal: read_lines relies on the file object's own internal buffering rather than holding extra lines itself; tokenize holds one line's tokens at a time; filter_stopwords holds one token at a time; batch holds at most batch_size tokens.
• Nothing runs until something pulls from the outermost stage: building pipeline does no work at all, the for loop is what actually drives every generator in the chain, one item at a time.
• The stopword check uses a set for $O(1)$ average membership, which matters because it runs once per token across the whole stream; a list would make that check scale with the size of the stopword list on every single token.
• The final partial batch (fewer than batch_size items) has to be flushed explicitly after the loop, or the last few tokens of the corpus silently disappear.

Complexity

Time: $O(n)$ total, where $n$ is the number of characters/tokens in the file, since every stage visits each token a constant number of times as it flows through the chain. Space: $O(1)$ per stage plus $O(b)$ for the batch buffer at any instant, where $b$ is batch_size, never $O(n)$ for the whole dataset.

Edge cases

• A single line that is itself too large to hold comfortably in memory: read_lines yields whole lines, so a corpus with no newlines (or pathologically long lines) needs chunking within read_lines itself rather than relying on line boundaries.
• Non-word tokens or a different tokenization scheme (punctuation-sensitive, subword) means adjusting _token_re or swapping in a real tokenizer, without touching any other stage.
• Where laziness genuinely has to break:
  • Sorting all tokens requires the entire stream in memory or on disk, since sorting has no online (single-pass, forward-only) form.
  • Deduplicating a token against the FULL history, not just the current batch, needs a structure that survives across batches: an in-memory set if the vocabulary is small enough, or spilling to an on-disk hash set / streaming external-sort-and-dedupe utility once the unique-token count won't fit in memory.
  • Anything downstream that needs random access (for example, batch #500 without having produced batches 1 through 499) forces spilling to an indexable structure, such as a file with a byte-offset index or a queue/database, because a pure generator chain can only move forward.

Once a company has more than a handful of self-serve analysts, letting each of them build transformation logic inside their own BI tool workbook (a Tableau Prep flow, a Power BI query) breaks in a specific, predictable order.

What breaks first

1. Duplication of logic. The same "active user" definition gets implemented slightly differently in three different dashboards, because each analyst wrote their own version instead of referencing a shared one, and nobody notices until an executive asks why two reports disagree.
2. No discoverability. Logic buried in a BI tool's proprietary transformation layer isn't searchable, versioned, or reviewable the way a warehouse table or a dbt model is; you can't grep it, and there's no lineage graph pointing back to it.
3. Query performance and governance drift out of anyone's control. Heavy transformations running inside the BI tool (rather than pre-materialized in the warehouse) mean every dashboard refresh re-does expensive work, and row-level security or masking rules applied in the warehouse don't automatically extend into whatever an analyst built locally.

How to decide what belongs where
Critical, widely-consumed metrics (revenue, active users, anything that ends up in an executive dashboard or a board deck) belong centrally in the warehouse as tested, documented ELT models, owned by whoever is accountable for that metric being right. Genuinely exploratory, single-use analysis (an analyst reshaping data for one ad-hoc investigation that will never be reused) is fine to leave in the BI tool, because the cost of formalizing it exceeds the benefit.

Who should own transformation logic for critical metrics
The team that's accountable for the metric being correct, which in practice means a data engineering or analytics engineering function that can enforce testing, review, and a single source of truth, not whichever analyst happened to need the number first. The self-serve BI layer should consume the already-correct warehouse table, not re-derive the definition.

The trade-off worth naming: centralizing everything into the warehouse is more governable but slower for an individual analyst to iterate on. The realistic policy most teams land on is a promotion path: an analyst prototypes a transformation in the BI tool, and once it's proven useful and gets reused by more than one person, it gets "promoted" into a tested dbt model in the warehouse, rather than either extreme (everything centralized from day one, or nothing ever formalized).

Range-based sharding

• Mapping: Keys are ordered (e.g., user_id or timestamp) and contiguous key ranges are assigned to shards (shard A: 1–1,000,000; shard B: 1,000,001–2,000,000).
• Advantages: Intuitive, supports range queries efficiently (scan a small number of shards), easy to route if ranges are known.
• Failure modes: Hotspots when many writes/reads target a narrow range (e.g., recent timestamps); costly rebalancing when ranges must be split/merged — moving contiguous data can be heavy.
• Typical use case: Time-series or log storage where queries are range-based (recent N days), or analytics systems partitioned by date.

Hash-based sharding

• Mapping: A hash function maps each key to a shard (shard = hash(key) mod N). Keys are distributed pseudorandomly across shards.
• Advantages: Good uniform distribution → fewer hotspots for uniform key access; simple routing, easy to scale out by adding shards (with consistent hashing variation).
• Failure modes: Range queries are expensive (data for a key range lives on many shards); rebalancing naive modulo schemes cause massive remapping when N changes (use consistent hashing to reduce movement); hot keys still possible if some keys are extremely popular.
• Typical use case: User-profile or KV stores where uniform access distribution is desired and point lookups dominate.

Directory (lookup)-based sharding

• Mapping: A central mapping (directory) maps each key or key-prefix to a shard (e.g., metadata table or service returning shard for key).
• Advantages: Maximum flexibility — arbitrary placement policies (by tenant, load, geography); supports affinity (co-locate related keys); easy to move keys by updating directory.
• Failure modes: Directory is a single point of coordination (needs replication/consensus); lookup adds extra hop and latency; directory maintenance overhead (consistency, scaling).
• Typical use case: Multi-tenant systems where tenants vary in size and need isolation, or when you need to co-locate related datasets (e.g., all data for a given customer) and perform operational rebalancing.

Short guidance: choose range when queries are range-heavy, hash for even distribution and simple point lookups, directory when placement flexibility and tenant isolation matter. Consider consistent hashing, monitoring for hotspots, and automating rebalancing for production systems.

The medallion architecture organizes a lakehouse or warehouse into three progressive layers: bronze holds raw data close to how it arrived, silver holds cleaned and conformed data, and gold holds business-ready, aggregated data. Each promotion step adds quality and structure, and the layering makes it possible to trace a bad number in a report back to the exact stage where it went wrong.

What lives in each layer

Bronze (raw). Data as it arrived from the source, with minimal changes: maybe a timestamp added, but no real cleaning, deduplication, or business logic applied. It's kept close to raw specifically so you can always reprocess it later if downstream logic changes. A data scientist would come here to inspect what the source actually looks like, or to debug why a downstream number seems wrong.

Silver (cleaned and conformed). Deduplicated, validated, typed data with obvious errors filtered out or flagged, and different sources joined into a consistent, entity-centric shape (one clean 'customer' table instead of three inconsistent ones). This is usually where a data scientist does most exploratory work and feature engineering, because it's reliable enough to trust but not yet flattened into business-specific aggregates.

Gold (curated, business-ready). Aggregated, denormalized tables built for a specific business purpose: a metric, a dashboard, a feature table for a production model. This is what business intelligence (BI) tools and dashboards query directly, and what a data scientist would use to validate that a model's output lines up with the business's own numbers.

Worked example: what happens at each promotion

Going from bronze to silver typically involves the validations that catch structural problems: rejecting or quarantining rows with impossible values, deduplicating records that arrived twice, and resolving type mismatches. Going from silver to gold applies business logic: computing the aggregates and joins a specific report or model actually needs, which is where a subtle bug (a wrong join key, double-counting a category) would surface as a wrong number in a dashboard.

Because each layer's boundary is explicit, when a gold-layer number looks wrong, you can check whether the problem is in the gold aggregation logic, in the silver cleaning logic, or in the bronze data itself, rather than having to untangle one monolithic transformation.

Trade-offs and pitfalls

The most common mistake is treating the layer names as a rigid rulebook instead of a communication convention: not every pipeline genuinely needs three distinct physical layers, and forcing a small dataset through all three stages for the sake of following the pattern adds pipeline complexity without adding real quality. The second pitfall is skipping the boundary discipline the pattern is meant to provide: if the same job does raw ingestion, cleaning, and business aggregation all at once with no clear layer boundary, you lose the exact debugging benefit (isolating which stage broke) that's the whole point of adopting the pattern in the first place.