Amazon Data Engineer Interview Preparation Guide - Junior Level

Direct answer

For facts that occur at two different natural grains (session-level and event-level), the cleanest option is usually two separate fact tables, one per grain, sharing conformed dimensions; a single consolidated multi-grain table with nulls works but complicates every query with grain-awareness logic, and cross-grain analysis is best done by joining/aggregating the two separate tables at query time rather than forcing one physical table to serve both.

Structured elaboration

• Separate fact tables (recommended default): session_fact(session_id, user_key, date_key, session_duration, page_count) and event_fact(event_id, session_id, user_key, date_key, event_type), each internally consistent at its own grain, avoiding the aggregation-logic bugs that come from mixing grains in one table.
• Consolidated multi-grain table with nulls: one table where session-level rows have event-specific columns NULL and vice versa. This avoids a second table but means every query MUST filter by a grain-indicator column first or risk silently mixing grains in an aggregation (summing a measure that only exists at one grain, inflated by NULL-padded rows from the other grain if the filter is forgotten).
• Event table with derived aggregates: store only the finest grain (events) and derive session-level metrics via a view or materialized aggregate on top, which avoids grain duplication entirely but pushes session-level query cost onto an aggregation over potentially many events per session.
• Cross-grain analysis without double counting: whichever approach is used, cross-grain analysis (comparing session count to event count) must never join the two grains directly on a shared key without an explicit aggregation step first (for example, joining event_fact to session_fact directly and summing a session-level measure would multiply it by the number of matching events).

Worked example

"Average events per session" needs event_fact aggregated to session grain first (COUNT(*) GROUP BY session_id), THEN averaged, not a direct join of session_fact to event_fact followed by a naive average, which would double count session-level attributes once per matching event row. With two separate fact tables, this two-step aggregate-then-analyze pattern is natural; with a single consolidated table, the same discipline is needed but is easier to accidentally skip since both grains superficially live in "one table."

Trade-offs and pitfalls

The consolidated multi-grain table's appeal (one table, seemingly simpler) is usually outweighed by the ongoing risk of a grain-unaware query silently mixing levels; two clean, single-grain fact tables cost a bit more extract, transform, load (ETL) and dimension-conformance discipline but make grain-mixing bugs much harder to write accidentally, since each table only ever means one thing.

Direct answer

The speed layer owns the window of data the batch layer has not yet finished reprocessing, typically the last partial day or the last few hours, while everything older than the batch layer's most recently completed run is served exclusively from batch output, because batch has already recomputed it correctly. The handoff is a watermark: once batch advances that watermark forward, the speed layer's corresponding window is dropped and superseded, not merged value-by-value, because the entire reason the speed layer existed for that window was that it had not yet been through the authoritative computation.

Structured elaboration

The speed layer's job. Give an approximate, low-latency view of data that has not yet gone through the accurate batch recompute. It tolerates being wrong in small ways, duplicate counts, out-of-order updates, because it will be superseded once batch catches up.

The batch layer's job. Perform a full, authoritative recompute over a complete time window on a schedule, correcting for late-arriving and out-of-order events and enforcing deduplication. Its output for a given period becomes authoritative the moment the run completes.

The boundary, concretely. Define a watermark $W$ equal to the timestamp through which the most recent batch run is complete. Any query for time before $W$ reads only from batch output. Any query for time at or after $W$ reads from the speed layer. When the next batch run finishes and advances $W$ forward, the speed layer's now-covered range is simply discarded and replaced by batch's output for that range, not reconciled row by row, because batch is strictly more correct for anything it has already covered.

Why no per-row reconciliation is needed. The speed layer's numbers for a given window are never "corrected" against batch; they are superseded wholesale once batch's watermark passes that window. This is what keeps the two layers from needing complex agreement logic between them.

Delivery guarantees differ by layer. The speed layer can tolerate at-least-once or approximate counting because its output is provisional by design. The batch layer needs deduplication and exactly-once-equivalent accounting because its output is what actually persists.

flowchart LR
  E[Event stream] --> Sp[Speed layer: stream processor]
  Sp --> Fv[Fast view: low-latency store]
  E --> Rs[Raw storage: append-only]
  Rs --> Bl[Batch layer: full recompute]
  Bl --> Av[Authoritative view]
  Fv --> Serve[Serving layer]
  Av --> Serve
  Bl -->|advances watermark W| Sp

Worked example

