Data that lives in source systems is not data you can use. ETL pipelines are what move it to where decisions get made.

Scheduled extraction from ERP, CRM, WMS, flat files, databases, and SaaS APIs with the orchestration infrastructure that makes each run reliable, observable, and recoverable when something goes wrong. Apache Airflow as the orchestration platform for pipelines requiring complex dependency graphs, custom operators, and rich monitoring, DAGs define the task dependencies, execution schedule, and retry behaviour; the Airflow UI provides run history, task-level logs, and re-run capability for failed runs without reprocessing already-loaded data. Prefect as an alternative for teams that prefer Python-native workflow definitions with less operational overhead than self-hosted Airflow, Prefect Cloud for managed orchestration at small to medium pipeline scale. Incremental load patterns for tables too large to re-extract fully on each run: watermark-based incremental extraction using the source table's updated_at or created_at column to fetch only records modified since the last successful run, with the high-water mark stored in a pipeline state table. Full-table extraction reserved for small lookup tables and dimension tables where insert-only semantics are not guaranteed and deletions need to be detected. SAP extraction via the SAP NetWeaver RFC SDK, calling BAPIs (Business Application Programming Interfaces) for master data and transaction data extraction, the standard SAP extraction approach that does not require direct database access to the SAP schema. Oracle E-Business Suite extraction via database views and the Oracle JDBC driver. Retry logic with exponential backoff for transient failures: a pipeline that fails due to network timeout retries at 5 minutes, 15 minutes, and 1 hour before alerting, reducing false-alarm escalations while surfacing genuine failures quickly.

Raw data landing in Snowflake, BigQuery, or Redshift in its source form before any transformation runs in the warehouse, the ELT pattern that preserves full source fidelity and allows transformations to be rebuilt without re-extracting from source systems. Snowflake for teams prioritising near-zero storage cost on compressed columnar data, automatic multi-cluster scaling under heavy concurrent query load, and Time Travel for accessing historical data states for up to 90 days. BigQuery for Google Cloud environments and teams with variable query patterns, serverless query pricing (per-byte scanned) works well for intermittent workloads, with reservations purchased for predictable high-volume workloads. Redshift for AWS-native organisations with large volumes of structured data and teams that want tight integration with the AWS ecosystem. dbt (data build tool) as the transformation layer: SQL models in version-controlled .sql files with a {{ ref() }} dependency graph that dbt uses to determine execution order and compile a lineage DAG. Three-layer architecture standard in dbt projects: staging models (stg_*) rename and lightly cast source columns without business logic; intermediate models (int_*) join and aggregate across sources; mart models (fct_* and dim_*) produce the business-ready tables analysts query directly. Incremental materialisations for large fact tables: the is_incremental() Jinja block filters the transformation to process only new or updated records rather than the full table on each run, reducing warehouse compute cost by 80-95% on tables above 100 million rows. Column lineage documentation auto-generated from dbt model definitions so analysts can trace a dashboard metric back to the source column and the transformation logic that derived it.

Connectors for the SaaS tools that hold business data and expose it via REST or GraphQL APIs, built with the rate limiting, pagination, and incremental pull patterns that make high-volume production ingestion reliable. Salesforce extraction via the Bulk API 2.0 for large object volumes (Accounts, Contacts, Opportunities, Activities): the Bulk API handles 150 million records/day per API call without count against the REST API governor limits, processes jobs asynchronously so the connector doesn't block, and supports incremental queries via SOQL WHERE LastModifiedDate > :lastRunTimestamp for changed-record-only extraction. HubSpot CRM extraction via the HubSpot Contacts, Deals, Companies, and Engagements APIs with cursor-based pagination using the vidOffset or after cursor token to walk through paginated responses without missing records on reruns. Stripe financial data via the Stripe Payments, Subscriptions, and Invoices list endpoints with created[gt] timestamp filters for incremental extraction. Shopify extraction via the Admin REST API with the updated_at_min parameter for incremental order and product catalogue extraction, and the GraphQL Bulk Operations API for full catalogue extracts that exceed REST pagination limits. Google Ads and Meta Ads performance data via their respective reporting APIs for marketing spend and campaign performance. Authentication token management: OAuth 2.0 refresh token rotation stored in a secrets manager (AWS Secrets Manager, GCP Secret Manager) with automatic refresh before expiry, preventing pipeline failures from expired tokens. Custom REST API connectors built from an OpenAPI specification or API documentation for internal systems without Fivetran or Airbyte connector support, typically 3 to 5 days engineering per connector depending on API complexity and authentication mechanism.

