Skip to content

Migration and Medallion extension

Product direction

Product goal: add an optional end-to-end migration capability to the retail/e-commerce starter kit while keeping BigQuery and Snowflake implementations separate. The loader path has passed synthetic CSV and SQLite to local DuckDB runs, including reconciliation and stale-schema refusal. The full warehouse Medallion flow has not been validated end to end: no cloud load, migrated-data dbt Silver/Gold build, or full-refresh comparison has run. The intended flow moves source data into an isolated warehouse target, reconciles the move, then exposes analytics through Bronze, Silver, and Gold layers without changing the existing starter examples or coupling the editions.

The owner selected local, headed UTF-8 CSV files and relational SQL databases. The current loader accepts PostgreSQL via postgresql+psycopg:// and existing immutable SQLite file snapshots; other SQLAlchemy dialects fail closed even when a driver is installed and require code changes plus security review before support. The product direction is source-to-warehouse ingestion: BigQuery is the primary destination and Snowflake remains separate. SQLite is used for synthetic source demos. The loader does not support arbitrary file formats or remote object stores. Keep the connector interface source-specific and the downstream model contract warehouse-neutral.

The current prototype is a small Python CLI that streams local CSV rows or reads explicitly selected SQL tables through dlt, then fully replaces selected tables in a Bronze namespace. The existing warehouse-specific dbt projects contain opt-in retail Silver and Gold models. Remote object stores, other file formats, generalized retry/pagination behavior, and incremental/CDC loading are future scope. See the dlt SQL database source guide and BigQuery/Snowflake destination guides.

Proposed architecture and current boundaries

source connector -> isolated Bronze landing -> Silver normalized entities -> Gold business marts
                          |                         |                          |
                  raw + load metadata       typed/cleaned records      agreed metric contract
  • Ingestion connector (current): reads local headed UTF-8 CSV files or selected SQL tables and performs full replacement. The loader adds _migration_run_id and _ingested_at_utc, plus _source_file_name for CSV or _source_entity for SQL. Generalized retry/pagination and incremental loading are future work. Secrets come from the user's local environment or a secret manager; never from committed profiles or CLI arguments.
  • Bronze (current): keeps source-shaped rows with the loader metadata above. dlt also creates _dlt_loads, _dlt_pipeline_state, and _dlt_version system tables in the dedicated namespace and may add _dlt_ lineage columns to data tables. Treat these names and objects as reserved; do not manually alter them. Source update fields are retained only when present in the input; the loader does not create a source update marker. Define retention explicitly; this is not an unrestricted copy of customer data.
  • Silver (current): the included models cast types, normalize selected retail fields/statuses, and deterministically deduplicate the four canonical retail tables. Descriptive text fields trim ASCII U+0020 spaces explicitly in both editions; tabs and Unicode whitespace remain data. Reviewed source aliases into the canonical retail contract are supported by the migration mapping file; arbitrary mappings and delete/tombstone propagation are future work. For orders, Silver sorts first by source updated_at; for customers, products, and order items, _ingested_at_utc is run-level and equal across rows in a full replacement, so canonical output fields determine duplicate winners. These field-value tie-breakers produce a stable choice only when typed input values and session-timezone settings are identical; they do not identify the business-correct latest row. A source-specific row-version timestamp is needed for that claim. Cross-warehouse timestamp/date parity is not verified. As a review inference, Snowflake timestamp casts and timestamp-to-date conversion can depend on timestamp type and session timezone, while BigQuery may apply different conversion defaults. A timezone shift could alter timestamp ordering, deduplication winners, or the date assigned to daily sales. Explicitly align UTC conversion/session behavior and test timezone-boundary fixtures in both warehouses before claiming parity; no warehouse verification has been performed. Keep source keys and provenance columns for traceability.
  • Gold (current): the included marts implement the documented retail metrics only. New marts require agreed grain, status semantics, and PII scope, with matching metric definitions in the shared mart contract and SQL in each edition.
  • Control plane: the migration CLI selects the source, entities, and target namespace. Its explicit --dry-run mode inventories the source without creating persistent pipeline state or writing data; temporary disposable state may be used. Cloud writes require an explicitly confirmed target and table list.

