0

Using a Business View to Update a Historical Table

How bad of an idea is it to use a business schema as a source for a historical table in  schema?

We have a requirement to keep a snapshot of customer data on a monthly basis. We have a business view that is created from a variety of customer related tables and I really don't want to create a table in a schema that is essentially a copy of the business view just for this purpose. It seems like that would be going against what the strength of Incorta is, not having to join all the tables in a schema prior to the business view.

3replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Ryan Sather -- I assume you need to keep several months of customer data, correct?  And this data needs to be persisted/stored (not just in memory), correct?  If so you can do the following...

    1) Create a new datasource in Incorta using the Postgres JDBC connector that points to the SQL interface of Incorta (basically a datasource that connects back to Incorta)

    2) Create a new table in a new schema that is defined by a SQL SELECT statement to your Business View (e.g. SELECT * FROM <Business View> GROUP BY Month)

    3) Schedule this table in this schema to load monthly (this table would persist in parquet just like any other Incorta table so you'd have your monthly snapshot for historical purposes.

    Reply Like
  • Hi Dan

    Thanks for the reply. Yes, that is essentially what I'm doing now and it's working. My question is more of if there is a better way or a reason I shouldn't do that. The other option I thought of would be to create the format I need as a table in a schema but then I'm losing the benefit of the business schema by creating the business schema in a table. 

    Reply Like
    • Ryan Sather based on your needs, what you're doing is what I would recommend.  Happy building!

      Reply Like
Like Follow
  • 1 mth agoLast active
  • 3Replies
  • 22Views
  • 2 Following