DataSimplePractical GuideLog inOpen DataSimple
DataSimple
Chapter 10Govern and monitor

Audit trail columns

Back to the index · Previous: Lakehouse governance

How it works#

Every row DataBridge writes carries bookkeeping columns, and every Bronze and Silver apply adds its own. They are ordinary columns: you query them like any other, and they are what lets you answer "which run loaded this row", "what changed since yesterday", "what did this table look like at 18:58", "which rows arrived twice" and "which file or message did this value come from" without a separate audit store. Columns with one underscore (_load_ts) describe the row as it arrived; columns with two underscores (__seq, __is_current) describe the row's place in a Silver table; positions (__cdc_lsn, __kafka_offsets) are not columns in your tables but the watermark values DataBridge keeps per table, visible in Load History and in the job result.

Showing and hiding them#

In every notebook result and on the Kinetic page, the audit trail columns (every column whose name starts with _) are hidden by default, so the grid shows your business columns first. The Audit columns (n) button next to Columns shows them; press it again to hide them. The choice is remembered in your browser. While they are hidden, the column picker lists them greyed out. CSV, Excel and Copy export the columns you see.

A column you add with a formula or the Kinetic pen is placed after your business columns and before the audit trail columns, in the generated SQL and Python as well as in the preview.

A Bronze table with its 10 audit trail columns hidden: only the business columns show
A Bronze table with its 10 audit trail columns hidden: only the business columns show
With Audit columns on: the new gross_amount column sits before _row_hash and the other audit trail columns
With Audit columns on: the new gross_amount column sits before _row_hash and the other audit trail columns

The columns#

On every source copy (Parquet, Delta, Iceberg) and carried into Bronze#

Column Written by Meaning Example
_row_hash DataBridge, before the write SHA-256 over all source columns (nulls replaced by typed defaults first); the same input always gives the same hash, so an unchanged row is never counted as changed 9f2c…e41a
_extracted_at DataBridge when the read from the source finished 2026-10-16 18:58:02
_source_ts DataBridge the row's change time in the source: the watermark or a recognised change column (updated_at, modified_at, …), the change-log commit time (SQL Server LSN mapped to time, PostgreSQL commit time), the file drop change time column, or the Kafka timestamp field 2026-10-16 18:57:49
_load_ts DataBridge when the row was written to the lake 2026-10-16 18:58:10
_batch_id DataBridge the job id of the run that wrote the row (the same number as in Load History) 74
_datasource DataBridge the Data Source name (or the Source Name typed for a manual connection) CdcDemo-SQL, Kafka-Test
_user DataBridge who or what started the run: the user name or the run label given through the API; scheduled runs carry nightly:schedule:<id> <name> admin, nightly:schedule:5 Kafka-Test
_op_type DataBridge I insert, U update, D delete, as seen by this run (first run: all I) U
_is_deleted DataBridge true for a tombstone: the key was deleted at the source (change log, delete marker, snapshot comparison) true
_row_seq DataBridge 1..N in extract order within the load 1017
_is_duplicate DataBridge true for the second and later occurrence of a key in one load; the first occurrence is the kept row, so WHERE NOT _is_duplicate is the unique set false
_tenantid DataBridge; the notebook writers (save_table, write_delta, create_managed_table, merge_delta, ds.apply_changes, ds.apply_changes_from_snapshot, @ds.table, CREATE TABLE … AS SELECT, MERGE CHANGES) the tenant that wrote the row, three digits. A DataFrame that already carries _tenantid keeps it; one that lacks it (including one where it was dropped in Python) gets the cell's tenant on write 001

File drop#

Column Written by Meaning Example
_source_file DataBridge, file drop the object the row was read from drop/CUSTOMER/inc-2026-10-14.parquet
_file_etag DataBridge, file drop the storage ETag of that object at read time; a re-dropped file with new content gets a new ETag and is read again "5d41402a…"

Kafka#

