TL;DR
Of the 12 ETL best practices, four matter most: design from the deadline, make loads rerun-safe, validate at three gates and monitor the data. For a nightly batch warehouse, switch full reloads to incremental loads and MERGE on business keys so a rerun never duplicates rows. For sources that change all day, add change data capture and keep the same validation gates. When a legacy SSIS or Informatica estate is the problem, run old and new pipelines in parallel and cut over only once their outputs reconcile. Quarantine bad records with a named owner so they are neither loaded nor silently lost. Track cost per successful run as well, because reruns and full reprocessing are where ETL budgets leak.
Key Takeaways
An ETL workflow is production-ready when it meets its SLA, survives a rerun, can be observed, recovers from failure and follows governance rules.
Incremental loads by watermark or change data capture, plus MERGE-based upserts, remove most duplicate-row and long-runtime problems.
Validation belongs at three points: on extract, after transform and before publish, with failing records sent to quarantine.
Monitor freshness, row volume, schema and duration, since a job can finish green while the data underneath is wrong.
Track cost per successful run, because reruns and full reloads are where ETL budgets usually leak.
Modernize legacy ETL with a parallel run and table-by-table reconciliation, then cut over against agreed criteria.
Watch on YouTube
Step-by-Step Guide to Automate SSIS to Microsoft Fabric with FLIP
Kanerika walks through automating an SSIS to Microsoft Fabric migration with FLIP, step by step, for teams whose legacy ETL workflows need modernizing.
The Nightly Job That Finished Green
The scheduler showed a green check at 2:14 a.m. Every task in the ETL workflow had run, and nobody was paged. Then, on Monday morning, the revenue dashboard was short a full day of orders.
Finding the cause then took most of the morning. Over the weekend, a source team had renamed a column from order_ts to order_timestamp. The extract used SELECT *, so the column simply arrived under its new name. The transform still looked for order_ts, found nulls, and its date filter quietly dropped every one of Sunday’s orders.
Nothing in that pipeline crashed. Still, it had no contract with its source, no row-count check and no alert on freshness. Those three gaps are exactly what ETL best practices close.
The 12 rules below are tool-neutral. Each one also comes with a check your team can run before the next release. The checks work the same way, so it does not matter whether the pipeline lives in Airflow, Fabric, Databricks or plain SQL scripts.
What Is an ETL Pipeline, and What Makes One Production-Ready?
Two terms often get used interchangeably here. The pipeline is what moves the data, and the workflow is when and in what order it runs. An ETL pipeline extracts data from source systems, transforms it into clean and consistent shapes, and loads it into a warehouse or lakehouse. Reports, models and applications then read it from there.
An ETL workflow is the scheduled, dependency-ordered run of those steps. The orders extract, for instance, has to finish before the daily revenue table that depends on it can build. ETL is one family within the wider set of data pipelines , and it is the one most reporting still depends on.
The Three Phases and What Breaks in Each
Each phase also tends to fail in its own way. That is why the checks later in this guide sit at the boundaries between phases, where a problem can be stopped before it spreads.
Phase What happens What usually breaks
Extract Pull full or changed records from sources such as databases, files, APIs and SaaS apps Renamed columns, API limits, late files, missed deletes Transform Clean, deduplicate, join, convert types and then apply business rules Silent nulls, duplicate keys, logic that drifts from the business definition Load Write to staging first, then to warehouse or lakehouse tables Duplicate rows when a job reruns, partial loads, locks on busy tables
The extract phase is where data ingestion choices matter most, because a missed delete or a late file there flows through every later step.
The Production-Ready Test
A demo pipeline and a production pipeline can run identical code. What separates them is whether the pipeline keeps its promises when sources change, jobs fail and volumes grow. A short test makes that concrete.
Meets an SLA: the data lands before an agreed time with agreed completeness.Safe to rerun: when it runs twice, the result matches a single run.Observable: someone can see freshness, volume and failures without reading raw logs.Recoverable: once a run fails, it resumes or replays from a known point.Governed: access, masking, retention and lineage follow written rules.
If a pipeline fails any of the five, it is still a prototype, however long it has been running. The rest of this guide turns those five tests into 12 practices, grouped by when you apply them.
Kanerika Service
Data Integration Services
Kanerika designs and runs governed ETL and ELT pipelines that connect ERP, CRM, files and SaaS sources to your warehouse or lakehouse, with validation and monitoring built in.
Explore Data Integration ETL Best Practices at a Glance
The 12 ETL best practices fall into four stages: design, build, quality and operate. Practices 1 to 4 are decisions you make before writing code, while 5 to 7 shape the code itself. Practices 8 and 9 decide which data is allowed through, and the last three keep the pipeline healthy once it runs every night.
The 12-Practice Scorecard
Use the table as a scorecard for each production pipeline you own. A team that can answer the last column for every pipeline is in good shape, while a team that cannot has just found its backlog.
Practice What it prevents How to check it
1. Start from the consumer’s SLA Pipelines tuned for the wrong deadline Each dataset has a written freshness target 2. Agree a data contract with each source Surprise changes in schema or meaning Contract lists schema, keys, nulls, owner and notice 3. Default to incremental loads Long runtimes and wasted compute Daily runs move only changed rows, while full refreshes run on a schedule 4. Land raw data, then transform in layers Lost history and no way to replay A raw copy is kept so it can rebuild downstream tables 5. Make every load idempotent Duplicates and gaps after reruns Rerunning yesterday’s load leaves counts unchanged 6. Version and deploy through CI/CD Untested changes in production Every change has a review and a test run before it merges 7. Plan for schema change Breakage from renamed columns Each change type has a defined response 8. Test the pipeline like software Logic errors reaching reports Unit, integration and reconciliation tests run when code changes 9. Quarantine bad records Bad data loaded or lost without a trace Rejected rows land in a table with a reason 10. Build in error handling and recovery Long outages and manual restarts Retries, checkpoints and a runbook exist before go-live 11. Monitor the data, not only the job Jobs that pass while the data is wrong Alerts fire on freshness, volume, schema and duration 12. Tune performance and track cost Rising bills and missed load windows Cost per successful run is reported monthly
Design Practices: Decide Before You Build
Design decisions are cheap on a whiteboard and expensive once a pipeline runs every night. The first four ETL best practices settle the questions that otherwise surface later as incidents.
1. Start From the Consumer’s SLA
Begin with the people and systems that read the data, then work back to the source. For each dataset, ask how fresh it must be, how complete it must be, and when it must land.
The answers differ more than teams expect. A finance close report may accept data that is a day old, but it cannot accept a missing branch. A replenishment feed may need hourly data, although it can live with a few late SKUs.
Dataset (illustrative) Freshness Completeness Delivery deadline
Daily revenue dashboard Previous day 100% of stores 6:00 a.m. local time Inventory replenishment feed 1 hour 99% of SKUs Hourly, on the hour Monthly finance close Month end 100%, reconciled to the ledger Business day 2
Written targets then change engineering choices. For example, a one-hour freshness target rules out a nightly batch. A 100% completeness target also means the pipeline must fail loudly instead of publishing a partial table.
2. Profile Sources and Agree a Data Contract
Before building anything, profile each source. Look at row counts, key uniqueness, null rates, value ranges and how often records change. Profiling turns assumptions into numbers, and it is how a team learns that a customer key is unique in one region but reused in another.
Next, write down a data contract with the source owner. It can be short, so long as it exists, it is versioned and both sides agree to it.
Schema and data types for every field the pipeline reads
Primary and business keys, plus how duplicates are resolved
Null and default rules for required fields
A named owner and an on-call contact
A change notice period, for example 10 business days before a breaking change
The renamed column in the opening story is exactly what a contract prevents. With a notice period in place, the change arrives as a ticket instead of a Monday incident.
Talk to Kanerika
Want a Second Look at Your Pipelines?
Kanerika reviews how your ETL workflows handle reruns, schema change and recovery, then shows where a few targeted fixes would cut incidents and runtime.
Schedule a Demo → 3. Default to Incremental Loads and Reserve Full Refreshes
A full reload copies everything on every run. It is simple to build, then it gets slower and more expensive as tables grow. An incremental load copies only what changed since the last successful run.
There are two common ways to find those changes. A watermark uses a reliable column such as updated_at or an increasing ID. Change data capture (CDC) reads the database’s change log instead, so it also catches deletes and updates that a timestamp can miss.
-- Pull only rows changed since the last successful run
SELECT order_id, customer_id, amount, status, updated_at
FROM src.orders
WHERE updated_at > :last_watermark
AND updated_at <= :run_started_at;
-- After the load commits, store :run_started_at as the new watermarkStore the new watermark only after the load commits, so a failed run retries from the same point. Managed tools also build this logic in. As of October 2026, Microsoft documents that a Fabric Copy job offers full and incremental modes and finds changes through CDC or a watermark. It also tracks the last successful run, so it can resume after a failure.
Kanerika’s guide to Microsoft Fabric data pipelines shows how that fits a wider workflow. Keep full refreshes only for small dimension tables, one-off backfills and periodic reconciliation, since those are where a complete copy earns its cost.
Watermark or CDC: Picking a Default
Choosing between a watermark and CDC comes down to what the source can promise. Open-source tools such as Debezium read the change log directly. Its documentation describes “a set of distributed services to capture changes in your databases” that records row-level changes in a change event stream. Use the table below to pick a default for each source.
Source situation Default pick Why
updated_at is set on every insert and update, and rows are rarely deleted Watermark Simplest to build, with no access to the database log Rows are hard-deleted and reports must reflect it CDC The change log records deletes that a timestamp filter never sees No reliable timestamp or increasing ID CDC There is no column to filter on safely Small dimension or lookup table Scheduled full refresh A complete copy costs little and resets any drift
When a source fits two rows, pick the simpler option and add a periodic full reconciliation to catch what it misses.
4. Land Raw Data, Then Transform in Layers
Write extracted data to a raw landing layer before changing it, and refine it in three stages after that. A raw or bronze layer mirrors the source, and a conformed or silver layer holds cleaned and joined data. Finally, a serving or gold layer is shaped for reports and models. The medallion architecture is the best-known name for this pattern.
Raw retention is what makes replay possible. When a business rule changes or a bug turns up, the team rebuilds downstream tables from raw. Nobody has to ask the source for history it may no longer keep. Layering also makes data warehouse automation easier, because each layer has a clear contract with the one above it.
Build Practices: Pipelines You Can Rerun and Change
Every pipeline will fail and rerun at some point, so the build has to make reruns boring. These three practices cover loading, change control and the shape of incoming data.
5. Make Every Load Idempotent and Safe to Retry
An idempotent load gives the same result whether it runs once or five times. That property is what lets an on-call engineer press retry at 3 a.m. without fear of doubling the revenue table.
The Apache Airflow project puts it plainly in its best practices guide . Its advice is to “treat tasks in Airflow equivalent to transactions in a database,” so a task never leaves incomplete results behind. The same guide also warns against using INSERT during a task rerun, since a blind insert simply adds the same rows again.
Three patterns avoid that trap. MERGE, or upsert, on business keys updates existing rows and adds new ones. Partition overwrite replaces a whole day or hour of data in one step. Delete-then-insert inside a single transaction also works when MERGE is unavailable.
MERGE INTO dw.fact_orders AS t
USING stg.orders_batch AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
UPDATE SET amount = s.amount,
status = s.status,
updated_at = s.updated_at
WHEN NOT MATCHED THEN
INSERT (order_id, customer_id, amount, status, updated_at)
VALUES (s.order_id, s.customer_id, s.amount, s.status, s.updated_at);Note the condition on updated_at. It stops an older replay from overwriting a newer value, which matters when late files arrive out of order.
6. Keep Transformations Modular, Versioned and Deployed Through CI/CD
Treat transformation logic as code, because it is code. Keep it in Git, review every change in a pull request, and run tests automatically before anything reaches production. This way of working is often called DataOps , since it applies DevOps habits to data pipelines.
Small, single-purpose models are far easier to test than one 900-line script, so split logic by business entity or by layer. Picture a pull request that changes how net revenue treats refunds. CI builds that model and everything downstream of it on a sample of production data, then runs their tests.
Before approving the merge, the reviewer sees how row counts and totals differ from the current version. That way, the new definition never reaches a dashboard as a surprise.
Keep development, test and production environments as alike as you can, including realistic data volumes. Follow a shared style guide for naming, and document each model’s purpose, owner and grain next to the code, where people actually read it.
7. Plan for Schema Change
Sources will change shape, so the useful question is whether the pipeline notices and responds in a planned way. Sort changes into additive and breaking, then agree the response for each type before it happens.
Change Example Planned response
Additive: new nullable column A source adds a new column such as loyalty_tier Auto-add to raw, then add to downstream models in the next release Additive: new table or file A new region starts sending data Register it in the contract, route it to raw and alert the owner Breaking: renamed or removed column order_ts becomes order_timestamp Stop at the extract gate, then quarantine the batch and bump the contract version Breaking: type or meaning change Amount switches from cents to dollars Fail range checks until a migration is agreed with the source owner
Detecting the change is the easy half: compare the incoming schema with the contracted one on every run. The harder half is routing each mismatch to its planned response, so the load never has to guess.
Data Quality Practices: Validate at Three Gates
Data quality checks work best at the points where data changes hands, rather than as one big test at the end. Picture three gates along the pipeline. Practices 8 and 9 put these gates to work: tests stop bad logic, and quarantine handles bad records.
The first gate sits on extract and asks whether the right amount of data arrived on time. A second gate then sits after transform and checks the rules the business depends on. Finally, a third gate sits before publish and reconciles totals with the source, so a report never shows a number the source system would dispute.
Gate Typical checks Action on failure
On extract Row count compared to the previous run, file arrival, freshness, schema match Stop the run and alert before any partial data is transformed After transform Not-null and unique keys, valid references, accepted value ranges Quarantine failing rows, then fail the run above a set threshold Before publish Totals and counts reconciled to source, period-over-period change within bounds Hold the publish while the previous table stays live
Common tools support these checks out of the box. For instance, dbt data tests include not-null, unique and relationship tests, and each test selects the records that fail it. Databricks Lakeflow pipeline expectations apply SQL Boolean constraints to each record, then let you retain, drop or fail on the invalid ones.
Set data validation thresholds with the consumer’s SLA in mind. A 2% drop in row count may be normal on a public holiday and alarming on a Tuesday. Where volume is seasonal, compare each run with the same weekday in earlier weeks.
8. Test the Pipeline Like Software
Validation catches bad data, while tests catch bad logic before it ships. A sound test suite has three layers.
Unit tests on individual transforms, using small hand-built inputs with known outputsIntegration tests that run the full pipeline on a representative sample in a test environmentReconciliation tests that compare the output’s counts and totals with the source for the same period
Picture the orders model from the opening story. A unit test feeds it three hand-built rows, one with a null order date, and checks that exactly two rows come out. An integration test then runs the whole workflow on a one-day sample in the test environment. The reconciliation test compares that day’s order count and revenue total with the source, and that check alone would have caught the missing Sunday.
In dbt, the column checks from the after-transform gate are a few lines of YAML next to the model. The file below declares a unique, not-null key and a relationship test that ties every order to a known customer.
version: 2
models:
- name: fact_orders
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: customer_id
data_tests:
- not_null
- relationships:
to: ref('dim_customer')
field: customer_idRun dbt build in CI on every pull request, since it builds each model and runs its tests in dependency order. That way practices 6 and 8 share one gate.
Add a test whenever an incident happens. Over time, the suite then becomes a record of every way the pipeline has failed, and a guard against each one.
9. Quarantine Bad Records Instead of Failing Silently or Loading Them
When a record fails validation, a pipeline has three choices: keep it and flag it, drop it, or stop the whole run. The worst outcome is a fourth one that nobody chose, where the record disappears with no trace.
A quarantine table makes the choice explicit. Each rejected row lands with the rule it broke, the run ID and a timestamp, and a named owner reviews the table on a schedule.
-- Keep the quarantine rerun-safe: clear this run's rejects first
DELETE FROM dq.orders_quarantine
WHERE run_id = :run_id;
INSERT INTO dq.orders_quarantine
(order_id, rule_broken, run_id, rejected_at)
SELECT order_id,
CASE WHEN customer_id IS NULL THEN 'customer_id is null'
ELSE 'amount is negative' END,
:run_id,
CURRENT_TIMESTAMP
FROM stg.orders_batch
WHERE customer_id IS NULL
OR amount < 0;The DELETE on run_id is what keeps the step safe to retry, which is practice 5 applied to rejects. The load into the fact table then reads only rows that pass both rules, so a bad record is never loaded and never lost.
Review cadence should follow the SLA. A daily revenue table needs its quarantine checked each morning, while a monthly finance feed can wait for the close. Fail the whole run only when rejects pass a threshold that would break the SLA, such as more than 1% of the day’s rows.
Operate Practices: Recover Fast and See Problems First
Once a pipeline runs every night, the goal shifts from building it to keeping it honest. These three practices decide how fast a team learns about a problem and how fast it recovers.
10. Build Error Handling and Recovery Into the Workflow
Assume every external call will fail sometimes. Network drops, API limits and locked tables are routine events, so the workflow should treat them that way.
Retries with exponential backoff for transient errors, up to a capped number of attempts
Checkpoints so a long run resumes from the last committed step
Dead-letter tables for records or messages that cannot be processed
One named owner and an on-call rota for each pipeline
A runbook that explains how to rerun, backfill and roll back when something fails
How a pipeline waits between retries matters as much as how often it retries. Google’s Site Reliability Engineering book advises teams to “always use randomized exponential backoff when scheduling retries” and to limit retries per request. In Airflow, that policy is a few task arguments, usually set once as DAG defaults.
# Airflow 2.x task defaults, set once for every task in the DAG
from datetime import timedelta
default_args = {
"owner": "data-platform",
"retries": 3,
"retry_delay": timedelta(minutes=2),
"retry_exponential_backoff": True,
"max_retry_delay": timedelta(minutes=30),
}Each retry then waits longer than the last, and no wait passes 30 minutes. After the third retry the task fails, which is the moment the owner’s alert should fire.
Kanerika Service
Data Engineering Services
Kanerika’s data engineering team designs, builds and modernizes ETL pipelines with idempotent loads, quality gates and monitoring built in.
Explore Data Engineering Orchestrators handle dependencies, retries and schedules for you, and the choice among them matters less than using one consistently. Kanerika compares the main options in its guide to data orchestration tools . Teams running many pipelines can also read its overviews of data pipeline automation and automated data integration .
11. Monitor Freshness, Volume, Schema and Duration, Not Only Job Status
A job status tells you the code ran. However, it says nothing about whether the data is right, which is exactly how the opening story’s dashboard ended up a day short.
Metric What it catches Example alert threshold (illustrative)
Freshness Late or stalled loads Table not updated 30 minutes after its SLA time Volume Missing or duplicated data Row count outside 20% of the same weekday’s average Schema Renamed, added or retyped columns Any difference from the contracted schema Duration Slow queries, skew, resource limits Run time over twice its 30-day median
Lineage turns an alert into a diagnosis, because it shows which upstream table fed the broken one and which reports sit downstream. OpenLineage describes itself as “an open framework for data lineage collection and analysis,” and it is also a project of the LF AI & Data Foundation.
Kanerika covers these signals in more depth in its guides to data observability , data lineage and data pipeline monitoring tools . The same discipline applies to AI data pipelines that feed retrieval or agents, where stale data shows up as wrong answers instead of wrong charts.
12. Tune Performance and Track Cost per Successful Run
Performance work starts with not doing unnecessary work. Prune partitions so queries read only the dates they need. Skip data that has not changed, and push filters down to the source where you can.
Size compute to the job as well. Run independent tasks in parallel, watch memory and skew on large joins, and scale down outside the load window.
A simple metric keeps cost visible. Cost per successful run equals compute plus egress plus rerun cost, divided by the number of successful runs. For example, take illustrative monthly figures of $300 in compute, $20 in egress and $80 in reruns, with 28 successful runs. That gives $400 divided by 28, so about $14.29 per run.
If that number rises while data volume holds steady, look first at reruns and full reloads.
Checklist
Data Modernization Checklist
Put these practices on a plan. Review platforms, pipelines, governance and migration readiness step by step before you modernize your ETL estate.
Get the Checklist → Security and Governance Practices for ETL
ETL pipelines often touch more sensitive data than the systems they read from, because they copy it from everywhere into one place. That also makes the pipeline a natural control point, so apply protection while data moves and long before it reaches a report.
The federal baseline says the same. NIST’s PII guide (SP 800-122, April 2010) says PII “should be protected from inappropriate access, use, and disclosure,” and it suggests matching safeguards. A staging layer that copies every source is a broad point of access, which is why masking belongs there first.
Mask or tokenize personal data in staging, so developers and test environments never see raw identifiers (see data masking )
Run each pipeline under its own service identity, with least-privilege access to the sources and targets it needs
Encrypt data in transit and at rest, including raw landing zones and quarantine tables
Set a retention period for each layer, then delete raw personal data on schedule
Keep an audit trail of who changed pipeline code, ran backfills or read sensitive tables
Record lineage so an auditor’s question can be answered from metadata
Retention deserves extra care in raw layers, since they exist to keep history. GDPR Article 5 sets a storage-limitation principle. Personal data is kept in identifiable form “for no longer than is necessary for the purposes for which the personal data are processed” (Article 5(1)(e)). Raw replay windows therefore need a documented limit, and personal fields may need masking even in the raw layer.
Choosing the Processing Pattern: Batch ETL, ELT, CDC or Streaming
The 12 practices apply to every processing pattern, while the pattern itself should follow the freshness target from practice 1. Pick the slowest pattern that still meets the SLA, because each step toward real time adds work to run and test.
Pattern Typical latency Best fit Watch-outs
Batch ETL Hours Daily reporting, finance, transforms needed before data lands Long windows, full reloads creeping back ELT Hours to minutes Cloud warehouses and lakehouses with elastic compute Raw data sprawl, transform cost inside the warehouse CDC Minutes Operational databases with frequent updates and deletes Log retention, schema changes, event ordering Streaming Seconds Fraud checks, IoT, live operations Late and duplicate events, harder testing, higher running cost
Many teams mix patterns, for example CDC into a raw layer followed by scheduled ELT into serving tables. For the full comparisons, see data pipeline vs ETL and ETL vs ELT . To see how ETL sits inside wider integration work, read data integration vs ETL as well as the guide to enterprise data integration .
Whatever the pattern, the same rules hold. A streaming job still needs idempotent writes, a schema contract and an alert on freshness, and a batch job still needs a runbook.
Tools That Support Each Practice
No single tool covers all 12 practices, so most teams assemble a small stack. The table maps common open-source and managed options to the practices they support. Each tool is one example, not a ranking.
Need Example tools Practices supported
Orchestration, schedules and retries Apache Airflow, Fabric Data Factory pipelines 10 Incremental extract and CDC Fabric Copy job, Debezium 3 Layered, versioned transforms dbt, Spark on Databricks 4, 6 Tests and record-level quality rules dbt data tests, Databricks Lakeflow expectations 8, 9 Lineage OpenLineage 11
Favor tools your team can already run and test in CI. A familiar tool applied with these rules beats a new one applied without them.
Worked Example: Turning a Full Nightly Reload Into Incremental ETL
A worked example shows how several practices combine in one change. The numbers below are illustrative and do not come from a client.
Picture an orders table of 200 GB that a pipeline reloads in full every night. Only about 3 GB of rows change on a typical day, so roughly 1.5% of each run is new information. The other 98.5% is a copy of data that already exists.
Agree the SLA: revenue tables ready by 6:00 a.m. and complete for all stores.
Profile the source and confirm that updated_at is set on every insert and update; if it is not, plan CDC instead.
Run one last full load as a baseline, then record the watermark once it commits.
Switch the nightly extract to rows changed since the watermark, with a short overlap window for late updates.
Replace INSERT with MERGE on order_id, so the overlap and any rerun cannot create duplicates.
Add gate checks: changed-row count within its normal range, no null keys, daily totals reconciled to the source.
Schedule a weekly or monthly full reconciliation to catch deletes the watermark cannot see.
Track run time and cost per successful run before and after the change.
The overlap window in step 4 is a deliberate trade. Re-reading a few minutes of changes costs little, and MERGE makes the resulting duplicates harmless. Deletes are the main gap in a watermark design, which is why step 7 exists, and why CDC is the better choice when deletes matter.
Expect the nightly volume to fall by about 98%, from 200 GB to around 3 GB. Run time usually falls by less than that, since fixed costs such as startup, metadata checks and index maintenance remain.
Case Study
90% Less Data Lag for a Financial Services Provider
Kanerika replaced a three-day KPI cycle built on Excel hand transforms with automated Airflow ingestion, data quality rules and row-level security, cutting reporting latency by 96%.
Read the Case Study → Common ETL Mistakes and the Practice That Prevents Each
ETL incidents tend to repeat a short list of mistakes, and each one maps back to one of the ETL best practices above. Use the table as a quick review during a post-incident meeting.
Mistake Symptom Fix (practice)
Treating job status as data health Green runs, wrong dashboards Monitor freshness, volume and schema (11) Blind INSERT on rerun Duplicate rows, inflated totals MERGE or partition overwrite (5) Full reload of every table Runs that outgrow the load window Incremental loads (3) No contract with source owners Breakage after a column rename Data contracts and schema checks (2, 7) Transforming before landing raw No way to replay history Raw layer and layered transforms (4) Silently dropping bad rows Totals that never reconcile with the source Quarantine with an owner (9) Editing logic directly in production Untraceable changes, no rollback Git, review and CI/CD (6) No runbook or named owner Long outages when jobs fail at night Error handling and recovery (10)
If a team can fix only three items first, start with monitoring, idempotent loads and a quarantine table. Those three turn silent failures into visible, recoverable ones.
How Kanerika Applies ETL Best Practices
Kanerika builds and modernizes data pipelines for enterprises, and the 12 practices above are the working rules for that delivery. Its data engineering services team applies them to new builds and to migrations of older ETL estates alike.
Modernizing Legacy ETL Without Breaking Reports
Legacy estates, often SSIS packages or Informatica mappings built up over a decade, rarely fail because of one bad job. They fail because nobody can see all the jobs at once. So Kanerika starts each modernization with an inventory of packages, dependencies, schedules and the reports that consume them.
The new pipelines then run in parallel with the old ones. Outputs are reconciled table by table. Cutover happens against agreed criteria, such as matching totals over a set number of consecutive runs, and never on a calendar date alone.
Reconciliation goes beyond row counts. Daily totals, totals by region or product, null rates and sampled row-level differences are compared too. That matters because two tables can match on count and still disagree on value.
Kanerika’s FLIP migration accelerators automate up to 80% of the migration process, according to Kanerika’s November 2025 accelerator announcement . Supported paths include SSIS to Microsoft Fabric, Informatica to Databricks , and Azure Data Factory or Synapse to Fabric. For SSIS estates heading to Databricks, see the SSIS to Databricks datasheet .
The same announcement also reports client results from the FLIP Data Accelerator. They include a 30% improvement in data processing speed and a 40% reduction in operational costs. Teams on those paths can go deeper with the SSIS to Fabric migration guide or the Informatica vs Databricks comparison.
Case Study: 90% Less Data Lag for a Financial Services Provider
A financial services provider waited three days for its KPIs. Its data sat in Amazon S3 with little metadata, while analysts transformed it by hand in Excel. No automated data quality rules existed either.
Kanerika built Airflow 2.0 pipelines that moved data from S3 on a schedule. Databricks processed and enriched it, and Dremio served it to reporting teams. Automated data quality rules checked the data on the way in, while row-level security controlled who could see what. Mapped to this guide, the work covered scheduled, orchestrated ingestion (practice 10), automated quality rules at the gates (practice 9) and governed access.
The results were a 90% reduction in data lag from source to consumption and a 96% reduction in reporting latency from the old three-day cycle. BI adoption also rose by 100%.
What to Ask a Pipeline Partner
Choosing who builds the pipelines matters as much as choosing the tools. Ask any partner how they handle reruns, where their validation gates sit and what their runbooks look like. Those three answers reveal more than a capability deck, because they describe how the team behaves on a bad night.
Evidence beats answers, too. A reconciliation report from a past migration says more about delivery quality than any list of tools. A sample runbook or the alert thresholds set on a live pipeline say just as much. Asking how they would apply practices 5, 9 and 11 to one of your own pipelines shows whether those rules are everyday habits or slogans.
Kanerika’s podcast takes up that question directly, from the buyer’s side of the table. The episode below discusses how to choose the right data engineering partner, and it makes a useful listen before a vendor review or a build-versus-partner decision.
Pre-Production ETL Best Practices Checklist
Run this checklist before any new pipeline goes to production, and again before any major change. Each item is a yes or no question, so a single “no” is a clear task for the backlog.
Each dataset has a written freshness, completeness and delivery target.
Every source has a versioned data contract with a named owner.
Daily runs load incrementally, while full refreshes run on a schedule.
Raw data lands first and is kept for a documented window.
A rerun of any load leaves row counts unchanged.
All pipeline code is in Git and deploys through CI/CD.
Additive and breaking schema changes have a defined response.
Unit, integration and reconciliation tests run before every change merges.
Failing records go to a quarantine table with a reason and an owner.
Retries, checkpoints and a dead-letter table are in place.
Alerts fire when freshness, volume, schema or duration drift.
Lineage is captured from source to report.
Personal data is masked, encrypted and deleted on schedule.
Cost per successful run is reported every month.
A runbook explains how to rerun, backfill and roll back when a job fails.
For a wider review of platforms, governance and migration readiness around the pipeline itself, Kanerika’s Data Modernization Checklist covers the steps this list leaves out.
The Bottom Line
Which ETL best practices matter most? Design from the consumer’s SLA, make every load safe to rerun, validate at three gates and watch the data as well as the job. Those rules are what turn an ETL pipeline into a reliable, scalable one that fails loudly, recovers quickly and keeps the reports right.
Frequently Asked Questions
What are ETL best practices?
ETL best practices are the working rules that keep data pipelines reliable as they grow. The core set covers design from the consumer’s SLA, source data contracts, incremental loads and layered transforms. It also covers idempotent loads, CI/CD, schema change plans, testing, quarantine, recovery, data monitoring and cost tracking. Each rule should come with a check a team can run.
What is an ETL pipeline, and what is it used for?
An ETL pipeline extracts data from source systems, transforms it into a clean and consistent shape, and loads it into a warehouse or lakehouse. Companies use it to feed dashboards, finance reports, data science and AI applications. It also brings data from many systems, such as ERP, CRM and web apps, into one trusted place for analysis.
What is the best way to handle errors in ETL?
Handle ETL errors by type. Retry transient failures, such as timeouts and API limits, with exponential backoff and a cap on attempts. Send bad records to a quarantine or dead-letter table with the rule they broke. Use checkpoints so long runs resume from the last committed step. Give each pipeline a named owner and a written runbook.
How do you monitor ETL pipelines in production?
Monitor the data as well as the job. Track freshness against the SLA, row volume against the same weekday, schema against the contract, and run time against its recent median. Alert the owner when any signal crosses its threshold. Add lineage, for example with OpenLineage, so the team can trace a broken table to its upstream cause.
How do you handle schema changes in ETL pipelines?
Compare the incoming schema with the contracted schema on every run. Let additive changes, such as a new nullable column, flow into the raw layer automatically. Stop the load and quarantine the batch when a column is renamed, removed or changes type. Agree a notice period with each source owner, and version the data contract after every change.
How can you improve ETL performance and reduce cost?
Start by avoiding work you do not need. Load incrementally, prune partitions and skip data that has not changed. Run independent tasks in parallel, watch memory and skew on large joins, and right-size compute for each job. Measure cost per successful run each month, and look at reruns and full reloads first when that number climbs.
Is ETL part of DevOps?
ETL work follows DevOps practices when teams treat pipelines as software, an approach often called DataOps. Pipeline code lives in Git, changes go through review, and automated tests run in CI before deployment. Environments stay consistent from development to production. Monitoring and incident response then cover data quality, freshness and the health of each pipeline run.
Which programming language is used to build ETL pipelines, and is SQL enough?
SQL handles most transformation work in modern warehouses and lakehouses, and many pipelines are mostly SQL. Python is the most common choice for extraction, orchestration, API calls and complex logic. Scala and Java appear in some Spark workloads. A practical team uses SQL for set-based transforms and Python for the glue around them, with both kept in Git.
Which tools are used for ETL pipelines in 2026?
ETL tools fall into a few categories. Orchestrators such as Apache Airflow schedule and order tasks. Managed services such as Fabric Data Factory copy data from many sources. Transformation frameworks such as dbt run SQL models and tests in the warehouse. Lakehouse platforms such as Databricks handle large-scale processing. The right mix depends on sources, latency and team skills.
Is ETL obsolete, and will AI replace it?
ETL is still essential. Many teams now run it as ELT or change data capture on cloud platforms, and the core job of extracting, cleaning and loading data remains. AI tools help write transforms, document pipelines and spot anomalies. People still set the SLAs, data contracts and quality rules, and they own the result when something breaks.
What should an ETL checklist include?
An ETL checklist should confirm a written SLA, a source data contract and an incremental load design. It should also cover idempotent loads, a raw layer, version control and CI/CD. Add a schema change plan, unit and reconciliation tests, quarantine rules, retries, alerts on freshness and volume, a runbook, access controls, masking, retention and cost tracking.
How do you migrate legacy ETL pipelines without losing data?
Start with a full inventory of jobs, dependencies, schedules and downstream reports. Rebuild or convert the pipelines, then run old and new side by side on the same inputs. Reconcile row counts and totals table by table. Cut over only when outputs match for an agreed number of runs, and keep the legacy jobs ready for a short rollback period.
What are the main steps of the ETL process?
The ETL process has three core steps. Extract pulls full or changed records from databases, files and APIs. Transform cleans, deduplicates, joins and applies business rules. Load writes the result into staging and then into warehouse or lakehouse tables. Production pipelines add two steps around these three, which are validation of the data and monitoring of every run.
What is an ETL workflow? Can you give an example?
An ETL workflow is the scheduled, dependency-ordered run of extract, transform and load tasks. A typical example starts nightly at 1 a.m. It extracts changed orders and customers, validates row counts, merges them into warehouse tables and rebuilds the daily revenue table. It then checks totals against the source and publishes the dashboard by 6 a.m.
How do you automate an ETL workflow?
Automate an ETL workflow with an orchestrator that runs tasks on a schedule or an event and respects their dependencies. Add automatic retries with backoff, alerts on failure and checks on freshness and volume. Keep pipeline code in Git and deploy it through CI/CD. Kanerika’s guide to automated data integration covers the wider platform choices for this work.
How do you build a scalable ETL pipeline?
Build a scalable ETL pipeline by loading only changed data, partitioning large tables and running independent tasks in parallel. Land raw data first, then transform it in layers so each stage can scale on its own. Make loads idempotent so retries are safe. Size compute to each job and track cost per successful run as volume grows.
What is incremental loading in ETL, and when does change data capture help?
Incremental loading copies only the rows that changed since the last successful run. A watermark column such as updated_at is the simplest way to find those rows. Change data capture reads the database change log. It helps when deletes matter, when updates are frequent, or when the source has no reliable timestamp column to filter on.
How do you prevent duplicate records when an ETL job reruns?
Make every load idempotent, so a rerun gives the same result as the first run. Use MERGE or upsert on business keys, overwrite whole partitions, or delete and insert inside one transaction. Avoid a plain INSERT on rerun, which the Apache Airflow best practices guide warns against. Then confirm that a repeat run leaves row counts unchanged.
What data quality checks should an ETL pipeline run?
Run checks at three gates. On extract, check row counts, file arrival, freshness and schema. After transform, check for null keys, duplicate keys, broken references and values outside agreed ranges. Before publish, reconcile totals and counts with the source system. Send failing rows to a quarantine table with a reason, and alert the named owner right away.
How do you test an ETL pipeline?
Test an ETL pipeline in three layers. Unit tests check each transform against small inputs with known outputs. Integration tests run the whole pipeline on a representative sample in a test environment. Reconciliation tests compare output counts and totals with the source. Run all three in CI on every change, and add a new test after each incident.