Batch runs nightly starting at 02:00, covering the prior 24 hours, and takes 3 hours to complete due to full reprocessing and joins, so its watermark $W$ only advances to "yesterday 00:00" once the run finishes around 05:00:

If a dashboard is queried at 15:00 the same day, the speed layer is the only source for everything from $W$ to now:

Everything before that 39-hour window comes from last night's batch output. Once tonight's 02:00 to 05:00 run completes, $W$ advances to "today 00:00," and the speed layer's live window shrinks back down to just the hours elapsed so far today.

Trade-offs & pitfalls

If a batch run fails or runs long, the speed layer's provisional window grows and stays uncorrected for longer, since nothing has caught up to supersede it; this is why a maximum-staleness service-level agreement (SLA) on the batch run, with alerting if it's missed, matters as much as the batch logic itself. A common wrong turn is trying to reconcile or merge speed and batch numbers cell by cell instead of a clean cutover at the watermark; that doubles the reconciliation logic for no benefit, since batch is always strictly more correct for anything it has already covered. The other well-known cost of this architecture is maintaining two independent codepaths, a streaming job and a batch job, that must implement the same business logic and never drift apart; this exact duplication is what kappa architecture removes by reprocessing history through the same streaming code instead of a separate batch pipeline.

Direct answer
When postmortem action items routinely go uncompleted, the fix is almost never 'try harder to remember them,' it's process and tooling that makes overdue items visible automatically, assigns real ownership, and periodically forces a decision (do it, reschedule it, or explicitly drop it) rather than letting items sit in limbo indefinitely.

Structured elaboration

• Every item gets a taxonomy, not just a description. Categorize each as a code change, a test, a runbook update, or a policy change; this matters because 'we fixed it' claims are easy to make vaguely but hard to fake once the category demands a specific, checkable artifact (a merged pull request, a passing test, an updated document link).
• Automated tracking, not manual follow-up. Integrate action items with the team's existing ticketing system rather than a document nobody revisits, and set up automatic escalation when an item passes its due date, for example flagging the owner's manager after a defined grace period.
• A regular review cadence. A recurring, lightweight review (monthly, say) of all open action items across recent postmortems, where each overdue item gets an explicit decision: still committed with a new date, explicitly deprioritized with a documented reason, or escalated because it's blocked.
• Tie urgency to real signal where relevant. For teams with formal reliability targets, an action item addressing a gap close to breaching its service-level objective or eating into an error budget should visibly outrank a lower-urgency item, rather than all items being treated as equally important by default.
• Verification, not just closure. An item marked 'done' should have some evidence attached (a passing test, a dashboard showing the metric improved), not just a status flip, since a false-positive 'closed' item is worse than an honestly still-open one.

Worked example
A team's postmortem tool shows 40% of action items are still open past their original due date, with no visibility into why. After the fix: items are tagged by type (12 code changes, 8 tests, 15 runbook updates, 5 policy changes), each syncs to the team's existing ticket tracker with an owner and due date, and any item 30 days overdue auto-escalates to the owner's manager with a link back to the original postmortem. A monthly 15-minute review meeting looks only at the overdue list, and each item gets one of three outcomes: recommitted with a new date, explicitly dropped with a one-line reason recorded (so it doesn't silently reappear as a mystery six months later), or flagged as blocked and escalated further. Within one quarter, the overdue rate drops from 40% to under 10%, not because engineers suddenly became more diligent, but because the system now makes an overdue item visible and forces a real decision instead of letting it fade quietly.

Trade-offs and pitfalls
The most common failure is adding tracking overhead without addressing WHY items go uncompleted in the first place, usually because they were never actually prioritized against regular roadmap work and got silently deprioritized without anyone saying so. Tracking makes that silent deprioritization visible, which is uncomfortable but necessary; the alternative is items that look committed on paper but were never really going to happen.

Direct answer

Move to a dedicated warehouse when ad-hoc analytical queries start measurably hurting the production database's transactional performance, when the same numbers are being computed slightly differently in different spreadsheets, or when reporting needs data joined across sources the production database does not have (a payments processor, a support tool, a marketing platform). Start with the simplest useful version: a small set of tables that are periodically copied out of production into a separate database or a managed cloud warehouse, denormalized just enough to answer the handful of reports people actually run today, not a fully modeled Kimball bus architecture with conformed dimensions across every future business process.

Structured elaboration