Migration lifecycle

  1. Inventory source entities, keys, types, row counts, update/delete behavior, size, and data-retention constraints. Establish source snapshot consistency separately: verify a supported source snapshot/version boundary where available and keep its evidence in a restricted operational record. If no reliable marker is available, agree and record a maintenance window with the source owner and avoid concurrent changes where practical. Do not add potentially sensitive snapshot identifiers to migration manifests. The loader does not create or verify a transactionally consistent source snapshot; schema fingerprints and pre-extraction CSV filename/size metadata do not prove row-level consistency or which exact source version was extracted.
  2. Map source fields to Bronze/Silver contracts; report reflected type mismatches and review values before extraction. Python mapping/profile casts are not proof of BigQuery or Snowflake castability, precision preservation, or timezone equivalence. Before claiming cross-edition parity, explicitly define UTC handling for timestamp comparisons and date derivation, then test rows near timezone/date boundaries in both warehouses.
  3. Run --dry-run first and review the resolved tables, columns/types, missing or empty inputs, row counts, and combined schema_confirmation_fingerprint. CSV counts are exact; SQL inventory uses a bounded existence probe and does not count every row. The fingerprint binds ordered table/column names, available source type families, and the mapping configuration; CSV types remain untyped. Compare the inventory with the selected Silver/Gold contract, resolve mapping or compatibility gaps, estimate target storage/compute, and verify a disposable or approved non-production target. The dry run inspects source inputs and resolves local destination configuration; it does not contact the destination, verify access, or check cloud namespace existence. Its ready_to_load field is never a cloud-write go-ahead. A real run must pass the exact dry-run fingerprint as --confirm-schema-fingerprint; the loader also fails closed if current inventory, mapping configuration, or the schema actually opened for extraction differs. Perform a cloud load only after a separate invocation passes the namespace preflight and exact --target-id, --confirm-target, --confirm-tables, and schema fingerprint checks. See the migration guide for commands and output details.
  4. For the current prototype, execute the selected-table full replacement only after reviewing the source inventory and confirming the isolated target and exact table list. Record counts and rejects without logging row values or credentials. Stable checkpoints, resumable batches, and recording a committed source position are future requirements.
  5. Compare source-to-Bronze counts using the current count report, then run dbt tests and compare Gold metrics against agreed source totals. Bronze-to-Silver key coverage requires manual review until the proposed reconciliation tool is implemented. Count-only manifests do not prove original-source key equality.
  6. Incremental updates, insert/update/delete capture, and reliable watermark/CDC handling are not implemented. Until they are added and validated, use only the documented full-replacement behavior; do not promise change capture from an event timestamp alone.
  7. Future release requirement: provide a cutover checklist, a reviewed rollback/cleanup command scoped to the extension-owned namespace, and a rerun procedure that cannot silently overwrite unrelated datasets or schemas. No cloud cleanup command is implemented.

Post-load Bronze/Silver key reconciliation design

This is a proposed, opt-in destination check, separate from the current Bronze row-count report. It compares the loaded Bronze key set with the corresponding Silver key set, and checks the existing Silver relationships. It does not prove equality with the original source keys: the migration manifest stores aggregate counts, not source key values, and this design does not rescan the source.

  • Use the four existing Silver business keys: customers.customer_id, products.product_id, orders.order_id, and order_items.order_item_id. For each table, report Bronze non-null key rows, distinct keys, duplicate groups and extra duplicate rows; Silver row and distinct-key counts; distinct Bronze keys missing from Silver; and distinct Silver keys absent from Bronze. A raw Bronze row-count difference can be expected because Silver filters null keys and deduplicates; compare key sets and report duplicate counts separately.
  • Check Silver relationships matching the current dbt tests: orders.customer_id → customers.customer_id, order_items.order_id → orders.order_id, and order_items.product_id → products.product_id. Count non-null child rows without a matching parent key; apply the same null handling and key casts as the Silver models and dbt relationship tests.
  • Keep BigQuery and Snowflake queries edition-specific while returning the same report fields and status values. Use the exact key casts and filters in each edition's Silver SQL. Return aggregate counts only; do not emit keys, hashes, samples, source values, credentials, or raw query errors.
  • Require a separate explicit full-reconciliation opt-in, exact target confirmation, and the normalized table confirmation. This reads full destination tables, can incur warehouse cost, and must not run during ordinary dry-run or load commands. Set report_type: key_coverage_diagnostic, label every result count_only_diagnostic, use count_match, count_mismatch, or count_unavailable, and always set cutover readiness to false. A matching report is evidence for review, not approval to cut over.

Acceptance requires synthetic fixtures in both editions that cover valid key sets, duplicate Bronze keys deduplicated by Silver, null keys, missing/extra Silver keys, valid and orphan relationships, and unavailable tables/keys. Equivalent fixtures must produce the same aggregate counts and statuses in both editions. Confirm that opt-out performs no destination query, the report and logs contain no key or row values, and no result implies source-key equality or cutover readiness.

Required safeguards and acceptance

  • Separate configuration, credentials, Terraform state, and target namespace for BigQuery and Snowflake.
  • Use least-privilege target roles and avoid production/customer data in fixtures, logs, CI, or release archives.
  • Inventory selected source inputs before loading. Require an explicit write invocation and exact target/table confirmations for cloud replacement; full load, incremental load, backfill, validation, cutover, and cleanup must have clear scope.
  • Validate schema compatibility, uniqueness, null expectations, row-count deltas, delete behavior, and key business metrics. Store a minimal migration manifest; never include credentials or row values, and treat entity/target identifiers as potentially sensitive.
  • Prove a repeat run is safe and that interrupted batches can resume or be discarded predictably.
  • Demonstrate a synthetic end-to-end migration and compare its Gold outputs with a full-refresh baseline before claiming completion.
  • Keep the current dbt-only examples runnable without this optional extension.

Decisions made and remaining gates

  1. Selected: local headed UTF-8 CSV files plus selected relational SQL tables; PostgreSQL via psycopg and immutable SQLite snapshots are accepted. Other SQLAlchemy dialects fail closed and require code plus security review before support.
  2. Selected: source-to-warehouse; BigQuery is the primary target and Snowflake remains an independent destination edition.
  3. Initial behavior: source-only inventory with --dry-run, followed by selected-table full replacement only on explicit invocation and exact target/table confirmation in a dedicated Bronze namespace. CDC, ongoing sync, delete capture, schema-object migration, and cross-warehouse moves are not part of this initial implementation.
  4. Still needed before a real run: source entity/table selection, source field mapping for the retail Silver/Gold contract, approximate volume, destination project/account, warehouse permissions, and cost review.

Do not run a migration until the target and source table selection are explicit and verified non-production. No customer credentials or source data are needed to build the synthetic framework.