Zero-ETL Explained: Aurora, DynamoDB & Salesforce Warehouse

zero-etl is the marketing term that quietly rewrote how operational data reaches the analytics warehouse — and it is one of the most misunderstood phrases a data engineer will be asked to define in an interview, because the name promises something the technology does not actually deliver. The pitch is seductive: point a fully managed service at your Aurora cluster, your DynamoDB table, or your Salesforce org, and the rows show up in your warehouse seconds later with no pipeline to build, no Airflow DAG to babysit, no connector to patch at 3 AM. That part is real. What the name hides is that the transform — the T that the acronym claims to have zeroed — has not vanished at all; it has simply moved from a pipeline you owned to SQL you run inside the warehouse after the data lands.

This guide is the walkthrough you wished existed the first time someone asked "so what actually is zero-ETL, and where did the transformation go?" It opens the box in layers: the managed, change-data-capture replication that powers every zero-ETL integration, the Aurora-to-Redshift path that mirrors an operational relational database into a warehouse in near-real-time, the two ways DynamoDB reaches a warehouse (a zero-ETL integration and a point-in-time export to S3), the managed-connector story for SaaS sources like Salesforce, and — most important for architecture interviews — the honest checklist for when zero-ETL fits versus when a classic ETL pipeline is still the right call. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the ETL practice library →, rehearse the source-system fundamentals on the database practice library →, and sharpen the load-and-model axis with the data-processing practice library →.

---

On this page

• What zero-ETL means — and what it does not
• Aurora zero-ETL integration to Amazon Redshift
• DynamoDB to the warehouse — zero-ETL and S3 export
• SaaS sources — Salesforce and the managed-connector path
• When zero-ETL fits versus classic ETL
• Cheat sheet — zero-ETL recipes
• Frequently asked questions
• Practice on PipeCode

---

1. What zero-ETL means — and what it does not

Zero-ETL is managed replication with in-warehouse transformation — not the absence of transformation

The one-sentence invariant: zero-ETL is a fully managed, change-data-capture-based replication service that lands your operational data in a warehouse within seconds — it eliminates the extract-and-load pipeline you used to hand-build and operate, but the transform never disappears; it moves downstream into the warehouse as SQL you run after the data arrives, which is why zero-ETL is more honestly described as managed replication plus ELT. The name is a claim about operations, not about data shape: what has gone to zero is the pipeline code, the schedulers, the connector maintenance, and the on-call surface — not the modelling, the deduplication, the type coercion, or the business logic. Every downstream team that assumes "zero-ETL means the data arrives clean and modelled" ships a bug, because what actually arrives is a faithful, near-real-time mirror of the source schema, warts and all.

What zero-ETL actually removes.

• The extract step. There is no SELECT ... WHERE updated_at > watermark you write and schedule. The provider taps the source's native change stream (binlog, logical replication, DynamoDB streams, a SaaS change API) directly.
• The load step. There is no S3 staging bucket you manage, no COPY command you author, no micro-batch you tune. The managed integration writes into the target for you.
• The pipeline you operate. No Airflow DAG, no Kafka Connect cluster, no Debezium connector to patch, no dead-letter queue to drain. The failure surface you owned becomes the provider's responsibility, monitored through a handful of system views.
• The seed-plus-incremental plumbing. The integration performs an initial snapshot (the seed) of existing rows and then switches to streaming ongoing changes automatically. You did not write the snapshot-then-tail logic; it is built in.

What zero-ETL does not remove.

• Transformation. Type mapping, denormalisation, slowly-changing-dimension logic, currency conversion, PII masking — all of it still has to happen. With zero-ETL it happens after the load, as views, materialized views, or a dbt project running inside the warehouse. The architecture is ELT, not "no T".
• Schema coupling. Because the target mirrors the source, an ALTER TABLE on the source propagates into the warehouse. Your downstream models are now coupled to the operational schema; a column rename upstream can break a dashboard downstream.
• Modelling responsibility. A raw mirror of orders, order_items, and customers is not a star schema. Someone still builds the facts and dimensions; zero-ETL just delivers the raw ingredients faster.
• Cost. Replication consumes warehouse storage and compute (and, for some sources, change-stream read capacity). "No pipeline" is not "no bill."

The 2026 reality — zero-ETL is a same-cloud, operational-to-analytics mirror.

• The sweet spot is narrow and deep. Zero-ETL shines when the source and target live in the same cloud, the source is on the provider's supported list (Aurora, RDS, DynamoDB, and a growing set of SaaS applications for AWS; equivalents on other clouds), and you want a low-latency copy you will model after it lands.
• It is CDC with the sharp edges filed off. Under the hood every zero-ETL integration is change data capture — the same binlog/WAL/stream tailing you would build with Debezium — but productised, so you never touch a replication slot or a connector config.
• It coexists with classic ETL rather than replacing it. Most real warehouses in 2026 run zero-ETL for the handful of same-cloud operational sources it supports and keep classic pipelines for everything else — cross-cloud sources, on-prem systems, and any feed that needs heavy transformation before landing.

What interviewers listen for.

• Do you say "zero-ETL is managed CDC replication, and the transform moves in-warehouse (ELT)" rather than "it means no transformation"? — required answer.
• Do you name the schema-coupling trade-off (source DDL propagates downstream) without prompting? — senior signal.
• Do you distinguish the seed snapshot from ongoing CDC as two phases of one integration? — senior signal.
• Do you note that zero-ETL is same-cloud and source-list-constrained, not a universal ingestion tool? — required answer.
• Do you frame the choice as "mirror-then-model versus transform-then-load" rather than "new versus old"? — senior signal.

Worked example — mapping a classic ETL pipeline to its zero-ETL equivalent

Detailed explanation. The fastest way to internalise zero-ETL is to take a pipeline you already understand and delete the parts the managed integration absorbs. Take a textbook nightly orders pipeline — extract from Postgres, land in S3, copy into the warehouse, then model — and rewrite it as its zero-ETL equivalent. What is left after deletion is the zero-ETL mental model.

• Classic stages. Extract (SQL + watermark), stage (S3), load (COPY), model (dbt), schedule (Airflow), monitor (alerts on each stage).
• Zero-ETL stages. Create integration (once), model (dbt), monitor (system views).
• The residue. Modelling and monitoring survive; everything between extract and load is now the provider's job.

Question. For the nightly orders pipeline, list each classic stage and mark whether zero-ETL keeps it, deletes it, or moves it.

Input.

Classic stage	What you built	Under zero-ETL
Extract	SELECT ... WHERE updated_at > watermark + watermark store	deleted (native CDC)
Stage	S3 landing bucket + file layout	deleted (managed)
Load	COPY into raw tables	deleted (managed)
Transform / model	dbt star schema	kept (runs post-load)
Schedule	Airflow DAG	deleted (continuous)
Monitor	per-stage alerts	moved (watch integration views)

Code.

Classic ETL (nightly)                     Zero-ETL (continuous)
=====================                     =====================
[Postgres] --extract--> [S3] --load-->    [Aurora] ==managed CDC==> [Redshift raw]
[Redshift raw] --dbt--> [Redshift marts]  [Redshift raw] --dbt--> [Redshift marts]

Owned surface:                            Owned surface:
  - extract SQL + watermark                 - (none — provider handles it)
  - S3 bucket + partitioning                - (none)
  - COPY jobs                               - (none)
  - Airflow DAG + retries                   - (none)
  - dbt models                              - dbt models        <- survives
  - alerting on 5 stages                    - alerting on 1 integration

Step-by-step explanation.

1. The extract stage — the updated_at > watermark poll plus its durable watermark store — is the first thing zero-ETL deletes. The integration reads the source's native change stream, so there is no query for you to write and no high-watermark to advance.
2. The stage and load steps collapse into the managed integration. There is no S3 bucket to lay out and no COPY to author; rows flow from the change stream into the warehouse's raw tables automatically.
3. The schedule disappears because zero-ETL is continuous, not nightly. There is no DAG and no cron; freshness is measured in seconds of lag, not "runs at 2 AM."
4. The transform/model stage survives unchanged. Your dbt project still turns the raw orders/order_items/customers mirror into a star schema — it now just runs against fresher inputs.
5. The monitor stage moves rather than vanishes. Instead of alerting on five pipeline stages, you alert on the integration's health and lag (via the warehouse's integration system views). The surface shrinks but does not go to zero.

Output.

Stage	Classic owner	Zero-ETL owner	Net change
Extract	you	provider	deleted
Stage / load	you	provider	deleted
Schedule	you	provider (continuous)	deleted
Model	you	you	unchanged
Monitor	you (5 stages)	you (1 integration)	shrunk

Rule of thumb. Describe zero-ETL by subtraction: start from a pipeline you know, delete extract-stage-load-schedule, keep model-and-monitor. Whatever remains is exactly what zero-ETL still asks of you — and "model" remaining is why the T never actually went to zero.

Worked example — the vocabulary: seed, CDC, and where the transform runs

Detailed explanation. Interviewers reward precise vocabulary. A zero-ETL integration has exactly two data phases and one place the transform lives, and confusing them is the most common junior mistake. Name the seed snapshot, the ongoing CDC stream, and the in-warehouse transform as three distinct things, and the rest of the discussion falls into place.

• Seed (initial snapshot). A one-time consistent copy of every existing row at integration start. For a large source this is I/O-heavy and can take hours; it is the "how does the warehouse learn about the 500M rows already there?" answer.
• CDC (ongoing replication). After the seed, the integration tails the source's change stream and applies inserts, updates, and deletes as they commit. This is the "seconds fresh" phase.
• Transform (in-warehouse ELT). Views and materialized views (or dbt) that turn the raw mirror into modelled tables. This runs on the warehouse's compute, on your schedule, never blocking replication.

Question. Label each event in an integration's life as seed, CDC, or transform, and say which system owns it.

Input.

Event	Phase	Owner
Integration created; 500M existing rows copied	seed	provider
A new order commits on the source	CDC	provider
An order is updated, then deleted	CDC	provider
Nightly dim_customer rebuild	transform	you
A late column add (ALTER TABLE) on the source	CDC (DDL)	provider applies; you adapt models

Code.

-- The transform layer you still own: raw mirror -> modelled marts.
-- Runs inside the warehouse AFTER zero-ETL lands the rows.

-- Raw tables 'orders', 'customers' are the zero-ETL mirror (source schema).
CREATE MATERIALIZED VIEW analytics.fct_orders AS
SELECT
    o.id                              AS order_id,
    o.customer_id,
    o.total_cents / 100.0             AS total_usd,     -- type/units transform
    o.status,
    o.created_at::date                AS order_date,
    o.deleted_at IS NOT NULL          AS is_cancelled   -- soft-delete handling
FROM   raw.orders  o
WHERE  o.created_at >= DATE '2024-01-01';

CREATE MATERIALIZED VIEW analytics.dim_customer AS
SELECT
    c.id                              AS customer_id,
    INITCAP(c.name)                   AS customer_name,
    LOWER(c.email)                    AS email,
    c.region
FROM   raw.customers c
WHERE  c.deleted_at IS NULL;                            -- filter mirrored deletes

Step-by-step explanation.

1. The seed is a provider-run, one-time consistent snapshot. You do not schedule it and cannot skip it; it exists so the warehouse starts with the full history, not just changes-from-now.
2. The CDC phase applies every insert/update/delete from the source's change stream. Crucially, updates and deletes are applied in place on the raw mirror — the warehouse table looks like the source table at all times, including soft-deletes and hard-deletes.
3. DDL is part of CDC: a source ALTER TABLE ... ADD COLUMN propagates into the raw mirror automatically, but your fct_orders view will not use the new column until you edit the model. Replication is automatic; adoption is not.
4. The transform materialized views are the T that "zero-ETL" pretends is gone. total_cents / 100.0, INITCAP, LOWER, and the soft-delete filter are all real transformation — they simply run on warehouse compute after landing.
5. Because the transform is decoupled from replication, a slow or failed model rebuild never blocks the CDC stream. This separation is the whole point of ELT: land fast and unconditionally, model on your own cadence.