Change Data Capture (CDC) replication from MySQL, PostgreSQL, SQL Server, and Oracle that captures every insert, update, and delete at the row level as it happens in the source database, without full table scans, without polling queries that load the production database, and without the delete blindness that makes incremental-by-timestamp extraction incorrect for tables where rows are deleted. Debezium as the CDC engine for open-source, production-proven database log reading: Debezium connects to the database's transaction log (MySQL binlog, PostgreSQL WAL via logical replication slot, SQL Server CDC tables, Oracle LogMiner) and streams change events as structured JSON messages to a Kafka topic or directly to the target system. The production database sees only the standard replication client connection, CDC does not add queries to the production workload beyond what a read replica connection would. Kafka Connect sink connectors for warehouse delivery: Confluent JDBC Sink Connector or BigQuery/Snowflake-specific connectors consume the Debezium change stream and write upserts to the target table at configurable batch intervals. Full row history preservation in the warehouse using Type 2 slowly changing dimension (SCD2) pattern: each row update appended as a new record with effective_from, effective_to, and is_current columns rather than overwriting the previous value, the audit trail for tables where row history matters for compliance or analytics. Use cases where CDC is the correct choice over watermark-based incremental extraction: tables with no reliable updated_at column; tables where deletes are frequent and must be propagated; tables too large to re-extract in a batch window; replication latency requirements below 1 minute; and event sourcing patterns where every state transition is a business event that downstream consumers need to process.

Run logging, row count validation, schema change detection, and freshness SLA monitoring across all pipeline jobs, the observability layer that means a pipeline failure is discovered by the monitoring system, not by an analyst who noticed a dashboard stopped updating. Row count validation at every pipeline stage: the number of records extracted, the number after transformation (checking for unexpected row inflation or loss from join operations), and the number successfully loaded compared to the expected range based on historical run statistics, a run that delivers 10% of the normal record count triggers an alert rather than silently loading partial data. Schema change detection comparing the source schema against the expected schema on every extraction run: new columns logged and optionally auto-added to the raw table; removed or renamed columns that would break transformation models trigger an alert before the load step runs, preventing broken downstream dbt models from silently producing null values. Data freshness SLA monitoring: each pipeline has a configured maximum acceptable lag (e.g., the sales pipeline must complete by 06:00 daily); a pipeline that hasn't delivered fresh data by the SLA deadline triggers a Slack or email alert to the on-call data engineer. Great Expectations as the data quality framework for custom assertion rules beyond row counts: assertions on value ranges (order amounts between $0 and $1M), referential integrity (every order in the fact table has a matching customer in the dimension table), and completeness (no more than 5% null in required columns) run as dbt test blocks on every model refresh and fail the pipeline if assertions breach. Central Airflow or Prefect monitoring dashboard: run status, last success timestamp, duration trend, record counts, and alert history across all jobs in one view, the data engineering team's operational health view without navigating multiple tools.

dbt SQL models that define your business entities, metrics, and reporting constructs in version-controlled SQL with full lineage documentation, replacing the ad-hoc analyst queries and undocumented spreadsheet formulas that make data definitions inconsistent across teams. Model architecture follows the staging-intermediate-mart convention: stg_salesforce__accounts.sql normalises and casts the raw Salesforce accounts table with no business logic; int_customers.sql joins across the CRM and billing system to produce a unified customer entity; fct_revenue.sql calculates recognised revenue per month per customer using the business logic agreed with Finance, each model has a single clearly defined purpose and can be changed without breaking layers above or below it. Column-level documentation in schema.yml files: every output column in every mart model described with its definition, the source column it derives from, and the business rule applied, generated as a dbt Docs site that analysts browse without asking the data team what a field means. dbt tests on every model: not_null and unique tests on primary keys to catch extraction or join bugs that produce duplicate or missing records; relationships tests between fact and dimension tables to catch referential integrity violations; custom test_between assertions for business rules (revenue must be positive, discount percentage must be between 0 and 100). dbt source freshness checks validate that source tables in the warehouse have been updated within the expected window before running downstream models, so a dbt run that begins before an upstream pipeline has completed fails fast at the source check rather than silently producing stale mart data. Model performance optimisation: query profiling on slow-running models with warehouse-specific optimisations (Snowflake clustering keys, BigQuery partition pruning, Redshift sort and dist keys applied based on query patterns) to keep mart refresh time below the freshness SLA.