community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Compare databases

What is the best way to compare 2 databases?

 

Data have been migrated from one system to another and I have pre-migration and post-migration reports.

 

I need to verify all fields are correctly reported (same exact attributes)

Alteryx Certified Partner
Alteryx Certified Partner

I like to compare data by use of a TRANSPOSE tool.  You would select your key field(s) and then select all data elements that you want to compare.  The output of this process is KEY+Name+Value.  You would then JOIN the data on KEY + NAME + VALUE.  Any records that are not an EXACT match would show up in the LEFT or RIGHT outputs.

 

You can then JOIN these UNMATCHED records by KEY + NAME and then you'll see the different VALUES that exist.

 

Does this answer the question?

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Certified Partner

Another option If your trying to match the metadata (field types/sizes etc) you can use the 'Field Info' tool on each database, join into a single table of metadata and check if all the fields match (fieldtype from DB1 = fieldtype from DB2)

thanks MarqueeCrew,

I am pretty new with Alteryx, so I might miss something.


I don't understand why you suggest TRANSPOSE tool.I believe JOIN might be the right formula for my questions.

 

 

I know DB1 has more fields than DB2.


I am using JOIN to help me figure it out.

Alteryx Certified Partner
Alteryx Certified Partner

compare Data pic.PNG

In this example I changed a ZIP code and was able to detect it.  The summary tells me what fields changed (how many times) from ZIP1 to ZIP2.  It will tell you all the data differences in your files.  Open the yxzp and see how it works.

 

Thanks,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Bolide

So instead of doing a transpose, I was wondering if I could generate a hash or checksum of each table see if two match for equality?

Alteryx Partner

There need to be a rather simple tool for comparing two data servers:
A) Prod DataServer vs.
B) Dev DataServer
which would compare all the dbs, tables, metadata, attributes, etc...
Previously I was using Micosoft Database Compare 2013, but I was looking for a robust solution in Alteryx.

Any thoughts?

Labels