Column Written by Meaning Example
_kafka_topic, _kafka_partition, _kafka_offset DataBridge, Kafka source where the message sat orders, 2, 8
_kafka_timestamp DataBridge the broker's message time 2026-10-18 09:05:00
_kafka_key DataBridge the message key as text {"order_id": 5}
_kafka_error DataBridge set when the message could not be decoded in the chosen format; the data columns are then null Expecting value: line 1 column 1
_src_db, _src_table, _src_lsn, … DataBridge, Debezium format scalar fields of the Debezium source block orders

Positions kept per table (not columns in the table)#

Name Kept by Meaning Example
__cdc_lsn DataBridge, change-log CDC the log position the next run starts from: a SQL Server LSN, or the PostgreSQL WAL position as 16 hex digits; advanced only after the copies and Silver were written 0x0000002C000005700005
__kafka_offsets DataBridge, Kafka the next offset to read per partition, as JSON; advanced the same way {"0": 7, "1": 7, "2": 9}
__kafka_schema DataBridge, Kafka the column schema recorded on the first run; every later batch is aligned to it, so a key-only delete or an integer in a double column cannot change the copies

The job result (GET /api/databridge/api/jobs/<id>) shows cdc_lsn_before / cdc_lsn_after and kafka_offsets_before / kafka_offsets_after.

Bronze#

Column Written by Meaning Example
_batch_id Bronze apply the job whose batch last touched this key 74
_op_type, _is_deleted Bronze apply the latest operation for the key; a deleted key stays as a marker D, true
_cdc_seq DataBridge change-log and Kafka readers order of the change inside one batch (PostgreSQL logical decoding, Kafka partition and offset order); used to keep the last change per key before the copies are written 10432
_tenantid Bronze apply, notebook writers the tenant that wrote the row, as on the source copies 001

Silver, Type 1 (current state)#

Column Written by Meaning Example
__seq DataBridge's Silver option job id × 10⁹ + position in the batch; a MERGE clause only wins when the incoming __seq is greater 77000000002
__sequence ds.apply_changes, APPLY CHANGES INTO the SEQUENCE BY value of the change that produced the row, in its own type (a timestamp if you sequenced by _load_ts) 2026-10-16 18:58:10
__updated_at both when this row was last written 2026-10-16 18:58:23
_source_ts DataBridge's Silver option kept from the batch row
_tenantid both the tenant that wrote the row; stored and updated like the other system columns, never compared as a data column (a snapshot row that differs only in _tenantid is not an update) 001

Silver, Type 2 (history)#

Column Written by Meaning Example
__start_at both forms when the version became current: the source commit time (DataBridge, when the database provides it) or the SEQUENCE BY value (ds.apply_changes) 2026-10-16 18:40:11
__end_at both when it was closed; null while current 2026-10-16 18:58:23
__is_current both true for the open version of each key true
__op both I the key's first version, U a later one (DataBridge also writes D on the event that closed a version) U
__seq / __sequence DataBridge / notebook form as above
_tenantid both the tenant that wrote the version; a system column, so a change in it alone opens no version and TRACK HISTORY EXCEPT COLUMNS never needs to name it 001

Protected columns#

_tenantid on lake tables and tenantid on application-database tables are protected: no SQL cell, Python cell, scheduled job, pipeline or ds call may drop, rename or retype them. The server refuses ALTER TABLE … DROP COLUMN[S], RENAME COLUMN, ALTER COLUMN … TYPE, CHANGE COLUMN and a REPLACE COLUMNS list that leaves the column out, in either spelling, before Spark is involved; the kernel repeats the check for every spark.sql() call made from Python, and for CREATE OR REPLACE TABLE / INSERT OVERWRITE with an explicit column list that lacks _tenantid on a table that has it. The message is _tenantid is a protected column: every lake table keeps the tenant that wrote it. ds.apply_changes(except_columns=…) and APPLY CHANGES … EXCEPT COLUMNS (…) may not name it either. A df.drop("_tenantid") in Python is not intercepted, but every write helper stamps the column back, so the written table always has it. CREATE TABLE … AS SELECT gains '<tenant>' AS _tenantid when the query does not already produce the column; a plain INSERT INTO <lake table> SELECT … is left as written, so include _tenantid in such a SELECT (or write through save_table / write_delta, which add it). Reads do not filter by _tenantid, tenancy of reads is the catalog's; as defence in depth, read_table on a table that carries _tenantid only yields the cell's tenant's rows (rows written before the column existed, where it is NULL, still show).

