How to Audit and Update Managed Data Cloud (MDC) Views for Deleted Record Changes

This article describes the removal of the default soft-delete filter from Managed Data Cloud (MDC) views within the FORTH system. It was developed for Data Engineers, BI Analysts, and System Administrators who maintain downstream data pipelines and reporting environments integrated with the FORTH Managed Data Cloud (MDC).

The article is organized into the following sections:

• Overview
• Prerequisites
• How to Access
• How it Works
• Troubleshooting / FAQ
• Assistance

Overview

FORTH updated 168 Managed Data Cloud (MDC) table views by removing the default _fivetran_deleted = FALSE filter. This change gives your data team direct visibility into soft-deleted records across your syncs, allowing full auditability and custom data retention logic. To prevent unexpected soft-deleted records from appearing in your reports or data pipelines after August 19, 2026, you must audit your queries and explicitly handle deleted status filtering in your downstream code.

Prerequisites

• Access to your organization's Snowflake environment.
• Permission to query SNOWFLAKE.ACCOUNT_USAGE view histories, tasks, and definitions.
• Standard user access to the FORTH CRM.

How to Access

1. Log in to your Snowflake console.
2. Open a new SQL worksheet.
3. Set your target database context to DPP_DATA and target schema context to READER.

How it Works

To ensure your reports and downstream pipelines continue to function as expected, complete the following steps to audit your environment and update your queries.

1. Review the attached list of 168 impacted table views provided in your client notification email.
2. Run audit queries in Snowflake to identify views, scheduled tasks, and past queries that reference the impacted schema.
3. Review the returned objects that lack explicit deleted status checks.
4. Update downstream SQL definitions, tasks, and BI models to explicitly include _FIVETRAN_DELETED = FALSE in the WHERE clause where you want to exclude deleted records.

To assist your data engineering, BI, and analytics teams in identifying which views, tasks, or recent queries reference these schemas, you can use the following Snowflake helper queries:
 

SQL
-- Find views/tasks referencing a database/schema and check for _fivetran_deleted = false usage
-- Co-authored with CoCo

-- Set your target database and schema here:
SET target_db     = 'DPP_DATA';
SET target_schema = 'READER';

-- Views whose definition text references the target database + schema
SELECT
    'VIEW' AS object_type,
    table_catalog AS object_database,
    table_schema AS object_schema,
    table_name AS object_name,
    view_definition,
    CASE
        WHEN UPPER(view_definition) LIKE '%_FIVETRAN_DELETED%FALSE%'
          OR UPPER(view_definition) LIKE '%_FIVETRAN_DELETED%=%FALSE%'
        THEN TRUE
        ELSE FALSE
    END AS has_fivetran_deleted_check
FROM snowflake.account_usage.views
WHERE deleted IS NULL
  AND (
    UPPER(view_definition) LIKE '%' || $target_db || '.' || $target_schema || '%'
    OR UPPER(view_definition) LIKE '%' || $target_db || '."' || $target_schema || '"%'
  )
ORDER BY has_fivetran_deleted_check ASC, object_database, object_schema, object_name;

-- Tasks whose SQL body references the target database + schema
SELECT
    'TASK' AS object_type,
    task_database AS object_database,
    task_schema AS object_schema,
    task_name AS object_name,
    definition,
    CASE
        WHEN UPPER(definition) LIKE '%_FIVETRAN_DELETED%FALSE%'
          OR UPPER(definition) LIKE '%_FIVETRAN_DELETED%=%FALSE%'
        THEN TRUE
        ELSE FALSE
    END AS has_fivetran_deleted_check
FROM snowflake.account_usage.tasks
WHERE deleted IS NULL
  AND (
    UPPER(definition) LIKE '%' || $target_db || '.' || $target_schema || '%'
    OR UPPER(definition) LIKE '%' || $target_db || '."' || $target_schema || '"%'
  )
ORDER BY has_fivetran_deleted_check ASC, start_time DESC;

-- Query history referencing the target database + schema
SELECT
    query_id,
    query_type,
    user_name,
    role_name,
    database_name AS executed_in_database,
    schema_name AS executed_in_schema,
    start_time,
    total_elapsed_time / 1000 AS elapsed_seconds,
    query_text,
    CASE
        WHEN UPPER(query_text) LIKE '%_FIVETRAN_DELETED%FALSE%'
          OR UPPER(query_text) LIKE '%_FIVETRAN_DELETED%=%FALSE%'
        THEN TRUE
        ELSE FALSE
    END AS has_fivetran_deleted_check
FROM snowflake.account_usage.query_history
WHERE start_time >= DATEADD('day', -90, CURRENT_TIMESTAMP())
  AND execution_status = 'SUCCESS'
  AND (
    UPPER(query_text) LIKE '%' || $target_db || '.' || $target_schema || '%'
    OR UPPER(query_text) LIKE '%' || $target_db || '."' || $target_schema || '"%'
  )
ORDER BY has_fivetran_deleted_check ASC, start_time DESC;
 

 

Troubleshooting/FAQ

Why am I seeing soft-deleted records in my downstream reports after the update?

If soft-deleted records appear in your reporting tables after August 19, 2026, your query relied on the legacy default view filter. Add WHERE _FIVETRAN_DELETED = FALSE to your query or model definition to restore the previous behavior.

What if I want to retain soft-deleted records for historical auditing?

No action is required. Removing the default filter allows you to access all records, including deleted ones. You can query _FIVETRAN_DELETED = TRUE to analyze deleted data.

When will this deployment take effect?

The update will be deployed after 9:00 PM CST on August 19, 2026. Ensure all SQL updates are completed before this time.

Assistance

For further assistance, reach out to support@setforth.com or mdc@setforth.com.

Article Revision History