Output.

Object	Phase it belongs to	Refresh trigger
raw.orders (mirror)	seed then CDC	continuous, provider-driven
raw.customers (mirror)	seed then CDC	continuous, provider-driven
analytics.fct_orders	transform	your schedule (e.g. dbt)
analytics.dim_customer	transform	your schedule (e.g. dbt)

Rule of thumb. Always answer with three nouns: seed, CDC, transform. The provider owns seed and CDC; you own transform. If a candidate cannot place the transform "in the warehouse, after the load," they have not understood zero-ETL.

Worked example — the "is this really zero-ETL?" checklist

Detailed explanation. Vendors slap "zero-ETL" on anything with a managed connector, so senior engineers run a quick four-question checklist to decide whether a given feature is genuinely zero-ETL or just a rebranded batch connector. The distinction matters because true zero-ETL is CDC-based and near-real-time; a rebranded batch job is neither.

• Q1 — Is it CDC-based? Real zero-ETL tails a change stream. A scheduled full/incremental extract is batch, not zero-ETL.
• Q2 — Is it near-real-time? Seconds-to-minutes lag, not hourly/nightly.
• Q3 — Is the pipeline fully managed? No connector you configure, patch, or scale.
• Q4 — Does it land a source mirror (ELT)? Data arrives as the source schema; transform happens after.

Question. Classify three "integrations" against the checklist and label each as zero-ETL or batch-connector-in-disguise.

Input.

Feature	CDC?	Near-real-time?	Fully managed?	Lands mirror?
Aurora → Redshift integration	yes	yes (seconds)	yes	yes
Nightly Salesforce Bulk API → S3 job	no (batch)	no (nightly)	no (you own it)	raw files
Managed SaaS zero-ETL connector	yes (change API)	yes (minutes)	yes	yes

Code.

# A tiny classifier that encodes the four-question checklist.
def is_zero_etl(cdc_based: bool,
                near_real_time: bool,
                fully_managed: bool,
                lands_source_mirror: bool) -> str:
    if cdc_based and near_real_time and fully_managed and lands_source_mirror:
        return "zero-ETL"
    if fully_managed and not cdc_based:
        return "managed batch connector (not zero-ETL)"
    return "custom pipeline (not zero-ETL)"


print(is_zero_etl(True,  True,  True,  True))    # Aurora -> Redshift
# -> zero-ETL
print(is_zero_etl(False, False, False, False))   # nightly Bulk API job
# -> custom pipeline (not zero-ETL)
print(is_zero_etl(True,  True,  True,  True))    # managed SaaS connector
# -> zero-ETL

Step-by-step explanation.

1. The Aurora → Redshift integration passes all four questions: it tails the enhanced binlog / logical replication stream (CDC), lands rows in seconds (near-real-time), is fully managed (no connector to run), and mirrors the source schema (ELT). It is unambiguously zero-ETL.
2. The nightly Salesforce Bulk API job fails three of four: it is a scheduled batch extract, not CDC; it is nightly, not near-real-time; and you own and operate it. Calling it zero-ETL would be a category error.
3. The managed SaaS connector passes because it consumes the SaaS platform's change API, ships changes within minutes, is fully managed, and lands mirrored objects. Minutes (rather than seconds) is still near-real-time relative to nightly batch.
4. The classifier encodes a useful bright line: fully managed but not CDC is a batch connector; not managed is a custom pipeline; only the intersection of all four is zero-ETL. This is exactly the distinction interviewers want you to draw.
5. In practice, the label matters because it sets expectations: zero-ETL promises freshness and no-ops; a batch connector promises convenience but not low latency. Confusing the two leads to over-promising freshness to stakeholders.

Output.

Feature	Verdict
Aurora → Redshift integration	zero-ETL
Nightly Salesforce Bulk API → S3	custom pipeline (not zero-ETL)
Managed SaaS zero-ETL connector	zero-ETL

Rule of thumb. Run the four-question checklist before you accept a "zero-ETL" label: CDC-based, near-real-time, fully managed, lands a source mirror. If any answer is no, it is a batch connector wearing a zero-ETL badge, and you should set freshness expectations accordingly.

Data engineering interview question on defining zero-ETL

A senior interviewer often opens with: "Your VP read that zero-ETL means we can delete our whole transformation layer. Explain, precisely, what zero-ETL is, what work it removes, what work it does not remove, and where the transformation actually runs afterwards. Then sketch the resulting architecture for an orders source and name the one trade-off you would flag to the VP."

Solution Using a mirror-then-model (ELT) architecture with an explicit transform layer

-- Layer 0 (provider-owned): zero-ETL lands a raw mirror of the source.
--   raw.orders, raw.order_items, raw.customers appear automatically,
--   in the SOURCE schema, updated continuously by managed CDC.
--   You write NO extract/load code for this layer.

-- Layer 1 (you own): staging views normalise types and handle deletes.
CREATE VIEW stg.orders AS
SELECT
    id                         AS order_id,
    customer_id,
    total_cents / 100.0        AS total_usd,
    status,
    created_at,
    updated_at,
    (deleted_at IS NOT NULL)   AS is_deleted
FROM raw.orders;

-- Layer 2 (you own): modelled marts — the star schema for BI.
CREATE MATERIALIZED VIEW mart.fct_orders AS
SELECT o.order_id,
       o.customer_id,
       o.total_usd,
       o.status,
       o.created_at::date AS order_date
FROM   stg.orders o
WHERE  o.is_deleted = FALSE;      -- exclude mirrored soft-deletes from facts

CREATE MATERIALIZED VIEW mart.dim_customer AS
SELECT c.id AS customer_id, INITCAP(c.name) AS name, LOWER(c.email) AS email
FROM   raw.customers c
WHERE  c.deleted_at IS NULL;

The architecture you sketch on the whiteboard:

[Aurora orders/customers]  ==managed CDC (zero-ETL)==>  [Redshift raw.*]   (Layer 0)
                                                              |
                                                     you own from here down
                                                              v
                                              [stg.* views]  (Layer 1: types, deletes)
                                                              v
                                       [mart.fct_orders, mart.dim_customer] (Layer 2)
                                                              v
                                                    [BI / dashboards]

Step-by-step trace.

Step	What happens	Who owns it
1. Create integration	source ARN bound to warehouse target; seed begins	provider
2. Seed snapshot	existing orders/customers rows copied into raw.*	provider
3. Ongoing CDC	every insert/update/delete streams into raw.* in seconds	provider
4. Staging views	stg.orders coerces total_cents→total_usd, flags deletes	you
5. Marts	fct_orders/dim_customer build the star schema	you
6. Monitor	integration health + model freshness	you (shrunk surface)

After deployment, orders mutations appear in raw.orders within seconds of the source commit; your staging and mart layers turn that raw mirror into the star schema your dashboards read. You wrote zero extract/load code — but you wrote the entire transform layer, which is precisely the point you make to the VP.

Output:

Layer	Object	Owner	Freshness
0 raw mirror	raw.orders, raw.customers	provider	seconds (CDC)
1 staging	stg.orders	you	on model run
2 marts	mart.fct_orders, mart.dim_customer	you	on model run
— trade-off flagged	source DDL couples to models	you	continuous risk

Why this works — concept by concept:

• Managed CDC replication — the provider tails the source's native change stream and applies changes to the raw mirror. This is what "zero-ETL" actually is: the extract and load you used to own, productised.
• Mirror-then-model (ELT) — the raw layer is a faithful source mirror; all transformation runs afterward in staging and mart layers. The T did not vanish; it relocated to warehouse SQL you control.
• Soft-delete handling in staging — because CDC mirrors deletes, the fact layer must explicitly exclude is_deleted rows. Zero-ETL delivers the deletes; deciding what to do with them is still your job.
• Schema-coupling trade-off — a source ALTER TABLE flows into raw.* automatically but can break a mart if a column is renamed or dropped. This is the one risk you flag to the VP: fewer pipelines, but tighter coupling to the operational schema.
• Cost — replication is O(changes) on warehouse storage/compute plus the model rebuild cost; there is no per-night full-scan. Compared to a nightly full-refresh pipeline this is far fresher and cheaper to operate, but "no pipeline" is not "no bill" — you still pay for storage, CDC apply, and model compute.

ETL
Topic — etl
ETL and ELT modelling problems

Practice →

Database Topic — database Database and change-stream problems

Practice →

---

2. Aurora zero-ETL integration to Amazon Redshift

Aurora zero-ETL mirrors a relational operational database into Redshift with managed CDC — near-real-time, no pipelines

The mental model in one line: Aurora zero-etl is a fully managed integration that reads Aurora's change stream (enhanced binlog for Aurora MySQL, logical replication for Aurora PostgreSQL), seeds an initial snapshot, and then continuously applies inserts, updates, and deletes into an Amazon Redshift warehouse — you create the integration once, bind a Redshift database to it, and thereafter the operational tables appear in the warehouse within seconds of being written, with no extract job, no S3 staging, and no COPY to author. The same integration exists for RDS for MySQL and RDS for PostgreSQL; Aurora is the flagship because its enhanced binlog is purpose-built to make the change stream cheap to read.

The prerequisites — what must be true before the integration will start.

• Source change stream enabled. Aurora MySQL needs the enhanced binlog (aurora_enhanced_binlog cluster parameter) turned on; Aurora PostgreSQL needs logical replication (rds.logical_replication). Without a change stream there is nothing for CDC to tail.
• Redshift target on the right tier. The target must be an RA3 provisioned cluster or Redshift Serverless — the older node types are not supported — and the target namespace must have case-sensitivity enabled (enable_case_sensitive_identifier) so source identifiers map cleanly.
• Networking and IAM. The source and target must be reachable (same region is the common case) and an authorised-integration policy on the Redshift namespace must permit the source account to write.
• Filtering (optional). You can scope the integration to specific databases or tables so you only replicate what the warehouse needs, rather than the whole cluster.

The two-step setup — create the integration, then bind a database.

• Step A — create the integration. On the source side you create an Integration object that references the Aurora cluster ARN as source and the Redshift namespace ARN as target. This kicks off the seed.
• Step B — bind a Redshift database. Inside Redshift you run CREATE DATABASE ... FROM INTEGRATION '<integration_id>'. That statement is what makes the replicated tables queryable as a normal Redshift database.
• Step C — query. From that point the mirrored tables behave like any Redshift tables; you build views and marts on top of them.

Monitoring — the handful of views that replace your pipeline alerts.

• Integration state. SVV_INTEGRATION shows each integration and its overall state (e.g. active, needs attention).
• Per-table state. SVV_INTEGRATION_TABLE_STATE shows whether each table is synced, resyncing, or failed — the table-level equivalent of a per-partition load alert.
• Activity and lag. SYS_INTEGRATION_ACTIVITY (and the table-activity views) expose apply latency and row counts, which is where your "replication lag" alert reads from.
• Failure signals. A table that drops to a failed state (often after an unsupported DDL) shows up here; the fix is usually a resync of that table.

The delete and DDL story — why the mirror stays honest.

• Deletes. Because the integration is CDC-based, physical deletes on the source are applied to the Redshift mirror — unlike timestamp polling, zero-ETL is not blind to deletes.
• Updates. Applied in place; the Redshift row always reflects the latest source row.
• DDL. Column adds and compatible changes propagate; some DDL (certain type changes, unsupported operations) can put a table into a resync, during which that table is temporarily rebuilt from a fresh snapshot.
• The coupling cost. As in section 1, the mirror's fidelity is also its liability: your downstream models are coupled to the source schema and must adapt when it changes.

Common interview probes on Aurora zero-ETL.

• "How does Aurora zero-ETL differ from running Debezium yourself?" — same CDC idea, but fully managed; no connector, slot, or Kafka to operate.
• "What latency should I expect?" — seconds in steady state; the seed can take hours for a large cluster.
• "How do you monitor it?" — SVV_INTEGRATION, SVV_INTEGRATION_TABLE_STATE, SYS_INTEGRATION_ACTIVITY; alert on table-failed and on apply lag.
• "What breaks it?" — disabling the source change stream, an unsupported DDL forcing a resync, or the Redshift target lacking case-sensitivity / the right tier.

