Data Ingestion and Source System Integration Questions

Direct answer

The checks fall into four layers, each catching a different failure mode: schema validation catches structural surprises, null and range checks catch individually implausible values, referential-integrity checks catch records that reference something that does not exist, and volume or velocity checks catch a batch that is structurally fine but wrong in aggregate. Whether to quarantine or let-through-with-alert depends on blast radius: quarantine when a bad record could actively corrupt a downstream aggregate or join, alert-and-continue when the risk is a small, bounded amount of noise a human can review after the fact.

Structured elaboration

Schema validation

• Confirm the batch's fields, types, and required-ness match what the mapping expects, catching a source-side structural change (a renamed, removed, or newly-required field) before it reaches anything downstream.

Null and range checks

• Flag nulls in fields the mapping declares required, and flag values outside a plausible range (a negative count, a date far in the future, an amount several orders of magnitude larger than anything previously seen from this source).
• Calibrate the range against this specific source's own history, not a generic global assumption, since "plausible" for one source can be wildly different from another.

Referential-integrity checks

• Confirm foreign-key-style references (an order referencing a customer ID, an event referencing a known event type) actually resolve against data you already have, catching a partial or out-of-order delivery before it creates an orphaned record downstream.

Volume and velocity checks

• Compare this batch's row count and arrival timing against the source's own recent history; a batch that is 90% smaller or 10x larger than typical is very often a signal of an upstream problem even when every individual row in it looks perfectly valid.

Quarantine versus alert-and-continue

• Quarantine the batch when a failure could actively corrupt something downstream: a referential-integrity violation that would create an orphaned join, or a schema mismatch that could silently mis-populate a column.
• Alert and let it through when the failure is a small, boundable number of individually-suspect rows a human can review without materially affecting the aggregate: a handful of out-of-range values in an otherwise-normal batch.
• Either way, the decision should be a documented threshold, not a judgment call made fresh under pressure during an actual incident.

Worked example

