Data Integration Services in India

What a Data Integration Engagement Actually Covers

Not every engagement includes everything below, and the sequence matters more than the list. Building a streaming layer before you have a reliable nightly batch is a common and expensive inversion. What follows is the full surface area, in roughly the order we tend to build it.

Source discovery and an access plan

The first week is unglamorous and decides most of the schedule. We inventory every source, and for each one establish what the extraction mechanism will be, who owns it, whether a read replica exists, what the retention and deletion behaviour is, and whether deletes are hard or soft. Soft deletes matter enormously and get missed constantly: a row marked is_active = false is not a delete to your pipeline unless somebody tells it so.

The access plan is the other half. Credentials, network paths, VPN or private link, replica provisioning, and any database configuration change that needs a maintenance window. Enabling logical replication on PostgreSQL requires wal_level = logical, and on most managed instances that is a parameter group change with a restart attached. Discovering that in week five costs you a week. Discovering it in week one costs you a calendar invitation.

Extraction and ingestion

Getting rows out of the source, whether that is log-based change data capture, incremental queries against a high watermark, full snapshots for small reference tables, file drops on SFTP or object storage, API pagination against a rate limited endpoint, or a managed connector doing the work for you. Each of those has a different failure mode and a different cost profile, and mixing them in one estate is normal rather than a sign of mess.

Landing, staging and the raw layer

Raw data lands untransformed and immutable, with the ingestion timestamp and the source system recorded on every row. This is the single most useful architectural decision in the whole stack, because it means every downstream mistake is recoverable by reprocessing rather than by re-extracting from a source that may have already overwritten the truth. Storage is cheap. Asking a partner to resend six months of files is not.

Transformation and modelling

Cleaning, typing, deduplication, joins, slowly changing dimensions, business logic, and the semantic layer analysts actually query. In practice this is a dbt project, version controlled, reviewed like software, with tests attached to the models rather than living in a separate quality tool nobody opens.

Loading and publication

Merge strategies, partition handling, atomic swaps, and the write-audit-publish pattern so that a failed quality check means nothing gets published rather than bad data being published and then corrected. The difference between those two behaviours is the difference between a quiet Tuesday and an email from the CFO.

Orchestration and scheduling

Dependencies, retries with sensible backoff, sensors that wait for upstream files, SLAs, and alerting that distinguishes a transient network blip from a genuine data problem. An orchestrator that pages someone for every retry gets muted within a fortnight, and after that it may as well not exist.

Quality gates and observability

Tests that run inside the pipeline and can stop it, freshness monitoring, volume anomaly detection, and a status page an analyst can check before they raise a ticket asking whether the data is up to date.

Documentation, lineage and handover

Column level lineage, model documentation generated from the code rather than written separately and left to rot, runbooks for the three most likely failures, and an architecture note that explains why each decision was taken and what the alternative was. The test we apply is whether an engineer on your side who was not part of the build can take a 2am page, follow the runbook, and either fix the load or safely skip it until morning.

Change Data Capture Without Hurting the Source Database

Change data capture is how you keep a warehouse in step with an operational database without re-reading the whole table. There are two families, and choosing badly between them is one of the more expensive mistakes available in this discipline.

Query-based CDC and where it quietly fails

The familiar approach: store a high watermark, then run SELECT * FROM orders WHERE updated_at > :last_run every few minutes. It needs nothing special from the database, any engineer can read it, and for a small table with a well maintained timestamp it is perfectly reasonable.

It fails in four specific ways. Hard deletes are invisible, because a deleted row cannot appear in a result set, so your warehouse accumulates records the source no longer has. Rows updated twice between polls give you only the final state, which is fine for a current snapshot and useless if you needed the intermediate history. Transactions that commit out of timestamp order, which happens under load with long running transactions, can slip past the watermark entirely and are never seen again. And the query itself competes with production traffic, so the more current you want your data, the more load you place on the database serving your customers.

That last point is the trap. Teams respond to stale data by polling more often, which slows the source, which makes someone add an index, which slows writes. We have seen a five minute poll on a large table consume more database time than the application it was reading from.

Log-based CDC