Signal one: production impact. A heavy analytical query (a full table scan for a monthly report, say) run directly against the database serving live user traffic can degrade transactional latency for real users; if analysts are being asked to "only run reports at night" or engineers are seeing production incidents traced to a report someone ran, that is a concrete, observable signal, not a vague sense that things feel slow.

Signal two: inconsistent numbers. Once more than one person is computing the same metric independently (one analyst's spreadsheet formula, another's ad-hoc query), small differences in filtering or date handling silently produce different answers to "what was our revenue last month," and nobody notices until two answers are compared in the same meeting. This is the earliest, cheapest form of the exact conformance problem later covered by dimension conflicts across marts; catching it before it compounds is far cheaper than the reconciliation project.

Signal three: joining across sources. Once a report needs to combine production order data with a separate support tool's ticket data and a third-party payment processor's transaction data, there is no single production database to query against anymore; some place has to receive copies of all three and let them be joined together, which is the core job a warehouse exists to do.

The simplest version to build first. Do not start with a full dimensional model. Start with a small, straightforward extract-and-load process (even a scheduled job that copies a handful of production tables into a separate database or a managed cloud warehouse on a nightly cadence) and let analysts query those copies directly, denormalized or lightly modeled, for exactly the reports people already run. Introduce actual dimensional modeling (declared grain, a real date dimension, slowly-changing-dimension handling) only once a second or third report reveals that ungoverned ad-hoc structure is producing inconsistent answers or is too slow to maintain by hand, which is the point at which the methodology and system-design questions the rest of this topic covers actually become relevant.

Worked example

A single unindexed analytical query scanning a 10-million-row production orders table for a monthly report can hold a lock or consume enough I/O bandwidth to add hundreds of milliseconds to unrelated transactional queries hitting the same table concurrently; at a company processing customer-facing checkout requests against that same table, a delay large enough for customers to notice during checkout is the concrete, business-visible cost of skipping a warehouse, not an abstract inefficiency. That single observation, "a report degraded checkout latency," is usually the moment a 20-person startup's engineering leadership actually approves the investment, well before any of the modeling-methodology questions in this topic become the operative concern.

Trade-offs and pitfalls

The most common mistake at this stage is over-building: reaching for a full Kimball-style bus architecture, multiple conformed dimensions, and Type 2 slowly changing dimension (SCD) history tracking before there is more than one or two reports that need any of it wastes engineering effort the startup does not have to spare, and most of that early investment will be redesigned anyway once real reporting needs are better understood. The opposite mistake, waiting until the production database is visibly struggling before doing anything, is also common and more expensive to unwind, since by then inconsistent numbers have usually already reached several audiences and eroded trust in whichever spreadsheet or dashboard people were relying on.

Direct answer

Ownership should follow causal control, not who asked for the metric. A number that only one team's actions actually move belongs to that team as a leading indicator. A number that several teams jointly move needs to be treated as a shared outcome with exactly one canonical definition that everyone points to, not each team computing its own version of 'the same' number.

Structured elaboration

1. Decide ownership by who controls the number

Ask: if this metric moved tomorrow, whose decisions would most plausibly explain it? If the answer is one team, it's team-owned. If the honest answer is 'several teams, depending on the week,' it's a shared outcome metric and needs shared governance, not a single team's dashboard.

2. Give every shared metric one canonical definition

Store the computation (the query or transformation logic) in one place, documented with an owner, a last-updated date, and the exact filters and date logic used. Any dashboard or report showing that metric should read from that canonical source, not recompute it independently.

3. When two teams report different numbers, reconcile, don't debate

The canonical definition is the tiebreaker by default. If a mismatch appears, the fix is a reconciliation step: compare the two calculations side by side, find where the logic diverges (a different date window, a different filter, a stale cache), and correct the deviating one, or update the canonical definition itself if it turns out to be wrong. Either way, log the decision so the same disagreement doesn't restart from zero next quarter.

4. Put governance around who can change a shared definition

A shared metric's definition should not change because one team unilaterally decides a different cohort or window looks better. Route changes through a lightweight review involving everyone who reports on that metric, and version the definition so historical numbers can be explained if they shift after a redefinition.

Worked example

A dashboard spans data engineering, product, and marketing for a signup-to-paid-conversion initiative. Splitting ownership this way keeps the dashboard honest:

When marketing's report shows a different sign-up count than the shared dashboard, the reconciliation step finds that marketing's number excluded a promo-code cohort by mistake. The canonical query is correct; marketing's ad hoc report is fixed to match it, and the discrepancy is logged so the next person who notices a mismatch can find the resolution instead of reopening the debate.

Trade-offs and pitfalls

• Centralizing every metric, including team-level leading indicators, slows down the teams that need to iterate quickly on their own signals; only the genuinely shared outcomes need the heavier canonical-definition process.
• Fully decentralizing shared outcome metrics guarantees mismatched dashboards eventually, which quietly erodes trust in the data even when the underlying numbers are directionally fine.
• A 'single source of truth' only works if using an alternate calculation is treated as a defect to fix, not a valid difference of opinion; without that enforcement, teams drift back to their own numbers within a quarter.
• Late-arriving corrections that change historical values need an explicit policy (do dashboards restate history, or only apply corrections going forward) decided in advance, or every correction becomes its own dispute.

Direct answer. A petabyte-scale time-series feature table needs a storage layout built around two access patterns at once: efficient time-range scans (recent-window queries for monitoring and drift checks) and point-in-time joins (retrieving the feature values that were TRUE as of a specific historical timestamp, for training-set construction without leaking future information). The layout that supports both is a columnar format (Parquet or a table format built on it), partitioned by time, with an entity-and-timestamp-aware indexing strategy layered on top so a point-in-time join doesn't have to scan every partition.

Structured elaboration.

