Design every task to be idempotent
A task is idempotent if rerunning it produces the same result as running it once, which is non-negotiable because pipelines fail and orchestrators retry. Rationale: retries, catchups, and backfills are routine, and non-idempotent steps duplicate or corrupt data on the second run. Do: overwrite a deterministic partition or MERGE on a stable key. Don't: blindly append rows, which silently duplicates data whenever a task reruns after a partial failure.
Prefer ELT and keep the raw data
Land raw, untransformed data first and transform it in place using the warehouse's compute, retaining the raw layer as an immutable record. Rationale: cheap cloud storage makes keeping raw data affordable, and it lets you re-run or fix transformations without re-extracting from fragile source systems. Do: keep a bronze/raw layer and rebuild downstream tables from it. Don't: discard source data after transforming, leaving you unable to reprocess when logic changes or a bug is found.
Partition on the column you filter by, and avoid tiny partitions
Choose a partition key (usually a date) that matches your most common query filter so engines can prune and scan less. Rationale: partition pruning is one of the largest cost and performance levers in analytics, but over-partitioning creates the small-files problem and metadata overhead. Do: partition by ingestion or event date at a sensible grain and compact small files. Don't: partition by a high-cardinality key like user_id, which explodes into millions of tiny partitions.
Test data like you test code
Encode data-quality expectations as automated tests (uniqueness, not-null, referential integrity, accepted ranges) that run in the pipeline. Rationale: bad data is a defect, and catching it at the source is far cheaper than debugging a wrong dashboard weeks later. Do: fail or quarantine data that violates a contract before it reaches consumers. Don't: rely on consumers to notice anomalies, which turns every data issue into a downstream fire drill.
Make schema changes explicit with data contracts
Treat the schema and semantics of a published dataset as a versioned contract between producer and consumers. Rationale: silent upstream changes are the most common cause of broken pipelines, and a contract turns the interface into a testable, negotiable artefact. Do: version the schema, add producer-side tests, and announce breaking changes with a migration window. Don't: rename or repurpose a column in place and let every downstream consumer discover it by breaking.
Separate orchestration from compute
Let the orchestrator decide what runs, in what order, and what happens on failure, while a separate engine does the heavy processing. Rationale: mixing concerns makes pipelines hard to scale, test, and reason about, and couples scheduling logic to data logic. Do: define dependencies and retries in Airflow/Dagster/Prefect and run the work in Spark, the warehouse, or dbt. Don't: hide critical scheduling and retry logic inside ad hoc scripts triggered by cron with no observability.
Process by event time and use watermarks in streaming
In stream processing, aggregate on the time an event actually occurred, not when it was processed, and use watermarks to bound how long you wait for late data. Rationale: networks and producers deliver events out of order, so processing-time aggregations are non-deterministic and wrong. Do: define event-time windows with a watermark and a documented late-data policy. Don't: window on processing time and assume events arrive in order, which silently miscounts under load or replay.
Handle late and out-of-order data deliberately
Decide explicitly what happens to events that arrive after their window's watermark: drop, side-output, or allow bounded updates. Rationale: late data is inevitable, and an unstated policy means the system makes the decision for you, often by silently dropping records. Do: route late events to a dead-letter or correction path and monitor their volume. Don't: assume late data does not exist, then discover gaps only when totals fail to reconcile.
Classify and protect sensitive data from ingestion
Identify PII and regulated data at the point of ingestion and apply access control, masking, and retention accordingly. Rationale: privacy obligations (such as GDPR) attach the moment you store personal data, and retrofitting protection across a sprawling lake is expensive and error-prone. Do: tag sensitive columns in the catalog, restrict access by role, and mask in non-production. Don't: copy raw production data containing PII into open analytics environments "temporarily".
Capture lineage and metadata automatically
Record where each dataset comes from, how it was transformed, and who owns it, ideally at the column level, in a catalog. Rationale: lineage enables impact analysis, debugging, and compliance, and manual documentation always rots. Do: emit lineage from your transformation and orchestration tools into a catalog like DataHub or OpenMetadata. Don't: rely on tribal knowledge and stale wiki pages to explain where a number came from.
Monitor data health, not just job success
Observe freshness, volume, schema drift, and value distributions, not only whether a job exited zero. Rationale: a pipeline can succeed technically while producing empty, stale, or anomalous data, and consumers feel that long before any alert fires. Do: set thresholds on row counts, freshness, and null rates and alert on anomalies. Don't: treat a green DAG as proof the data is correct.
Make incremental loads the default, not full reloads
Process only new or changed data using change data capture, high-watermark columns, or partition-scoped runs. Rationale: full reloads waste compute, lengthen pipelines, and increase load on source systems as data grows. Do: track a watermark or use CDC and upsert changes idempotently. Don't: truncate-and-reload large tables every run, which is slow, costly, and brittle once volume grows.
Manage cost as a first-class engineering concern
Treat compute and storage spend as an engineering metric you measure and optimise, especially on usage-billed cloud platforms. Rationale: inefficient queries, full scans, and idle clusters silently dominate the bill, and cost is a real constraint on what the platform can do. Do: prune partitions, select only needed columns, right-size clusters, and set retention/TTL on cold data. Don't: run unbounded SELECT * scans on multi-terabyte tables or leave large clusters running idle.
Keep transformations version-controlled and reviewed
Define transformations as code in version control with tests, code review, and CI, the same as application software. Rationale: ad hoc SQL run by hand is unreproducible, untested, and impossible to audit or roll back. Do: model transformations in dbt or equivalent, review changes, and run tests in CI before deploy. Don't: edit production tables with one-off scripts that leave no history and no test coverage.