EventsToTimeRange

EventsToTimeRange

Bases: DerivedTable

EventsToTimeRange converts individual code events into time ranges with start and end dates.

This derived table takes a codelist (e.g. medication prescriptions) and creates a time range for each event. The start date is the event date, and the end date is calculated by adding a specified number of days to the start date. Adjacent or overlapping periods are combined into single continuous periods.

This is particularly useful for identifying medication discontinuation when only prescription dates (not durations) are available. For example, discontinuation may be defined as a gap of ≥180 days between prescriptions.

Parameters:

Name	Type	Description	Default
domain	str	The source domain containing event data.	required
codelist	Optional[Codelist]	The codelist used to filter events.	None
max_days	Optional[int]	Fixed integer number of days used to compute the end date. Used for every row when days_columnname is not provided, or as a fallback when the days_columnname value is null for a given row.	None
days_columnname	Optional[str]	Name of a column in the source table whose integer value is used to compute the end date for that row, allowing a different duration per row. When the column value is null, max_days is used instead.	None
operator	str	Comparison operator applied to the day count. Use '<=' (default) to add the day value directly, or '<' to subtract one day first (exclusive upper bound). Applies to both max_days and days_columnname.	'<='
gap_period	int	Additional days appended to the computed day count before deriving the end date. For example, with max_days=30 and gap_period=5 the end date is calculated as if max_days=35. The operator offset is applied to the combined total. Defaults to 0 (no gap).	0
exit_domain	Optional[str]	Name of a domain table containing events that should terminate a period early, e.g. an observation that contradicts the state represented by domain (a normal-range lab value while tracking an abnormal-range state). When provided, the tentative end date (START_DATE + days) is truncated to the day before the earliest such event that falls within the period. If the contradictory event falls on the same day as START_DATE (a same-day discordant observation), the period ends immediately, i.e. END_DATE is set to START_DATE.	None
exit_codelist	Optional[Codelist]	Optional codelist used to filter exit_domain down to the events that count as contradictory/exit events. Requires exit_domain to be a code table. If omitted, every row of exit_domain is treated as a contradictory event.	None
name		Optional name for the derived table.	required

Attributes:

Name	Type	Description
domain		The domain of events to process.
codelist		The codelist used for filtering events.
max_days		Fixed day count fallback.
days_columnname		Column name providing per-row day counts (when used).
operator		The comparison operator ('<=' or '<').
gap_period		Additional days added to each day count before computing the end date.
exit_domain		Domain of events that end a period early.
exit_codelist		Codelist used to filter exit_domain to contradictory events.

Examples:

Identifying medication discontinuation

from phenex.derived_tables import EventsToTimeRange
from phenex.phenotypes import TimeRangePhenotype
from phenex.codelists import Codelist
from phenex.filters.value import LessThanOrEqualTo
from phenex.filters import RelativeTimeRangeFilter

# Create a derived table for medication coverage periods
et_codelist = Codelist(["RX12345", "RX12346"])
derived_table = EventsToTimeRange(
    name = 'ET_USAGE',
    domain = 'DRUG_EXPOSURE',
    codelist = et_codelist,
    max_days = 180,
)

# Return the persons that discontinue post index
# EVENT_DATE column will be the date of discontinuation
# VALUE will be the number of days from index to discontinuation date
pt_et_discontinuation = TimeRangePhenotype(
    domain = 'ET_USAGE',
    relative_time_range = RelativeTimeRangeFilter(
        when = 'after',
    )
)

# Execute the derived table with your data
et_periods = derived_table.execute(tables)

Ending a state early on a contradictory observation

from phenex.derived_tables import EventsToTimeRange, LoadTable

# domain of abnormal LVEF observations (VALUE <= 35)
lvef_low = LoadTable(
    name="LVEF_LOW",
    table_name="LVEF_ALL_PATIENTS",
    database=SNOWFLAKE_DEST_DATABASE,
    function=aggregate_lvef_low,
)

# domain of normal LVEF observations (VALUE > 35), used only to end periods early
lvef_normal = LoadTable(
    name="LVEF_NORMAL",
    table_name="LVEF_ALL_PATIENTS",
    database=SNOWFLAKE_DEST_DATABASE,
    function=aggregate_lvef_normal,
)

lvef_as_time_ranges = EventsToTimeRange(
    name="LVEF_TIME_RANGE",
    domain="LVEF_LOW",
    max_days=90,
    exit_domain="LVEF_NORMAL",
)

Source code in phenex/derived_tables/events_to_time_range.py

