Data warehouse integration is the work of moving data from your applications, databases, SaaS tools and files into one warehouse, keeping it updated as the sources change, and shaping it into tables people can query. The first load is usually the easy part; the harder job is keeping later loads correct when sources change shape, connectors fail halfway and records arrive twice.
This guide walks through what the work involves, where it tends to break, and how to decide between building connectors and buying a managed tool.
What data warehouse integration involves
Every integration has the same basic stages, whatever tools you use. It helps to name them, because each one fails in its own way.
The destination is usually a cloud data warehouse such as Snowflake, Google BigQuery or Amazon Redshift, or a lakehouse platform such as Databricks. The stages below apply to all of them.
| Stage | What it does | What usually goes wrong |
|---|---|---|
| Sources | The systems that create data: your product database, CRM, billing, support, ad platforms, files | Undocumented APIs, rate limits, fields used for the wrong thing |
| Extraction (connectors) | Reads data from each source, in full or as changes | Missed deletes, gaps after a failure, pagination bugs |
| Raw landing | Writes source records into the warehouse close to their original shape | Schema changes break the load; duplicates after retries |
| Transformation | Cleans, joins and models raw data into analysis-ready tables | Models run in the wrong order; a failure leaves half the tables stale |
| Quality checks | Tests freshness, row counts, uniqueness and business rules | No checks at all, so problems surface in a dashboard |
| Orchestration and monitoring | Schedules runs, retries failures, alerts people | Silent failures; nobody knows data is a day old |
The data warehouse integration layer
When people talk about a data warehouse integration layer, they usually mean the first three stages together: the connectors, the raw landing tables, and the saved state that records how far each sync has got. Keeping this layer separate from your modelled tables has a practical benefit. If a transformation is wrong, you can fix it and rebuild from the raw data without going back to the source systems.
ETL vs ELT
The main design choice is where transformation happens.
| ETL (extract, transform, load) | ELT (extract, load, transform) | |
|---|---|---|
| Where data is transformed | Before it reaches the warehouse, in a separate engine | Inside the warehouse, after loading |
| What lands in the warehouse | Only the cleaned, shaped result | Raw data first, then modelled tables on top |
| Fixing a modelling mistake | Usually means re-extracting from the source | Rebuild from the raw tables already loaded |
| Good fit for | Removing sensitive fields before they land; older on-premises warehouses | Cloud warehouses with plenty of compute; teams that want SQL-based models |
| Main risk | Logic is locked inside the pipeline and harder to test | Raw tables grow, and sensitive data lands unless you mask it |
Many teams end up with a mix. They use ELT for most sources, and transform or mask before loading where a field should never reach the warehouse in raw form.
Full loads, incremental loads and change data capture
How you pick up changes decides whether your warehouse stays accurate.
| Approach | How it works | Watch out for |
|---|---|---|
| Full reload | Copy the whole table every run | Fine for small tables; slow and costly for large ones |
| Incremental by cursor | Ask for records changed since the last saved timestamp or ID | Deleted records never show up; records sharing a timestamp at the boundary can be skipped |
| Change data capture (CDC) | Read the source's own change stream or log | More setup, and it needs source permissions; usually still needs an initial full copy |
Cursor-based loads are the most common and the easiest to get subtly wrong. If a record is deleted in the source, there is no "changed since" row to fetch, so it stays in your warehouse forever unless you add a separate delete check. Using "greater than or equal to" the last cursor value, and then removing duplicates, is safer than "greater than", which can skip records written in the same instant.
Log-based CDC avoids most of this. Debezium's documentation, for example, lists these advantages of log-based CDC: it can capture deletes, and it produces change events with very low delay while avoiding the extra CPU load that frequent polling causes. The trade-off is setup: you need the right database permissions, and you still need an initial snapshot before the change stream takes over.
Batch, micro-batch or streaming
How you detect changes and how often you load them are separate choices. A cursor load or a CDC stream can feed the warehouse once a night, every hour or continuously.
| Schedule | How it runs | Good fit | What it costs you |
|---|---|---|---|
| Daily or hourly batch | A scheduled job loads everything since the last checkpoint | Finance, sales and most management reporting | Data is as old as the gap between runs |
| Micro-batch | Small loads every few minutes | Operational dashboards, such as the order backlog during the working day | More runs to schedule, monitor and retry |
| Streaming | Changes flow continuously from a CDC stream or event bus | Cases where a delay of minutes changes the outcome, such as stock levels shown to buyers | Always-on infrastructure, plus harder handling of ordering and duplicates |
Set the schedule per source, based on the decision the data feeds. A dashboard that people read once each morning gains nothing from streaming, and it still pays for the extra infrastructure and on-call work. Our default is a batch load for each source, with streaming kept for the few where freshness changes what someone does.
Loading options in Snowflake, BigQuery, Redshift and Databricks
The table sums up the file and stream features of the four platforms named above, from each vendor's documentation as we read it on 10 November 2026.
| Platform | Feature | Built for | What the vendor states |
|---|---|---|---|
| Snowflake | Snowpipe | Files that your pipeline already writes to cloud storage | Snowflake points file-based pipelines to Snowpipe |
| Snowflake | Snowpipe Streaming | Rows sent straight from applications, devices or services | Rows can be ready to query in as little as 5 seconds, by Snowflake's own figure |
| Google BigQuery | Batch load jobs | Large loads from many kinds of source | Google says they can cost less and use fewer resources |
| Google BigQuery | Storage Write API | Streaming and batch writes over gRPC connections | The default stream, meant for continuously arriving data, is at-least-once. Exactly-once needs a stream your application creates and tracks with stream offsets. In the pending type, records wait until you commit the stream |
| Amazon Redshift | Streaming ingestion | Data from Amazon Kinesis Data Streams or Amazon MSK, the managed Kafka service | Records land in a materialized view with no S3 staging area, and each one is processed exactly once |
| Databricks | Auto Loader | New files as they arrive in cloud storage | A checkpoint store gives exactly-once processing, and Databricks recommends it in Lakeflow pipelines |
Redshift and Auto Loader state exactly-once processing, and BigQuery offers it only on streams your application creates. BigQuery's default stream can deliver the same record more than once. Where a vendor does promise exactly-once, the promise covers only the move from the stream or the files into a warehouse table. The connector that reads your CRM or billing system sits outside it. If that connector retries a batch, the repeated records reach the stream as new messages, and the warehouse loads them as new rows. The checkpoint section below explains how loads should absorb those repeats.
Schema drift
Sources change shape without asking you. A new column appears, a field gets renamed, or a number starts arriving as text. This is schema drift, and it is one of the most common reasons a load that worked for months suddenly stops.
Decide your policy before it happens:
- New columns: usually safe to add automatically to the raw table.
- Removed or renamed columns: keep the old column in the raw table and alert someone; downstream models probably reference it.
- Type changes: land the value as text in the raw layer if needed, and fail the model test that casts it, so the problem is visible.
Landing raw data close to its original shape gives you room here, because the integration layer keeps loading while a person decides what the change means.
Save progress only after the records are written
This is the detail that separates a pipeline you can trust from one that loses records quietly.
A connector reads a batch from the source, then writes it to the warehouse. It also saves a checkpoint, a note saying "I have processed everything up to here". If the checkpoint is saved before the warehouse confirms the write, and the write then fails, the next run starts after records that never arrived. They are gone, and nothing alerts you.
The fix is ordering. The checkpoint should only move forward after the destination has confirmed that the matching records are written. If the run fails in between, the next run repeats the batch.
That means some records can arrive twice. This is called at-least-once delivery, and it is the normal, honest contract for most pipelines. Your loads should expect it, which usually means merging on a primary key instead of blindly appending.
In a warehouse connector case study, we worked on exactly this link. The load pipeline counts records against the source's state messages, and a count only reaches the state stage after the destination has flushed. A reconciler then publishes the completed checkpoints. The case study is explicit that this does not make delivery exactly once: a retry can repeat records, so anything downstream has to plan for duplicates.
Modelling the warehouse layer
Raw tables mirror the sources, but people need tables shaped around the business. The usual shape is a dimensional model, or star schema. The Kimball Group describes it as fact tables linked to dimension tables through primary and foreign keys. We favour it partly because it uses only plain tables and keys, so the design does not depend on one warehouse product.
- Fact tables hold events or transactions, such as orders, invoice lines or support tickets. Each has a declared grain, for example one row per order line, and numbers you can add up.
- Dimension tables describe what those events refer to, such as customers, products and sales regions. They carry the context of who, what, where, when, why and how, and reports filter and group by their columns.
Declare the grain before you build a fact table. A table that mixes order rows with order-line rows will double-count totals, and the error is hard to spot in a dashboard.
Keeping history in dimensions
Customers move to a new region and accounts change owner. For each attribute, decide whether reports should show the current value or the value at the time of the event. Dimensions that change like this are called slowly changing dimensions (SCD), and the table compares the two basic types.
| Type 1 SCD | Type 2 SCD | |
|---|---|---|
| On a change | The old value is overwritten | A new row is added with the new values |
| History | Lost | Kept |
| Extra columns | None | At least a start date, an end date and a current-row flag |
| Keys | One key per customer | A new surrogate key, an ID the warehouse assigns, for each version |
| Last year's sales | Move to the customer's new region | Stay with the region they were sold in |
| Cost | Simple to build and query | More storage and more care in joins |
The Kimball Group states plainly that Type 1 destroys history. Type 2 is worth its cost where history changes a decision, such as sales territory or plan tier. Type 1 suits corrections, such as a fixed spelling, where nobody needs the old value.
Some sources keep only the current state. For those, a regular snapshot of the table in the warehouse is the only way to build history later, so start snapshots early where you expect to need it.
This modelling is also what makes self-service BI workable, because users build on clear facts and dimensions instead of raw source tables. Microsoft's Power BI review checklist, for example, expects semantic models to use a star schema design.
SQL transformation and model runs
Once raw data has landed, transformation turns it into the tables people actually use. Most teams now write these as SQL models that reference each other, and a tool works out the order to run them in from those references.
The tricky part is failure. When a run fails partway, some models have finished, some failed, and some never started because something they depend on was missing. Rerunning everything wastes time and warehouse spend. Rerunning only the failed models skips the ones that never started. A good retry picks up both the failed and the skipped work, leaves successful models alone, and keeps the original run's settings, including whether it was a full refresh.
That retry behaviour was the centre of a SQL transformation case study. The tool we worked on resolves model references into a dependency plan, saves run results as JSON and Parquet artifacts, and on retry reads those results to restore the original command and refresh settings before rerunning only the failed or skipped nodes.
Data quality checks
Tests should run as part of the pipeline, not after someone notices a wrong number. Start with the cheap ones:
- Freshness: when did each source table last receive data?
- Row counts: does the warehouse count roughly match the source for the same period?
- Uniqueness and nulls: is the primary key unique and never empty?
- Relationships: does every order point to a customer that exists?
- Business rules: no negative quantities, no invoice dated before its order.
If you use dbt, four of these come built in. Its documentation lists unique, not_null, accepted_values and relationships as the generic data tests that ship with it, and each test is a query that returns failing rows, passing when it returns none.
Monitoring and backfills
Every failed run and failed test should alert a named person, with enough context to start investigating. Late data deserves its own alert too. A pipeline that has not run since yesterday and reports nothing is worse than one that fails loudly.
Some problems never fail a run. In Amazon Redshift streaming ingestion, a record over the maximum size is skipped and the materialized view refresh still succeeds. AWS writes a note about it to the SYS_STREAM_SCAN_ERRORS system table. A scheduled check on that table, or a row-count comparison with the source, is what turns the skipped record into an alert.
Every pipeline needs a backfill at some point, either to load history for a new source or to repair a period after a bug. Plan for it from the start:
- Make loads for a date range repeatable, so running the same range twice gives the same result.
- Keep raw data, so a backfill of modelled tables does not need the source. This matters most for streaming sources. By default, Kinesis keeps stream history for 24 hours and Amazon MSK keeps topic history for 7 days, so AWS notes that a Redshift streaming view cannot be fully recomputed from the stream. If the source system does not keep its own history, the raw tables in your warehouse are what you rebuild from.
- Run large backfills in chunks, and watch source rate limits and warehouse cost.
- Check row counts against the source for the backfilled range before you call it done.
Governance and security
Loading every system into one warehouse also puts customer, financial and staff data side by side. Design access and tracking into the pipeline from the first source, because both are hard to add once people depend on the tables.
- Limit access by layer. Raw tables belong to the engineers who run the pipelines. Analysts get the modelled layer, and business users get the certified tables and reports. Raw data is where unmasked fields and undocumented columns sit.
- Mask or hash personal data such as email addresses and tax IDs. If nobody needs a field for analysis, drop it before it lands, which is the ETL-style step described above.
- Give each connector a least-privilege account in the source, one that can read only what it needs. Its credentials belong in a secrets manager instead of pipeline code.
- Confirm that connectors use encrypted connections. Check that the warehouse and any staging storage encrypt data at rest, including temporary files that are easy to forget.
- Record lineage, meaning which source fields and models each table comes from. When a source changes, lineage shows which reports are affected.
- Keep audit logs of who queried or exported sensitive tables and who changed access rights. Security reviews and customer questionnaires often ask for this.
Row and column controls in each warehouse
All four platforms can limit which rows and columns a user sees. The features carry different names and conditions, and those conditions affect your licence and your report speed.
| Platform | Row-level control | Column-level control | Worth knowing |
|---|---|---|---|
| Snowflake | Row access policies | Dynamic Data Masking, which masks column values at query time | Both need Enterprise Edition or higher |
| Google BigQuery | Row-level security, which denies anyone not on the allowed list | Column-level security through policy tags | Masking applies after the row filter. A user sees only the allowed rows, and masked columns stay masked within them |
| Amazon Redshift | Row-level security by user or role | Permissions on a subset of columns | AWS warns that a query can perform differently for a user with a row-level policy attached |
| Databricks | Row filters in Unity Catalog | Column filters and attribute-based policies in Unity Catalog | Unity Catalog also keeps audit records of data access |
Check the Snowflake edition before you design around masking, because it decides which licence you need. The Redshift warning matters for dashboards, so test report speed while signed in with the role your business users will have.
Example: Salesforce data warehouse integration
Salesforce is one of the most common sources, and it shows the patterns above clearly.
Initial copy. For the first load, you need every record in the objects you care about. Salesforce's Bulk API is built for this; Salesforce's own training describes it as optimized for large sets of data, from a few thousand to millions of records, with requests that run asynchronously.
Ongoing changes. After that, you can either query for records modified since your last cursor, or subscribe to Change Data Capture. Salesforce describes Change Data Capture as publishing events for create, update, delete and undelete operations, and recommends it for keeping an external system updated instead of periodic exports or API polling. Getting deletes as events solves the gap that cursor-based loads leave.
Things to check before you trust the numbers:
- Which objects and fields the integration user can read. A missing permission looks like missing data.
- How deleted and restored records are handled in your models.
- How long your connector can be down before it needs a fresh copy. Check the replay window for your setup in Salesforce's documentation.
- Custom fields added by admins, which are a frequent source of schema drift.
If part of the job is building or fixing the API connection itself rather than the warehouse side, that work sits with our API integration and development services.
Build or buy connectors?
Managed connector tools cover many common SaaS sources and can save months. Custom connectors still make sense in some cases.
| Factor | Managed connector tool | Custom-built connector |
|---|---|---|
| Common SaaS source with a standard API | Usually the faster option | Rarely worth it |
| Internal database or in-house application | May work through a generic database connector | Often needed for unusual schemas or CDC setups |
| Niche or legacy source | Often not supported | Usually the only route |
| Cost model | Ongoing fee, often tied to data volume | Build cost plus your own maintenance |
| Control over checkpoints and retries | Set by the vendor | Yours to design and test |
| Who fixes it when the source API changes | The vendor, on their timeline | Your team or your partner |
A common answer is both: a managed tool for standard sources, and a small number of custom connectors that follow the same rules for checkpoints, schema drift and monitoring.
Common use cases
Step 2 of the plan below is to pick one use case. The use case sets which sources load first, and the joins between those sources decide how hard the work will be. The examples below describe typical projects, and none of them reports a result from client work.
Revenue reporting and reconciliation draws on billing, the payment processor and the CRM. Finance uses it to match booked, billed and collected revenue. The payment processor join needs the most care. One payout can bundle many charges, so each charge needs an invoice or customer ID that the billing system also stores. Without that shared ID, matching falls back on amounts and dates, and some payments will not reconcile.
Sales forecasting adds marketing and product sign-up data to CRM opportunities. A sign-up can arrive with only an email address before any CRM record exists, so the model needs a rule for matching the two.
Product usage and customer health reporting loads application or event data. When events carry a user ID rather than an account ID, a table that maps users to accounts has to load first.
Support reporting draws on the help desk, and operations and inventory reporting draws on ERP and order systems. Each source brings its own customer and product codes. Backlog, stock and fulfilment tables join cleanly only after those codes map to the same warehouse dimensions.
Where to start
A first integration usually runs in this order:
- List your sources and their owners. Note how you can reach each one, who can answer questions about it and how fresh its data needs to be.
- Pick one use case. Choose the one that matters most and integrate only the sources it needs.
- Choose a load method and schedule per source. Pick a full reload, a cursor or CDC. Then decide how often the source loads, and match that choice to your warehouse's file or streaming feature.
- Land raw data and set a schema drift policy. Decide how new, renamed and retyped columns are handled before the first change arrives.
- Model the tables the use case needs. Declare each fact table's grain and decide which attributes need Type 2 history.
- Add tests and alerts. Every failed run, failed test and late source alerts a named person.
- Set access before users arrive. Limit raw tables to the pipeline team. Mask sensitive fields and apply row and column controls to the modelled tables.
- Backfill and reconcile. Load the history, compare row counts with the source, and only then hand the tables to users.
Our data engineering and analytics services page explains how we scope this, starting with a two-week readiness assessment that maps sources, owners and data-quality issues before any build. When the data is in place, self-service BI is usually the next step.
FAQ
What is data warehouse integration?
It is the process of moving data from many source systems into one warehouse, keeping it updated, and modelling it so people can query it consistently.
What is the difference between data integration and data warehouse integration?
Data integration is the broader term for connecting systems, including app-to-app syncs. Data warehouse integration is the part that loads those systems into a warehouse for analysis.
Is a SaaS tool's "data warehouse integration" feature the same thing?
It is a narrower use of the same term. Some products use it for a feature that exports their own data into your warehouse on a schedule. Treat that feature as one more source. Check what it exports, how often it runs, and how it handles deletes and schema changes. If you need a connection a product does not offer, our API integration and development services cover custom connectors.
Should I use ETL or ELT?
ELT suits most cloud warehouses because you can rebuild models from raw data. Use ETL-style steps where sensitive fields must be removed before they land.
What is change data capture?
CDC reads a source's own record of changes, including deletes, instead of repeatedly asking for everything modified since a timestamp.
How do I stop duplicate records after a retry?
Expect them. Save progress only after writes succeed, and merge on a primary key in the warehouse so a repeated batch updates rows instead of adding them.
If you have a list of sources and a use case that needs them, you can get a range for your data project. The estimator asks about your sources, any warehouse you already run and how current the data must be, and sends back an initial range.
