Tell me about your experience with SQL, database concepts, and incremental loading for keeping datasets up to date.
Demonstrate the incremental load using the provided PostgreSQL tables. Apply only records newer than the active watermark, insert new records, update existing records, and leave older records unchanged.
transaction_id, status, amount, and updated_at.transaction_id ascending.| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Source transaction identifier |
| status | VARCHAR(20) | Current transaction status |
| amount | NUMERIC(12,2) | Transaction amount |
| currency | VARCHAR(3) | ISO currency code |
| updated_at | TIMESTAMP | Timestamp of the latest source change |
| Column | Type | Description |
|---|---|---|
| transaction_idPK | INT | Target transaction identifier |
| status | VARCHAR(20) | Stored transaction status |
| amount | NUMERIC(12,2) | Stored transaction amount |
| currency | VARCHAR(3) | Stored ISO currency code |
| updated_at | TIMESTAMP | Timestamp represented in the target |
| Column | Type | Description |
|---|---|---|
| load_state_idPK | INT | Load state identifier |
| pipeline_name | VARCHAR(100) | Name of the incremental pipeline |
| last_loaded_at | TIMESTAMP | Latest successfully processed source timestamp |
| is_active | BOOLEAN | Whether this watermark is currently active |