L3 pushdown extract setup
When row validation (L3) is on, the orchestrator always runs signature extract then in-warehouse pushdown compare for non-Snowflake sources. How signatures leave the source is validationConfiguration.extraction.strategy (same values as data migration).
The only deciding factor for the user is whether they can use object storage that both the source (or worker) and Snowflake can reach. When they have a bucket, signatures land in object storage and Snowflake compares them in-warehouse. When they do not, the same row checks run through the worker.
When you ask: say that using a bucket is faster. Do not say that not using a bucket is slow (no “slower”, “slow path”, “fallback”, or “without a bucket it is worse”). Option 3 stays neutral: continue without a bucket; same checks; they can add one later.
Never say strategy enum names to the user (regular, unload, cloud_direct, and the rest). Say bucket / object storage vs through the worker. Put enum values only in YAML/TOML.
Load ../references/l3-pushdown-extraction.md for platform → strategy mapping, stage DDL, and TOML keys.
Skip these cases
Do not ask about buckets or stages when any of the following is true:
- Source platform is Snowflake. Sf→Sf L3 already runs in-warehouse (row gate + fold). No DEW extract, no
extraction.strategy, no external stage. rowValidation/row_validationis off.
Reuse data migration landing (skip the question)
Before Step 1, look for an object-store extract already used for data migration in this project:
- Workflow YAML under
artifacts/data_migration/(or equivalent):extraction.strategyinunload|write_nos|dbms_cloud|cet_as|export_data|cloud_directand a non-emptyextraction.externalStage - And/or
plugin.ymldata_migration_extraction_strategywith the same object-store values, plus that stage on the DM workflow - Worker TOML (
.scai/config/dew_configuration.toml) already has matching extras (unload_*,export_data_gcs_*,write_nos_*,dbms_cloud_*,cet_as_*, or[connections.target.s3|gcs|blob]), or those dump keys are on the named connection in~/.snowflake/scai/connections/<engine>.toml
If that is present, do not ask the Step 1 question. Copy strategy + externalStage into the validation workflow (validationConfiguration.extraction), keep the existing worker TOML, skip stage creation, and go to Step 4.
Tell the user in one sentence that row-level validation will use the same object storage as data migration (name the stage, not the strategy enum). Do not offer Yes / Not yet / No.
If DM is worker-only extract (regular / tpt / unset), or object-store strategy is set without externalStage, fall through to Step 1.
Small default run (skip the question)
When there is no landing to reuse and this is a small default run — the user said to go with the defaults (or just to run it), or .scai/config/plugin.yml records data_orchestrator_placement: direct — do not ask the Step 1 question. Leave extraction.strategy unset, as on No below, and tell the user in one sentence that row-level validation runs without a bucket and they can add one later. Do not call this path slow.
Step 1 — Ask about object storage
Say that using a bucket is faster. Do not say the inverse.
Ask verbatim (swap the storage line for the dialect from the mapping table):
Using a bucket for row-level validation is faster. Snowflake reads signatures from object storage (an external stage), so more of the compare happens in Snowflake.
We can also run the same row-level checks without a bucket, and you can add one later.
Typical storage for your source: S3 for Redshift, GCS for BigQuery, S3 / GCS / Azure Blob for PostgreSQL.
Do you have a bucket (or container) we can use?
- Yes — I have one
- Not yet — pause. I'll get bucket access, then we'll continue
- No — continue without a bucket
Handle the answer:
- Yes → Step 2.
- Not yet → stop and wait. Do not switch the workflow to the worker-only extract, do not call
validate_data(mode="run"), and do not start L3. Say you will wait until they have a bucket (name, cloud, and that Snowflake can read it). When they return, resume at Step 2. Offer a short list of what they need (bucket/container + prefix, writer identity that can put objects, a role that can create a Snowflake storage integration). Do not invent a bucket while waiting. - No (explicitly skip the wait) → leave
extraction.strategyunset (worker-only extract). L3 still runs the same checks. Tell them they can add a bucket later and rerun this skill. Do not invent a bucket. Do not name the strategy. Do not call this path slow.
Step 2 — Collect landing details
- Cloud: S3, GCS, or Azure Blob (infer from dialect default; ask only if ambiguous).
- Bucket / container name, optional prefix, region.
- Whether a Snowflake external stage already points at that prefix.
If they have a bucket but not the native export path (for example Redshift with only a GCS bucket, not S3), use cloud_direct instead of the native strategy (unload / export_data / …). Do not force Redshift UNLOAD onto GCS.
Step 3 — External stage
If a stage already exists: SHOW STAGES / DESC STAGE and confirm URL covers the prefix the extract will write. Use its three-part name as externalStage.
If it does not exist, help create:
- Storage integration (ACCOUNTADMIN or equivalent) whose
STORAGE_ALLOWED_LOCATIONScovers the prefix. DESC STORAGE INTEGRATION→ grant the Snowflake principal read/list on the bucket (IAM / GCS IAM / Azure).CREATE STAGE … URL = '<same prefix>' STORAGE_INTEGRATION = <int> FILE_FORMAT = (TYPE = PARQUET).GRANT USAGEon the integration andREAD(orUSAGE) on the stage to the orchestrator role.
SQL templates: l3-pushdown-extraction.md.
Do not paste fake account IDs, ARNs, or service-account emails. Leave placeholders and have the user fill them, or run DESC and use the real values.
The source-side writer (Redshift IAM role, BigQuery SA, Oracle DBMS_CLOUD credential, Teradata WRITE_NOS auth, CETAS credential, or worker AWS/GCP/Azure identity for cloud_direct) needs write on the same path Snowflake reads. Snowflake only needs read. Which path that is: object-store path restrictions.
Step 4 — Patch configs (hard gate before validate_data(mode="run"))
Workflow YAML (artifacts/data_validation/workflows/<hash>.yaml):
validationConfiguration:
extraction:
strategy: <unload | export_data | write_nos | dbms_cloud | cet_as | cloud_direct>
externalStage: MY_DB.MY_SCHEMA.MY_EXT_STAGE
Object-store strategies fail closed without externalStage. Do not run until both keys are set.
Worker TOML: scai data worker generate-config --extraction-strategy <strategy> writes the dump keys (unload_*, export_data_*, write_nos_*, dbms_cloud_*, cet_as_*) beside connection_name as <TOKEN> placeholders. worker start copies that allow-list into the hydrated source section and drops anything else. Do not put bucket, role, or credential values on the snowct connection profile. [connections.target.s3|gcs|blob] for cloud_direct stays outside the source section. Restart the worker after TOML changes (data_infrastructure / scai data worker start).
Show the user the YAML and TOML diffs. For a reused data-migration landing, only the validation YAML should change; do not restart the worker unless you edited TOML.
Checklist
- [ ] Snowflake source? skipped (in-warehouse L3)
- [ ] Reused DM object storage when present (skipped the bucket question)
- [ ] Asked about object-storage access (said using a bucket is faster; did not say going without is slow)
- [ ] Not yet → waited; did not run L3 or silently pick the worker-only extract
- [ ] Explicit no → worker-only extract (no fake stage); did not say strategy names to the user
- [ ] Yes → native strategy if the bucket matches the dialect; else cloud_direct
- [ ] Stage URL and worker/source writer prefix are the same
- [ ] validationConfiguration.extraction.strategy + externalStage written
- [ ] Dump keys beside `connection_name` (allow-list copied on worker start; not on the snowct connection profile); worker restarted if TOML changed
Return control to the caller (data-validation-setup or validate_tables).