Built for teams with 3+ sources and multiple stakeholders. For a single source, a lighter setup (dlt + dbt + a managed warehouse) is enough.
Suggested
Stacks:
Upgrade Outcomes & ROI
Click any outcome to highlight components:• Interactive
1.
Save Time
Data updates itself, and fixes or new reports ship on the same day.
• Before: Scripts and exports run by hand;
failures discovered only when a client asks. Stored procedures make changes slow and risky.
• With this pipeline: Pipelines run
themselves every night and alert the team on Slack only when something breaks. Developers test changes
on a copy of production in seconds, so fixes and new reports ship quicker with standardised flow.
• Before: Always-on database or
orchestration servers billing 24/7; recurring monthly BI viewer license fees per user.
• With this pipeline: Compute runs only
while jobs run, and reports have no per-viewer licence. Costs grow with the data you process, not with
the number of people reading reports.
Any data engineer can pick it up, and nothing depends
on one person.
• Before: Logic hidden in stored procedures
or one person's laptop. Breakages found by clients; rollback is guesswork.
• With this pipeline: Every rule, model and
report lives in Git using standard open-source tools. A new engineer can read it, change it safely and
roll it back, without relying on whoever built it.
New clients on the same data sources onboard through configuration, without custom code or per-client database clones.
• Before: Adding a client requires manual MySQL database setup in AWS, custom stored procedure parameters, and duplicating Power BI files.
• With this pipeline: Each client reuses the same dbt models and dashboard templates. Onboarding is a config entry, scripted Snowflake role setup, and an automated Evidence build behind Cloudflare Access, with row-level security enforced by Snowflake. A new type of data source still needs its own dlt source and dbt models, added once and reused for every later client.
8 practical data engineering failure scenarios and
handling solutions
Click to expand
1
What happens when an API rate-limits (HTTP 429) or goes offline?
• Handling: dlt retries failed API requests up to 5 times with exponential backoff (15s to 240s). Dagster retries the run once if the failure persists.
• If retries fail: Dagster halts with exit code 1. Downstream Gold assets are not materialized, so live tables keep the last verified sync, and a failure log alerts Slack #data-ops.
2
What happens when a SaaS API changes its schema?
• Handling: dlt adds new API fields as nullable columns without breaking tables. Removed or renamed fields are not dropped: the old column stays and fills with NULLs.
• Downstream models: dbt models use explicit column SELECTs, so extra columns are ignored, and not_null plus row-count/freshness tests on required columns halt Gate 2 when data goes missing.
3
What happens when data needs to be backfilled?
• 1st-Time Sync (Onboarding): dlt checks
last_sync_at; finding it null, it switches automatically from incremental to full historical load (e.g. 3
years).
• On-Demand Backfill: Engineer triggers Dagster
backfill with tenant_id and date range (start_date, end_date). dlt extracts that slice and dbt overwrites
those partitions.
4
What happens when duplicate records arrive? (Which tool handles what)
• dlt (Ingestion): Appends incoming records into
Bronze with metadata (_tenant_id, _ingestion_id, _sync_at).
• Snowflake + dbt (Transform): Silver models
deduplicate deterministically using ROW_NUMBER() by updated_at, _ingestion_id.
• dbt test (Quality): Asserts unique on
invoice_id in schema.yml. If duplicates survive, build halts with exit code 1.
5
What happens when business data mappings are missing or ledgers do not reconcile?
• 4 Critical Checks: Custom SQL assertions in
tests/*.sql check unmapped staff rates, unmapped GL account codes, ledger total mismatches, and orphaned
WIP jobs.
• Result: If any check fails, Gate 2 halts,
Dagster locks Gold tables, and half-calculated numbers never publish to production data table/dashboards.
6
How do we test in Dev without asking upstream to manually sync staging data?
• Snowflake PR Clone: On PR open, CI runs CREATE SCHEMA analytics_pr_123 CLONE prod.analytics;
in seconds. Instant real table access with no extra storage until the clone's data diverges. CI drops the schema on merge.
• dbt Tenant Scoping: In local dev, dbt run --target dev
automatically scopes queries to test tenant 1042 with complete multi-year financial history via Jinja.
Models compile in 3 seconds without
waiting for a manual dump.
7
How do we keep bugs from ever reaching Production?
• 3 Gates in Dev: All changes must pass Gate 1
(Preflight), Gate 2 (dbt test), and Gate 3 (Evidence build) against a Dev database clone first.
• Code-First Dashboards: Evidence.dev reports live as SQL and markdown in git. Changes are tested in CI before merge, providing version-controlled reports with automated builds.
8
What happens if a hidden bug slips through to Production?
• Before release: dbt build skips every downstream model when an upstream test fails, so Gold is never built from bad Silver data. Failed runs alert #data-ops and are fixed or reverted before the next run.
• After Release: If a subtle calculation bug
slips through, engineer opens a revert PR (git revert commit). GitHub Actions fast-tracks revert deploy,
restoring verified code in minutes.