Every relational database already writes a durable log of changes so it can recover from a crash and feed replicas. PostgreSQL has the write ahead log and logical decoding, MySQL has the binary log in row format, SQL Server has its own change data capture tables and change tracking, Oracle has redo logs and LogMiner. Log-based CDC reads that stream. Deletes appear because they are logged. Every intermediate update appears in commit order. And the read cost to the primary is a fraction of a repeated table scan, because the log is sequential and was being written anyway.

Debezium is the reference implementation in open source, and it runs either as a set of Kafka Connect connectors or standalone through Debezium Server or the embedded engine when you do not want a Kafka cluster in your life. That second option matters for smaller teams. Running Kafka to move three tables is a decision you should have to justify.

The five things that bite people running Debezium

Replication slot lag is the first and worst. A PostgreSQL slot guarantees the WAL will be kept until the consumer has read it, so a connector that dies on a Friday evening can fill the primary's disk by Sunday. This is an operational property, not a bug, and it needs an alert on slot lag with a threshold agreed with whoever owns the database before anything goes live.

Second, TOAST columns. PostgreSQL stores large values out of line, and an update that does not change a TOASTed column sends a placeholder rather than the value. Your consumer sees an unchanged marker where it expected text. Setting REPLICA IDENTITY FULL solves it and increases WAL volume, so it is a trade rather than a fix.

Third, before-images. By default PostgreSQL logs only the primary key of the old row, so if you need to know what a field changed from rather than to, you have to opt into full replica identity and accept the extra write volume.

Fourth, the initial snapshot. Getting the existing table contents consistent with the start of the stream traditionally meant a lock or a long blocking read. Debezium's incremental snapshots, which use a signalling table and chunked watermarks, allow that backfill to run alongside the live stream without stopping it. It is worth configuring properly rather than accepting the default on a large table.

Fifth, schema history. The connector maintains its own record of the source schema over time, and losing that topic or file is a genuinely painful recovery. It gets backed up alongside everything else, and we have seen more than one team discover it was not.

MySQL has its own version of most of this: the binary log must be in ROW format, and binlog_row_image should be FULL if you want complete before and after images. Retention is the other one to check. If your binlogs are kept for 24 hours and your connector is down for a long weekend, the only recovery path is a fresh snapshot.

Schema Evolution, or How a Producer Breaks a Consumer in Silence

A backend team ships a migration on Thursday afternoon. They rename customer_id to account_id because the domain language changed, update every service that reads it, and deploy without incident. Nobody in that room knows your warehouse exists. Your pipeline breaks that night, or worse, does not break and starts writing nulls into a column three dashboards depend on.

Schema changes come in three severities and it is worth being precise about which is which. Additive changes, such as a new nullable column, are safe if your pipeline ignores unknown fields rather than failing on them. Widening changes, such as an integer becoming a bigint, are usually safe but occasionally truncate on the target. Breaking changes, meaning renames, type changes, dropped columns and altered enum encodings, will corrupt or halt something downstream every time.

The naive defence is to make the pipeline permissive: cast whatever arrives, fill missing fields with nulls, keep going. That converts a loud failure into a silent one, which is the wrong direction. The right posture is to fail fast on the specific change classes that matter and to detect them before the data reaches a consumer.

Three mechanisms do the work, and most estates need more than one.

A schema registry sits in front of event streams. Confluent Schema Registry with Avro, Protobuf or JSON Schema enforces a compatibility mode at the point of publication, so a producer attempting an incompatible change is rejected by their own CI rather than discovered by you at midnight. BACKWARD compatibility, which lets new consumers read old data, is the right default for most analytics use cases. This is the strongest control available, and it only works if the producing teams accept it.

Contracts on the ingestion side cover the sources you do not control. dbt source freshness and model contracts, or expectation suites in Great Expectations, assert the column set and types you require. When reality diverges, the run fails at the staging boundary and nothing publishes. The failure message names the column and the change, which turns a three day investigation into a five minute one.

A quarantine path handles what you decide not to reject outright. Rows that fail parsing or violate a constraint go to a separate table with the raw payload and the reason, instead of being dropped. That table gets a row count alert. It is remarkable how often it turns out that a client has been silently discarding two percent of a feed for months.

The organisational half matters as much as the technical half. Somebody on the producing team has to know the pipeline exists. The cheapest version of this is a check in the source repository's CI that flags changes to tables that are known to be replicated, pointing at a document naming the owner on the data side. It is not sophisticated. It stops most of the incidents.

