The Ultimate Guide to Data Ingestion

June 2026

A practical guide to data ingestion: source types, extraction patterns, schema evolution, checkpoints, replay safety, backfills, security, and how ingestion hands off into modeling.

Start Here: What Data Ingestion Actually Is

Data ingestion is the job of getting data from a source system into a destination or landing surface in a way that is repeatable, inspectable, and useful for later work. That sounds obvious, but the word gets stretched until it means everything from one CSV upload to a full replication program. A better definition is narrower: ingestion decides what to read, how to detect progress, how to preserve source shape, and when the destination has durably received the data.

A concrete example helps. Imagine a company with orders in PostgreSQL, product events arriving in Kafka, and historical exports dropped into S3. The ingestion layer decides how tables or topics are discovered, how offsets or checkpoints move forward, how files are listed and typed, how raw data is written into bronze tables, and what happens when the next run sees new rows, changed rows, or new fields.

This is why ingestion is not the same as transformation. In many batch and incremental systems, ingestion effectively ends when trustworthy raw data lands in the destination. ELT picks up from there and models the raw data. CDC is one specific ingestion pattern for mutation-heavy systems, and in CDC-backed paths the ingestion contract can extend one step further into final-state reconciliation before downstream modeling starts. Replication is a related idea, but ingestion is the broader category because it also includes files, streams, polling, and one-time backfills.

That distinction matters in practice. Teams often say "we need an ingestion tool" when they really mean one of three different things: land raw data on a schedule, keep a destination continuously current, or bootstrap an analytical stack from many source types without hand-building every connector boundary. Those are related jobs, but they are not identical.

• Ingestion owns the read path: how the source is reached, filtered, and authenticated.
• Ingestion owns the progress model: table snapshots, file scans, offsets, replication positions, or stream checkpoints.
• Ingestion owns the raw landing contract: what reaches bronze, how namespaces are formed, and what metadata survives the trip.
• Ingestion does not replace modeling: a successful load is not the same as a usable semantic layer.

The Mental Model: Source Surface, Cursor, Namespace, and Destination Contract

Most ingestion systems become easier to reason about once you reduce them to four moving parts.

• Source surface: what the system exposes for reading, such as tables, collections, topics, files, messages, or HTTP payloads.
• Cursor or checkpoint: the durable position that tells the next run where to resume.
• Namespace and schema shape: how source objects become named raw objects and what fields and types the system believes they have.
• Destination contract: what counts as "loaded," when progress can advance safely, and how retries behave.

A PostgreSQL source might use table metadata for discovery and a WAL position for CDC resume. A Kafka source uses brokers, a topic, and a consumer group offset. An S3 source uses a bucket, prefix, and the set of matching objects discovered under that prefix. An SFTP source uses a remote path or glob and whatever files the SSH user can actually list and read. The connector surface changes, but the mental model stays stable.

If a system does not make these parts explicit, it becomes hard to answer simple operational questions. Where exactly did the last run stop? Which source objects are in scope? What happens if the runner dies halfway through a load? When a new field appears in the source, is it preserved, ignored, or rewritten? Those are ingestion questions, not downstream modeling questions.

That is also why mature ingestion design feels more like systems work than like copying bytes. The hard part is not just reading data once. The hard part is making the read path reliable after the tenth rerun, the schema change, the network hiccup, and the backfill.

Source Classes Need Different Ingestion Designs

One of the biggest mistakes in data platform design is pretending all sources are basically tables. They are not. Databases, files, streams, and webhooks expose data differently and therefore demand different ingestion contracts.

Source classExamplesWhat ingestion has to doTypical operational riskOperational databasesPostgreSQL, MySQL, MongoDB, DynamoDBDiscover tables or collections, read current state or mutation streams, preserve keys and typesPermissions, network reachability, replication or change-stream prerequisites, and source load pressureObject and file storesS3, SFTP, local fileList files, infer or read structure, filter prefixes or paths, and decide how files map into namespacesLate files, replaced files, bad partitions, path mistakes, and format driftOpen table formatsDelta LakeRead a table URI and its transaction log, resolve table version semantics, and ingest rows as a table contract rather than as loose filesWrong table URI, missing log access, confusing storage credentials, or reading the wrong version boundaryStreams and messagingKafka, Kinesis, SQS, AMQP, MQTTConsume messages, manage offsets or sequence state, and preserve delivery contextOrdering assumptions, duplicate delivery, consumer-group behavior, and auth or ACL failuresHTTP and network inputsHTTP client, HTTP server, WebSocket, socketPoll or receive payloads, infer structure, and define what counts as durable receiptRate limits, payload drift, unstable upstream contracts, and weak retry semantics

