Skip to content

ADR 006: Bronze TRUNCATE + FORCE Decision

Status: Accepted Date: 2025-12-24 Decision Makers: Data Engineering Team Related: ADR 001 (Load Strategy Terminology), dbt Phase 1 Implementation

Context

When implementing Bronze landing tables for the xo-data ELT pipeline, we faced a critical decision about how to handle data loading to ensure idempotent pipeline behavior.

Problem Statement

  1. Snowflake Copy History Limitation (14 Days):
  2. Snowflake Standard Edition only retains COPY history for 14 days
  3. After 14 days, FORCE=FALSE provides no protection against duplicate loads

  4. Pipelines need to be rerunnable indefinitely (not just within 14 days)

  5. DAG Reruns Must Be Idempotent:

  6. Running a DAG twice for the same data_date should produce identical results
  7. Current behavior: APPEND mode causes duplicate data on reruns

  8. Requirement: Rerunning 2025-12-15 data should replace previous load, not append

  9. Bronze Tables Are Temporary:

  10. Bronze is a staging/landing layer, not permanent storage
  11. Historical data is preserved in Silver layer (dbt transformations)
  12. Bronze should be truncated and reloaded daily

Initial Concerns

  • Data Loss: What if TRUNCATE succeeds but COPY fails?
  • Concurrent Queries: What if analysts are querying the table during TRUNCATE?
  • Error Scenarios: How to handle partial loads or malformed data?

Decision

Always TRUNCATE Bronze tables before loading, with FORCE=TRUE, wrapped in a transaction.

Implementation Pattern

BEGIN TRANSACTION;
  TRUNCATE TABLE {bronze_table};
  COPY INTO {bronze_table} FROM @stage
    FORCE = TRUE
    ON_ERROR = 'ABORT_STATEMENT'
  ;
COMMIT;

Key Components

  1. TRUNCATE TABLE: Empties table while preserving structure (metadata operation, instant)
  2. FORCE=TRUE: Bypasses copy history to allow reloading same files
  3. ON_ERROR='ABORT_STATEMENT': Triggers ROLLBACK on any COPY failure
  4. Transaction Wrapper: Ensures atomic TRUNCATE + COPY (all-or-nothing)

Architectural Clarification

Critical Distinction: - Bronze (xo-foundry): ALWAYS truncated, regardless of refresh type - Silver (dbt): Refresh type (full_refresh, incremental, historical) affects merge logic, not Bronze loading

Load strategies ONLY affect: 1. S3 path structure (already implemented) 2. Silver merge logic (handled by dbt, not xo-foundry)

Rationale

Why TRUNCATE?

  1. Idempotency: Guarantees same result on DAG reruns
  2. Simplicity: No complex conditional logic needed
  3. Performance: TRUNCATE is a metadata operation (instant)
  4. Safety: Preserves table structure, grants, and Time Travel
  5. Bronze Purpose: Landing tables are temporary by design

Why FORCE=TRUE?

  1. 14-Day Limitation: Snowflake Standard only tracks copy history for 14 days
  2. Rerunnability: Must support reruns after 14 days
  3. Simplicity: No need to track what files were loaded when
  4. Bronze Pattern: Combined with TRUNCATE, ensures clean slate

Why ON_ERROR='ABORT_STATEMENT'?

  1. Transaction Safety: Triggers ROLLBACK on any error
  2. Data Integrity: Prevents partial loads (all-or-nothing)
  3. Clear Failures: Airflow task fails immediately on error

Why Transaction Wrapper?

  1. Atomicity: TRUNCATE + COPY execute as single unit
  2. Rollback Protection: TRUNCATE is reverted if COPY fails
  3. Data Loss Prevention: Table restored to pre-TRUNCATE state on error

Consequences

Positive

  1. ✅ Idempotent Pipelines: Rerunning DAGs produces identical results
  2. ✅ No 14-Day Limitation: Reruns work indefinitely
  3. ✅ Transaction Safety: ROLLBACK protects against data loss
  4. ✅ Simple Logic: No conditional behavior based on refresh type
  5. ✅ Clean Separation: Bronze (xo-foundry) vs Silver (dbt) responsibilities clear
  6. ✅ Best Practice: Aligns with industry standard ELT patterns

Negative

  1. ⚠️ Concurrent Query Risk: Queries fail if run during TRUNCATE window
  2. ⚠️ Window of Unavailability: Brief period when table is empty (1-2 minutes)
  3. ⚠️ No Partial Loads: All-or-nothing approach (failure reverts everything)

Mitigations

Risk Mitigation
Concurrent query failures Schedule during low-traffic windows (early morning)
Data loss on failure Transaction ROLLBACK restores pre-TRUNCATE state
Partial load scenarios ON_ERROR='ABORT_STATEMENT' ensures atomicity
Debugging complexity Enhanced logging shows TRUNCATE, COPY, COMMIT steps clearly