Data Quality Tests as a Gate, Not a Dashboard

A quality dashboard that shows yesterday's problems is a report on damage. A quality test inside the pipeline that stops publication is a control. The difference is whether bad numbers reach a human before somebody catches them, and it is the change that pays back fastest on most estates.

The pattern that implements this is write-audit-publish. The run writes into a staging location, tests execute against that staging data, and only a clean result promotes it into the tables analysts and dashboards read. When a test fails, yesterday's data stays live and stale, an alert fires, and nobody makes a decision on a broken number. Stale and known beats fresh and wrong every time.

On tooling, dbt tests are where most of this belongs, because the assertions live next to the model definition and travel with it in review. The four built-ins cover more ground than people expect: not_null, unique, accepted_values and relationships for referential integrity. dbt-utils adds equal row counts across models, expression checks and mutually exclusive ranges. dbt-expectations ports much of the Great Expectations vocabulary into the same place. Singular tests, which are just SQL that should return zero rows, handle the business rules that are specific to you, and those are usually the ones that catch real problems.

Great Expectations is the stronger choice when validation has to happen before the warehouse, on files or dataframes in flight, or when a non engineering audience needs readable validation documentation. Soda Core suits teams that want checks written in a compact YAML dialect rather than SQL. Elementary adds anomaly detection over dbt run history, which catches the class of problem no static assertion can express: the table that usually loads two million rows and today loaded eleven thousand, with every individual row perfectly valid.

Severity levels are what keep the whole thing from being switched off. Not every test should halt a pipeline. A null in a legally required identifier should. A slightly unusual row count should warn. dbt's error and warn severities plus store_failures let you keep the failing rows for inspection without blocking the run. Teams that make everything an error end up with an alert channel nobody reads, which is functionally the same as having no tests at all.

Freshness deserves its own mention because it fails differently. A source that stops sending data produces no wrong rows at all. Every test passes. The dashboard just quietly reflects last Wednesday. dbt source freshness thresholds, or an equivalent max-timestamp check, are what catch the pipeline that succeeded at doing nothing.

Warehouse Targets: What Actually Changes Per Platform

Loading rows looks similar everywhere. What differs is the cost lever, the merge semantics, how you isolate one workload from another, and what you have to get right for performance. Choosing the wrong pattern for your platform is how a pipeline that ran fine in a proof of concept becomes a monthly cost review.

Snowflake

Compute is virtual warehouses, billed per second with a sixty second minimum, which makes auto suspend one of the most valuable settings in the account. Isolation is easy and underused: give loading, transformation, backfills and BI their own warehouses so a heavy job cannot queue behind an analyst or vice versa. MERGE is well supported, micro-partitions are managed for you, and clustering keys are worth defining only on genuinely large tables where the pruning is measurable. Zero-copy clone makes the write-audit-publish pattern almost free, and Time Travel means a botched load can be undone rather than reloaded. The costly mistake is warehouse size inflation, since each step up doubles the credit burn per second and a query that is bottlenecked on a single partition will not go faster for it.

BigQuery

The main cost lever is bytes scanned under the on-demand model, or slot capacity under editions and reservations. That single fact drives the design: partitioning and clustering are not optimisations, they are the budget. A query without a partition filter on a large table is the classic expensive accident, and require_partition_filter exists to prevent exactly that. There are no indexes to tune. Use the Storage Write API rather than legacy streaming inserts for anything new, understand that streamed rows sit in a buffer briefly before they are queryable, and prefer MERGE on partitioned tables with a filter narrow enough that it does not rewrite the whole thing.

Redshift

Physical data layout still matters here more than on the others. Distribution style and sort keys determine whether a join redistributes data across nodes, and getting those wrong on a large fact table is the difference between seconds and minutes. RA3 nodes separated storage from compute and managed the worst of the old sizing pain, and Redshift Serverless removes cluster sizing entirely in exchange for RPU based billing. Concurrency scaling handles the bursts. Copy from S3 in parallel with correctly sized files rather than inserting row by row, and use workload management queues to keep the backfill away from the analysts.

Databricks and the lakehouse

