Deduplicate cost entry line items when processing

[adberglund]

User Story

As a koku user, I do not want duplicate data represented in my dashboard so that I have an accurate representation of my usage and billing.

Impacts

Masu Backend

Role

backend engineer

Assumptions

• The cursor position being tracked per file in theory takes care of not processing duplicate data, but things go wrong and we should specifically enforce that a duplicate row can't make it into the database

Implementation Details

• We will need a unique constraint on some combination of columns to enforce this
• Currently we are using Postgres's copy functionality. We may need to switch to an insert with ON CONFLICT DO NOTHING clause See https://www.postgresql.org/docs/9.6/static/sql-insert.html for explanation
• It is also worth researching if copy can ignore duplicates
• It is also worth attempting to query and cache the necessary line item columns to construct the unique constraint in code and skip inserting matches

Acceptance Criteria

• [ ] Verify that when a duplicate line item is inserted it does not end up in the database

[blentz]

For AWS, the uniqueness constraints are:

1. time period (1 hour resolution)
2. region / AZ
3. service
4. resource / line item description

So, for example, there should be exactly 1 line item for (13 Mar 2018 12:00-13:00, ec2, us-east-1a, RunInstances-m4.xlarge)

The main question in terms of de-duplication is, when we see a duplicate line with a different charge, do we ignore or replace the line in our DB?

For the month-end finalization, I'd think we'd want to repace/update rows rather than just ignore-and-discard them.

[chargio]

1. The file is final (it has an invoice number): there is an error, that can not happen --> notify the customer
2. The file being processed is newer, there has been some processing after the last time we read it. Update it.
3. The file being processed is older, drop it.

[lcouzens]

[2018-12-04 10:51:13,895: ERROR/ForkPoolWorker-1] masu.processor.tasks.process_report_file[f2fff33a-6021-4b15-a015-e036c08ec8c9]: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

So when we have dup rows in a sheet we see that error and nothing gets imported.

@adberglund Is that the expected outcome?

[adberglund]

@lcouzens and I spoke about this, and it is not expected outcome. The example error was while processing OCP and I believe represents a bug that needs investigation.

[lcouzens]

Seems this is working as expected on AWS so this is just a potential bug/issue with OCP.

[lcouzens]

Verified Commit: 3011d8e0631a72809f070c36de0e533d89ac0ade Added multiple duplicate lines to a CSV and verified that each line was only processed once.