The docs make this difference explicit. Source connectors span databases, object stores, streaming systems, HTTP surfaces, and lower-level inputs such as sockets and files. That alone should push teams away from one-size-fits-all ingestion thinking. A Kafka topic with Debezium envelopes is not the same problem as scanning a DynamoDB table, and neither is the same problem as picking up JSON files from SFTP every hour.

The most useful question to ask at design time is: what does the source naturally expose? If you start there, the ingestion shape usually follows. If you ignore it, you end up forcing database logic onto files, file logic onto streams, or CDC logic onto systems that only wanted a simple batch scan.

Batch Snapshots, Incremental Sync, CDC, and Stream Consumption

These four patterns get blurred together constantly, but they solve different problems.

PatternProgress modelBest fitWhat to watch out forBatch snapshotWhole table, file set, or query result at one point in timeSmaller or slower-changing datasets, one-time loads, or simple scheduled pipelinesExpensive reruns, weak handling of fast updates and deletes, and growing runtime as data growsIncremental syncLast seen timestamp, key, offset, or object boundaryRegular scheduled loads where only new or changed data needs processingLate-arriving records, non-monotonic source fields, and ambiguous update behaviorCDCReplication log, change stream, or ordered mutation feedSystems where updates and deletes matter and low-latency convergence is worth the complexityResume state, ordering, business keys, and final-state reconciliation in the destinationStream consumptionMessage offsets or sequence positionsEvent pipelines, telemetry, and systems whose native shape is a message flowDuplicate delivery, offset management, schema drift in events, and consumer-group semantics

A useful example is an orders source. If the business only needs a nightly finance snapshot, a daily batch scan may be enough. If the dataset is large and new rows arrive all day, incremental sync might be the right default. If order status changes and deletes have to be reflected quickly and correctly downstream, you probably need CDC. If the source is not a table at all but a Kafka topic of order events, then stream consumption is the natural ingestion shape from the start.

CDC is also where source-specific prerequisites become real. PostgreSQL CDC needs wal_level = logical and a replication-capable user. MySQL CDC needs binlog_format = ROW and binlog_row_image = FULL. MongoDB change streams need a replica set or sharded cluster. DynamoDB CDC needs Streams with NEW_AND_OLD_IMAGES. Kafka CDC needs Debezium-formatted messages with the expected op, before, and after envelope fields. That is one reason CDC deserves its own deeper guide: the category is unified, but the operator work is not.

The mistake is not choosing the simplest pattern. The mistake is choosing a pattern that does not match the source truth you need. Batch looks wonderfully simple until somebody asks why cancelled orders still look paid for half a day. CDC looks wonderfully powerful until the team realizes it did not actually need mutation fidelity and now has to operate a more complex system than the use case justified.

Discovery and Schema Evolution Are Part of Ingestion, Not Cleanup

Many teams treat schema handling as a downstream annoyance, but the first schema contract is created by the ingestion layer. If discovery is weak or implicit, every later stage inherits that confusion.

Skippr's core concepts docs describe discovery by source type: databases read catalog metadata such as table and column definitions, object stores sample structured files, streams infer structure from records, and HTTP or network inputs infer structure from payload shape. The important point is that discovery is not manual glue code. It is part of the first run.

Schema evolution matters just as much. A PostgreSQL table may gain a nullable column. A JSON file dropped into S3 may add a new nested field. A Kafka event may ship a new payload version. Good ingestion behavior preserves compatible new structure and surfaces incompatible changes clearly instead of pretending nothing happened.

• Additive changes should be easy to absorb: a new compatible field is normal operational life, not a reason to rebuild the stack from scratch.
• Nested structure should stay legible: flattening or silently discarding rich payloads too early makes later modeling harder.
• Incompatible type shifts should be explicit: if a source changes a field from integer to string, the system should surface that as evolution, not hide it under a best-effort cast that corrupts meaning.
• Discovery should stay deterministic: schema mapping and evolution handling are part of ingestion correctness, not a place for guesswork.

That last point is important because it defines where AI belongs and where it does not. AI can help draft staging models or documentation, but source discovery, type reconciliation, and progress tracking need deterministic behavior. Otherwise the system becomes very hard to trust after the first schema change.

Checkpoints, Replay, Ordering, and Idempotency

If schema is the visible side of ingestion, checkpoints are the invisible side. They determine whether the system can rerun cleanly, resume after failure, and avoid silently dropping or duplicating data.

A batch file source may checkpoint the set of objects already processed. A Kafka consumer tracks offsets for a topic and consumer group. Incremental database sync often depends on the last committed key or timestamp boundary. CDC uses more exact resume positions such as WAL locations, binlog positions, change-stream tokens, or stream sequence state.

The key operational rule is simple: progress should advance only after the corresponding load is durably committed. If the system marks progress too early, a crash can lose data. If it never marks progress clearly, retries can duplicate data or force costly rereads.