Worked example — creating the Aurora → Redshift integration

Detailed explanation. The canonical setup: turn on the enhanced binlog for the Aurora MySQL cluster, create the integration referencing source and target ARNs, then bind a Redshift database to it and query the mirror. Walk through each command and what it accomplishes.

• Enable. aurora_enhanced_binlog = ON via a cluster parameter group (a reboot applies it).
• Create integration. aws rds create-integration with --source-arn (Aurora) and --target-arn (Redshift namespace).
• Bind. CREATE DATABASE ... FROM INTEGRATION inside Redshift.
• Verify. Query SVV_INTEGRATION and then the mirrored table.

Question. Provide the source-side enablement, the integration-create call, and the Redshift bind, then show the first successful query against the mirror.

Input.

Component	Value
Source	Aurora MySQL cluster orders-prod
Change stream	enhanced binlog = ON
Target	Redshift Serverless namespace analytics
Integration name	orders-zeroetl
Bound database	orders_mirror

Code.

# 1. Enable the enhanced binlog on the Aurora MySQL cluster parameter group.
#    (Set in the cluster parameter group, then reboot the writer to apply.)
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name orders-prod-params \
  --parameters "ParameterName=aurora_enhanced_binlog,ParameterValue=1,ApplyMethod=pending-reboot"

# 2. Create the zero-ETL integration: Aurora source -> Redshift target.
aws rds create-integration \
  --integration-name orders-zeroetl \
  --source-arn        arn:aws:rds:us-east-1:111122223333:cluster:orders-prod \
  --target-arn        arn:aws:redshift-serverless:us-east-1:111122223333:namespace/analytics \
  --data-filter       'include: orders_db.public.orders, orders_db.public.customers'

-- 3. On the Redshift side, bind a database to the integration.
--    The integration_id comes from SVV_INTEGRATION or the create-integration output.
CREATE DATABASE orders_mirror
  FROM INTEGRATION '9f2c1e77-4a6b-4d2e-8c11-abc123def456';

-- 4. Verify the integration is active and the tables are syncing.
SELECT integration_id, target_database, state
FROM   SVV_INTEGRATION;

SELECT table_name, table_state, rows_replicated
FROM   SVV_INTEGRATION_TABLE_STATE
ORDER  BY table_name;

-- 5. Query the mirror like any Redshift table.
SELECT status, COUNT(*) AS n
FROM   orders_mirror.public.orders
GROUP  BY status
ORDER  BY n DESC;

Step-by-step explanation.

1. Step 1 enables the enhanced binlog, the change stream the integration will tail. It is set on the cluster parameter group and applied on reboot; without it, create-integration has no source to read and the integration will not activate.
2. Step 2 creates the integration object linking the Aurora cluster ARN (source) to the Redshift namespace ARN (target). The optional --data-filter scopes replication to two tables, so you mirror only what the warehouse needs rather than the whole database.
3. Step 3's CREATE DATABASE ... FROM INTEGRATION is the Redshift-side half. Until you run it, the integration is replicating into the namespace but you have no database handle to query; this statement exposes the mirror as orders_mirror.
4. Step 4 checks health. SVV_INTEGRATION should show state = active; SVV_INTEGRATION_TABLE_STATE shows each table moving from a seeding/resyncing state to synced with a growing rows_replicated. These views are your new "did the load succeed?" check.
5. Step 5 queries the mirror exactly like a native table. There is no COPY, no staging bucket — the rows are simply present and continuously updated. From here you build staging views and marts on top, as in section 1.

Output.

View / query	Result
SVV_INTEGRATION.state	active
SVV_INTEGRATION_TABLE_STATE (orders)	synced, rows growing
SVV_INTEGRATION_TABLE_STATE (customers)	synced, rows growing
orders_mirror.public.orders GROUP BY status	live counts, seconds fresh

Rule of thumb. The Aurora zero-ETL setup is three moves: turn on the change stream, create-integration with the two ARNs, and CREATE DATABASE ... FROM INTEGRATION in Redshift. If the integration will not activate, the culprit is almost always a disabled binlog, an unsupported Redshift tier, or missing case-sensitivity on the target.

Worked example — monitoring replication lag and reacting to a failed table

Detailed explanation. With no pipeline to watch, your entire operational signal comes from the integration views. The two things that go wrong are lag (the mirror falls behind the source) and a failed table (usually after an unsupported DDL). Build the monitoring query and the runbook.

• Lag. Read apply latency from the integration activity views; alert when it exceeds a threshold (e.g. 5 minutes) for a sustained window.
• Failed table. SVV_INTEGRATION_TABLE_STATE shows a table in a failed/error state; the fix is a targeted resync of that table.
• Runbook. Check source change stream is still on → check target health → resync the specific table → escalate if the whole integration is unhealthy.

Question. Write the lag/health monitoring query and describe the on-call response when a single table fails.

Input.

Signal	Source view	Alert condition
Apply lag	SYS_INTEGRATION_ACTIVITY	lag > 5 min for 10 min
Table failed	SVV_INTEGRATION_TABLE_STATE	any table_state = 'failed'
Integration down	SVV_INTEGRATION	state <> 'active'

Code.

-- 1. Overall integration health.
SELECT integration_id, target_database, state
FROM   SVV_INTEGRATION
WHERE  state <> 'active';               -- rows here => page on-call

-- 2. Per-table health — surface any non-synced table.
SELECT table_name, table_state, rows_replicated, last_updated
FROM   SVV_INTEGRATION_TABLE_STATE
WHERE  table_state <> 'synced'
ORDER  BY last_updated;

-- 3. Apply lag — the "replication is behind" signal.
SELECT integration_id,
       MAX(DATEDIFF('second', last_commit_timestamp, GETDATE())) AS lag_seconds
FROM   SYS_INTEGRATION_ACTIVITY
GROUP  BY integration_id
HAVING MAX(DATEDIFF('second', last_commit_timestamp, GETDATE())) > 300;

# 4. A thin scheduled check that turns those queries into alerts.
import boto3, redshift_connector

def check_integration_health(conn) -> list[str]:
    alerts = []
    with conn.cursor() as cur:
        cur.execute("SELECT integration_id, state FROM SVV_INTEGRATION WHERE state <> 'active'")
        for integration_id, state in cur.fetchall():
            alerts.append(f"integration {integration_id} state={state}")

        cur.execute("""
            SELECT table_name, table_state
            FROM   SVV_INTEGRATION_TABLE_STATE
            WHERE  table_state = 'failed'
        """)
        for table_name, table_state in cur.fetchall():
            alerts.append(f"table {table_name} FAILED -> resync required")
    return alerts

# If alerts is non-empty, publish to SNS / PagerDuty.

Step-by-step explanation.

1. Query 1 is the top-level heartbeat. If SVV_INTEGRATION.state is anything other than active, the entire mirror is at risk and the on-call engineer investigates immediately — this is the equivalent of "the pipeline is down."
2. Query 2 catches the more common, narrower failure: a single table out of sync while the rest keep flowing. A table stuck in resyncing for a long time, or in failed, is the signal to act on that table specifically.
3. Query 3 computes apply lag from the activity view. A few seconds is normal; sustained lag above five minutes means the warehouse is meaningfully behind the source, which matters for freshness-sensitive dashboards.
4. The Python check runs on a schedule (e.g. every minute) and converts the SQL into alert strings, publishing to your paging system. This tiny job is the entire operational footprint of a zero-ETL integration — a fraction of the alerting a hand-built pipeline needs.
5. The runbook for a failed table: confirm the source change stream is still enabled, check whether an unsupported DDL triggered it, then resync the specific table (re-seed just that table) rather than tearing down the whole integration. Whole-integration failures escalate to the provider's support surface.

Output.

Condition	Detected by	Response
Integration active, tables synced	all three queries empty	none
One table failed	query 2	resync that table
Lag > 5 min sustained	query 3	investigate source load / target health
Integration not active	query 1	page on-call; check binlog + IAM

Rule of thumb. Replace your five-stage pipeline alerting with three queries: integration state, per-table state, and apply lag. Alert on state <> active, any failed table, and sustained lag — that trio is the whole on-call surface of Aurora zero-ETL.

Worked example — schema evolution and the resync trade-off

Detailed explanation. The mirror's fidelity means source DDL is not free. A compatible column add flows through transparently; an incompatible change can drop a table into a resync, and — critically — your downstream models must be updated to adopt (or ignore) the new shape. Walk through both cases.

• Compatible add. ALTER TABLE orders ADD COLUMN promo_code TEXT appears in the mirror; existing models keep working; you edit a model only when you want to use promo_code.
• Incompatible change. A type change or drop can force a table resync; during the resync that table is rebuilt and may be briefly unavailable or stale.
• Model adaptation. Because staging/mart layers reference columns explicitly, a rename upstream breaks them until you edit the SQL.

Question. Show how a compatible add and an incompatible change each flow through the mirror, and how the model layer must respond.

Input.

Source change	Mirror effect	Model effect
ADD COLUMN promo_code	column appears, back-filled NULL	no break; adopt when ready
RENAME COLUMN total_cents -> amount_cents	mirror follows rename	staging view breaks until edited
Incompatible type change	table enters resync	reads may be stale during resync

Code.

-- Source-side DDL (Aurora): a safe, additive change.
ALTER TABLE public.orders ADD COLUMN promo_code TEXT;   -- compatible

-- The column appears in raw.orders automatically. Existing staging view keeps working:
--   stg.orders never referenced promo_code, so nothing breaks.

-- To ADOPT the new column, edit the staging view on YOUR schedule:
CREATE OR REPLACE VIEW stg.orders AS
SELECT
    id                        AS order_id,
    customer_id,
    total_cents / 100.0       AS total_usd,
    promo_code,                                  -- newly adopted
    status,
    (deleted_at IS NOT NULL)  AS is_deleted
FROM raw.orders;

-- Source-side DDL: a breaking rename.
ALTER TABLE public.orders RENAME COLUMN total_cents TO amount_cents;

-- The mirror follows the rename; raw.orders now has amount_cents, not total_cents.
-- The OLD staging view referencing total_cents now ERRORS. Fix by editing it:
CREATE OR REPLACE VIEW stg.orders AS
SELECT
    id                        AS order_id,
    customer_id,
    amount_cents / 100.0      AS total_usd,      -- follow the source rename
    status,
    (deleted_at IS NOT NULL)  AS is_deleted
FROM raw.orders;

Step-by-step explanation.

1. The additive ADD COLUMN promo_code is the easy case: CDC propagates the column into raw.orders, back-filling NULL for existing rows. Because your staging view never mentioned promo_code, nothing downstream breaks — replication and adoption are decoupled.
2. Adoption is a deliberate, model-layer act. You edit stg.orders to select promo_code when you are ready to use it, on your own schedule. Zero-ETL delivered the column; you decide when it enters your models.
3. The RENAME COLUMN is the dangerous case. The mirror faithfully follows the rename, so raw.orders loses total_cents and gains amount_cents. Any model still referencing total_cents now errors at query time — this is the schema-coupling liability made concrete.
4. The fix is a coordinated model edit: update the staging view to reference amount_cents. This is exactly the coupling cost you flag in interviews — fewer pipelines, but the operational schema and the warehouse models are now joined at the hip.
5. An incompatible type change can trigger a table resync, during which that table is rebuilt from a fresh snapshot. Reads may be stale until the resync completes, so treat upstream type changes as coordinated events, not silent deploys.

Output.

Change	Automatic?	Breaks models?	Action
Add column	yes	no	adopt when ready
Rename column	mirror follows	yes	edit staging/mart SQL
Type change (incompatible)	triggers resync	possibly (stale)	coordinate; wait for resync

Rule of thumb. Additive source changes are safe and adopt-on-your-schedule; renames and type changes are coordinated events because the mirror couples your warehouse models to the operational schema. Put a review gate on source DDL for any table under zero-ETL.

