Data Ingestion and Source System Integration Questions

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

Match the integration pattern to what each source can actually do, not to a single company-wide standard: the event-emitting source becomes a subscriber, the paginated-read-only source becomes a scheduled poll, and the nightly-file-export source becomes a scheduled file pickup. The design stays maintainable as requirements evolve by landing all three into the same normalized raw layer with a consistent schema and metadata, so a later change (adding near-real-time alerts from one source) only touches that one source's ingestion path, not the shared downstream model.

Structured elaboration

Matching pattern to source capability

• The event-emitting source: subscribe to its events (a webhook or a stream), land each event as it arrives; this is the only one of the three sources that can support the future near-real-time alert requirement without a redesign.
• The paginated-read-only source: a scheduled poll walking every page since the last checkpoint, on whatever interval balances freshness against the source's rate limit.
• The nightly-file-export source: a scheduled pickup job (SFTP, meaning SSH File Transfer Protocol, cloud storage, or however the export is delivered) that runs shortly after the export is expected, with a check that the file has actually landed before processing it.

Keeping the design maintainable

• Land all three into a common raw/landing layer with a consistent envelope (source name, ingestion timestamp, and the original payload), so downstream consumers work against one shape regardless of which pattern produced the data.
• Keep each source's connector isolated: a change to how the file-export source is polled should never require touching the event-subscriber's code.
• Make the freshness characteristics of each source visible downstream (a "last updated" or "as of" indicator per source) rather than presenting all three as if they were equally fresh, since silently blending a nightly-batch source with a real-time one can mislead a dashboard's audience about how current the numbers actually are.

Building in room for the requirement to evolve

• Today's ask is a daily dashboard, which the paginated and file-export sources already satisfy on their existing schedules; the event source's daily aggregation is just a rollup of its already-landed real-time stream, so no rework is needed there either.
• The stated future need (near-real-time alerts from one source) should specifically inform WHICH source you architect for streaming from day one, even if you are not yet building the alerting logic; retrofitting an already-batch-oriented connector into a streaming one later is a much larger project than building it as a subscriber from the start when you already know that is where it is headed.

Worked example

The event-driven source publishes order-status-change events; land each event onto a raw topic or table immediately as it arrives, since this is the source flagged for a future near-real-time alert. The paginated-read-only source (a support-ticketing API) is polled every 30 minutes using cursor-based pagination and a persisted checkpoint, comfortably supporting the daily dashboard's freshness need without over-polling a source that has no true real-time signal to offer anyway. The nightly-file-export source (a partner's inventory feed) is picked up by a job scheduled 30 minutes after the partner's documented export window, with a presence check before processing so a late or missing file triggers an alert rather than the job silently processing yesterday's stale file again. All three land into the same raw schema (source, ingested_at, payload), and the daily dashboard is built from a view over all three, each contributing at its own natural freshness. When the near-real-time alert requirement actually arrives, only the event source's already-real-time data needs a new consumer built against it: neither the poll-based nor the file-based ingestion paths need to change at all.

Trade-offs & pitfalls

• The most common mistake is trying to force all three sources into ONE integration pattern for "consistency," typically by wrapping the event source in a batch poll of its own API instead of subscribing directly, which throws away the one capability (real-time delivery) that source actually offers and that the stated future requirement will need.
• A file-pickup job with no presence check is a classic silent-staleness bug: if the partner's export is late or fails, a naive job that just re-reads "the file at this path" will happily reprocess yesterday's data with no error at all.
• Landing all three sources into a shared raw schema is good for maintainability, but resist the temptation to also force them into the SAME refresh cadence in that shared layer; an artificial "we refresh everything hourly" schedule either wastes effort re-polling a nightly file source or under-serves the event source's real freshness.
• Do not let "keep it maintainable" become an excuse to over-engineer a generic pluggable-connector framework for just three sources; isolating each connector's code is enough, a fully abstracted framework is a cost worth paying only once you have many more sources than three.

