Hello, thank you for your question.

Since SQL-based Delta Live Tables (DLT) pipelines do not natively support IF-ELSE or TRY-CATCH constructs, you'll need an approach to gracefully handle missing files. Here are two recommended solutions:

Solution 1: Use read_files with allow_missing_files = true

The read_files function supports an allow_missing_files option that prevents failure if the path is empty or missing. Modify your queries as follows:

CREATE OR REFRESH MATERIALIZED VIEW jamf_mobile_devices
COMMENT "Incremental upload of data incoming from endpoint_2"
AS
SELECT *
FROM read_files(
  '/Volumes/catalog/${schema_path}/catalog_name-${schema_path}/source=source/*/table_name=endpoint_2/*/*.json',
  allow_missing_files => true  -- Prevents failure when no files exist
);

This ensures that if no new files exist, the query does not fail and instead returns an empty dataset.

Solution 2: Use WHERE with a Metadata Table (Advanced)

• Maintain a metadata table tracking available file paths.
• Before querying, filter out missing sources dynamically.

WITH available_sources AS (
  SELECT path FROM file_metadata_table WHERE table_name = 'endpoint_2'
)
CREATE OR REFRESH MATERIALIZED VIEW jamf_mobile_devices AS
SELECT * FROM read_files(
  (SELECT path FROM available_sources)
);

This prevents querying non-existent sources.

If allow_missing_files => true works in your case, it is the easiest solution. Otherwise, dynamically maintaining a metadata table for available file paths is a more robust alternative. Let me know if you need more details!