Data engineering interview question on Aurora zero-ETL

A senior interviewer might ask: "You inherit a nightly job that mysqldumps an Aurora MySQL orders cluster and COPYs it into Redshift, giving 24-hour-stale analytics. The business now needs sub-minute freshness and correct delete handling. Walk me through migrating to Aurora zero-ETL — the prerequisites, the integration setup, how you cut over without losing history, the monitoring you add, and the one architectural risk you accept."

Solution Using an Aurora zero-ETL integration with a monitored cutover

# 1. Prerequisites: enable the change stream and confirm the Redshift target tier.
aws rds modify-db-cluster-parameter-group \
  --db-cluster-parameter-group-name orders-prod-params \
  --parameters "ParameterName=aurora_enhanced_binlog,ParameterValue=1,ApplyMethod=pending-reboot"
# Redshift target: RA3 or Serverless with enable_case_sensitive_identifier = true.

# 2. Create the integration; the seed snapshot carries the FULL history,
#    so no separate backfill is needed.
aws rds create-integration \
  --integration-name orders-zeroetl \
  --source-arn arn:aws:rds:us-east-1:111122223333:cluster:orders-prod \
  --target-arn arn:aws:redshift-serverless:us-east-1:111122223333:namespace/analytics

-- 3. Bind the mirror database and let the seed complete.
CREATE DATABASE orders_mirror FROM INTEGRATION '9f2c1e77-...';

-- 4. Cutover: build marts on the mirror, dual-run against the old nightly copy,
--    then repoint dashboards once row counts and totals reconcile.
CREATE MATERIALIZED VIEW mart.fct_orders AS
SELECT id AS order_id, customer_id, total_cents/100.0 AS total_usd,
       status, created_at::date AS order_date
FROM   orders_mirror.public.orders
WHERE  deleted_at IS NULL;               -- deletes handled natively by CDC

-- 5. Reconciliation query run during dual-run.
SELECT 'mirror' AS src, COUNT(*), SUM(total_cents) FROM orders_mirror.public.orders
UNION ALL
SELECT 'nightly', COUNT(*), SUM(total_cents) FROM legacy.orders_nightly;

-- 6. Monitoring that replaces the old job's alerts.
SELECT table_name, table_state FROM SVV_INTEGRATION_TABLE_STATE WHERE table_state <> 'synced';
SELECT integration_id FROM SVV_INTEGRATION WHERE state <> 'active';

Step-by-step trace.

Step	Before (nightly dump)	After (zero-ETL)
Freshness	~24 h	seconds
Delete handling	lost (dump reload masks history)	native via CDC
History backfill	separate reload	seed snapshot carries it
Extract/load code	mysqldump + COPY jobs	none
Cutover	N/A	dual-run + reconcile, then repoint
Monitoring	job success/fail	integration + table state + lag

After the migration, orders mutations reach orders_mirror within seconds; the seed snapshot means no separate history backfill; the reconciliation query proves the mirror matches the legacy nightly copy before you repoint dashboards; and the nightly mysqldump/COPY job is deleted from the scheduler.

Output:

Metric	Before	After
Warehouse freshness	24 h	< 1 min
Delete correctness	none	native
Pipeline code owned	mysqldump + COPY + DAG	none
On-call surface	job alerts	3 integration queries
Accepted risk	none new	source DDL couples to marts

Why this works — concept by concept:

• Enhanced binlog as the change stream — the enhanced binlog is what makes Aurora's changes cheap to tail; enabling it is the non-negotiable prerequisite, and its absence is the most common reason an integration will not start.
• Seed snapshot carries history — the integration's initial snapshot copies all existing rows, so the migration needs no separate backfill; the warehouse starts complete, then stays fresh via CDC.
• Native delete handling — because zero-ETL is CDC, physical deletes propagate to the mirror. The nightly dump masked history by reloading; the mirror preserves the true current state, which the fact layer filters explicitly.
• Dual-run reconciliation — running the mirror and the legacy copy side by side and comparing counts and sums is the safe cutover: you repoint dashboards only after the numbers match, so the switch is invisible to consumers.
• Cost — O(changes) apply cost plus mart-rebuild compute, versus O(rows) every night for the dump-and-reload. Freshness improves from a day to seconds while the owned code drops to zero; the price is the accepted schema-coupling risk and continuous replication compute rather than one nightly batch.

Database
Topic — database
Database replication and CDC problems

Practice →

ETL Topic — etl ETL cutover and reconciliation problems

Practice →

---

3. DynamoDB to the warehouse — zero-ETL and S3 export

DynamoDB reaches a warehouse two managed ways — a zero-ETL integration for freshness and a point-in-time S3 export for backfills

The mental model in one line: DynamoDB has two managed, no-scan paths into analytics — a zero-etl integration that continuously replicates items into Redshift (or OpenSearch) via the table's change stream, and a point-in-time export that dumps a consistent snapshot of the table into S3 as DynamoDB-JSON or Amazon Ion without consuming any read capacity — and the senior move is knowing which to reach for: the integration for near-real-time freshness, the export for full backfills, reprocessing, and cheap historical loads. Both paths exist precisely because the classic alternative — a full-table Scan — burns read-capacity units, throttles production traffic, and misses deletes.

Path A — the zero-ETL integration (freshness).

• What it is. A managed integration that reads DynamoDB's change data and continuously lands items in Redshift, so analytics stay within minutes of the operational table.
• No scans. The integration uses the change stream, not a Scan, so it does not compete with production read capacity.
• Deletes included. Being CDC-based, it applies deletes to the mirror — the same delete-fidelity advantage as Aurora zero-ETL.
• The catch. DynamoDB items are schemaless and often single-table-design; the landed data still needs flattening into relational columns downstream.

Path B — the point-in-time export to S3 (backfill and reprocessing).

• What it is. ExportTableToPointInTime writes a consistent snapshot of the table (at a chosen second within the PITR window) to an S3 prefix, as DynamoDB-JSON or Ion, gzip-compressed.
• No capacity consumed. The export runs off the continuous backups, so it costs zero read-capacity units and does not throttle the table — this is the headline operational win over a Scan.
• Full and incremental. A full export snapshots the whole table; an incremental export emits only the items that changed between two timestamps, which is how you keep an S3-based mirror fresh without re-exporting everything.
• Then load. Downstream, you COPY the S3 files into Redshift, query them with Athena, or crawl them with Glue.

The prerequisite and format details.

• PITR must be on. Point-in-time recovery (continuous backups) is required for any export; the export time must fall inside the PITR window.
• DynamoDB-JSON vs Ion. DynamoDB-JSON preserves the typed attribute envelope ({"S": "..."}, {"N": "..."}); Ion is a superset of JSON with richer types. Pick the one your loader parses most easily.
• Manifest files. Each export writes a manifest describing the data files; loaders read the manifest to find the parts.
• Incremental windows. Incremental exports take a from and to time; chaining windows (each new window starting where the last ended) gives you a continuously updated S3 copy.

Flattening single-table design — the transform that survives.

• The problem. DynamoDB single-table design packs multiple entity types (customer, order, order-item) into one table, distinguished by key prefixes and a type attribute. A relational warehouse wants one table per entity.
• The transform. Post-load, split by entity type and project the sparse attributes into typed columns — classic ELT that runs after the export/integration lands the raw items.
• The reminder. This is, once again, the T that zero-ETL did not remove; DynamoDB just makes it more visible because the source has no schema to mirror.

Common interview probes on DynamoDB analytics.

• "How do you get DynamoDB into a warehouse without scanning it?" — zero-ETL integration or point-in-time S3 export; never a full Scan in production.
• "Full export vs incremental export?" — full for the first load / reprocessing; incremental windows to stay fresh cheaply.
• "What does export cost the table?" — nothing in read capacity; it runs off continuous backups (PITR must be on).
• "How do you model single-table-design items downstream?" — split by entity type and flatten attributes into columns in the warehouse.

Worked example — point-in-time export to S3, then Redshift COPY

Detailed explanation. The canonical backfill path: enable PITR, run a full ExportTableToPointInTime to an S3 prefix as DynamoDB-JSON, then COPY the exported files into a Redshift staging table and flatten. Walk through each step.

• Enable PITR. Continuous backups on the orders table.
• Export. aws dynamodb export-table-to-point-in-time to s3://analytics-exports/orders/ as DYNAMODB_JSON.
• Load. COPY the gzipped JSON into a Redshift staging table.
• Flatten. Project the typed attributes into columns.

Question. Provide the export command and the Redshift load, and show the flattened output for a few items.

Input.

Parameter	Value
Table	orders (single-table design)
PITR	enabled
Export format	DynamoDB-JSON, gzip
S3 target	s3://analytics-exports/orders/
Load target	Redshift stg.orders_raw

Code.

# 1. Enable point-in-time recovery (required for any export).
aws dynamodb update-continuous-backups \
  --table-name orders \
  --point-in-time-recovery-specification PointInTimeRecoveryEnabled=true

# 2. Full export to S3 at "now" (must be within the PITR window).
aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:111122223333:table/orders \
  --s3-bucket analytics-exports \
  --s3-prefix orders/ \
  --export-format DYNAMODB_JSON \
  --export-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)"

-- 3. Stage the raw exported items in Redshift as SUPER (semi-structured).
CREATE TABLE stg.orders_raw (item SUPER);

COPY stg.orders_raw
FROM 's3://analytics-exports/orders/AWSDynamoDB/'
IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-copy'
FORMAT AS JSON 'auto'
GZIP;

-- 4. Flatten the DynamoDB-JSON typed envelope into columns.
--    DynamoDB-JSON encodes values as {"S": "..."} / {"N": "..."} etc.
CREATE TABLE stg.orders_flat AS
SELECT
    item.Item.order_id.S::varchar        AS order_id,
    item.Item.customer_id.S::varchar     AS customer_id,
    item.Item.total_cents.N::bigint       AS total_cents,
    item.Item.status.S::varchar          AS status,
    item.Item."type".S::varchar          AS entity_type
FROM stg.orders_raw
WHERE item.Item."type".S = 'ORDER';      -- single-table-design filter

Step-by-step explanation.

1. Step 1 turns on PITR, the hard prerequisite. The export reads from continuous backups, so without PITR there is nothing to export from; this also means the export consumes zero read-capacity units on the live table.
2. Step 2 runs a full export at the current instant. The export writes gzipped DynamoDB-JSON data files plus a manifest under the S3 prefix. Because it snapshots a point in time, the output is internally consistent — no torn reads from a moving table.
3. Step 3 stages the raw items into a Redshift SUPER column, which natively holds the semi-structured DynamoDB-JSON. COPY ... FORMAT AS JSON 'auto' GZIP ingests the parts described by the manifest.
4. Step 4 is the flatten transform. DynamoDB-JSON wraps every value in a typed envelope (.S for string, .N for number), so the projection unwraps each attribute into a typed column and filters to type = 'ORDER' to peel one entity out of the single-table design.
5. The result, stg.orders_flat, is a clean relational table your marts can build on. The export path is ideal for the first big backfill or for reprocessing history; you would pair it with incremental exports (next example) to stay fresh cheaply, or switch to the zero-ETL integration for continuous freshness.

Output.

order_id	customer_id	total_cents	status	entity_type
o-1001	c-7	1500	shipped	ORDER
o-1002	c-9	4200	pending	ORDER
o-1003	c-7	999	cancelled	ORDER

Rule of thumb. For DynamoDB backfills, never Scan in production — enable PITR and use ExportTableToPointInTime to S3, load into a SUPER staging column, then flatten the typed DynamoDB-JSON envelope into columns. The export costs zero read capacity because it runs off continuous backups.

Worked example — incremental exports to keep the S3 mirror fresh

Detailed explanation. A full export every hour is wasteful. Incremental exports emit only items that changed between two timestamps, so you chain windows — each new window starting where the last ended — to maintain a fresh S3 copy at a fraction of the cost. Build the chaining loop.