• Partitioning strategy. Partition by a time column at a granularity that matches query patterns (daily is a common default for feature tables refreshed daily; finer granularity like hourly helps if features are updated intraday but multiplies file and partition count, so it trades query narrowness against small-file and metadata risk). Time partitioning directly serves both target access patterns: a time-range scan prunes to the relevant date partitions, and a point-in-time join for a training example dated T only needs to consider partitions up to and including T.
• File formats. Parquet (or a table format like Apache Iceberg or Delta Lake layered on Parquet) for the columnar benefits already covered: selective column reads (a training job for one model rarely needs every feature column), compression, and predicate pushdown on the timestamp and entity-id columns.
• Compaction. Feature pipelines are often incremental (a new partition lands daily or hourly), which risks the same small-files problem seen in any incremental columnar write. A scheduled or triggered compaction step that merges small incremental files into properly-sized ones keeps both the time-range scan and the point-in-time join fast, since both have to open fewer files per partition touched.
• Bloom filters / indexes. A point-in-time join is fundamentally an equality (or range) lookup on entity_id combined with a timestamp <= filter; a Bloom filter on the entity-id column lets the engine skip whole row groups or files that provably don't contain a given entity's rows, which matters enormously when a training set only touches a small fraction of entities in any one query.
• Metadata services. At petabyte scale with potentially years of daily or hourly partitions, listing and locating the right partitions and files becomes its own bottleneck; a metadata catalog (a table format's native metadata layer, or an external service backed by a catalog like the Hive Metastore or AWS Glue) that can answer "which files might contain entity X at or before time T" without a brute-force object-store listing operation is what keeps point-in-time joins tractable at scale.

Worked example. Consider a point-in-time join for a training set of 10 million (entity, label_timestamp) pairs against a feature table partitioned daily over 3 years (about 1,095 partitions). Without partition pruning, every one of the 10 million lookups risks scanning across all 1,095 partitions; with time partitioning and a catalog that can answer "which partitions have date <= label_timestamp" directly from metadata, the join only opens the partitions actually needed per label timestamp, typically a small handful clustered around each label's date, not the full history. Layering an entity-id Bloom filter on top further narrows which FILES within those partitions need to be opened at all.

Trade-offs & pitfalls. A common mistake is partitioning purely by ingestion time and joining on event or feature-effective time; if those two clocks diverge (late-arriving feature updates, backfills), a point-in-time join can silently miss or double-count rows unless the join logic and the partition-pruning logic agree on which timestamp is authoritative.

Direct answer
Reduce filler words by replacing the urge to fill silence with a deliberate pause, by slowing down at the start of an answer, and by preparing your first sentence in advance so you're not composing it live while also speaking it.

Structured elaboration

• Replace filler with silence. A half-second pause where "um" used to go feels awkward to the speaker but is barely noticeable to a listener, and it reads as more confident than a filler sound. Practice: the next time you feel a filler word coming, close your mouth instead.
• Slow down your opening sentence. Most filler happens in the first few seconds of an answer, while you're still figuring out what to say. Preparing (even mentally, for two seconds) how you'll start, before you start talking, removes most of the pressure that produces filler.
• Chunk your answer into a structure you can hold in your head (for example, "there are two things here: first... second..."), so you're not searching for what comes next mid-sentence.
• Record yourself and count filler words in a short answer. Most people are surprised by the number until they've heard it; the awareness alone reduces the habit over the next few attempts.
• Slow your overall pace, not just remove filler. Filler words often show up when speaking too fast for the thought to keep up; a slightly slower baseline pace gives your thinking time to catch up to your mouth.

Worked example
Before: "So, um, I think the, uh, main reason is like, you know, we didn't really have enough test coverage, if that makes sense."

After (pause instead of filler, front-loaded structure): "The main reason [pause] was insufficient test coverage."

Both convey the identical fact. The second version uses a brief pause where filler used to sit and states the point directly instead of hedging around it.

Trade-offs and pitfalls

• Eliminating filler entirely in the moment, under real pressure, is unrealistic; the realistic goal is a noticeable reduction, not zero.
• Overcorrecting into a rigid, over-rehearsed cadence can read as stiff; the goal is fewer filler words, not a scripted delivery.
• Practicing alone (recording yourself) tends to work faster than trying to notice it live, because live self-monitoring competes with the cognitive effort of actually answering the question.

Direct answer. A dashboard that had quietly grown from a few hundred thousand rows to several million was taking eight to twelve seconds to load; I traced it to a sequential scan feeding a nested loop join whose row-count estimate was badly stale, fixed it with a statistics refresh plus a targeted composite index, and got it back under a second.

Structured elaboration. Situation: an internal reporting dashboard, previously fast, had degraded gradually enough that no single deploy looked like the cause, and initial suspicion fell on the application layer rather than the database. Task: find the actual bottleneck and fix it without a major schema change, since the dashboard needed to stay available. Action: I ran EXPLAIN ANALYZE against the slow query directly (rather than trusting application-level timing alone) and found the estimated row count for the main filter was off by roughly two orders of magnitude from the actual, which had pushed the optimizer toward a nested loop join that made sense for the old, smaller estimate but was disastrous for the real, current volume; I confirmed a stale-statistics diagnosis by checking when statistics had last been refreshed on the affected table (a while before the data had grown), refreshed them, and added a composite index that matched the query's actual filter-plus-sort pattern, which the old, smaller table hadn't needed.

Result: dashboard load time dropped from the eight-to-twelve-second range to under one second, confirmed by re-running EXPLAIN ANALYZE and seeing the plan switch to an index scan with estimated and actual row counts now closely matched, plus a follow-up check a week later confirming the improvement held under real traffic rather than being a one-off measurement.

Trade-offs and pitfalls. The fix itself was straightforward once correctly diagnosed; the harder part was resisting the urge to guess at an application-layer cause first, since the symptom (a "slow page") didn't obviously point at the database until I actually looked at a real execution plan rather than just a wall-clock timing number.

Direct answer

The fix is not to force IAM (Identity and Access Management), service principals, and OAuth into one literal mechanism, since they're genuinely different primitives for different service families, but to consolidate identity at the layer above all three: a single source of truth for who a human or workload is, and a single, uniform way to express and audit what data they can access, translated into each managed service's native identity model at the edge. Every human identity federates in through one identity provider (IdP), every service identity is provisioned and tracked through one workload-identity registry regardless of which managed service actually implements it as an IAM role, a service principal, or an OAuth client, and every fine-grained data-access decision is expressed once, in a catalog or policy layer, rather than three times in three native permission systems.

Structured elaboration

• Separate "who" from "how each service enforces it." IAM roles, service principals, and OAuth clients are enforcement mechanisms native to particular platforms; they should all resolve back to the same underlying identity, a specific human or a specific named workload, rather than being three independent identities that happen to be used by the same actual system. A data pipeline job that reads from one managed service via an IAM role and writes to another via a service principal should be traceable, in an audit log, to one workload identity, not to two unrelated-looking credentials that require tribal knowledge to connect.
• Federate humans through one identity provider. Every managed service that supports federated login, and most modern managed data services support single sign-on (SSO) via Security Assertion Markup Language (SAML) or OpenID Connect (OIDC), should be configured against the same identity provider, so a human's access to any of them can be granted, reviewed, and revoked from one place, instead of separate local accounts per service that a leaver process has to remember to visit individually.
• Register every service or workload identity in one place, even though each platform implements it differently. Maintain a workload-identity registry, even a well-maintained spreadsheet-plus-process is better than nothing, though a proper catalog is better, that maps each IAM role, service principal, and OAuth client actually in use back to the workload or team that owns it, what data it's meant to touch, and when it was last reviewed. This is what makes fine-grained data access auditable across services instead of just within each one.
• Express fine-grained data-access policy once, at the data or catalog layer, not three times. Where the platform supports it, a data catalog or lakehouse permissions layer that sits above the individual managed services, define access at the level of "this identity can read this table, column, or row-filtered view" in one policy system, and let that system push or translate the resulting grants down into each managed service's native model, an IAM policy statement, a service-principal role assignment, an OAuth scope, rather than an engineer hand-authoring three separate native policies that can drift out of sync with each other.
• Centralize the audit trail. Each managed service emits its own access logs in its own format; route all of them into one log destination and normalize them to a common shape, identity, resource, action, timestamp, which managed service, so "who accessed what, across the whole platform" is a single query instead of three separate investigations that have to be manually cross-referenced by a human during an audit.

Worked example

A concrete platform with three managed services: a managed data warehouse with native IAM-role-based access, a managed orchestration service that uses per-workload service principals, and a third-party analytics tool integrated via OAuth.

• The data-engineering team's identity provider issues a group membership that federates SSO login into the warehouse's console and the orchestration service's console alike.
• The nightly ETL workload has one entry in the workload-identity registry, named for that workload, which maps to: an IAM role in the warehouse scoped to read three source tables and write one target table, a service principal in the orchestration service scoped to trigger this one pipeline definition, and an OAuth client-credential grant that lets the analytics tool read the target table read-only. All three are tagged with the same workload identifier in the registry.
• A quarterly access review pulls from the registry, not from three separate consoles, and asks one question per row, does this workload still need read access to these three source tables, rather than three separate reviewers checking three separate systems and possibly reaching different conclusions about the same underlying workload.
• When the workload is decommissioned, all three grants are revoked from the registry entry in one change, rather than relying on someone remembering all three places it touched.

Trade-offs and pitfalls

Trying to force a single literal mechanism, for example insisting everything use OAuth, instead of a single source of truth with per-service translation usually fails, because some managed services simply don't support every mechanism; the consolidation has to happen at the identity or policy layer, not by picking one enforcement primitive and mandating it everywhere. A workload-identity registry that isn't kept current becomes worse than no registry, because it gives false confidence during an audit while the real, drifted state lives in each service's native console; the registry needs an update step built into the actual provisioning workflow, not a separate manual bookkeeping task that's easy to skip under deadline pressure. Pushing policy down from one catalog layer to three native models is only as fine-grained as the least expressive native model; if one managed service can only grant table-level access while the catalog wants to express row-level filtering, the strategy has to either accept that gap explicitly, compensating with a view or proxy, or exclude that service from the unified fine-grained model, and pretending otherwise is a common way this kind of project quietly under-delivers. Centralizing audit logs from services with very different log formats and timestamp conventions is real integration work, not a checkbox; underestimating it is a common reason "one unified audit trail" ships as three dashboards side by side instead of one normalized view.

Change Data Capture is the practice of identifying exactly which rows changed (and how) in a source system, so a downstream consumer only has to process the delta instead of re-reading everything. There are three common ways to implement it, and they trade off latency, load on the source, and correctness very differently.

When each is the right call

• Log-based is the right default for a high-write OLTP database feeding near-real-time analytics: it doesn't add load to the source and it's the only one of the three that captures deletes and preserves transaction ordering. Debezium is the standard open-source tool here (into Kafka, then a sink connector or stream processor).
• Trigger-based shows up when you don't have log access (a managed database that doesn't expose its replication log, or a source you don't control at the infrastructure level) but do have DDL permission to add triggers.
• Timestamp polling is the pragmatic choice when the source has a reliable updated_at, deletes are rare or handled another way (a soft-delete flag), and near-real-time latency isn't required: it's the cheapest to build and operate, at the cost of missing hard deletes and adding poll-interval latency.

The trap: teams often reach for log-based CDC (Change Data Capture) by default because it's "the correct one," without checking whether they actually need deletes or sub-minute latency. Trigger-based and timestamp-based CDC are legitimate production patterns, not just fallbacks, when their specific correctness gaps don't matter for the use case.