Documentation

Table Maintenance

Abstract

SummaryBasic table maintenance tasks, including how to rebuild a table, add a table, and remove a table from replication.

This article covers the following common data warehouse table maintenance tasks:

These tasks should apply to all data warehouse types. Any differences are noted below.

Task: Rebuild a table (and restart replication)
  1. First, stop replicating the table. You can do this directly in the UI via the Mapper, or you can discard via the Code Engine. We recommend using the Code Engine, especially if you have data in the Restream Queue.

    For example, to remove the EMPLOYEES table via the Code Engine, you might add code like this in the transform function:

    Copy
    if event['_metadata']['event_type'] == 'EMPLOYEES':
        return None
  2. Verify the Restream Queue doesn't contain more events of that type. Revisit step 1 if necessary.

  3. Drop the table(s) in your data warehouse.

    For Redshift:

    Copy
    BEGIN TRANSACTION; 
    DROP TABLE IF EXISTS "{schema}"."{table_name}_log" CASCADE;
    DROP TABLE IF EXISTS "{schema}"."{table_name}" CASCADE; 
    COMMIT;

    For Snowflake:

    Copy
    BEGIN TRANSACTION; 
    DROP TABLE IF EXISTS "{SCHEMA}"."{TABLE_NAME}_LOG" CASCADE;
    DROP TABLE IF EXISTS "{SCHEMA}"."{TABLE_NAME}" CASCADE; 
    COMMIT;

    For BigQuery:

    1. In the Mapper, click Edit (at the very top of the screen, next to the dataset selection).

    2. Search for the table in the Select Table drop-down menu.

    3. Hover the cursor over the table you want to remove and click the X to drop the table. Do the same for the corresponding _log table as well.

    4. Click Apply.

    For Azure:

    Copy
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}' AND TABLE_NAME = '{table}')
        DROP TABLE [{schema}].[{table}];
    
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}' AND TABLE_NAME = '{table}_log')
        DROP TABLE [{schema}].[{table}_log];"
  4. Delete the mapping for the Event Type. You can do this via the API or via the Mapper.

    Delete the mapping via the API:

    api.delete_event_type(event_type)

    (See Managing Mappings Programmatically for more information on using the API)

    Delete the mapping via the Mapper:

    1. In the Mapper, click Edit (top of the left-hand column).

    2. Find the event type to remove.

    3. Click the X to delete the Event Type, confirm the deletion.

    4. Click Done.

  5. Now, re-enable replication for your table (for example, in the Code Engine, remove the code you added in the transform function). Your data should begin flowing again.

Task: Remove a table from replication

When you remove a table from replication, you also have the option of dropping the table from the Data Warehouse.

  1. Remove the table from the list of tables to replicate, if applicable. You can use the Code Engine or the Mapper.

    Discard the event type via Code Engine:

    Copy
    if event['_metadata']['event_type'] == 'EMPLOYEES':
        return None

    Discard event types via the Mapper:

    1. Select the Event Type in the list on the left.

    2. Click the Actions pull-down and click Discard Event Type.

    3. Click Discard to confirm your choice.

      Note

      You can remap the event type later.

  2. (Optional) Drop the table from the data warehouse.

    For Redshift:

    Copy
    BEGIN TRANSACTION;
    DROP TABLE IF EXISTS "{schema}"."{table_name}_log" CASCADE;
    DROP TABLE IF EXISTS "{schema}"."{table_name}" CASCADE;
    COMMIT;

    For Snowflake:

    Copy
    BEGIN TRANSACTION; 
    DROP TABLE IF EXISTS "{SCHEMA}"."{TABLE_NAME}_LOG" CASCADE;
    DROP TABLE IF EXISTS "{SCHEMA}"."{TABLE_NAME}" CASCADE; 
    COMMIT;

    For BigQuery:

    1. In the Mapper, click Edit (at the very top of the screen, next to the dataset selection).

    2. Search for the table in the Select Table drop-down menu.

    3. Hover the cursor over the table you want to remove and click the X to drop the table. Do the same for the corresponding _log table as well.

    4. Click Apply.

    For Azure:

    Copy
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}' AND TABLE_NAME = '{table}')
        DROP TABLE [{schema}].[{table}];
    
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{schema}' AND TABLE_NAME = '{table}_log')
        DROP TABLE [{schema}].[{table}_log];"
Task: Add a table to an input

To add a table to an existing input, go to the Plumbing screen, select your Input, and then select the Settings tab. 

If you have questions about rebuilding tables, or anything else for that matter, please let us know!

Search results

    No results found