• Windows. Each incremental export takes --incremental-export-specification with an ExportFromTime and ExportToTime.
• Chaining. Persist the last ExportToTime; the next window runs from there to now.
• Merge. Downstream, upsert the changed items into the flattened table by primary key.

Question. Implement the incremental-export chaining and the downstream merge into stg.orders_flat.

Input.

Parameter	Value
Export type	incremental
Window	last_to_time → now
Cadence	every 15 minutes
Merge key	order_id

Code.

# 1. Chain incremental exports: each window starts where the last ended.
import boto3, json
from datetime import datetime, timezone

ddb = boto3.client("dynamodb")
STATE_KEY = "s3://analytics-exports/_state/orders_last_to.json"

def run_incremental_export(last_to: datetime) -> datetime:
    now = datetime.now(timezone.utc)
    ddb.export_table_to_point_in_time(
        TableArn="arn:aws:dynamodb:us-east-1:111122223333:table/orders",
        S3Bucket="analytics-exports",
        S3Prefix=f"orders-incr/{now:%Y/%m/%d/%H%M}/",
        ExportFormat="DYNAMODB_JSON",
        ExportType="INCREMENTAL_EXPORT",
        IncrementalExportSpecification={
            "ExportFromTime": last_to,
            "ExportToTime": now,
            "ExportViewType": "NEW_AND_OLD_IMAGES",   # capture updates + deletes
        },
    )
    return now   # persist as the next window's ExportFromTime

-- 2. Load the incremental window into a delta table, then MERGE by PK.
COPY stg.orders_incr (item)
FROM 's3://analytics-exports/orders-incr/2026/09/05/0900/'
IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-copy'
FORMAT AS JSON 'auto' GZIP;

MERGE INTO stg.orders_flat AS tgt
USING (
    SELECT item.Keys.order_id.S::varchar          AS order_id,
           item.NewImage.customer_id.S::varchar   AS customer_id,
           item.NewImage.total_cents.N::bigint     AS total_cents,
           item.NewImage.status.S::varchar         AS status,
           (item.NewImage IS NULL)                 AS is_deleted
    FROM stg.orders_incr
) src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.is_deleted THEN DELETE
WHEN MATCHED     THEN UPDATE SET customer_id = src.customer_id,
                                 total_cents = src.total_cents,
                                 status      = src.status
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, total_cents, status)
                     VALUES (src.order_id, src.customer_id, src.total_cents, src.status);

Step-by-step explanation.

1. Step 1 runs an incremental export whose window is [last_to, now]. Setting ExportViewType = NEW_AND_OLD_IMAGES captures both the new and old image of each changed item, which is what lets the downstream merge distinguish an update from a delete.
2. Chaining is the key idea: after each run you persist now as the next ExportFromTime, so consecutive windows tile the timeline with no gaps and no overlap. This is the DynamoDB analogue of advancing a watermark — but the export, not you, reads the change data.
3. Step 2 loads just the changed items for that window into a small stg.orders_incr delta table. Because the window is 15 minutes, this is a tiny load compared to a full export.
4. The MERGE applies the delta by primary key: a captured delete (no NewImage) removes the row, an update overwrites the columns, and a new key inserts. This keeps stg.orders_flat a faithful, delete-correct mirror without ever scanning the source.
5. The chained incremental exports give you an S3-and-warehouse mirror that stays fresh at roughly O(changes) cost per window — cheaper than repeated full exports and, unlike a Scan, invisible to production read capacity. When you need seconds rather than minutes, that is the signal to switch to the DynamoDB zero-ETL integration instead.

Output.

Window	Items exported	Merge effect
08:45 → 09:00	120 changed	90 upserts, 30 deletes
09:00 → 09:15	64 changed	64 upserts, 0 deletes
09:15 → 09:30	0 changed	no-op

Rule of thumb. Chain incremental exports with NEW_AND_OLD_IMAGES and merge by primary key to keep a delete-correct S3/warehouse mirror at O(changes) cost. Persist each window's end time as the next window's start so the timeline tiles without gaps — and reach for the zero-ETL integration when minutes are not fresh enough.

Data engineering interview question on DynamoDB analytics

A senior interviewer might ask: "A product team runs a high-traffic DynamoDB orders table with single-table design. Analysts keep running full Scans that throttle the production table. Design a managed path to keep a delete-correct, query-friendly copy in Redshift without ever scanning the table, explain when you'd use the zero-ETL integration versus the S3 export, and show how you'd flatten the single-table items."

Solution Using PITR exports for backfill plus a zero-ETL integration for freshness

# 1. Backfill: enable PITR and take one full point-in-time export for history.
aws dynamodb update-continuous-backups --table-name orders \
  --point-in-time-recovery-specification PointInTimeRecoveryEnabled=true

aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:111122223333:table/orders \
  --s3-bucket analytics-exports --s3-prefix orders/full/ \
  --export-format DYNAMODB_JSON --export-time "$(date -u +%Y-%m-%dT%H:%M:%SZ)"

-- 2. Load + flatten the backfill (single-table design -> per-entity tables).
CREATE TABLE stg.orders_raw (item SUPER);
COPY stg.orders_raw FROM 's3://analytics-exports/orders/full/AWSDynamoDB/'
  IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-copy' FORMAT AS JSON 'auto' GZIP;

CREATE TABLE mart.orders AS
SELECT item.Item.order_id.S::varchar    AS order_id,
       item.Item.customer_id.S::varchar AS customer_id,
       item.Item.total_cents.N::bigint  AS total_cents,
       item.Item.status.S::varchar      AS status
FROM   stg.orders_raw
WHERE  item.Item."type".S = 'ORDER';    -- split ORDER entity out of the table

-- 3. Freshness: a DynamoDB zero-ETL integration continuously lands changes,
--    so after backfill you STOP re-exporting and let CDC keep mart.orders fresh.
--    (Deletes propagate natively; analysts never Scan the source again.)

Step-by-step trace.

Step	Mechanism	Cost to source table
1. Enable PITR	continuous backups	none
2. Full export	point-in-time snapshot to S3	zero read capacity
3. Load + flatten	COPY into SUPER, split by type	none (warehouse side)
4. Zero-ETL integration	managed CDC into Redshift	none (change stream)
5. Analysts query	mart.orders in Redshift	none (never Scan)

After deployment, the one-time export backfills full history into mart.orders, the zero-ETL integration keeps it fresh via the change stream, deletes propagate correctly, and the production DynamoDB table never sees another analytics Scan. The export path owns the backfill; the integration owns ongoing freshness.

Output:

Concern	Old (Scan)	New (export + zero-ETL)
Source impact	throttles prod	zero
History	re-scan each time	one export
Freshness	on-demand scan	minutes (CDC)
Deletes	missed	native
Single-table modelling	ad hoc	flattened per entity

Why this works — concept by concept:

• Point-in-time export off continuous backups — the export reads PITR backups, not the live table, so it consumes zero read capacity and cannot throttle production. This is the core reason to never Scan for analytics.
• Full export for backfill, integration for freshness — the two managed paths are complementary: one export seeds full history cheaply, and the zero-ETL integration's CDC keeps it current without repeated exports.
• Flattening single-table design — the transform that survives: splitting entities by type and unwrapping the typed DynamoDB-JSON envelope turns schemaless items into relational tables the marts can use.
• Native delete propagation — because the integration is CDC-based, deletes reach the mirror; the old Scan approach silently missed them, shipping a correctness bug into every dashboard.
• Cost — O(rows) once for the backfill export (zero source capacity) plus O(changes) for ongoing CDC, versus O(rows) per analyst Scan charged against production throughput. The managed paths move the cost off the operational table entirely and onto the warehouse and backup subsystems.

Data processing
Topic — data-processing
Semi-structured flattening and load problems

Practice →

Database Topic — database Key-value store and export problems

Practice →

---

4. SaaS sources — Salesforce and the managed-connector path

SaaS integration zero-ETL replaces hand-built API pipelines with a managed connector that mirrors objects into the warehouse

The mental model in one line: zero-ETL for SaaS sources like Salesforce is a managed connector that consumes the application's change API, handles the API-limit, pagination, incremental-watermark, and soft-delete plumbing you used to hand-code, and lands each SaaS object as a mirrored table in the warehouse (Redshift or a lakehouse) — turning a brittle Bulk-API-plus-cron job you patched every quarter into a fully managed replication you configure once. On AWS this is delivered through Glue zero-ETL integrations for applications (Salesforce, SAP, ServiceNow, Zendesk, and a growing list) and through Amazon AppFlow for managed flows; the through-line is that the connector, not you, owns the ugly parts of talking to a SaaS API.

What the hand-built SaaS pipeline used to own — and the connector now absorbs.

• API limits and backoff. Salesforce enforces daily API call caps and rate limits; a hand-built job needs careful batching and exponential backoff. The managed connector handles throttling internally.
• Pagination and Bulk API. Large objects require the Bulk API with job/batch lifecycle management. The connector orchestrates that for you.
• Incremental watermarking. Pulling only records changed since the last run (via SystemModstamp) is watermark logic you no longer write.
• Soft deletes. Salesforce marks deleted rows with IsDeleted (and a recycle bin); the connector surfaces these so your mirror can stay delete-correct.

What still lands on your plate — the T, again.

• Object selection and field mapping. You choose which objects (Account, Opportunity, Contact) and which fields to replicate; over-selecting bloats the mirror.
• Formula and rollup fields. Salesforce formula fields are computed server-side; whether they replicate as static snapshots or need recomputation downstream is a modelling decision you own.
• Relationship flattening. SaaS objects are graph-shaped (lookups, master-detail); turning them into star-schema facts and dimensions is in-warehouse ELT.
• PII governance. Deciding which fields to mask or drop before they reach analysts is your policy, applied in the transform layer.

The setup shape — connection, integration, target.

• Connection. A stored, OAuth-based connection to the Salesforce org (the connector manages token refresh).
• Integration. A zero-ETL integration binding the SaaS connection (source) to the warehouse/lakehouse target, with the object and field selection.
• Target tables. Each selected object lands as a table (e.g. salesforce.account, salesforce.opportunity), updated incrementally.
• Monitoring. Integration-level health and per-object sync state, analogous to the database integration views.

Why "minutes" is still zero-ETL for SaaS.

• SaaS change APIs are coarser than a binlog. A CRM exposes changes through change-data-capture events or modstamp polling, not a byte-level log, so latency is typically minutes rather than seconds.
• That is still near-real-time. Compared to the nightly Bulk API job it replaces, a minutes-fresh mirror is a step-change — and it passes the section-1 checklist (CDC-based, near-real-time, managed, mirror).
• The win is operational. Even where latency is similar to a well-tuned batch job, deleting the API-limit, pagination, and token-refresh code you maintained is the real payoff.

Common interview probes on SaaS zero-ETL.

• "Why not just call the Salesforce API yourself?" — API limits, Bulk API lifecycle, watermarking, token refresh, and soft-delete handling are exactly what the managed connector removes.
• "How do deletes work?" — the connector surfaces IsDeleted; you filter or tombstone in the transform layer.
• "What latency?" — minutes, via the SaaS change API; still near-real-time versus nightly batch.
• "What do you still own?" — object/field selection, formula-field decisions, relationship flattening, and PII governance.

Worked example — a Salesforce → Redshift zero-ETL integration

Detailed explanation. The canonical SaaS setup: create an OAuth connection to the Salesforce org, create a zero-ETL integration selecting Account and Opportunity, and let the connector land and incrementally maintain those objects as Redshift tables. Then build a staging view that handles IsDeleted.

• Connection. OAuth to the org; the connector manages refresh.
• Integration. Select Account, Opportunity; map to salesforce.account, salesforce.opportunity.
• Incremental. The connector watermarks on SystemModstamp.
• Deletes. IsDeleted = true rows are surfaced; staging filters them.

Question. Configure the integration and write the staging view that yields a delete-correct Opportunity mirror.

Input.

Component	Value
Source	Salesforce org (OAuth connection)
Objects	Account, Opportunity
Target	Redshift salesforce schema
Incremental field	SystemModstamp
Delete flag	IsDeleted

