Does Lakeflow Connect Have Any Change Tracking Diagnostics?

[cvh]

We have set up Change Tracking on multiple SQL Servers for Lakeflow Connect successfully in the past, but lately we are having lots of problems with a couple of servers. The latest utility script has been run and both lakeflowSetupChangeTracking and lakeflowFixPermissions have been executed several times. The database trigger [lakeflowDdlAuditTrigger_1_5] is enabled, CT has been enabled on the database (checked sys.change_tracking_databases) and on the tables we want to ingest (checked sys.change_tracking_tables). We can see the ddl audit table (dbo.[lakeflowDdlAudit_1_5]) is also present, but when we try to run the ingestion pipeline we get ...

INGESTION_GATEWAY_DDL_OBJECTS_MISSING

DDL objects missing on table '<TABLE>'. Execute the DDL objects script and full refresh the table on the Ingestion Pipeline

Reason: - Catalog is not properly configured to capture DDL changes for the provided set of tables. How to fix: - To capture DDL changes for a table with LakeFlow Connect, appropriate set of support objects must be setup on the catalog. Different support objects are needed depending if tables that are replicated are using CT and/or CDC extraction mechanism. - Investigation shows that only CT tables are included in the replication, but corresponding DDL support object do not exist in the catalog.

We also found this in the ingestion gateway error logging, but it still doesn't identify specifically where the problem is:

ddlCaptureNotEnabledTables={<TABLE>= Reason: - Catalog is not properly configured to capture DDL changes for the provided set of tables. How to fix: - To capture DDL changes for a table with LakeFlow Connect, appropriate set of support objects must be setup on the catalog. Different support objects are needed depending if tables that are replicated are using CT and/or CDC extraction mechanism. - Investigation shows that only CT tables are included in the replication, but corresponding DDL support object do not exist in the catalog.

Is there anything that we can run on the SQL Server to give us more information about the specific problem? How does Lakeflow Connect determine that DDL has not been configured? When the documentation has been followed - and yet we still get this error - where to from here? 

[Ashwin_DSA]

Hi @cvh,

Having done some research, I have found that for SQL Server CT, Lakeflow expects the following (per DB/catalog)..

• Utility objects at version 1.5: dbo.lakeflowUtilityVersion_1_5() = '1.5'.
• CT DDL objects present in the same database as the CT tables:
  • Table: dbo.lakeflowDdlAudit_1_5
  • Database DDL trigger: lakeflowDdlAuditTrigger_1_5 (enabled)
• The ingestion user can read from dbo.lakeflowDdlAudit_1_5.
• The DDL trigger is actually writing rows for CT tables (i.e., DDL is being captured).

If any of these fail, the gateway reports INGESTION_GATEWAY_DDL_OBJECTS_MISSING and logs ddlCaptureNotEnabledTables.

Does your setup meet all the above requirements? Do you know how to check these, or do you need help with that?

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[amirabedhiafi]

I think you need to check 2 more things that are easy to miss.

The ingestion user must have all required metadata permissions, not just access to the source tables, including sys.change_tracking_tables, sys.change_tracking_databases, sys.objects, sys.triggers, and VIEW DATABASE PERFORMANCE STATE

And the DDL audit trigger must actually be writing rows into dbo.lakeflowDdlAudit_1_5 for CT tables. Databricks validates DDL support, CT/CDC state, utility objects, and permissions separately, so object existence alone is not sufficient.

If you upgraded the utility script you need to verify that the gateway was stopped first and the setup procedure was rerun with the same parameters as the original config.

Once you fix the issue don't forger to make full refresh for the affected table.