Google Data Engineer Interview Preparation Guide - Mid Level

Direct answer
Before presenting, think through the questions a skeptical, informed listener would actually ask, prioritizing the ones that probe your weakest assumption or your most surprising claim, and prepare a short, direct answer for each rather than hoping you'll improvise well.

Structured elaboration

1. Look for your weakest link first. Every piece of work has at least one assumption, data limitation, or judgment call that's more debatable than the rest; that's almost always where a sharp question comes from.
2. Look for your most surprising or counterintuitive claim. Anything that contradicts what people expected invites a "how do you know that's really true?" question.
3. Prepare a one-to-two sentence answer, not a rehearsed speech. A concise, direct answer reads as confident; a long, defensive one reads as though you're worried about the question.
4. It's fine to prepare an honest "we don't know yet" answer for a genuine gap, rather than inventing a more impressive-sounding answer under pressure; a confident admission of a limitation is usually better received than an unconvincing dodge.
5. Practice saying the answers out loud, not just thinking through them mentally; the gap between a mentally-rehearsed answer and one you can actually say smoothly under pressure is often bigger than expected.

Worked example
Presenting a recommendation to shift budget from one marketing channel to another based on eight weeks of data: anticipated tough questions might be "how confident are you this isn't just seasonal?", "what happens if the trend reverses next month?", and "did you control for the pricing change that happened in week 5?" Prepared answers: "We checked against the same period last year and saw a similar pattern, though eight weeks is admittedly a short window;" "if it reverses, the downside is limited since we're proposing a 20% shift, not the full budget;" "we did exclude the two weeks around the pricing change specifically to avoid conflating the two effects."

Each answer is short, direct, and, where there's a genuine limitation (the short time window), honestly acknowledged rather than glossed over.

Trade-offs and pitfalls

• Over-preparing for every conceivable question can lead to over-rehearsed, stiff-sounding answers; focus on the two or three questions most likely to actually come up, not an exhaustive list.
• Being defensive about a genuinely fair question damages credibility more than the limitation itself would; a calm, honest acknowledgment of a real gap usually lands better than an unconvincing justification.
• If a question comes up that you genuinely didn't anticipate and don't know the answer to, saying so plainly and offering to follow up is stronger than guessing in the moment.

Direct answer

Yes, in general break the monolith apart, but only along boundaries where two sides genuinely have different owners, different release cadences, or different failure domains, not just wherever the code happens to have a function boundary. The cut should define a bounded data product, its own schema, service-level agreement (SLA), and consumer contract, rather than an arbitrary step lifted straight out of the existing code.

Structured elaboration

Finding the boundaries. Map every output the job produces and who consumes it. Group outputs and the logic that produces them by business domain and ownership. Then look for genuinely low-coupling seams in the dependency graph, a stage whose output is read-only and consumed independently downstream is a good cut point; a stage that shares mutable intermediate state with its neighbor is not.

Deciding whether to split at all. Split when pieces need independent deploy cadence (one team ships fixes weekly, another monthly, and coupling them means blocking each other), independent scaling (one stage is bursty and compute-heavy, another is light and constant), or different failure tolerance (a non-critical report should not be able to block a financial load sharing its deployable unit). Splitting adds real cost, more dependency edges, more contracts to keep compatible, so it needs one of these reasons, not just "smaller files."

Organization-scale framing. Once more than one team is involved, the decision is as much about ownership as architecture. Each independently deployable stage needs a single accountable owner and a versioned contract other teams can build against without reading that owner's source. Skipping this just relocates the coupling from explicit code dependencies to undocumented cross-team assumptions, which is worse because it stays invisible until it breaks in production.

Undocumented, legacy-codebase framing. When the monolith has no documentation and thin test coverage, the current code structure cannot be trusted as evidence of the real data dependencies, a function boundary may exist for reasons that have nothing to do with what actually depends on what. The practical approach is to instrument the existing job first, log every table or file it reads and writes per code region, to reconstruct an empirical dependency graph, and treat that observed graph, not the module layout, as the source of truth for where a cut is safe.

Migration mechanics. Extract one bounded piece as a new stage and run it in shadow, in parallel, not yet consumed, alongside the intact monolith. Validate its output against the monolith's using partition-level checksums or keyed aggregates over a defined window. Cut consumers over only after that window is clean, and keep the old code path as a fallback until the new stage has been trusted in production.

Worked example

The monolith produces twelve downstream tables across three domains: orders (five tables, needing hourly refresh for an operations dashboard), customers (four tables), and inventory (three tables, daily refresh is sufficient):

