dlt-snowflake-template

Imported from innovation-igloo/dlt-snowflake-template (AGENTS.md). Install upstream with npx skills add innovation-igloo/dlt-snowflake-template. Copyright stays with the author.

AGENTS.md

Context for an AI coding agent working in this repository. Read this before changing anything; several of the constraints below are load-bearing and look like tidiness opportunities.

Human-facing docs, in the order a person would read them: README.md, then the three runbooks (MAKE-COMMANDS.md, MAKE-COMMANDS-SPCS.md, MAKE-COMMANDS-PROD.md), then docs/development-gameplan/. make help lists every target.

What this repo is

A registry-driven dlt → Snowflake template. A pipeline is data, not code. You add one by writing a YAML entry, not by writing a Python module.

One container image runs every pipeline. The only argument it receives is a pipeline name; everything else is looked up. That single fact explains most of the design, because a Snowflake Task passes no arguments at all.

registries/*.yml  ──sync──▶  DLT_DB.OPS.PIPELINE_REGISTRY  ──read──▶  container
                                                                         │
                                                          dlt load ──────┘──▶ <DB>.<schema>

Layout, and the rule that holds it together

pipelines/
  run.py                 THE entry point. Dispatches on a pipeline's source type.
  registry_sync.py       Pushes every registries/*.yml into the control table.
  common/                Shared by every runner package.
    models.py            PipelineSpec + validation.
    runner.py            Secrets, tokens, params, preflight, execution, CLI.
    registry_store.py    Runtime read of the control table.
    observability.py     Tagged logs + OPS._DLT_RUNS.
    snowflake_session.py Connector session (PAT / key-pair / OAuth).
  api_batch/             Claims: rest_api, sample.
  db_batch/              Claims: sql_database.
  cdc/                   Snowpipe Streaming. A separate flow; see "Out of scope".

A runner package owns exactly two things: SUPPORTED_SOURCES and build_source(spec). Everything that does not differ by source type belongs in common/.

A runner package must never import a sibling runner package. tests/test_packages.py enforces this by walking the AST, and asserts the packages' SUPPORTED_SOURCES partition models.ALL_SOURCES exactly — so a source type cannot be claimed twice or forgotten.

To add a source type, create a package with those two symbols and add it to REGISTRY_PACKAGES. To add a source system, run make new-source — do not hand-write the four files it generates, because they cross-reference each other by name.

Constraints that will look like cleanup opportunities

Each of these has a failure behind it. Changing one without understanding why is how the bug comes back.

pipelines/common/models.py must import with no runtime dependencies. Not dlt, not the Snowflake connector. deploy/tasks/generate_tasks.py imports it to emit Task DDL on a CI runner where neither is installed. CI proves this in a subprocess with both blocked. Anything needing dlt belongs in common/runner.py.

Deferred imports are deliberate. build_source imports each driver inside its own branch so the dispatcher does not pay for a driver it will not use. PLC0415 is enabled in ruff precisely so a new deferred import has to be marked rather than appearing by accident.

The Task DDL sets no NAME =. A completed SPCS job service is retained for 30 days and there is no OR REPLACE, so a fixed name succeeds exactly once and then collides nightly for a month. An unnamed job service is created in the submitting Task's schema (DLT_DB.OPS), which is why sql/base/02_control_plane.sql grants CREATE SERVICE there and not only on DEPLOY.

The job spec is rendered locally and inlined, not staged. SPECIFICATION_TEMPLATE_FILE fails a couple of seconds after every Task fires with Object 'snowflake.snowpark.pypi_shared_repository' does not exist or not authorized — Snowflake's server-side renderer resolves a dependency you do not control. The dev templates are staged; production inlines.

EXTERNAL_ACCESS_INTEGRATIONS must precede FROM in EXECUTE JOB SERVICE. After it, the statement is a syntax error. Note also that EXECUTE JOB SERVICE cannot be compile-checked standalone — wrap it in CREATE OR ALTER TASK to validate.

CREATE OR ALTER TASK resets a Task to SUSPENDED. tasks-apply and tasks-resume are separate on purpose, because generating a schedule and starting one are different decisions. CI is the exception and runs both, or every deploy would silently stop every schedule it touched.

database in a registry is a STEM, not a database name. database: SALES resolves to SALES_DEV_DB or SALES_PROD_DB. Resolve it with python -m pipelines.common.models --database <name> --env DEV|PROD; never interpolate a variable.

A scheduled pipeline must declare secret, env_var and external_access. Validation fails without all three, because a Task cannot be handed them at 09:00 UTC.

env_var is derived, not free-form. It is the dlt config path uppercased with dots as double underscores: sources.foo.token → SOURCES__FOO__TOKEN. A mismatch fails inside the container with a KeyError, not at submission.

Warehouses set GENERATION = '2' — quoted. Unquoted 2 is invalid value [2] for parameter 'GENERATION'. The generation shows in SHOW WAREHOUSES → resource_constraint, not type.

Mounting a Snowflake SECRET needs READ, not USAGE.

Working on this repo

Everything below is offline. No Snowflake, no network.

make install                  # uv sync (dev extra: duckdb, dlt[duckdb], jinja2, pytest, ruff)
make lint                     # ruff
make test                     # full suite
make list                     # every pipeline, its source and schedule
make run-local NAME=sample    # end-to-end into DuckDB, no credentials
make emit                     # write build/{sync,tasks,resume}.sql without applying anything
make plan                     # render a markdown plan to standalone HTML

Read build/tasks.sql rather than reasoning about the generator. It is the complete literal statement that will be applied.

pytest needs no sys.path preamble; [tool.pytest.ini_options] pythonpath = ["."] handles it. The suite passes from any working directory — keep it that way. If a test depends on dlt resolving .dlt/config.toml, run it in a subprocess with an explicit cwd: dlt discovers .dlt/ relative to the working directory and caches the run context, so an in-process version is coupled to both the invoking directory and test order.

Verifying a change

• Makefiles: check git diff. make help parses ## comments and never executes a recipe, so it passes even when an edit has joined a recipe line onto its target.
• Guards: a test that cannot fail is not a guard. Plant a violation, confirm it fails, revert.
• Docs: tests/test_docs.py checks that every documented make target exists, that relative links resolve, and that no reference names a deleted module or file. It covers dotted module paths as well as slashed ones: a python -m command names a module, not a file, so a path-existence check cannot see it — which is how a whole docs site once kept telling users to run a module that had been deleted.
• Docs site: cd docs/development-gameplan && npm run build runs tsc --noEmit first. Code samples are template literals holding pre-colored HTML — never put a backtick inside one; it terminates the string and reports as TS1005.
• SQL: compile-check with only_compile before applying. Invalid property names and some values are rejected at compile time before the privilege check, so a privilege error means the syntax was accepted — useful when you lack the grant to run the statement.

Conventions

Comments explain the failure, not the mechanism. Most of the comment volume here records something that actually broke. A comment that survives an edit with its reasoning trimmed off is how the next person reintroduces the bug. If you shorten one, keep the why.

SQL comments state the expected result or the invariant — -- Must return 1, -- owner must read DLT_LOADER_ROLE — not what the query does.

Secrets never appear in a registry, the control table, or a comment. Use a secret: reference.

Roles are not cosmetic. $(SNOW) for DDL that self-selects with USE ROLE; $(SNOW_DEV) for dev jobs; $(SNOW_LOADER) for anything production owns or writes. A Task runs with its owner's privileges, and fixing that later is a GRANT OWNERSHIP per Task rather than a re-run.

Prefer deleting to deprecating. This is a template people fork; a deprecation shim is dead weight they inherit.

Out of scope unless asked

CDC (pipelines/cdc/, sql/cdc/, cdc_service.tmpl.yaml, the cdc-* targets) is a separate subsystem. Only the MySQL adapter is verified; postgres, sqlserver and generic set SUPPORTED = False and are rejected at validation. Fixing a comment there is fine; changing behaviour is not, without being asked.

Production DDL (sql/prod/) is meant to be tailored per customer. sql/base/ and sql/dev/ are the shared path.

sql/** is applied by a human, never by CI. Those files create roles and grants as SYSADMIN, USERADMIN and ACCOUNTADMIN. tests/test_workflows.py asserts they appear in no CI path filter.