Databricks Write Patterns Lab

Technical learning lab · supporting evidence for CareerSignal and MarketTime

I built this lab around one practical question: what should happen when the same data arrives again?

The answer depends on what the pipeline treats as identity. A file name, an event identifier, a business key and a date partition solve different problems. The notebooks let me test those differences instead of treating INSERT, append, COPY INTO, MERGE and overwrite as interchangeable commands.

What I am demonstrating

• I define the grain and identifier before choosing a write method.
• I keep source timestamps separate from ingestion timestamps.
• I validate physical rows against distinct events or business keys.
• I test replay behaviour deliberately instead of assuming a load is idempotent.
• I preserve raw history first, then resolve duplicates before current-state modelling.
• I use Delta history, time travel and restore as controlled recovery mechanisms.

Learning path

Notebook	Engineering question	Expected evidence
01 — Insert	Does an idempotent setup make the data load idempotent?	A replay changes physical rows from 1 to 2 while distinct events remain 1
02 — Bulk append	Does moving a batch efficiently protect it from duplicates?	A replay changes physical rows from 5 to 10 while distinct events remain 5
03 — Copy Into	What identity does file-aware ingestion remember?	The same file is skipped; identical content under a new name is loaded
04 — Complete lab	How do write patterns connect across Bronze, Silver and recovery?	Append, file replay, deduplication, merge, delete, partition overwrite, SCD2 and restore are compared in one run

The first three notebooks preserve the small experiments I used to build the concepts. The fourth is the cleaner, self-contained demonstration I would use for a walkthrough.

System view

flowchart LR
    A["Source files"] --> B["Bronze: preserve input and lineage"]
    B --> C["Validate identity and replay behaviour"]
    C --> D["Silver: deduplicate, merge and retain history"]
    D --> E["Gold: replace deterministic analytical slices"]
    D --> F["Delta history, time travel and recovery"]

Loading

This is a write-behaviour lab rather than a complete production pipeline. Orchestration, monitoring, access control and deployment belong in the flagship projects where the wider system context is visible.

Repository map

.
├── notebooks/
│   ├── 01_insert.ipynb
│   ├── 02_bulk_append.ipynb
│   ├── 03_copy_into.ipynb
│   └── 04_complete_write_patterns_lab.py
├── data/
│   ├── api_responses/       # JSON/JSONL inputs for notebooks 01–03
│   └── *.csv                # Inspectable copies of the complete-lab inputs
├── docs/run-evidence/       # Credential-free workspace validation notes
├── tests/                   # Repository and fixture contract checks
├── LICENSE
└── README.md

Run the focused notebooks

1. Import the selected notebook into a Databricks workspace.
2. Create or choose a dedicated practice catalog/schema and Unity Catalog Volume.
3. Upload the matching file from data/api_responses/ to the Volume path shown at the top of the notebook.
4. Run one cell at a time, including the deliberate replay.
5. Compare the physical row count with the distinct event or business-key count.

The focused notebooks use workspace.de_write_lab.practice_files as a clear learning default. Change that fully qualified location if your workspace uses a different writable catalog.

Run the complete lab

1. Import 04_complete_write_patterns_lab.py as a Databricks source notebook.
2. Attach compute with Unity Catalog and Delta support.
3. Set the catalog, schema and Volume widgets to a dedicated practice location.
4. Run one cell at a time. The notebook creates synthetic inputs only under its own write_patterns_lab/ directory.
5. Keep the optional cleanup cell commented until you have reviewed the tables and Delta history.

The complete notebook creates its own safe sample files, so it does not depend on an employer system or private dataset.

Validation contract

For each pattern I ask four questions:

1. What does one row represent?
2. Which field or file identifies it?
3. What changes when the same input is replayed?
4. Can I reconcile the resulting table with the expected state?

The checks include row counts, distinct-key counts, duplicate-key inspection, source-versus-target comparison, partition-filter evidence and Delta table history. This makes the result explainable beyond “the command ran successfully.”

Pattern boundaries

Pattern	Identity it understands	Safe replay boundary
Plain insert / append	None by default	Requires event deduplication or a downstream rule
COPY INTO	Previously loaded file	A renamed file with identical content can still be loaded
MERGE	Match condition, normally a business key	Source keys must be unique and stale updates need an explicit rule
replaceWhere	A declared predicate or partition slice	Correctness depends on replacing a complete, deterministic slice
SCD Type 2	Business key plus change detection	Hash and effective-date logic must prevent unchanged reruns from creating history

Current status

Implemented and documented:

• reproducible synthetic input files;
• explicit source and target contracts;
• SQL and PySpark loading examples;
• append and same-file/renamed-file replay tests;
• event-key deduplication and business-key MERGE;
• duplicate-source-key handling;
• hard delete, soft delete and staging-table truncation;
• partitioned Delta tables, pruning evidence and selective overwrite;
• full overwrite for deterministic Gold rebuilds;
• SCD Type 2 history;
• Delta history, time travel and restore;
• executable assertions for the key replay, merge, partition, history and recovery invariants;
• automated repository checks for notebook syntax, narration and fixture contracts;
• expected results and interpretation after each deliberate test.

The repository structure, input fixtures and notebook syntax are checked locally and in GitHub Actions. The consolidated notebook also completed one clean top-to-bottom Databricks Free Edition run: 22 code cells succeeded, zero failed, and every executable assertion passed. The credential-free summary is recorded in docs/run-evidence/2026-08-13-complete-lab.md. Production orchestration and monitoring will be demonstrated in CareerSignal rather than duplicated here.

Data, attribution and evidence boundary

The repository uses synthetic learning data only. It contains no employer data, internal screenshots, credentials or confidential architecture.

The focused notebooks preserve my hands-on learning, and the consolidated walkthrough now has a verified workspace run. Nothing here is presented as production experience. Documentation may include AI-assisted editing, while the implementation remains subject to my own review and validation.