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

The failure here is that a format change was invisible because the old parser tolerated it well enough to keep running, just on wrong data. The fix has two halves: catch it fast with contract validation that actively checks the SHAPE of incoming data against what you expect rather than passively accepting whatever parses, and design the rollout of any downstream fix so a corrected pipeline does not need to guess which rows were affected.

Structured elaboration

Catching it early: validate the contract, not just the parse

• A field "parsing" is a much weaker guarantee than a field having the RIGHT type, format, or range; a date field that silently starts arriving as MM/DD/YYYY instead of YYYY-MM-DD will often still parse as a string while producing garbage downstream.
• Run schema and value-shape checks at ingestion time: expected field presence, type, and a sanity check on value ranges or patterns, not merely "did JSON.parse succeed."
• A schema registry with enforced compatibility rules (for structured formats like Avro or Protobuf) rejects an incompatible producer change at write time rather than letting it through to be discovered downstream.
• For less structured sources (a partner's CSV or JSON feed with no registry), a lightweight contract test run against a small sample of every batch, before the full batch loads, catches the same class of problem.

Tracing it back to the specific source

• Every ingested record should carry provenance: which source, which connector version, and roughly when it was captured, so an anomaly can be traced back without guessing which of several sources is responsible.
• When a validation check fails, the alert should name the specific field, the specific source, and a sample of the offending records, not just "a data quality check failed somewhere."

Rolling the fix out safely

• Quarantine newly-arriving bad records rather than either blocking the whole pipeline or silently loading them; this buys time to fix the parser without an outright ingestion outage.
• Once a fix ships, replay the quarantined records through the corrected logic rather than asking downstream consumers to somehow know which historical rows were affected.
• Coordinate the rollout with the source-owning team if you have any relationship with them: even an informal heads-up channel materially shortens how long a format drift goes undetected the next time.

Worked example

A partner feed silently starts sending "1,234.50" (a thousands separator and no currency symbol) instead of the previously-agreed "1234.50" for an amount field. The old parser calls float() on the string, which raises on the comma, but the code catches that exception and defaults to 0.0 rather than failing loudly, so rows keep flowing with silently zeroed amounts. A contract check that asserts "amount is a positive number above some plausible floor for this source" would have caught the very first batch, because a wave of exact 0.0 values is an obvious outlier the moment it is checked, rather than a value that happens to parse. Once caught, the fix is to correct the parser to strip thousands separators, quarantine and reprocess the affected window of already-ingested rows from the raw landing copy (never from the already-corrupted downstream table), and add an explicit test fixture using this exact malformed string so the same regression cannot silently reappear.

Trade-offs & pitfalls

• A defensive try/except around parsing that silently defaults to a "safe-looking" value (0, null, an empty string) is precisely how this defect hides; a catch block should always increment a visible counter or emit a quarantine record, never both catch AND stay silent.
• Blocking the entire pipeline on any validation failure is not always right either: for a high-volume source, a small fraction of malformed rows quarantined with an alert is usually better than an outright halt, provided someone is actually watching the quarantine.
• Keep the raw, unparsed landing copy of every batch for at least as long as your reprocessing window; without it, "replay the corrected fix" is impossible once the source's own retention window has passed.
• Over-tight contract checks are their own failure mode: a validation rule so strict it rejects a legitimate but unusual value (a genuine large transaction, for example) just creates a different kind of silent data loss inside the quarantine queue if nobody reviews it.

Direct answer

Kafka Connect is a connector framework, not a connector itself: it gives you a plugin runtime, offset management, and a scaling model, but you still choose a source connector (JDBC, Debezium, or a vendor plugin) to run inside it. Debezium is a log-based change-data-capture connector, most often deployed as a Kafka Connect source plugin, that reads a database's transaction log directly. AWS DMS (Database Migration Service) is a managed, standalone replication service that does its own log reading and its own delivery, with no dependency on Kafka at all. The practical choice usually comes down to whether you are already committed to Kafka as your transport, how much operational ownership you want, and whether the source and target are both natively supported by a managed service.

Structured elaboration

Kafka Connect (the framework)

• Sources and targets: whatever plugin you install; huge open-source and commercial ecosystem (JDBC, Debezium, S3, Elasticsearch, Snowflake sinks, and more).
• Operational model: you run and scale Connect workers yourself (or use a managed flavor like Confluent Cloud or MSK Connect); connectors are configured via a REST API and distribute their work as tasks across workers.
• Latency: near-real-time when paired with a log-based source connector like Debezium; can be batch-like (minutes) with a JDBC polling connector.
• Schema handling: integrates with a schema registry; Single Message Transforms (SMTs) let you reshape, mask, or route records in-flight without writing custom code, for example dropping a field or renaming a topic based on the source table.

Debezium (a log-based CDC connector, usually run inside Kafka Connect)

• Sources: MySQL, PostgreSQL, MongoDB, SQL Server, Oracle, and others, reading each database's native change log (binlog, write-ahead log or WAL, oplog).
• Targets: Kafka topics (one per captured table by default); further delivery from there is a separate sink connector's job.
• Operational model: you run it, typically as a Kafka Connect source connector; it needs log-retention and permissions on the source database, and captures deletes and DDL, not just row values.
• Latency: sub-second to low-second, because it tails the log rather than polling.
• Schema handling: emits schema-aware records (with before/after state) and can be paired with a schema registry; DDL changes on the source generate corresponding schema evolution events.

AWS DMS (a managed, standalone replication service)

• Sources and targets: a fixed, broad matrix of relational and NoSQL engines on both ends (Oracle, SQL Server, PostgreSQL, MySQL, DynamoDB, S3, Redshift, and more), configured declaratively rather than through a plugin ecosystem.
• Operational model: fully managed by AWS; you configure a replication instance and endpoints and it handles the full-load-plus-CDC lifecycle, including the initial snapshot, without you standing up any infrastructure.
• Latency: log-based CDC once the initial load completes, comparable to Debezium; the initial full load is a separate, often slower phase.
• Schema handling: less flexible in-flight transformation than Kafka Connect SMTs; schema changes on the source can require re-running or reconfiguring a task rather than being absorbed automatically.

When you would prefer each

• Prefer Kafka Connect + Debezium when Kafka is already your transport of record and you want every downstream consumer, not just one target, to see the change stream, or when you need in-flight transformation via SMTs.
• Prefer AWS DMS when you are moving data between two AWS-native or classic relational engines and do not want to operate any connector infrastructure yourself, especially for a one-time or few-target migration rather than a fan-out to many consumers.
• Prefer Kafka Connect with a non-CDC plugin (JDBC source, a vendor-published connector, a sink connector) when the source has no usable log access at all and you are accepting timestamp-based polling instead.

Worked example

A team is migrating change events from an on-prem PostgreSQL order-processing database so three different services (fraud detection, analytics, and a search index) can each consume the same stream independently. Because there are three independent downstream consumers, not one target, Kafka Connect with Debezium is the right shape: Debezium publishes one topic per table, and each service runs its own consumer group against those topics with no coupling to the others. If instead the requirement had been "replicate this same database into an RDS PostgreSQL replica for read scaling, one source, one target," AWS DMS would have been the simpler, lower-operational-cost choice, since there is nothing to fan out and no need to run Connect infrastructure at all.

Trade-offs & pitfalls

• Debezium needs enough log retention on the source (binlog expiry, WAL retention) to survive a connector outage without falling behind and being forced into a full re-snapshot; this is an easy production surprise if nobody sizes it.
• Kafka Connect's flexibility is also its operational cost: you own worker sizing, connector upgrades, and dead-letter handling for bad records, none of which AWS DMS asks of you.
• AWS DMS's schema-change story is the sharpest limitation: adding or renaming a column mid-flight is not something it absorbs gracefully the way a schema-registry-backed Debezium pipeline can.
• Do not conflate "Kafka Connect" and "Debezium": Connect is the runtime, Debezium is one of many possible connectors you can run inside it, and mixing them up in an interview answer signals you have not actually operated either.

Direct answer

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

Structured elaboration

Reliability

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

Offset and checkpoint management

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

Schema support

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

Latency and throughput

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

Security

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

Operational maturity

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

How the calculus differs by connector type

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

Worked example

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

Trade-offs & pitfalls

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

Direct answer

Securing an ingestion pipeline that spans multiple clouds and on-prem sources means treating every connector as a distinct trust boundary: authenticate it with the strongest mechanism the source and target both support, encrypt data both in transit and at rest, keep every secret in a managed secrets store with rotation, log enough to reconstruct who touched what and when, and grant each connector only the specific permissions it needs, never a broad standing credential.

Structured elaboration

Authentication and authorization

• Prefer mutual TLS (mTLS) for connector-to-connector traffic where both ends support it, since it authenticates both parties, not just the client to the server.
• Use cloud-native IAM (Identity and Access Management) roles or service accounts for connectors talking to a cloud provider's own services, rather than long-lived static keys; a role can be scoped narrowly and its usage is natively auditable.
• For on-prem sources with neither mTLS nor cloud IAM available, a short-lived, narrowly-scoped service account credential is the fallback, still avoiding a single shared "integration user" account used by every connector.

Encryption

• In transit: TLS for every hop, including internal ones between a connector and an internal message bus; do not assume "internal network" means encryption is unnecessary.
• At rest: envelope encryption via a key management service (KMS), so data is encrypted with a data key that is itself encrypted by a master key the KMS controls, giving you centralized key rotation and revocation without re-encrypting all your data on every rotation.

Secret management

• Every credential, API key, database password, and certificate lives in a secrets manager, never in connector configuration files, environment variables checked into source control, or container images.
• Automate rotation where the source supports it; where it does not, at minimum track credential age and alert on staleness so rotation happens on a schedule rather than never.

Auditing

• Log every authentication event, every credential access, and every connector's data-access pattern (which source, how much data, when) in a way that is queryable after the fact, not just written to an ephemeral log that rotates away in days.
• Treat audit logs as themselves sensitive: they can reveal a lot about your data flows and access patterns, so restrict who can read them.

Least privilege for producers and consumers

• A connector reading from a source should have read-only access to exactly the tables, topics, or endpoints it needs, never blanket access to the whole source system.
• A connector writing to a target should be scoped to write only to its designated landing location, not broad write access across the destination platform.
• Review these scopes periodically; connector permissions tend to accumulate over time as requirements evolve, and nobody circles back to remove access that is no longer needed.

Worked example

A connector reads from an on-prem PostgreSQL database (via a VPN tunnel with TLS) and writes to a cloud data warehouse. The read side authenticates with a database service account scoped to SELECT on exactly the three tables the connector needs, credentials stored in the cloud secrets manager and fetched at connector startup rather than baked into its container image. The write side authenticates via a cloud IAM role scoped to write only into the specific landing schema this connector owns, with no access to any other team's tables. Both the credential fetch and every batch write are logged with a timestamp, source, and row count, so a security review six months later can answer "what did this connector actually access and when" without needing to reconstruct it from memory.

Trade-offs & pitfalls

• The most common real-world shortcut is a single shared "integration" database user with broad access used by every connector "to keep things simple"; this collapses your entire audit trail (you cannot tell which connector did what) and turns one leaked credential into access to everything.
• mTLS is not always available on legacy on-prem systems; do not let its absence become an excuse to skip TLS entirely, a one-way TLS connection is still meaningfully better than plaintext.
• Rotating secrets automatically is only safe if the connector can pick up a rotated credential without a manual restart; verify this before relying on automated rotation, or a rotation event becomes an unplanned outage.
• Least-privilege scopes decay over time as requirements shift; without a periodic review, "least privilege at launch" quietly becomes "broad privilege nobody remembers granting" within a year or two.