Orders is the strongest candidate to extract first: it has both a distinct consumer (hourly ops dashboard) and a distinct cadence from the other two domains. The cutover bar for its shadow run should be defined in advance rather than judged by eyeball: for example, zero row-count and keyed-aggregate mismatches across three consecutive daily partitions, where any prior deltas are fully explained by the source's own known late-arrival window (say, at most two hours past a partition boundary). A threshold defined and checked this way, not "it looked right in a spot check," is what makes the decision to cut consumers over defensible.

Trade-offs & pitfalls

Splitting too finely, one stage per table, multiplies the number of contracts and DAG (directed acyclic graph) edges to maintain, and every edge is a place ordering or schema assumptions can silently drift apart. Splitting too coarsely, keeping unrelated domains in one deployable unit, means a slow query or bug in inventory logic can delay the orders load that operations depends on hourly. A common wrong turn is cutting consumers over before the shadow-run validation window has covered a full business cycle, such as month-end close, so an edge case like a backfill scenario only surfaces after cutover. Another pitfall is treating "no test coverage" as a reason to delay indefinitely, rather than building the checksum and shadow-run harness that substitutes for the tests that don't exist.

In Spark, repartition performs a full shuffle to redistribute rows into a specified number of partitions, which can both increase or decrease partition count and can rebalance data across them; coalesce only ever reduces partition count, and does so by combining existing partitions locally (on the same worker where possible) without a full shuffle, which makes it much cheaper but means it cannot fix an existing imbalance across partitions, since it merges neighboring partitions rather than truly redistributing rows.

Choosing for a small-output-files problem

When a job's output has too many small files, coalesce is usually the right tool if the goal is purely to reduce the number of output files without needing to genuinely rebalance the data first, since it achieves that at a fraction of repartition's cost by avoiding a full shuffle. repartition is the right tool if the upstream data is also unevenly distributed across partitions (some partitions much larger than others) and you need genuinely balanced output partitions, not just fewer of them, since coalescing an already-imbalanced set of partitions just produces fewer, still-imbalanced ones.

Cost and performance implications

coalesce avoids the network shuffle entirely (partitions are merged in place, or with minimal data movement), so it is significantly cheaper and faster when it applies. repartition's full shuffle costs network transfer and serialization proportional to the whole dataset, the same cost profile as any other shuffle-triggering operation, but it is the only one of the two that can actually fix a genuinely uneven distribution rather than just reducing file count on top of an existing imbalance.

Trade-offs and pitfalls

A common mistake is reaching for coalesce to fix a small-files problem when the underlying cause is actually skewed upstream partitioning; coalescing skewed partitions down still leaves the same skew (now concentrated into fewer, but still unevenly sized, output files), so the small-files symptom can appear fixed (file count drops) while the underlying performance problem (a few oversized output partitions) remains. Check whether the upstream partition sizes are actually balanced before assuming coalesce alone will solve the problem; if they are not, repartition's shuffle cost is a worthwhile trade for genuinely even output.

Direct answer. Managed cloud data services impose real operational limits that only surface after adoption: API throughput caps, maintenance windows, backup retention limits, restore-time floors, and coarse-grained scaling steps. Naming these upfront and planning mitigations avoids a painful surprise mid-project.

Structured elaboration. Five concrete constraint types:

1. API throughput limits. Managed services cap requests per second per account or per resource (for example, a fixed number of DDL operations per minute on a warehouse, or API calls per second to a managed database's control plane). Mitigation: batch operations where the API supports it, and build in exponential backoff and retry logic rather than assuming unlimited throughput.
2. Maintenance windows. Providers reserve a window (often configurable, sometimes not) to apply patches, which can briefly affect availability or performance. Mitigation: schedule your own critical batch jobs and SLA-sensitive workloads outside the provider's maintenance window, and verify whether the window is genuinely optional or forced for major version upgrades.
3. Backup retention limits. Automated backups are often retained for a fixed maximum (for example, 35 days), after which older recovery points are unavailable through the managed mechanism. Mitigation: export longer-term backups to cheaper archival storage yourself if your compliance or business requirements exceed the provider's default retention window.
4. Restore time. A large database's point-in-time restore can take hours, not minutes, and this restore-time floor does not shrink just because your recovery-time objective is tighter. Mitigation: test an actual restore at production scale before you need it in an emergency, and if the tested restore time exceeds your RTO, add a faster-recovery mechanism (read replica promotion, cross-region standby) rather than relying on the backup restore alone.
5. Scaling granularity. Managed services often scale in fixed steps (a specific set of instance sizes, a specific increment of warehouse "T-shirt" sizes) rather than continuously, so you may be forced to over-provision to the next available tier even when your actual need falls between two steps. Mitigation: benchmark your workload against the available tiers before committing, and build in the assumption that your effective capacity will be somewhat larger (and more expensive) than your exact requirement.

Worked example. A team migrating a database with a strict 4-hour recovery-time objective discovered during a fire drill, not a real incident, that their managed service's point-in-time restore for their data volume actually took 6 hours, silently violating the RTO the whole time it went untested. Because they tested this ahead of a real incident, they had time to add a cross-region read replica they could promote in minutes as a faster recovery path, using the slower backup restore only as a last-resort fallback rather than the primary recovery mechanism.

Trade-offs and pitfalls. The common thread across all five constraints is that they are invisible until you hit them: none of them show up in a proof-of-concept running at small scale over a few days. Budget time specifically to read the managed service's documented limits and to test the ones that matter most to your SLAs (especially restore time) before committing to production traffic, rather than discovering them during an actual incident.

Owning a small ETL/ELT pipeline end to end for the first time is mostly about establishing ground truth before touching anything, then closing the gaps you find in priority order.

Weeks 1 to 2: understand before you change anything
Read the pipeline's code and current run history. Answer three questions before doing anything else: what does it actually compute (not what the name suggests), who consumes the output and for what decisions, and what's the current failure behavior (does it alert anyone, does it retry, does it silently produce stale data on failure). This phase often surfaces the first real risk: an undocumented pipeline someone else built and left, where the biggest early win is just writing down what it does.

Weeks 3 to 4: instrument and stabilize
Add or fix the basics that let you know the pipeline is healthy without checking manually: a freshness check, a row-count sanity check against a rolling baseline, and an alert if the job fails or its output looks anomalous. Fix any known-broken behavior found in the discovery phase (an alert that fires into a channel nobody reads, a retry policy that silently gives up after one attempt).

Weeks 5 to 6: define success and plan the handoff
Define concrete success metrics: data freshness (how stale can the output be before it's a problem), error rate (what fraction of runs fail or need manual intervention), and run time (is it trending up as data volume grows). Document a runbook for the two or three most likely failure modes you found in discovery, and decide how ownership will continue after this initial six weeks: full-time ownership by you, a rotation across the team, or a handoff to an operations function, with the runbook and the instrumentation from weeks 3 and 4 being exactly what makes that handoff possible without you being the only person who understands the pipeline.

Risk mitigation throughout: treat every change as reversible until proven otherwise. Land instrumentation and alerting before touching the transformation logic itself, so you have visibility into whether a later change actually helped or quietly made things worse.

The habit that actually distinguishes a strong answer here: naming that the biggest risk in "owning a small pipeline" usually isn't the pipeline's code, it's that a single person becomes a silent single point of failure for something the business depends on. The six-week plan above is deliberately structured so that by the end, the pipeline's health is visible to more than one person and the runbook exists independent of whoever wrote it.

Direct answer

Localize each user's naive local timestamp to their own IANA time zone with Series.dt.tz_localize, using ambiguous='NaT' (not 'infer') and nonexistent='shift_forward', then convert to UTC (Coordinated Universal Time) for storage while keeping the original local timestamp as-is. ambiguous='infer' only works when a timezone has repeated (duplicate) wall-clock times to compare against each other in sequence; with one event per user per ambiguous instant, there is nothing to infer from and it raises.

Approach

1. Parse the local timestamp as a naive datetime (no timezone attached yet).
2. Group by each user's IANA time zone name (e.g. America/New_York) since tz_localize takes one zone at a time.
3. Localize with an explicit policy for both DST (daylight saving time) edge cases: ambiguous='NaT' marks the fall-back duplicate hour as missing rather than guessing, and nonexistent='shift_forward' rolls a spring-forward time that never existed to the next valid instant.
4. Convert the localized column to UTC with .dt.tz_convert('UTC').
5. Keep both columns: the tz-aware local time (for display) and the UTC time (for cross-user comparison and storage).

Worked example

import pandas as pd
pd.set_option("display.width", 200)
pd.set_option("display.max_columns", None)

df = pd.DataFrame({
    "user_id": [1, 2, 3, 4],
    "local_ts": ["2021-11-07 01:30:00", "2021-03-14 02:30:00", "2021-06-01 12:00:00", "2021-11-07 01:30:00"],
    "user_tz": ["America/New_York", "America/New_York", "Europe/Berlin", "America/Chicago"],
})

def normalize(frame):
    out = []
    for tz, group in frame.groupby("user_tz"):
        naive = pd.to_datetime(group["local_ts"])
        # ambiguous='NaT': the duplicated fall-back hour becomes missing instead
        # of a silent guess; nonexistent='shift_forward': spring-forward gaps
        # roll to the next valid instant
        aware = naive.dt.tz_localize(tz, ambiguous="NaT", nonexistent="shift_forward")
        group = group.copy()
        group["local_aware"] = aware
        group["utc_ts"] = aware.dt.tz_convert("UTC")
        group["ambiguous_dst"] = aware.isna() & naive.notna()
        out.append(group)
    return pd.concat(out).sort_index()

result = normalize(df)
print(result[["user_id", "local_ts", "user_tz", "local_aware", "utc_ts", "ambiguous_dst"]])

Output (verified against pandas 3.0.3):

   user_id             local_ts           user_tz                local_aware                    utc_ts  ambiguous_dst
0        1  2021-11-07 01:30:00  America/New_York                        NaT                       NaT           True
1        2  2021-03-14 02:30:00  America/New_York  2021-03-14 03:00:00-04:00 2021-03-14 07:00:00+00:00          False
2        3  2021-06-01 12:00:00     Europe/Berlin  2021-06-01 12:00:00+02:00 2021-06-01 10:00:00+00:00          False
3        4  2021-11-07 01:30:00   America/Chicago                        NaT                       NaT           True

Row 0 and row 3 both fall inside the fall-back duplicate hour in their respective US time zones (clocks repeat 1:00-2:00 on 2021-11-07) and are correctly flagged rather than silently resolved. Row 1 falls inside the spring-forward gap (2:00-3:00 does not exist on 2021-03-14 in America/New_York) and is shifted forward to 3:00 local. Row 2 has no DST transition nearby and passes through unchanged.

Key points

• ambiguous='infer' needs at least two occurrences of the ambiguous wall-clock time in the same call, in a sequence pandas can use to figure out which occurred first; with one row per user it has nothing to infer from and raises ValueError: Cannot infer dst time ... as there are no repeated times. 'NaT' is the safe default when you cannot supply extra information to disambiguate.
• Grouping by user_tz before localizing is required because tz_localize takes a single timezone per call; it does not accept a per-row timezone.
• Keep local_aware (for showing the user their own event time) and utc_ts (for any cross-user math, like ordering events from different users) as two separate columns rather than collapsing to one.

Complexity

O(n) time overall: the per-timezone groups are each localized in one vectorized call, and the number of distinct timezones is typically small compared to n. Memory: tz_localize and tz_convert each return a new Series (pandas 3.0's Copy-on-Write, CoW, model means the original local_ts/naive column is never mutated in place by these calls), so expect roughly 2-3x the base column's memory for the local_aware, utc_ts and ambiguous_dst columns combined.

Edge cases

• Ambiguous times (fall-back): resolved to NaT here and surfaced via ambiguous_dst; the calling code decides whether to fix these with more context (e.g. UTC millisecond offsets logged at collection time) or drop them.
• Nonexistent times (spring-forward): resolved by shifting to the next valid instant; nonexistent='shift_backward' or nonexistent='raise' are the other documented policies if shifting forward is not the right default for your data.
• Invalid or unknown timezone strings: tz_localize raises zoneinfo.ZoneInfoNotFoundError for a bad IANA name; validate incoming timezone strings against zoneinfo.available_timezones() before this step rather than letting the whole batch fail on one bad row.
• Historical rule changes: IANA's tz database is updated periodically as governments change DST rules; keep the Python/OS tz database current, since a stale one silently mis-localizes historical timestamps near a rule-change boundary.

Trade-offs and pitfalls

• ambiguous='infer' reads as the "smart" option but is the least robust in practice: it only works under a specific data shape (paired, ordered occurrences), and reaching for it as a default is a common mistake. Prefer an explicit, always-safe policy ('NaT') plus a follow-up decision, over a policy that sometimes throws depending on how the data happens to be grouped that day.
• Storing only UTC and discarding the original local time is tempting for storage efficiency, but it throws away the user's actual wall-clock context (which matters for anything showing "your event was at 9am"); storing both is the safer default unless storage cost is a proven constraint.

Direct answer. Read the plan tree from the leaves up, note the join algorithm and physical operator at each level, and compare each node's estimated row count to its actual row count; the largest divergence, combined with the node consuming the most time, is almost always where you should focus first.

Structured elaboration. Start by identifying the leaves (the scans) and work upward, tracking, at each join, which side was the "outer/driving" side and which was the "inner/probed" side, and which physical algorithm was used. For each node, note actual time (cumulative, including children) and actual rows versus estimated rows. A join order that puts a large, unfiltered table on the outer side of a nested loop is a red flag; a hash join whose build side turns out much larger than estimated is a sign the memory budget for that hash table may be undersized. Once you've walked the tree once for structure, walk it again purely looking for the single node with the largest gap between estimated and actual rows, since that's usually the root cause the other symptoms trace back to.

Worked example. Suppose a plan shows, from the bottom: a sequential scan on orders with an estimated 50,000 rows and an actual 48,000 rows (a good estimate), feeding into a hash join with customers whose own estimate and actual are both close, but that hash join then feeds a nested loop join against an addresses table where the estimated row count was 10 and the actual was 12,000, executed 1,000 times in a loop. The nested loop's own local estimate wasn't wildly wrong (10 vs 12,000 estimate-vs-actual per iteration is close in absolute terms), but multiplied across 1,000 loop iterations that's the node actually dominating total time, which a glance at just its own row estimate would hide.

Trade-offs and pitfalls. It's easy to anchor on the operator name that "sounds expensive" (hash join, sort) rather than the actual numbers; a hash join over a small, well-estimated input can be nearly free, while a nested loop that LOOKS cheap per iteration can dominate total runtime once you account for how many times it runs. Always multiply per-iteration cost by loop count before deciding a node is innocent.

Building a coalition before a decision point starts before you ever present: identify whose support or veto will actually matter, engage them privately in an order that makes each later yes easier to get, and bring each person something concrete they need rather than a generic ask for support.

Mapping and sequencing

• Map influence and interest. List everyone who could formally veto or bless the proposal, plus anyone with no formal say who still has real influence over those decision-makers.
• Sequence deliberately. Engage the lowest-friction likely allies first, before the proposal is public, so you arrive at the wider decision point with visible support already lined up rather than asking a group to be first movers together.
• Offer something specific per stakeholder, tied to what they're actually measured on: reduced risk to their own metric, a pilot scoped to their team, early visibility into results, or public credit. A generic ask for support is much weaker than something concrete.

Two named shapes of this pattern

Resolving separate vetoes before convening a group. A tech lead wants to relax a security control temporarily to hit a launch date, with a compensating control added afterward, a security versus time-to-market tradeoff. Brought cold to a mixed room, the most risk-averse voice usually wins by default. Instead, the lead meets security first, alone, asking what compensating control would make a temporary exception acceptable, not asking them to simply waive the check. Only once security has a specific answer does the lead bring legal, showing the agreed compensating control and asking what documentation legal needs to be comfortable with the interim exposure window. Product only joins once security and legal's actual sign-off is already attached, so the wider room is there to confirm, not to negotiate the tradeoff from scratch.

Multiple buy-in strategies aimed at different needs. For a cross-functional analytics initiative that hasn't launched yet, product and marketing may need to be brought along with three genuinely different offers: a scoped pilot for the team most worried about disruption, early access to the resulting data for the team that wants visibility, and public co-ownership credit for whichever team's cooperation is hardest to secure. Using the same single pitch on both functions usually undersells what each one actually needs to say yes.

Scaling it into standing influence

• From one-off coalition to a repeatable habit. Winning support once, on one proposal, with one team, is different from scaling personal influence beyond your immediate team into middle management across the organization. That scaling requires codifying the tactic into something repeatable (pilot, then data, then public credit) rather than reinventing the ask each time, and building relationships with peer leads before you actually need something from them.
• Trusted contributor to go-to partner. The credibility this builds over time moves through a specific progression: from being a trusted contributor, someone whose individual work is reliable, to being a go-to partner, someone stakeholders proactively loop in before a decision is even finalized, because your input has consistently made past decisions better. Track this by whether you're being consulted earlier in the process over time, not just by whether individual asks succeed.
• Owning the plan without owning the decision. When you don't own the decision outright, such as cross-functional analytics choices that belong to other teams, a personal influence plan means investing in relationships and data credibility with the actual owners on an ongoing cadence, not waiting until you need a specific yes.

Worked example

A tech lead wants to ship an integration faster by relaxing a specific security control temporarily, with a compensating control added within a defined follow-up window, instead of the default full security review blocking the launch date, a security versus time-to-market compromise. Approached cold, in a mixed room, security could veto outright, legal could block over compliance exposure, and product needs the date to hold for a partner commitment.

The lead meets security separately first: "what compensating control would make a temporary exception acceptable to you?" Security proposes a monitoring and alerting control plus a hard remediation date. The lead brings that specific agreement to legal next, asking what documentation legal needs to be comfortable with the interim exposure window; legal signs off given a written record and the fixed remediation date. Only then does the lead convene product, security, and legal together, now presenting a plan that already carries security and legal's specific sign-off, so product's core need (the date holds) is satisfied without the lead having to relitigate the tradeoff with all three functions at once.

The wider meeting is short, because every veto-holder's actual concern was resolved one-on-one beforehand, tailored to that function's own criteria, not a single generic pitch delivered to all three simultaneously.

What a senior person does differently here: never brings unresolved cross-functional tension into a group room, resolves each function's specific veto criteria privately in an order that makes later conversations easier, and only convenes the group to confirm what's already agreed.

Trade-offs and pitfalls

• Sequencing takes real calendar time. Under a hard deadline, skipping the one-on-one alignment to save time usually costs more time recovering from a group veto than the sequencing would have taken.
• What you offer each stakeholder has to be genuinely deliverable; an empty promise to secure a yes burns exactly the go-to-partner reputation the moment it isn't honored.
• Scaling this into a repeatable, org-wide habit without a track record of delivered promises just looks like politicking. The trusted-contributor credibility has to come first, before the scaled version works.

Direct answer

A minimal WAL needs three properties: append() writes a length-prefixed, checksummed record and blocks until fsync confirms it is durable; replay() reads records back in the exact order they were appended, stopping cleanly at the first corrupt or truncated record rather than guessing; and segment rotation caps any single file's size by rolling over to a new file once a threshold is crossed, so the WAL never becomes one unbounded, slow-to-open file. Below is a from-scratch implementation covering all three, executed with real file I/O (not simulated), including forced multi-segment rotation and a genuine torn-write (truncation) test.

Structured elaboration

On-disk record format. Each record is [4-byte length][8-byte seq][payload bytes][4-byte CRC32 checksum]. The length prefix lets replay() know exactly how many payload bytes to read without a delimiter (which could collide with binary payload content). The seq is a monotonically increasing number assigned at append time, used for ordering and as a natural idempotency key for downstream reconciliation. The checksum covers the payload and is verified on every read; a mismatch means corruption (or a torn write) and replay stops there.

Durability via fsync. write() alone only guarantees the OS page cache has the bytes, not that they reached disk; a crash before the OS flushes its cache can lose data the caller believed durable. os.fsync(f.fileno()) blocks until the write has actually reached durable storage, the guarantee append() must make before returning, since callers depend on "append returned" meaning "this record survives a crash."

Segment rotation. Once the current segment would exceed segment_max_bytes, a new file opens (zero-padded index, so filename order equals chronological order) and appends continue there. This bounds any single file's size, useful for eventual archival/deletion of old segments once a checkpoint makes them unnecessary, with no separate index file needed since replay() simply processes segments in filename order.

Crash recovery of the WAL's own bookkeeping. On construction, the WAL scans existing segments for the highest seq already used, so a restarted process resumes numbering correctly instead of colliding with pre-crash records: the WAL's own metadata (next seq, current segment) must itself be recoverable from disk, not held only in memory.

Worked example

"""
A simple write-ahead log: append(record), fsync durability, replay() in
order, and segment rotation. On-disk record format:
  [4-byte length][8-byte seq][record bytes][4-byte CRC32 checksum]
Segments rotate at a size threshold, named wal.0000000000, wal.0000000001,
..., so replay processes them in order by filename with no extra index.
"""
import os, struct, zlib, glob


class WriteAheadLog:
    HEADER_FMT = "!IQ"
    HEADER_SIZE = struct.calcsize(HEADER_FMT)

    def __init__(self, directory, segment_max_bytes=1024):
        self.directory = directory
        os.makedirs(directory, exist_ok=True)
        self.segment_max_bytes = segment_max_bytes
        self._next_seq = self._recover_next_seq()
        self._current_segment_idx = self._recover_current_segment_idx()
        self._current_path = self._segment_path(self._current_segment_idx)
        self._current_size = os.path.getsize(self._current_path) if os.path.exists(self._current_path) else 0

    def _segment_path(self, idx):
        return os.path.join(self.directory, f"wal.{idx:010d}")

    def _existing_segments(self):
        return sorted(glob.glob(os.path.join(self.directory, "wal.*")))

    def _recover_current_segment_idx(self):
        segments = self._existing_segments()
        return int(os.path.basename(segments[-1]).split(".")[1]) if segments else 0

    def _recover_next_seq(self):
        # On restart, scan existing segments for the highest seq seen, so
        # numbering continues correctly instead of colliding with prior records.
        max_seq = -1
        for _, seq, _ in self._replay_paths(self._existing_segments()):
            max_seq = max(max_seq, seq)
        return max_seq + 1

    def append(self, record_bytes):
        seq = self._next_seq
        self._next_seq += 1
        crc = zlib.crc32(record_bytes)
        entry = struct.pack(self.HEADER_FMT, len(record_bytes), seq) + record_bytes + struct.pack("!I", crc)
        if self._current_size + len(entry) > self.segment_max_bytes and self._current_size > 0:
            self._current_segment_idx += 1
            self._current_path = self._segment_path(self._current_segment_idx)
            self._current_size = 0
        with open(self._current_path, "ab") as f:
            f.write(entry)
            f.flush()
            os.fsync(f.fileno())  # block until the OS confirms the write hit disk
        self._current_size += len(entry)
        return seq

    @staticmethod
    def _replay_one_segment(path):
        with open(path, "rb") as f:
            while True:
                header = f.read(WriteAheadLog.HEADER_SIZE)
                if len(header) < WriteAheadLog.HEADER_SIZE:
                    return
                length, seq = struct.unpack(WriteAheadLog.HEADER_FMT, header)
                payload = f.read(length)
                crc_bytes = f.read(4)
                if len(payload) < length or len(crc_bytes) < 4:
                    return  # torn write: stop here
                if zlib.crc32(payload) != struct.unpack("!I", crc_bytes)[0]:
                    return  # checksum mismatch: stop here
                yield seq, payload

    def _replay_paths(self, paths):
        for path in paths:
            for seq, payload in self._replay_one_segment(path):
                yield path, seq, payload

    def replay(self):
        for path, seq, payload in self._replay_paths(self._existing_segments()):
            yield seq, payload


def main():
    import shutil
    test_dir = os.path.join(os.path.dirname(__file__), "s25_wal_dir")
    if os.path.exists(test_dir):
        shutil.rmtree(test_dir)

    # Small segment_max_bytes forces MULTIPLE rotations with a modest record
    # count, so rotation is actually exercised, not just theoretically present.
    wal = WriteAheadLog(test_dir, segment_max_bytes=200)
    records = [f"record-{i}-payload".encode() for i in range(50)]
    seqs = [wal.append(r) for r in records]
    print("Appended 50 records, seqs:", seqs[0], "...", seqs[-1])

    segment_files = sorted(glob.glob(os.path.join(test_dir, "wal.*")))
    print(f"Segment files created: {len(segment_files)}")
    assert len(segment_files) > 1, "rotation never fired: test would be vacuous"

    replayed = list(wal.replay())
    replayed_seqs = [seq for seq, _ in replayed]
    replayed_payloads = [payload for _, payload in replayed]
    print(f"Replayed {len(replayed)} records; seqs in order: {replayed_seqs == list(range(50))}")
    assert replayed_seqs == list(range(50))
    assert replayed_payloads == records
    print("Assertion passed: replay across multiple rotated segments returns all 50 records,")
    print("in original order, byte-identical to what was appended.")

    # Crash recovery: a FRESH instance on the SAME directory (simulated restart)
    # must continue numbering seq correctly, not collide with prior records.
    wal2 = WriteAheadLog(test_dir, segment_max_bytes=200)
    new_seq = wal2.append(b"post-restart-record")
    print(f"After simulated restart, next seq assigned: {new_seq} (expected 50)")
    assert new_seq == 50

    # Corruption handling: truncate the last segment mid-record (a torn write)
    # and confirm replay stops cleanly at the corruption point.
    last_segment = sorted(glob.glob(os.path.join(test_dir, "wal.*")))[-1]
    with open(last_segment, "r+b") as f:
        f.seek(0, os.SEEK_END)
        f.truncate(max(0, f.tell() - 3))
    wal3 = WriteAheadLog(test_dir, segment_max_bytes=200)
    replayed_after_corruption = list(wal3.replay())
    print(f"After truncating the last segment (simulated torn write), replay returned "
          f"{len(replayed_after_corruption)} valid records (expected 50: the corrupted "
          f"51st record is correctly excluded, not returned as garbage).")
    assert len(replayed_after_corruption) == 50

    shutil.rmtree(test_dir)


if __name__ == "__main__":
    main()

Output (actually executed with python3):

Appended 50 records, seqs: 0 ... 49
Segment files created: 9
Replayed 50 records; seqs in order: True
Assertion passed: replay across multiple rotated segments returns all 50 records,
in original order, byte-identical to what was appended.
After simulated restart, next seq assigned: 50 (expected 50)
After truncating the last segment (simulated torn write), replay returned 50 valid records (expected 50: the corrupted 51st record is correctly excluded, not returned as garbage).

At segment_max_bytes=200, 50 small records genuinely force 9 separate segment files, confirming rotation actually fired rather than being an untested code path. Replay across all 9 segments returns exactly the 50 original records, in order, byte-identical to what was appended. The restart test confirms seq bookkeeping survives a process restart (resumes at 50, not 0). The truncation test is the deliberately non-vacuous corruption check: physically chopping bytes off the last segment (simulating a crash mid-fsync) and confirming replay still returns exactly the 50 valid records, correctly excluding the truncated one rather than raising or returning garbage.

Trade-offs and pitfalls

• Common mistake: calling write() without fsync and assuming durability. This is the single most consequential gap for a WAL, since its entire point is a guarantee stronger than "the OS probably has it"; skipping fsync makes it no more crash-safe than an ordinary buffered file.
• fsync has a real, measurable per-call cost, which is why production WALs almost always batch multiple writes into one fsync (group commit) rather than fsyncing every append(); this implementation fsyncs per-append for clarity, and a senior answer should name group commit as the standard optimization, not treat per-append fsync as free.
• Common mistake: using a delimiter (like a newline) instead of a length prefix. A payload containing the delimiter byte sequence corrupts parsing; the length-prefix format sidesteps this, since replay always knows exactly how many bytes belong to the current record regardless of content.
• Rotation alone does not bound total growth, only per-file size; a production WAL needs old segments deleted once a checkpoint confirms they are no longer needed, left out here as an orthogonal concern from the WAL mechanics themselves.

Direct answer

Do not rebuild the existing department's star schema from scratch: keep it running, and introduce a compatibility view layer between its existing dimensions and a newly-built conformed version, so its current reports keep working unmodified while every new mart is built against the conformed dimensions from day one. Sequence the three new departments' marts in whichever order matches the bus matrix's widest-touching dimensions first, exactly as you would for a greenfield build, since from their point of view they are joining an enterprise warehouse that already has its shared dimensions defined.

Structured elaboration

What to do with the existing star schema. The existing department's fact table and dimensions almost certainly still work correctly for that department's own reports; the problem is only that its dimensions were never designed to be reused by anyone else. Do not touch the fact table. Build new, properly conformed versions of whichever dimensions the other departments will also need (typically customer and date), migrate the existing department's fact table to reference the new conformed dimension's keys, and leave everything else about that department's schema alone.

Introducing conformed dimensions without breaking existing reports. The mechanism that makes this safe is a compatibility view: create a view with the OLD dimension's name and column names, backed by the NEW conformed dimension underneath, so any report or dashboard query that has not been touched keeps running against the old names while the underlying table is now the shared one. Reports get migrated to query the conformed dimension directly (picking up any new attributes it offers) on the team's own schedule, not as a synchronized cutover, and the compatibility view is retired only once nothing depends on it anymore.

Sequencing the migration. For the three new departments, treat this the same way you would size a bus matrix for a greenfield enterprise build (see the sales-orders-first reasoning used when a company plans from scratch): whichever new department's mart shares the most dimensions with the others should be built first against the now-conformed dimensions, so its build validates that the conformed dimensions actually generalize before a second and third department also depend on them.

Worked example

A minimal, runnable illustration of the compatibility-view mechanism:

CREATE TABLE dim_customer_conformed (
    customer_sk   BIGINT PRIMARY KEY,
    customer_id   VARCHAR,
    customer_name VARCHAR,
    region_code   VARCHAR      -- new attribute the conformed dimension adds
);
INSERT INTO dim_customer_conformed VALUES
  (1, 'CUST-100', 'Ada Lovelace', 'EMEA'),
  (2, 'CUST-200', 'Grace Hopper', 'AMER');

-- The original department's dimension name and column names, preserved as a view
-- backed by the new conformed table underneath:
CREATE VIEW dim_customer_v1 AS
SELECT customer_sk, customer_id AS cust_id, customer_name AS cust_nm
FROM dim_customer_conformed;

-- The department's existing report, unmodified, still returns correct results:
SELECT cust_id, cust_nm FROM dim_customer_v1 ORDER BY cust_id;

Executed, the unmodified report query returns [('CUST-100', 'Ada Lovelace'), ('CUST-200', 'Grace Hopper')], exactly what it returned before the migration, while a new report written directly against dim_customer_conformed can immediately use the new region_code attribute the old schema never had.

Trade-offs and pitfalls

The most damaging mistake is treating this as a single cutover weekend: forcing every existing report to move to the new conformed dimension at once maximizes the chance something breaks in production with no fallback. The compatibility view exists precisely so migration can happen gradually, dashboard by dashboard, with the old and new dimensions correct and consistent with each other for as long as both are in use. The second common mistake is skipping the conformance work for a "small" attribute mismatch (say, the old dimension calls it cust_nm and everyone assumes it obviously maps to customer_name), which is exactly the kind of undocumented assumption that produces the four-different-customer-dimensions problem a badly-managed enterprise rollout tends to create.