Skip to main content

​
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:
1

Detects the Deletion

During the next sync cycle, Kroo identifies that the record no longer exists in the source system
2

Marks as Deleted

Instead of removing the record, Kroo adds a deletion flag (typically _fivetran_deleted = 1)
3

Preserves Historical Data

The record remains in your warehouse with all original data intact, just flagged as deleted
4

Maintains Relationships

Related records and foreign key relationships remain functional for historical analysis

​
Database Implementation

​
Deletion Flags

Soft-deleted records are identified by special columns: 
-- Most common implementation
SELECT * FROM kroo_procore.rfis
WHERE _fivetran_deleted = 0;

​
Querying Active Records

To get only active (non-deleted) records in your queries: 
-- Example: Get active RFIs only
SELECT
    procore_id,
    due_date,
    status,
    created_date
FROM kroo_procore.rfis
WHERE (_fivetran_deleted = 0)
AND status = 'open';
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.

​
Benefits of Soft Deletes

Data Integrity

Historical reports remain accurate even when source records are deleted

Audit Trail

Track what was deleted and when for compliance and debugging

Relationship Preservation

Foreign key relationships stay intact for historical analysis

Recovery Capability

Accidentally deleted data can be identified and addressed

​
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

-- Standard query for current data
SELECT project_name, total_budget
FROM kroo_procore.projects
WHERE _fivetran_deleted = 0

​
Important Considerations

Query Performance

Always include WHERE _fivetran_deleted = 0 in your queries to maintain optimal performance and avoid including unwanted deleted records.

Storage Impact

Soft deletes mean your warehouse retains all historical data. Monitor storage usage and work with your team on data archival strategies if needed.

Data Privacy

Soft-deleted records containing sensitive information are still present in your warehouse. Consider this for GDPR and other privacy compliance requirements.

​
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: 
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.
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.

​
Need Help?

Questions About Data Retention?

Contact your implementation team at implementations@getkroo.com to discuss data retention policies or custom deletion handling requirements.