class EventsToTimeRange(DerivedTable):
    """
    EventsToTimeRange converts individual code events into time ranges with start and end dates.

    This derived table takes a codelist (e.g. medication prescriptions) and creates a
    time range for each event. The start date is the event date, and the end date is calculated
    by adding a specified number of days to the start date. Adjacent or overlapping periods are
    combined into single continuous periods.

    This is particularly useful for identifying medication discontinuation when only prescription
    dates (not durations) are available. For example, discontinuation may be defined as a gap of
    ≥180 days between prescriptions.

    Parameters:
        domain: The source domain containing event data.
        codelist: The codelist used to filter events.
        max_days: Fixed integer number of days used to compute the end date. Used for every row
                  when ``days_columnname`` is not provided, or as a fallback when the
                  ``days_columnname`` value is null for a given row.
        days_columnname: Name of a column in the source table whose integer value is used to
                         compute the end date for that row, allowing a different duration per row.
                         When the column value is null, ``max_days`` is used instead.
        operator: Comparison operator applied to the day count. Use ``'<='`` (default) to add
                  the day value directly, or ``'<'`` to subtract one day first (exclusive upper
                  bound). Applies to both ``max_days`` and ``days_columnname``.
        gap_period: Additional days appended to the computed day count before deriving the end
                    date. For example, with ``max_days=30`` and ``gap_period=5`` the end date is
                    calculated as if ``max_days=35``. The ``operator`` offset is applied to the
                    combined total. Defaults to ``0`` (no gap).
        exit_domain: Name of a domain table containing events that should terminate a period
                     early, e.g. an observation that contradicts the state represented by
                     ``domain`` (a normal-range lab value while tracking an abnormal-range
                     state). When provided, the tentative end date (``START_DATE`` + days) is
                     truncated to the day before the earliest such event that falls within the
                     period. If the contradictory event falls on the same day as ``START_DATE``
                     (a same-day discordant observation), the period ends immediately, i.e.
                     ``END_DATE`` is set to ``START_DATE``.
        exit_codelist: Optional codelist used to filter ``exit_domain`` down to the events that
                       count as contradictory/exit events. Requires ``exit_domain`` to be a code
                       table. If omitted, every row of ``exit_domain`` is treated as a
                       contradictory event.
        name: Optional name for the derived table.

    Attributes:
        domain: The domain of events to process.
        codelist: The codelist used for filtering events.
        max_days: Fixed day count fallback.
        days_columnname: Column name providing per-row day counts (when used).
        operator: The comparison operator (``'<='`` or ``'<'``).
        gap_period: Additional days added to each day count before computing the end date.
        exit_domain: Domain of events that end a period early.
        exit_codelist: Codelist used to filter ``exit_domain`` to contradictory events.

    Examples:

    Example: Identifying medication discontinuation
        ```python
        from phenex.derived_tables import EventsToTimeRange
        from phenex.phenotypes import TimeRangePhenotype
        from phenex.codelists import Codelist
        from phenex.filters.value import LessThanOrEqualTo
        from phenex.filters import RelativeTimeRangeFilter

        # Create a derived table for medication coverage periods
        et_codelist = Codelist(["RX12345", "RX12346"])
        derived_table = EventsToTimeRange(
            name = 'ET_USAGE',
            domain = 'DRUG_EXPOSURE',
            codelist = et_codelist,
            max_days = 180,
        )

        # Return the persons that discontinue post index
        # EVENT_DATE column will be the date of discontinuation
        # VALUE will be the number of days from index to discontinuation date
        pt_et_discontinuation = TimeRangePhenotype(
            domain = 'ET_USAGE',
            relative_time_range = RelativeTimeRangeFilter(
                when = 'after',
            )
        )

        # Execute the derived table with your data
        et_periods = derived_table.execute(tables)
        ```

    Example: Ending a state early on a contradictory observation
        ```python
        from phenex.derived_tables import EventsToTimeRange, LoadTable

        # domain of abnormal LVEF observations (VALUE <= 35)
        lvef_low = LoadTable(
            name="LVEF_LOW",
            table_name="LVEF_ALL_PATIENTS",
            database=SNOWFLAKE_DEST_DATABASE,
            function=aggregate_lvef_low,
        )

        # domain of normal LVEF observations (VALUE > 35), used only to end periods early
        lvef_normal = LoadTable(
            name="LVEF_NORMAL",
            table_name="LVEF_ALL_PATIENTS",
            database=SNOWFLAKE_DEST_DATABASE,
            function=aggregate_lvef_normal,
        )

        lvef_as_time_ranges = EventsToTimeRange(
            name="LVEF_TIME_RANGE",
            domain="LVEF_LOW",
            max_days=90,
            exit_domain="LVEF_NORMAL",
        )
        ```
    """

    def __init__(
        self,
        domain: str,
        max_days: Optional[int] = None,
        codelist: Optional["Codelist"] = None,
        days_columnname: Optional[str] = None,
        operator: str = "<=",
        gap_period: int = 0,
        exit_domain: Optional[str] = None,
        exit_codelist: Optional["Codelist"] = None,
        **kwargs,
    ):
        self.domain = domain
        if codelist is not None:
            if not isinstance(codelist, Codelist):
                raise ValueError("codelist must be an instance of Codelist or None")
            self.codelist_filter = CodelistFilter(codelist)
        self.codelist = codelist

        if max_days is None and days_columnname is None:
            raise ValueError("Either max_days or days_columnname must be provided")
        if operator not in ("<", "<="):
            raise ValueError(f"operator must be '<' or '<=', not {operator!r}")

        if exit_codelist is not None and exit_domain is None:
            raise ValueError("exit_domain must be provided when exit_codelist is used")
        if exit_codelist is not None:
            if not isinstance(exit_codelist, Codelist):
                raise ValueError(
                    "exit_codelist must be an instance of Codelist or None"
                )
            self.exit_codelist_filter = CodelistFilter(exit_codelist)
        self.exit_domain = exit_domain
        self.exit_codelist = exit_codelist

        self.max_days = max_days
        self.days_columnname = days_columnname
        self.operator = operator
        self.gap_period = gap_period
        super(EventsToTimeRange, self).__init__(**kwargs)

    def _execute(
        self,
        tables: Dict[str, Table],
    ) -> "Table":
        table = tables[self.domain]
        table = self._perform_codelist_filtering(table, tables)
        table = self._create_start_end_date_table(table)
        if self.exit_domain is not None:
            table = self._apply_exit_criteria(table, tables)
        table = self._combine_overlapping_periods(table)
        return table

    def _perform_codelist_filtering(self, table, tables):
        """
        Filter source table to codelist events of interest i.e. drug x events only

        Returns:
            Source DataFrame with all original columns:
        """
        if self.codelist is None:
            return table
        assert is_phenex_code_table(table)
        table = self.codelist_filter.autojoin_filter(table, tables)
        return table

    def _apply_exit_criteria(self, table, tables):
        """
        Truncate each tentative period's END_DATE to end sooner when a contradictory
        exit event falls within the period. An exit event on the same day as
        START_DATE (same-day discordant observation) ends the period immediately, i.e.
        END_DATE is set equal to START_DATE. An exit event on a later day truncates
        END_DATE to the day before the exit event.

        Returns:
            Table with three columns:
            PERSON_ID
            START_DATE
            END_DATE : truncated to end before/at the earliest exit event, if any
        """
        exit_table = tables[self.exit_domain]
        if self.exit_codelist is not None:
            assert is_phenex_code_table(exit_table)
            exit_table = self.exit_codelist_filter.autojoin_filter(exit_table, tables)
        exit_table = exit_table.select("PERSON_ID", "EVENT_DATE").mutate(
            EVENT_DATE=exit_table.EVENT_DATE.cast("date")
        )
        exit_table = exit_table.rename(EXIT_DATE="EVENT_DATE").distinct()

        # Find the earliest exit event within each tentative period
        joined = table.inner_join(
            exit_table,
            [
                table.PERSON_ID == exit_table.PERSON_ID,
                exit_table.EXIT_DATE >= table.START_DATE,
                exit_table.EXIT_DATE <= table.END_DATE,
            ],
        ).select(
            table.PERSON_ID,
            table.START_DATE,
            table.END_DATE,
            exit_table.EXIT_DATE,
        )
        first_exit = joined.group_by(["PERSON_ID", "START_DATE"]).agg(
            FIRST_EXIT_DATE=joined.EXIT_DATE.min()
        )

        table = table.left_join(
            first_exit,
            [
                table.PERSON_ID == first_exit.PERSON_ID,
                table.START_DATE == first_exit.START_DATE,
            ],
        ).select(
            table.PERSON_ID,
            table.START_DATE,
            table.END_DATE,
            first_exit.FIRST_EXIT_DATE,
        )
        table = table.mutate(
            END_DATE=ibis.case()
            .when(table.FIRST_EXIT_DATE.isnull(), table.END_DATE)
            .when(table.FIRST_EXIT_DATE == table.START_DATE, table.START_DATE)
            .else_(
                ibis.least(
                    table.END_DATE, table.FIRST_EXIT_DATE - ibis.interval(days=1)
                )
            )
            .end()
        )
        return table.select("PERSON_ID", "START_DATE", "END_DATE")

    def _create_start_end_date_table(self, table):
        """
        Create start and end date columns for the events. Start date is the event
        date; end date is computed from the per-row ``days_columnname`` value when
        non-null, falling back to the fixed ``max_days`` value otherwise.

        Returns:
            Table with three columns:
            PERSON_ID
            START_DATE : the codelist EVENT_DATE
            END_DATE   : START_DATE + days (per-row or fixed)
        """
        if self.days_columnname is not None:
            table = table.select("PERSON_ID", "EVENT_DATE", self.days_columnname)
        else:
            table = table.select("PERSON_ID", "EVENT_DATE")
        table = table.mutate(EVENT_DATE=table.EVENT_DATE.cast("date"))
        table = table.distinct()
        table = table.mutate(START_DATE=table.EVENT_DATE)
        offset = -1 if self.operator == "<" else 0
        gap = self.gap_period
        if self.days_columnname is not None:
            days_col = table[self.days_columnname].cast("int32")
            if self.max_days is not None:
                days_col = (
                    ibis.case()
                    .when(days_col.isnull(), self.max_days)
                    .else_(days_col)
                    .end()
                )
            days_col = days_col + gap + offset
            table = table.mutate(
                END_DATE=table.START_DATE + days_col.as_interval(unit="D")
            )
        else:
            table = table.mutate(
                END_DATE=table.START_DATE
                + ibis.interval(days=self.max_days + gap + offset)
            )
        table = table.group_by(["PERSON_ID", "START_DATE"]).agg(
            END_DATE=table.END_DATE.max()
        )
        return table.select("PERSON_ID", "START_DATE", "END_DATE")

    def _combine_overlapping_periods(self, table):
        """
        Combine all overlapping and consecutive periods

        Returns:
            Table with three columns with consecutive and overlapping periods combined into single time ranges
            PERSON_ID
            START_DATE : the codelist EVENT_DATE
            END_DATE : START_DATE + max_days
        """
        cop = CombineOverlappingPeriods(domain="_")
        table = cop.execute(tables={"_": table})
        return table