Code.

// 1. Glue zero-ETL integration (conceptual config): Salesforce -> Redshift.
{
  "IntegrationName": "salesforce-zeroetl",
  "SourceConnection": "salesforce-oauth-conn",
  "Target": {
    "Warehouse": "redshift-serverless:analytics",
    "Schema": "salesforce"
  },
  "Objects": [
    { "name": "Account",     "fields": ["Id", "Name", "Industry", "SystemModstamp", "IsDeleted"] },
    { "name": "Opportunity", "fields": ["Id", "AccountId", "Amount", "StageName", "CloseDate", "SystemModstamp", "IsDeleted"] }
  ],
  "IncrementalField": "SystemModstamp",
  "Cadence": "PT15M"
}

-- 2. The connector lands salesforce.opportunity (mirror, incl. IsDeleted rows).
--    Staging view yields a delete-correct, typed model.
CREATE OR REPLACE VIEW stg.opportunity AS
SELECT
    id                          AS opportunity_id,
    accountid                   AS account_id,
    amount::numeric(14,2)       AS amount_usd,
    stagename                   AS stage,
    closedate::date             AS close_date,
    systemmodstamp              AS modified_at
FROM   salesforce.opportunity
WHERE  isdeleted = FALSE;                 -- drop soft-deleted CRM rows

-- 3. A dimension the connector could never build for you: won-deal facts.
CREATE MATERIALIZED VIEW mart.fct_won_opportunities AS
SELECT o.opportunity_id, o.account_id, o.amount_usd, o.close_date
FROM   stg.opportunity o
WHERE  o.stage = 'Closed Won';

Step-by-step explanation.

1. Step 1 declares the integration: an OAuth connection as source, a Redshift schema as target, and an explicit object/field selection. Listing fields explicitly (rather than "all fields") keeps the mirror lean and avoids replicating hundreds of unused CRM columns.
2. Including SystemModstamp and IsDeleted in the field list is deliberate: the connector uses SystemModstamp as the incremental watermark, and IsDeleted is what makes the mirror delete-aware. Omitting them would cripple incremental sync and delete handling.
3. The Cadence: PT15M sets a 15-minute sync — minutes-fresh, which is the expected SaaS latency and still far better than the nightly Bulk API job. You did not write any pagination, backoff, or Bulk-API-job code to achieve it.
4. Step 2's staging view is the transform that survives: it types amount, aliases fields to warehouse conventions, and filters isdeleted = false so soft-deleted CRM records do not pollute analytics. The connector delivered the deletes; you decided to exclude them.
5. Step 3 builds a fact the connector could never produce — closed-won opportunities — because that is business modelling, not replication. This cleanly separates "mirror the objects" (managed) from "model the business" (yours), which is the whole zero-ETL bargain.

Output.

opportunity_id	account_id	amount_usd	stage	close_date
006A1	001X7	25000.00	Closed Won	2026-08-30
006A2	001X9	12000.00	Negotiation	2026-09-20
006A3	001X7	8000.00	Closed Won	2026-08-12

Rule of thumb. For SaaS zero-ETL, always include the modstamp and delete-flag fields (SystemModstamp, IsDeleted) in the selection — they power incremental sync and delete-correctness — and keep the object/field list explicit so the mirror stays lean. Everything past the mirror (typing, filtering deletes, business facts) is your transform layer.

Worked example — replacing a brittle Bulk API job, and what breaks less

Detailed explanation. The value of SaaS zero-ETL is clearest when you diff it against the hand-built job it replaces. Take a typical Python Bulk API extractor — with its API-limit accounting, batch polling, watermark file, and soft-delete reconciliation — and enumerate which failure modes the managed connector simply deletes.

• The old job. Nightly; queries SystemModstamp > watermark; manages Bulk API job/batch state; writes CSV to S3; reconciles IsDeleted separately.
• The failure modes. API-limit exhaustion, batch timeouts, token expiry mid-run, watermark corruption, missed deletes.
• The connector. Absorbs all five as managed concerns.

Question. List the old job's failure modes and mark which the managed connector removes.

Input.

Failure mode	Hand-built Bulk API job	Managed connector
Daily API limit hit	job fails mid-run	throttled internally
Bulk batch timeout	manual retry	managed retry
OAuth token expiry	run aborts	auto-refresh
Watermark file corrupted	reprocess / gaps	managed state
Missed soft-deletes	stale rows	IsDeleted surfaced

Code.

# The OLD job (abridged) — every commented risk is a page you used to get.
import requests, json
from datetime import datetime

def extract_opportunities(token, watermark):
    # RISK: daily API call cap — must batch + count usage
    # RISK: token may expire mid-run — must refresh
    soql = f"SELECT Id, AccountId, Amount, StageName, SystemModstamp, IsDeleted " \
           f"FROM Opportunity WHERE SystemModstamp > {watermark}"
    job = start_bulk_job(token, soql)      # RISK: Bulk API job lifecycle
    while not job_complete(token, job):    # RISK: batch timeout / polling
        pass
    rows = fetch_results(token, job)
    # RISK: must handle IsDeleted separately (queryAll / getDeleted)
    # RISK: watermark file write must be atomic or you get gaps/dupes
    write_watermark(max(r["SystemModstamp"] for r in rows))
    return rows

-- The NEW world — no extractor at all. The connector lands the mirror;
-- your ENTIRE remaining code is the transform view.
CREATE OR REPLACE VIEW stg.opportunity AS
SELECT id AS opportunity_id, accountid AS account_id,
       amount::numeric(14,2) AS amount_usd, stagename AS stage,
       systemmodstamp AS modified_at
FROM   salesforce.opportunity
WHERE  isdeleted = FALSE;

Step-by-step explanation.

1. The old extractor's first three risks — API-limit accounting, Bulk API job lifecycle, and token expiry — are pure plumbing that produced real 3 AM pages. The managed connector internalises throttling, retry, and OAuth refresh, so these failure modes disappear from your on-call rotation entirely.
2. The watermark-corruption risk is subtle: a non-atomic watermark write could skip or duplicate records. The connector manages incremental state internally, removing an entire class of "why are rows missing?" incidents.
3. The missed-soft-delete risk required a separate getDeleted/queryAll reconciliation in the old job. The connector surfaces IsDeleted as part of the mirror, so delete-correctness becomes a one-line WHERE isdeleted = false filter instead of a second pipeline.
4. The new-world code is startling in its brevity: a single staging view. Everything the old 200-line extractor did is gone, replaced by managed replication plus one transform — the concrete meaning of "zero-ETL."
5. What remains yours is judgment, not plumbing: which fields to mask, whether formula fields need recomputation, how to model relationships. That is the right division of labour — the connector handles the API, you handle the business.

Output.

Failure mode	Removed by connector?
Daily API limit	yes
Bulk batch timeout	yes
Token expiry	yes
Watermark corruption	yes
Missed soft-deletes	yes (IsDeleted surfaced)

Rule of thumb. The SaaS zero-ETL win is measured in deleted failure modes: API limits, batch timeouts, token refresh, watermark corruption, and missed deletes all become the connector's problem. Keep only the transform view — and spend the reclaimed time on modelling and governance, not plumbing.

Data engineering interview question on SaaS zero-ETL

A senior interviewer might ask: "Your team maintains a nightly Salesforce Bulk API extractor that pages get missed when the API limit is hit, and analysts complain deleted opportunities linger for days. Design a zero-ETL replacement — the connection, the object selection, how incremental sync and deletes are handled, what latency to promise, and where you'd still put transformation and PII governance."

Solution Using a managed Salesforce connector with a delete-correct staging layer

// 1. Managed connection + zero-ETL integration (Salesforce -> Redshift).
{
  "Connection":  { "name": "salesforce-oauth-conn", "auth": "OAuth2 (managed refresh)" },
  "Integration": {
    "name": "salesforce-zeroetl",
    "target": "redshift-serverless:analytics/salesforce",
    "objects": [
      { "name": "Opportunity",
        "fields": ["Id","AccountId","Amount","StageName","CloseDate","SystemModstamp","IsDeleted"] },
      { "name": "Account",
        "fields": ["Id","Name","Industry","OwnerId","SystemModstamp","IsDeleted"] }
    ],
    "incrementalField": "SystemModstamp",
    "cadence": "PT15M"
  }
}

-- 2. Delete-correct staging + PII governance in the transform layer.
CREATE OR REPLACE VIEW stg.opportunity AS
SELECT id AS opportunity_id, accountid AS account_id,
       amount::numeric(14,2) AS amount_usd, stagename AS stage,
       closedate::date AS close_date, systemmodstamp AS modified_at
FROM   salesforce.opportunity
WHERE  isdeleted = FALSE;                 -- deleted opps vanish within one cadence

CREATE OR REPLACE VIEW stg.account AS
SELECT id AS account_id, name AS account_name, industry,
       -- PII governance: owner email dropped for analyst-facing model
       systemmodstamp AS modified_at
FROM   salesforce.account
WHERE  isdeleted = FALSE;

-- 3. Business model the connector cannot build: pipeline by industry.
CREATE MATERIALIZED VIEW mart.pipeline_by_industry AS
SELECT a.industry, SUM(o.amount_usd) AS open_pipeline
FROM   stg.opportunity o
JOIN   stg.account a ON a.account_id = o.account_id
WHERE  o.stage NOT IN ('Closed Won', 'Closed Lost')
GROUP  BY a.industry;

Step-by-step trace.

Step	Mechanism	Owner
1. OAuth connection	managed token refresh	connector
2. Object/field selection	Opportunity, Account (explicit fields)	you (config)
3. Incremental sync	watermark on SystemModstamp, 15-min cadence	connector
4. Delete handling	IsDeleted surfaced; staging filters	connector lands / you filter
5. PII + business model	drop PII, build pipeline marts	you (transform)

After deployment, the nightly extractor is deleted; opportunities and accounts land in salesforce.* within a 15-minute cadence; deleted opportunities disappear from analytics within one cycle via the isdeleted = false filter; PII is dropped in staging; and the pipeline_by_industry mart answers the business question the connector never could.

Output:

Metric	Old (Bulk API job)	New (zero-ETL)
Delete latency	days	one cadence (~15 min)
Freshness	nightly	~15 min
API-limit failures	frequent	none (managed)
Owned code	~200-line extractor	staging + mart views
PII governance	ad hoc	explicit in transform layer

Why this works — concept by concept:

• Managed connector absorbs the API plumbing — throttling, Bulk API lifecycle, pagination, and OAuth refresh become the connector's concern, deleting the failure modes that plagued the hand-built job.
• SystemModstamp watermark — the connector's incremental sync keys on the modstamp, so each cycle pulls only changed records; you no longer own the watermark state that used to corrupt and cause gaps.
• IsDeleted surfaced for delete-correctness — because the mirror includes the delete flag, a one-line WHERE isdeleted = false keeps analytics current, fixing the "deleted opportunities linger for days" complaint.
• Transform layer owns PII and business logic — dropping owner PII and building industry-pipeline marts are modelling decisions that live in your views, the T that zero-ETL relocated rather than removed.
• Cost — O(changed records) per 15-minute cadence on the connector plus your mart-compute, versus a nightly O(all records) Bulk API pull with its API-limit risk. Freshness improves from a night to minutes, delete latency from days to one cadence, and the owned code shrinks to a handful of views.

ETL
Topic — etl
SaaS ingestion and incremental-sync problems

Practice →

Data processing Topic — data-processing Modelling and PII-governance problems

Practice →

---

5. When zero-ETL fits versus classic ETL

Zero-ETL wins the same-cloud mirror-then-model case — classic ETL wins whenever you must transform before landing or cross a boundary

The one-sentence invariant: zero-ETL is the right tool when the source is on the provider's supported list, lives in the same cloud as the warehouse, and you are happy to land a raw mirror and model it afterward (ELT) — and classic ETL (or a custom CDC pipeline) is the right tool the moment any of those is false: an unsupported or on-prem source, a cross-cloud hop, a hard requirement to transform, filter, or redact before the data lands, or a multi-source join that has to happen in flight. The senior skill is not preferring one over the other; it is running a short constraint checklist and defending the choice.