Questions you can answer with them#

Which batch loaded a row, and what else came with it

SQL
SELECT OrderID, _op_type, _batch_id, _load_ts, _user
FROM datasimple.source.dbo_Orders_DELTA WHERE OrderID = 1003;

SELECT _op_type, COUNT(*) FROM datasimple.source.dbo_Orders_DELTA
WHERE _batch_id = 74 GROUP BY _op_type;                       -- what job 74 did

Rows changed since a time

SQL
SELECT * FROM datasimple.bronze.dbo_Orders
WHERE _load_ts > TIMESTAMP '2026-10-16 18:00:00' ORDER BY _load_ts;        -- by load time

SELECT * FROM datasimple.bronze.dbo_Orders
WHERE _source_ts > TIMESTAMP '2026-10-16 18:00:00';                        -- by the time it changed in the source

Live rows, deletes, duplicates

SQL
SELECT COUNT(*) FROM datasimple.source.dbo_Orders_DELTA WHERE NOT _is_deleted;          -- live
SELECT OrderID, _batch_id, _load_ts FROM datasimple.source.dbo_Orders_DELTA WHERE _is_deleted;   -- deleted keys and when
SELECT * FROM datasimple.source.CUSTOMER WHERE _is_duplicate;                           -- repeated keys in one load
SELECT OrderID, COUNT(*) FROM datasimple.silver.dbo_Orders GROUP BY OrderID HAVING COUNT(*) > 1;  -- must return nothing

Which file or message a value came from

SQL
SELECT _source_file, _file_etag, COUNT(*) FROM datasimple.source.CUSTOMER GROUP BY 1, 2;
SELECT order_id, _kafka_partition, _kafka_offset, _kafka_timestamp FROM datasimple.source.orders
WHERE order_id = 7;
SELECT * FROM datasimple.source.orders WHERE _kafka_error IS NOT NULL;                   -- undecodable messages

State as of a time (Type 2)

SQL
SELECT * FROM datasimple.silver.dbo_Orders_HISTORY
WHERE __start_at <= TIMESTAMP '2026-10-16 18:58:10'
  AND (__end_at IS NULL OR __end_at > TIMESTAMP '2026-10-16 18:58:10');

SELECT * FROM datasimple.silver.dbo_Orders_HISTORY WHERE __is_current;   -- current state from the history
SELECT OrderID, Amount, Status, __op, __start_at, __end_at
FROM datasimple.silver.dbo_Orders_HISTORY WHERE OrderID = 1001 ORDER BY __start_at;   -- one key's life

Which run last touched a Silver row, and whether a batch was applied

SQL
SELECT OrderID, __seq, FLOOR(__seq / 1000000000) AS job_id, __updated_at FROM datasimple.silver.dbo_Orders;
SELECT COUNT(*) FROM datasimple.silver.dbo_Orders WHERE FLOOR(__seq / 1000000000) = 77;   -- rows written by job 77

Replay a run (what the lake looked like before job 77, Delta copy)

SQL
DESCRIBE HISTORY datasimple.source.dbo_Orders_DELTA;      -- one version per run, with timestamp and operation
SELECT * FROM datasimple.source.dbo_Orders_DELTA VERSION AS OF 1;
Python
before = read_delta_at("source.dbo_Orders_DELTA", version=1)
after  = read_table("source.dbo_Orders_DELTA")
after.join(before, "OrderID", "left_anti").show()            # keys job 77 added

Tenant#

Every table in the application database also stores who the row belongs to: column tenantid, exactly three digits (000..999, zero-padded), NOT NULL. A few older platform tables keep the name tenant_id; the meaning is the same. The code stamps it from the logged-in session (or the parent row for background work) and a write without a tenant is rejected by the database, never filled with a default. The column is protected: it cannot be dropped, renamed or retyped from a SQL cell, Python, psql or a migration, and a new table cannot be created without it unless it is on the explicit global list. The rule, the exempt list and the guard are in TENANT-STANDARD.md.

Read more#

Next: 11. Monitoring, logs and recovery