dependencies property

Recursively collect all dependencies of a node (including dependencies of dependencies).

Returns:

Type	Description
Set[Node]	List[Node]: A list of Node objects on which this Node depends.

dependency_graph property

Build a dependency graph where each node maps to its direct dependencies (children).

Returns:

Type	Description
Dict[Node, Set[Node]]	Dict[Node, Set[Node]: A mapping of Node's to their children Node's.

execution_metadata property

Retrieve the execution metadata rows for this node from phenex.db.

Returns:

Type	Description
	pandas.DataFrame: One row per database the node ran against, or None if it has never been executed. Columns: NODE_NAME (its table name), NODE_HASH (fingerprint of the node's settings), EXECUTION_PARAMS (where it ran: connector type, source and destination database), EXECUTION_ID, EXECUTION_START_TIME, EXECUTION_END_TIME, EXECUTION_DURATION, NODE_SQL (the query that built its table).

reverse_dependency_graph property

Build a reverse dependency graph where each node maps to nodes that depend on it (parents).

Returns:

Type	Description
Dict[Node, Set[Node]]	Dict[Node, List[Node]: A mapping of Node's to their parent Node's.

table = None instance-attribute property writable

This step's result table. After load(), the first read fetches it from the database and keeps it.

unique_name property

The name of this node's table. A Cohort sets it to the phenotype's place, e.g. OUTCOMES__BLEEDING__CHILD_1. Otherwise it is name.

attach_lazy_table(con, db_name)

Note where this step's saved table lives, without reading it. The first read of table fetches it. Used by load().

Source code in phenex/node.py

def attach_lazy_table(self, con: "DatabaseConnector", db_name: str) -> None:
    """Note where this step's saved table lives, without reading it.
    The first read of `table` fetches it. Used by load()."""
    self._table = None

    def _load() -> Optional[Table]:
        try:
            return con.get_dest_table(db_name)
        except Exception as e:
            logger.warning(
                f"Node '{self.name}': could not attach table '{db_name}': {e}"
            )
            return None

    self._table_loader = _load

clear_cache(con=None, recursive=False)

Clear the cached state for this node, forcing re-execution on the next call to execute().

This method removes the node's hash from the node states table and optionally drops the materialized table from the database. After calling this method, the node will be treated as if it has never been executed before.

Parameters:

Name	Type	Description	Default
con	Optional[object]	Database connector. If provided, clears only runs with matching execution context and drops the materialized table. If None, clears all runs for the node.	None
recursive	bool	If True, also clear the cache for all child nodes recursively. Defaults to False.	False

Example

# Clear all cached runs for a single node
my_node.clear_cache()

# Clear runs with specific execution context and drop materialized table
my_node.clear_cache(con=my_connector)

# Clear cache for node and all its dependencies
my_node.clear_cache(recursive=True)

Source code in phenex/node.py

def clear_cache(self, con: Optional[object] = None, recursive: bool = False):
    """
    Clear the cached state for this node, forcing re-execution on the next call to execute().

    This method removes the node's hash from the node states table and optionally drops the materialized table from the database. After calling this method, the node will be treated as if it has never been executed before.

    Parameters:
        con: Database connector. If provided, clears only runs with matching execution context and drops the materialized table. If None, clears all runs for the node.
        recursive: If True, also clear the cache for all child nodes recursively. Defaults to False.

    Example:
        ```python
        # Clear all cached runs for a single node
        my_node.clear_cache()

        # Clear runs with specific execution context and drop materialized table
        my_node.clear_cache(con=my_connector)

        # Clear cache for node and all its dependencies
        my_node.clear_cache(recursive=True)
        ```
    """
    # Delegate all logic to NodeManager
    return Node._node_manager.clear_cache(self, con=con, recursive=recursive)

copy()

Return a deep copy of this node with _table_name_prefix cleared on all nodes.

Source code in phenex/node.py

def copy(self):
    """Return a deep copy of this node with _table_name_prefix cleared on all nodes."""
    clone = copy.deepcopy(self)
    clone._table_name_prefix = None
    for dep in clone.dependencies:
        dep._table_name_prefix = None
    return clone

delete_table(con, table_name_prefix=None)

Delete this node's materialized table from the destination database.

Parameters:

Name	Type	Description	Default
con		Database connector.	required
table_name_prefix	Optional[str]	Optional prefix. If None, uses the prefix from the last execution.	None

Returns:

Name	Type	Description
bool	bool	True if the table was dropped, False if it didn't exist.

Source code in phenex/node.py

def delete_table(self, con, table_name_prefix: Optional[str] = None) -> bool:
    """
    Delete this node's materialized table from the destination database.

    Parameters:
        con: Database connector.
        table_name_prefix: Optional prefix. If None, uses the prefix from the last execution.

    Returns:
        bool: True if the table was dropped, False if it didn't exist.
    """
    db_name = self.get_table_name(table_name_prefix)
    try:
        con.drop_table(db_name)
        logger.info(f"Node '{self.name}': dropped table '{db_name}'")
        return True
    except Exception as e:
        logger.warning(f"Node '{self.name}': could not drop table '{db_name}': {e}")
        return False

execute(tables=None, con=None, overwrite=False, lazy_execution=False, n_threads=1, table_name_prefix=None)

Executes the Node computation for the current node and its dependencies.

Lazy Execution

When lazy_execution=True, nodes are only recomputed if changes are detected. The system tracks: 1. Node definition changes: Detected by hashing the node's parameters (from to_dict()) and class name 2. Execution environment changes: Detected by tracking source/destination database configurations

A node will be rerun if either: - The node's defining parameters have changed (different hash than last execution) - The database connector's source or destination databases have changed - The node has never been executed before

If no changes are detected, the node uses its cached result from the database instead of recomputing.

Requirements for lazy execution: - A database connector (con) must be provided to store and retrieve cached results - overwrite=True must be set to allow updating existing cached tables

State tracking is maintained in a local DuckDB database (__PHENEX_META__NODE_STATES table) that stores: - Node hashes, parameters, and execution metadata - Database connector configuration used during execution - Execution timing information

Parameters:

Name	Type	Description	Default
tables	Dict[str, Table]	A dictionary mapping domains to Table objects.	None
con	Optional[object]	Connection to database for materializing outputs. If provided, outputs from the node and all children nodes will be materialized (written) to the database using the connector. Required for lazy_execution.	None
overwrite	bool	If True, will overwrite any existing tables found in the database while writing. If False, will throw an error when an existing table is found. Has no effect if con is not passed. Must be True when using lazy_execution.	False
lazy_execution	bool	If True, only re-executes nodes when changes are detected in either the node definition or execution environment. Defaults to False. Requires con to be provided.	False
n_threads	int	Max number of Node's to execute simultaneously when this node has multiple children.	1

Returns:

Name	Type	Description
Table	Table	The resulting table for this node. Also accessible through self.table after calling self.execute().

Raises:

Type	Description
ValueError	If lazy_execution=True but overwrite=False or con=None.

Source code in phenex/node.py

def execute(
    self,
    tables: Dict[str, Table] = None,
    con: Optional[object] = None,
    overwrite: bool = False,
    lazy_execution: bool = False,
    n_threads: int = 1,
    table_name_prefix: Optional[str] = None,
) -> Table:
    """
    Executes the Node computation for the current node and its dependencies.

    Lazy Execution:
        When lazy_execution=True, nodes are only recomputed if changes are detected. The system tracks:
        1. Node definition changes: Detected by hashing the node's parameters (from to_dict()) and class name
        2. Execution environment changes: Detected by tracking source/destination database configurations

        A node will be rerun if either:
        - The node's defining parameters have changed (different hash than last execution)
        - The database connector's source or destination databases have changed
        - The node has never been executed before

        If no changes are detected, the node uses its cached result from the database instead of recomputing.

        Requirements for lazy execution:
        - A database connector (con) must be provided to store and retrieve cached results
        - overwrite=True must be set to allow updating existing cached tables

        State tracking is maintained in a local DuckDB database (__PHENEX_META__NODE_STATES table) that stores:
        - Node hashes, parameters, and execution metadata
        - Database connector configuration used during execution
        - Execution timing information

    Parameters:
        tables: A dictionary mapping domains to Table objects.
        con: Connection to database for materializing outputs. If provided, outputs from the node and all children nodes will be materialized (written) to the database using the connector. Required for lazy_execution.
        overwrite: If True, will overwrite any existing tables found in the database while writing. If False, will throw an error when an existing table is found. Has no effect if con is not passed. Must be True when using lazy_execution.
        lazy_execution: If True, only re-executes nodes when changes are detected in either the node definition or execution environment. Defaults to False. Requires con to be provided.
        n_threads: Max number of Node's to execute simultaneously when this node has multiple children.

    Returns:
        Table: The resulting table for this node. Also accessible through self.table after calling self.execute().

    Raises:
        ValueError: If lazy_execution=True but overwrite=False or con=None.
    """
    if table_name_prefix:
        table_name_prefix = re.sub(r"[^A-Za-z0-9_]", "_", table_name_prefix).upper()

    # Store prefix on all nodes so get_table_name() works after execution
    all_deps = self.dependencies
    for dep in all_deps:
        dep._table_name_prefix = table_name_prefix
    self._table_name_prefix = table_name_prefix

    # Handle None tables
    if tables is None:
        tables = {}
    else:
        # Work on a shallow copy so publishing completed nodes' tables (below)
        # for intra-execution sibling lookups never mutates the caller's dict,
        # which is reused as subset_tables_entry/index (domain -> PhenexTable).
        tables = dict(tables)

    # Build dependency graph for all dependencies
    nodes = self._graph_nodes()

    # Build dependency and reverse graphs
    dependency_graph = self._build_dependency_graph(nodes)
    reverse_graph = self._build_reverse_graph(dependency_graph)

    # Track completion status and results
    completed = set()
    completion_lock = threading.Lock()
    # Guards reads (snapshotting) and writes of the shared `tables` dict so a node's
    # _execute() never iterates it while another thread concurrently inserts into it.
    tables_lock = threading.Lock()
    worker_exceptions = []  # Track exceptions from worker threads
    stop_all_workers = (
        threading.Event()
    )  # Signal to stop all workers on first error

    # Track in-degree for scheduling
    in_degree = {}
    for node_name, dependencies in dependency_graph.items():
        in_degree[node_name] = len(dependencies)
    for node_name in nodes:
        if node_name not in in_degree:
            in_degree[node_name] = 0

    # Queue for nodes ready to execute
    ready_queue = queue.Queue()

    # Add nodes with no dependencies to ready queue
    for node_name, degree in in_degree.items():
        if degree == 0:
            ready_queue.put(node_name)

    def _restore_phenex_wrapper(materialized, original):
        # Re-wrap: get_dest_table() strips PhenexTable metadata, restore the original subclass.
        if isinstance(original, PhenexTable) and not isinstance(
            materialized, PhenexTable
        ):
            return type(original)(
                materialized, name=original.NAME_TABLE, column_mapping={}
            )
        return materialized

    def _run_and_materialise(node, node_name, node_tables):
        """Execute *node*, materialise the result, record timing, and update the run hash."""
        db_name = node.get_table_name(table_name_prefix)
        node.lastexecution_start_time = datetime.now()
        table = node._execute(node_tables)
        # Save the generating query before create_table() materializes it (to_sql layer 1).
        node._expression = table
        if table is not None:
            original = table
            con.create_table(table, db_name, overwrite=overwrite)
            table = _restore_phenex_wrapper(con.get_dest_table(db_name), original)
        node.lastexecution_end_time = datetime.now()
        node.lastexecution_duration = (
            node.lastexecution_end_time - node.lastexecution_start_time
        ).total_seconds()
        Node._node_manager.update_run_params(node, con)
        return table

    def worker():
        """Worker function for thread pool"""
        while not stop_all_workers.is_set():
            try:
                node_name = ready_queue.get(timeout=1)
                # timeout forces to wait 1 second to avoid busy waiting
                if node_name is None:  # Sentinel value to stop worker
                    break
            except queue.Empty:
                continue

            try:
                logger.info(
                    f"Thread {threading.current_thread().name}: executing node '{node_name}'"
                )
                node = nodes[node_name]
                active_display().node_started(node_name)
                served_from_cache = False

                # Snapshot `tables` once per node so this node's _execute() (which may
                # iterate the dict, e.g. LoadTable) never races with another thread
                # inserting a newly-completed sibling's table into the shared dict.
                with tables_lock:
                    node_tables = dict(tables)

                # Execute the node (without recursive child execution since we handle dependencies here)
                if lazy_execution:
                    if not overwrite:
                        raise ValueError(
                            "lazy_execution only works with overwrite=True."
                        )
                    if con is None:
                        raise ValueError(
                            "A DatabaseConnector is required for lazy execution."
                        )

                    # Sampler nodes write no dest table (the SAMPLER_STAGE group returns
                    # None; a fraction=1.0 node is a no-op via _skip_cache). Skip the cache
                    # for them — with no table to look up they would perpetually MISS.
                    if isinstance(node, NodeGroup) or getattr(
                        node, "_skip_cache", False
                    ):
                        table = node._execute(node_tables)
                    elif Node._node_manager.should_rerun(node, con):
                        table = _run_and_materialise(node, node_name, node_tables)
                    else:
                        db_name = node.get_table_name(table_name_prefix)
                        try:
                            table = con.get_dest_table(db_name)
                            served_from_cache = True
                        except Exception:
                            # Cached table was dropped or is inaccessible; recompute.
                            logger.warning(
                                f"Cached table for '{node_name}' not found at {db_name}; recomputing."
                            )
                            table = _run_and_materialise(
                                node, node_name, node_tables
                            )
                else:
                    # Time the execution
                    node.lastexecution_start_time = datetime.now()
                    table = node._execute(node_tables)
                    # Save the generating query before create_table() materializes it (to_sql layer 1).
                    node._expression = table

                    if (
                        con and table is not None
                    ):  # Only create table if _execute returns something
                        original = table
                        db_name = node.get_table_name(table_name_prefix)
                        logger.info(
                            f"Thread {threading.current_thread().name}: materializing '{node_name}' to database ..."
                        )
                        _t_mat = datetime.now()
                        con.create_table(table, db_name, overwrite=overwrite)
                        logger.info(
                            f"Thread {threading.current_thread().name}: materialized '{node_name}' "
                            f"in {(datetime.now() - _t_mat).total_seconds():.3f}s"
                        )
                        table = _restore_phenex_wrapper(
                            con.get_dest_table(db_name), original
                        )

                    node.lastexecution_end_time = datetime.now()
                    node.lastexecution_duration = (
                        node.lastexecution_end_time - node.lastexecution_start_time
                    ).total_seconds()

                node.table = table
                # Publish immediately (not only after the whole graph finishes) so
                # sibling nodes depending on this domain (declared via add_children)
                # can look it up in `tables` during the same execute() call.
                # Only DerivedTable nodes register a domain: ordinary nodes
                # (phenotypes, reporters, subset tables) must never be added to the
                # domains dict, e.g. a phenotype named 'DEATH' must not overwrite the
                # 'DEATH' source domain that later subset nodes still read from.
                if table is not None and isinstance(node, DerivedTable):
                    with tables_lock:
                        tables[node.name] = table

                with completion_lock:
                    completed.add(node_name)

                    # Update in-degree for dependent nodes and add ready ones to queue
                    for dependent in reverse_graph.get(node_name, set()):
                        in_degree[dependent] -= 1
                        if in_degree[dependent] == 0:
                            # Check if all dependencies are completed
                            deps_completed = all(
                                dep in completed
                                for dep in dependency_graph.get(dependent, set())
                            )
                            if deps_completed:
                                ready_queue.put(dependent)

                # Log completion with timing info
                if (
                    not served_from_cache
                    and node.lastexecution_duration is not None
                ):
                    logger.info(
                        f"Thread {threading.current_thread().name}: completed node '{node_name}' "
                        f"in {node.lastexecution_duration:.3f} seconds"
                    )
                else:
                    logger.info(
                        f"Thread {threading.current_thread().name}: completed node '{node_name}' (cached)"
                    )

                active_display().node_finished(
                    node_name,
                    served_from_cache,
                    duration=node.lastexecution_duration,
                )

            except Exception as e:
                logger.error(f"Error executing node '{node_name}': {str(e)}")
                with completion_lock:
                    # Store exception for main thread
                    worker_exceptions.append(e)
                    # Signal all workers to stop immediately and exit worker loop
                    stop_all_workers.set()
                    break
            finally:
                ready_queue.task_done()

    # Start worker threads
    threads = []
    for i in range(min(n_threads, len(nodes))):
        thread = threading.Thread(target=worker, name=f"PhenexWorker-{i}")
        thread.daemon = True
        thread.start()
        threads.append(thread)

    # Wait for all nodes to complete or for an error to occur
    _last_heartbeat = datetime.now()
    while (
        len(completed) < len(nodes)
        and not worker_exceptions
        and not stop_all_workers.is_set()
    ):
        threading.Event().wait(0.1)  # Small delay to prevent busy waiting
        _now = datetime.now()
        if (_now - _last_heartbeat).total_seconds() >= 30:
            _pending = sorted(set(nodes.keys()) - completed)
            logger.info(
                f"Node '{self.name}': still waiting for {len(_pending)} nodes: {_pending}"
            )
            _last_heartbeat = _now

    if not stop_all_workers.is_set():
        # Time to stop workers and cleanup
        stop_all_workers.set()

    # Check if any worker thread had an exception
    if worker_exceptions:
        # Signal workers to stop
        for _ in threads:
            ready_queue.put(None)
        # Wait for threads to finish
        for thread in threads:
            thread.join(timeout=1)
        # Re-raise the first exception
        raise worker_exceptions[0]

    # Signal workers to stop and wait for them
    for _ in threads:
        ready_queue.put(None)  # Sentinel value to stop workers

    for thread in threads:
        thread.join(timeout=1)

    logger.info(
        f"Node '{self.name}': completed multithreaded execution of {len(nodes)} nodes"
    )
    return self.table

get_sql_filename()

Filesystem-safe .sql filename for this node's table.

<>:"/\|?* become _, plus a short hash when that changed the name so two names that clean up alike never collide. Filenames longer than 250 characters (including .sql) are truncated with the same hash suffix, staying below the common 255-character per-filename filesystem limit. Only the filename changes, not the SQL.

Source code in phenex/node.py

def get_sql_filename(self) -> str:
    """Filesystem-safe `.sql` filename for this node's table.

    `<>:"/\\|?*` become `_`, plus a short hash when that changed the name so two
    names that clean up alike never collide. Filenames longer than 250 characters
    (including `.sql`) are truncated with the same hash suffix, staying below the
    common 255-character per-filename filesystem limit. Only the filename changes,
    not the SQL.
    """
    MAX_FILENAME_LEN = 250  # includes ".sql"
    name = self.get_table_name()
    safe = re.sub(r'[<>:"/\\|?*]', "_", name)
    if safe != name:
        safe = f"{safe}__{hashlib.md5(name.encode()).hexdigest()[:8]}"
    if len(safe) + len(".sql") > MAX_FILENAME_LEN:
        digest = hashlib.md5(name.encode()).hexdigest()[:8]
        keep = MAX_FILENAME_LEN - len(".sql") - len("__") - len(digest)
        safe = f"{safe[:keep]}__{digest}"
    return f"{safe}.sql"

get_table_name(table_name_prefix=None)

Get the table name used in the destination database for this node.

Parameters:

Name	Type	Description	Default
table_name_prefix	Optional[str]	Optional prefix. If None, uses the prefix from the last execution.	None

Returns:

Name	Type	Description
str	str	The table name in the destination database.

Source code in phenex/node.py

def get_table_name(self, table_name_prefix: Optional[str] = None) -> str:
    """
    Get the table name used in the destination database for this node.

    Parameters:
        table_name_prefix: Optional prefix. If None, uses the prefix from the last execution.

    Returns:
        str: The table name in the destination database.
    """
    prefix = table_name_prefix or self._table_name_prefix
    if prefix:
        prefix = re.sub(r"[^A-Za-z0-9_]", "_", prefix).upper()
        # a phenotype named by its place always gets the prefix
        if self._unique_name is not None or not self.name.startswith(prefix):
            return f"{prefix}__{self.unique_name}"
    return self.unique_name

to_dict()

Return a dictionary representation of the Node. The dictionary must contain all dependencies of the Node such that if anything in self.to_dict() changes, the Node must be recomputed.

Source code in phenex/node.py

def to_dict(self):
    """
    Return a dictionary representation of the Node. The dictionary must contain all dependencies of the Node such that if anything in self.to_dict() changes, the Node must be recomputed.
    """
    return to_dict(self)

to_sql(sql_dir='./sql', connector=None)

Return the SQL that produces this node's table, read-only (execute() writes it).

Returns the first source available, in order: the in-memory expression (this session), the saved {sql_dir}/{name}.sql file, the phenex.db cache, or a recompile from dependency tables. Pass connector to pin the dialect.

Raises:

Type	Description
RuntimeError	If required upstream tables are missing, call execute() first.

Source code in phenex/node.py

def to_sql(self, sql_dir: str = "./sql", connector=None) -> str:
    """Return the SQL that produces this node's table, read-only (execute() writes it).

    Returns the first source available, in order: the in-memory expression (this
    session), the saved `{sql_dir}/{name}.sql` file, the `phenex.db` cache, or a
    recompile from dependency tables. Pass `connector` to pin the dialect.

    Raises:
        RuntimeError: If required upstream tables are missing, call `execute()` first.
    """
    # Explicit compile target when a connector is given; else each layer self-identifies.
    target_dialect = (
        ibis_dialect_of_connector(connector) if connector is not None else None
    )
    name = self.get_table_name()  # cohort-prefixed; matches the written filename

    # 1. in-memory expression: compile + stamp in the target (or its own backend) dialect.
    if self._expression is not None:
        logger.info(f"to_sql('{name}') ← memory (this session)")
        return compile_sql(self._expression, dialect=target_dialect)

    # 2. saved file, keyed by name (cohort-prefixed) to match the written filename.
    # sql_dir=None means "no directory to read from", skip straight to the cache.
    if sql_dir is not None:
        filepath = os.path.join(sql_dir, self.get_sql_filename())
        if os.path.exists(filepath):
            file_sql = read_sql_file(filepath)
            # Use the file unless it's stamped for a different backend (wrong dialect).
            stamp = read_dialect_stamp(file_sql)
            if target_dialect is None or stamp is None or stamp == target_dialect:
                logger.info(f"to_sql('{name}') ← file: {filepath}")
                return file_sql

    # 3. phenex.db SQL dialect-aware with a connector (only its SQL), else hash-only lookup.
    try:
        sql = Node._node_manager.get_sql(self, con=connector)
    except Exception:
        sql = None
    if sql is not None:
        logger.info(f"to_sql('{name}') ← phenex.db cache")
        return sql

    # 4. auto-collect dependency tables and recompile
    collected = {dep.name: dep.table for dep in self.dependencies}
    missing = [dep_name for dep_name, tbl in collected.items() if tbl is None]
    if missing:
        raise RuntimeError(
            f"Node '{self.name}' cannot compile SQL. Missing upstream tables: "
            f"{', '.join(missing)}. Call `load()` or `execute()` first."
        )
    # KeyError = a missing source table -> clear error; a real node bug -> re-raise.
    try:
        # Recompiled from dependency tables: compile + stamp in the target (or backend) dialect.
        recompiled = compile_sql(self._execute(collected), dialect=target_dialect)
        logger.info(
            f"to_sql('{self.get_table_name()}') ← recompiled from dependencies"
        )
        return recompiled
    except KeyError as e:
        key = e.args[0]
        if key not in collected:
            raise RuntimeError(
                f"Node '{self.name}' cannot compile SQL. "
                f"Missing source-domain table: '{key}'. "
                "Call `load()` or `execute()` first."
            ) from e
        raise

visualize_dependencies()

Create a text visualization of the dependency graph for this node and its dependencies.

Returns:

Name	Type	Description
str	str	A text representation of the dependency graph

Source code in phenex/node.py

def visualize_dependencies(self) -> str:
    """
    Create a text visualization of the dependency graph for this node and its dependencies.

    Returns:
        str: A text representation of the dependency graph
    """
    lines = [f"Dependencies for Node '{self.name}':"]

    nodes = self._graph_nodes()

    # Build dependency graph
    dependency_graph = self._build_dependency_graph(nodes)

    for node_name in sorted(nodes.keys()):
        dependencies = dependency_graph.get(node_name, set())
        if dependencies:
            deps_str = ", ".join(sorted(dependencies))
            lines.append(f"  {node_name} depends on: {deps_str}")
        else:
            lines.append(f"  {node_name} (no dependencies)")

    return "\n".join(lines)