Documentation

Report Data Editor

Build query pipelines in the Report Data Editor using table joins, global views, custom calculated fields, and partition optimization.

← Back to Documentation Hub / Building Blocks

Pipeline Joining Architecture

Query datasets are assembled by connecting sequential data sources, called Parts:

  1. Anchor Table (Part 1): The first Part added serves as the primary anchor for all subsequent query operations.

  2. Secondary Parts: Subsequent Parts join to the Anchor or prior Parts using standard join types (LEFT, INNER, RIGHT, FULL) mapped over explicit key conditions.

  3. Output Mode Selection:

    • Direct Output (df = joined): Sends combined table rows directly to the formula executor.
    • Global Query Output (global): Wraps joined tables in an overarching master query to run top-level database aggregations and window functions.

Database Calculated Fields

Calculated Fields execute directly on the underlying database engine before row fetching. This makes transformed fields instantly available across joins, filters, and post-processing formulas.

/* Example Database Calculated Field */
COALESCE(stock_quantity, 0) - COALESCE(allocated_units, 0) + COALESCE(incoming_units, 0)

Common Applications

  • Data Sanitization: Use functions like COALESCE(column, 0) or NULLIF() to clean missing values at the database layer.
  • Custom Dimensions: Create conditional flags (such as CASE WHEN sales > 1000 THEN 'High' ELSE 'Low' END) for simple downstream filtering.
  • Composite Keys: Concatenate multiple fields (such as CONCAT(store_id, '_', sku_id)) to enable multi-column key matching when joining Parts.

Performance Optimization Rules

  • Filter Early: Apply criteria directly within individual Parts before running join operations to keep data volumes small.
  • Partition Pruning: Always apply filters directly to partition keys (e.g., year, month, region) when reading Delta Lake or Parquet data files.

Critical Tip: Avoid wrapping partition columns in functions within WHERE clauses (e.g., WHERE COALESCE(status, 'N/A') = 'Active'). Wrapped functions prevent the query engine from using metadata indexes, triggering slow full-table scans. Use direct expressions (like WHERE status = 'Active') instead.


← Back to Documentation Hub / Building Blocks