> ## Documentation Index
> Fetch the complete documentation index at: https://guide.getkroo.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Data Retention & Soft Deletes

> Understanding how Kroo handles deleted records and maintains data integrity in your warehouse.

## Overview

Kroo uses a **soft-delete methodology** to handle records that are deleted in your source construction management systems. This approach prioritizes data integrity and historical tracking over immediate data removal. Deleted records will always be marked with the condition `_fivetran_deleted=1`

## How Soft Deletes Work

When a record is deleted in your source system (like Procore), Kroo:

<Steps>
  <Step title="Detects the Deletion">
    During the next sync cycle, Kroo identifies that the record no longer exists in the source system
  </Step>

  <Step title="Marks as Deleted">
    Instead of removing the record, Kroo adds a deletion flag (typically `_fivetran_deleted = 1`)
  </Step>

  <Step title="Preserves Historical Data">
    The record remains in your warehouse with all original data intact, just flagged as deleted
  </Step>

  <Step title="Maintains Relationships">
    Related records and foreign key relationships remain functional for historical analysis
  </Step>
</Steps>

## Database Implementation

### Deletion Flags

Soft-deleted records are identified by special columns:

<Tabs>
  <Tab title="Standard Flag">
    ```sql theme={null}
    -- Most common implementation
    SELECT * FROM kroo_procore.rfis
    WHERE _fivetran_deleted = 0;
    ```
  </Tab>
</Tabs>

### Querying Active Records

To get only active (non-deleted) records in your queries:

```sql theme={null}
-- Example: Get active RFIs only
SELECT
    procore_id,
    due_date,
    status,
    created_date
FROM kroo_procore.rfis
WHERE (_fivetran_deleted = 0)
AND status = 'open';
```

<Info>
  **Pro Tip:** Many BI tools and data visualization platforms can automatically filter out soft-deleted records by adding `WHERE _fivetran_deleted = 0` to your base queries.
</Info>

## Benefits of Soft Deletes

<CardGroup cols={2}>
  <Card title="Data Integrity" icon="shield-check">
    Historical reports remain accurate even when source records are deleted
  </Card>

  <Card title="Audit Trail" icon="file-text">
    Track what was deleted and when for compliance and debugging
  </Card>

  <Card title="Relationship Preservation" icon="link">
    Foreign key relationships stay intact for historical analysis
  </Card>

  <Card title="Recovery Capability" icon="arrow-rotate-left">
    Accidentally deleted data can be identified and addressed
  </Card>
</CardGroup>

## Working with Soft Deletes

### In Your Reports

When building reports or dashboards:

1. **Default to Active Records**: Filter out deleted records unless specifically analyzing deletions
2. **Historical Analysis**: Include deleted records when analyzing trends over time
3. **Data Validation**: Use deletion flags to identify data quality issues

### In Your Queries

<Tabs>
  <Tab title="Active Records Only">
    ```sql theme={null}
    -- Standard query for current data
    SELECT project_name, total_budget
    FROM kroo_procore.projects
    WHERE _fivetran_deleted = 0
    ```
  </Tab>

  <Tab title="Include Deleted Records">
    ```sql theme={null}
    -- Historical analysis including deleted records
    SELECT
        project_name,
        total_budget,
        CASE WHEN _fivetran_deleted = 1 THEN 'Deleted' ELSE 'Active' END as status
    FROM kroo_procore.projects
    ```
  </Tab>

  <Tab title="Deletion Analysis">
    ```sql theme={null}
    -- Analyze deletion patterns
    SELECT
        DATE(updated_at) as deletion_date,
        COUNT(*) as records_deleted
    FROM kroo_procore.rfis
    WHERE _fivetran_deleted = 1
    GROUP BY DATE(updated_at)
    ORDER BY deletion_date DESC
    ```
  </Tab>
</Tabs>

## Important Considerations

<CardGroup cols={1}>
  <Card title="Query Performance" icon="gauge-high">
    Always include `WHERE _fivetran_deleted = 0` in your queries to maintain optimal performance and avoid including unwanted deleted records.
  </Card>

  <Card title="Storage Impact" icon="hard-drive">
    Soft deletes mean your warehouse retains all historical data. Monitor storage usage and work with your team on data archival strategies if needed.
  </Card>

  <Card title="Data Privacy" icon="user-shield">
    Soft-deleted records containing sensitive information are still present in your warehouse. Consider this for GDPR and other privacy compliance requirements.
  </Card>
</CardGroup>

## Filtering Deleted Records Across Parent & Child Tables

When a record is deleted in your source system, Kroo flags the **parent** record as deleted — but that flag does not automatically flow down to its **child** records. If you query a child table on its own, stale rows may still appear as active.

To get accurate results, join the child table back to its parent and filter both:

```sql theme={null}
SELECT
    li.*
FROM kroo_procore__direct_cost_line_items li
INNER JOIN kroo_procore__direct_costs dc
    ON li.direct_cost_id = dc.id
WHERE dc._fivetran_deleted = 0
  AND li._fivetran_deleted = 0
```

In this example, `kroo_procore__direct_costs` is the parent and `kroo_procore__direct_cost_line_items` is the child. The join ensures any line items belonging to a deleted direct cost are excluded from your results.

<Info>
  Apply this pattern wherever you query child tables in Kroo. Using the parent as your anchor is the most reliable way to keep stale records out of your reports.
</Info>

## Need Help?

<Card title="Questions About Data Retention?" icon="question-circle">
  Contact your implementation team at [implementations@getkroo.com](mailto:implementations@getkroo.com) to discuss data retention policies or custom deletion handling requirements.
</Card>