Neutral

  1. 🔄 Silver Responsibility: Historical data preserved in Silver layer
  2. 🔄 Time Travel: Snowflake Time Travel provides recovery (1-90 days)
  3. 🔄 Monitoring: Existing Airflow alerts detect load failures

Implementation

Files Modified

  1. packages/xo-foundry/src/xo_foundry/tasks/snowflake_tasks.py (lines 217-460):
  2. Changed FORCE=FALSE → FORCE=TRUE (hardcoded)
  3. Changed ON_ERROR='SKIP_FILE' → ON_ERROR='ABORT_STATEMENT' (hardcoded)
  4. Added BEGIN TRANSACTION before TRUNCATE
  5. Added TRUNCATE TABLE execution
  6. Added COMMIT after successful COPY

  7. Added ROLLBACK in exception handler

  8. packages/xo-foundry/src/xo_foundry/dag_factory/templates/snowflake_load.py.j2 (line 127):

  9. Changed "autocommit": True → "autocommit": False
  10. Enables explicit transaction control

Removed Configuration Options

The following config options were removed (behavior is now hardcoded): - force_overwrite: Always TRUE - on_error config: Always 'ABORT_STATEMENT' - Conditional TRUNCATE logic: Always executed

Load Strategy Behavior (Unchanged)

The load_strategy field in YAML configs remains unchanged and is used ONLY for: 1. S3 path structure (already working) 2. Silver merge logic (dbt, future implementation) 3. Documentation/clarity

Bronze loading is now strategy-agnostic - always TRUNCATE + FORCE for all strategies.

Testing Strategy

Manual Testing Checklist

  • Verify TRUNCATE execution in logs
  • Test idempotency: run DAG twice for same date, check row counts match
  • Test transaction rollback: inject malformed CSV, verify table restored
  • Verify FORCE=TRUE in Snowflake query history
  • Test Airflow task failure detection on COPY errors

Validation Queries

-- Check TRUNCATE execution in query history
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE QUERY_TEXT ILIKE '%TRUNCATE TABLE%'
  AND START_TIME > CURRENT_TIMESTAMP - INTERVAL '1 day'
ORDER BY START_TIME DESC;

-- Verify idempotency: all batches have same row count
SELECT BATCH_ID, COUNT(*) as row_count
FROM WBP_DB_DEV.BRONZE.GLADLY_CONTACT_TIMESTAMPS
GROUP BY BATCH_ID
HAVING COUNT(*) != (
  SELECT COUNT(*)
  FROM WBP_DB_DEV.BRONZE.GLADLY_CONTACT_TIMESTAMPS
  WHERE BATCH_ID = '2025-12-24'
  LIMIT 1
);
-- Should return 0 rows (all batches identical)

Deployment

Rollout Plan

  1. Phase 1: Deploy to dev environment, monitor for 1 day
  2. Phase 2: Validate idempotency with multiple test runs
  3. Phase 3: Deploy to production with team communication
  4. Phase 4: Monitor first production run closely

Communication

Team should be informed: - Bronze tables will be truncated before each load - Brief window (~1-2 min) when tables are empty - Queries during load window will fail (schedule accordingly) - Silver tables maintain historical data (Bronze is temporary)

  • ADR 001: Defines full_refresh, incremental, historical terminology
  • ADR 004: Metadata columns and RECORD_KEY/RECORD_HASH usage
  • dbt Phase 1: Silver layer preserves historical data from Bronze

Future Considerations

Phase 2: Silver FORCE Option

Separate from this decision, we may implement a --force flag for Silver layer: - Purpose: UPDATE/REPLACE records by RECORD_KEY regardless of existence - Scope: Affects dbt models, not xo-foundry Bronze loading - Distinction: Bronze ALWAYS truncates; Silver --force is optional override

Monitoring Enhancements

Consider adding: - Snowflake query monitoring to detect concurrent queries during TRUNCATE - Automated alerts for ROLLBACK events - Dashboard showing Bronze load times and transaction durations

References

  • Snowflake Documentation: TRUNCATE vs DELETE vs DROP
  • Snowflake COPY INTO: FORCE parameter behavior
  • Snowflake Transactions: BEGIN/COMMIT/ROLLBACK semantics
  • Implementation Plan: .claude/plans/fluffy-soaring-barto.md

Approval

Approved: 2025-12-24 Implemented: 2025-12-24 Validated: Pending production deployment

Summary: Bronze tables are ALWAYS truncated before loading with FORCE=TRUE and transaction safety, ensuring idempotent pipelines and clean separation between Bronze (temporary) and Silver (historical) layers.