Use a simple example. An order row is updated from pending to paid, and the runner extracts that change but dies before the destination commit finishes. If the checkpoint advanced anyway, the next run may skip the update. If the system replays the change but the destination write path is not idempotent, the row may be duplicated or reconciled incorrectly. The checkpoint and the write contract have to work together.

• Replay safety matters even for batch: interrupted file scans and partial table loads still need a clean restart story.
• Ordering scope matters for mutations: CDC and streams need a credible story for update order and final convergence.
• Idempotent landing is safer than blind append: retries are normal, so design them as part of the system rather than as an exception path.
• Checkpoint visibility matters operationally: if the team cannot explain where the run will resume, it does not really understand the ingestion system yet.

Backfills, Initial Loads, and Cutovers Deserve Their Own Plan

The first successful run and the steady-state run are usually different engineering problems. Initial loads and backfills are where ingestion systems either prove they are durable or reveal that they were only designed for the happy path.

A PostgreSQL source may need an initial table read before any CDC resume position becomes useful. An S3 source might need a historical prefix sweep before moving to a narrower rolling prefix. An SFTP pipeline may need to pull years of old exports before it switches to the last day's drop folder. Kafka pipelines often need a decision about whether to start from earliest or latest depending on whether history matters.

• Define the historical boundary explicitly: full history, last twelve months, or only new data from a cutover point onward.
• Run a controlled first load that proves the source can be reached, the raw namespace is correct, and the destination can absorb the data volume.
• Validate the raw landing results before widening scope, because a wrong namespace, bad filter, or missing permission compounds quickly during large backfills.
• Only cut over to the steady-state schedule once the team understands how retries, checkpointing, and schema evolution behave under real load.

Backfills also expose a hidden question: does the ingestion path put too much pressure on the source? Full scans against transactional systems, giant remote file pulls, or badly tuned consumers can create operational pain upstream. Good ingestion is not just convenient for the destination team. It also respects the source system it is reading from.

Security, Network Access, and Source Permissions Are First-Class Requirements

A surprising amount of ingestion work is really identity and networking work. The docs across source connectors make that plain.

• PostgreSQL and MySQL need reachable hosts, valid credentials or connection strings, and read access to the selected tables.
• MongoDB needs a usable connection URI, the right database and collection, and a user that can actually read them.
• DynamoDB depends on the AWS credential chain and permissions such as table describe, scan, and stream access where CDC is involved.
• S3 depends on bucket visibility plus s3:GetObject and s3:ListBucket on the right bucket and prefix.
• SFTP depends on SSH reachability, a working password or private key, and read access to the target remote path.
• Kafka depends on broker reachability, topic read permissions, consumer-group behavior, and sometimes SASL or TLS settings.

These details matter because ingestion bugs are often reported at the wrong layer. "The pipeline is broken" sometimes means the Kafka ACL does not allow the configured consumer group. "No rows are arriving" sometimes means the S3 prefix is wrong or the SSH user cannot list the remote path. "CDC is unstable" sometimes means the source permissions or replication prerequisites were never actually in place.

Strong ingestion design therefore validates the boring things early: can the runner reach the endpoint, can the identity read the intended objects, and are the source-side prerequisites satisfied before the first sync starts? Teams that leave those questions to the end create their own incidents.

Ingestion Quality: Duplicates, Late Data, Bad Payloads, and Missing Files

Ingestion quality problems are often more mechanical than modeling problems. The payload exists, but it arrives twice, arrives late, arrives malformed, or arrives in a path the runner was not actually scanning.

Use a few real examples. A Kafka producer retries and the same message lands twice. A source database backdates an update so a naive incremental filter misses it. An S3 pipeline receives a corrected Parquet file several hours after the expected partition window. An SFTP drop contains a file with the same name but different contents. None of these failures is exotic. They are normal ingestion reality.

• Duplicate delivery is normal: streams and retries mean downstream raw tables and reconciliation logic need to tolerate repeated inputs.
• Late-arriving data needs a policy: timestamp-only incrementality is fragile if the source can update older records or publish late files.
• Malformed payloads should surface clearly: silently swallowing bad records creates a false sense of success.
• Namespace mistakes are quality issues too: landing data under the wrong raw object name is operationally equivalent to data loss for downstream consumers.
• Source filters need scrutiny: table lists, file prefixes, and collection filters are part of the contract and can silently exclude data if chosen carelessly.

The deeper point is that ingestion quality is not only about "did bytes move." It is about whether the data landed in a form that preserves enough truth to support later transformation and analysis. If the raw layer is already confusing, the silver and gold layers inherit that confusion.

Ingestion Is Only the First Layer: Bronze to Silver to Gold