Direct answer

Credential management for many third-party connectors comes down to three disciplines: store every credential in a secrets manager and never in connector configuration files, refresh OAuth2 tokens proactively before they expire rather than reactively after a call fails, and treat a refresh failure as a distinct, alertable condition rather than letting it silently degrade into skipped syncs. Rate limits and retry behavior then have to be tracked per source, since every third-party API enforces its own limit differently.

Structured elaboration

Secure storage

• Store client IDs, client secrets, and refresh tokens in a dedicated secrets manager (a cloud provider's secrets service or an equivalent), never in a connector's plain configuration file or in version control.
• Scope access narrowly: the connector process should have permission to read only the credentials it needs, not every secret in the organization's store, so a compromised connector cannot pivot to unrelated systems.

Refreshing tokens without losing data

• Refresh proactively, ahead of expiry (for example, when a token has less than 10 minutes of validity left), rather than waiting for a call to fail with a 401 and refreshing reactively; this avoids losing an in-flight batch to an expired token mid-pull.
• If a refresh does fail (the refresh token itself is invalid or revoked), do not let ingestion silently stop: raise a distinct, named alert ("source X requires re-authorization") rather than letting the connector fail the same generic way it would for a transient network error, since these need different human responses.
• Persist enough state that a connector recovering from a refresh failure resumes from its last successful checkpoint rather than needing a full re-pull once access is restored.

Enforcing each source's rate limit

• Each third-party API documents its own limit differently (requests per second, per minute, per day, sometimes per specific endpoint); track each source's limit as explicit configuration rather than a single global assumption.
• A token-bucket (a counter that starts at some capacity, refills by a fixed amount on a fixed schedule such as once per second, and is spent one token per request, so you physically cannot send faster than the refill rate once the initial balance is used up) or sliding-window (which instead counts how many requests actually landed in the trailing N-second window and blocks new ones once that count hits the cap) limiter per source, refilled or evaluated at that source's documented rate, keeps you comfortably under the cap without needing to guess a safe interval empirically.

Retry and backoff, and making it auditable

• Exponential backoff with jitter on 429s and 5xxs, capped so a persistent failure surfaces as an alert instead of retrying forever.
• Log every token refresh, every rate-limit-triggered backoff, and every retry with enough context (which source, which credential, timestamp) that an audit can reconstruct exactly what happened to a given source's access over time, which matters both for debugging and for satisfying a security review.

Worked example

A connector integrates with 12 different third-party sources, each with its own OAuth2 app registration and its own rate limit. Credentials for all 12 live in a secrets manager, tagged by source, with the connector's service identity granted read access only to that tag group. A background refresh job checks each source's token expiry every 5 minutes and refreshes any token with under 10 minutes of remaining validity, well before any extraction job would hit it expired. When source #7's refresh token is revoked (an admin at the third-party company deauthorized the app), the refresh job's next attempt fails distinctly, raising a "source 7 needs re-authorization" alert rather than the generic "sync failed" alert every other source's transient hiccup produces, so the on-call engineer knows immediately this needs a human to click through an OAuth consent screen again, not a routine retry.

Trade-offs & pitfalls

• Reactive-only token refresh (refresh on the first 401) is simpler to implement but risks losing an in-flight extraction to a mid-batch expiry, especially for a slow, long-running pull; proactive refresh is worth the extra complexity for anything beyond a trivial connector.
• Logging refresh and retry events "for audit" is only useful if the logs are actually structured and queryable; a wall of unstructured text log lines does not satisfy a real security audit request.
• A single global rate limiter across all sources under-utilizes fast sources and still risks exceeding a slow source's limit; per-source limiting is more code but is the only version that is actually correct.
• Storing a refresh token is itself a long-lived secret with real blast radius if leaked; rotate the underlying OAuth application's credentials periodically even if no incident has occurred, not only in response to one.

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.