Hi All, Any good examples of when to use the "Rewrite table component"?
I'm loading csv files to a table and I add two columns through the calculator component. The result is loaded to a second table. The second table is then used to append the data on a third table. The first and second table are not needed any more and may be dropped. Is this the best strategy? I'm trying to find out if this "rewrite table component" is handy to use here.
3 Community Answers
Cristian Ivanoff —
Cant change the heading. The question should be if the "rewrite table component" or other component may be used as a temporary table.
The best design pattern with Matillion is to first extract and load the data (using an Orchestration job) and then immediately afterwards append it into a permanent target table (using a Transformation job). A Rewrite Table component is a good choice in the Transformation job if you want to destroy and re-create the permanent table every time. Otherwise you may choose a Table Output (which appends) or a table Update (which upserts).
By default, every time a Load component executes, you’ll end up with a database table containing only the newly-extracted-and-loaded data. Load components either drop and re-create or else first truncate the table.
For this reason:
It’s good practice to follow a standard for naming Target Tables. For example always prefixing the name with stg_ or load_
After the Load component has finished, you will need to run a Transformation job to (optionally transform) the new data and copy it into the permanent table.
You may treat the stg_ or load_ tables as if they were temporary. For example you may choose to drop them as a final step. Alternatively, simply wait until the next time the job executes and the target table will first be recreated or truncated anyway.