The fit checklist — four questions that decide it.

• Same cloud? Zero-ETL integrations are same-provider (and usually same-region). A source in another cloud or on-prem fails this immediately and routes to classic ETL.
• Supported source? The source must be on the provider's list (Aurora/RDS, DynamoDB, specific SaaS apps). An unsupported engine — a niche database, a bespoke API — routes to classic.
• Transform after load acceptable? If landing a raw mirror and modelling in-warehouse is fine, zero-ETL fits. If you must transform, filter, or redact before landing (e.g. legal cannot allow raw PII into the warehouse), classic ETL wins.
• Low-latency mirror wanted? If you want a fresh copy of the operational shape, zero-ETL is ideal. If you want a heavily reshaped, enriched, multi-source dataset, that is a pipeline, not a mirror.

Where classic ETL still wins outright.

• Pre-load transformation is mandatory. PII redaction before landing, format normalisation across heterogeneous sources, or compliance filtering that cannot happen post-load.
• Cross-cloud or on-prem. Anything that crosses a provider boundary or originates on-prem is outside zero-ETL's reach.
• Multi-source joins in flight. Enriching a stream against a lookup, joining two sources before the warehouse, or fan-in from many systems is pipeline work.
• Unsupported sources. If the source is not on the list, there is no integration to create.

The trade-offs you accept with zero-ETL.

• Less control. You cannot inject arbitrary logic into the replication path; transformation is strictly post-load.
• Schema coupling. Source DDL propagates into the warehouse and can break downstream models (section 2).
• Vendor lock-in. The integration ties you to the provider's ecosystem; moving off it means rebuilding ingestion.
• Cost shape. Continuous replication compute/storage replaces batch compute; usually cheaper to operate but a different bill, and not free.

The hybrid reality — most warehouses run both.

• Zero-ETL for the supported same-cloud operational sources. Aurora, DynamoDB, and supported SaaS land as mirrors.
• Classic pipelines for the rest. Cross-cloud sources, on-prem systems, pre-load-transform feeds, and multi-source joins stay in Airflow/Spark/dbt.
• One modelling layer over both. dbt (or equivalent) models the zero-ETL mirrors and the pipeline outputs together into one coherent warehouse, so consumers never see the seam.

Common interview probes on the fit decision.

• "When would you NOT use zero-ETL?" — unsupported/on-prem/cross-cloud source, or a mandatory pre-load transform (e.g. PII redaction).
• "What do you give up with zero-ETL?" — control, loose coupling, portability; you accept schema coupling and lock-in.
• "Can you mix zero-ETL and classic ETL?" — yes; the hybrid is the norm, unified by one in-warehouse modelling layer.
• "Is zero-ETL always cheaper?" — usually cheaper to operate, but it is continuous replication cost, not free, and lock-in has a price.

Worked example — the fit decision matrix over four sources

Detailed explanation. The cleanest interview artifact is a decision matrix: run each candidate source through the four-question checklist and let the pattern fall out. Walk four sources — Aurora, an on-prem Oracle, a cross-cloud BigQuery export, and a source with mandatory PII redaction — through the checklist.

• Aurora (same cloud, supported, mirror OK). Passes all four → zero-ETL.
• On-prem Oracle. Fails same-cloud → classic ETL.
• Cross-cloud BigQuery. Fails same-cloud/supported → classic ETL.
• Source needing PII redaction before landing. Fails transform-after-load → classic ETL.

Question. Fill the decision matrix and record the chosen pattern for each source.

Input.

Source	Same cloud?	Supported?	Transform-after-load OK?	Low-latency mirror wanted?
Aurora MySQL	yes	yes	yes	yes
On-prem Oracle	no	no	yes	yes
Cross-cloud BigQuery	no	no	yes	no
PII-source (redact first)	yes	yes	no	yes

Code.

# The four-question checklist as code — the pattern falls out of the constraints.
def choose_pattern(same_cloud: bool, supported: bool,
                   transform_after_ok: bool, wants_mirror: bool) -> str:
    if same_cloud and supported and transform_after_ok and wants_mirror:
        return "zero-ETL"
    return "classic ETL / custom pipeline"

print(choose_pattern(True,  True,  True,  True))   # Aurora
# -> zero-ETL
print(choose_pattern(False, False, True,  True))   # on-prem Oracle
# -> classic ETL / custom pipeline
print(choose_pattern(False, False, True,  False))  # cross-cloud BigQuery
# -> classic ETL / custom pipeline
print(choose_pattern(True,  True,  False, True))   # must redact PII pre-load
# -> classic ETL / custom pipeline

Step-by-step explanation.

1. Aurora MySQL passes every question: same cloud, supported source, happy to model after load, and a low-latency mirror is exactly what is wanted. It routes to zero-ETL with no reservations.
2. On-prem Oracle fails the very first question — it is not in the cloud, so no same-cloud integration exists. The decision is immediate: classic ETL, likely a self-managed CDC connector or a batch extract.
3. Cross-cloud BigQuery fails same-cloud and supported. Even though transformation-after-load would be fine, there is no zero-ETL path across the provider boundary, so it is classic ETL (an export-and-load or a federated pull).
4. The PII source is the subtle one: it is same-cloud and supported, but a hard requirement to redact PII before it lands in the warehouse fails the transform-after-load question. Zero-ETL cannot redact in-flight, so this routes to classic ETL despite passing the other three.
5. The matrix makes the reasoning legible: a single failed constraint is enough to route to classic ETL. This is why the senior answer is "run the checklist," not "prefer the newer tool" — the constraints, not fashion, decide.

Output.

Source	Chosen pattern	Deciding constraint
Aurora MySQL	zero-ETL	passes all four
On-prem Oracle	classic ETL	not same-cloud
Cross-cloud BigQuery	classic ETL	not same-cloud/supported
PII-source (redact first)	classic ETL	transform required pre-load

Rule of thumb. Route by the four-question checklist — same cloud, supported source, transform-after-load acceptable, low-latency mirror wanted. A single "no" sends the source to classic ETL; only a clean sweep of four "yes" answers earns zero-ETL. Defend the choice by naming the failed constraint.

Worked example — the hybrid architecture with one modelling layer

Detailed explanation. Real warehouses are hybrids. Model an estate with Aurora (zero-ETL), DynamoDB (zero-ETL), a supported SaaS (zero-ETL), an on-prem source (classic ETL), and a cross-cloud source (classic ETL), all unified under one dbt project so consumers see a single coherent warehouse. Walk through the layering.

• Zero-ETL mirrors. Aurora, DynamoDB, SaaS land as raw.* mirrors, continuously.
• Classic pipelines. On-prem and cross-cloud sources land as raw.* via Airflow/Spark batch loads.
• One modelling layer. dbt reads all raw.* — mirror and pipeline alike — and builds mart.*.
• The seam is invisible. Consumers query mart.* and never know which source used which pattern.

Question. Show how zero-ETL mirrors and classic-pipeline outputs unify under a single modelling layer.

Input.

Source	Ingestion pattern	Lands as	Freshness
Aurora	zero-ETL	raw.orders	seconds
DynamoDB	zero-ETL	raw.sessions	minutes
Salesforce	zero-ETL (SaaS)	raw.opportunity	~15 min
On-prem Oracle	classic ETL	raw.gl_entries	nightly
Cross-cloud BigQuery	classic ETL	raw.ad_spend	hourly

Code.

-- The modelling layer treats every raw table identically, regardless of
-- how it arrived (zero-ETL mirror or classic pipeline output).

-- Zero-ETL mirror inputs
CREATE VIEW stg.orders      AS SELECT * FROM raw.orders      WHERE deleted_at IS NULL;   -- Aurora
CREATE VIEW stg.sessions    AS SELECT * FROM raw.sessions;                               -- DynamoDB
CREATE VIEW stg.opportunity AS SELECT * FROM raw.opportunity WHERE isdeleted = FALSE;    -- Salesforce

-- Classic-pipeline inputs (arrived via Airflow/Spark batch)
CREATE VIEW stg.gl_entries  AS SELECT * FROM raw.gl_entries;                             -- on-prem
CREATE VIEW stg.ad_spend    AS SELECT * FROM raw.ad_spend;                               -- cross-cloud

-- One unified mart joins across BOTH ingestion styles — the seam is invisible.
CREATE MATERIALIZED VIEW mart.revenue_vs_spend AS
SELECT o.order_date,
       SUM(o.total_usd)              AS revenue,
       MAX(s.spend_usd)              AS ad_spend
FROM   stg.orders   o
JOIN   stg.ad_spend s ON s.spend_date = o.created_at::date
GROUP  BY o.order_date;

Step-by-step explanation.

1. The three zero-ETL sources (Aurora, DynamoDB, Salesforce) land as raw.* mirrors continuously, each at its natural latency — seconds for Aurora, minutes for DynamoDB and SaaS. Their staging views apply the usual delete filters.
2. The two classic-pipeline sources (on-prem Oracle, cross-cloud BigQuery) land as raw.* too, but via Airflow/Spark batch jobs at nightly and hourly cadence. From the modelling layer's perspective, they are just tables in the raw schema.
3. The critical design choice is that staging views treat all raw.* tables uniformly. Nothing in stg.orders versus stg.gl_entries reveals that one arrived by zero-ETL and the other by a batch pipeline — the ingestion pattern is an implementation detail below the staging line.
4. The unified revenue_vs_spend mart joins a zero-ETL mirror (stg.orders) against a classic-pipeline output (stg.ad_spend) with an ordinary SQL join. Consumers query the mart and are entirely insulated from the hybrid ingestion beneath.
5. This is the mature end state: zero-ETL for the supported same-cloud sources where it shines, classic pipelines for everything else, and a single modelling layer that erases the seam. The warehouse looks coherent; the ingestion complexity is hidden.

Output.

Mart	Joins across	Consumer sees
mart.revenue_vs_spend	zero-ETL orders + pipeline ad_spend	one clean table
mart.opportunity_sessions	SaaS + DynamoDB mirrors	one clean table
mart.gl_reconciliation	on-prem pipeline + Aurora mirror	one clean table

Rule of thumb. Do not force a single ingestion pattern across the whole estate. Use zero-ETL where it fits and classic pipelines where it must, then unify everything under one modelling layer so consumers see a coherent warehouse and never the ingestion seam.

Data engineering interview question on choosing the pattern

A senior interviewer might ask: "You are architecting ingestion for a new analytics platform with five sources: an Aurora PostgreSQL app DB, a high-traffic DynamoDB table, a Salesforce org, an on-prem Oracle finance system, and a partner's dataset in another cloud — plus a legal requirement that customer PII be redacted before it reaches the warehouse. Decide the ingestion pattern for each, justify it against a checklist, and describe the unified modelling layer."

Solution Using a checklist-driven hybrid with a single modelling layer

# 1. Run every source through the four-question checklist.
def choose_pattern(same_cloud, supported, transform_after_ok, wants_mirror):
    return "zero-ETL" if (same_cloud and supported and transform_after_ok and wants_mirror) \
                      else "classic ETL"

sources = {
    "aurora_app":    (True,  True,  True,  True),   # -> zero-ETL
    "dynamodb":      (True,  True,  True,  True),   # -> zero-ETL
    "salesforce":    (True,  True,  True,  True),   # -> zero-ETL (SaaS)
    "oracle_onprem": (False, False, True,  True),   # -> classic ETL (not cloud)
    "partner_xcloud":(False, False, True,  False),  # -> classic ETL (cross-cloud)
    "pii_feed":      (True,  True,  False, True),    # -> classic ETL (redact pre-load)
}
for name, params in sources.items():
    print(name, "->", choose_pattern(*params))

