def _expire_history_script(self, source):

        if not isinstance(source, Table):
            raise ValueError('Source must be a table')

        # Get the secondary columns of the table
        secondary_columns = [column for column in source.columns()
                             if not column.primary]

        # There must be at least one primary and secondary key
        if len(source.primary_keys) == 0:
            raise ValueError('Source table must have a primary key')
        if len(secondary_columns) == 0:
            raise ValueError('Source table must have a non-primary column')

        # Expire if corresponding row in the source table has been changed
        # First, match primary key info to determine corresponding rows
        same_statement =\
            '{history_name}.{column_name} = {source_name}.{column_name}'
        matching_primary_keys_condition = ' AND '.join(
            [same_statement.format(history_name=self.full_name,
                                   source_name=source.full_name,
                                   column_name=column.name)
             for column in source.primary_keys]
        )
        # Then, filter to get only the records that have changed
        # A record has been changed if one of it's non-primary columns
        # are different
        different_statement = """
            {history_name}.{column_name} != {source_name}.{column_name}
            OR (
                {history_name}.{column_name} IS NULL
                AND {source_name}.{column_name} IS NOT NULL
            )
            OR (
                {history_name}.{column_name} IS NOT NULL
                AND {source_name}.{column_name} IS NULL
            )
            """
        record_changed_condition = '(' + ' OR '.join(
            [different_statement.format(history_name=self.full_name,
                                        source_name=source.full_name,
                                        column_name=column.name)
             for column in secondary_columns]
        ) + ')'
        # Lastly, filter to get only the non-expired columns
        # This statement will be reused for the removal check
        not_expired_condition =\
            '{expiration_column} = \'{expiration_max}\''.format(
                expiration_column=HIST_EXPIRATION_COLUMN,
                expiration_max=HIST_EXPIRATION_MAX,
            )
        # Expire changed columns
        script = SqlScript("""
            UPDATE {history_name}
                SET {expiration_column} = SYSDATE - INTERVAL '0.000001 seconds'
            FROM {source_name}
            WHERE {matching_primary_keys}
                AND {record_changed}
                AND {not_expired};
            """.format(history_name=self.full_name,
                       expiration_column=HIST_EXPIRATION_COLUMN,
                       source_name=source.full_name,
                       matching_primary_keys=matching_primary_keys_condition,
                       record_changed=record_changed_condition,
                       not_expired=not_expired_condition))

        # Expire if corresponding row in the source table has been deleted
        # Filter to get the history rows which have primary keys
        # that are no longer in the source table
        primary_keys = ",".join([name for name in source.primary_key_names])
        missing_primary_keys_condition = """
            (
                {primary_keys}
            )
            NOT IN (
                SELECT {primary_keys}
                FROM {source_name}
            )
            """.format(primary_keys=primary_keys,
                       source_name=source.full_name)

        script.append("""
            UPDATE {history_name}
                SET {expiration_column} = SYSDATE - INTERVAL '0.000001 seconds'
            WHERE {missing_primary_keys}
                AND {not_expired};
            """.format(history_name=self.full_name,
                       expiration_column=HIST_EXPIRATION_COLUMN,
                       missing_primary_keys=missing_primary_keys_condition,
                       not_expired=not_expired_condition))
        return script
SQL script to expire outdated records
dataset/ETHPy150Open coursera/dataduct/dataduct/database/history_table.py/HistoryTable._expire_history_script