Healthy stacks make the handoff from ingestion to modeling explicit. In Skippr's public pipeline model, the runner discovers the source, syncs raw data into bronze tables, models a dbt project, and validates the result. That sequence is useful because it makes the bronze boundary legible.

Bronze exists so the raw source shape is still available and inspectable. Silver and gold exist so the raw source shape does not become the final user interface for every analyst, dashboard, or downstream consumer. That is why a successful ingestion program should stop pretending that raw landing alone is the whole job.

The nuance is that CDC-backed systems can push the ingestion contract one step further than raw landing. Before downstream modeling begins, the system may also need to reconcile mutations into a correct final-state table in the destination. That final-state apply behavior is still an ingestion concern because it determines whether retries, ordering, and deletes converge on the same current truth. The modeling layer then builds on top of that stable state or the raw mutation history, depending on the design.

LayerWhat it should preserve or doMain ownerBronzeRaw landed data, source fidelity, replay visibility, and low-level debugging valueIngestionSilverTyping, cleanup, renaming, light normalization, and stable staging logicTransformation layerGoldBusiness-facing marts, aggregates, and published analytical interfacesAnalytics engineering and business ownership

This distinction is one of the clearest ways to keep ingestion from sprawling into every other discipline. Ingestion should preserve raw truth and progress correctly. It should not quietly absorb every business decision, metric definition, or semantic debate that belongs in the model layer.

Common Failure Patterns in Ingestion Systems

Ingestion failures are repetitive, which is useful because repetitive failures can be designed against.

• Batch pretending to be CDC: teams choose snapshots for a workload that actually needs reliable updates and deletes.
• No real resume model: the pipeline works once, but nobody can explain where it resumes after a crash.
• Hidden schema assumptions: the first version works until a new field or type change appears and nobody knows which layer owns the response.
• Source pressure ignored: initial loads or recurring scans are designed without respecting the operational cost imposed on the source.
• Weak auth and network validation: connector setup is treated as trivial until the first production run reveals missing permissions or unreachable endpoints.
• Too many tool boundaries: one product reads, another stages, another reconciles, and a separate repo boots the model layer, making failures harder to trace.
• No raw landing contract: bronze exists, but downstream users cannot tell what is complete, what is incremental, and what a rerun is allowed to change.

The common thread is simple: the team chose an ingestion path, but not an ingestion operating model. The result is a system that looks automated until real change starts happening.

How Skippr Fits a Practical Ingestion Program

Skippr fits best when the goal is not "copy one table once," but "stand up a real source-to-destination path with visible phases and ordinary artifacts." The public pipeline stays explicit: discover the source shape, sync raw data into bronze, model a dbt project, and validate the result against the destination.

• Broad source surface: the documented sources span databases such as PostgreSQL, MySQL, MongoDB, and DynamoDB, object and file inputs such as S3 and SFTP, streaming systems such as Kafka, and additional HTTP or network connectors.
• Deterministic ingestion responsibilities: schema discovery and destination mapping, type reconciliation and evolution handling, incremental checkpoints and replay behavior, and CDC reconciliation logic stay deterministic rather than model-guessed.
• Generated downstream assets: after raw landing, Skippr can generate the dbt project as standard files such as dbt_project.yml, profiles.yml, models/schema.yml, and staging models that point at bronze data.
• Clear data boundary: your data stays within your system and in your destination, while the cloud path handles authentication and control-plane services.
• Fewer boundaries to debug: instead of wiring together separate products for extract, landing, and model bootstrap, the public workflow keeps the early stack narrower and easier to inspect.

That is useful because ingestion programs often stall between "we can reach the source" and "we have a trustworthy raw-to-model path." Skippr is strongest in that gap: getting the supported source connected, landing raw data directly, and generating a normal dbt starting point without hiding the mechanics.

For related reading, pair this guide with The Ultimate Guide to Change Data Capture, The Ultimate Guide to ELT, The Ultimate Guide to Cloud Data Warehouses, The Ultimate Guide to dbt, the How It Works docs, and the source connector docs.

Your Practical Ingestion Checklist

If you want one section to keep open while designing an ingestion path, use this one.

• Name the source class first: database, file store, stream, or HTTP-style input.
• Choose the simplest ingestion pattern that still matches the truth you need: batch, incremental, CDC, or stream consumption.
• Define the checkpoint and replay model before you trust the first successful run.
• Make source discovery, namespace shape, and schema evolution behavior explicit.
• Validate the source-side permissions, network reachability, and auth mode early.
• Plan the initial load and backfill path separately from the steady-state schedule.
• Keep bronze honest: raw landing should preserve source reality well enough to debug and model later.
• Treat ingestion as one layer in the stack, not as a substitute for silver and gold modeling.

That sequence reflects how strong ingestion systems actually get built. First respect the source. Then prove the load path. Then harden the contract before the pipeline spreads across the rest of the business.