-- 2. Zero-ETL sources land as raw mirrors; classic sources land via pipelines;
--    PII feed is redacted in-flight by the pipeline BEFORE landing.
CREATE VIEW stg.app_orders  AS SELECT * FROM raw.app_orders  WHERE deleted_at IS NULL;   -- Aurora (zero-ETL)
CREATE VIEW stg.sessions    AS SELECT * FROM raw.sessions;                               -- DynamoDB (zero-ETL)
CREATE VIEW stg.opportunity AS SELECT * FROM raw.opportunity WHERE isdeleted = FALSE;    -- SaaS (zero-ETL)
CREATE VIEW stg.gl_entries  AS SELECT * FROM raw.gl_entries;                             -- Oracle (classic)
CREATE VIEW stg.partner     AS SELECT * FROM raw.partner;                                -- cross-cloud (classic)
-- PII already redacted upstream by the pipeline; warehouse never holds raw PII.
CREATE VIEW stg.customers   AS SELECT customer_id, region, tier FROM raw.customers_redacted;

-- 3. One modelling layer unifies all five ingestion styles.
CREATE MATERIALIZED VIEW mart.customer_360 AS
SELECT c.customer_id, c.region, o.total_usd, op.stage
FROM   stg.customers   c
LEFT   JOIN stg.app_orders  o  ON o.customer_id = c.customer_id
LEFT   JOIN stg.opportunity op ON op.account_id  = c.customer_id;

Step-by-step trace.

Source	Checklist result	Pattern	Why
Aurora app DB	4/4 yes	zero-ETL	same-cloud supported mirror
DynamoDB	4/4 yes	zero-ETL	same-cloud supported mirror
Salesforce	4/4 yes	zero-ETL (SaaS)	managed connector
On-prem Oracle	fails same-cloud	classic ETL	not in cloud
Partner (other cloud)	fails same-cloud	classic ETL	cross-cloud
PII feed	fails transform-after	classic ETL	redact before landing

After deployment, three sources ride zero-ETL mirrors, three ride classic pipelines (one of them redacting PII in-flight so raw PII never lands), and a single modelling layer joins all of them into customer_360. Consumers see one coherent warehouse; the ingestion choices are justified one failed-constraint at a time.

Output:

Source	Pattern	Freshness	Owned code
Aurora	zero-ETL	seconds	staging view
DynamoDB	zero-ETL	minutes	staging view
Salesforce	zero-ETL	~15 min	staging view
Oracle	classic ETL	nightly	full pipeline
Partner (x-cloud)	classic ETL	hourly	full pipeline
PII feed	classic ETL	hourly	pipeline + redaction

Why this works — concept by concept:

• Checklist-driven routing — each source's pattern is decided by four constraints, not preference; a single failed constraint routes to classic ETL, which makes every choice defensible in the review.
• Zero-ETL for the clean-sweep sources — Aurora, DynamoDB, and Salesforce pass all four questions, so they land as managed mirrors with minimal owned code and near-real-time freshness.
• Classic ETL for boundary and transform cases — on-prem, cross-cloud, and the PII feed each fail a constraint; the PII feed specifically fails transform-after-load, so a pipeline redacts before landing and raw PII never enters the warehouse.
• One modelling layer erases the seam — staging views treat every raw.* table identically, so customer_360 joins mirrors and pipeline outputs with ordinary SQL and consumers never see the hybrid.
• Cost — continuous O(changes) replication for the three zero-ETL sources plus batch O(rows) compute for the three pipelines, versus an all-classic estate that would owe you six hand-built pipelines. The hybrid minimises owned code where zero-ETL fits while retaining full control where the constraints demand it.

ETL
Topic — etl
Ingestion-pattern and architecture problems

Practice →

Data processing
Topic — data-processing
Warehouse modelling and unification problems

Practice →

---

Cheat sheet — zero-ETL recipes

• Definition to recite. Zero-ETL = fully managed, CDC-based replication that lands operational data in a warehouse in seconds-to-minutes with no pipeline to build or operate — but the transform does not vanish, it moves in-warehouse as ELT (views/materialized views/dbt). It is same-cloud and source-list-constrained, not a universal ingestion tool.
• The three nouns. Every zero-ETL integration has a seed (one-time consistent snapshot, provider-owned), CDC (ongoing change stream apply, provider-owned), and transform (in-warehouse modelling, you own). Place the transform "after the load" or you have not understood it.
• The four-question "is this really zero-ETL?" test. CDC-based? near-real-time? fully managed? lands a source mirror? All four yes = zero-ETL; fully-managed-but-batch = managed connector; not-managed = custom pipeline.
• Aurora → Redshift setup. Enable the change stream (aurora_enhanced_binlog for MySQL, rds.logical_replication for PostgreSQL) → aws rds create-integration --source-arn <aurora> --target-arn <redshift-namespace> → in Redshift CREATE DATABASE mirror FROM INTEGRATION '<id>'. Target must be RA3/Serverless with enable_case_sensitive_identifier.
• Aurora monitoring trio. SVV_INTEGRATION (overall state), SVV_INTEGRATION_TABLE_STATE (per-table synced/resyncing/failed), SYS_INTEGRATION_ACTIVITY (apply lag). Alert on state <> active, any failed table, and sustained lag > 5 min. A failed table = targeted resync.
• DynamoDB two paths. Zero-ETL integration (managed CDC into Redshift/OpenSearch, minutes-fresh, deletes included) for freshness; ExportTableToPointInTime to S3 (DynamoDB-JSON/Ion, zero read capacity, PITR required) for backfills and reprocessing. Never Scan production for analytics.
• DynamoDB incremental export. Chain INCREMENTAL_EXPORT windows with ExportViewType=NEW_AND_OLD_IMAGES; persist each window's end as the next window's start; MERGE by primary key (no NewImage = delete). O(changes) per window.
• Single-table-design flattening. Load exported items into a Redshift SUPER column, unwrap the typed DynamoDB-JSON envelope (.S, .N), filter by the type attribute to split each entity into its own relational table. This is the T that DynamoDB makes visible.
• SaaS (Salesforce) zero-ETL. Managed connector (Glue zero-ETL for applications / AppFlow) over an OAuth connection; select objects + explicit fields including SystemModstamp (incremental watermark) and IsDeleted (delete-correctness); ~15-min cadence. Keep the field list lean.
• What SaaS zero-ETL deletes. API-limit accounting, Bulk API job lifecycle, pagination, OAuth token refresh, watermark state, and missed-delete reconciliation — all become the connector's problem. You keep only the transform views (typing, delete filter, PII governance, business marts).
• Fit checklist (zero-ETL vs classic). Same cloud? supported source? transform-after-load acceptable? low-latency mirror wanted? A single "no" routes to classic ETL. Classic wins for on-prem, cross-cloud, unsupported sources, mandatory pre-load transform (PII redaction), and multi-source in-flight joins.
• Hybrid pattern. Run zero-ETL for supported same-cloud sources and classic pipelines for the rest, then unify every raw.* table under one dbt/modelling layer so consumers see a coherent warehouse and never the ingestion seam. This hybrid is the 2026 norm.
• Trade-offs you accept. Less control (no in-flight logic), schema coupling (source DDL propagates and can break marts — gate source DDL), vendor lock-in, and a continuous-replication cost shape that is usually cheaper to operate but never free.

Frequently asked questions

What is zero-ETL in one sentence?

Zero-ETL is a fully managed, change-data-capture-based replication service that lands your operational data (from Aurora/RDS, DynamoDB, or supported SaaS applications like Salesforce) into an analytics warehouse in near-real-time without you building or operating an extract-and-load pipeline. The name describes what it removes operationally — the pipeline code, schedulers, staging buckets, and connector maintenance — not the disappearance of transformation, which relocates into the warehouse as ELT. It is a same-cloud, source-list-constrained mirror, so it is best understood as "managed replication plus in-warehouse modelling," not as a universal ingestion tool.

Does zero-ETL really mean no transformation?

No — this is the single most common misconception. Zero-ETL removes the extract and load stages and the pipeline you operated, but the transform is still required and simply moves downstream: type coercion, denormalisation, slowly-changing-dimension logic, currency conversion, deduplication, and PII masking all run inside the warehouse as views, materialized views, or a dbt project after the raw mirror lands. The architecture is ELT (extract-load-transform), so a more honest name would be "managed replication with in-warehouse transform." Any team that assumes the data arrives clean and modelled will ship a bug, because what actually arrives is a faithful mirror of the source schema.

How is Aurora zero-ETL different from running Debezium myself?

They implement the same idea — change data capture over the database's write stream — but Aurora zero-ETL is fully managed while Debezium is self-operated. With Debezium you run Kafka Connect, configure a connector, manage a replication slot, monitor slot lag, and patch the stack; with Aurora zero-ETL you enable the enhanced binlog (MySQL) or logical replication (PostgreSQL), run create-integration with the source and target ARNs, bind a Redshift database with CREATE DATABASE ... FROM INTEGRATION, and monitor three system views. Zero-ETL trades the flexibility and portability of a self-managed CDC pipeline for near-zero operational surface, at the cost of vendor lock-in and same-cloud/supported-source constraints. Debezium still wins when you need cross-cloud targets, custom transforms in the stream, or a source Aurora zero-ETL does not support.

How does DynamoDB reach a warehouse without scanning it?

DynamoDB has two managed, no-Scan paths. The first is a zero-ETL integration that continuously replicates items into Redshift (or OpenSearch) via the table's change data, giving minutes-fresh analytics with deletes included and no read-capacity consumption on the live table. The second is ExportTableToPointInTime, which writes a consistent snapshot to S3 as DynamoDB-JSON or Amazon Ion — a full export for the initial backfill or reprocessing, or incremental exports (chained windows with NEW_AND_OLD_IMAGES) to stay fresh cheaply; because the export runs off point-in-time-recovery backups it consumes zero read capacity and cannot throttle production. Use the integration for ongoing freshness and the export for backfills; either way, flatten the single-table-design items into per-entity relational tables downstream.

Can I use zero-ETL for Salesforce and other SaaS sources?

Yes — SaaS zero-ETL is delivered through managed connectors (on AWS via Glue zero-ETL integrations for applications and Amazon AppFlow) that consume the SaaS platform's change API and land each object as a warehouse table. The connector absorbs the plumbing that made hand-built SaaS extractors brittle: daily API-limit accounting, Bulk API job lifecycle, pagination, OAuth token refresh, incremental watermarking on SystemModstamp, and soft-delete handling via IsDeleted. Latency is typically minutes rather than the seconds you get from a binlog, because SaaS change APIs are coarser than a database log — but that is still near-real-time versus a nightly batch. You still own object/field selection, formula-field decisions, relationship flattening, and PII governance in the transform layer.

When should I NOT use zero-ETL?

Route to classic ETL (or a custom CDC pipeline) whenever any fit-checklist question fails: the source is on-prem or in a different cloud than the warehouse; the source is not on the provider's supported list; you must transform, filter, or redact before the data lands (for example, a legal requirement that raw PII never enter the warehouse); or you need multi-source joins and enrichment performed in flight rather than a straightforward mirror. You also weigh the trade-offs zero-ETL imposes — reduced control, schema coupling where source DDL propagates and can break downstream models, and vendor lock-in. In practice most warehouses are hybrids: zero-ETL for the supported same-cloud operational sources, classic pipelines for everything else, unified under a single in-warehouse modelling layer.

Practice on PipeCode

• Drill the ETL practice library → for the extract-load-transform, incremental-sync, cutover, and reconciliation problems senior interviewers love.
• Rehearse the source-system fundamentals on the database practice library → for the change-data-capture, replication, and key-value export patterns that power zero-ETL.
• Sharpen the load-and-model axis on the data-processing practice library → for semi-structured flattening, single-table-design modelling, and warehouse unification.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the zero-ETL-versus-classic-ETL decision matrix against real graded inputs.

Lock in zero-ETL decision muscle memory

Docs explain the buttons. PipeCode drills explain the decision — when zero-ETL is a same-cloud mirror-then-model win, when the transform is still yours to build in-warehouse, when a DynamoDB export beats a scan, and when a mandatory pre-load transform sends you back to classic ETL. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice ETL problems →
Practice database problems →