Delta Lake gives you ACID transactions and time travel over object storage, so merges and atomic swaps behave the way you want without a traditional warehouse underneath. OPTIMIZE with compaction, and Z-ordering or liquid clustering on the columns you filter by, are the maintenance that keeps small file proliferation from strangling read performance, and a streaming ingest with no compaction job is the most common way that happens. Unity Catalog is the reason a lot of teams pick this platform: governance, column level lineage and access control in one place rather than three. Keep SQL warehouses and job clusters separate so interactive queries and pipeline runs do not compete.

What we standardise regardless of target

The raw layer stays immutable with source and ingestion timestamp columns on every row. Transformations live in a dbt project in your repository with tests attached. Loads are idempotent through merge or dedup-on-read. Publication is atomic. Compute for backfills is separate from compute for anything a person is waiting on. Those five hold on all four platforms, which means a migration later is a rewrite of the loading layer rather than of everything.

PII on the Road to Analytics

Analytics pipelines are where personal data quietly spreads. A production database has access controls, an audit trail and a security owner. A warehouse built by copying that database has all the same data with a different, usually looser, access model and a much wider audience. This is a design decision worth making on purpose.

The work starts with classification, not tooling. Go column by column through the sources and decide, with someone from your side who can make the call, which fields are personal data, which are special category, and which are merely commercially sensitive. It is tedious and it takes a couple of days, and skipping it is how a support ticket free-text column full of customer phone numbers ends up in a table forty people can read.

Then each classified column gets a treatment. Drop it, if analytics genuinely does not need it, which is the answer more often than people expect. Hash it with a salt held outside the warehouse, when you need to join on identity but never to read it. Tokenise through a vault, when a controlled reverse lookup has to remain possible for a small group. Generalise it, turning a date of birth into an age band and a full postcode into its outward part, which usually preserves the analytical value entirely. Or keep it and control access, when there is a real reason.

Where the treatment happens is the second decision. Masking in flight, before the data lands, means the sensitive value never exists in the analytics environment, which is the strongest position and the hardest to reverse when requirements change. Masking in the warehouse, using Snowflake dynamic data masking policies, BigQuery policy tags with column level security, or Unity Catalog column masks and row filters, is more flexible and depends on the access model being correct. For regulated fields we lean towards in-flight, because a policy misconfiguration is a plausible event and a column that was never loaded cannot be misconfigured.

Deletion requests interact awkwardly with an immutable raw layer, and this needs deciding before it is urgent rather than during a request. A workable pattern is a deletion log that is applied on every rebuild, plus crypto-shredding where a per-subject key can be destroyed. Which approach satisfies your obligations under GDPR, the UK equivalent, Australian privacy law or a state level US regime is a question for your counsel, and we will build to the decision they give you rather than offer one.

On our side of the arrangement, the practical controls are the ones that matter day to day. Named identities per engineer in your cloud account, scoped roles rather than shared credentials, work performed through a bastion or a browser based workspace, development against masked or synthetic data, and no production extracts on local machines. Data residency is preserved because compute and storage stay in the region you chose. What crosses a border is access under audit, not rows.

Three Situations We Get Called Into

These are patterns rather than named clients. They recur often enough that if one of them describes your situation, the diagnosis below is probably close.

The managed connector bill that tripled without the data growing

A subscription business runs a managed ELT tool across eighteen sources. Sixteen are small SaaS applications and two are large tables in their own PostgreSQL. The invoice climbs steeply quarter over quarter while the underlying business grows at a fraction of that rate. Nobody can explain it.

The cause is usually consumption pricing meeting a churny update pattern. Row based billing counts a row as active if it changed, and a table with a last_seen_at column touched by a background job marks a large share of rows modified every single day. You are paying to re-sync data that has not meaningfully changed.

The fix is to split the estate rather than replace the tool. The two high volume tables move to log-based CDC with Debezium, where the cost is infrastructure that does not scale with row churn. The sixteen small sources stay exactly where they are, because writing and maintaining sixteen API connectors to save a modest amount is a bad trade. The work is a few weeks and the shape of the bill changes permanently.

The reporting replica that is always six hours behind

A B2B software company reports off a read replica of production, on the reasonable theory that this avoids building a warehouse. Then product asks for in-app analytics for customers, finance wants monthly cohorts, and the queries needed for both are heavy enough to make the replica lag. Lag makes the numbers wrong, which makes people distrust the numbers, which is worse than not having them.