A partner's royalty-reporting feed (music labels, ad networks, payment processors) lands daily and needs to reconcile with a financial ledger, per the partner's own service-level agreement (SLA). Schema validation confirms every expected field is present in the day's file. Referential-integrity checks confirm every reported transaction ID exists in the internal catalog; on a day where the partner's own file references 40 transaction IDs that do not exist in the catalog (a symptom of the partner's file arriving before the catalog sync it depends on), the batch is quarantined rather than loaded, since silently loading orphaned royalty records would corrupt the financial reconciliation the whole pipeline exists to support. A separate, milder finding, three records with a royalty amount 2x anything previously seen from this partner but still schema-valid and referentially sound, triggers an alert and loads anyway, flagged for a human reviewer to confirm with the partner rather than blocking the whole day's reconciliation over three rows.

Trade-offs & pitfalls

• A quarantine policy is only as good as what happens to the quarantine queue; an unmonitored quarantine store just delays data loss by however long it takes someone to notice nothing has moved.
• Range checks calibrated too tightly against a short history will flag entirely legitimate seasonal spikes (a holiday sales surge, a partner's own promotional event) as anomalies, training people to ignore the alerts, which defeats the purpose.
• Referential-integrity checks assume the "known good" side of the reference is itself trustworthy and current; if the catalog sync this depends on is itself lagging, you can quarantine perfectly valid records purely because your reference data has not caught up yet.
• Do not let volume/velocity checks be the ONLY safety net; a batch can be exactly the expected size and still be entirely wrong in content (every row swapped or corrupted in some structural way volume checks cannot see).

Direct answer

Without log access, you cannot get true change-data-capture, so the honest options are periodic full extraction, timestamp-based incremental polling, or a full-table hash/diff, and the choice among those three is driven by how the load impact on the primary database and the need for delete-detection trade off against each other. A low-impact replication approach (batched exports or reading from a read replica rather than the primary) is usually the right way to reconcile "the source can barely tolerate any extra load" with "we still need periodic incremental extraction."

Structured elaboration

Why log-based CDC is off the table

• Log-based CDC needs either a readable write-ahead log or database-vendor support for logical decoding; a legacy system with neither leaves you with query-based detection instead, which is fundamentally less complete (it cannot see a delete unless the row is soft-deleted with a flag, and it cannot see an update-then-revert that happened between two polls).

Latency without log access

• None of the three query-based options below gets you log-based CDC's sub-second latency; the best you can do is push the poll interval as tight as the load constraint on the primary (or its replica) allows, so latency here is a direct trade-off against load impact, not a separate dial you can turn for free.
• "Acceptably low" has to be defined against the actual freshness requirement, not assumed: an hourly poll is a reasonable latency budget for a daily-dashboard need, but it is not low-latency in any absolute sense and should not be described as near-real-time.

Detecting changes without log access

• Timestamp-based polling: query WHERE updated_at >= last_checkpoint (not a strict >), the simplest option, but only works if every table reliably maintains an accurate updated_at, and it structurally misses hard deletes. A strict > comparison against a checkpoint set to the previous poll's MAX(updated_at) can silently and permanently drop a row that shares that exact timestamp but was not yet committed when the previous poll ran; using >= re-pulls that boundary row on the next poll instead, which the idempotent upsert on the destination absorbs harmlessly.
• Full-table hash/diff: compute a checksum per row (or per chunk of rows) and compare against the previous run's checksums to detect changes, including deletes (a row present last time and absent this time); more source load than timestamp polling, but works when there is no trustworthy timestamp column at all.
• A hybrid: timestamp polling for inserts and updates, plus a periodic (daily or weekly) full reconciliation pass to catch deletes and drift the incremental method missed.

Reducing load on the primary

• Read from a read replica if one exists, so extraction competes for I/O with reporting queries, not with the primary's own transactional workload.
• Batch the extraction into off-peak windows and cap the query's own resource usage (statement timeouts, row-limit chunks) rather than pulling the entire table in one long-running query.
• If neither a replica nor an off-peak window is available, negotiate a lower query frequency with the source-owning team rather than silently degrading the primary's performance to hit your own freshness target.

Initial snapshot

• The first pull has to be a full extraction regardless of the ongoing method, chunked by primary key range or a stable ordering column so it can be paused and resumed without re-reading rows already captured, and scheduled during the lowest-traffic window you can negotiate.

Ordering and resume semantics

• Without log-based ordering, use a monotonically increasing column (an auto-increment ID or the same reliable timestamp) as your resumable cursor, checkpointed after each successfully-processed chunk.
• On a connector failure mid-run, resume from the last durable checkpoint; because each chunk's extraction is naturally idempotent (re-querying the same range returns the same rows, absent further changes), a partial re-pull of the last chunk causes no harm.

Handling schema changes anyway

• Without DDL events from a log, you have to detect schema changes defensively: compare the observed column set against an expected schema on every pull and alert rather than silently ingest under a stale assumption.

Worked example

A 15-year-old inventory system on a database with no accessible write-ahead log (WAL), only a read replica three minutes behind the primary, needs daily incremental sync plus reliable delete detection for a monthly reconciliation. The connector polls the replica (never the primary) hourly with WHERE updated_at >= last_checkpoint for inserts and updates, chunked by primary key range with the checkpoint persisted after each chunk so a mid-run failure resumes cleanly. Because updated_at cannot reveal a hard delete, a separate weekly job runs a full primary-key diff between the replica's current key set and the destination's, and any key present in the destination but absent from the source is marked deleted. This combination gets hourly visibility into changes, comfortably inside the stated daily-sync requirement (not near-real-time in any absolute sense, since the poll interval itself is the latency floor here), with essentially zero load on the primary, at the cost of deletes taking up to a week to be reflected, a trade-off made explicit and agreed with the downstream consumers rather than left as a silent gap.

Trade-offs & pitfalls

• Trusting updated_at without verifying every write path actually sets it (an ORM might, a bulk-import script might not) is one of the most common silent-data-loss bugs in exactly this pattern; audit it against the actual application code, not just the schema documentation.
• A full-table hash/diff is I/O-heavy at any real scale; if the source genuinely cannot tolerate it even from a replica, delete detection may simply have to be a known, accepted gap rather than something you force through anyway.
• Chunked extraction by primary key range assumes IDs do not get reused after a hard delete; if they can be, a chunk boundary can silently skip a row that was deleted and re-inserted with a reused key between polls.
• Do not let "we cannot get log access" become "so we will poll the primary as hard as we want"; the entire premise of this design is respecting a constraint the source team gave you, and violating it quietly is how a connector gets its access revoked.

Direct answer

The pipeline needs to treat the SFTP (SSH File Transfer Protocol) transfer, the validation, and the commit into the partitioned dataset as three distinct, separately-verifiable steps, because collapsing them (writing directly into the final partitioned location as bytes arrive) is exactly what turns a partial or corrupted transfer into bad data a downstream query can already see.

Structured elaboration

Transfer and verification

• Pull the file from SFTP into a staging location first, never directly into the final partitioned dataset, so a failed or partial transfer never becomes visible to a downstream reader.
• Verify the transfer completed correctly using a checksum: if the SFTP source can provide one (many partner feeds publish a companion checksum file), compare against it; otherwise compute your own checksum on the staged file and compare its size against what the source reports, at minimum.

Schema and checksum validation

• Validate the staged file's schema against what you expect before touching the partitioned dataset at all: column presence, types, and a row-count sanity check against recent history for this same daily drop.
• A checksum mismatch or a schema-validation failure halts the pipeline at this stage, before anything downstream is touched, rather than partially loading a suspect file.

Atomic commit

• Write the converted, partitioned Parquet output to a temporary path, then atomically move or rename it into the final partition location only once the write is fully complete and verified, so a reader never sees a partially-written partition.
• On object stores without a true atomic rename across prefixes, achieve the same effect by writing to a versioned or staged prefix and updating a pointer (a manifest or a catalog entry) only after the write finishes, rather than writing in place.

Partitioning strategy

• Partition by the file's own delivery date, since that is the natural, stable grain at which this daily file arrives, and keep the partition scheme simple and predictable so downstream consumers can reason about "yesterday's partition" without needing pipeline-internal knowledge.

Metadata-catalog updates

• Register the new partition with your catalog (Glue, Hive metastore, or equivalent) only after the atomic commit succeeds, so the catalog and the actual data on disk never disagree about what partitions exist.

Retry and backoff

• Retry a failed SFTP transfer with backoff, since transient network issues on a large 100 GB transfer are common; but do not retry a checksum or schema-validation failure blindly, since that is very likely a genuine problem with the source file that a retry will just reproduce identically.

Cost

• Compress and use a columnar format (Parquet) for the final dataset to reduce both storage cost and downstream query cost; keep the raw staged copy for a bounded retention window (long enough to support reprocessing after a bug fix) rather than indefinitely, since 100 GB/day of uncompressed staged copies adds up fast.

Worked example

A 100 GB file lands on the SFTP endpoint overnight. The transfer job pulls it into a staging bucket, verifying the transferred byte count and checksum against the source's own manifest; a mismatch (a partial transfer from a dropped connection) triggers an automatic retry of just the transfer, not the whole pipeline. Once verified, a validation pass checks the file's schema against the expected columns and confirms the row count falls within the normal range for this daily drop (catching, for example, a file that is unexpectedly only 10% of its usual size, a signal the partner's own export may have failed partway). The conversion job then writes partitioned Parquet output to a temporary prefix, and only after that write completes and is itself verified does a final atomic operation move it into the dated partition and register it with the metadata catalog, so a downstream query against "today's partition" either sees the complete, correct data or does not see the partition at all, never a partial one.

Trade-offs & pitfalls

• Writing directly into the final partitioned location "to save a copy step" is the single most common way this kind of pipeline produces a partial-partition incident; the staging-then-atomic-commit pattern costs extra storage and time but is what actually prevents that class of failure.
• Retrying a checksum failure automatically, without distinguishing it from a transient transfer failure, wastes time re-downloading a file that is going to fail the checksum identically every time if the source file itself is genuinely corrupted.
• Keeping the raw staged copy indefinitely "just in case" quietly becomes a real cost line at 100 GB/day; define and enforce a retention window deliberately rather than letting it default to forever.
• A catalog update that happens BEFORE the underlying data write is fully durable is a subtle but real bug: a reader could see a partition registered in the catalog moments before the actual files are consistently readable from the object store, depending on the store's consistency model.

Direct answer

Log aggregation collects unstructured or semi-structured text output that applications and infrastructure already emit (request logs, error traces, system events) using a lightweight agent running alongside the source, and ships it to a central broker for downstream processing. It differs from a structured event stream in one fundamental way: a log line is written for a human to read, with no guaranteed schema, while a structured event is deliberately designed as data, with a defined shape a consumer can rely on; that difference shapes everything about how you have to handle log ingestion.

Structured elaboration

What is typically collected

• Application logs (request/response traces, error stack traces, application-level warnings), system logs (OS and infrastructure-level events), and access logs (web server or load balancer request records).

Common collection agents

• Filebeat: a lightweight, low-resource agent focused specifically on tailing log files and forwarding them, commonly paired with the Elastic stack.
• Fluentd (and its lighter sibling Fluent Bit): a more general-purpose log collector and router with a large plugin ecosystem for routing to many different destinations.
• Logstash: a heavier-weight collector with built-in parsing and transformation capability, often used when logs need real processing before landing, not just forwarding.

How a log stream differs from a structured event stream

• Schema: a structured event has a defined, versioned shape a consumer can validate against; a log line is free text whose format can change any time a developer changes a log statement, with no contract and often no warning.
• Intent: a log line is written primarily for a human debugging an issue in the moment; a structured event is written primarily for a downstream SYSTEM to consume programmatically. Extracting reliable structured data out of logs (parsing timestamps, fields, and values out of free text) is a real engineering task in a way that reading an already-structured event is not.
• Volume characteristics: logs are often far higher-volume and noisier than a purpose-built event stream, since every application already emits a lot of log output that was never designed with ingestion cost in mind.

Handling log rotation

• A log agent needs to track its read position (an inode and offset, not just a filename) so it correctly continues reading a rotated file's new incarnation without either re-reading already-shipped lines or silently skipping lines written in the brief window around rotation.

Handling multi-line log entries

• A single logical log entry (a stack trace spanning many lines, for example) needs to be recognized and grouped correctly by the agent before shipping, using a pattern (a line starting with a timestamp signals a new entry, everything after it until the next timestamp belongs to the same entry) rather than treating every physical line as a separate record, which would shred a stack trace into meaningless individual fragments.

Handling backpressure

• When the central broker cannot keep up with the volume an agent is producing, the agent needs a bounded local buffer (disk-backed, ideally, so a restart does not lose buffered lines) and a policy for what happens when that buffer fills: block and slow the source application (risky, since logging should rarely be allowed to affect application performance), drop the oldest or newest lines (a real, explicit data-loss decision that should be a deliberate choice, not a default nobody decided on), or sample.

Worked example

An application server emits a Java stack trace as 15 separate physical lines when an exception occurs. A naive line-by-line log shipper would forward each of those 15 lines as 15 unrelated log entries, destroying the ability to read the actual stack trace as a coherent unit downstream. Configuring the collection agent (Filebeat or Fluentd, either supports this) with a multi-line pattern, "a new entry starts only on a line matching a timestamp pattern, otherwise append to the previous entry," correctly reassembles the full 15-line trace into one shipped log entry, matching how a human reading the raw file would naturally interpret it.

Trade-offs & pitfalls

• Treating log data as if it had the same reliability as a structured event stream is a common mistake; a developer can change a log line's format in a routine code change with zero awareness that a downstream parser depends on its exact shape, and that parser will then silently misparse or drop fields with no warning from anywhere in the chain.
• Getting multi-line grouping wrong in either direction is costly: too aggressive grouping merges genuinely separate log entries into one garbled record, too conservative grouping shreds a single logical entry (like a stack trace) into fragments; test the pattern against real production log samples, not synthetic ones.
• A backpressure policy of "just buffer more" without a bound eventually exhausts local disk on the source host, which then risks affecting the actual application, the opposite of what a good logging design should ever do.
• Retention and cost for raw log volume can dwarf structured event costs at real scale, since logs are typically far higher-volume and lower information-density per byte; a deliberate retention and sampling policy matters more here than for most other source types.

Direct answer

Pull is you initiating contact with the source on your own schedule, for example polling a REST API or fetching a file drop; push is the source initiating contact with you, for example a webhook call or a message it publishes to a stream you subscribe to. Pull gives you full control over pacing and load on the source, at the cost of built-in latency between when something happens and when you notice. Push gives you near-real-time delivery, at the cost of needing to be reliably available to receive it and coordinate with whatever retry behavior the source uses when you are not.

Structured elaboration

Pull

• Concrete examples: polling a REST endpoint every N minutes, fetching a nightly file drop via SFTP (SSH File Transfer Protocol) or from S3, running a scheduled SQL query against a source database.
• Latency: bounded below by your polling interval; a change occurring right after a poll will not be seen until the next one.
• Throughput and source load: you control the request rate directly, which is good for respecting a source's capacity, but a poorly tuned interval can either waste calls when nothing changed or lag badly when a lot changed.
• Operational complexity: you own scheduling, checkpoint tracking, and retry logic; the source does not need to know or care about you.
• Failure modes: a missed poll (your job did not run) simply gets caught on the next poll if your extraction is incremental; the risk is a silent scheduler failure going unnoticed for a while.

Push

• Concrete examples: an inbound webhook call from a payment processor, a message a source publishes to a queue or event stream you consume.
• Latency: near-real-time, since the source notifies you the moment something happens rather than you having to ask.
• Throughput and source load: the source decides the rate, which can spike unpredictably; you need to be able to absorb bursts without falling over.
• Operational complexity: you must run a reliably-available receiver (an endpoint or a consumer), and you inherit whatever the source's own retry and ordering guarantees are, or are not.
• Failure modes: if your receiver is down when a push arrives, you depend entirely on the source retrying it; some sources retry aggressively, some drop the event, and a few offer no redelivery at all.

How to choose

• Freshness requirement: sub-minute or real-time needs generally rule out pure polling.
• Source support: you cannot choose push if the source does not offer it; not every system has webhooks or a stream to subscribe to.
• Control versus availability: pull lets you throttle yourself to protect a fragile source; push demands your receiver be highly available, since you cannot control when the source sends.
• Operational maturity: a small team with no on-call receiver infrastructure may be better served starting with pull, even at some freshness cost, and moving specific sources to push as reliability matures.

Worked example

A team gathering training data for a model has three needs: (a) a large historical backfill of past user actions, (b) online feature updates that must reflect a user's most recent action within seconds, and (c) periodic collection of new human feedback labels. For (a), pull is the only sensible choice: there is no "event" to push, it is a bulk historical extraction, typically against an API or a warehouse export. For (b), push is close to mandatory, since seconds-level freshness is well below what any reasonable polling interval could deliver without hammering the source. For (c), pull on a modest schedule (hourly or daily) is usually sufficient, since new labels do not need to reach the training pipeline instantly, and a scheduled pull is far simpler to operate than standing up a webhook receiver just for this.

Trade-offs & pitfalls

• A common mistake is polling far too aggressively "to reduce latency," which just moves the bottleneck onto the source's rate limits without meaningfully improving freshness once you are polling faster than data actually changes.
• Push without idempotent handling on your side is a duplicate-processing incident waiting to happen, since almost every push-based source will retry a delivery it believes may have failed, even when you actually received and processed it.
• Do not assume push is strictly better because it sounds more modern; a source with unreliable delivery and no replay mechanism can lose data silently in a way a well-designed poll with checkpointing cannot.
• Micro-batching (short, frequent pulls, seconds to low minutes) is a real middle ground worth naming explicitly: it gets you most of push's freshness without needing a highly-available receiver.