Replicas are built for failover, not for analytical scans. The path out is CDC from the replica into a columnar warehouse, incremental dbt models with tenant identity carried through so per-customer analytics can be served safely, and masking applied to the customer fields that the in-app view must never expose. The replica goes back to being a replica.

The part that takes the longest is not the pipeline. It is agreeing what a metric means when the product team and the finance team have been computing it differently for two years, both correctly, from the same rows.

The month-end close that breaks every month

An operations heavy business loads nightly from an on-premise SQL Server. Every month end, the finance team finds that prior period figures have moved, and every month somebody rebuilds a spreadsheet by hand to explain the difference. It is treated as a fact of life.

The cause is late corrections. Adjustments, refunds and reclassifications are entered against original transaction dates, so a record that belongs to March arrives in April. If the pipeline partitions by ingestion date, March is understated forever. If it overwrites without a record, March silently changes and nobody can say by how much.

The fix has three parts. Partition by event date so figures land in the period they belong to. Reprocess a trailing window on every run, sized from the measured arrival delay in that specific business rather than a guess. And keep a restatement log that records exactly which prior period rows changed and when, so the finance team can see the movement rather than discover it. That last piece is what ends the monthly argument, and it is the smallest of the three to build.

What Goes Wrong, and What We Do About It

Every data integration project hits some subset of the following. Naming them in week one is cheaper than discovering them in week six.

Access takes longer than the build

The most common schedule risk by a distance. A read replica has to be provisioned, a network path opened, a database parameter changed with a restart, or a third party vendor has to approve an integration user. None of that is technical difficulty, all of it is calendar time in someone else's queue. We front-load access requests into week one, name the owner for each, and track them as tasks with dates rather than as assumptions.

The source is worse than documented

Timestamps stored without timezone, so the same column means IST in one row and UTC in another. Currency amounts in cents in one table and units in the next. Enumerations with values not present in any documentation. A column called status carrying four historical meanings depending on the year of the row. Profiling before designing catches these. Designing first and discovering later means rework, so the first week is always spent reading the data rather than the documentation about the data.

Nobody agrees what the metric means

Two teams compute active customers differently and both are right within their own context. This is not a pipeline problem and cannot be solved by one, but it will stall the pipeline until it is resolved. We surface the conflict early with a written definition per metric, and push for a named owner who decides. If no owner is nominated, we build both and label them plainly, which is uglier and better than silently picking one.

Deletes are invisible

The source soft deletes with a flag, or hard deletes and expects a full reload to notice, or archives rows to a second table nobody mentioned. Any of those means your warehouse slowly diverges from reality. Handling is decided per table during discovery, and where deletes genuinely cannot be captured, a periodic reconciliation against source counts detects the divergence rather than letting it accumulate quietly.

Key people leave, on either side

Ours and yours. On our side, at least two engineers touch every pipeline from the start and the runbook is written by whoever did not build it, which is the fastest way to find the gaps in it. Handover terms and how a change of staffing is managed are set in the agreement before work begins, and we would rather point you to that document than invent a policy here. On your side, the defence is the same: documentation generated from the code and ownership recorded in metadata, so a departure does not take the map with it.

Scope grows because the data reveals a business problem

This one is usually good news badly timed. You build a pipeline, the numbers become visible, and it turns out the fulfilment process has a gap that has been invisible for years. That is a separate project. We flag it, write it up, and keep it out of the current scope so the pipeline actually ships, because the alternative is an engagement that expands until it delivers nothing.

Where This Sits Alongside Our Other Work

Data integration overlaps with several things we do and is distinct from all of them. If your problem is moving bulk data into somewhere you can analyse it, this is the page. If your problem is two applications needing to talk to each other transactionally, that is a different discipline and our enterprise integration work covers it. Broader platform and warehouse design belongs with data engineering, which is often the same engagement viewed from a wider angle.

Downstream, once clean data is landing reliably, model training and deployment pipelines are covered by our MLOps services in India rather than duplicated here. The cloud foundations underneath, including networking, private connectivity and account structure, sit with cloud architecture, and the deployment automation that ships pipeline changes safely is part of CI/CD pipeline work. If you need staffing rather than a scoped build, you can hire Python developers in India for the ingestion and tooling side of this work.